转载

使用组合索引的注意事项

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

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

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

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

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

select  * from    t1, t2 where   t1.col_1 = t2.ab and t1.col_2 in (12, 38);  -- 创建索引如下 create index idx_t1_query on t1(col_1, col_2);
-- 或者仅创建索引如下
create index idx_t1_col2 on t1(col_2);

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

select  * from    t1 where   t1.PartId = 'xxxx' and t1.STATE = 2 and t1.PROCID = 'yyyy'  select  * from    t1 where   (t.PartId = 'xxxx' or t1.ActualPartId = 'xxxx' ) and t1.STATE = 2 and t1.PROCID = 'yyyy'   -- 创建一个“全覆盖的索引”,把查询条件都包含的索引 create index idx_t1_query on t1(partId, actualpartId, state, procid);  -- 还是分开创建如下两个索引 create index idx_t1_PartId on t1(partId, state, procid) create index idx_t1_actualPartId on t1(actualpartId, state, procid)

以执行计划和逻辑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)包含了,没有任何存在的必要。

附,查询指定数据表的索引定义情况:

--Sqlserver: sp_helpindex 'tableName' --或者 select  t2.name tabName, t3.name indName, t4.name colName, t1.* from sys.index_columns t1  join sys.tables t2 on t1.object_id = t2.object_id  join sys.indexes t3 on t2.object_id = t3.object_id and t1.index_id = t3.index_id  join sys.columns t4 on t2.object_id = t4.object_id and t1.column_id = t4.column_id where t2.name = 'tableName' order by t3.name, t1.index_column_id  
--Oracle: select  *  from    user_ind_columns a  where   a.TABLE_NAME = upper('tableName')  order by a.INDEX_NAME, a.COLUMN_POSITION;
正文到此结束
Loading...