浅析索引在SQL语句中的使用技巧

引 言

在DBMS 中，索引是访问数据库中数据的重要手段，特别是在大型系统中，更是必备方法。如果说数据库是一本字典， 其中的数据是字典的内容，那么，索引就是这本字典的目录。查字典可以使用偏旁部首、汉语拼音、笔画等不同的方法进行查找，查询数据库中的数据也有各种各样的索引可供使用。在数据库中，索引使用DML 操作可以迅速找到表数据，不用对整张表逐行访问。索引是一种结构很强的方案对象，它将数据和存放数据的位置对应起来。对于数据量非常大的表来说，通过全表扫描来访问和通过索引来访问，速度可能有是数量级上的差别。

索引能通过事先保存的索引键，按照一定顺序记录数据的位置，由此替代 DML 操作原本要进行的全表扫描，通过“以空间换时间”的方法，牺牲少量的存储空间，换取快速的反应时间。在一张表上是否该建立索引、建立怎样的索引、怎样才能有效利用建立起的索引是数据库优化中最常见的问题。

1 SQL语句的执行过程

是否会使用索引要从Oracle 执行查询的机制开始分析。在Oracle 进行必要的语法检查和语义分析之后，会自动对语句进行优化，其目的就是找到最高效的运行路径。可能会使用的优化器有两种 ：RBO 基于规则的优化器，CBO 基于成本的优化器。默认情况下是 CBO 优化器，它会快速统计数据量的大小，选择开销最小（尽量消耗最少的 CPU 和I/O）的执行计划。确定了执行计划之后，Oracle 会将SQL 语句格式化为内部执行代码。

由此可见，想要提高SQL 语句的性能，就要从优化器会选择怎样的执行计划这块考虑。如果表上没有建立索引，执行计划就是全表扫描，它会根据PGA 中的系统设定，读取一批数据块，如果表数据较小，全表可以一次性全部读入 ；如果数据量很大，则需要多次读取数据块。如果在数据量稍大的表上建立了索引，优化器会比较利用索引和全表扫描的读取数据次数，如果利用索引代价小，则选择利用索引，否则还是选择全表扫描。一般来说，查询语句要读取 10% 以上的数据量的话，优化器就宁愿选择全表扫描，而不是索引。因为一个索引项指向了一个数据块，所要读取的数据块太多，就有大量的I/O 操作要进行，反而降低性能。

2 索引的分类

建立怎样的索引。常见的索引有以下几种 ：B* 索引（包含唯一索引），位图索引，函数索引，本地前缀分区索引，全局范围分区索引等。

(1) B*树索引

B* 树索引就是利用建立B 树，将索引列和指向表中各行的 rowid 组织起来，按照顺序建立一颗平衡树，根据索引列的数量，自动建立合理高度的B 树。在B 树索引中，所有叶子的深度一样，结构自动保持平衡，在增删改之后都由Oracle 自动维护。更重要的是，B 树索引可以适应多种查询条件，包含范围查找 >、<、>=、<=、like 和完全匹配。由于树的高度是基于索引列的数量，当访问的数据量占总数据的 10% 以上的时候，需要访问 B 树的范围太大，这个时候，其优势无法体现。

(2) 位图索引

当某一列上的值可选择性较低的时候，一般使用位图索引。所谓的“可选择性”就是指查询这一列的 distinct 值与这一列所有值的比例，这个比值越高，说明可选择性越高。对于“性别”列，只存在两种值，可选择性低 ；“姓名”列，可选择性一般较高。

(3) 函数索引

通过在某一列上利用函数来创建索引。例如 ：createINDEX idx_func_sub on emp substr（empno，1，2）；就是利用substr 函数，在empno上建立索引。

3 使用索引的方法和技巧

(1) 对于索引的建立，我们一般选择在可选择性高的列上建立索引。以地税系统的一张登记表为例，这张表记录了纳税人的登记信息，一个纳税人识别号对应一条记录，纳税人所属的区县对应字段dwid。在识别号 sbh和区县 dwid上分别建立索引IDX_SWJ_SBH和IDX_SWJ_dwid。如果有查询语句：

select * from glfw_swdj_tempwyp where sbh=’12345’ and dwid=’54321’；

查看执行计划如图 1所示， 执行计划没有使用IDX_ SWJ_dwid，因为sbh列的可选性要远高于dwid字段。

图1在SBH和DWID上建立索引后的执行计划

(2) 如果查询条件中包含多列，则在多列上建立复合索引效果更好。还是以上面的 SQL语句为例，glfw_swdj_ tempwyp 表上，建立基于sbh和dwid 的复合索引

create index IDX_SWJ_SBH_DWID on GLFW_SWDJ_ TEMPWYP（SBH，DWID）；

这个时候，新的执行计划如图 2 所示。此时，利用条件语句中的两列复合索引，显然要比单一索引效率高。

