MySQL回表与覆盖索引

1. MySQL存储引擎

MySQL比较常见的三种存储引擎：InnoDB、MyISAM、Memory。

这三种存储引擎的实现方式各不相同，InnoDB、MyISAM使用的是B+树，Memory使用的是哈希索引。虽然InnoDB、MyISAM都使用的是B+树，但两者还是不完全相同的。InnoDB中，主键索引的B+树叶子节点存储的是整行数据。MyISAM主键索引的B+树叶子节点存储的是整行数据所在内存中的地址。

2. MySQL索引结构

2.1 B树和B+树定义

MySQL索引结构是B-tree与B+tree结构。B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。如人们可能会以为B-树是一种树，而B树又是另一种树。而事实上是，B-tree就是指的B树。

B树就是一种平衡多路查找树，其每个节点可以有两个以上的子节点。B+树在B树的基础上做了改进，在B+树中，出现在分支结点中的元素会被当作它们在该分支结点位置的中序后继者（叶子结点）中再次列出，且每一个叶子结点都会保存一个指向后一叶子结点的指针。

2.2 B树和B+树区别

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

2.3 B+Tree优点

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

3. MySQL索引回表

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

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

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：
1.主键索引的叶子结点Data域存储的是一行完整的数据。
2.非主键索引的叶子结点Data域存储的则是主键值。

所以，当我们需要查询的时候
1.如果是通过聚簇索引也就是主键索引来查询，此时聚簇索引叶子结点Data域存储的是完整的数据，通过一次B+Tree搜索就可以查询到数据。例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。

2.如果是通过非聚簇索引也就是非主键索引来查询，通过非聚簇索引叶子结点Data域查询的主键的值，然后再通过主键索引叶子结点Data域找到完整数据，也就是通过两次B+Tree搜索才能找到数据。例如 select * from user where username=‘javaboy’，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

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

4. MySQL覆盖索引

1.使用非聚簇索引也就是非主键索引一定会回表吗？不一定！
如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

比如有一个部门表dept，id是主键索引，code是非主键索引，如果通过非主键索引查询整行记录，就需要先通过非聚簇索引code查询到聚簇索引也就是主键索引id，在根据id查询到整条记录，这个需要回表，但是，如果通过code查询主键索引id，这个通过非主键索引code直接可以查询到，就不需要回表。

2.MySQL覆盖索引实现验证

-- 创建部门表
CREATE TABLE `dept` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `code` varchar(32) NOT NULL COMMENT '部门编码',
  `name` varchar(30) NOT NULL COMMENT '部门名称',
  `address` varchar(60) DEFAULT NULL COMMENT '部门地址',
  `ceo` int(11) DEFAULT NULL COMMENT '随机数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='部门表';

-- 查看执行计划
EXPLAIN
SELECT * FROM dept where code = 'ubVBVXam';

-- 覆盖索引 Extra 的值为 Using index
EXPLAIN
SELECT id FROM dept where code = 'ubVBVXam';

执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dept		const	idx_code	idx_code	130	const	1	100.00	Using index

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

5. 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 操作就可以了。

参考文档
MySQL索引数据结构B-tree和B+tree详解
MySQL数据库索引及失效场景
MySQL架构设计详解