转载

使用组合索引的注意事项

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

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

正文到此结束

所属分类：编程技术

本文标签： 测试 Select 智能数据 sql 数据库 Oracle tab
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

使用组合索引的注意事项

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

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

索引合并的简单说明：

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流