图 2建立符合索引的执行计划

(3) 在建立复合索引的时候需要注意，复合索引的列的顺序很重要，最好依据各列的可选择性，由高到低排序。这样，通过B索引树进行访问的时候，由复合索引的第一列就能过滤掉更多的数据，使得I/O地读取更小。有些观点认为， 如果在 SQL语句中没有使用复合索引的前导列，则查询不会利用这个复合索引。这种说法是错误的。Oracle对于索引的访问分为索引唯一扫描（indexuniquescan）、跳跃式索引扫描（index skip scan）、索引范围扫描（index range scan）、索引全扫描（index full scan）等多种方式。就算条件语句中没有利用前导列，跳跃式索引扫描（index skip scan）依然起效。这种访问方式，通过将前导列分为各个不同的区域，在各区域内部使用复合索引的剩余部分来访问数据，最后将各个区域内符合条件的数据做union 操作，得出结果集。

(4) 对于多表连接时，需要建立复合索引的情况下，首先要判断驱动表和被驱动表。驱动表就是查询范围较少的表， 以它作为嵌套连接 nested loops 的外层循环，被驱动表作为内存循环。执行时，从驱动表中选取一个结果，与被驱动表匹配， 匹配上的就并入结果集，再选取驱动表的下一个结果，依次往后。这个时候，可以在被驱动表的连接字段和该表的其它约束条件上建立复合索引，这样，就能提高内层循环的效率。以地税局的应征表为例，当应征表（数据量很大）与税种代码表（数据量很小）发生关联时，税种代码表 dm_shuizhong_ wyp 是驱动表，在应征表上建立关联字段（szdm）和其它约束字段（szpq）的联合主键，还在应征表上建立 szdm 的单一主键，查看执行计划，发现优化器选择的是复合索引，如图 3 所示。

图 3 在表连接的连接字段上建立复合索引

对于建立的索引，在编写SQL 的时候要注意条件语句的写法，以免索引不被使用：

（1）在 where 条件中对索引列使用了函数，导致不能使用索引。

例如 在 应征 表 上 对 glbm 建 立 索引 INX_YZTEST_ GLBM，比较使用substr 函数的区别。

使用函数后如图 4 所示。

图 4 在索引字段上使用了函数时的执行计划

没有使用函数的如图 5 所示。

图 5 没有在索引字段上使用函数的执行计划

很明显，对索引所在的列使用函数，会导致索引无法使用。引起索引失效的函数还有decode、instr 等。索引如果真的要在条件语句中添加某些函数操作，可以在等号的另一边使用带“%”的模糊匹配，如图 6 所示。当然，由此也可以看见，使用like 的模糊匹配对索引的利用并没有完全匹配，原因也是显然的，查询的范围更大，匹配的结果更多。

图 6 在等号的另一边使用模糊匹配

但是，模糊匹配的时候“，%”不要加在匹配字符串的开始，否则索引仍然不起效，如图 7 所示。

图 7 将“%”放在模糊匹配的前端，无法利用索引

这是因为，模糊匹配过程中，索引会按照顺序采取“部分” 匹配原则 ：先选择首字符为“1”的进行匹配，再在结果中选择第二个字符为“2”的继续匹配……但是如果模糊匹配一开始就要用“%”，则相当在最开始，所有结果都匹配上了，这时候索引就“罢工”了。

（2）在写SQL 语句的时候，要注意所比较的是字符串还是数字，例如，glbm 的字段类型是 char，如果在条件语句中将它与 54321 而不是‘54321’比较，则 Oracle 会通过内部转换， 将整个SQL 变为 ：select a.* from sb_yzss_temp_wyp a where TO_NUMBER（a.glbm）>54321，由此，导致在索引列利用了函数，索引失效：

select a.* from sb_ yzss_temp_wyp a where a.glbm

>54321 ；

对于建立在数字类型字段上的索引，在条件语句中不要对数字字段进行算术运算，否则可能导致索引失效，例如在应征表的 nse 这个数字类型字段上建立索引，没有对 nse 进行算术运算时，索引可用：

selecta.*fromsb_yzss_temp_wypawhere a.nse>10000000.00

一旦加上了算术运算，索引就失效了：

select a.* from sb_ yzss_temp_wyp a where a.nse*1>10000000.00

（3）IS NULL 和 IS not NULL 的逻辑比较，也会导致索引失效。

4 结 语

索引不是越多越好，建立索引要遵循以下两点：

(1) 不需要为小表建立索引，这种表还不如采用全表扫描，先读索引再根据索引读数据反而麻烦；

(2) 根据具体的业务需求，只在经常使用的列上添加索引，根据可选性的不同，在可选择性高的列上建立 B树索引（例如员工编号字段），在可选择性较小的列上建立位图索引（例如性别字段），不常使用的列可以不建立索引。