回表与覆盖索引，索引下推

转载地址： https://www.jianshu.com/p/d0d3de6832b9

一、什么是回表查询？

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了主键，则PK就是聚集索引；
（2）如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

先创建一张表，sql 语句如下：

create table xttblog(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine = InnoDB;

然后，我们再执行下面的 SQL 语句，插入几条测试数据。

INSERT INTO xttblog(id, k, name) VALUES(1, 2, 'xttblog'),
    (2, 1, '哈哈'),
    (3, 3, '哈哈哈哈哈');

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少，如果数据量大的话，你能很明显的看出两次查询所用的时间，很明显使用主键查询效率更高。

更多如下图：

（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

小总结

使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。

二、什么是索引覆盖？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

三、如何实现索引覆盖？

1、常见的方法是：将被查询的字段，建立到联合索引里去。

例子

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

第一个sql：
select id,name from user where name=‘shenjian’;

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。
Extra：Using index。

第二个sql：
select id,name,sex from user where name=‘shenjian’;

能够命中name索引，索引叶子节点存储了主键id，没有储存sex，sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫描聚集索引获取sex字段，效率会降低。

Extra：Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user1 (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

可以看到：

select id,name … where name=‘shenjian’;
select id,name,sex … where name=‘shenjian’;
单列索升级为联合索引(name, sex)后，索引叶子节点存储了主键id，name，sex，都能够命中索引覆盖，无需回表。

画外音，Extra：Using index。

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

场景1：全表count查询优化

原表为：
user(PK id, name, sex)；

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

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

场景2：列查询回表优化

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

场景3：分页查询

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

五、如何创建有效的索引

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

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

如何选择前缀

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

使用多列索引

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

选择合适的索引列顺序

• 经验是将选择性最高的列放到索引最前列，可以在查询的时候过滤出更少的结果集 但这样并不总是最好的，如果考虑到 group by 或者
  order by 等情况，再比如考虑到一些特别场景下的 guest 账号等数据情况，上面的经验法则可能就不是最适用的

覆盖索引

• 所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了 MySQL 中只能使用 B-Tree
  索引做覆盖索引，因为哈希索引等都不存储索引的列的值，覆盖索引对于 MyISAM 和 InnoDB
  都非常有效，可以减少系统调用和数据拷贝等时间 Tips：减少 select * 操作

使用索引扫描来做排序

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

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

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

压缩索引

• MyISAM
  中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引，这部分优化默认也是只针对字符串的，但是可以自定义对整数做压缩
• 这个优化在一定情况下性能比较好，但是对于某些情况可能会导致更慢，因为前缀压缩决定了每个关键字都必须依赖于前面的值，所以无法使用二分查找等，只能顺序扫描，所以如果查找的是逆序那么性能可能不佳

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

• MySQL 的唯一限制和主键限制都是通过索引实现的，所以不需要在同一列上增加主键、唯一限制再创建索引，这样是重复索引
• 再举个例子，如果已经创建了索引（A，B），那么再创建索引（A）的话，就属于重复索引，因为 MySQL
• 索引是最左前缀，所以索引（A，B）本身就可以使用索引（A），但是创建索引（B）的话不属于重复索引
• 尽量减少新增索引，而应该扩展已有的索引，因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢
• 可以考虑删除没有使用到的索引，定位未使用的索引，有两个办法，在 Percona Server 或者 MariaDB 中打开userstates 服务器变量，然后等服务器运行一段时间后，通过查询INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率

索引和锁

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

减少索引和数据碎片

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

三、什么是索引下推

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

mysq> 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的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。