MySQL索引详解和优化技巧

myskya

索引（MySQL中也叫“键（Key)"）在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引，也能
: I& A. Q$ u& R4 z9 X, \有好的性能，但是当数据增加的时候，性能就会很快下降。理解索引如何工作的最简单的方式就是把索引看成
一本书。为了找到书中一个特定的话题，你须要查看目录，它会告诉你页码。索引会让查询锁定更少的列
在InnoDB中，只有事务提交后才会解锁

) m5 N4 z- @5 F) f4 ~索引包含了来自于表中某一列或多个列的值。如果索引了多列数据，那么列的顺序非常重要，因为MySQL只
能高效地搜索索引的最左前缀（Leftmost Prefix）。如你所见，创建一个双列索引和两个单列索引是不一样的。

B-TREE
0 k# W# c( t2 ]- Q2 C' p能使用B-Tree索引的查询类型。B一Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找
' w1 j' P" F$ t  A+ S使用了素引的最左前缀（Leftmost Prcfix）的时候才有用。上节中的索引对于以下类型的查询有用。
- X3 w7 `6 I5 \. D" k

1. 
   
2. CREATE TABLE People(
   , ]  w( c0 ]. x" E. B/ w- D9 U
3. last_name varchar(50)   not  null
   ! ], a. S8 ?1 x# C6 R
4.           first_name  varchar(50)     not   null
   
5.           dob  date      not    null
   8 w9 Q1 I: V8 a4 ~+ x1 b% B
6.       gende       enum('m','f')    not    null
   
7.         key(last_name,first_name,dob)

复制代码

匹配全名
全键值匹配指和索引中的所有列匹配。例如，索引可以帮你找到一个叫CubaAllen并且出生于1960-01-01。
2 m% H; d8 U0 S$ n- k的人。
匹配最左前缀
: I8 A; X: S/ l) ?B-Tree索引可以帮你找到姓为Allen的所有人。这仅仅适用了索引中的第一列。
匹配列前缀
可以匹配某列的值的开头部分。这种索引能帮你找到所有姓氏以J开头的人。这只会使用索引的第1列。
. t5 }/ C: r) S* M. o2 Y匹配范围值
这种索引能帮你找到姓大干Allen并且小干Barrymore的人。这也只会使用索引第一列．
精确匹配一部分并且匹配某个范围中的另一部分
) T3 b7 c- D7 ^5 x! U这种索引能帮你找到姓为Allen并且名字以字母K(Kim、Karl等）开头的人。它精确匹配了last
列并且对first name列进行了范囤查询。
name
只访问索引的查询
8 k( d9 ?0 N7 z) c0 R( z# S/ WB-Tree索引通常能支持只访问索引的查询，它不会访问数据行。
8 ?) R4 j( e: M/ C
由于树的节点是排好序的，它们可以用于查找（查找值）和ORDER BY查询（以排序的方式查找值）。通常来说，
如果B-Tree能以某种特殊的方式找到某行，那么它也能以同样的方式对行进行排序。因此，上面讨论的所有查
& h& m6 k# E% h7 ]1 D* D找方式也可以同等地应用于ORDER BY。
) b/ O3 x- @$ G& a# a5 M. v1 D+ }7 F
$ ~$ t# Z* R/ u$ {. w6 \下面是B-Tree索引的一些局限：
2 k& f9 n& i2 Y; x, O; _3 Y
: |" R* C. v  [, d0 E5 A1,如果查找没有从索引列的最左边开始，它就没什么用处。例如，这种索引不能帮你找到所有叫Bill的人，
! H" g8 L; _% x也不能找到所有出生在某天的人，因为这些列不在索引的最左边。同样，你不能使用该索引查找某个姓
  X" Y1 g! e3 m2 T氏以特定字符结尾的人。
1 D) k: b  _1 n4 I
2,不能跳过索引中的列。也就是说，不能找到所有姓氏为Smith并且出生在某个特定日期的人。如果不定
! Q) C, |/ V: H5 d$ Z义first_name列的值，MySQL就只能使用索引的第一列。

) R5 k+ L4 x* t+ u6 a1 A. p3,存储引擎不能优化访问任何在第一个范围条件右边的列．比如，如果查询是where last_name='Smith' AND first_name LIKE 'J%' and dob ='1967-12-23'，访问就只能使用索引的头两列，因为LIKE是
. K3 n7 K$ j, ?; V9 H范围条件（但是服务器能把其余列用于其他目的）。对于某个只有有限值的列，通常使用等干条件，而
不是范围条件来绕过这个问题。本章稍后的索引案例中我们会举出详细的例子。

. z  p7 A! G, f哈希索引，空间索引和全文索引等，暂时没有设计
1 v# f' r1 v, w$ d
高性能索引策略

1，隔离列，意思就是不要对查询条件中列进行计算等操作
2，前缀索引，针对blob和text，较长的varchar类型，使用前缀索引
Select count(distinct 列) /count(*) from table;
看看这个值时多少，如0.0312
( D0 o/ U& T* M  U那么就是说，如果前缀的选择率能够接近0.0312，基本就可以了。可以在同一个查询中对不同长长度进行计算
，这对于大表很有用。
1 |; H1 \, Z/ v# I; [& HSelect  count(distinct left(列,3)) /count(*)  as  sel1,
9 Y. [( D# `# U# D6 w' q count(distinct left(列,4)) /count(*)  as  sel1  ,
count(distinct left(列,5)) /count(*)  as  sel1,
count(distinct left(列,6)) /count(*)  as  sel1,
count(distinct left(列,7)) /count(*)  as  sel1   from table;
找到接近0.0312即可。
: p& J- Q* f* L) ?2 g
4 |: N8 U9 d' _- g7 BAlter table table_name add key (列(7))
3,覆盖索引
包含或者覆盖所有满足查询的数据索引叫做覆盖索引
" R  Z2 w8 G3 M8 I& D2 }1 G3 ^4 Wexplain时，extra中的会显示using index
, d, E' A! g; I2 u# c" d这里一个重要的原则是
/ W6 ?3 v$ H5 @6 ~select后面的列不能使用*，要使用单独的需要查找的列，使用带索引的列
: O& w; ~7 o% d如select id from table_name;

