【MySQL优化(六)】InnoDB索引优化与索引规约

序

上一篇讲解了建表规范后，本章重点分析下创建索引的一些规范
由于索引是工作在存储引擎层，所以以下规约都是基于InnoDB引擎

题外话

在满足语句需求的情况下， 尽量少地访问/消耗资源是数据库设计的重要原则，
所以如何利用索引达到上述目的则是创建索引的标准，这个原则同样适用于设计表结构

关于索引

索引的优点

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

缺点

1. 索引会带来额外维护的开销，减慢写入速度

索引规约

通用原则

1. 代码先行，索引后上。开发时建立索引步骤：建表-开发完主体业务-建索引。
2. 利用覆盖索引来进行查询操作，减少select *，避免回表
3. 不允许存在重复索引（指完全相同的索引），大多数时候也应该避免冗余索引（指索引被其他联合索引包含）
4. 不要在小基数字段上建立索引，注意列的区分度（例如大多数时候性别列不应该单独建立索引）
5. is null，is not null 一般情况下也无法使用索引
6. MySQL在使用不等于(!=或者<>)，not in，not exists 的时候无法使用索引会导致全表扫描
7. < 小于、 > 大于、 <=、>= 这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
8. 防止因字段类型不同造成的隐式转换，导致索引失效 ，例如：字符串不加单引号索引失效，数字型字段匹配字符串值等
9. 少用or或in，用它查询时，MySQL不一定使用索引，MySQL内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化
10. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效
11. 当要索引的列的值非常长时，可以考虑增加一个对应的hash列，自定义一个Hash算法（结果最好是整数）来同步更新对应的hash列，通过改为在hash列加索引查询将大大提高查询性能
12. where和order by 冲突时，优先满足where条件，当过滤的数据集足够小时，哪怕走文件排序性能依然很高。
13. 严禁左模糊或者全模糊，如果需要请走搜索引擎来解决
14. 在较长的varchar上建立索引时，尽量指定索引长度。通常为前20个字符创建前缀索引能达到90%的区分度。可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。需注意，使用前缀索引后，由于索引不包含完整的值，无法使用覆盖索引，需要回表。区分度不好的时候，可以考虑倒序存储，然后加前缀索引
15. 优化联合查询
    1. 尽量避免超过三个表的JOIN查询。
    2. 需要JOIN的字段，数据类型要保持绝对一直，包括字符集，关联字段尽量选择整数类型字段。
    3. 保证被关联表的字段一定有索引
    4. 小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去MySQL优化器自己判断的时间

注意：MySQL多表join很难优化，应尽量避免。如果一定要使用多表的大连接查询，那么请进行拆分，然后在应用中关联，好处如下：

1. 减少锁竞争
2. 增加缓存的可能性
3. 可以减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用只需要查询一次，而在数据库中做关联，则需要重复的访问一部分数据，这样可以减少网络和内存的消耗。
4. MySQL的资源是非常宝贵，而且相对于应用服务器来说相对是难以扩容的，通过转移计算的压力到应用服务器，来降低MySQL的负载。

关于主键（聚簇索引）

1. 一定要主动创建一个主键，减少MySQL的工作，否则InnoDB会自动选择一个唯一的非空索引代替，若没有会隐式定义一个主键作为聚簇索引。
2. 大多数时候建议使用自增的整型作为主键，若非顺序的主键会导致数据插入的时候可能产生较多的页分裂，降低插入速度，同时产生存储碎片，影响查询性能。当然可以根据业务特性需要聚集某些维度的数据，也可以使用其他数据列作为聚簇索引，来提高检索性能，但这需要综合评估其他操作。

Hash索引

1. Hash索引只包含哈希值和行指针，不存储字段值，不能避免回表操作
2. Hash索引只能执行=、IN查找，不支持索引前缀匹配，范围和排序
3. 在区分度很低的列创建hash索引会导致大量hash冲突，性能很低，特别是更新/删除的时候

联合索引

1. 创建联合索引时
   1. 大多数时候应该将区分度最高的列放在最左边
   2. 让联合索引尽量使用全部的列，减少通过索引筛选出来的结果集
   3. InnoDB不能使用索引中范围条件右边的列，尽量把需要范围查询的列放在最右侧（MySQL5.6引入了索引下推，可以在一定程度上优化该情况，减少回表次数，见下面的扩展阅读）
   4. 如果需要同时建立联合索引和单列索引，例如：[(name, age) 和 age] 以及 [(age, name) 和 name] 建议考虑前者，因为age通常比name占用磁盘空间小, 整体索引较小, I/O次数也较少
2. 联合索引，第一个就走范围查询可能不会走索引，MySQL内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描
3. 控制单表索引数量，尽量避免使用单列索引，尽量使用3个左右联合索引覆盖80%业务查询场景，包括where，order by，group by，注意最左前缀原则
4. 在建立联合索引的时候，如何安排索引内的字段顺序?
   1. 考虑索引的复用能力，如果通过调整顺序，可以少维护一个索引，那么大多时候这个顺序就优先考虑采用，例如：index(b, a) index(a) 合并成 index(a, b)。当然这个里还要考虑a字段的区分度。
   2. 考虑的存储空间，当需要同时满足a、b、ab查询时，在区分度差别不大的情况下，如果a列存储空间大于b列，优先考虑(a,b) 和 b 两个索引，而不是(b, a) 和 a。
