复合索引是否会被使用的条件

stacktestor · 发表于 2016-5-3 16:52:37

本帖最后由 stacktestor 于 2016-5-3 17:44 编辑

背景：
今天，接到一个项目的项目经理电话，告之说生产环境有几个查询超级慢，就是查询单张表的数据，查询条件也很简单，但是加了索引以后并没有走索引，依然还是走的全表扫描。
听到该问题描述，我开始浮想联翩，统计信息太旧？存在隐式转换？索引树倾斜度太高，导致oracle认为走索引的成本更高？
带着各种可能的原因猜想，火速赶到了现场，发现原来都是我想多了。不走索引单纯是建立的索引不合理，查询条件是多个字段，应该建立复合索引，现场维护人员只对其中单个字段建立了索引，ORACLE认为不如走全表扫描开销小，所以没走索引。
汗~~~~~~
添加索引的时候发现，几个不同的查询，查询条件字段都一样，但是写的顺序却不一样（开发写SQL太随意了！！！！字段一样，顺序也写成一样啊！！！！！！！），结果先剧透一下，同样也是可以走索引的。而由此联想到些问题，于是在解决完效率问题后，在个人环境上做了一个验证。
这个就是完整的背景。

---------------------------------------------------
验证内容：
ORACLE 11GR2 复合索引的使用条件。
前提条件：
创建一张表，并对字段A、B建立组合索引，顺序为index（A、B）；
测试场景：
针对以下6个场景进行测试：
1、查询条件为：A='XXX' and B='YYY';  顺序完全一致的情况；
2、查询条件为：B='YYY' and A='XXX';  顺序不一致的情况；
3、查询条件为：A='XXX';             单个字段且为复合索引前导列的情况；
4、查询条件为：B='YYY';             单个字段且不是复合索引前导列的情况；
5、查询条件为：C='ZZZ' and A='XXX';  查询条件既包含其它字段，也包含复合索引前导列的情况；
6、查询条件为：C='ZZZ' and B='YYY';  查询条件既包含其它字段，也包含复合索引非前导列字段的情况；
7、查询条件为：C='ZZZ' and A='XXX' and B='YYY';  查询条件除复核索引字段外还包括其它字段,且索引外字段在第一位；

---------------------------------------------------
执行过程：

创建测试数据：
create table test_index_demo(recid  RAW(16) not null,customer_id RAW(16) not null,product_id  RAW(16) not null)
create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)
根据查询字段做笛卡尔积准备了将近3000万数据；
exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');

分别针对每个场景进行测试，查看执行计划如下：
场景一：
select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此处是走索引的，符合预期；

场景二：
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

此处可以看到ORACLE的优化器实际上把查询条件的顺序进行了调整，所以同样走了索引，符合预期；

场景三：
select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

此处因为查询条件为复合索引的前导列，所以走了索引，符合预期；

场景四：
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此处没有走索引，走的全表扫描，我原本以为即便不是复合索引的前导列，也会走索引的，看来我原来的认识是错误的。
针对这个我特意临时添加了index（B、A）验证了一下，是因为查询条件字段不是复合索引前导列导致，还是因为出于其它方面的成本考虑，测试发现，添加了index（B、A）顺序的索引后，该SQL就可以走索引了，所以应该可以认为是查询条件字段不是复合索引前导列导致的。

场景五：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

ORACLE优化器并没有调整查询条件字段的顺序，但是依然走了索引。走索引是符合预期的，但是我原本以后应该会改变字段顺序的，这块我有点不太理解，如果有看到这篇日志的大牛请帮忙解惑一下，谢谢。

场景六：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此处没有走索引，倒是符合预期，汗~~~~

场景七：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此处走索引了，结果倒是符合预期。

----------------------------------------------------
结果对比及结论：
所以经此对比，我的结论是：
1、查询条件字段与复合索引字段一致的，无论顺序如何，ORACLE优化器会自动调整顺序，结论是会走索引；
2、查询条件字段与复合索引字段不一致，查询条件字段包含复合索引前导列的，可以走索引；不包含索引前导列，则不走索引；
--------------------------
所以，由此结论可以看出，在设计查询的时候，还是应该要求开发在组织SQL的时候对于第一个查询条件该用哪个字段还是需要综合考虑系统所有查询来进行设计一下的。