Mysql 回表查询和覆盖索引概念

最近在补Mysql基础时，发现很多判断查询语句是否优化时都通过回表查询等术语作为指标，看的很懵逼，就开始查找相关的文献，这时发现了一篇很通俗易懂的文章zhangdeTalk的博客 《MySQL 的覆盖索引与回表》

一、回表查询

因为InnoDB具有聚簇索引(主键索引)和二级索引(普通索引)，且两种索引叶子节点存放的内容不一样，前者存储了所有的行数据，而后者仅仅存储主键的数据。

MyISAM的索引叶子节点存储得是行记录的指针

那么从普通索引是无法直接定位到行记录的，先扫描一遍普通索引获得主键值后在扫描聚簇索引，这就是所谓的回表查询，先定义主键值，在定位行记录。

二、覆盖索引

MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

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

三、如何实现覆盖索引

常见的方法：将被查询字段，建立到联合索引中去

对于一个表

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

执行如下查询语句

select id,name from user where name='shenjian';　

通过name二级索引，索引叶子存储了主键的id，所以通过这个索引树可以直接返回结果，无需回表，符合索引覆盖，效率高。

那么对于这样的语句

select id,name,sex from user where name='shenjian';

虽然id和name都可以通过name的二级索引获得，但是sex的值仍然需要回表查询，那么可以把单列索引升级为联合索引

alter table user drop key(name);
alter table user add key(name,sex);

四、哪些场景使用覆盖索引来优化SQL

1.全表count查询优化

select count(name) from user;

将name列添加索引

2.列查询回表优化

就是第三节中介绍的使用联合索引代替单列索引

3.分页查询
如果只有name是索引，执行下一条sql语句

select id,age,name from user order by age limit 100,2;

根据Extra发现使用的是Using filesort文件排序。

做法是可以建立联合索引key(age,name)