图解｜用好MySQL索引，你需要知道的一些事情

一篇文章来聊一聊如何用好MySQL索引。

为了更好地进行解释，我创建了一个存储引擎为InnoDB的表user_innodb，并批量初始化了500W+条数据。包含主键id、姓名字段（name）、性别字段（gender，用0，1表示不同性别）、手机号字段（phone），并为name和phone字段创建了联合索引。

CREATE TABLE `user_innodb` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `gender` tinyint(1) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, PRIMARY KEY (`id`)， INDEX IDX_NAME_PHONE (name, phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 索引的代价

索引可以非常有效地提升查询效率，既然这么好，我给每个字段都创建一个索引行不行？我劝你不要冲动。

任何事情都有两面，索引也不例外。过度使用索引，我们在空间和时间上都会付出相应的代价。

1.1 空间上的代价

索引就是一棵B+数，每创建一个索引都需要创建一棵B+树，每一棵B+树的节点都是一个数据页，每一个数据页默认会占用16KB的磁盘空间，每一棵B+树又会包含许许多多的数据页。所以，大量创建索引，你的磁盘空间会被迅速消耗。

1.2 时间上的代价

空间上的代价你可以使用“钞能力”来解决，但时间上的代价我们可能就束手无策了。

链表的维护

我以主键索引为例举个例子，主键索引的B+树的每一个节点内的记录都是按照主键值由小到大的顺序，采用单向链表的方式进行连接的。如下图所示：

如果我现在要删除主键id为1的记录，会破坏3个数据页内的记录排序，需要对这3个数据页内的记录进行重排列，插入和修改操作也是同理。

注：这里给大家提一嘴，其实删除操作并不会立即进行数据页内记录的重排列，而是会给被删除的记录打上一个删除的标识，等到合适的时候，再把记录从链表中移除，但是总归需要涉及到排序的维护，势必要消耗性能。

假如这张表有12个字段，我们为这张表的12个字段都设置了索引，我们删除1条记录，需要涉及到12棵B+树的N个数据页内记录的排序维护。

更糟糕的是，你增删改记录的时候，还可能会触发数据页的回收和分裂。还是以上图为例，假如我删除了id为13的记录，那么数据页124就没有存在的必要了，会被InnoDB存储引擎回收；我插入一条id为12的记录，如果数据页32的空间不足以存储该记录，InnoDB又需要进行页面分裂。我们不需要知道页面回收和页面分裂的细节，但是能够想象到这个操作会有多复杂。

如果每个字段都创建索引，所有这些索引的维护操作带来的性能损耗，你能想象了吧。

查询计划

执行查询语句之前，MySQL查询优化器会基于cost成本对一条查询语句进行优化，并生成一个执行计划。如果创建的索引太多，优化器会计算每个索引的搜索成本，导致在分析过程中耗时太多，最终影响查询语句的执行效率。

2. 回表的代价

2.1 什么是回表

我再啰嗦一遍什么是回表，我们可以通过二级索引找到B+树中的叶子结点，但是二级索引的叶子节点的内容并不全，只有索引列的值和主键值。我们需要拿着主键值再去聚簇索引（主键索引）的叶子节点中去拿到完整的用户记录，这个过程叫做回表。

上图中我以name二级索引为例，并且只画出了二级索引的叶子节点和聚簇索引的叶子节点，省略了两棵B+树的非叶子节点。

从二级索引的叶子节点延伸出的3条线表示的就是回表操作。

2.2 回表的代价

我们根据name字段查找二级索引的叶子节点的代价还是比较小的，原因有二：

1. 叶子节点所在的页通过双向链表进行关联，遍历的速度比较快；
2. MySQL会尽量让同一个索引的叶子节点的数据页在磁盘空间中相邻，尽力避免随机IO。

但是二级索引叶子节点中的主键id的排布就没有任何规律了，毕竟name索引是对name字段进行排序的。进行回表的时候，极有可能出现主键id所在的记录在聚簇索引叶子节点中反复横跳的情况（正如上图中回表的3条线表示的那样），也就是随机IO。如果目标数据页恰好在内存中的话效果倒也不会太差，但如果不在内存中，还要从磁盘中加载一个数据页的内容（16KB）到内存中，这个速度可