DBA手记0830-关于索引的深入

1.       索引访问的深入。

}h0X SWp0

B-Tree

o[
G/GW_0

e%M(O-k-d!k.c0D0

前缀索引，向左命中索引。

p6\ Y{1H6r+~0

命中情况51Testing软件测试网.C3Vz*{D}

匹配全名

'_Oe!? B\w0

匹配最左前缀

c7VM(_o0

匹配列前缀

0N8N7SH @``0

匹配范围值51Testing软件测试网z7h\c`!Dia,o:r6B*r;c

精确匹配一部分并匹配某个范围中的一部分

5A(T [&k[rR0

只访问索引的查询

d,uP x XO0

 

NRX.y z0

聚合索引51Testing软件测试网)p%brl$d T'Q

 51Testing软件测试网1\U1FgZLd g

覆盖索引51Testing软件测试网$y@v"Go%V'D

 

CF9KU0J{!{ |0

重复索引和多余索引的问题

vPAtZ-F-X0N0

 

fr:jLz9x9s0

 51Testing软件测试网f&D vLC [`,J

2.       几个比较难命中或不命中索引的语句 的优化空间51Testing软件测试网6y{!B yb$J

Like语句优化：   可以用合并索引及重写的方式

5^;K
g;DA-EL0

实例151Testing软件测试网b"oty:p/~*bT$|

Select * from products where actor=’sss’ and title like ‘%ba’

1|rYs&z5Ym`&Lt0

缺点：没有索引覆盖查询。覆盖到所有的列

W R'y4K%d.s9y7t0

Mysql不能在索引中执行like操作。51Testing软件测试网Tl(`o7G\D

 

T%~]:~+X[H oE0

改进后

Rp+_ OAc0

Select * from product join （select prod_id from product where actor=’sss’ and tile like’%ba’） as t1 on (t1.prod_id=product.prod_id)

#J
W-p/b
Lej:q0

 51Testing软件测试网c:r!W0d%lF(X

从上，可以引入一种查询的好策略

t3m~)yS(@#e0

恶劣的语句51Testing软件测试网&tyX!LZ

实例2：

1H&Fx1W#N0B(HX&u0

Select <col> from profiles where sex=‘m’ order by rating limit 10000,1051Testing软件测试网(b1MT I*n II^&X

这种无论有没有索引 都不能命中。而且高偏移量会要求花费很大的时间扫描数据，

|P)t\g-N9N"g0

 51Testing软件测试网B/R%F
h!u5{,a\7Q

解决办法： 规范化，预先计算， 缓存51Testing软件测试网x/R|$a#nG

 51Testing软件测试网!yI%x8lfs.^g

优化后51Testing软件测试网-I#^q\U

Select * from profiles inner join<select primary key col>  from profiles where x.sex=’m’ order by rating limit 10000,10 as x using <primary key col> 51Testing软件测试网0JPS%Ga#kK

 

:j;r(O$tn0

 51Testing软件测试网*l8s8feQhy3S.A

范围条件

yqyZ2W4K0

>id

$p;i0U(m*{_6^+[)nU0

转换成in（id）51Testing软件测试网+|i3p2is

 

D4?e6W%R'o0

3.       Optimize table 的重要性

zTxg b.t W0

 消除 行碎片，内部行碎片（全表扫描和聚集索引范围引起的）51Testing软件测试网X"d] ZN y$k[-RV

对于聚集索引，重建他，并优化填充页面，他使得范围查询使得完全的顺序索引访问。51Testing软件测试网)\G]!U n4p@

 

a(vxz:q4b"^T0

引入定期修表的脚本。

+E^WBd m;t0

4.       几种被隔离的 索引。51Testing软件测试网}7@L$G/E-J

联接语句，不能同时使用一个索引进行排序和过滤。51Testing软件测试网G$L T'rG(u#Y

 51Testing软件测试网{-_;VJX#X:\

5.       如何避免磁盘上的临时表。

J5uA mF0

慎重使用text和bloB

0T0_2~\ v;P#`0