mysql中的回表查询与索引覆盖
上海的梅雨季来了，最近我也很霉。
没得办法，吹着夏天的风，像斯巴达勇士一样，开始逐一安排我积累的问题。
今天了解一下MySQL中的回表查询与索引覆盖。
什么是回表查询？
要说回表查询，先要从InnoDB的索引实现说起。InnoDB有两大类索引，一类是聚集索引（Clustered Index），一类是普通索引（Secondary Index）。
InnoDB的聚集索引
InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。
1.如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
2.如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。
这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。
InnoDB的普通索引
InnoDB普通索引的叶子节点存储主键值（MyISAM则是存储的行记录头指针）。
什么是回表查询
假设有个t表(id PK, name KEY, sex, flag)，这里的id是聚集索引，name则是普通索引。
表中有四条记录：
id name sex flag
1 sj m A
3 zs m A
5 ls m A
9 ww f B
聚集索引的B+树索引（id是PK，叶子节点存储行记录）：
普通索引的B+树索引（name是KEY，叶子节点存储PK值，即id）：
普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。
select * from t where name = 'lisi';
1
这里的执行过程是这样的：
粉红色的路径需要扫描两遍索引树，第一遍先通过普通索引定位到主键值id=5，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。
索引覆盖
索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。
覆盖索引的定义与注意事项
如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。
要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引MySQL只能使用B-Tree索引做覆盖索引。
另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。
覆盖索引的优点
1.索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
2.索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
3.一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
4.由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。
什么是二级索引（secondary indexes）
对于InnoDB表，在非主键列的其他列上建的索引就是二级索引（因为聚集索引只有一个）。二级索引可以有0个，1个或者多个。二级索引和聚集索引的区别是什么呢？二级索引的节点页和聚集索引一样，只存被索引列的值，而二级索引的叶子页除了索引列值，还存这一列对应的主键值。
————————————————
版权声明：本文为CSDN博主「进击BigFactory」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_37587418/article/details/106694937