|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能+ D6 ^% E% |! C# D+ M% t6 W* q- Z
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
) J& ~" R( p+ `+ P一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列) n- V6 _; W8 Z- o
在InnoDB中,只有事务提交后才会解锁 j! }1 z j- j1 P
# Z5 t8 O6 D1 c# b' p
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只
4 m6 @6 R8 t. E. D" z6 @/ V能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
- O- y: E+ h$ S Z5 N1 w4 M, J" L) J# v, {
B-TREE
; g) ]( ]- X4 \: x$ z: ^ [能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
' l; ~1 T$ {4 Q* k& r) J* `+ e6 C使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。/ x: U/ O' ~0 L7 J+ G$ n9 @! Q& H' L
K1 u/ E9 j; m* G$ Q1 T- q- CREATE TABLE People(- ^; s6 o" x% R; H
- last_name varchar(50) not null6 ?5 m7 J! O. x, }+ k/ k' {% H% S0 u/ b3 a
- first_name varchar(50) not null8 |2 [8 }) U4 T5 L. G
- dob date not null
k( d2 f( k6 e* L' K& {3 S2 t - gende enum('m','f') not null
8 R( z" f& N9 g4 b C - key(last_name,first_name,dob)
复制代码 匹配全名+ Z+ M5 E9 \" ~ L5 ~( _
全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
- @. |% z3 U/ X8 U# t的人。 A6 {8 x- U" Y$ n" i
匹配最左前缀- ]8 N; g; `5 N' n9 w
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
+ L8 n4 s) \" g- [匹配列前缀
5 @3 C' L$ Q6 U* R. V @可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
: s5 Y. r5 x, V1 k/ A匹配范围值
6 @. h9 e! V1 f这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.3 D2 [, J" p3 F3 @- M
精确匹配一部分并且匹配某个范围中的另一部分6 I1 \: P; X4 j1 m, W' q
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
# Q; A6 Y7 [) Z0 ]2 z: a列并且对first name列进行了范囤查询。
- u0 `$ A& A- i2 O3 n$ B' s- Zname
9 I: k0 U" Z1 t9 C+ g只访问索引的查询% q+ @: M. ~8 G8 r( o# q; ?( [
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。' x: V# t% I& ^) @6 ^2 |6 f: C
2 d7 l, O' _- U6 G由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,- d# i+ s, o( d% A4 p3 x& g8 J
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
$ p( ~0 d) |3 q" T6 v1 k. [找方式也可以同等地应用于ORDER BY。
" k+ J3 L0 I* L
7 f" {. I5 p4 S下面是B-Tree索引的一些局限: J+ ^/ o+ l6 e8 {
" B; i8 |3 C2 i3 ` Z5 Y
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,+ N0 E3 y q( P) z v" a8 P
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓
# T" U8 W8 {- s/ w4 K/ ^; C氏以特定字符结尾的人。
- ]( i2 i# q8 O5 E( A- ?4 t5 q) d7 ?
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定3 a3 G9 C z& }
义first_name列的值,MySQL就只能使用索引的第一列。 V2 s# L( K* J/ h
& s. N# G3 @- ?, V
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是& T/ W& k4 F4 G# M- n
范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而4 }& L6 j! u9 `0 Y
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。2 E: v4 V* w* p, Z, V; H. a% R
8 c4 ` x; L+ O9 |, x% i# I, Y/ h哈希索引,空间索引和全文索引等,暂时没有设计' @9 d; B" d+ [4 I; j% ~) I
; z) i3 [# O, E- J) a, C) [" f# P
高性能索引策略
. u# m8 J- n( O2 g7 d
; M! p- I, t" X8 t6 x. R1,隔离列,意思就是不要对查询条件中列进行计算等操作
; _& Z0 D6 t) V! t2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引$ q3 C `. Q Z
Select count(distinct 列) /count(*) from table;
0 f4 \( P8 o, B9 i看看这个值时多少,如0.0312
! A0 v, N8 S+ A/ I; A9 s那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算0 y* p7 p( a' I* b4 R1 M: l
,这对于大表很有用。
* }2 s( K6 }1 a( c, U6 ]Select count(distinct left(列,3)) /count(*) as sel1,
" z% Y- g) [3 _% Z5 ^ count(distinct left(列,4)) /count(*) as sel1 ,
8 W% }) z- f7 q0 q1 q count(distinct left(列,5)) /count(*) as sel1,
8 n; n2 m# t5 j& [$ Z& \ count(distinct left(列,6)) /count(*) as sel1,
1 k( o. w) }# F. i/ d+ q count(distinct left(列,7)) /count(*) as sel1 from table;
7 ?: G# z. k7 Y8 d2 U" }% d" L) }找到接近0.0312即可。8 D( T% Y! }$ H E6 @- w
/ d+ E, Z3 k7 ^4 ?Alter table table_name add key (列(7))* W( g: c) U( f8 }" _
3,覆盖索引! d3 F2 I& i% D1 [. r8 ^
包含或者覆盖所有满足查询的数据索引叫做覆盖索引0 c7 ^9 a" R+ C$ S' w: Z/ ]* ~7 O
explain时,extra中的会显示using index) c5 D! _! `+ M# V, d7 E/ j
这里一个重要的原则是9 H# h8 |' p% r$ I
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
* B( a! v! u0 k$ B: A+ K如select id from table_name;5 }" F1 ?7 G9 X# A4 ~
) L: Q3 M9 k- o# T8 L
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不2 G0 Y, P+ f a, W# ]7 b" l9 q
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。/ a- T. F) [9 H
6 I: r+ F: A- q% @. s) l
- Explain Select * from table_name where col ='nam' and col1 like '%name%';
! F1 I+ Q( }3 z9 R4 ~3 @. M - Extra:using where
复制代码 该索引不能覆盖查询的原因:# ?# z5 w% X4 d
1,; W9 v# E7 ]8 @7 f, O# A% r
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。3 v/ h/ e; n5 o6 Z% W1 T7 P2 h) L
2,3 t$ q! o5 v3 L6 ]
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
- C4 ~8 P: n; r3 F, t% G3 T# `有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:/ ^9 S7 k7 p, ^0 y7 h+ p' [0 N3 N
' T0 h% _$ U6 M4,为排序使用索引扫描8 H' ^. c l% A
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
/ w4 ]! P# d c: H' Eexplain输出type为index,表示mysql会扫描索引
- [& {4 _, u/ Q
1 z/ S' r9 T. S, g9 k1 Z扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.& K% p* @2 d( @0 d' R
. E" ~3 x. l' xMySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。9 r" \2 Y, \/ R4 Y# Y
- L6 `. Q. V5 a( i' j% p: w
按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
# F* n2 W7 N) C8 G
7 a+ Q4 o, N7 Y5 E/ {ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。5 t7 T p: H/ K7 t. z, D
) D, m7 P7 ~4 B3 X8 }% C使用join可能情况会有不同
: f7 U. s! N5 p0 e
2 }8 k4 R" N+ @. K5 z9 b5,压缩索引(myisam)! Z0 |! n! c( T
6,多余和重复索引(应该避免)3 x' W+ v2 {% f" |
' q, q- K# K; }) b
多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)' `0 }9 q1 D% t3 |. P
上有索引,那么另外一个列(A)上的& A w' U! ^! w0 C+ j; Z# v
索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
: O+ F" s/ c0 n# V. r) l/ T U然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
) L. i8 f0 M. K# Z
8 q: k" U4 |0 x0 X- w4 {要点:0 S' z! U9 m( C8 P( Y
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.6 e; l' X) K, v* h
5 k; \3 O, n0 U即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行
/ P6 J0 S5 a3 J2 R; c7 m/ J" b9 J) R8 Y+ I0 w
3 X( u0 w+ _! o0 }- Q- o3 d+ p6 \+ `
|
|