hello，大家好，我是张张，「架构精进之路」公号作者。

最近的工作中，遇到一个查询里用到主键索引与二级索引并存的问题状况，那对于这种状况，索引是怎么高效执行的，是否会产生回表查询呢？

等等，首要解释一下，什么是回表？

回表界说：先索引扫描，再经过ID去取索引中未能供给的数据，即为回表。

即先定位主键值，再定位行记载。

1、两类索引

为了更好地阐释这个问题，我们还是从索引来介绍吧。

InnoDB 索引分为两大类，一类是集合索引(Clustered Index)，一类对错集合索引(Secondary Index)

1.1 集合索引（聚簇索引）

InnoDB集合索引的叶子节点存储行记载，因此InnoDB有必要要有且只有一个集合索引。

  • 假如表界说了PK（Primary Key，主键），那么PK便是集合索引。

  • 假如表没有界说PK，则第一个NOT NULL UNIQUE的列便是集合索引。

  • 不然InnoDB会另外创建一个躲藏的ROWID作为集合索引。

这种机制使得依据PK的查询速度非常快，由于直接定位的行记载。

1.2 非集合索引（一般索引、非聚簇索引、二级索引）

一般索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的一般索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的一般索引存储的是记载指针。

Q：为什么非主键索引结构叶子结点存储的是主键值？

A：减少了出现行移动或者数据页割裂时二级索引的保护工作（当数据需求更新的时候，二级索引不需求修正，只需求修正聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需求修正聚簇索引就可以了，不需求重新构建二级索引）

在运用非集合索引时，为了取到详细数据，则需求经过PK回到集合索引里去查询数据。这就叫回表查询，扫描了2次索引树，所以效率相对较低。

2、运用示例

一例胜千言，show me you code！

2.1 建表操作

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> sex tinyint(4),
    -> type varchar(8),
    -> primary key (id),
    -> index idx_name (name)
    -> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，name 字段是一般索引（二级索引）

2.2 填充数据

mysql> select * from user;
+----+--------+------+------+
| id |  name  |  sex | type |
+----+--------+------+------+
| 1 | sj  |  m  |  A  |
| 3 | zs  |  m  |  A  |
| 5 | ls  |  m  |  A  |
| 9 | ww  |  f  |  B  |
+----+-----+-----+-----+

2.3 索引结构

  • 聚簇索引（ClusteredIndex）

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记载的数据

还傻傻分不清MySQL回表查询与索引覆盖？

  • 一般索引（secondaryIndex）

name 是一般索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

还傻傻分不清MySQL回表查询与索引覆盖？

2.4 查找过程

  • 一般索引查找过程

假如查询条件为主键（聚簇索引），则只需扫描一次B+树即可经过聚簇索引定位到要查找的行记载数据。

select * from user where name = 'lisi';
一般索引由于无法直接定位行记载，其查询过程在通常状况下是需求扫描两遍索引树的。

实际执行过程：

还傻傻分不清MySQL回表查询与索引覆盖？

途径需求扫描两遍索引树，第一遍先经过一般索引定位到主键值id=5，然后第二遍再经过集合索引定位到详细行记载。

这便是所谓的回表查询，即先定位主键值，再依据主键值定位行记载，性能相对于只扫描一遍集合索引树的性能要低一些。

3、索引掩盖

索引掩盖是一种避免回表查询的优化战略。

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

3.1 怎么完成掩盖索引

将要查询的数据作为索引列树立一般索引（可以是单列索引，也可以一个索引句子界说一切要查询的列，即联合索引），这样的话就可以直接回来索引中的的数据，不需求再经过集合索引去定位行记载，避免了回表的状况产生。

explain select id, name from user where name = 'lisi';

explain分析：由于name是一般索引，运用到了name索引，经过一次扫描B+树即可查询到相应的结果，这样就完成了掩盖索引

还傻傻分不清MySQL回表查询与索引覆盖？

– END –

希望今天的解说对大家有所协助，谢谢！

Thanks for reading!

作者：架构精进之路，十年研发风雨路，大厂架构师，CSDN 博客专家，专心架构技能沉积学习及共享，工作与认知升级，坚持共享接地气儿的干货文章，期待与你一起生长。
关注并私信我回复“01”，送你一份程序员生长进阶大礼包，欢迎勾搭。