Mysql优化(十)什么是 MySQL 的 回表 ？怎么减少回表的次数？

索引优化

索引结构

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

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

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

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

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

1. B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
2. 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，唯一的区别在于叶子结点中存储的内容不同：

• 主键索引的叶子结点存储的是一行完整的数据。
• 非主键索引的叶子结点存储的则是主键值。叶子结点不包含行记录的全部数据；非主键的叶子结点中，除了用来排序的key还包含一个bookmark；该书签存储了聚集索引的key。

这就是两者最大的区别。

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

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

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

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

众所周知在InnoDB引用的是B+树索引模型，这里对B+树结构暂时不做过多阐述，很多文章都有描述，在第二问中我们对索引的种类划分为两大类主键索引和非主键索引，那么问题就在于比较两种索引的区别了，我们这里建立一张学生表，其中包含字段id设置主键索引、name设置普通索引、age(无处理)，并向数据库中插入4条数据：（"小赵", 10）（"小王", 11）（"小李", 12）（"小陈", 13）

create table `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  primary key (`id`),
  KEY `I_name` (`name`)
) ENGINE=InnoDB;

INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

这里我们设置了主键为自增，那么此时数据库里数据为

每一个索引在 InnoDB 里面对应一棵B+树，那么此时就存着两棵B+树。

可以发现区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id, 在我们执行如下sql后

SELECT age FROM student WHERE name = '小李'；

流程为：

1. 在name索引树上找到名称为小李的节点 id为 03
2. 从id索引树上找到id为 03的节点 获取所有数据
3. 从数据中获取字段命为age的值返回 12

在流程中从非主键索引树搜索回到主键索引树搜索的过程称为：回表，在本次查询中因为查询结果只存在主键索引树中，我们必须回表才能查询到结果，那么如何优化这个过程呢？引入正文覆盖索引

覆盖索引

就是把单列的非主键 索引 修改为 多字段 的联合索引, 在一棵索引数上。 就找到了想要的数据, 不需要去主键索引树上,再检索一遍 这个现象,称之为 索引覆盖.

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

• 如何使用是覆盖索引？

之前我们已经建立了表student，那么现在出现的业务需求中要求根据名称获取学生的年龄，并且该搜索场景非常频繁，那么先在我们删除掉之前以字段name建立的普通索引，以name和age两个字段建立联合索引，sql命令与建立后的索引树结构如下

# 删除之前的非主键索引
alter table student drop index I_name;
# 添加非主键索引
alter table student add index I_name_age(name, age);

那在我们再次执行如下sql后：

select age from student where name = '小李'；

流程为：

1. 在name,age联合索引树上找到名称为小李的节点
2. 此时节点索引(非主键索引)里包含信息age 直接返回 12

• 如何确定数据库成功使用了覆盖索引呢？

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息:

这里我们很清楚的看到Extra中using index表明我们成功使用了覆盖索引。

覆盖索引避免了回表现象的产生，从而减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是性能优化的一种手段。

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

不一定！

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

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

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

我们来看如下分析:

explain select uname,address from user where uname='javaboy';

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

哪些场景可以利用索引覆盖来优化SQL？

1. 全表count查询优化

直接：

 select count(name) from user;
 不能利用索引覆盖。

添加索引：

 alter table user add key(name);
 就能够利用索引覆盖提效。

2. 列查询回表优化

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

3. 分页查询

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

如何创建有效的索引

1. 如果需要索引很长的字符串，此时需要考虑前缀索引

前缀索引即选择所需字符串的一部分前缀作为索引，这时候，需要引入一个概念叫做索引选择性，索引选择性是指不重复的索引值与数据表的记录总数的比值，可以看出索引选择性越高则查询效率越高，当索引选择性为1时，效率是最高的，但是在这种场景下，很明显索引选择性为1的话我们会付出比较高的代价，索引会很大，这时候我们就需要选择字符串的一部分前缀作为索引，通常情况下一列的前缀作为索引选择性也是很高的

如何选择前缀

• 计算该列完整列的选择性，使得前缀选择性接近于完整列的选择性

2. 使用多列索引

尽量不要为多列上创建单列索引，因为这样的情况下最多只能使用一星索引，这样的话，不如去创建一个全覆盖索引，在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能，MySQL 5.0 中引入了合并索引，在一定程度上可以表内多个单列索引来定位指定的结果，但是 5.0 以前的版本，如果 where 中的多个条件是基于多个单列索引，那么 MySQL 是无法使用这些索引的，这种情况下，还不如使用 union。

3. 选择合适的索引列顺序

经验是将选择性最高的列放到索引最前列，可以在查询的时候过滤出更少的结果集。

但这样并不总是最好的，如果考虑到 group by 或者 order by 等情况，再比如考虑到一些特别场景下的 guest 账号等数据情况，上面的经验法则可能就不是最适用的

4. 覆盖索引

所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了

覆盖索引对于 MyISAM 和 InnoDB 都非常有效，可以减少系统调用和数据拷贝等时间.

Tips：减少 select * 操作

5. 使用索引扫描来做排序

MySQL 生成有序的结果有两种方法：通过排序操作，或者按照索引顺序扫描；使用排序操作需要占用大量的 CPU 和内存资源，而使用 index 性能是很好的，所以，当我们查询有序结果时，尽量使用索引顺序扫描来生成有序结果集。

怎样保证使用索引顺序扫描？

• 索引 列 顺序和 ORDER BY 顺序一致
• 所有列的排序方向一致
• 如果关联多表，那么只有当 ORDER BY 子句引用的字段全部为第一张表时，才能使用索引做排序，限制依然是需要满足索引的最左前缀要求

6. 压缩索引

MyISAM 中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引，这部分优化默认也是只针对字符串的，但是可以自定义对整数做压缩

这个优化在一定情况下性能比较好，但是对于某些情况可能会导致更慢，因为前缀压缩决定了每个关键字都必须依赖于前面的值，所以无法使用二分查找等，只能顺序扫描，所以如果查找的是逆序那么性能可能不佳

7. 减少重复、冗余以及未使用的索引

MySQL 的唯一限制和主键限制都是通过索引实现的，所以不需要在同一列上增加主键、唯一限制再创建索引，这样是重复索引

再举个例子，如果已经创建了索引（A，B），那么再创建索引（A）的话，就属于重复索引，因为 MySQL 索引是最左前缀，所以索引（A，B）本身就可以使用索引（A），但是创建索引（B）的话不属于重复索引

尽量减少新增索引，而应该扩展已有的索引，因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢

可以考虑删除没有使用到的索引，定位未使用的索引，有两个办法，在 Percona Server 或者 MariaDB 中打开 userstates 服务器变量，然后等服务器运行一段时间后，通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率

8. 索引和锁

InnoDB 支持行锁和表锁，默认使用行锁，而 MyISAM 使用的是表锁，所以使用索引可以让查询锁定更少的行，这样也会提升查询的性能，如果查询中锁定了1000行，但实际只是用了100行，那么在 5.1 之前都需要提交事务之后才能释放这些锁，5.1 之后可以在服务器端过滤掉行之后就释放锁，不过依然会导致一些锁冲突

9. 减少索引和数据碎片

• 首先我们需要了解一下为什么会产生碎片，比如 InnoDB 删除数据时，这一段空间就会被留空，如果一段时间内大量删除数据，就会导致留空的空间比实际的存储空间还要大，这时候如果进行新的插入操作时，MySQL 会尝试重新使用这部分空间，但是依然无法彻底占用，这样就会产生碎片
• 产生碎片带来的后果当然是，降低查询性能，因为这种情况会导致随机磁盘访问
• 可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据

什么是索引下推

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

 select * from tuser where name like '张%' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？

当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。