【sql】MySQL覆盖索引和回表查询的执行过程

MySQL覆盖索引覆盖索引是指查询使用了索引且返回需要的列，在该索引列中已经全部能够找到。

在查询时，尽量使用覆盖索引，减少select *。

表中存在联合索引 idx_user_pro_age_sta (关联了三个字段profession, age, status)，该索引也是一个二级索引，该叶子节点下面的是这一行的主键id。

当查询返回的数据在id, profession, age, status 中，则直接走二级索引返回数据，

如果查询字段超出这个范围，就需要拿到主键id, 再去扫描聚集索引获取额外的数据，这个过程就是回表。

当我们一直使用 select * 查询返回所有字段值，很容易造成回表查询（除非根据主键查询，此时只会扫描聚集索引）。

演示如下：

explain select id, profession, from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select id, profession, age, status from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select id, profession, age, status, name from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select * from tb_user where profession=’软件工程’ and age=31 and status=’0’;??

为了让大家更清楚地理解，什么是覆盖索引和回表查询，我们一起来看下一组sql的执行过程：

A. 表结构及索引示意图:id是主键，是一个聚集索引。name字段建立了普通索引，是一个二级索引(辅助索引)。??

B. 执行SQL: ?select * from tb_user where id = 2;根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。??

C. 执行SQL: ?selet id,name from tb_user where name = 'Arm';

虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索 引中，这两个值都是可以直接获取到的，

因为覆盖索引，所以不需要回表查询，性能高。???

D. 执行SQL: selet id,name,gender from tb_user where name = 'Arm';

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。???

思考一下：一张表四个字段（id, username, password, status）,如何对sql优化最优？

select id, username, password from tb_user where username=’itcast’;

答案：针对username, password 建立联合索引，避免回表查询。