|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能: l- T0 V% x& ~1 G& @% U* y
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
( D6 e+ X7 X! _ G' ]6 W一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列! N2 p5 J" l# U' M! B
在InnoDB中,只有事务提交后才会解锁
7 W& T$ S+ \: N7 ^
( h+ r% Z; s8 [4 }9 S; L% S/ M# o& U索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
k6 |' K, U3 v3 ?" Z# P能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。$ D7 e! A& w6 f0 P$ t/ E' c& ^% m; E
5 I9 Q4 w& B- F; ^! ]3 ZB-TREE
* W! C1 g6 k) L% f3 X0 O+ e/ J能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
" e5 T" Q: e8 A# a使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。# R3 K& ^. {3 B3 z3 k" ^3 i* l
- . m" `' k5 G% L! k8 v
- CREATE TABLE People(
! c" d D0 D; m4 x# w1 z' f. } - last_name varchar(50) not null
+ Q4 y0 [' P' A( P - first_name varchar(50) not null |8 Q4 O# P* s/ D Q/ \% Z
- dob date not null) [6 q8 `5 D( q- J' u2 m
- gende enum('m','f') not null; s( p! g- ]9 H! J: g p8 |
- key(last_name,first_name,dob)
复制代码 匹配全名
* p# C3 v- B% ~/ l; }全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
: K+ n6 t4 E9 j- h. N) ~: g的人。. v4 n6 G' B1 N d( E
匹配最左前缀
( ^1 {9 |. J* i* [- {B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
; M4 j: F* d3 N! `8 a) F# B匹配列前缀6 @" p) U8 p ]* p3 G
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
/ L5 b9 d* F0 F! V* D3 `+ j1 @匹配范围值# B) \5 V5 q3 E% h5 _0 t# z
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.$ F ]/ X! `9 M5 B+ h# q
精确匹配一部分并且匹配某个范围中的另一部分
- e: `) {' _/ g这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last) U& X* O( j2 X& a
列并且对first name列进行了范囤查询。
# D0 x5 ]/ K9 l1 iname
& M/ c$ E# G' \/ B, q' d3 O只访问索引的查询
; ^( e0 n) ?8 {: g2 w7 KB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。0 U3 g3 C1 `& ]. F! i; z( \# c# W
, b6 U! p l$ I K- L
由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,; e+ w# x- K' `4 g$ N# _
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查" d7 f0 v( M2 v9 m. S
找方式也可以同等地应用于ORDER BY。; v* `% F1 n2 F" V8 ]/ e
Y/ a& G8 G4 Y3 g* I
下面是B-Tree索引的一些局限:9 Z: ]8 A4 W; P0 g4 Q: |/ X
- s9 i6 f' T3 ?1 x1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,3 ^. h! F) s, Q4 T- l! z& X9 G
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
; e9 Y/ Q5 w# a1 J- u5 J氏以特定字符结尾的人。
9 d! p2 _1 |2 w& K, [2 `
! ?6 j# R3 S( w2 p2 m* Y! e2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
; v6 U& ^9 a, X& n义first_name列的值,MySQL就只能使用索引的第一列。# b) o) ^, b$ L6 C! D$ a7 n
6 p2 h6 d3 ~8 N; ~1 L8 f
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是- ]4 }3 `: B9 N. a0 J% N4 a" S& U
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而
4 B" I- @1 c9 r不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
2 x. C5 Y* _0 C: h# G9 |
( g% d2 e2 K: V) r9 [8 q1 r哈希索引,空间索引和全文索引等,暂时没有设计' @) [3 Q9 U4 {! p) _
. M u" M) L1 i4 J' c$ P; d
高性能索引策略2 u8 t- y/ c/ L# j/ }2 ~
: @, A- j$ _" Y+ d
1,隔离列,意思就是不要对查询条件中列进行计算等操作+ U- _( ? k, S/ P! G5 X
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引7 p" I, W s. f4 |/ [
Select count(distinct 列) /count(*) from table;
: u$ w, N8 O% T3 S0 P看看这个值时多少,如0.0312
- r& i, e3 r+ Y7 {& a那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算: J# u( V# h" U( v3 i: t% @& s% S+ `
,这对于大表很有用。
F4 s4 l- B) E' e+ ?8 d5 Q5 qSelect count(distinct left(列,3)) /count(*) as sel1,
5 [2 Z" D2 D7 N2 r3 l a- K: ` count(distinct left(列,4)) /count(*) as sel1 , X6 u: e, _ S
count(distinct left(列,5)) /count(*) as sel1,
) M, ]- |* r; D1 U; T count(distinct left(列,6)) /count(*) as sel1,
% C1 g- X o* D/ |$ L, }! e count(distinct left(列,7)) /count(*) as sel1 from table;5 k+ B, r; n' S9 X& `
找到接近0.0312即可。
4 t9 q) ^ ~! [; t: }1 |
7 m% |# u8 \7 @, U e- \Alter table table_name add key (列(7))8 u/ Z: w' T5 W+ i
3,覆盖索引4 {# r4 E. p- [" Q4 C
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
! U d, Z% O* c- Iexplain时,extra中的会显示using index
8 b1 w; E2 y; s' p% A" }* ?这里一个重要的原则是
4 E3 N; a v* D+ ^: y, \select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
% J0 C- U9 }, {9 B如select id from table_name;2 d& R# o3 a& s2 t k0 \. Z
8 K9 S2 i( R5 f+ N7 O, [/ u' q
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不4 X/ t5 j \( L. R r
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。: a" r0 U! P7 H2 K2 y; J& X
& W8 a B# k) R V0 k7 n. U- Explain Select * from table_name where col ='nam' and col1 like '%name%';
' c; X; A" h n8 h3 l/ c& X& R - Extra:using where
复制代码 该索引不能覆盖查询的原因:
% G* a2 g+ E& H+ L+ K4 a/ T1,
; E4 e* \; }9 K; q1 ?! C没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。' d' I) w6 Y, k. Y2 i) ?
2,
0 R d+ o! l6 r& D4 zMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
/ r2 U/ A6 Y' M- _! j- _8 S有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:% `& q5 V" C9 w: w, d5 |
& {, b2 u3 J; r# M4,为排序使用索引扫描
5 ~' Y4 ^% S! `) m% _4 u% l- Mmysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。- {# ?8 p# O1 s: c
explain输出type为index,表示mysql会扫描索引: m$ _. i# A1 Z3 b& |
i/ Y d( g9 Y# e# T1 G: [扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.
0 L; W) a, r. [2 T
: u% g: T9 f8 r) a# OMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
4 o1 O% ]6 `, V- @2 g( j( q; i* @) J( h; X* K! K
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
6 K$ z2 {2 {" i4 m3 Y, o7 R
& m( h( Y7 d9 b3 QORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。% Z: h0 G3 }1 N
) d# _0 V& r; t- `0 f1 A7 y T7 d
使用join可能情况会有不同
5 z- u% X0 C8 s/ ?- t
* o: J8 i' H9 @3 ~# ?" G5,压缩索引(myisam)* U/ e+ [% O7 {7 b; ~7 j/ f
6,多余和重复索引(应该避免)
9 z9 e4 y# y' J$ n" y& A" ?, K, h6 e* ?4 Q* g, W8 Y* f) b
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)2 x5 Y4 h2 | `5 R6 Y
上有索引,那么另外一个列(A)上的
0 u' x& r! S0 u1 ?; I索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)! Q( B/ d; x# K/ a( u
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。* m3 L) t; ~8 m5 k( x0 e% d
3 x/ d" m% W2 [, h( Y( W+ n/ h" y# L要点:
e9 i U6 R! T7 Z7 M+ P1 [在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.0 V' I+ T8 V' l
( ~# w! C% {6 |8 B即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
0 r, A# Y7 s# e; ~- I; V a9 e5 @- W* N- Y# z
! a! d: [1 g! f z. ]' u6 k+ b7 I% v
! n7 V' R# K. l |
|