APP下载

基于Oracle数据库的索引优化查询分析

2019-09-09朱勇

数码世界 2019年6期
关键词:语句数据库性能

朱勇

摘要:本文对Oracle数据库的查询技术进行分析与研究,通过索引优化查询技术达到提高查询速度的目的。在对Oracle數据表创建合理的索引后,通过对优化SQL语句,提升了数据库查询效率。

关键字:Oracle数据库索引 优化查询 分析

1绪论

作为主流的关系型数据库,Oracle数据库被广泛应用于各种管理信息系统。Oracle数据库使用SQL语言进行数据的增删改查等操作,查询效率已成为影响数据库性能的关键因素之一,开展查询优化研究已成为研究关注的问题。

Oracle数据库体系结构决定了数据库系统内存、硬件及各系统进程和程序的使用,了解Oracle的体系结构有助于分析和研究数据库的优化问题。Oracle数据库主要由内存结构、进程结构以及存储结构等部分组成。其中,内存结构包含系统全局区域(SGA)以及程序全局区域(PGA)。进程结构包括用户进程和服务器进程以及各种后台进程。存储结构分为逻辑存储和物理存储。SGA对数据库性能影响最为关键,也对查询优化起着重要作用。

Oracle数据库提供了大量的索引选项,建立索引是进行查询优化的重要方法。使用索引可以避免全表扫描,并减少查询所造成的I/O开销,提高数据的查询效率和数据库的性能。但索引会占据数据库的空间,根据实际的查询需要建立索引才能达到优化查询的目的。

2创建合理的索引

索引可以只有一个字段,也可以同时具有多个字段。一般情况下多使用单字段索引,因为太多的复合索引在有单个字段索引的情况下是没有意义的,还会降低数据增删操作时的效率。但当复合索引中包含所有要查询的列时,Oracle数据库仅查询索引块就可以获取查询结果,使用复合索引比多个单列索引的查询速率会高得多。

索引将数据表中的逻辑值映射到rowID,在查询时可以快速定位到数据的物理位置。但是研究发现,对一个大型表建立索引后并不能加快查询的速率,反而影响数据库性能。这是源于SGA数据管理方式。Oracle数据库进行数据块高速缓存管理时,带有索引数据会比普通的数据具有更高的驻留权限,在内存空间使用中,会将普通数据先行移除。对含有索引的大型表查询时,索引数据可能用尽数据块的缓存空间,数据库必须进行频繁的磁盘读写来获取数据。因此,可对大型表进行分区处理,根据相应的分区表来建立分区索引。在对表建立索引时,应保证创建索引的字段尽可能出现在Where条件子句中。

有效的利用索引可以很好的提升查询效率。索引不是越多越好,必须要适当使用才能做到事半功倍,不仅提升查询速率,还能不过度消耗磁盘容量。

3基于索引的SQL语句优化

正确地使用索引可以加快数据查询速度,此时还需要良好的SQL语句来支持。接下来本文着重分析建立索引后利用SQL语句来优化查询。

(1) is Null与is not Null

在任何列中如果包含了Null指针,则该列不会被包含在索引中。即使索引建立在多列中,一旦列中出现了空值,该列索引也不会提高性能。而且在Where子句后使用is Null或is not Null时,优化器认为索引是无效的。 (2)连接列 当一个SQL查询语句中使用连接符进行列连接时,此查询语句中的列即使建立了索引,优化器也不会使用该列的索引。例如有查询语句(1):

SQL> select * from student where snol‖'‖sage=192110121 '语句(1)

此时在sno字段上有索引,但是未通过索引对表进行查询。查询语句(1)执行109Rows、324Bytes的性能为:SelectStatement操作和Table Access Full操作均耗时28s。

将语句(1)改为语句(2)进行查询:

SQL> select*from student where sno='1921101' andsage=21;语句(2)

此时通过sno字段上带有的索引进行了查询,提高了查询速度。查询语句(2)执行109Rows、324Bytes的性能为:SelectStatement操作、Table Access By Index Rowid操作和IndexUnique Scan操作均耗时23s。

