合理利用索引

SQL 什么条件会使用索引？

当字段上建有索引时，通常以下情况会使用索引：

INDEX_COLUMN = ? ( 或者 > 、 >= 、 < 、 <=)

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%' （后导模糊查询）

T1. INDEX_COLUMN=T2. COLUMN1 （两个表通过索引字段关联）

SQL 什么条件不会使用索引？

1. 经过普通运算或函数运算后的索引字段不能使用索引，如要使用可以使用函数索引。

2. 使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引。

3. 含前导模糊查询的 Like 语法不能使用索引。

4. 系统在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。

索引使用经验总结如下 :

1.   避免对查询的列的操作

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。如 :

select * from record where substr ( cardno , 1 , 4 ) = '5378'

select * from student where sid * 2 < 4

由于 where 子句中对列的任何操作结果都是在 SQL 运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表扫描，因此将 SQL 重写如下 :

select * from record where cardno like '5378%'

select * from student where sid < 4 / 2

2. 避免不必要的类型转换

需要注意的是，尽最避免潜在的数据类型转换。如将字符型数据与数值型数据比较，会自动将字符进行转换，从而导致全表扫描。

3. 尽量不要使用前导模糊查询

由于前导模糊查询 ( 前面有 % 的 like 查询 ) 不能利用索引 , 所以速度会比较慢。如果真要使用前导模糊查询，但速度不能满足要求，可以使用 Lucene 来实现 。

  4. 尽量不要使用<> 、!= 、not in 、

    <> 、not in 、、 not exists 虽然会使用索引，但不能有效使用索引，它会扫描索引列上的每个数据。所以要尽量转换成其他写法，比如<>1 可以转换成>1 and <1 ( 数据库不会这样自动转换来判断是否符合条件) ，这样转换后，在某些情况下可以提高速度。

   注: 在DB2(V8 和V9.7) 上测试，!< 和!> 是可以高效利用索引的，它们会被转换成>= 和=< 。

关于索引的谣传：

网上很多地方说下面几种情况不能使用索引，但经过我在 DB2 上的测试，是可以使用索引的。

1. 尽量避免非操作符（包括 != 、 <> 、 !< 、 !> 、 not in 、 not exists ）的使用。

上面的非操作符都可以利用索引，但 != 、 <> 、 not in 、 not exists 不能有效利用索引，所以需要将其转换成其它写法。而 !< 、 !> 是可以有效利用索引的。

2. 尽量去掉 IN 、 OR

经过在DB2 上的测试，如果or 的左右两边的列都有索引，则可以利用索引，否则（只有一边的列有索引或者两边都没有索引）不能利用索引。

3. 去掉 Where 子句中的 IS NULL 和 IS NOT NULL

网上有些地方说 B-TREE 索引里不保存字段为 NULL 值记录，因此 IS NULL 不能使用索引。经过在DB2 上的测试，这种说法是错误的。如果null 在整个列所占比例很小，DB2 会先利用索引找到不需要读取出来的数据排除它们，然后读取剩下的数据。同样,is not null 也是会利用索引的。我们需要排除空数据时，用is not null 或者 列名 >= min and 列名 <= max , 效率是一样的。