很容易把Extra列的“使用索引（Using Index)”和type列的“索引（index)”弄混淆。然而，它们完全不
一样。type列和覆盖索引没有任何关系，它显示了查询的访问类型，或者说是查询查找数据行的类型。
3 Y9 h/ f/ d! y9 `6 p

1. Explain Select * from table_name where col ='nam' and col1 like '%name%';
   
2. Extra:using where

复制代码

该索引不能覆盖查询的原因：
! Z( ^, n7 V1 g& A1，
没有索引覆盖查询，因为从表中选择了所有的列，并且没有索引覆盖所有列。MySQL理论上有一个捷径可以使用，但是，WHERE子句只提到了索引覆盖的列，因此MysQL可以使用索引找到col并检查col1是否匹配，这只能通过读取整行进行。
2，
* Y1 i8 j; W5 K5 SMySQL不能在索弓l中执行LIKE操作。这是低层次存储引擎API的限制，它只允许在索引进行简单比较。MysQL能在索引中执行前缀匹配的LIKE模式是因为能把它们转化为简单比较，但是查询中前导的通配符是存储引擎无法转化匹配的。因此，MySQL服务器自己将不得不提取和匹配行的数据，而不是索引值。
$ A! M3 z: E, L$ S$ ]' d有办法可以解决这个问题，那就是合并索引及重写查询。可以把索引进行延伸，让它覆盖（artist,title,prod_id）并且按照下面的方式重写查询：
2 K! r9 d- D; y& p$ q! E
4,为排序使用索引扫描
) s8 F) t' I- u. o( K4 l$ g' h+ ?# @* \mysql有两种产生排序结果的方式：使用文件排序（fileSort），或者扫描有序索引。
4 `5 S1 R0 p# t5 v  c3 u, Dexplain输出type为index，表示mysql会扫描索引
" V$ ^% Z; I# b
9 E# u% G. r# p' f4 G: S( V4 d5 B扫描索引本身是很快的，因为它只需要从一条索引记录移到另外一条记录。然而，如果MySQL没有使用索引覆盖查询，就不得不查找在索引中发现的每一行。这基本是随机I/O的，因此以索引顺序读取数据通常比顺序扫描表慢得多，尤其对于I/O密集的工作负载．
! G7 Y% N- A$ z6 h/ R% r
3 [" p+ B. F7 G/ w7 `9 mMySQL能为排序和查找行使用同样的索引。如果可能，按照这样一举两得的方式设计索引是个好主意。
- g: l* M1 [# B% J3 D  K) o6 a* `
按照索引对结果进行排序，只有当索引的顺序和ORDER BY子句中的顺序完全一致，并且所有列排序的方向（升序或降序）一样才可以。如果查询联接了多个表，只有在ORDER BY子句的所有列引用的是第一个表才可以。查找查询中的ORDER BY子句也有同样的局限：它要使用索引的最左前级。在其他所有情况下，MySQL使用文件排序。

ORDER BY无须定义索引的最左前级的一种情况是前导列为常量（也就是说第一个索引不能是范围查询，如果是组合索引应该以此为常量）。如果WHERE子句和JOIN子句为这些列定义了常量，它们就能弥补索引的缺陷。
5 Q4 v& L% X2 n% y) B- j5 o. M
使用join可能情况会有不同

7 p5 I5 j" m% _' l/ Q. Y5，压缩索引（myisam）
6，多余和重复索引（应该避免）
; j# `: L0 K5 L* F7 i
( S3 ^, J! F4 e  I% |2 b& z多余索引（Redundant Index）和重复索引有一些不同。如果列（A,B）
- ~: k2 [: R/ ?) n上有索引，那么另外一个列（A）上的
" n& G+ Y. i) Y. s索引就是多余的。这就是说，(A,B）上的索引能被当成（A）上的索引。（这种多余只适合于B一Tree索引。）
$ ]9 c" A3 k' K9 w8 X' s. s& d7 e# R8 r然而，(B,A）上的索引不会是多余的，（B）上的索引也不是，因为列B不是列（A,B）的最左前缀。还有，不同类型的索引（例如哈希或全文索引）对于B一Tree索引不是多余的，无论它们针对的是哪一列。

/ `5 ?. J* n9 g! P2 n% V要点：
; [6 p7 {( j3 O6 U8 O在任何可能的地方，都要试着扩展索引（之前是一个列A上面有索引，现在两个列A，B上建立索引），而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布，就要尽可能地使索引变得更有选择性，因为高选择性的索引通常更有好处．
2 w) k' Z- \  a% t4 ^
) C' l4 I) a9 u# J) v即使InnoDB使用了索引，它也能锁定不需要的行，这个问题在它不能使用索引找到并锁定行的时候会更严重：如果没有索引，mysql不管是否需要行，都会进行全表扫描并锁定每一行

- g8 O1 S& _0 X# f+ s- l2 [( }
0 d$ {+ n9 h7 h
3 c$ Q5 E9 E$ V( p9 m! c

myskya

创建索引时，
. z% r* F5 t& U/ }5 F- y5 p
拥有唯一值的列选择性最高，那些具有很多相同值的不适合创建索引
1 r5 U* w" M5 S
2 X. t# C1 ^9 r
  c6 s# D* F2 F6 ?  T% O0 [  _$ f
7 g1 F; m, |4 k5 \6 K9 n1 l5 h一个通用的规则：保持表上的所有选项。当你设计索引的时候，不要只想着已有查询需要的索
: L* \- y9 a* j' w0 d7 s# o" Q$ }
( w# G2 o; C! @$ O7 j' v# i引，也要想着优化查询。如果看到需要某个索引，但是一些查询会因它而受到损害，就要问问自己是否应该改变这些查询。应该一起优化查询和索引，以找到最佳的折中。没有必要闭门造车，以得到最好的索引。
9 }; Y0 d9 l3 z0 D  B# ~! r8 O

7 Z. }: d9 h2 a+ T# m# e1 T
一个在多列上面的索引，为了是这个索引生效，必须满足最左原则。

$ S5 f1 J+ \6 Y3 C% k例如inex（a,b,c）,这个时候如果只是用了a,c。没有使用b这个时候就不会使用索引。怎么处理

; G) ]! k0 l! u( M& s( w& ]这里如果b是一个可以枚举的类型那么可以使用in（…）,将b全部列出。这样相当于b没有起到筛选的作用，但是却可以是索引发挥作用。这个方法也不能滥用，因为会出现n*n的结果，如果枚举数相乘过大，应该选择其他方式


4 g# I9 b8 U, L: e; b' H
, v* M, v4 ?4 a避免多个范围条件，只能对其中一个使用索引
2 D) j# \6 Z5 n- p" i; h- w/ Q

8 k9 Y$ o1 d  R
' D. `3 n5 i6 f5 \5 ?索引和表维护

表维护的主要目标：查找和修复损坏，维护精确的索引统计，并且减少碎片.
4 M6 ]( D. j" W+ ?9 o
# A  G! f4 V0 g3 Ccheck table table_name;
repair table table_name;
Show index from table_name;检查索引的基数性

0 C$ ?) u# S5 {4 U主要关注cardinality列，显示存储引擎估计的索引中唯一值的数量

