Mysql回表查询、索引覆盖等概念

参考：【mysql】MySQL中的回表查询、索引覆盖、索引下推、谓词下推_mysql回表-CSDN博客

【一】回表查询
【1】索引的存储形式
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引		必须有，而且只有1个
二级索引		可以存在多个

聚集索引选取规则

（1）如果存在主键，主键索引就是聚集索引。
（2）如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
（3）如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

该文章中用到user表，字段有id、name、gender。

聚集索引和二级索引的具体结构如下：

（1）聚集索引的叶子节点下挂的是这一行的数据 。
（2）二级索引的叶子节点下挂的是该字段值对应的主键值

【2】sql的执行过程
接下来，我们来分析一下，当我们执行如下的SQL语句时，

select * from user where name='arm'

具体的查找过程是什么样子的。

具体过程如下：
（1）由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
（2）由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
（3）最终拿到这一行的数据，直接返回即可。

得到回表查询的概念：
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

【3】案例分析
以下两条SQL语句，那个执行效率高? 为什么?

-- A语句
-- 备注: id为主键，name字段创建的有索引；
select * from user where id = 10 ;-- B语句
select * from user where name = 'Arm' ;

A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

【4】什么是mysql的回表
在MySQL数据库中，回表（Look Up）指的是在进行索引查询时，首先通过索引定位到对应页，然后再根据行的物理地址找到所需的数据行。换句话说，回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。

【5】什么情况下会触发回表？
MySQL的回表操作通常在以下情况下会发生：

（1）索引不Cover所有需要查询的字段
当查询语句中需要返回的列不在索引列上时，即使通过索引定位了相关行，仍然需要回表获取其他列的值。

（2）使用了非聚簇索引
非聚簇索引（Secondary Index）只包含了索引列的副本以及指向对应主键的引用，查询需要通过回表才能获取完整的行数据。

（3）使用了覆盖索引但超过了最大索引长度
在MySQL的InnoDB存储引擎中，每个索引项的最大长度是767字节，如果查询需要返回的字段长度超过了该限制，同样会触发回表操作。

需要注意的是，回表操作主要发生在读取操作（SELECT）中，写入操作（INSERT、UPDATE、DELETE）一般不会触发回表。

【6】哪些情况下不会触发回表？
在某些特殊情况下，MySQL的回表操作可以被避免：

（1）覆盖索引
如果查询的字段都在某个索引上，并且没有超过最大索引长度限制，MySQL可以直接从索引中获取所需数据，而无需回表。

（2）使用聚簇索引
InnoDB存储引擎的主键索引是聚簇索引，它包含了整个行的数据。当查询条件使用了主键或者通过主键查询时，MySQL可以直接从主键索引中获取所有需要的数据，无需回表。

【7】回表操作的问题和场景
回表操作虽然提供了更全面的数据信息，但也带来了一些问题和局限性。

（1）性能问题
回表操作通常需要访问两次索引，增加了IO开销和CPU消耗，对查询性能有一定的影响。特别是在高并发、大数据量的情况下，回表可能成为性能瓶颈。

（2）数据一致性
由于回表操作是基于物理地址来获取数据，如果在回表过程中发生了数据修改（如DELETE、UPDATE），则可能会读取到不一致或错误的数据。

（3）是否使用覆盖索引的判断
在选择是否使用覆盖索引时，需要综合考虑查询的字段以及字段长度，以及查询操作的频率和数据量。如果查询需要返回的字段较多或字段长度较长，可能需要权衡回表带来的性能损耗和数据完整性的需求。

在实际应用中，我们可以根据具体的场景来决定是否使用回表操作。下面列举了一些使用回表的典型场景：

需要返回更全面的数据：有些查询场景下，返回的字段可能不仅仅是索引所包含的列，此时回表可以提供更全面的数据信息。
使用非聚簇索引：当表中没有定义主键或者查询条件没有使用主键时，非聚簇索引成为主要的索引选择，但回表操作则难以避免。
超过最大索引长度限制：如果需要返回的字段长度超过了最大索引长度限制，即使使用了覆盖索引也无法避免回表，此时需要注意回表带来的性能损耗。

【8】总结
综上所述，MySQL的回表操作是在索引查询时，通过主键索引再次访问以获取完整数据记录的过程。

【二】、索引覆盖，

【三】、