5. 在某些场景下可以适当冗余来利用覆盖索引的特性提高查询性能，例如：用户信息表身份证号已经是唯一标识，但是如果有大量的根据身份证号查询身份证号和名字的查询即可建立身份证号+名字的联合索引来提高性能。
6. 优化联合索引原则
   1. 第一原则：索引的区分度，尽可能将选择性高的列放在左边
   2. 第二原则：索引的覆盖度，尽可能使用3个以内联合索引覆盖80%的查询
   3. 第三原则：索引的复用能力。如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
   4. 第四原则：空间维度。比如市民表，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

联合主键

当存在联合主键时，假设 t 表 有 a、b、c三个字段，a，b为联合主键

索引	分析
联合索引 (c, b)	等于 (c、b、a[主键部分])
索引 c	等于(c、a[主键部分])、b[主键部分]
联合索引(c, a)	等于(c、a、b[主键部分]) 【等同于索引c】
联合索引(c, b)	等于(c、b、a[主键部分])

唯一索引还是普通索引

创建二级索引时选择 普通索引还是唯一索引？

• 结论
  如果追求性能使用普通索引，如果追求数据唯一性使用唯一索引。
  根据墨菲定律，长久来看，只要没有唯一索引，就一定会有脏数据产生，所以在大多数时候建议选择联合索引
• 从性能上分析

1. 查询速度，唯一索引更快。唯一索引查找到记录就停止，普通索引需要查找到下一个不同的值出现，但MySQL实际上是以页为单位读取数据，大多数时候下几条记录都通过一次I/O读取到了内存中，在内存中多做几次判断的耗时可以忽略不计

2. 更新速度：普通索引快很多，唯一索引无法利用Change Buffer导致需要进行磁盘I/O读取数据到内存判断

   唯一索引提升的查询速度非常有限，但是无法利用Change Buffer导致写操作速度下降很多。

索引合并

1. 索引合并（index merge）指同时利用多个索引分别进行扫描，然后将扫描结果进行 合并 UNION（OR查询）或 相交 INTERSECTION （AND查询） 或者 两者皆有，在对多个索引做联合操作时，需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上，但优化器不会计算这些查询成本，导致成本被低估，有时候还不如全表扫描，应该创建联合索引避免该情况。

重建索引

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

1. 重建二级索引，可以达到节省空间，提高索引效率的目的

   alter table T drop index k;
   alter table T add index(k);
   

2. 重建主键索引

   alter table T drop primary key;;
   alter table T add index(k);
   

   不论是删除主键还是创建主键，都会将整个表重建，所以该语句起始包含了重建二级索引的作用，耗时较长。
   可以考虑使用下述语句代替：

   alter table T engine=InnoDB
   

索引监控

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.03 sec)

Handler_read_first：读取索引第一个条目的次数
Handler_read_key：通过index获取数据的次数
Handler_read_last：读取索引最后一个条目的次数
Handler_read_next：通过索引读取下一条数据的次数
Handler_read_prev：通过索引读取上一条数据的次数
Handler_read_rnd：从固定位置读取数据的次数
Handler_read_rnd_next：从数据节点读取下一条数据的次数

扩展阅读

索引下推

在5.6以后MySQL引入索引下推机制，可以在索引遍历过程中，对索引中 包含的所有字段 先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，索引下推只运用于二级索引。 col1 like ‘??%’ AND col2 = ‘??’ 即可能使用索引下推。

回表

先搜索二级索引树，得到主键的值，再到主键索引树搜索一次。这个过程称为回表

页分裂

当记录所在的数据页已经满了，需要写入数据时，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。（为了减少数据移动和页分裂，会先去前后两个页看看是否满了，提高空间利用率）

这个过程称为页分裂，比较耗时，应尽量避免。

页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

查看索引利用率

select * from performance_schema.table_io_waits_summary_by_index_usage

Change Buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页，用于加速插入和更新。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

此时真正将数据写入磁盘是在执行merge操作的时刻，merge操作触发的条件有以下几种：

1. 访问change buffer中的数据时
2. 后台系统定期merge
3. 数据库正常关闭时先执行merge

适用场景：写多读少的时候适用，页面在写完后马上被访问的概率很小，例如账号、日志系统。
不适用场景：假设一个业务的更新模式是写入之后马上会做查询，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

参考文章

【官方文档】 https://dev.mysql.com/doc/refman/5.7/en
【高性能MySQL第三版】
【MySQL技术内幕：InnoDB存储引擎】
【丁奇MySQL实战45讲】

系列文章

上一篇：【MySQL优化(五)】InnoDB索引结构及特点
上一篇：【MySQL优化(七)】MySQL Explain详解