复合索引的先决使用条件

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

背景:51Testing软件测试网5Jo7pcI-rI
今天,接到一个项目的项目经理电话,告之说生产环境有几个查询超级慢,就是查询单张表的数据,查询条件也很简单,但是加了索引以后并没有走索引,依然还是走的全表扫描。51Testing软件测试网fbv0u#G
听到该问题描述,我开始浮想联翩,统计信息太旧?存在隐式转换?索引树倾斜度太高,导致oracle认为走索引的成本更高?51Testing软件测试网8]R6[eo
带着各种可能的原因猜想,火速赶到了现场,发现原来都是我想多了。不走索引单纯是建立的索引不合理,查询条件是多个字段,应该建立复合索引,现场维护人员只对其中单个字段建立了索引,ORACLE认为不如走全表扫描开销小,所以没走索引。51Testing软件测试网B9|c yJ-f
汗~~~~~~
\lp[cSi$M0添加索引的时候发现,几个不同的查询,查询条件字段都一样,但是写的顺序却不一样(开发写SQL太随意了!!!!字段一样,顺序也写成一样啊!!!!!!!),结果先剧透一下,同样也是可以走索引的。而由此联想到些问题,于是在解决完效率问题后,在个人环境上做了一个验证。
s%P0r\U4J;nm3I0这个就是完整的背景。51Testing软件测试网C @*_R;X"g.q!z0h%BMt2U

l~&}|2\(N*Hk"s"M0---------------------------------------------------
}S-Kb%R},WT QGc&m0验证内容:
g Pi,]'G m0ORACLE 11GR2 复合索引的使用条件。51Testing软件测试网E|)D\!J.A*|qb
前提条件:
v8\R _%yq0创建一张表,并对字段A、B建立组合索引,顺序为index(A、B);
,yc$N.P"WDwG0测试场景:
j!R4[Qj JLN)qRZ0
针对以下6个场景进行测试:
QOy)Tu*c1^0
1、查询条件为:A='XXX' and B='YYY';  顺序完全一致的情况;
CH(^p6k!s1Z0~02、查询条件为:B='YYY' and A='XXX';  顺序不一致的情况;
4E8{8h,bE$rl"|!Y03、查询条件为:A='XXX';              单个字段且为复合索引前导列的情况;51Testing软件测试网 S&K-md&A*ry
4、查询条件为:B='YYY';              单个字段且不是复合索引前导列的情况;
j)d,A4E)Qb*W Fs05、查询条件为:C='ZZZ' and A='XXX';  查询条件既包含其它字段,也包含复合索引前导列的情况;
:q_ wC7x-s06、查询条件为:C='ZZZ' and B='YYY';  查询条件既包含其它字段,也包含复合索引非前导列字段的情况;
.FX T1X F.e:q07、查询条件为:C='ZZZ' and A='XXX' and B='YYY';  查询条件除复核索引字段外还包括其它字段,且索引外字段在第一位;51Testing软件测试网No Y+EX\p sw

