|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能
: I& A. Q$ u& R4 z9 X, \有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成4 Q3 F, B& }) U1 C; j
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列 k( h: K8 `7 ` F
在InnoDB中,只有事务提交后才会解锁- c3 {3 b5 e$ Y+ o) Y
) m5 N4 z- @5 F) f4 ~索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只+ N# q( Y5 ]& S9 J% P% o, i
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。3 y& @2 a# C% @) t
6 } V4 g" A& M6 Z% `2 X; F' ^
B-TREE
0 k# W# c( t2 ]- Q2 C' p能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
' w1 j' P" F$ t A+ S使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
- X3 w7 `6 I5 \. D" k- 8 b3 b" S7 ^7 x, d! a4 E
- CREATE TABLE People(
, ] w( c0 ]. x" E. B/ w- D9 U - last_name varchar(50) not null
! ], a. S8 ?1 x# C6 R - first_name varchar(50) not null3 ~3 _+ f% l; k9 Q2 D; B; Z; x( b- t
- dob date not null
8 w9 Q1 I: V8 a4 ~+ x1 b% B - gende enum('m','f') not null. u& q/ E. [6 I8 L
- key(last_name,first_name,dob)
复制代码 匹配全名' }; x# J/ A n
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
2 m% H; d8 U0 S$ n- k的人。/ p: [; D1 [, b6 S
匹配最左前缀
: I8 A; X: S/ l) ?B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。. e+ m- E3 w6 d. t. J; M
匹配列前缀4 c/ L6 V1 N3 |4 O1 O
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
. t5 }/ C: r) S* M. o2 Y匹配范围值: c: p. K3 S K5 F- x: R
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列./ V: R" f+ r; R
精确匹配一部分并且匹配某个范围中的另一部分
) T3 b7 c- D7 ^5 x! U这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last+ I% f. M( ]( d2 s
列并且对first name列进行了范囤查询。. z8 P; Q1 N/ T/ Y; y* ]
name4 g2 X) }' ~4 B# H
只访问索引的查询
8 k( d9 ?0 N7 z) c0 R( z# S/ WB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
8 ?) R4 j( e: M/ C% M, Y( S! P5 ]& N
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说, L# Y5 E @, q0 o; }5 h" |
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
& h& m6 k# E% h7 ]1 D* D找方式也可以同等地应用于ORDER BY。
) b/ O3 x- @$ G& a# a5 M. v1 D+ }7 F
$ ~$ t# Z* R/ u$ {. w6 \下面是B-Tree索引的一些局限:
2 k& f9 n& i2 Y; x, O; _3 Y
: |" R* C. v [, d0 E5 A1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
! H" g8 L; _% x也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
X" Y1 g! e3 m2 T氏以特定字符结尾的人。
1 D) k: b _1 n4 I) C2 u5 N, S3 J. _" F
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
! Q) C, |/ V: H5 d$ Z义first_name列的值,MySQL就只能使用索引的第一列。7 }% |$ X: p9 S3 _- }
) R5 k+ L4 x* t+ u6 a1 A. p3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
. K3 n7 K$ j, ?; V9 H范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而" \! p; D0 o# C- i
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。1 K" [; P# Z; `5 \* i) e
. z p7 A! G, f哈希索引,空间索引和全文索引等,暂时没有设计
1 v# f' r1 v, w$ d9 C$ F# u" V, V5 f. G. h) O
高性能索引策略, ?0 H4 B; k& O }9 ~3 F# u+ a
7 b( T$ P% a9 e8 f+ p- T @# \' G
1,隔离列,意思就是不要对查询条件中列进行计算等操作: @. v: d# i$ u% n& o6 G
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引# Q: H$ d1 O% p/ x0 |" ?
Select count(distinct 列) /count(*) from table;+ H% C; F. r3 {/ l9 A& R) V
看看这个值时多少,如0.0312
( D0 o/ U& T* M U那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算& n( |/ U [8 x( Y
,这对于大表很有用。
1 |; H1 \, Z/ v# I; [& HSelect count(distinct left(列,3)) /count(*) as sel1,
9 Y. [( D# `# U# D6 w' q count(distinct left(列,4)) /count(*) as sel1 ,1 I& x) S8 }. y
count(distinct left(列,5)) /count(*) as sel1,9 x. W+ _# O( t( i9 _! _2 J
count(distinct left(列,6)) /count(*) as sel1,2 f/ y" q, i Z4 f. K @
count(distinct left(列,7)) /count(*) as sel1 from table;8 R4 A" {& j3 y, _/ a% n
找到接近0.0312即可。
: p& J- Q* f* L) ?2 g
4 |: N8 U9 d' _- g7 BAlter table table_name add key (列(7))3 G$ j$ e c7 }2 X' b
3,覆盖索引. R9 [8 g# [$ p3 M" N+ F
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
" R Z2 w8 G3 M8 I& D2 }1 G3 ^4 Wexplain时,extra中的会显示using index
, d, E' A! g; I2 u# c" d这里一个重要的原则是
/ W6 ?3 v$ H5 @6 ~select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
: O& w; ~7 o% d如select id from table_name;; n8 c- ^8 d2 a
& k+ ^; e7 ~$ ^9 q7 f4 J2 q- C
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不/ m i: q6 P8 T4 k U
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
3 Y9 h/ f/ d! y9 `6 p D r" D$ M6 x0 p1 ?
- Explain Select * from table_name where col ='nam' and col1 like '%name%';/ E# Y4 o& E" N5 ~( T* `) F! f
- Extra:using where
复制代码 该索引不能覆盖查询的原因:
! Z( ^, n7 V1 g& A1,$ _2 Y' S7 F0 Y2 G2 z# B
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。& k8 s- O1 u7 u* Z
2,
* Y1 i8 j; W5 K5 SMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
$ A! M3 z: E, L$ S$ ]' d有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:
2 K! r9 d- D; y& p$ q! E) O$ w! S9 d+ v& X- H
4,为排序使用索引扫描
) s8 F) t' I- u. o( K4 l$ g' h+ ?# @* \mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
4 `5 S1 R0 p# t5 v c3 u, Dexplain输出type为index,表示mysql会扫描索引
" V$ ^% Z; I# b
9 E# u% G. r# p' f4 G: S( V4 d5 B扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
! G7 Y% N- A$ z6 h/ R% r
3 [" p+ B. F7 G/ w7 `9 mMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
- g: l* M1 [# B% J3 D K) o6 a* `, J2 C0 k/ g0 w6 o
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。4 J1 O. v( \ @* X, Z
+ ]* j! E: @, N* X# {1 i2 K n
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
5 Q4 v& L% X2 n% y) B- j5 o. M- {0 z5 J( N' {
使用join可能情况会有不同0 W+ c, x) q4 x: T9 i9 t
7 p5 I5 j" m% _' l/ Q. Y5,压缩索引(myisam)* L- r8 {: j/ N7 N
6,多余和重复索引(应该避免)
; j# `: L0 K5 L* F7 i
( S3 ^, J! F4 e I% |2 b& z多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
- ~: k2 [: R/ ?) n上有索引,那么另外一个列(A)上的
" n& G+ Y. i) Y. s索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
$ ]9 c" A3 k' K9 w8 X' s. s& d7 e# R8 r然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。$ K' J- e4 l* g" S- u& P% r- H9 @
/ `5 ?. J* n9 g! P2 n% V要点:
; [6 p7 {( j3 O6 U8 O在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
2 w) k' Z- \ a% t4 ^
) C' l4 I) a9 u# J) v即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行8 `8 m& G9 `9 Q9 ~# X, U1 ^
- g8 O1 S& _0 X# f+ s- l2 [( }
0 d$ {+ n9 h7 h
3 c$ Q5 E9 E$ V( p9 m! c |
|