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

朱勇

摘要：本文对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.