|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
# ]1 x3 }# C5 \7 X# b. C有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成$ K7 c/ [. L1 N
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
" j4 {$ u; J; Y1 Y& S8 g) j- p在InnoDB中,只有事务提交后才会解锁
9 ]' n& t+ _. H4 `
- U, U7 l7 t1 {* e3 B9 ~索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只- A* f* F* ]# c5 v" U# e9 A/ P# C
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。5 t2 z* M( q) [) C% b- z
6 j+ @5 G4 s4 u" d* S1 E% mB-TREE
5 w2 b$ r9 N8 Y# ]6 _% L- `- r能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
e+ I/ X% A* N$ A: G2 z8 {. i1 r, b使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
$ L1 n' q) w4 d5 A/ H- - M8 Y8 k2 `+ `. Z
- CREATE TABLE People(0 d! d7 h) ~* \9 T) L. S n. M
- last_name varchar(50) not null+ k2 a) \: ], l8 I% f
- first_name varchar(50) not null9 D# b7 Z& P* m* ? f: g K
- dob date not null/ o r! d' N( P9 Y. i
- gende enum('m','f') not null1 V2 a& S z5 L' B4 q( N+ c
- key(last_name,first_name,dob)
复制代码 匹配全名
) P* D5 z& Y( M- |/ S全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。* n# ?1 i, L H) }% L3 o5 {! b
的人。5 F7 u \0 K2 _5 k
匹配最左前缀
2 m; j7 T. A- H6 }0 ?1 ?' A6 BB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
9 E7 k- R7 W8 D3 N1 R( O匹配列前缀- I' s9 e; X6 |& p7 b3 h
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。$ F9 V, {' F$ ~& A
匹配范围值
( u3 y& n& f8 T% K. f, i这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
% R" q& A- d& `( a8 i# S; c" h. D精确匹配一部分并且匹配某个范围中的另一部分
8 e) ~( \& c% {2 @) b这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
2 R# R8 E6 N0 i5 A列并且对first name列进行了范囤查询。
9 ~1 B& ~( s- \: O. d; Cname
7 h( {. Y5 T, R1 J, x只访问索引的查询
- u" F& D3 v2 V! n) IB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
+ A2 N0 g7 t9 @; }. T# H: D4 \4 V6 s2 u8 @
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,8 T! `" e3 t" }8 t
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查0 i6 ]4 a7 K! l- \ T7 D
找方式也可以同等地应用于ORDER BY。
1 T: R0 c) M0 B% }1 B- I# r( o4 D* [# Y& b+ r- Z Y
下面是B-Tree索引的一些局限:
9 ~& N5 L2 [$ q, L! D' q1 t- t% P# w4 P3 \; E& h( m' {
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
, I8 k2 c5 x! ~也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓6 R$ _: I! \! y9 c9 u+ n& Z: E4 B* M! z
氏以特定字符结尾的人。& `0 b; P+ i4 s6 ~! u
, L+ k% b4 a. K! C
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
+ q# \& g& e4 M& A2 v" ^1 J义first_name列的值,MySQL就只能使用索引的第一列。
. Y+ f& x! G3 c
) }, t$ n0 c" r+ ]% h, ^1 h# m3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
+ k4 q6 ?' R) c9 u2 |范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而! d, n5 r7 K( y6 B
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
# w l5 G L3 h( c4 `$ g9 ^- I9 [ C
哈希索引,空间索引和全文索引等,暂时没有设计/ J" Y- L8 |7 s! c2 K9 |
8 H7 e. \5 I$ i5 G$ n( |
高性能索引策略
2 d% v% _% K& }3 j' R& n6 L) H- U0 _1 }/ l
1,隔离列,意思就是不要对查询条件中列进行计算等操作
6 }$ w9 Q8 t6 J- n e2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
3 J1 P6 @7 W# R jSelect count(distinct 列) /count(*) from table;
3 _+ x1 W+ \6 R看看这个值时多少,如0.0312
p4 o- \, \. [% T6 _那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
: o; h4 Y0 j# J+ O. H) E,这对于大表很有用。" b% s4 V) h, [ N2 ?5 p
Select count(distinct left(列,3)) /count(*) as sel1,
5 R8 w) D' r; J% y; V count(distinct left(列,4)) /count(*) as sel1 ,
' z2 J6 W I' i+ {, A4 x count(distinct left(列,5)) /count(*) as sel1,8 E( N8 p* `: b/ `
count(distinct left(列,6)) /count(*) as sel1,
! j% m7 O' p+ J; F! w count(distinct left(列,7)) /count(*) as sel1 from table;' Z9 Q/ h" i7 k! K8 o' ^1 B7 @
找到接近0.0312即可。# m. T6 X# x# M" O" g) m: Q
. M4 |' c" P6 w! u! ^- z) l
Alter table table_name add key (列(7))
9 m# Q& X$ c7 J S' e3,覆盖索引
# i2 _6 k& o+ {包含或者覆盖所有满足查询的数据索引叫做覆盖索引
3 s# P1 t9 x; [' i- cexplain时,extra中的会显示using index
8 e6 U7 v; i B( Z0 K' ], i这里一个重要的原则是
$ R$ Q! `( C- J2 J! Fselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列0 r% M4 l0 u- y# ^. g, w$ ?
如select id from table_name;+ c0 x. D M1 H0 z
7 l& K- T/ k1 b, }- ]/ }很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不
1 y& s( [5 e" \" d5 e) R0 u一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。: e/ y& B4 {: q, D, C
6 Q1 s" [$ N% u! q- d; l2 I4 L- Explain Select * from table_name where col ='nam' and col1 like '%name%';
& e. P4 Z' ^6 ^" V; | - Extra:using where
复制代码 该索引不能覆盖查询的原因:$ j( h5 E, `' o) {# J+ E: @
1,: g, V) F# P2 F* Q8 K; f( P+ d
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
8 } ~# A) b- c: I, s! V2,0 i4 ]! s i' [8 @0 k6 a3 H" c
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
7 U* L# a* b: |有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
$ O: n' ]$ B5 q5 J6 `2 t9 ^# |) [" l4 Z+ M8 y+ M
4,为排序使用索引扫描
" z C3 @7 m/ q' M% g$ Umysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。1 Y( O( O5 l; \, y# {1 R* E0 e, m
explain输出type为index,表示mysql会扫描索引
# f+ f5 ?0 T- ^$ _5 ^& r4 O
8 p9 g. n, o- D3 e& m扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
% g- @ y: d# `% V0 b. K6 ~0 Z) L
+ V$ X S0 X3 E7 ?" b* FMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
3 y& v9 s+ }! u: }
, a: n$ O7 K+ i8 i3 Q% T% u, ^按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
" G0 t3 p R" L; ~+ g. g; j
0 ~+ r. s9 I( {1 J, @7 J, k$ J4 C" D( GORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
& y# B! U% e1 i- o4 e% I. z, E
# A# u [! G6 V& L6 X2 h使用join可能情况会有不同+ S4 S* Y. K, W' c( v: c
1 B% a8 g5 [ A4 }. z( R/ O; k8 ?5,压缩索引(myisam)
/ {8 ~& y8 x; F I$ L$ \" A: K6,多余和重复索引(应该避免)
# O- H2 @7 g$ `/ R* g0 q* \- p8 g# p
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
7 h& W4 X6 \2 S( L' n! b! |上有索引,那么另外一个列(A)上的
6 C- [" O9 ~' y {9 ^( q" P' y J索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。): b Q" U+ F% B0 S, |' }1 q0 n% ^ n& l
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。2 [- T$ a. m% e$ V' g
$ |/ {6 u9 m5 r要点:
4 e1 B3 c9 g7 f! f" i8 o; Z在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.* S0 H& a% s& m$ v: f- n, b: l
, @3 |) s7 j1 o1 D# x/ R
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
3 F$ s+ l0 Z" _% ^
0 y" }5 ]9 ]: t6 Z0 v6 k1 U
' ]: a0 ~9 `* y' ^4 a6 _8 B6 h
, u" K1 s4 V) X |
|