|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能# r9 y* s4 C& Z# E# ^
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成) ?8 E/ B; }' _. u E4 G8 e
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
' F# T* ~+ d7 w+ n3 e$ k在InnoDB中,只有事务提交后才会解锁
* _1 ?' `1 m4 H4 F. X. q6 D* U" ?& S5 F% q. [
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只+ v/ q/ _7 l( S* V
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
$ W* p1 K5 }" K
/ P2 u: s( t1 | ^8 UB-TREE! S9 {9 q k; z- }/ G
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
- J+ ~# N3 D! j" ~% A使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。8 c6 y% H! ]4 z$ c
- " B: x( k' K& {3 Z7 ~/ a: ?
- CREATE TABLE People(2 u4 x, p" \* _$ g0 t: {
- last_name varchar(50) not null
5 H) k! v5 W {5 S: Y9 c - first_name varchar(50) not null" t: i2 Q P/ D1 R5 n' P
- dob date not null+ o( L" Z8 \& W) D s
- gende enum('m','f') not null
: t" g3 i( z7 s) }- ] - key(last_name,first_name,dob)
复制代码 匹配全名 d' A9 n1 f5 h
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。3 w1 V4 m2 z5 a* I4 a& }
的人。
+ y, ?2 ?, _9 V4 T0 }* m. [2 h匹配最左前缀, k: J8 p# S0 |( y2 ^4 D( Z
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。" t8 W5 t; F& | I4 Z/ E
匹配列前缀
0 v# q9 Y1 [' u7 O" E可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
/ b; f. T4 z6 b& Y# ^# ?3 `% M/ D3 E匹配范围值
{$ Y [3 E, N* u9 p& V这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.( {/ E6 U. U+ H; `
精确匹配一部分并且匹配某个范围中的另一部分' @" r) ]% s# C! p3 p0 E
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last- @! ~9 o, }1 ^5 t4 @+ g
列并且对first name列进行了范囤查询。( Z! @6 O/ B% U
name2 Q( L4 W9 L5 }
只访问索引的查询# D) ?9 F/ t( Z' \- T' x7 l( o6 \! p
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。
5 S% [/ t3 s5 ?9 X( {$ y
6 w' w( F6 B, _ ?- `1 ^由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,# p8 Y. H) x& d
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
# \2 Q3 a# N/ }- r. w+ `2 n! d找方式也可以同等地应用于ORDER BY。
& P3 ~: Q1 q' j& n9 L, J6 N! i: R
下面是B-Tree索引的一些局限:
8 f8 c. Q! _: \1 z$ i& _. o
^& |9 U' J1 b) p8 Y: e1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
2 L2 r% \' M. z* \" D也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓2 x7 L4 k2 C% g0 ?& W2 k7 _
氏以特定字符结尾的人。
4 I4 W- d/ v( n Z
/ q% i# }) ]1 e0 R8 ]& J2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
5 n, S; S1 F' z/ ~( l: Y G% |义first_name列的值,MySQL就只能使用索引的第一列。% V. t7 }) j. N' {+ V' t+ h$ F
% z& g7 J6 N. I2 I6 k) l3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
3 r0 `3 r! n$ a- N9 K" \6 @- e+ s2 h范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
; B. f! a; U! @& @$ O不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
" h* k- W( D" l4 g$ C$ i& g
9 c" T4 X% S: H8 p哈希索引,空间索引和全文索引等,暂时没有设计
: I! ]/ I* p3 w5 @& L# C) K- Q! b! C' y3 S4 k
高性能索引策略
6 u+ O% u8 i5 W! x7 Q! s
) ~* T) Q: X3 {1,隔离列,意思就是不要对查询条件中列进行计算等操作( u' d& n2 Z1 ~7 W
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引0 A! u" j3 ?" r0 \2 t
Select count(distinct 列) /count(*) from table;7 y8 N7 E0 s& e
看看这个值时多少,如0.0312 l: r: D8 Y0 p9 L% U5 A, H# |
那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
- C. [" x5 g! Z i( i' }. z,这对于大表很有用。
# G7 J2 T& y! w( qSelect count(distinct left(列,3)) /count(*) as sel1,: j* N; \2 B. s8 P1 p
count(distinct left(列,4)) /count(*) as sel1 ,8 @! G$ L# k; U# b3 W9 {: `! R1 L
count(distinct left(列,5)) /count(*) as sel1,! w9 \4 m- I( S/ @) \4 C
count(distinct left(列,6)) /count(*) as sel1,/ y! s' f- U3 e `4 T
count(distinct left(列,7)) /count(*) as sel1 from table;
' v" Q2 [+ e0 ?找到接近0.0312即可。
4 q+ f! A7 r% k2 `2 a: j8 z- S$ D6 B0 T0 m, B* I) P
Alter table table_name add key (列(7))2 o" f5 v7 u! ]3 b! m; Q* r: B
3,覆盖索引( r5 _$ f, D8 Q3 w2 b( s- n; T$ b
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
# e: R1 u0 o/ Q: D& v' }1 }$ i9 Bexplain时,extra中的会显示using index" V4 ?( n7 Q( e4 `" Z1 [, a+ {
这里一个重要的原则是
$ o! A* E, o& r4 k! Qselect后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列+ t8 U- C4 D2 \+ ?8 V" I
如select id from table_name;
# U+ W9 U* Q7 E) j
, ^% l8 Q4 W ?3 x( v& b. j+ ?很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不# P0 m0 q5 e9 U$ v y+ m
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。- W% ~$ L2 c3 e" A% ` C b
% M' K- U& \ U* n9 e& Z3 {
- Explain Select * from table_name where col ='nam' and col1 like '%name%';
& T" I. b' P& E5 m4 w* `' r - Extra:using where
复制代码 该索引不能覆盖查询的原因:
# d7 \* q' |! z9 F+ \4 [# ^0 U7 R1,
8 @' D Q0 ?4 n+ E没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。
) Q. i5 i0 V2 @% J/ W B2,0 y, J) ~! F& {% x2 ]# {* w( w8 t
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。 i0 ^* X" J+ o0 |4 ~( l
有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:6 ]3 K D! U. R0 \& C' P
# z1 y1 }- P/ h
4,为排序使用索引扫描
8 D3 P; m* r- ~mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。2 `/ L6 g- R# E8 F5 @1 z( ~+ n
explain输出type为index,表示mysql会扫描索引& y2 \# i* t0 O/ ^& ^; P3 a) c
* {1 L8 u, ?: E2 c
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
/ \# H1 h7 h/ f' y4 k
# Q/ Z" U+ C1 L; l! j1 ]MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
7 V' J* j3 F' W; ?9 P# B/ ?
% t n& U$ R; {) | s2 o' v8 n按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。9 x- S: `) Z% r C* D
; y. L6 G+ J/ m; q( Q5 ^- A" U EORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。/ u! c0 s* ~. f' s1 V# I* I
. }4 q/ Y7 j- P
使用join可能情况会有不同# f' K( {8 E2 y
# U* F7 h# o& N# p5 K1 _6 v
5,压缩索引(myisam)
2 n, j# ^1 G/ q8 a6,多余和重复索引(应该避免)5 K: y9 j8 S( r0 r
. J- H% w, N6 i* P4 {
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)' g8 w& @4 L1 ^/ P/ T/ T( E$ f( a
上有索引,那么另外一个列(A)上的, F* Q! w3 `9 b1 _# }
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)( [. i- V! M, r. N5 H
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
3 e. K& U% U# t1 n$ p0 m9 g0 ], q
要点:1 U$ z& a" {9 ^0 E' ]4 M' w
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
# J3 I; b+ }8 i: I3 h
: c! T% A3 e4 @$ C3 q即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
4 |, y$ l2 D ^0 y
: S$ z! Y1 E+ h- K- Z
6 ^; f5 G* x+ T, Q) h& [: z P, |
7 |# k4 C+ M' C, u: _: @/ q5 X |
|