使用索引的注意事项及常见场景、案例

      索引的原理与作用，各种书籍和网络上的介绍可以说是铺天盖地，基本上主流数据库系统的也都是一致的。选择索引字段的原则，比如外键字段、数据类型较小的字段、经常用于查询或排序的字段、表关联的字段等等，在此不做赘述。本人在工作中见到过很多人创建的索引，回想自己以前也会有理论知识空洞的体会，总感觉理论知识无法与具体的工作问题相匹配。在此仅以工作学习中积累的一点经验和问题场景整理以飨读者。先把常见的注意事项整理如下：

索引应该建在选择性高的字段上（键值唯一的记录数/总记录条数），选择性越高索引的效果越好、价值越大，唯一索引的选择性最高； 组合索引中字段的顺序，选择性越高的字段排在最前面； where条件中包含两个选择性高的字段时，可以考虑分别创建索引，引擎会同时使用两个索引（在OR条件下，应该说必须分开建索引）； 不要重复创建彼此有包含关系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)； 组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引；

      最后需要提醒的是，不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销，对于插入、删除、更新操作也会增加处理上的开销，而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

举下面一个场景的例子，创建这样的索引是有效的吗？

再比如，该表最常使用的SQL场景有以下两种类型，应该如何创建索引？

      以执行计划和逻辑IO的统计数据显示，两个场景的测试结果都是后者索引有明显的效果，大家有兴趣可以自己测试验证一下。当然，生产环境远比这些要复杂，各表的数据量及数据分布情况也会影响引擎的执行方式，引擎对索引选择与要求也会不一样，此处仅以简单语句做示例进行说明。

组合索引查询的各种场景：

组合索引 Index (A, B, C)

下面条件可以用上该组合索引查询：

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B=6 AND C IN (2, 3)

下面条件将不能用上组合索引查询：

B>5                                           ——查询条件不包含组合索引首列字段

B=6 AND C=7                            ——理由同上

下面条件将能用上部分组合索引查询：

A>5 AND B=2                            ——当范围查询使用第一列，查询条件仅仅能使用第一列

A=5 AND B>6 AND C=2             ——范围查询使用第二列，查询条件仅仅能使用前二列

A=5 AND B IN (2, 3) AND C=2   ——理由同上

组合索引排序的各种场景：

组合索引 Index(A, B)

下面条件可以用上组合索引排序：

ORDER BY A                   ——首列排序

A=5 ORDER BY B            ——第一列过滤后第二列排序

ORDER BY A DESC, B DESC      ——注意，此时两列以相同顺序排序

A>5 ORDER BY A            ——数据检索和排序都在第一列

下面条件不能用上组合索引排序：

ORDER BY B                   ——排序在索引的第二列

A>5 ORDER BY B            ——范围查询在第一列，排序在第二列

A IN(1,2) ORDER BY B    ——理由同上

ORDER BY A ASC, B DESC       ——注意，此时两列以不同顺序排序

索引合并的简单说明：

数据库能同时使用多个索引

SELECT * FROM TB WHERE A=5 AND B=6

能分别使用索引(A) 和 (B)；

对于这个语句来说，创建组合索引(A,B) 更好；

最终是采用组合索引，还是两个单列索引？主要取决于应用系统中是否存在这类语句：SELECT * FROM TB WHERE B=6

SELECT * FROM TB WHERE A=5 OR B=6

组合索引(A, B)不能用于此查询（目前的数据库也很智能，部分OR条件也能够使用组合索引，但效果不是很稳定）；

很明显，分别创建索引(A) 和 (B)会更好；

删除无效的冗余索引

TB表有两个索引(A, B) 和 (A)，对应两种SQL语句：SELECT * FROM TB WHERE A=5 AND B=6 和 SELECT * FROM TB WHERE A=5

执行时，并不是WHERE A=5 就用 (A)； WHERE A=5 AND B=6  就用 (A, B)；

其查询优化器会使用其中一个以前常用索引，要么都用(A, B)， 要么都用 (A)。

所以应该删除索引(A)，它已经被(A, B)包含了，没有任何存在的必要。

附1，查询指定表的数据量与索引定义情况：

附2，借助性能视图，查询数据表的SQL访问方式

附3，索引重建示例

补充：

      Where条件中Or的两组条件如果分别落在两个数据表上，即使对应的字段都已创建索引，引擎也是无法使用索引的。如下SQL，此语句实际上仅返回一条数据，但对于TRFKZL和TRHBZL来说，Oracle、SqlServer都是进行全表扫描。

按照建议更改SQL写法，走索引查找，响应时间在1秒以内。当然，从原始语句的筛选条件也能够感觉到怪怪的，根本上来讲应该是个设计问题。