背景事件和分析

某天，Atome运营部门向大量用户发送了APP内push的营销信息，并附带了优惠券，造成了几分钟内大量用户涌入APP抢购。过一会，就发现系统很多接口的响应速度变慢，大量接口要5秒左右才能返回，甚至有部分接口调用直接timeout，造成了APP对客户的响应速度下降，影响客户体验。好在这个抢购高峰只持续了几分钟，响应速度慢的问题很快也就随着流量的下降而消失了。

事后通过分析发现，这个线上问题有如下几个特征：

1. 在发现响应速度慢之后，立马对响应慢的服务进行了扩容，但发现响应慢的问题没有改善。 

2. 在APP流量高峰的时候也是保持很小的调用量的系统，例如后台系统，批处理任务等，在这段时间内也出现了响应慢甚至timeout的情况 

3. 这些报错的服务使用的数据表都在同一个MySQL host上 

根据这些特征，立马怀疑是数据库系统慢造成的。经过对数据库深入的排查，最后发现是由于在优惠券核销的时候的一条慢SQL导致了这个问题，而导致这个慢SQL的原因就是列索引建立不当。

基本信息：表结构如下，字段数量：31，数据量：1587413，符合where条件的数据量：116203

查询语句如下：

优化前执行时间0.6s

优化分析：

SQL改写为：

改写SQL后执行计划及分析：

继续优化：

执行时间：0.32

继续优化：

执行时间：0.14

总结Extra 变化过程：

根据当前sql的逻辑，判断sql当前是否使用索引，再根据extra、rows、key等来判断sql的整体运行情况是怎样的，从整体分析sql是否走了合适的索引，根据sql本身用到的字段数量以及使用频率来判断是否需要使用到覆盖索引，sql本身是否还存在优化的空间，再添加索引时还得注意添加的索引在不强制指定索引时是不是真的能够使用到索引。

那么接下来具体介绍下MySQL索引。

什么是索引？

索引是帮助MySQL高效获取数据的数据结构。

通俗一点，索引就好比一本书的目录，它能让你更快的找到自己想要的内容，让获取的数据更有目的性，从而提高数据库检索数据的性能。

假设你想从一本书中找到你感兴趣的内容，书又没有目录，恐怕就需要从第一页开始找，翻运气好的话很快就能找到内容，最糟糕的情况就是翻遍整本书，如果这本书比较薄你可能花很短时间就能翻完整本书，如果这本书是个新华字典，那只能耐着性子了...

所以说索引是用来提高数据检索的性能，数据量越大，性能越突出。

MySQL数据存储结构

在讨论索引之前，我们先看下MySQL磁盘数据页的存储结构以InnoDB为例，因为后面讨论索引的物理存储结构以及使用的时候跟物理存储页机构是有很大关联的。

我们先看下大量的数据页在磁盘文件里是怎么存储的，如下图：

一个数据页在磁盘文件里就是一段数据，可能是二进制或者别的特殊格式的数据，然后数据页里包含两个指针，一个指针指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址， 数据行是根据主键从小到大排序的，所以磁盘文件里的多个数据页是通过指针组成一个双向链表的，类似下图：

这个其实每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关系，其实就是二分查找，如下图所示：

根据上图数据页内部的数据行是组成单向链表的，每个数据页内根据主键做了一个页目录，查找的时候从页目录中根据二分查找就能快速定位到数据页，下面我们用一个动图来演示下过程：

实际上InnoDB的存储结构就是这种结构，所有的值都是按顺序存储的，所有的叶子节点存储每一页的数据，并且每一个叶子页到根的距离相同，至于B+Tree具体原理这里不过多讨论。

B+树生成及查询在线体验地址：B+tree可视化

索引的分类及区别

按照索引方法分类，分为：B-TREE、HASH、R-TREE、FULLTEXT，这里我们重点说下BTREE和HASH索引。

B-TREE索引

