oracle复合索引第一个字段,复合索引的先决使用条件 - stacktestor的个人空间 - 51Testing软件测试网 51Testing软件测试网-软件测试人的精神家园...

背景：51Testing软件测试网]k2u4q[F

今天，接到一个项目的项目经理电话，告之说生产环境有几个查询超级慢，就是查询单张表的数据，查询条件也很简单，但是加了索引以后并没有走索引，依然还是走的全表扫描。

UQZ5V!z.oh0听到该问题描述，我开始浮想联翩，统计信息太旧？存在隐式转换？索引树倾斜度太高，导致oracle认为走索引的成本更高？

%|vE#z2B/e1?m0带着各种可能的原因猜想，火速赶到了现场，发现原来都是我想多了。不走索引单纯是建立的索引不合理，查询条件是多个字段，应该建立复合索引，现场维护人员只对其中单个字段建立了索引，ORACLE认为不如走全表扫描开销小，所以没走索引。

[3FQF

eg0汗~~~~~~

[5M:LB4`~0添加索引的时候发现，几个不同的查询，查询条件字段都一样，但是写的顺序却不一样(开发写SQL太随意了！！！！字段一样，顺序也写成一样啊！！！！！！！)，结果先剧透一下，同样也是可以走索引的。而由此联想到些问题，于是在解决完效率问题后，在个人环境上做了一个验证。51Testing软件测试网$vc-T zg

这个就是完整的背景。

!RYG-O:F*]I ^M051Testing软件测试网&O@8saHK

---------------------------------------------------51Testing软件测试网?7d]7g0T;M

验证内容：51Testing软件测试网~r zvrz#Maq

ORACLE 11GR2 复合索引的使用条件。51Testing软件测试网f VWMT.h6h6p,f

前提条件：

A6GeAk8k!aX0创建一张表，并对字段A、B建立组合索引，顺序为index(A、B)；

/?SHYj}Un(i0测试场景：51Testing软件测试网oB g-e`\}&C针对以下6个场景进行测试：51Testing软件测试网7xX^2sx5p(o1、查询条件为：A='XXX' and B='YYY';  顺序完全一致的情况；

!~"{Y*D\7a,w(F:M1E02、查询条件为：B='YYY' and A='XXX';  顺序不一致的情况；51Testing软件测试网&@*{q9kE5dnM

3、查询条件为：A='XXX';              单个字段且为复合索引前导列的情况；

4Ca"BSDw#f04、查询条件为：B='YYY';              单个字段且不是复合索引前导列的情况；

@.ch-N?(v%m05、查询条件为：C='ZZZ' and A='XXX';  查询条件既包含其它字段，也包含复合索引前导列的情况；

#cYCl!XJ06、查询条件为：C='ZZZ' and B='YYY';  查询条件既包含其它字段，也包含复合索引非前导列字段的情况；

W4CBQ9I4f07、查询条件为：C='ZZZ' and A='XXX' and B='YYY';  查询条件除复核索引字段外还包括其它字段,且索引外字段在第一位；

jWE)TA4}051Testing软件测试网1_Yv&|f,m/R,B

---------------------------------------------------

,L

O2O'A)]|h2p0执行过程：51Testing软件测试网f?s,Ow2w;n

51Testing软件测试网i8n5T(pACV

创建测试数据：

6r9Lre6Kh+f%P0create table test_index_demo(recid  RAW(16) not null,customer_id RAW(16) not null,product_id  RAW(16) not null)

!s-Q8j

VB$B3G0create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)

+Z^&Lz.d(@-fF%oQ)u0根据查询字段做笛卡尔积准备了将近3000万数据；

FI_d.OJ(M$fk0exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');51Testing软件测试网GC$b$O0`9G

51Testing软件测试网9]+rt,Qg7qb ^

分别针对每个场景进行测试，查看执行计划如下：51Testing软件测试网S+l.X/gU

场景一：51Testing软件测试网? f"~8}_'U:i

select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网wSi9AwS

51Testing软件测试网(cZnB0|-Z9nb

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

[+YV.OmCq&i0

:pSfc!TR4Vo#z0场景二：

Vp8T0fuZ0select * from TEST_INDEX_DEMO T WHERE

T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');51Testing软件测试网aT[!l;D

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

$HPydx.A3^/r0

B3lHR(klK.A"|c0场景三：

es(|_i:EU4["\0select * from TEST_INDEX_DEMO T WHERE

T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');51Testing软件测试网8J U6N]+dSI^@|D

51Testing软件测试网h5D#VBN+\D%}+t

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

En6P-}/NR6D k~E7u051Testing软件测试网ej.^ZXFVD

场景四：51Testing软件测试网k*d!J.xVLe3If

select * from TEST_INDEX_DEMO T WHERE

T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');51Testing软件测试网8])M(g#d,YC0@

51Testing软件测试网 i.DYE dkc9`

此处没有走索引，走的全表扫描，我原本以为即便不是复合索引的前导列，也会走索引的，看来我原来的认识是错误的。51Testing软件测试网uI;v1sxpG

针对这个我特意临时添加了index(B、A)验证了一下，是因为查询条件字段不是复合索引前导列导致，还是因为出于其它方面的成本考虑，测试发现，添加了index(B、A)顺序的索引后，该SQL就可以走索引了，所以应该可以认为是查询条件字段不是复合索引前导列导致的。51Testing软件测试网QB_iF

j*T-P3D5d`#W y0场景五：

Tt"@Y/~+]9|l0select * from TEST_INDEX_DEMO T WHERE51Testing软件测试网e0ONmxDEiI

T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

)GP1eeh2W051Testing软件测试网]UY4p

x,q#\

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

51Testing软件测试网NrEx5n6V_/iyR

场景六：51Testing软件测试网&B`e3@h4^(HI

select * from TEST_INDEX_DEMO T WHERE51Testing软件测试网TI*Fua"x|E'm]$T

T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

jTR;R+t^;C051Testing软件测试网+?!S2F{P

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

X0T\~D7y0

!~%J4LU4w+U&E:j0场景七：51Testing软件测试网,m0B+OU.I2J7vV

select * from TEST_INDEX_DEMO T WHERE

pTyAn0T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND

T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

2O)V3Dbek8V2LW0

6AE-i2P

E9D%K0此处走索引了，结果倒是符合预期。51Testing软件测试网5E&V+F:_{{Y

C,ZP2k1kNl@'kbw0----------------------------------------------------

EG(h`

x[i0结果对比及结论：51Testing软件测试网2z2tm}(e.s9?mo所以经此对比，我的结论是：

2b{7oRT%a01、查询条件字段与复合索引字段一致的，无论顺序如何，ORACLE优化器会自动调整顺序，结论是会走索引；

(A*rO,v!A02、查询条件字段与复合索引字段不一致，查询条件字段包含复合索引前导列的，可以走索引；不包含索引前导列，则不走索引；

oo-Wp8j0--------------------------51Testing软件测试网,z

Y z#k:W3@

所以，由此结论可以看出，在设计查询的时候，还是应该要求开发在组织SQL的时候对于第一个查询条件该用哪个字段还是需要综合考虑系统所有查询来进行设计一下的。

:zZ;} vhs}%B0