背景:51Testing软件测试网&l6w8K`!U3O+v6f9G3{今天,接到一个项目的项目经理
电话,告之说生产环境有几个查询超级慢,就是查询单张表的数据,查询条件也很简单,但是加了
索引以后并没有走索引,依然还是走的全表扫描。
Lecv:^R9Y0听到该问题描述,我开始浮想联翩,统计信息太旧?存在隐式转换?索引树倾斜度太高,导致
oracle认为走索引的成本更高?
51Testing软件测试网
n1H9IO7E[6`带着各种可能的原因猜想,火速赶到了现场,发现原来都是我想多了。不走索引单纯是建立的索引不合理,查询条件是多个字段,应该建立复合索引,现场维护人员只对其中单个字段建立了索引,ORACLE认为不如走全表扫描开销小,所以没走索引。
M4a,\8P&zK'I0G0汗~~~~~~
+j D;@+V
t\_|5L0添加索引的时候发现,几个不同的查询,查询条件字段都一样,但是写的顺序却不一样(开发写
SQL太随意了!!!!字段一样,顺序也写成一样啊!!!!!!!),结果先剧透一下,同样也是可以走索引的。而由此联想到些问题,于是在解决完效率问题后,在个人环境上做了一个验证。
#M&[;I]pr2J0这个就是完整的背景。
!QE.y{
Maf1Mn0;c`'P H,Fe1C;f;d`0---------------------------------------------------
51Testing软件测试网?6G8|ZY9Uq7N验证内容:b:r-q!H
g#]'JIZVdO0ORACLE 11GR2 复合索引的使用条件。
51Testing软件测试网
R,J0[&]0V前提条件:51Testing软件测试网*k5r8t]MyW创建一张表,并对字段A、B建立组合索引,顺序为index(A、B);
51Testing软件测试网3g X eb^Z,P/U&M测试场景:
Q3_&uS8C0针对以下6个场景进行测试:
*q#S}M3I
C*ceV01、查询条件为:A='XXX' and B='YYY'; 顺序完全一致的情况;
*yR}\,c[!x
G7A02、查询条件为:B='YYY' and A='XXX'; 顺序不一致的情况;
[$Y N;w3Wo$c03、查询条件为:A='XXX'; 单个字段且为复合索引前导列的情况;
51Testing软件测试网}.Pb&cF1?(mZ
Z4、查询条件为:B='YYY'; 单个字段且不是复合索引前导列的情况;
:JH9}5}n?05、查询条件为:C='ZZZ' and A='XXX'; 查询条件既包含其它字段,也包含复合索引前导列的情况;
+r_K$XSbB4S1~06、查询条件为:C='ZZZ' and B='YYY'; 查询条件既包含其它字段,也包含复合索引非前导列字段的情况;
mz-K$Z \/U+K07、查询条件为:C='ZZZ' and A='XXX' and B='YYY'; 查询条件除复核索引字段外还包括其它字段,且索引外字段在第一位;
51Testing软件测试网6NR.qX&k"}"\51Testing软件测试网b
Cvw s({q}---------------------------------------------------
4A1m/x
v)c)tQ%u
tX0执行过程:(X4?J0_[)]{(S8d0C4p)Dh1cb#R}0创建测试数据:51Testing软件测试网{_w
u2Qg2x7_create table test_index_demo(recid RAW(16) not null,customer_id RAW(16) not null,product_id RAW(16) not null)
51Testing软件测试网"y2K1E&m5m.j2d9S$EBI'Icreate unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)
u)hi)TpzM\0根据查询字段做笛卡尔积准备了将近3000万数据;
?I {co7V pS0exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');
|o9I8Jfs08j,q$W"Agl0分别针对每个场景进行测试,查看执行计划如下:
51Testing软件测试网q#PPE5^[$Y场景一:
Z/X!z iV]0select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
'yrhycC051Testing软件测试网_:RyUm@此处是走索引的,符合预期;
7u ]n(_f zc W(T9J051Testing软件测试网
J)I\rqp?n x场景二:
+m:cgW5P/q }u0select * from TEST_INDEX_DEMO T WHERE
T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
3L!oW)} g-h7Y5{(X0s+rxt3G7ag0K`x1X0此处可以看到ORACLE的
优化器实际上把查询条件的顺序进行了调整,所以同样走了索引,符合预期;
51Testing软件测试网bk/k C7S$D4cw|Bls$f/dHh#a LBL)p0场景三:
51Testing软件测试网5} pJ3X1d$tYhselect * from TEST_INDEX_DEMO T WHERE
T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
|)@A w V
R7Hh051Testing软件测试网7M%oETR此处因为查询条件为复合索引的前导列,所以走了索引,符合预期;
;r4DmL"^"s:z051Testing软件测试网
sQ;FhgW%tmBf场景四:
4b?d3L R3s2U)Y0select * from TEST_INDEX_DEMO T WHERE
T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
51Testing软件测试网LF"NW~^-\A
pz
Ek;N)O$_^%Z0此处没有走索引,走的全表扫描,我原本以为即便不是复合索引的前导列,也会走索引的,看来我原来的认识是错误的。
51Testing软件测试网6Ak
V$}ocp@}%`针对这个我特意临时添加了index(B、A)验证了一下,是因为查询条件字段不是复合索引前导列导致,还是因为出于其它方面的成本考虑,测试发现,添加了index(B、A)顺序的索引后,该SQL就可以走索引了,所以应该可以认为是查询条件字段不是复合索引前导列导致的。
51Testing软件测试网D8Y/^7[/X8p$n?51Testing软件测试网`] G9LX.X-F场景五:
;eH&}RA_!k0select * from TEST_INDEX_DEMO T WHERE
51Testing软件测试网,S5lXG:[T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');
:@^:pO0{6R+b[&ml051Testing软件测试网!MR F'A,K+[ORACLE优化器并没有调整查询条件字段的顺序,但是依然走了索引。走索引是符合预期的,但是我原本以后应该会改变字段顺序的,这块我有点不太理解,如果有看到这篇日志的大牛请帮忙解惑一下,谢谢。
51Testing软件测试网7XQ0Okn
J51Testing软件测试网+v:|)n'D;Z3wc场景六:
51Testing软件测试网W*q!dm{select * from TEST_INDEX_DEMO T WHERE
51Testing软件测试网Xw.O@aT.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
6r
z?:Z
u%M,`051Testing软件测试网c:N9d5^/Pk此处没有走索引,倒是符合预期,汗~~~~
51Testing软件测试网$m1eJj j'Z51Testing软件测试网&D
A|;f)W!c%k场景七:
51Testing软件测试网9TH.BN&\/G&m]select * from TEST_INDEX_DEMO T WHERE
(ZBN%SR1P.UI2dm0T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND
T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');
2k&YW
L.T
O$q0ax0qQ)lt4i0此处走索引了,结果倒是符合预期。
.H5vv4c3mv)|"Pe`M0l2h
WYmI0P)U0----------------------------------------------------
"[&@ u;RL,j1I0结果对比及结论:
A&x{a fwf&FX"@{nU0所以经此对比,我的结论是:
!{ iK*iT,U01、查询条件字段与复合索引字段一致的,无论顺序如何,ORACLE优化器会自动调整顺序,结论是会走索引;51Testing软件测试网Q8VrL5m"h ?p
2、查询条件字段与复合索引字段不一致,查询条件字段包含复合索引前导列的,可以走索引;不包含索引前导列,则不走索引;51Testing软件测试网}vg'wNu?(t9?
--------------------------
.M+lH~:TE0所以,由此结论可以看出,在设计查询的时候,还是应该要求开发在组织SQL的时候对于第一个查询条件该用哪个字段还是需要综合考虑系统所有查询来进行设计一下的。
{5Ds8wB6_l0