|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能: r5 z+ O# a; D) ?+ F/ l
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
% j" P! J' m! W5 G) o- l一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
! G ^: Q6 V# @( y& C" m ~在InnoDB中,只有事务提交后才会解锁, G0 t& z# l$ S( v- @* s, S
' r' c" d7 j6 r' [* T索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
1 n8 v6 ~9 u; ^9 w/ d" ?能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。6 `7 S8 U! j# J$ U
+ r8 B. }. n2 D8 w+ q" A1 Q0 D
B-TREE
& v/ F8 k' W1 q! L- a能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找( y, t8 G/ V4 \4 `6 M
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。' Y: I; ^" R \0 T* G' ~
- 9 m$ {( c* r, u9 i: \
- CREATE TABLE People(" m7 Y1 j+ i- }* i3 ~" ~
- last_name varchar(50) not null
8 o: u+ X6 o5 y- \, `% R0 \ - first_name varchar(50) not null& v. N6 H7 F% l9 V( L" @
- dob date not null
0 Z5 z6 ^# K( ^7 _7 ?6 c- r, t - gende enum('m','f') not null
2 \2 G6 U# r7 w3 `: Q9 K% ^ - key(last_name,first_name,dob)
复制代码 匹配全名" N6 B( Q% B. v: y
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。' p4 L& n6 V* \7 T) J- _
的人。
- I5 w9 ]) Z4 \. _$ p* V: k匹配最左前缀
' v [7 K4 D* Q6 WB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。: z1 s3 ^5 Q+ B F$ ]
匹配列前缀
/ G5 i, j7 H O& C& o& \1 n可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
) i& \' r! C. e匹配范围值
. c- J; N6 t9 U+ H; ?这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.) _! p4 |( ?' n4 }5 J! U
精确匹配一部分并且匹配某个范围中的另一部分
9 R/ ?+ l6 K/ o# V* D这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last' y- s( h5 H; s; {
列并且对first name列进行了范囤查询。, }+ i8 P Q& u5 z0 X8 R6 Y+ \. ~# f
name0 \, p( F' s# O! k r7 ]
只访问索引的查询
8 ^1 z9 X+ j' p d' u/ nB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
" I" [1 ]5 F0 L- W/ x8 H2 H; o, T
! V: q/ N# i A: v8 e由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
5 c. U4 v* g+ n$ P: N如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查5 T6 s1 p- ?6 C6 O! n# w u0 @6 K
找方式也可以同等地应用于ORDER BY。
0 z# e# s" T* `: E1 k; T; N8 L$ F* V: b$ y* p5 [
下面是B-Tree索引的一些局限: ^8 o/ }# O% f7 G% V
: W' `- r- a9 x6 k5 [) B4 L( W" n
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,2 s& z. j" O% i) l8 J( K
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓2 p/ }5 S. W, Y7 g3 D% I3 x
氏以特定字符结尾的人。
4 u& y5 t) Z Z) e& ]4 B+ y" ~% O4 H8 c2 |+ x; b2 P
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定; P6 k( o/ N( |8 O9 b6 X8 r. J! m
义first_name列的值,MySQL就只能使用索引的第一列。
0 c( L" ^. ?% e* O9 ~5 w
1 b, K1 y' e2 W0 d3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是( ?0 E6 }: R4 ^$ S* X
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而 ]& t' I! G8 K+ g
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
0 ?7 H5 G3 G( W
3 w2 F8 M y: `9 n+ {* `哈希索引,空间索引和全文索引等,暂时没有设计3 B5 Q3 H# l& z( l
( z) E8 y" U1 X0 x+ K4 z; D高性能索引策略9 Q* b) E7 I; P z4 r8 {
6 w! }/ H% ~; M6 w1,隔离列,意思就是不要对查询条件中列进行计算等操作
! b x- `1 a) e' Q- e* i H2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
, V( T4 |+ a- Y1 uSelect count(distinct 列) /count(*) from table;
/ C; l) X3 E/ A看看这个值时多少,如0.0312
- s1 R7 a% h- s2 N$ o% j a那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算4 a5 H9 [% K& f( M: ^
,这对于大表很有用。; A; F* z- g1 v+ K0 i
Select count(distinct left(列,3)) /count(*) as sel1,0 l8 u; i/ S' e4 N
count(distinct left(列,4)) /count(*) as sel1 ,
[2 L& w; R2 T" [) J; ?9 ] Z. ` count(distinct left(列,5)) /count(*) as sel1,
9 j# h; T, y9 x8 H8 B count(distinct left(列,6)) /count(*) as sel1,
+ i! a/ D/ _2 L6 S' P+ @: K count(distinct left(列,7)) /count(*) as sel1 from table;
/ j. }1 U1 Z, T! D2 A& f2 {2 `找到接近0.0312即可。: \- T7 V9 m, u1 G: ~$ n
/ q. [; a5 s9 ?7 K
Alter table table_name add key (列(7))
X; l& M1 y4 b! r3,覆盖索引; S% k8 y3 ? d$ @
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
1 m8 d) B2 V5 N; K4 _explain时,extra中的会显示using index
; F2 n. P& v2 U3 |. V这里一个重要的原则是
; |7 O- X* a0 Z* u1 ]4 a! Mselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列( \/ {+ {/ ]" e! ?5 H: k4 e- d
如select id from table_name;
' r7 n q( V2 \; q' {9 _* Z* d
! X, H0 r$ d6 @1 }, Q0 {- C很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
" q3 X+ q" b1 U& K+ J1 d2 c {一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。1 R o7 n g; y0 l
0 Y% j9 D6 T8 R, Y- Explain Select * from table_name where col ='nam' and col1 like '%name%';# O( I2 U) Z. x9 v5 ]/ A7 d
- Extra:using where
复制代码 该索引不能覆盖查询的原因:) S2 W0 w0 s) }
1,
. h* o. S3 W! }' ]6 P! i, e没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
0 ^! s- b, L+ _7 ]0 f2,
- |4 I* @+ E$ e$ X) K+ Q" TMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。2 r; w- E8 _: @; R0 f3 v
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:* L/ N" p1 b" l" S t+ |) R
. k9 t/ J/ n0 C7 s& G$ g, p* L
4,为排序使用索引扫描
- l; c- Z4 g2 Q9 X% y6 i+ dmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
5 \% v* F X$ x% ~explain输出type为index,表示mysql会扫描索引- T3 A: N" q3 m! x! C7 j% W
2 Q: z) d k& Z! |" f4 D2 E: r" p9 G扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
2 j/ H& E! a* ]4 Y& N9 p" p% S8 I3 ~* W
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
7 B. _5 }6 l: W" `! T- e( b$ E, Z; _
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。( _7 Z1 P: I+ K& N- H8 G$ Q5 m& c
/ z, D% y8 b* D" [; H" T
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。8 ~/ a% }) Q, ~! t
1 H& l& z* N7 ^; A4 j3 v1 A; L
使用join可能情况会有不同
1 D, F# y& b. G, o3 L* s
2 M* Z9 [% T4 F D: A- a4 p5,压缩索引(myisam)+ B g2 n0 T1 {* @3 A3 `
6,多余和重复索引(应该避免)
# o- e7 O" c3 p6 R9 [% _; Q- b: D4 C' J$ ]% W: {
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)/ B: J, K3 I( r3 Z( D# z
上有索引,那么另外一个列(A)上的+ x6 C" D# E5 I
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)" a* d2 [2 j5 ~5 n* D
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
. U" A% T$ z9 }7 a: A
* W6 c. K3 [+ Q要点:1 i, j, B- x" o) R$ @# \
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
; m$ L# b* t) S: F: e. ]1 O3 Z2 @9 x/ d
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行" M9 ~( M) ?. o; l
! ? a: }: v Y" a) b6 r y
5 q4 t9 I2 W# T* k8 W3 Q8 S) H
, F# w8 A5 P9 H+ O
|
|