|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能7 ?! h! p m+ k& i. P0 o! c$ H
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成9 A# I% A; p$ F0 \9 I
一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列
! u; X0 }' T2 I0 J, s; r在InnoDB中,只有事务提交后才会解锁. e) V6 z6 r) \+ U6 S
# U% f' j, Y- Y; n
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只1 T$ ]1 P0 R7 T F9 }
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。
6 A b8 w6 H0 D9 d. E7 R9 N6 w
: O3 |- J/ R% l* [2 K. d4 F' y6 \B-TREE- |. S6 k; J0 R& @; g
能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
; F; `: V( E8 c; K使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
W/ v, }) s: U/ D( D3 n/ e* i
9 f8 W+ S0 ~- h2 Q, R. I- CREATE TABLE People(
& E/ J# f, J# x0 I - last_name varchar(50) not null
0 l# ?! L$ E0 s4 L - first_name varchar(50) not null# D' q% @2 |; ]4 b
- dob date not null
/ X7 G% X* Y2 J& m5 E4 s4 p - gende enum('m','f') not null" a0 @, {( c+ g9 F4 M# \* |9 G
- key(last_name,first_name,dob)
复制代码 匹配全名
* D# h& y) ?0 A1 @! j) J% P/ W全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
$ @- N. Z1 t: f5 L$ T" g的人。
- _5 F$ O l3 k) w, Q/ m4 ?9 r匹配最左前缀
, G9 |% x6 u. eB-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
+ \" ^# O% ~& X匹配列前缀
3 M; M( K: {! j: \可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。& \# ]8 i8 o9 m$ [$ i$ F
匹配范围值
- o5 ?; o7 C8 R这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.6 E4 }' ]7 l& H, O) E8 V+ y' c1 F! w
精确匹配一部分并且匹配某个范围中的另一部分
# ^8 s! W9 B3 a5 y" u: g这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
. x0 D. D& E6 K# L- ^列并且对first name列进行了范囤查询。' I6 ?5 T$ w9 Q+ Y, ~
name* ] l _" k- E
只访问索引的查询7 ~( M. ?" e- m9 |3 ^
B-Tree索引通常能支持只访问索引的查询,它不会访问数据行。- ^; S9 d4 z2 A5 ^( d
3 ^1 K0 w" e" h& d由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,6 J5 x$ z7 _2 ^
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查7 |3 d! ?# a h/ Q( y9 }& N* R
找方式也可以同等地应用于ORDER BY。
; \9 F% C( s% K3 _# a5 { [; t' o9 j4 K7 T' U
下面是B-Tree索引的一些局限:4 q3 T% V+ F f2 W' Z) t$ O
" z2 R+ e: J O; \: \1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,
- S7 f- a, O! k) [ n1 p7 v也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓, ~" O X) _& _
氏以特定字符结尾的人。, q9 U* x9 O3 z" [; N [$ @
x: A9 m, U* ~
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定1 l5 h7 |4 F4 ~, E* l
义first_name列的值,MySQL就只能使用索引的第一列。+ f" z/ O; }$ p( E! m
7 G6 l A& C$ ^3 I8 l
3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
0 ]: m8 d9 F4 f M范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而. w/ W/ a7 k% P F1 U. x
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
; v0 G/ u( {8 f0 i4 I; E9 m7 Z3 P0 P3 I1 u3 ^" H- I7 e% M. }1 e7 c
哈希索引,空间索引和全文索引等,暂时没有设计( x3 v' }# z3 ~
0 z4 X9 m8 u6 q; s8 ^( \高性能索引策略
' Y' P/ g. S$ k, b0 [& `( x7 x+ ?% T5 z w
1,隔离列,意思就是不要对查询条件中列进行计算等操作; @4 M/ U! A% V: b2 o8 \: j' _
2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引7 v& S* u9 u. M
Select count(distinct 列) /count(*) from table;8 N X7 \" I3 y1 E4 N. ^1 |) v
看看这个值时多少,如0.0312
! ] m$ }* t2 T+ w那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算, @( y1 K2 i/ d6 {
,这对于大表很有用。
7 E. ]- D8 r( v- hSelect count(distinct left(列,3)) /count(*) as sel1,
; e/ g; E# U8 E7 J count(distinct left(列,4)) /count(*) as sel1 ,( h4 r5 [$ g$ i0 e- [3 ^
count(distinct left(列,5)) /count(*) as sel1,0 D, [3 F" S. M3 |6 E+ t
count(distinct left(列,6)) /count(*) as sel1,
; }* R# E' N* f) T2 U) Y. D$ B& n count(distinct left(列,7)) /count(*) as sel1 from table;2 f6 }* T9 A* `- q2 o
找到接近0.0312即可。
; c) l9 |; Q0 D& @* E
& r# v& _ T7 G5 X; c: X1 MAlter table table_name add key (列(7))
) G" k3 s) a0 j1 K3,覆盖索引" ]7 R9 H/ U! M4 n* p' L
包含或者覆盖所有满足查询的数据索引叫做覆盖索引' }6 s' o& n2 C+ R* ?3 e' z
explain时,extra中的会显示using index2 `2 T7 i$ _' d
这里一个重要的原则是# ~$ l5 J7 H% P' b
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列6 ]. [# h \1 `( b) }
如select id from table_name;
( O6 ]1 l( g5 G0 s2 L N/ L3 p' c7 ?+ `6 H _3 B
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不0 D% l, E# w Z- t. K" |
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
7 G: f% ~* [$ g6 ?9 ?
S- u6 d; k+ h! j4 b# ~; @+ c% {- Explain Select * from table_name where col ='nam' and col1 like '%name%';( ^; |' Q" ] {# T1 G
- Extra:using where
复制代码 该索引不能覆盖查询的原因:7 q& q4 ^" I5 c; c% K6 ?
1,. j+ G" M, B* x( R+ S a" X- b
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。4 J# I" F% v3 C1 ]+ t
2,, P5 m/ N! v* m6 { P. D/ ~- @
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
( B# J! U$ x% O: }8 O' a; W) @3 `有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:9 w0 b2 Z7 V2 T2 v
$ s# \6 d- z' m: |4 O* x4,为排序使用索引扫描 z" B: ?* I6 R2 a" Y
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。1 q4 U6 Y9 \6 ^. S% L
explain输出type为index,表示mysql会扫描索引 m5 v$ k) \9 G4 [3 Z& r
! i. W. Z$ V( f! k$ y
扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.: \1 r( [9 T" v/ Z' W' k( T
4 D) [) T8 ^% Y6 D$ T
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
) Z) i5 V* m! U4 ]4 m
- L1 G0 h' C* h: I# d9 i按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。
5 {: \7 w- Q1 y; U; B% P# G( ]+ t* @7 x# H" i1 K
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。0 \/ ~2 z- ~ K3 b; w
( ~* ?: Y6 B) A. b" z2 r
使用join可能情况会有不同
# C) X4 [$ n8 J
$ j3 X$ n0 e& c' u: F8 N5,压缩索引(myisam)6 k4 n) f1 O8 i, g9 ~4 ]9 j# U. ~
6,多余和重复索引(应该避免)0 @6 t3 ^( l5 S0 [0 k0 O
+ F+ ]# B# @+ o- k多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
$ k1 E1 r0 i M上有索引,那么另外一个列(A)上的
5 ?$ W8 H- B( }* w w+ n索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)& H) P) e: O) u, p
然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
2 l5 h" k$ ^0 E+ P
' c; i# i) A" F G要点:1 c6 [. i, v6 |( y' w" @3 x+ m
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.
3 D' ?& |6 g7 E8 ` H! b5 Q$ o& d6 f1 z* P# t
即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行5 r" z6 E Z. Z8 l
$ S" m7 @! J$ X+ w1 o( d
# j' @; t* }* p* e9 Y. A y
+ w5 |4 G# h4 t) ?$ R" \9 b |
|