深入解析MySQL中的回表查询与索引覆盖技术

MySQL的回表查询与索引覆盖查询

回表查询

名词解释

上篇讲到&＃xff0c;MySQL中分为聚集索引和非聚集索引。非聚集索引的btree叶子节点中存储的是当行数据的PK。所以为了取到具体数据&＃xff0c;则需要通过PK回到聚集索引里去查询数据。着就叫回表查询。扫描了2次索引树。所以效率相对较低。

有图有真相

假设有个T表&＃xff0c;如下图所示。id是聚集索引&＃xff0c;name则是普通索引。

表中有4条数据

Id列的B&＃43;树索引图(聚集索引)

Name列的B&＃43;树索引图(非聚集索引)

执行的查询SQL

select name, sex from t where name &＃61; &＃39;A梦&＃39;;


• 1

其执行过程是这样的

粉红色的路径需要扫描两遍索引树&＃xff0c;第一遍先通过非聚集索引定位到主键值id&＃61;4&＃xff0c;然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询&＃xff0c;即先定位主键值&＃xff0c;再根据主键值定位行记录&＃xff0c;性能相对于只扫描一遍聚集索引树的性能要低一些。

索引覆盖

什么是索引覆盖&＃xff1f;

索引覆盖就是为了避免回表查询的一种解决方案。见名知意&＃xff0c;就是查询的所有列均被所使用的索引的列给到覆盖(可以是单列索引也可以是联合索引)。因为索引中已经包含了要查询的字段的值&＃xff0c;因此查询的时候直接返回索引中的字段值就可以了&＃xff0c;不需要再到表中查询&＃xff0c;避免了对主键索引的二次查询&＃xff0c;也就提高了查询的效率。

举例

还是以上面那张表与SQL为例子&＃xff0c;Id是聚集索引&＃xff0c;name是非聚集索引。
如果我们想要避免回表查询有两种方案。

• 在SQL中只查询name字段。这样name的索引就覆盖到了所有的查询列。
• 将name的索引修改为联合索引(name, sex)&＃xff0c;这样也覆盖到了所有的查询列。

注意事项

-不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值&＃xff0c;而哈希索引、空间索引和全文索引等都不存储索引列值&＃xff0c;索引MySQL只能使用B-Tree索引做覆盖索引。

• 当发起一个被索引覆盖的查询&＃xff08;索引覆盖查询&＃xff09;时&＃xff0c;在explain&＃xff08;执行计划&＃xff09;的Extra列可以看到【Using Index】的信息。

索引覆盖的优点