绝大部分的索引都是B-Tree 索引，它使用 B-Tree数据结构来存储数据，大多数 MySQL引擎都支持这种索引。

在讲B-TREE索引之前我们先说下InnoDB的存储结构，数据库所有的数据最终都是存在磁盘文件里的，文件存放的物理格式就是数据页，大量的数据页是按顺序一页一页存放的，然后两两相邻的数据页之间会采用双向链表的格式互相引用，一个指针指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址。

我们回过头来看B-TREE索引：

B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

总结下B-TREE索引对如下查询有效：

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配。

2. 匹配最左前缀：可以只匹配某—列值的开头部分。比如查找某一列A开头的数据。

3. 匹配范围值：前面说了索引列是按照顺序组织存储的所以很适合范围查找值。

4. 精确匹配某一列并范围匹配另外的一行，其实就是上面全值匹配+匹配最左前缀的组合。

5. 索引取值：某一列的索引其实就是存储的这一列的值，所以无需回表即可查询数据（回表后面讲）。

HASH索引

哈希索引是基于哈希表来实现，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个比较小的值，不同键值的行计算出来的哈希码是不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希算法时间复杂度为O(1)，将哈希值存储在哈希表中，哈希表就是散列表，如下图：

实际上可能会存在不同的key值哈希之后映射到相同的位置，叫做哈希碰撞，我们将同一槽位的元素放在表中同一行（链表），如下图：

InnoDB中的哈希算法，其冲突机制采用链表方式，哈希函数采用除法散列方式。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快，索引的检索可以一次定位。不像B-Tree索引需要从根节点到枝节点，最后才能访问到数据页这样多次的IO访问索引页，所以Hash索引的查询效率要远高于B-Tree索引。

但是在实际使用的时候，B-TREE索引的使用率远远高于HASH索引，两者的区别主要在：

1. hash索引只能进行等值过滤不能进行范围过滤。由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤。

2. Hash索引无法被用来进行数据的排序操作。因为Hash索引中存放的是经过Hash计算之后的Hash值

3. Hash索引不能利用部分索引键查询。对于组合索引，Hash索引是计算组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。

4. Hash索引在任何时候都不可避免要进行回表查询。因为hash索引存放的是列值的hash值。 

5. 当Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。　

R-TREE索引

MyISAM支持空间索引，可以用作地理数据存储。和 B-Tree 索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用 MySOL 的 GIS 相关函数如 MBRCONTAINS（）等来维护数据。MySQL的 GIS 支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对 GIS 的解决方案做得比较好的是 PostgreSQL 的 PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的 wHERE 条件匹配。

所以我们在选用索引算法的时候一定要明白他的原理及适用的范围。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，就是上面提到的数据存储结构，MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引和辅助索引（也叫非聚簇索引或二级索引）。

聚簇索引

由于数据页是在叶子节点按照顺序存放的，所以聚簇索引的顺序就是数据的物理存储顺序，索引的叶节点就是数据节点。因为真实数据的物理顺序只能有一种，如果聚簇索引列经常更新，就需要对已有数据重新进行排序，所以聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

聚簇索引结构如下图：

优点：

• 所有数据顺序存放非常方便顺序和范围查询减少磁盘IO

• 访问数据快，找到索引即找到数据。聚簇索引叶子节点保存的是实际数据。

缺点：

• 插入速度严重依赖插入顺序，否则将会出现页分裂，所以聚簇索引一般采用主键自增。

• 更新聚簇索引的代价非常高，有可能导致更新行的移动。

• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚簇索引

非聚簇索引，叶级页存有指针指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的数据页顺序是不一致的。

非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

非聚簇索引，如下图：

我们假如将字段id设为主键索引，name列设为非聚簇索引（二级索引）那么非聚簇索引的叶子节点存的是索引列的值以及主键id的值，如果我们根据name值查询id、name、age这三列则需要在聚簇索引树上重新检索数据，这就是索引回表。

优点：

• 索引覆盖能减少回表提高查询效率

