Oracle索引在数据查询中的应用

夏忠球，俞国红

（苏州健雄职业技术学院，江苏 苏州 215411）

Oracle索引在数据查询中的应用

夏忠球，俞国红

（苏州健雄职业技术学院，江苏 苏州 215411）

文章以Oracle数据库生产实践中的数据扫描为主要研究内容，结合索引技术等相关知识，提出了合理使用索引技术，避免执行全表扫描的设计总体思路，通过实际的需求分析，介绍了基于索引的SQL语句优化方法，实现数据库的高效查询。

索引；索引扫描；Oracle

随着大数据的广泛应用，数据高效查询已经在生产实践中成为新的研究热点。数据量大的时候，常出现软件性能的问题，需要反复修改结构化查询语言（Structured Query Language，SQL）语句，调试性能以达到最优。利用数据索引技术，正确使用数据库的索引，解决海量数据查询处理难的问题，不仅可实现高效的查询，而且提高磁盘IO的读写效率。

1 Oracle数据扫描技术

1.1 索引概述

索引是数据库中的对象，类似书的目录结构，Oracle的索引创建在数据表的列。索引作为一种独立的数据结构，需要占用存储空间。索引最大的作用是加快查询速度，其直接指向包含所查询值的行的位置，并存储了表中某一列的所有值。一个索引是基于数据表中的某一列而创建的，并且这列是排过序的。索引使用的数据结构有两种：MsSQL使用的是B+Tree结构，而Oracle及Sysbase使用的是B-Tree结构。

索引使用默认的键值排序来取代全表扫描，提高查询效率。那什么是全表扫描呢？全表扫描就是获取表中所有数据块，再根据条件进行过滤。在数据库中，对没有索引的表进行查询，搜寻表中每一条记录，直到找到所有符合给定条件的记录的过程，称为全表扫描。

1.2 数据库索引的工作原理

Oracle使用ROWID列来建立内部索引。那什么是ROWID呢？ROWID为数据表中行的唯一标识，是一个伪列，可以用在SELECT中，但不可以用INSERT，UPDATE来修改该值。每个表Oracle都存在一个伪列ROWID，这个伪列可以用SELECT查看，但是不可以用INSERT和UPDATE，DELETE命令来修改删除操作。

数据库执行查询时，首先会检查表中的列有没有创建索引，如果该列有索引，数据库还要判断是否应该使用索引检索要查找的值。

查询数据库的结果集时，当读取的数据比重大，占表数据20%～70%时，最好使用全扫描。一般超过10%的数据要读取就会选择全表扫描。

访问数据有两种基本的数据访问途径：全表扫描和索引扫描。索引扫描每次有IO操作，一次是对索引块，一次是对数据块。

1.3 数据库索引使用的场合

是否使用索引，由Oracle优化器决定。Oracle的优化器有3种：RULE（基于规则）优化器、COST（基于成本）优化器、CHOOSE（选择性）优化器。缺省情况下，Oracle采用CHOOSE优化器，因为该优化器默认选择的是全表扫描，所以应该尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

1.4 索引的种类

根据数据存储方式的类型，可分为B-Tree索引、反向索引、位图索引3类。

根据创建索引所在列的数量，可分为单列索引和组合索引，组合索引又称为复合索引。单列索引：基于单个列创建的B-Tree索引，是Oracle默认的索引类型。复合索引：基于两列或多列创建的索引，就是复合索引。复合索引引导列的选择：（1）前缀性，条件中最常使用到的字段作引导列；（2）选择性，选择性高的字段作引导列。

2 Oracle组合索引的创建

建立组合索引需要遵循的原则：（1）引导列要选择过滤条件的列作为引导列，比如where c.yyy='yyy'或者c.yyy〉或者c.yyy〈。引导列的选择性越高越好，因为选择性越高，扫描的leaf block就越少，效率就越高。（2）尽量把join列放到组合索引最后面，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列时也要排序。

3 Oracle扫描的优化

3.1 使用索引扫描

索引扫描，因为有了索引这个有序的数据结构作支持，只访问需要的索引块和数据块，而不是访问整张表，所以会比全表扫描的效率高。

例如，select count（*）from table1，主键索引扫描，要优于全表扫描。因为主键索引中，已经包含所有记录的主键。

下列3种情况，会使用索引扫描。

（1）没有谓语，但select列表中，其中一列有索引。

