复合索引的先决使用条件

上一篇 / 下一篇  2016-05-03 15:15:34 / 个人分类:性能诊断调优

背景:
.x6M-K,XU)KX SNu0今天,接到一个项目的项目经理电话,告之说生产环境有几个查询超级慢,就是查询单张表的数据,查询条件也很简单,但是加了索引以后并没有走索引,依然还是走的全表扫描。
{5S{K U*|g[N0听到该问题描述,我开始浮想联翩,统计信息太旧?存在隐式转换?索引树倾斜度太高,导致oracle认为走索引的成本更高?51Testing软件测试网t z Pg)JP4H dZ
带着各种可能的原因猜想,火速赶到了现场,发现原来都是我想多了。不走索引单纯是建立的索引不合理,查询条件是多个字段,应该建立复合索引,现场维护人员只对其中单个字段建立了索引,ORACLE认为不如走全表扫描开销小,所以没走索引。
NyWX3`-HD-j0汗~~~~~~51Testing软件测试网;SX I#J)t*T!B)d*Ek
添加索引的时候发现,几个不同的查询,查询条件字段都一样,但是写的顺序却不一样(开发写SQL太随意了!!!!字段一样,顺序也写成一样啊!!!!!!!),结果先剧透一下,同样也是可以走索引的。而由此联想到些问题,于是在解决完效率问题后,在个人环境上做了一个验证。51Testing软件测试网*@;swwTk&H*Z%N7@$Z
这个就是完整的背景。
0cN4m pe {;|0
Me-?$W/?k_m u0---------------------------------------------------
+r3N'qT1xh0验证内容:
yT5d2tg;IO-_S0ORACLE 11GR2 复合索引的使用条件。
evabWM0前提条件:51Testing软件测试网Y d-i1s3HH
创建一张表,并对字段A、B建立组合索引,顺序为index(A、B);51Testing软件测试网 KF ah9[N^
测试场景:51Testing软件测试网Q#d-UO i
针对以下6个场景进行测试:
6d;a0z ~Gm`0
1、查询条件为:A='XXX' and B='YYY';  顺序完全一致的情况;51Testing软件测试网!M*mx2b|dk I
2、查询条件为:B='YYY' and A='XXX';  顺序不一致的情况;51Testing软件测试网(Z/R1e3kQ(XO:UZ}
3、查询条件为:A='XXX';              单个字段且为复合索引前导列的情况;
&D D#W5K&D;e'w04、查询条件为:B='YYY';              单个字段且不是复合索引前导列的情况;51Testing软件测试网P$^,Wa0i(|0S aVUNg1Q
5、查询条件为:C='ZZZ' and A='XXX';  查询条件既包含其它字段,也包含复合索引前导列的情况;51Testing软件测试网JR,K1lv!mvXE6J'dt8Y
6、查询条件为:C='ZZZ' and B='YYY';  查询条件既包含其它字段,也包含复合索引非前导列字段的情况;51Testing软件测试网_DUs$eX.b7F
7、查询条件为:C='ZZZ' and A='XXX' and B='YYY';  查询条件除复核索引字段外还包括其它字段,且索引外字段在第一位;
(Ce4m&Y C;N%z0
8i-A0O9{d0---------------------------------------------------51Testing软件测试网MT4F;U]2XP%p9r
执行过程:51Testing软件测试网)O1j:G&L{ L
51Testing软件测试网+[j"[:O%O/Y'b:Sr
创建测试数据:
*{(T+x4ax V}^T0create table test_index_demo(recid  RAW(16) not null,customer_id RAW(16) not null,product_id  RAW(16) not null)51Testing软件测试网4x9_KIW3dz
create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)51Testing软件测试网3| k3{;bS7{Uo N
根据查询字段做笛卡尔积准备了将近3000万数据;
3I(D9nq1eH }4rn0exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');
3F.D-\3t1w051Testing软件测试网@WM D6B%c C
分别针对每个场景进行测试,查看执行计划如下:51Testing软件测试网/}Y6fxp1a-|D
场景一:
;l4}8K}L+y@s0select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
NK3A@v)kcJ @Zu0
1?Q\*lY4[v0此处是走索引的,符合预期;
1x,kBj Fu051Testing软件测试网&pat8etog%wg
场景二:51Testing软件测试网[i(wMnD%j
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');51Testing软件测试网!P$G%?0C'^wFS
51Testing软件测试网a$x*G(ZB5mX#dl
此处可以看到ORACLE的优化器实际上把查询条件的顺序进行了调整,所以同样走了索引,符合预期;51Testing软件测试网&b:` i'k CKFu3{ c

s+y J v9|y8@0场景三:
4X$DNk a6vn0select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
;q-V)N-In-L3S051Testing软件测试网.f!Cz/d~@\v
此处因为查询条件为复合索引的前导列,所以走了索引,符合预期;
,p{Ui"Y&n U9It`051Testing软件测试网 @rw"i W5s:zP F
场景四:
"xT%f6B z+_,X0select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
NO0Xm |H*T-F0
5aQ E&{2i3\ Z _3f1qH0此处没有走索引,走的全表扫描,我原本以为即便不是复合索引的前导列,也会走索引的,看来我原来的认识是错误的。
O(sY#eFo%ZQ0针对这个我特意临时添加了index(B、A)验证了一下,是因为查询条件字段不是复合索引前导列导致,还是因为出于其它方面的成本考虑,测试发现,添加了index(B、A)顺序的索引后,该SQL就可以走索引了,所以应该可以认为是查询条件字段不是复合索引前导列导致的。51Testing软件测试网:Q/@yC~-bb

8~W/I4e7pR;c `0场景五:51Testing软件测试网\ iHvM
select * from TEST_INDEX_DEMO T WHERE
[]~"~@'B0T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
|)nQ#p n Z9~:t q051Testing软件测试网 f@a'u{.n
ORACLE优化器并没有调整查询条件字段的顺序,但是依然走了索引。走索引是符合预期的,但是我原本以后应该会改变字段顺序的,这块我有点不太理解,如果有看到这篇日志的大牛请帮忙解惑一下,谢谢。51Testing软件测试网"CDu'E$G#~(JEC

v,hWL!YEoM0场景六:
OD;H3La%G'Sk0select * from TEST_INDEX_DEMO T WHERE
b&A7I)]SCS0T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网r!Z)~"m-a w:v5@T

EPSv9~EE%O0此处没有走索引,倒是符合预期,汗~~~~
Nj"D!o9i*JH \051Testing软件测试网-NP XC&x8~
场景七:51Testing软件测试网f&@KGyc&^
select * from TEST_INDEX_DEMO T WHERE
P}3ggm O-r0T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
2|9J'}5FV c]:X1Q W+S051Testing软件测试网Xj1h@0Eo
此处走索引了,结果倒是符合预期。
-XGe X(EPj]051Testing软件测试网;p)\ap?ijO
----------------------------------------------------51Testing软件测试网?;h4f2y1|{
结果对比及结论:
0t5L MAXl3y0
所以经此对比,我的结论是:
7VYW1tNzRHE[01、查询条件字段与复合索引字段一致的,无论顺序如何,ORACLE优化器会自动调整顺序,结论是会走索引;51Testing软件测试网 H,FZ\ J
2、查询条件字段与复合索引字段不一致,查询条件字段包含复合索引前导列的,可以走索引;不包含索引前导列,则不走索引;51Testing软件测试网A4Z#}:_(c;OZ
--------------------------
'F#cB"qy0所以,由此结论可以看出,在设计查询的时候,还是应该要求开发在组织SQL的时候对于第一个查询条件该用哪个字段还是需要综合考虑系统所有查询来进行设计一下的。51Testing软件测试网0k'id#s9j

TAG: 性能测试 索引 Oracle ORACLE 优化器 调优

 

评分:0

我来说两句

日历

« 2023-01-15  
1234567
891011121314
15161718192021
22232425262728
293031    

数据统计

  • 访问量: 29559
  • 日志数: 27
  • 建立时间: 2016-04-25
  • 更新时间: 2016-05-10

RSS订阅

Open Toolbar