MySQL优化（三）回表详解

一. 初识索引结构

首先要来介绍MySQL中索引存储的数据结构。
众所周知MySQL中索引存储的数据结构是B+树

1.1 什么是B+树，什么是B树，区别是什么

B树

B+树

B树和B+树的区别

1.指向具体记录的指针不同：B树中，所有的节点都会带有指向具体记录的指针；B+树中只有叶子节点会带有指向具体记录的指针。（即，B树中叶子节点和非叶子节点都存储数据，而B+树叶子节点存储数据，非叶子节点存储索引,让非叶子节点跟跟多的叶子节点连接。）
2.叶子节点是否连接到一起，如何连接到一起:B树中不同的叶子之间没有连到一起，B+树中所有的叶子节点通过指针连接在一起。（Mysql底层进行了优化，B+树的叶子节点的指针是双向的）
3.搜索效率稳定性:B树中可能在非叶子节点就拿到了指向具体记录的指针，搜索效率不稳定；B+树中，一定要到叶子节点中才可以获取到具体记录的指针，搜索效率稳定。
4.B+树中，由于非叶子节点不带有指向具体记录的指针，所以非叶子节点中可以存储更多的索引项，变相的增加了树的存储量，降低了树的高度，提升了搜索的效率和稳定性。
5.B+树中，叶子节点通过指针连接到一起，实现容易范围扫描，B树如果进行范围扫描会有回旋跳转的问题，扫描的时候在叶子节点和非叶子节点之间来回移动。

1.2 B+树可以存储多少条数据

对于第一点，一个 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 可以存储的数据量为：

1170117016=21902400

可以存储 2100万 条数据.

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

设置主键的小建议：

设置数字为主键，不要设置uuid为主键：
1.数字是递增的，不用过多的去设置，UUID则总是要去设置。
2.

1.3 两种索引

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

索引分类：

主键索引 —> 聚簇索引
除了主键索引之外的索引 —> 非聚簇索引

主键索引和非主键索引的不同：

不论是主键索引还是非主键索引，使用的数据结构都是B+树。

1.唯一的区别是叶子结点中存储的内容不同：

主键索引的叶子节点存储的是一行完整的数据
非主键索引的叶子节点存储的是主键值

2.导致 查询方法不同：

通过主键索引查询：例如 select * from user where id=100 ，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。

通过非主键索引查询：例如 select * from user where username=‘javaboy’ ，那么此时需要先搜索 username 这一列索引的 B+Tree，去索引连接的叶子节点中搜索得到主键的值，然后再去搜索主键索引的 B+Tree，通过主键索引找到主键索引对应的叶子节点的值就可以获取到一行完整的数据。

回表定义：

对于非主键索引，我们搜索了两颗B+树，第一次先从查询项的B+树找到主键的值，第二次通过主键的值找到主键索引的B+树，然后获得我们想要的数据。

1.4 不用主键索引一定会回表吗

答案是不一定

1.如果需要查询的那一列，它本身就存在索引中，那么即使使用二级索引一样也是不需要回表的。
2.如下，如果uname和address字段组成了一个复合索引，即使组成的这个索引是复合索引即二级索引，但是这个复合索引的叶子节点中除了保存主键值，也保存了address的值。

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

二. 思考

MySQL的优化中为什么建议自增主键。
1.自增主键往往占用空间比较小， int 占 4 个字节，bigint 占 8 个字节。由于二级索引的叶子节点存储的就是主键，所以如果主键占用空间小，意味着二级索引的叶子节点将来占用的空间小。
2.自增主键插入的时候快，直接插入就可以，不会涉及到叶子节点分裂的问题；而其他非主键自增插入的时候，则需要插入到两个已有的数据中间，即可能导致叶子节点分裂的问题，从而致使插入效率低。