MYSQL索引优化入门

MYSQL索引优化入门

一、 前言

索引对于关系型数据库MYSQL的重要性是不言而喻了。本文档的首要目的是让MYSQL初级使用者快速掌握如何正确的使用索引，不犯原则性的错误。其次，本文档会用少量篇幅介绍有关索引的基础理论，为有兴趣的深入研究的读者打一个简单的基础，以期达到抛砖引玉的作用。

 

二、 常见的几种误解

1、   查询会使用多个索引加速查询

不管一张表上有多少索引，最终查询时MYSQL只能选取其中一个最优的索引进行使用。

2、   索引越多越好

什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的最好索引。

3、   查询条件的顺序必须和复合索引顺序一致才能有效使用索引

查询条件的顺序有可能会影响到最优索引的选取，一旦最优索引确定后，就不会影响到索引的查询效率

 

三、 建立索引的原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

 

1. 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

   

1. 尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

 

1. 综合考虑查询和排序

比如查询条件a = ‘1’ order by b desc,则可以考虑建立联合索引(a,b)，加速排序

四、 查询的一般原则

1、   索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ‘2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(‘2014-05-29’);

2、   尽量使用join替代子查询

虽然子查询具有逻辑清晰的特点，但MYSQL的子查询效率极其低下。

3、   使用like的时候尽量使用前缀查询，也就是通配符%尽量不要放在最前端。

4、   必要时可以对联合索引进行“补洞”。

补洞是指对类似(a,b,c)这样的索引，查询条件是a=1 and c=2则只有a这部分能用上索引，如果已知b的可能值比较少，如只有0和1两个取值，可将查询条件改为a=1 and b in(0,1) and c = 2代替，如此更好的利用索引(a,b,c)。

5、   尽可能的利用覆盖索引。

如无必要，尽量减少返回列，这不仅可以减少数据传输量，而且在存在覆盖索引时可以避免对主索引的二次查询。

 

五、 性能分析利器——explain

1、   用法示例

 

2、   id

包含一组数字，表示查询中执行select子句或操作表的顺序, 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行; id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越早执行。

3、   select_type

表示查询中每个select子句的类型

a.SIMPLE：查询中不包含子查询或者UNION

b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）

e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在  FROM子句的子查询中，外层SELECT将被标记为：DERIVED

f.从UNION表获取结果的SELECT被标记为：UNION RESULT

 

4、   type

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型有All，index,range,ref,eq_ref,const,system,null, 由左至右，由最差到最好

1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
2. index：Full Index Scan，index与ALL区别为index类型只遍历索引树
3. range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
5. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
6. const：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
7. system是const类型的特例，当查询的表只有一行的情况下， 使用system
8. NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

 

5、   possible_keys

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6、   key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

7、   key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

8、   ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

9、   rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

10、 extra

包含不适合在其他列中显示但十分重要的额外信息

1. Using index 表示相应的select操作中使用了覆盖索引（Covering Index）
2. Using where 表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）
3. Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
4. Using filesort 表示MySQL中无法利用索引完成的排序操作称为“文件排序”

六、 索引原理

索引的目的在于提高查询效率，我们生活中最常见的索引例子就是字典、书的目录，它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但要复杂的多，特别是考虑到内存访问和磁盘访问性能方面的巨大差异（一般相差10万倍），我们需要设计一种数据结构，它能使得每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。专家们最好想到了一个高度可控的多路搜索树，这就是B+树。

 

如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

转载于:https://my.oschina.net/jumpLee/blog/740587