什么是 MySQL 的“回表”？

小灰灰 2024-03-16 12:18 14阅读 0赞

今天就来和大家聊一聊这个话题。

### **1. 索引结构** ###

要搞明白这个问题，需要大家首先明白 MySQL 中索引存储的数据结构。这个其实很多小伙伴可能也都听说过，B+Tree 嘛！

B+Tree 是什么？那你得先明白什么是 B-Tree，来看如下一张图：

![format_png][]

前面是 B-Tree，后面是 B+Tree，两者的区别在于：

*  B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
 *  B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
 *  B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。

基于上面两点分析，我们可以得出如下结论：

*  B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
 *  B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。

对于第一点，一个 B+Tree 可以存多少条数据呢？以主键索引的 B+Tree 为例（二级索引存储数据量的计算原理类似，但是叶子节点和非叶子节点上存储的数据格式略有差异），我们可以简单算一下。

> 计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。

基于这样的知识储备，我们可以大致算一下一个 B+Tree 能存多少数据。

假设数据库中一条记录是 1KB，那么一个页就可以存 16 条数据（叶子结点）；对于非叶子结点存储的则是主键值+指针，在 InnoDB 中，一个指针的大小是 6 个字节，假设我们的主键是 bigint ，那么主键占 8 个字节，**当然还有其他一些头信息也会占用字节，我们这里就不考虑了**，我们大概算一下，小伙伴们心里有数即可：

`16*1024/(8+6)=1170`

即一个非叶子结点可以指向 1170 个页，那么一个三层的 B+Tree 可以存储的数据量为：

`1170*1170*16=21902400`

可以存储 2100万 条数据。

在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了。

大家先搞明白这个 B+Tree。

### **2. 两类索引** ###

大家知道，MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。

我们日常所说的主键索引，其实就是聚簇索引（Clustered Index）;主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

*  主键索引的叶子结点存储的是一行完整的数据。
 *  非主键索引的叶子结点存储的则是主键值。

这就是两者最大的区别。

所以，当我们需要查询的时候：

*  如果是通过主键索引来查询数据，例如 select \* from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
 *  如果是通过非主键索引来查询数据，例如 select \* from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，**第一次搜索 B+Tree 拿到主键值后，再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。**

从上面的分析中我们也能看出，通过非主键索引查询要扫描两棵 B+Tree，而通过主键索引查询只需要扫描一棵 B+Tree，所以如果条件允许，还是建议在查询中优先选择通过主键索引进行搜索。

### **3. 一定会回表吗？** ###

那么不用主键索引就一定需要回表吗？

不一定！

如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

举个例子，我有如下一张表：

![0d8d85e8bd8ed7289b58a0b7814b2d94.png][]

uname 和 address 字段组成了一个复合索引，那么此时，虽然这是一个二级索引，但是索引树的叶子节点中除了保存主键值，也保存了 address 的值。

我们来看如下分析：

![b79bc245dffa31cc2beba31f25a3cffc.png][]

可以看到，此时使用到了 uname 索引，但是最后的 Extra 的值为`Using index`，这就表示用到了索引覆盖扫描（覆盖索引），此时直接从索引中过滤不需要的记录并返回命中的结果，这一步是在 MySQL 服务器层完成的，并且不需要回表。

### **4. 扩展** ###

基于第一、二小节的分析，我们再来捋一捋为什么在数据库中建议使用自增主键。

*  自增主键往往占用空间比较小，int 占 4 个字节，bigint 占 8 个字节。由于二级索引的叶子节点存储的就是主键，所以如果主键占用空间小，意味着二级索引的叶子节点将来占用的空间小（间接降低 B+Tree 的高度，提高搜索效率）。
 *  自增主键插入的时候比较快，直接插入即可，不会涉及到叶子节点分裂等问题（不需要挪动其他记录）；而其他非自增主键插入的时候，可能要插入到两个已有的数据中间，就有可能导致叶子节点分裂等问题，插入效率低（要挪动其他记录）。

**当然，这个是基于技术层面的讨论，如果业务上无法使用自增主键，或者有其他要求导致无法使用自增主键，那没办法，在满足新要求的情况下重新选择一个最佳实践吧。**

好啦，今天的主题是**回表**，现在大家明白什么是**回表**了吧？

原文链接：[什么是 MySQL 的“回表”？ - 知乎][MySQL _ -]

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/728e7f14b05445e2a2ca95778d8eea6a.png
[0d8d85e8bd8ed7289b58a0b7814b2d94.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/f9aacdb8765e4b3ba7d2c9f82e760503.png
[b79bc245dffa31cc2beba31f25a3cffc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/8c61792522ff4a859aba654e14abef6b.png
[MySQL _ -]: https://zhuanlan.zhihu.com/p/512644839