|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
& V+ t6 _, A$ C1 c8 p有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
* ~# t* N" v# H一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
( x. c0 y: j" t4 r& G ~: J9 N. S在InnoDB中,只有事务提交后才会解锁: ]! m5 B* Q# {
( B1 U( D; F5 A. [/ W: k/ M2 [索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
- s M5 G+ H4 o& H能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
" D: @! \( J: J" D1 b" B$ G! J
' Q; H; D; Y6 ~ PB-TREE
) w) F+ E$ ?: s% J& t能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
7 @" @+ ? z' Y7 }& Q1 R+ e/ C使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。) d) z/ x- P- C6 `" r" u% R" p
- 5 d7 E# ~) _ q. ~& p
- CREATE TABLE People(
0 [" n& a' Q* Y' I6 i! P - last_name varchar(50) not null# L6 L. m; e/ J6 j: d9 }
- first_name varchar(50) not null
) k8 ?2 }9 I* c2 s; i6 n# } - dob date not null' S* I, q2 ?6 O" O) W
- gende enum('m','f') not null
) ]$ ]& j8 K. M/ F. _1 W) Y - key(last_name,first_name,dob)
复制代码 匹配全名
4 }, N% \8 z! S I! C全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。. p& p, i: B t- W4 `
的人。" H9 ~! ^9 b7 `2 ?% C# ^
匹配最左前缀3 ^6 l# w/ b9 z+ N$ G( f& Q- L. v
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。- v3 ?% a' ^# e5 H) ]: c
匹配列前缀* \! R2 U6 j/ ]6 J @
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
1 l! L( `: G2 a0 U3 i匹配范围值
0 r( c: g( Y( J) O$ _; ^$ x* d; }这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
: ~6 u5 \. O7 E7 o精确匹配一部分并且匹配某个范围中的另一部分 o$ L. Q* k Q' S2 Z
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
& w3 g1 }1 p0 t, v& @列并且对first name列进行了范囤查询。
2 D! W9 V7 \/ \) \, [5 Xname9 E- x1 E+ p: R7 k+ {, E
只访问索引的查询6 ~# M8 j# g% Z9 E3 l' X
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。7 P; \% b. N% Y8 I5 ~- u# i
8 r3 M) S2 `( h5 r" ^* m由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,+ c% s$ {+ E; U4 U8 D2 G2 p' m
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
* q9 @. F. `; J' m: e找方式也可以同等地应用于ORDER BY。
% |+ w: e- l, M3 q- x! p# x# S3 y5 r- |# g$ u7 [! _
下面是B-Tree索引的一些局限:
$ M/ c- ]6 ^7 \( a9 G) p" j6 Z" m$ p% m4 s8 }# S
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,( X6 w1 O( O! I! _/ o4 i( ]
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓 i7 E, s' V4 N4 w" }
氏以特定字符结尾的人。( L8 \2 U6 _) q( P h: N2 S
% j+ O- L8 i8 s5 A/ t, b! d
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定4 U! d3 H5 j4 M; [- {: P
义first_name列的值,MySQL就只能使用索引的第一列。& X' Q. V8 U8 T. n. U5 f B+ R& @1 k, A
; ]3 e" Y5 w9 k
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
+ S; V2 H6 L. d8 _6 L) |范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
+ X6 ]! U! k4 ]3 |/ H) a: l# u不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
. J1 E) P9 f2 o& p; N% M& ]1 }4 `9 H! Q& s8 }
哈希索引,空间索引和全文索引等,暂时没有设计& M" p3 ^+ r* F( L
0 a S4 {$ Q1 p/ F; \) d6 z高性能索引策略 c1 X/ Z4 |; K. i- H
! B" U* Y2 h4 Z' O! R1,隔离列,意思就是不要对查询条件中列进行计算等操作
$ Q* n! ]4 W. e. D9 h: p2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
7 T3 R. h- M5 {$ @7 h( V0 h8 vSelect count(distinct 列) /count(*) from table;
# q. | u7 b$ u5 \看看这个值时多少,如0.0312
5 K: Z3 U5 A r那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算2 o& m A+ v# X0 D: R1 |5 F
,这对于大表很有用。% M. e6 m0 L9 w1 V% R9 n# B J
Select count(distinct left(列,3)) /count(*) as sel1,( J' d) y N- B
count(distinct left(列,4)) /count(*) as sel1 ,
+ I3 @. J+ m" p5 S3 u count(distinct left(列,5)) /count(*) as sel1,' ?. E( v3 K' Y6 N9 v: P
count(distinct left(列,6)) /count(*) as sel1,
' Y* P$ T$ {. ]% Z. A count(distinct left(列,7)) /count(*) as sel1 from table;: u! W, ?1 W# w6 q5 i W
找到接近0.0312即可。
1 k: }- @. C% l3 y: J4 b% K/ D" B+ W9 n% v
Alter table table_name add key (列(7))
; \2 v3 N( e$ E1 v' v' h3,覆盖索引
' I6 R9 |6 r @' e ?7 a0 b/ b包含或者覆盖所有满足查询的数据索引叫做覆盖索引
4 {/ L; v4 t. l& t* }, ?- Z" lexplain时,extra中的会显示using index% F1 D. G z; s+ T* Z, b
这里一个重要的原则是; T U- `3 |. ?' ?/ O; J3 H1 X' f
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
8 v. C$ ~) X. B: m. N' g/ W: b2 D. }+ d如select id from table_name;
4 p5 d7 m2 W& e; n- I' y2 a, v6 S/ h b; k/ C
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
0 N. V) B! f* }. t0 }2 l* |$ M一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。6 h4 \% I; L0 s: X/ W7 ~# T2 O- I
! }4 p) ]/ ?( c; c' D$ i: S- Explain Select * from table_name where col ='nam' and col1 like '%name%';% {: @% T5 B( A2 p! A* y% Q
- Extra:using where
复制代码 该索引不能覆盖查询的原因:
7 z( k8 w+ }3 x8 A9 ?7 L4 s1,& f. N& d% `' K: l# }& O
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。. _" I" H% a2 k8 Q, T; P: h+ M
2,
5 C( V1 A' Q7 i( tMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。% o+ [' c7 e$ m
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:2 m6 S8 q& N- @; i/ z+ o
( Q* T% i+ k0 C. P
4,为排序使用索引扫描; ?( f3 u) M% M; S% Y- v
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
$ R& W* |5 d, a8 c \- ~explain输出type为index,表示mysql会扫描索引
2 C; Q0 V' {- `2 `! f7 V5 w6 l5 ?
5 m" A: l4 b5 p/ G扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.' b/ N& e) f$ Z
$ F% l3 V9 ~7 f7 h2 l' V
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
$ m& W' p! L* Y0 A& ?& ]; S
6 a, N# Q# i. ]; @' W" i. c按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
- g$ W( H0 V0 `* J" \# c" h, s( `* l' h3 q
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。+ t' }7 ]6 D7 b
& I! H6 g9 G! _使用join可能情况会有不同
8 r: C! J+ R7 ]8 V+ {' B8 v* {( @0 ^* {
5,压缩索引(myisam)# [2 u6 {; c0 z
6,多余和重复索引(应该避免)
! Y6 l# z0 x4 w8 [9 f0 B. m% R% c# A; U5 u c
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)7 l- ~' \' R# K. ~$ j
上有索引,那么另外一个列(A)上的
- v" x+ F5 W2 e: R0 l索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)( @+ l% r5 S* U* l2 P8 @$ B1 P. k
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
. M) e; h0 }% M; Y$ K/ T& o- _/ {% F- A) q/ \
要点:
; u7 W7 \/ P) [2 Z/ f; C在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.$ }7 z8 B1 k/ W
" x- a4 B; w: |$ {0 W3 f2 i
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
6 u, {# ?# K% d; V1 }0 k+ e9 c
3 o! _- B8 U$ [, Q& z% g; ^2 s& V# ?! B& `! Z4 c* i" L
( _' d& k3 l- E, L7 [ |
|