（2）谓语中包含一个位于索引中非引导列中的条件。

（3）数据可以通过一个排过序的索引来获取并且会省略单独的排序步骤。

3.2 使用全表扫描

试想，对一张拥有百万级数据的表进行全表扫描，其扫描的性能会非常差。那么哪些情况适合使用全表扫描呢？下列3种情况，会使用全表扫描。

（1）表包含的数据行很小，使用索引占用的存储空间比表占用的空间反而还要大。

（2）访问的数据占全表数据的百分比大（一般超过70%），通过索引访问的总成本大于全表扫描的成本。

（3）表包含的数据未进行排序。

3.3 会引起全表扫描的几种SQL查询语句

3.3.1 SQL中使用了模糊查询

原因：like由于是模糊查询，效率比较低，查询条件应该尽量避免使用like；对于全模糊查找，形如like‘%...%’，是无法使用索引的。另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。

解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like‘…%’，是会使用索引的；左模糊like‘%...’无法直接使用索引，但可以变化成like‘…%’，则可使用索引。

3.3.2 SQL中使用了空值判断

原因：在数据库中，如果不知道某行记录的某列具体数据，通常会使用NULL来表示，NULL是空值的含义。查询条件中含有is null的select语句执行慢，会导致数据库引擎放弃使用索引而进行全表扫描。

解决方法：可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0。

3.3.3 SQL中使用了不等于操作符

原因：SQL中，不等于操作符会限制索引，引发全表扫描。在索引使用过程中，被索引的表数据存储在索引的叶结点中，索引只能查找索引中存在的数据。如果where条件中引用了不等于运算符（〈〉，!=），即使比较的字段上有索引，索引也无法发挥作用。

解决方法：把不等于操作符改成or，可以使用索引，避免全表扫描。例如，把columnA〈〉’bbb’，改写成columnA〈’bbb’or columnA〉’bbb’，就可以使用索引了。

3.3.4 or语句使用不当

or语句使用不当会引起全表扫描。原因：where子句中比较的两个条件，一个条件有索引，另外一个条件没索引，使用or连接两个条件则会引起全表扫描。

4 Oracle索引使用的注意点

4.1 预防索引陷阱

一张表中如果有上百万条数据，对某个字段加了索引，但是查询性能并没有提高，可能就是Oracle索引失效造成的。Oracle索引的目标是避免全表扫描，提高查询效率，但有些时候却适得其反，SQL优化会触发索引陷阱，造成索引失效。

在Oracle创建索引的实际操作中会出现的限制条件，如果在数据查询中违反了索引限制条件，那么添加的索引不会执行，Oracle仍然会自动选择执行全表扫描，反而可能由于数据库维护索引的系统开销造成查询性能更差。

4.2 索引创建策略

索引创建策略主要包含5条建议：对于取值范围很小的字段（比如性别字段）应当建立位图索引；为索引设置合适的PCTFREE值，即指定数据块中必需保留的最小空间的比例；存储索引的表空间最好单独设定导入数据后再创建索引；不需要为很小的表创建索引；限制表中的索引的数目。

[1]邓小培，孙洪涛.Oracle的索引[J].科技信息，2010（13）：66.

[2]李素奇.关于SQL索引建立规则与优化的探讨[J].科技展望，2014（19）：1-2.

[3]曾明霏，刘强.基于SQL和表设计的Oracle数据库开发审计研究[J].软件导刊，2016（12）：136-138.

[4]杭聪，黄连月，黄鑫.数据库SQL审查与性能优化技术研究与应用[J].电力信息与通信技术，2016（4）：146-153.

[5]李永亮.Oracle数据库中数据访问优化方法[J].科技视界，2015（15）：66.

Application of Oracle index in data query

Xia Zhongqiu, Yu Guohong
（Suzhou Chien-shiung Institute of Technology, Suzhou 215411, China）

Taking the data scanning of Oracle database in the practical production as the main research content, combining with the index technology and other related knowledge, this paper puts forward the rational use of indexing technology to avoid the overall design idea of performing full table scans, through the actual demand analysis, introduces the SQL statement optimization method based on index to realize the efficient query of database.

index; index scan; Oracle

2015年江苏省现代教育技术课题；项目名称：高职移动交互式数字教材的开发与应用研究；项目编号：2015-R-26864。

夏忠球（1964— ），男，江苏太仓人，工程师，学士；研究方向：网络安全，数据库技术及应用。