关于java:什么是-MySQL-的回表

6次阅读

共计 2434 个字符，预计需要花费 7 分钟才能阅读完成。

小伙伴们在面试的时候，有一个特地常见的问题，那就是数据库的回表。什么是回表？为什么须要回表？

明天松哥就来和大家聊一聊这个话题。

要搞明确这个问题，须要大家首先明确 MySQL 中索引存储的数据结构。这个其实很多小伙伴可能也都据说过，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 中，叶子结点通过指针连贯在一起，这样如果有范畴扫描的需要，那么实现起来将非常容易，而对于 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。

大家晓得，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，所以如果条件容许，还是倡议在查问中优先选择通过主键索引进行搜寻。

那么不必主键索引就肯定须要回表吗？

不肯定！

如果查问的列自身就存在于索引中，那么即便应用二级索引，一样也是不须要回表的。

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

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

咱们来看如下剖析：

能够看到，此时应用到了 uname 索引，然而最初的 Extra 的值为 Using index，这就示意用到了索引笼罩扫描（笼罩索引），此时间接从索引中过滤不须要的记录并返回命中的后果，这一步是在 MySQL 服务器层实现的，并且不须要回表。

基于第一、二大节的剖析，咱们再来捋一捋为什么在数据库中倡议应用自增主键。

自增主键往往占用空间比拟小，int 占 4 个字节，bigint 占 8 个字节。因为二级索引的叶子节点存储的就是主键，所以如果主键占用空间小，意味着二级索引的叶子节点未来占用的空间小（间接升高 B+Tree 的高度，进步搜寻效率）。
自增主键插入的时候比拟快，直接插入即可，不会波及到叶子节点决裂等问题（不须要移动其余记录）；而其余非自增主键插入的时候，可能要插入到两个已有的数据两头，就有可能导致叶子节点决裂等问题，插入效率低（要移动其余记录）。

当然，这个是基于技术层面的探讨，如果业务上无奈应用自增主键或者有其余要求导致无奈应用自增主键，那没方法，在满足新要求的状况下从新抉择一个最佳实际吧。

好啦，明天的主题是回表，当初大家明确什么是回表了吧？

正文完