(3)模糊查询like语句中带有通配符(%)

执行一条SQL查询语句时,like语句后的通配符如果出现在要搜索的词汇之首,则要查询的列不使用索引;如果通配符不出现在词首,则索引是有效的。例如有查询语句(3):

SQL> select sno from student where sno like'%92110%';语句(3)

此时查询不使用索引。查询语句(3)执行109Rows、324Bytes的性能为:Select Statement操作和Table AccessFull操作均耗时31s。

将语句(3)改为语句(4)进行查询:

SQL> select sno from student where sno like''821101%';

语句(4)

此时查询使用索引。查询语句(4)执行109Rows、324Bytes的性能为:Select Statement操作和Index Unique Scan操作均耗时21s。

(4) order by子句

order by子句可以对返回的查询结果进行排序。查询时使用order by子句需保证所查询的列中含有索引,且索引被使用,否则将会减慢查询速度。

(5)比较运算符

SQL查询语句where子句中常会使用>、<、=、<>等比较运算符。例如,有<>(不等于比较运算符)查询语句(5)和<(小于比较运算符)、>(大于比较运算符)查询语句(6):

SQL> select*from student where sno <>21;语句(5)

SQL> select*from student where sno<21 0r sno>21;语句(6)

语句(5)和语句(6)的运行结果是一样的,但是语句(6)会比语句(5)的查询速度更快,因为Oracle数据库对于语句(6)的查询方案允许使用索引。

(6) in和exists的使用

使用in子句时,首先进行子查询,把查询的结构存放在临时表中,之后再进行主查询,这就加大了系统查询时间。因此,使用in子句更为直观,而使用exists则会有更好的效率。例如有m查询语句(7):

SQL> select*from student where sno in (select snofrom se where sno=c0021);语句(7)

查询语句(7)执行600Rows、500Bytes的性能为:SelectStatement操作、TabLe Access Full和Index Range Scan操作均耗时68s。

如果SQL语句where子句的列中带有索引,使用exists子句时,优化器不通过全表扫描而是仅仅根据索引就可以完成操作。而且当使用Exists时,Oracle数据库先进行主查询然后进行子查询,直到找到第一个与条件相匹配的值為止,节省了查询时间。例如有exists查询语句(8):

SQL> select*from student where exists (select snofrom se where sno=c0021);语句(8)

查询语句(8)执行600Rows、500Bytes的性能为:SelectStatement操作、Table Access by Index Rowid,和IndexRange Scan操作均耗时58s。View操作耗时16s。查询所消耗的时间和资源明显比查询语句(7)要少,使用exists可以直接通过索引来获取数据,而使用in则不能,在大型表查询中使用exists的优势是明显的。

4结论

数据库查询作为数据库一项重要的功能要尽力提高其效率。本文从建立索引、优化SQL语句方面对Oracle数据库查询优化进行研究,有效提高了查询效率。

参考文献

[1]刘鹏.基于关系数据库的查询优化研究[J]网络安全技术与应用,2018 (02):66-67.

[2]饶淑珍.基于Oracle数据库的几种常见SQL优化策略[J].电脑知识与技术,2018,l4 (08):12-13.

[3]贾钦,数据库管理系统中查询优化的设计和实现措施[J]电子技术与软件工程,2018(15):142

[4]方芳,谢慧,关于Oracle数据库的SQL查询语句优化探究[J],河南科技,2018 (04):25-26.

[5]苏现鹏,基于Oracle数据库海量数据的查询优化研究[J].现代工业经济和信息化,2017,7 (03):84-85.

[6]. Information Technology - Database Management; New Datafrom Technical University of Munich Illuminate Findings inDatabase Management (Query optimization through the lookinggLass, and what we found running the Join Order Benchmark)[J]. Computers, Networks&Communications, 2018.

猜你喜欢

语句数据库性能
生与死的尊严(散文)
桌面端,GTX 1650并不是千元价位的显卡好选择
数据库
数据库
数据库
数据库
基本算法语句
我喜欢
作文语句实录