|
|
索引(MySQL中也叫“键(Key)")在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能/ [. W1 h; U$ f0 f1 w% d! i
有好的性能,但是当数据增加的时候,性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
P9 m. B; E4 r2 P. c' w! r+ R6 s& R一本书。为了找到书中一个特定的话题,你须要查看目录,它会告诉你页码。索引会让查询锁定更少的列4 @7 N# l9 w1 g8 F
在InnoDB中,只有事务提交后才会解锁
3 P$ ?2 w3 j I! L3 N$ @0 Z1 @# c4 V0 x$ A
索引包含了来自于表中某一列或多个列的值。如果索引了多列数据,那么列的顺序非常重要,因为MySQL只* j& d6 \' y- d! n# p+ g
能高效地搜索索引的最左前缀(Leftmost Prefix)。如你所见,创建一个双列索引和两个单列索引是不一样的。) L: h' A, \* N7 O4 N2 c
. O/ q2 F) j# e0 f/ _, DB-TREE
y; V4 Z n2 M" ^8 M F$ X7 L3 J能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
7 Y! [! {& I0 ~+ Q+ I) f使用了素引的最左前缀(Leftmost Prcfix)的时候才有用。上节中的索引对于以下类型的查询有用。
/ j( j2 C& m/ H y* ~
( B2 A# f0 O) o; d" N- CREATE TABLE People(
3 l& v: D I" A% M - last_name varchar(50) not null0 ~4 q; K7 k/ o" f, g0 d6 V
- first_name varchar(50) not null( Y- W8 Y" ~+ }; I" A
- dob date not null
2 o' b1 a# z5 {) S9 t R \& j - gende enum('m','f') not null
& k2 ~- T- V3 t4 y - key(last_name,first_name,dob)
复制代码 匹配全名
8 w' f: X7 F/ S9 N全键值匹配指和索引中的所有列匹配。例如,索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
2 o$ s* k4 B9 b& g的人。
; D, u# ^1 J- q: d# B匹配最左前缀6 A8 O& O9 L: |, f8 |# a& I
B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
' f3 O; W0 ?* t$ u匹配列前缀
" ?" n( L# \# R可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。0 `; x* [7 @& t8 A+ D4 g
匹配范围值
/ c% K; o" K% |* j' L* U这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列.
% g/ H9 D7 @+ c. R- N精确匹配一部分并且匹配某个范围中的另一部分! q4 ]& W3 l3 f* o: M% g! B
这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等)开头的人。它精确匹配了last
1 Y! I) A; ], E( v1 @7 m) r- W+ D列并且对first name列进行了范囤查询。
( q) |# y. l) kname
' ~, V" l- `) ^! k% v, c2 k0 M只访问索引的查询
g e2 \- n! L7 [- C6 v1 SB-Tree索引通常能支持只访问索引的查询,它不会访问数据行。 {+ ~ [1 q, B- A4 w
- K* Y7 O: K! a* c由于树的节点是排好序的,它们可以用于查找(查找值)和ORDER BY查询(以排序的方式查找值)。通常来说,+ F# F% J( x" \/ z* ?
如果B-Tree能以某种特殊的方式找到某行,那么它也能以同样的方式对行进行排序。因此,上面讨论的所有查
- f$ f3 {! w: `- ]8 U# s+ R找方式也可以同等地应用于ORDER BY。
' D& c" _& ~, a; d0 u: F8 d, P: d. E! i' @# ]
下面是B-Tree索引的一些局限:+ E! M. T) A4 M/ x! G0 \; J" N
. o) e) w" J, |, S' s- }8 F! g- Y
1,如果查找没有从索引列的最左边开始,它就没什么用处。例如,这种索引不能帮你找到所有叫Bill的人,7 _& x. u6 B, ]8 S% p9 c2 R
也不能找到所有出生在某天的人,因为这些列不在索引的最左边。同样,你不能使用该索引查找某个姓3 g+ |8 I. h- D) P7 T
氏以特定字符结尾的人。
1 v# ?& Z" b2 L: ^9 @' r! z. R6 [; V4 B0 y
2,不能跳过索引中的列。也就是说,不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
* `% M0 ~& d) a( l+ T7 G义first_name列的值,MySQL就只能使用索引的第一列。) Y. ^' g% i: m- Z5 ^9 H* ~
0 Z& k3 w3 i$ a' @2 x+ b3,存储引擎不能优化访问任何在第一个范围条件右边的列.比如,如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23',访问就只能使用索引的头两列,因为LIKE是
6 V$ w$ S0 ^6 N+ Z$ t; D范围条件(但是服务器能把其余列用于其他目的)。对于某个只有有限值的列,通常使用等干条件,而 L/ _* \% O$ u, k- o) z) P0 I
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。
0 i# e3 C0 [7 o: }, P3 x9 j3 e) s4 {& A
哈希索引,空间索引和全文索引等,暂时没有设计
5 g- K/ E6 g0 Q: G# b5 R8 L% C/ d0 ~6 a- J& k& s3 k4 l
高性能索引策略
6 {) u6 {: R1 b G; k7 [. J
# R5 T+ A$ U4 V& d) q! s1,隔离列,意思就是不要对查询条件中列进行计算等操作
7 W; ?' W: Z6 S; C6 X, P- K2,前缀索引,针对blob和text,较长的varchar类型,使用前缀索引
% R6 I! }9 c7 K r% h9 X' f, f, ISelect count(distinct 列) /count(*) from table;% d" } q- R0 h- m( Q3 W) v
看看这个值时多少,如0.0312
& @- U1 e& P9 `+ z4 H3 [那么就是说,如果前缀的选择率能够接近0.0312,基本就可以了。可以在同一个查询中对不同长长度进行计算
! O6 Q$ y/ y4 o3 Z2 q$ D) h! O,这对于大表很有用。3 E* j1 w) O9 ~$ R" [: Q, b
Select count(distinct left(列,3)) /count(*) as sel1,
L" ^2 }9 \0 D1 w' b count(distinct left(列,4)) /count(*) as sel1 ,
% ^. H" c" V" i2 q/ Z8 N count(distinct left(列,5)) /count(*) as sel1,: c H3 D1 s" N, X+ T- a
count(distinct left(列,6)) /count(*) as sel1,+ f5 }8 W8 z/ h3 @% _9 r
count(distinct left(列,7)) /count(*) as sel1 from table;
J& r$ G# y( P! \8 g1 ^+ N7 B. A找到接近0.0312即可。
, V7 V# s" V8 o
: B1 O/ _' i' m! a9 AAlter table table_name add key (列(7)), Z" ~' }1 ], C0 @& ?8 U8 `
3,覆盖索引
, O0 Q8 I. T# h( o- i2 K包含或者覆盖所有满足查询的数据索引叫做覆盖索引 K! b0 C& q9 t5 U
explain时,extra中的会显示using index
6 Z% ?& ?7 l/ S0 R, K1 ?5 K3 C这里一个重要的原则是: [- z+ ?' N. y, t3 o2 ]
select后面的列不能使用*,要使用单独的需要查找的列,使用带索引的列
$ i3 e- V8 ^' j# ]) k如select id from table_name;
1 j( M9 E, r: h/ X7 ~5 c3 j- @9 f8 c1 L+ F$ P5 m; r$ q6 c
很容易把Extra列的“使用索引(Using Index)”和type列的“索引(index)”弄混淆。然而,它们完全不5 N( {0 o! M3 @. M! |' W4 }4 y
一样。type列和覆盖索引没有任何关系,它显示了查询的访问类型,或者说是查询查找数据行的类型。
5 c* P# s# N. { L
, b9 R3 T: X0 P4 e/ ~2 Y5 D& e4 ]9 \- Explain Select * from table_name where col ='nam' and col1 like '%name%';
7 j+ Y5 f6 A* Z - Extra:using where
复制代码 该索引不能覆盖查询的原因:
- L0 j5 i! Y; S3 R1,0 J! o+ W, e1 T/ V' p3 u7 _1 ]
没有索引覆盖查询,因为从表中选择了所有的列,并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用,但是,WHERE子句只提到了索引覆盖的列,因此MysQL可以使用索引找到col并检查col1是否匹配,这只能通过读取整行进行。4 g0 t: J F8 L" O- D& `
2,* h) p( m1 ^, ]
MySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制,它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较,但是查询中前导的通配符是存储引擎无法转化匹配的。因此,MySQL服务器自己将不得不提取和匹配行的数据,而不是索引值。
+ U9 E V9 g2 ^7 I5 [8 A有办法可以解决这个问题,那就是合并索引及重写查询。可以把索引进行延伸,让它覆盖(artist,title,prod_id)并且按照下面的方式重写查询:, F( J9 S6 B- w( H, ]7 W
2 A- v! k) w7 J- m" x7 ^2 G* f- @! i
4,为排序使用索引扫描2 ~& O9 G z# _* @% Z4 J2 D9 [* Z
mysql有两种产生排序结果的方式:使用文件排序(fileSort),或者扫描有序索引。
) \/ G0 ~7 |7 Y$ ?* Zexplain输出type为index,表示mysql会扫描索引# z1 ? P, W3 \2 r
1 w5 [5 O8 Z2 W; G& w$ x7 F1 L扫描索引本身是很快的,因为它只需要从一条索引记录移到另外一条记录。然而,如果MySQL没有使用索引覆盖查询,就不得不查找在索引中发现的每一行。这基本是随机I/O的,因此以索引顺序读取数据通常比顺序扫描表慢得多,尤其对于I/O密集的工作负载.+ H% C% Y2 a& H) L
; B8 `8 V; E3 n. A- M
MySQL能为排序和查找行使用同样的索引。如果可能,按照这样一举两得的方式设计索引是个好主意。
' b9 H& j) F% u/ m- V
2 o: \" L! `+ k2 y6 a! R; [. D' t按照索引对结果进行排序,只有当索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(升序或降序)一样才可以。如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限:它要使用索引的最左前级。在其他所有情况下,MySQL使用文件排序。% I$ _! r4 j, @" u5 G
- j6 g0 O' X, w% S! S/ F8 }/ d
ORDER BY无须定义索引的最左前级的一种情况是前导列为常量(也就是说第一个索引不能是范围查询,如果是组合索引应该以此为常量)。如果WHERE子句和JOIN子句为这些列定义了常量,它们就能弥补索引的缺陷。
) V; N# N' o; Z2 Y- h! C0 N8 k( ?8 o I, k. \1 T8 t$ G A
使用join可能情况会有不同! k/ p/ B6 R# N" s" x3 T
1 [6 U5 ^; o# m7 }% j
5,压缩索引(myisam)
" e6 v, ?; R/ {! ?0 F8 a/ F% }6,多余和重复索引(应该避免)
$ J& T" e/ E. p
+ n2 |5 D- ?/ Z+ u' f% S4 F& N q# r多余索引(Redundant Index)和重复索引有一些不同。如果列(A,B)
9 e" v; D: D9 O$ ?上有索引,那么另外一个列(A)上的
- H4 u; E8 l1 Z* a- y索引就是多余的。这就是说,(A,B)上的索引能被当成(A)上的索引。(这种多余只适合于B一Tree索引。)
; z: {0 V6 r6 d/ O1 |然而,(B,A)上的索引不会是多余的,(B)上的索引也不是,因为列B不是列(A,B)的最左前缀。还有,不同类型的索引(例如哈希或全文索引)对于B一Tree索引不是多余的,无论它们针对的是哪一列。
* s6 O2 b( ?8 r7 x: p
' [! N0 r+ u0 {7 z- x, u7 D要点: l0 E0 w/ G, U
在任何可能的地方,都要试着扩展索引(之前是一个列A上面有索引,现在两个列A,B上建立索引),而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能地使索引变得更有选择性,因为高选择性的索引通常更有好处.3 a5 m: n9 J6 } a! a2 K i
0 H& [$ b7 c8 T/ ^& @' |4 [即使InnoDB使用了索引,它也能锁定不需要的行,这个问题在它不能使用索引找到并锁定行的时候会更严重:如果没有索引,mysql不管是否需要行,都会进行全表扫描并锁定每一行& I8 v8 Z# u/ ^; Z
5 M9 z$ j. {& G
' b, x$ L k8 q% }* i
0 J* Y8 W& [* W- z; k# K* } |
|