|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能, c" X D5 d) a' N( B. Z w
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成7 q0 S% L- e2 c( N" X8 [( r
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
8 Z* ?4 a1 Y) x. k/ J5 V在InnoDB中,只有事务提交后才会解锁
9 k2 t0 g3 s# M* L a9 C7 C: A* T5 g5 n* K' ~
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
3 d! F+ J7 x# {/ B% H能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
* i# Z3 q5 |$ [2 V5 z7 X
5 [7 G, Y) s* |5 F# X9 }B-TREE
+ {; D8 y- k4 o能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找0 u6 t$ c/ O1 S2 d& r8 e* r
使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。; G H- B0 Y5 A& [$ \$ E. A
- G; b2 {6 x% V! U- Q- CREATE TABLE People(
- j7 E: Z Z4 b - last_name varchar(50) not null3 x! K$ D9 s0 ^- k e: m0 n Y4 \
- first_name varchar(50) not null
9 X* ?! x4 H" u- l4 f - dob date not null; Y/ R2 R: W2 J+ P3 M# R7 ~
- gende enum('m','f') not null
1 _% t+ y4 G) K/ a - key(last_name,first_name,dob)
复制代码 匹配全名
" J- G, Z6 N# k' S3 R, ~全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。2 {1 f5 i1 G- I/ }
的人。
0 P7 f9 I& ?* G0 r2 \( K: i匹配最左前缀
+ F) a1 L @9 D7 tB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
7 B M% [5 @% z; \匹配列前缀3 d- J$ r% Z7 {) D! s; u4 M
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。' V; y+ S7 p+ ?4 o9 V
匹配范围值0 H5 V( h( k" X" U+ Q( D* e" R
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
7 ?1 ]4 q7 j% E7 u2 ?7 z精确匹配一部分并且匹配某个范围中的另一部分6 P E3 @7 T( s# c }
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
+ K$ Y" K: M/ n7 n* v* A% e列并且对first name列进行了范囤查询。- S9 d# N2 h' u
name
% L8 K2 I1 o S1 Y' g只访问索引的查询/ t* {( W: T6 N+ I
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
1 `. M* H) ^+ @% d' H
5 _; t! T7 N9 G6 t6 X由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,9 A- ]% ?1 [+ Z1 N% W8 H
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查8 \# z. m2 I5 `! W0 U- f: B) w
找方式也可以同等地应用于ORDER BY。
2 T, d& b2 D) F) E: p, m) K6 r
$ E, W$ {/ V$ a, P9 j下面是B-Tree索引的一些局限:5 |" L. Y) T( f. y) D+ {7 v+ I; p: Z
( o& S. s+ n, S, ?; `
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
& ~- o; t7 u% \: w" \也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓) }. F5 _" J/ g4 F J: ] w
氏以特定字符结尾的人。
# `; i3 j" g2 _9 P9 k0 u8 {! ^. t3 h+ }1 B$ u3 X+ s* g8 A
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
# m; U4 ?0 [) r& X义first_name列的值,MySQL就只能使用索引的第一列。
/ ~2 ?' _* m y
/ g$ a0 `6 A0 \; f3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
; f* g3 m; G2 `7 D4 y范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而0 W( Y7 I0 X# Z/ M! i
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
+ z+ G8 K/ M+ G& R$ r. X7 N% m8 @8 Z$ b! l
哈希索引,空间索引和全文索引等,暂时没有设计: v1 s; Y$ M9 m! `
" S8 S+ t1 t' f7 Q# ^5 A高性能索引策略
1 S( y3 G" V9 Q( Q9 W X$ e
9 Z9 }, s" U% s6 t1,隔离列,意思就是不要对查询条件中列进行计算等操作# A" }/ r0 o) z! |. ~8 |
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引7 y9 I3 c( W. n& {
Select count(distinct 列) /count(*) from table;# m- ~+ ~4 z! @( D" ]" ?' q
看看这个值时多少,如0.0312
* e, w3 O1 V$ |* A e' j3 Z$ J那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算; G3 C0 L7 D3 W9 b8 |7 d
,这对于大表很有用。
! S3 Y) T; M1 k, H" R: fSelect count(distinct left(列,3)) /count(*) as sel1,2 A, v5 n# A+ q/ p/ O9 w% ?
count(distinct left(列,4)) /count(*) as sel1 ,
0 E" l" u9 u3 A8 H0 o5 I8 L0 k count(distinct left(列,5)) /count(*) as sel1,
v& w/ m6 I/ D. E8 Y/ \ count(distinct left(列,6)) /count(*) as sel1,
5 `8 U/ O0 R) X) l count(distinct left(列,7)) /count(*) as sel1 from table;# }+ H) ]. p& D: i
找到接近0.0312即可。9 V$ _3 _2 z( N* q! e
2 h6 t3 P, m! b6 A9 R
Alter table table_name add key (列(7))/ W0 U: X" y, \" ~
3,覆盖索引 \! j5 _3 M* }
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
, l# X. G8 D4 y, L0 {explain时,extra中的会显示using index: Z) p5 i3 i+ R2 ?: y
这里一个重要的原则是
$ z( J7 s- ~8 G' J2 z0 Zselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
! h, G6 X' T R U5 A如select id from table_name;
2 V3 u: s/ L' v) H, f' A, i
/ t" p+ z. z& |1 O1 X很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
0 ~: U* {0 s C$ b一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。+ A3 f" w8 c R% x7 T
7 B9 R+ H" R, E7 L/ o- Explain Select * from table_name where col ='nam' and col1 like '%name%';% |: Y) i/ l9 u; r" d6 L, f
- Extra:using where
复制代码 该索引不能覆盖查询的原因:; O4 f' \6 r. c( [" ?, j" E
1,
7 u0 q- }, B6 ?6 v# ~ h没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
9 j+ k* [/ D* i8 @; |8 H2,
$ I: u0 b3 K u: f2 D2 J( k( qMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。$ X- o0 T8 I0 v: \ y+ [8 l
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:) K+ b5 o6 Z2 m/ {* Q" y
1 ~" H/ q. l- W$ g" K) p2 w7 O7 u; v4,为排序使用索引扫描
+ u# i. C l$ {. s3 q8 Vmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
/ q' h$ }, b+ ?& k7 c5 l% Pexplain输出type为index,表示mysql会扫描索引. } X) D, ]6 i5 e$ I
7 c% F; P _* D扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载./ e+ ]7 q1 D" `' R9 }$ O
5 S, I' f9 R, r- I( i
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
0 |6 W$ @0 U! f' P; ]7 N$ `2 T" m5 e: ^& d4 l7 G
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
4 V2 k! f. x. u) K/ F' P0 z5 p- O! ?
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。" ^ ~- v0 T3 A6 u2 c# ^2 u
( @: r! Z% u6 q9 c7 ^
使用join可能情况会有不同
: `; A4 D! K. f2 ?8 Y: y# j* `: d' x
5,压缩索引(myisam)
2 k7 [. o9 p3 Y6 S" p- {4 P+ q6,多余和重复索引(应该避免): Y; o+ i5 o$ Y
0 } a2 z" ~9 @: ^2 F) R多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)& M+ [, \* j5 e; @
上有索引,那么另外一个列(A)上的
% E( K3 G. _( ^4 `% B& p/ ~索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)) {1 F. N2 N& H( f' |
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
" x! @# j0 l' N' B2 t$ ^6 u1 d1 j6 x/ y7 H( H
要点:8 O5 b. n: f. F
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
% U b2 ]' ~/ s6 C/ ?% t
2 Z3 U8 ?3 A2 w J- H即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
& ~$ [" B `$ L1 G5 X
# ~" j, z$ ]. B; W5 D0 |9 N! T8 W) `- h$ R! ^' m; Y- B' {
9 m' \2 [% j8 S- z, r2 H# N3 n- `/ H |
|