1 }0 Q" b& a8 K/ ^7 Z# a; ~+ |( g
  w) \3 L% Z* `1 H
B-Tree索引能变成碎片，它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。

6 F  e; L5 R0 u. b* T3 g/ U2 I表数据也能变成碎片化。两种类型：
3 ?/ d2 q6 `4 f8 G7 R/ s
- R, E( ]- H" \# `$ `1，行碎片

当行披存储在多个地方的多个片段中时，就会是这种碎片。即使查询只从索引中找一行数据，行碎片也会降低性能。
' ]6 L) ]9 f  G: L4 m
% Q9 H% J" S$ d) x) }
) m/ t6 L2 l) P: A" v: p3 K
) Z) |  D+ b4 o& k5 {. ~) _  z2，内部行碎片

当逻辑上顺序的页面或行在磁盘上没有被顺序存储的时候，就会产生这种碎片。它影响了诸如全表扫描和

' T% [$ O. v! j+ P  W聚集素引范围扫描这样的操作。这些操作通常从磁盘上的顺序数据布局得益。



为了消除碎片，可以允许OPTIMIZE TABLE或转储并重新加载数据。

/ e( q( @( ?) g7 R. N4 p  S

5 X  M1 h3 Q' L6 u/ nALTER TABLE <table> ENGINE=<engine>


- J" x$ {& \; b
, R& x9 v) ~) z7 j加速ALTER TABLE
: W( _0 \/ A; M

% L0 h+ u; _- A6 |$ f
MySQL的ALTER TABLE的性能在遇到很大的表的时候会出问题。MySQL执行大部分更改操作都是新建一个需

% C3 \$ @, L/ {2 _, {# F要的结构的空表，然后把所有老的数据插入到新表中，最后删除旧表．这会耗费很多时间，尤其是在内存紧张，
1 U4 g0 Q4 X' @' @/ o0 Y
而表很大并含有很多索引的时候．许多人都遇到过ALTER TABLE操作需要几小时或几天才能完成的情况。
5 f' O# o: _- R: y
传统：
) q" m# z/ z, x& J! n! i) o' B
ALTER TABLE table_name MODIFY COLUMN col TINYINT(3) NOT NULL DEFAULT 5;
* }6 {) Z" J, K0 f理论上，MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中，因此可以不接触表而更
6 f7 {0 I% @1 i5 j1 u4 t1 q改它。MySQL没有使用这种优化，然而，任何MODIFY COLUMN都会导致表重建。

变化：

3 j2 w- p" j5 F. S- eALTER TABLE table_name ALTER COLUMN col SET DEFAULT 5;
这个命令更改了.frm文件并且没有改动表。它非常快。
还有一个CHANGE COLUMN