知识点2 ： MySQL优化：如何避免回表查询？_什么是索引覆盖？

使用普通索引的情况下，并且包含了非索引字段的时候，会通过该普通索引获取到叶子节点的主键信息，拿到主键信息后再去聚合索引中找到对应的行信息，这个过程就叫做回表查询。

数据库表结构：

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

select id,name where name='shenjian'
 
select id,name,sex where name='shenjian'

多查询了一个属性，为何检索过程完全不同？

什么是回表查询？

什么是索引覆盖？

如何实现索引覆盖？

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

一、什么是回表查询？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)
普通索引(secondary index)
InnoDB聚集索引和普通索引有什么差异？

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

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

注：所以PK查询非常快，直接定位行记录。

InnoDB普通索引的叶子节点存储主键值。

画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。

举个栗子，不妨设有表：

t(id PK, name KEY, sex, flag);

注：id是聚集索引，name是普通索引。

 

 

 

两个B+树索引分别如上图：

（1）id为PK，聚集索引，叶子节点存储行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

通常情况下，需要扫码两遍索引树。

例如：

select * from t where name='lisi';　

 

 

 二、什么是索引覆盖(Covering index)？

 

 

 

三、如何实现索引覆盖？

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

 

 

 

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

画外音，Extra：Using index。

 

 

 

 

 

 

 

 

 

 

 

什么是覆盖索引?

 

 

 

 

 

 

 

 

总结

实践是检验原理的唯一标准。 通过此次实践,想必你已经充分了解并且体验到覆盖索引的概念及其意义了。其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。