缺点：

• 由于非聚簇索引的叶子节点包含了引用行的主键列，非聚簇索引可能比想象的要大。

• 非聚簇索引需要两次索引查找

索引回表

上面我们已经知道了聚簇索引和非聚簇索引的存储结构，如果查询的列能够命中索引，查询的列不仅仅包含索引列还包含除主键外的其他列，那么其他列就要需要回表才能查询出来，这就是索引回表。

我们用个小例子来感受下：

select id, name, age  from user where name = 'J';

由于age字段不在索引中，所以需要根据id回表查询，使用索引覆盖优化，创建组合索引:

alter table user add index idx_name_age(name, age);

这样就能达到索引覆盖的目的。

索引该如何设计

索引是不是越多越好

上面我们MySQL数据库存储结构中我们演示了B+Tree索引页和数据页的裂变过程。数据页/索引页里面的记录都是组成一个单向链表的，而且是按照数据大小有序排列的；然后数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的，所以其实B+树索引是一个完全有序的数据结构。

时间上而言，如果数据页太多了，那么索引页里对应数据页指针也就会太多了，此索引页也必然会放满的，索引页也会分裂成多个，再形成更上层的索引页。那么你在进行增删改查的时候，每次都需要维护各个索引的数据有序性，频繁增删改频繁的裂变是非常耗时间的。

空间上而言，你要是给很多字段创建很多的索引，那你必须会有很多棵索引B+树，每一棵B+树都要占用很多磁盘空间，所以索引要是太多了，是非常耗费磁盘空间的。

所以，索引并不是越多越好！！！

设计索引该考虑哪些因素

通过前面的分析以InnoDB为例，我们大多数会选择选择B-TREE索引，所以结合B+TREE的特性，我们来聊下。

保证你的where语句能用上索引

当然设计索引的时候要保证每个SQL语句的where、order by和group by都能用上索引。

因为在你的新项目基本开发完成之后，你的SQL也就差不多确定了，这个时候你需要考虑如何设计索引了，你已经知道你到底需要什么样的查询语句。

合理选择索引列

对于聚簇索引，我们使用自增主键来保障数据能够顺序存储。

对于非聚簇索引，尽量使用字段类型小的列作为索引列，另外还要考虑字段的区分度，避免使用区分度低的列，比如字段值都是0或者1，区分度越高越能充分利用B+TRE高效查找的特性。

尽量避免选择经常被增删改的列作为索引列，因为维护B+TREE索引树的成本也是非常高的。

避免索引失效

我们建好了索引目的是使用他，所以一定避免一些骚操作导致索引失效。比如在SQL语句中搞个计算啊套个函数等。

常用索引使用原则

前缀匹配原则， LIKE操作中，like name '%xxx%'，则name索引会失效，like name 'xxx%'可以使用索引。

where语句中or中有字段没有索引会导致索引失效。如果条件中有or，只要其中一个条件没有索引，其他字段有索引也不会使用。对于没有索引的列会进行全表扫描从MySQL查询优化器的角度来看一次全表索引就能解决所有查询匹配，所以有索引字段也不走索引。

where子句‘=’ 左边不要出现函数、算数运算或者其他表达式运算会导致查询分析器放弃了索引的使用。

组合索引最左匹配原则：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

where语句中使用not in会导致索引失效。

where语句中使用 <>和 != 会导致普通索引失效。

隐式类型转换会导致索引失效。字符型和数字型的隐式转换，字符型的需要加上引号。

如果排序使用了索引，而select列未使用索引列，则该索引失效，这是因为优化器执行直接执行全表扫描速度更快。

使用覆盖索引提升查询性能，查询列也就是select的字段要被所使用的索引覆盖，查询列中可包括主键，覆盖索引减少了一个回表的过程，直接获取查询字段。所以要求覆盖索引必须要存储索引的列。

参考文章

1. 《高性能MySQL（第三版）》

2. MySQL 5.7官方文档