mysql 回表的代价（InnoDB）

为了方便理解我们先来看一个sql语句

SELECT * FROM demo_table where key1 > 'a' and key1 < 'c';

假设我们为key1列设置二级索引，索引结构为B+树

对于上面这个sql有两种执行方式：

1. 以全表扫描的方式执行该查询
全表扫描也就是直接扫描全部的聚簇索引记录，针对每一条聚簇索引记录，都判断搜索条件是否成立，如果成立则发送到客户端，否则跳过这条记录。

2. 使用idx_key1，也就是对应key1列的索引来执行该查询

• 过程

可以根据搜索条件 key1 > ‘a’ and key1 < ‘c’ 得到对应的扫描区间 （‘a’,‘c’），然后扫描该区间中的二级索引记录。由于二级索引记录idx_key1索引的叶子节点存储的不是完整的用户记录信息，而是只存储了二级索引列key1列和主键id这两个列，而我们的sql查询的列表是 * ，这意味着我们需要获取每条二级索引记录对应的聚簇索引记录（通过在二级索引记录中得到的主键id去然后去聚簇索引中查询完整的用户信息），也就是执行回表操作，然后获取到完整的用户信息发送给客户端。

• 执行回表的代价

对于InnoDB存储引擎来说，索引中的数据都是存放在磁盘中的，等到需要的时候再将磁盘中的数据加载到内存当中。这些数据页会被存放到磁盘中的一个或多个文件中，页面的页号对应着该页在磁盘文件中的偏移量，以16k大小的页面为例，页号为0的页面对应着这些文件中偏移量为0的位置，页号为1的页面对应着文件中偏移量为16k的位置。

我们知道B+树每层节点也就是每个数据页会使用双向链表连接起来，上一个节点和下一个节点的页号可以不相邻（不过会尽量相邻）。

也就是说idx_key1在扫描区间（‘a’,‘c’）中的二级索引记录所在的页面的页号会尽可能相邻，即使这些页面的页号不相邻，但是一个页面也可以存放很多记录，也就是说在执行完一次页面IO后，就可以把很多二级索引记录加载到内存当中。这种情况是因为我们要查询的数据量比较小。需要注意的一点是，我们通过二级索引得到的id值是没有规律的，因为二级索引是通过二级索引列来排序的，我们每得到一条二级索引记录，就要通过二级索引记录中的主键id去聚簇索引中查询，也就是需要执行回表操作。如果对应的聚簇索引的数据不在内存中，就需要将页面从磁盘加载到内存。由于要读取很多id值不连续的聚簇索引记录，而这些聚簇索引记录分布在不同的数据页中，这些数据的页号也没有规律，因此会造成大量的随机IO。

上面说的可能有点啰嗦，总结一下就是：
我们通过二级索引来查询的时候，通过二级索引建立的B+树存储的不是完整的用户记录，并且是按照二级索引列来排序的，主键id是无序的，当我们要查询完整的用户记录时，就需要回表。由于主键id在二级索引中没有顺序，所以主键id可能分布在多个页面，这个时候需要读取多个页面，造成大量的IO，如果是有序的可能就分布在一个页面，这样一次就读取到内存了

• 最好不要使用二级索引的情况
  当需要执行回表操作的记录越来越多，使用二级索引查询的效率也就越来越低，有些查询宁可全表扫描也不使用二级索引。比如key1值在’a’ ~ ‘c’ 之间的用户数占总记录的99%以上，如果使用二级索引的话，有99%以上的id值都需要执行回表操作，回表的代价上面已经提到了。
  一般情况下，可以给查询语句指定LIMIT子句来限制查询返回的记录数，这可能会让查询优化器倾向于选择使用二级索引+回表的方式来进行查询。

创作不易，点个赞吧~👍

最后的最后送大家一句话

白驹过隙，沧海桑田

与君共勉

参考资料：mysql是怎样运行的–小孩子4919

文章持续更新，可以关注下方公众号或者微信搜一搜「 最后一支迷迭香 」第一时间阅读，获取更完整的链路资料。