|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
, H5 |1 A+ y6 d! j ^! I) T有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
6 K! ]6 K6 d8 O& ^一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
# [/ T/ C, Q5 p在InnoDB中,只有事务提交后才会解锁
, i# ^0 ^% H4 ]% ?2 k# m. {+ M$ v: c+ l+ t+ n
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只; S' y: L8 ]' S% `( W5 Z
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。" A+ L6 M$ Y# v: X& M. Q1 H
" ^9 o! C; k. B9 T$ _' a1 ^0 e2 cB-TREE+ g# t1 a& \: d6 T2 Q
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
, L+ C& i4 H' V8 c6 t" Q使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。 g8 p2 X0 `9 \) F& n
- 9 K9 _. F6 N) R; M* @: U2 z
- CREATE TABLE People(1 G- c5 a8 ?( m7 I
- last_name varchar(50) not null. l* w5 d$ C6 _( ], }
- first_name varchar(50) not null
( {: d. G# z' ?& h - dob date not null
* }9 j8 H t" m7 [# T - gende enum('m','f') not null
1 [( s, T: C2 g" c' g - key(last_name,first_name,dob)
复制代码 匹配全名. D0 w% J0 B* ?4 C( a0 F! z x
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
: N J' `0 t v9 o y/ J3 o的人。1 c- e! G4 o0 s
匹配最左前缀
$ |0 M" J' ^7 p% xB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。, a7 a: t( A7 o2 Z# z% ]
匹配列前缀
( H r U9 e3 U; c可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
) i6 f' h& Q" o0 y( a5 M匹配范围值
# \$ n% c8 W3 C6 v% m9 u这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.( Y: B7 D7 {/ p" ~# J
精确匹配一部分并且匹配某个范围中的另一部分1 n, o& l% d' X# H7 O$ y
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
! K1 A- G; b) c5 u8 D7 i列并且对first name列进行了范囤查询。& H: H7 i5 k. H( q5 F# k( D- N! C
name* J& {/ f3 Y! [2 k; y% U0 v- ]7 t0 j" D
只访问索引的查询
9 }5 T4 u$ P. u: e( _B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。: V T4 |* T$ ~, {' i
0 A" Y4 c* k" t3 P$ o由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,
' A7 j6 M# I' D: Z, ^如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
% w9 E. V5 s' E/ z) L2 }. [* b' i找方式也可以同等地应用于ORDER BY。
( K* F. J. m. G7 x0 ~
4 o9 B" ?7 V5 r1 h. S+ G下面是B-Tree索引的一些局限:
9 {6 l6 ?/ |5 h v1 H, a5 n( E$ ^* j0 [7 f# ]. ?
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
% ^8 _. {! u/ [+ r" m也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓4 H7 B3 c3 f6 Y6 Q; `
氏以特定字符结尾的人。
8 R* P/ z0 c W3 q' g) `) @' f: W: h. n- O- F
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
- v: U+ _0 r+ y7 D; V: |义first_name列的值,MySQL就只能使用索引的第一列。
1 A& d* \+ S8 W b3 c7 \ I! q" y' i S. X/ g# S
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
7 ~2 B1 m' v+ w/ }! u范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而3 g; q% z V7 s. O# r
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。- Q7 i# f ?1 m! T* R9 O
1 ]) e, n. r9 Q @: Q
哈希索引,空间索引和全文索引等,暂时没有设计
6 C! @% S: N d/ V( n) m8 y: F% R& ~/ p
高性能索引策略
2 Z* d" M4 [2 d ?3 ?6 n: ?. h* D K" F) R: c8 @, x
1,隔离列,意思就是不要对查询条件中列进行计算等操作
" U+ v% x' Y# O7 _+ G8 m2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引! H1 ?, |! R/ o+ P" ~2 |7 ]" s
Select count(distinct 列) /count(*) from table;
* t. d, o, ? r# a# x3 n看看这个值时多少,如0.03125 s) n5 _# a5 w
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
# g2 y! E- D; S. _/ ]# ],这对于大表很有用。; P+ c7 |- h7 O( u3 e6 g
Select count(distinct left(列,3)) /count(*) as sel1,- U, @/ P- y: }0 H
count(distinct left(列,4)) /count(*) as sel1 ,
! G# N/ Z* l5 T) C. _+ u2 d count(distinct left(列,5)) /count(*) as sel1,& |* |$ U( u9 a
count(distinct left(列,6)) /count(*) as sel1,
! r8 R7 }2 ~4 D- V8 C' s, [5 B count(distinct left(列,7)) /count(*) as sel1 from table;' |+ Z0 x5 Q/ R2 U) |
找到接近0.0312即可。
1 [5 o; P H( \/ [% H4 R: C1 |; I, }% J
Alter table table_name add key (列(7))
9 J, i1 z2 p, @5 @/ ]* I M3,覆盖索引% x s! J1 Z9 k# j8 L
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
0 j3 G( {2 T. cexplain时,extra中的会显示using index3 c9 |$ T1 E0 _4 W& {. V
这里一个重要的原则是& k \; N/ z: [
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
, u/ X! k9 S$ \如select id from table_name;% Y2 E- u& v" z. r k' W- a
. u: r0 i( |% E4 y7 }" `$ c. _
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
8 b: t+ e" b L一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。4 \# d( X; U4 H( u; T. {9 k# a
( v5 f8 Q7 l8 N3 K: V# o( [- Explain Select * from table_name where col ='nam' and col1 like '%name%';
0 S T6 }% e3 Y) R! h0 q9 Y" ? - Extra:using where
复制代码 该索引不能覆盖查询的原因:1 u: ~0 p. F D/ X
1,4 g, @6 K: n! x) }4 v' W
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
! S9 P; R# S+ Y: t; a2,) S, C1 @# B6 h8 a
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
. Y+ z- Y. Q) \/ }1 A" @有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
( m/ g! y+ C) R: R
1 r. s7 h( ]9 ]. }4,为排序使用索引扫描
0 N0 E9 N& a& U8 J( kmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
( s5 l9 J8 P2 a, e. F* A$ X/ qexplain输出type为index,表示mysql会扫描索引
6 g, |3 C7 T; _- L' |3 T2 w7 j X+ Q9 J* b( Y1 p; p* L2 ?7 ?
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.2 N A1 b( @$ F" \+ Y7 O4 M* o
( m( L" `8 \3 S. G' x( hMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。2 n6 M2 @8 R, s6 m' s$ R
4 N+ A% m9 n/ V! |, Y% H3 ~按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
/ o4 V$ t; s0 j1 C' j+ ?4 w. [8 ~
( S% g" H% b$ a u7 @8 [ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。/ u1 x3 l* _, m& C
3 f ?; n5 d2 I v5 S, I使用join可能情况会有不同
( w( W, ~2 r* o7 O3 Z1 V; r* |$ Z$ m; T2 m% [+ @
5,压缩索引(myisam)
3 O8 B- w, T7 P6,多余和重复索引(应该避免)3 Y% i- R" a8 B# ^
! z0 t* f; `5 r3 ?多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
! t" l1 @" M5 t# i r1 s0 x上有索引,那么另外一个列(A)上的
- @ f; w2 ~4 \2 A. t索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
; j6 Q7 q- _4 u! i& {. m4 I, t然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。& Y7 k$ b6 x! \
; g8 e6 n) E" Q5 u& s要点:' x! Q1 e, g0 }; s& v( q: S% O) a
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
- q% q$ ]" ?; [* g) W' F: c
1 u1 D; N' L0 y即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行6 O0 x. d% ^! Z
4 M* O' [. Y6 y2 b
7 V/ `5 R, E8 n/ P* M/ N7 q
! d4 Z9 J! u+ a2 O- } |
|