MySQL索引-3回表查询与覆盖索引

回表查询是什么

前言：理解回表查询之前需要先了解聚集索引的概念，参考文章【MySQL索引-聚集索引探讨】: https://mp.csdn.net/mp_blog/creation/editor/127338329 。

回表查询指的是当我们使用普通索引进行数据查找时，不能得到我们需要的所有列数据，导致我们需要通过聚集索引（主键索引）再次去查询一次。

我们可以从一个例子来理解回表查询的过程（本文探讨的案例均为innoDB下的数据表）。

设有如下数据表EMPLOYEE：

ID	NAME	CITY
5	Bob	Shanghai
8	Lee	Beijing
18	Sam	Wuhan
24	Rose	Wuhan
40	Alan	Nanjing
55	Hobo	Chengdu

其中ID建立为主键索引（聚集索引），NAME建立了普通索引。

现在需要进行一次如下的查询：

select NAME,CITY from EMPLOYEE where NAME="Lee";

其查询的过程大致如下：首先通过普通索引去找到NAME="Lee"对应的叶子节点，该叶子节点存储的是主键值（ID=8），而该查询语句还需要获取CITY字段的值，于是MySQL要再根据该主键值（ID=8）进行主键索引查找，从而获得完整的数据（主键索引叶子节点存储了所有数据行）。这个过程就是所谓的回表查询。

该过程示意如下：

第一步，通过普通索引获取到主键（红色箭头示意索引过程）。

第二步，通过主键索引查询到其他需要的数据（CITY）。

回表查询与覆盖索引

针对上面提到的回表查询，由于回表导致多次扫描索引树，会降低查询效率，这就引出一个问题：如何有效避免回表查询？

常用的方法是：将查询需要的字段添加到索引中，建立联合索引。比如上面提到的查询语句，可以将(NAME,CITY)做成一个联合索引，这样在首次扫描索引树的时候就能从索引树本身获取到需要的所有信息，从而避免了回表。这里使用的方法就是覆盖索引。

我们通过一个实例来认识覆盖索引。

首先建立一个表t1，包含一个主键索引和一个name上的普通索引：

create table t1 (
id int,
name varchar(20),
city varchar(20),
hobby varchar(20),
primary key(id),
index idx_t1_name(name) -- name建立普通索引
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

并随便插入几条数据：

insert into t1(id,name,city,hobby) values(1,'p1','c1','h1');
insert into t1(id,name,city,hobby) values(2,'p2','c2','h2');
insert into t1(id,name,city,hobby) values(3,'p3','c3','h3');
insert into t1(id,name,city,hobby) values(4,'p4','c4','h4');

然后建立一个表t2，包含一个主键索引和一个(name,city)上的联合索引，并插入同样的数据。

create table t1 (
id int,
name varchar(20),
city varchar(20),
hobby varchar(20),
primary key(id),
index idx_t2_nc(name,city)-- name,city建立联合索引
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后用explain关键字分析同时查询name和city数据并以name作为where条件的sql。

mysql中可以使用explain关键字来查看sql语句的执行计划。

先看表t1：

explain select name,city from t1 where name='h2';

结果为：

然后看表t2：

explain select name,city from t2 where name='h2';

结果为：

EXtra为其他的重要信息，Using index 表示相应的select 语句使用到了覆盖索引。

通过这个例子就了解到了覆盖索引。