cy!F~@^r0---------------------------------------------------51Testing软件测试网p2[Z p$q:Oj
执行过程:
'~@srT,Qs6z+lM0
iG`m#Kw)hjv0创建测试数据:51Testing软件测试网8n)D~bl
create table test_index_demo(recid  RAW(16) not null,customer_id RAW(16) not null,product_id  RAW(16) not null)
(^6I3z]~'R#? W3I~v es0create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)
6W)k N!G2@-T+X i'z0根据查询字段做笛卡尔积准备了将近3000万数据;
's n%Ffj#xK5{3lg0exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');51Testing软件测试网O Mc(f'd.Z
51Testing软件测试网a?1j czh&^
分别针对每个场景进行测试,查看执行计划如下:51Testing软件测试网M"}X^.j$_r%X
场景一:
"ue?Fj x\0select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网-y^]&m7q @J

J$O*_1@w:U&w0此处是走索引的,符合预期;
2O o(Je&ADk O051Testing软件测试网(rU)OFz$x
场景二:51Testing软件测试网2Q%yJbpGC)kimM
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
xlhVJ0
d_ F+T kP0此处可以看到ORACLE的优化器实际上把查询条件的顺序进行了调整,所以同样走了索引,符合预期;
f(D,R$g%j ?0
9[+Y+v(iU0场景三:
km)HSb$p{Hv0select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');51Testing软件测试网4OA5mq(yB r:lt d

'@6X{.DN(V'T(}4Hg0此处因为查询条件为复合索引的前导列,所以走了索引,符合预期;
k"e({4V l5l&~0
-Wr5D/GcJ'm0场景四:
i o+IE`Kt0select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网AXU o$E

;V4e r4P!f~0此处没有走索引,走的全表扫描,我原本以为即便不是复合索引的前导列,也会走索引的,看来我原来的认识是错误的。51Testing软件测试网{'r rb k0Ft6u
针对这个我特意临时添加了index(B、A)验证了一下,是因为查询条件字段不是复合索引前导列导致,还是因为出于其它方面的成本考虑,测试发现,添加了index(B、A)顺序的索引后,该SQL就可以走索引了,所以应该可以认为是查询条件字段不是复合索引前导列导致的。51Testing软件测试网]M4w:_B,j ~7r]$C
51Testing软件测试网+oP0`[T9~Z!]C
场景五:51Testing软件测试网'~s7S^a.l'u H}
select * from TEST_INDEX_DEMO T WHERE51Testing软件测试网eE3^0hI
T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
'u/|#m%L F ? s"F+u051Testing软件测试网.t2g1b Jg8g%E!v
ORACLE优化器并没有调整查询条件字段的顺序,但是依然走了索引。走索引是符合预期的,但是我原本以后应该会改变字段顺序的,这块我有点不太理解,如果有看到这篇日志的大牛请帮忙解惑一下,谢谢。
UE/~3N![&J/r.R051Testing软件测试网 ?)C W6A7pm!w*S+g*Cr;L
场景六:
hcH5O KrJAF0select * from TEST_INDEX_DEMO T WHERE51Testing软件测试网&c l o;Uj.xI
T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
*\o\8g"B'G;PO0
$xo;yz1M&_0此处没有走索引,倒是符合预期,汗~~~~51Testing软件测试网dw1k!W[7U7C(v

-~6U;E*mc"z1WjZZ0场景七:
,m R.Y[5ed-[+~L0select * from TEST_INDEX_DEMO T WHERE
?5|"bdL%y9@"`d0T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网C"DlJuP/M
51Testing软件测试网b?#x2o)W^0B({ nB
此处走索引了,结果倒是符合预期。51Testing软件测试网\ P8GTM4_ dIe

7oQh EL!y?#Y;Q0----------------------------------------------------51Testing软件测试网/| }*M;gy7g X
结果对比及结论:
!IRCY9qD6i0
所以经此对比,我的结论是:
c%@b,pb2R01、查询条件字段与复合索引字段一致的,无论顺序如何,ORACLE优化器会自动调整顺序,结论是会走索引;51Testing软件测试网nq+{~`!vz h6[.bd
2、查询条件字段与复合索引字段不一致,查询条件字段包含复合索引前导列的,可以走索引;不包含索引前导列,则不走索引;51Testing软件测试网5kO,c5hp6W
--------------------------
1~nLbB dBR0所以,由此结论可以看出,在设计查询的时候,还是应该要求开发在组织SQL的时候对于第一个查询条件该用哪个字段还是需要综合考虑系统所有查询来进行设计一下的。51Testing软件测试网lV*R(ql&J

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

 

评分:0

我来说两句

日历

« 2024-02-19  
    123
45678910
11121314151617
18192021222324
2526272829  

数据统计

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

RSS订阅

Open Toolbar