MySQL中回表查询避免和优化指南

目录

• 什么是回表查询？
• 回表查询发生的过程
• 聚集索引（Clustered Index）
• 非聚集索引（Secondary Index）
• 回表查询的过程
• 案例场景
• 1. 根据主键查询，不会回表
• 2. 索引列和查询字段不匹配
• 3. 存在覆盖索引但查询的字段超出覆盖范围
• 如何避免回表查询
• 1. 创建覆盖索引
• 2. 减少查询字段
• 3. 分析执行计划
• 总结

什么是回表查询？

回表查询（Table Lookup或Back to Table）是数据库查询中的一个过程，指在使用非聚集索引（Secondary Index或Non-Clustered Index）定位数据时，由于索引节点中不包含查询所需的全部列，数据库需要根据索引找到数据行的位置（通常是主键或行标识符），然后回到聚集索引或数据表中读取完整的数据行。

这种行为通常发生在查询的字段未被索引覆盖，索引不足以直接满足查询需求时。例如，在mysql中，如果索引列无法完全满足查询字段，则数据库会通过索引找到记录位置后回表读取非索引列的数据。

如果对上面的概念理解的还不够透彻，先不着急，我们下面逐步拆解，并通过案例逐步分析讲解。

www.devze.com

回表查询发生的过程

这里我们以 MySQL 的InnoDB存储引擎为例来进行讲解。要想理解回表查询的过程，首先需要了解InnoDB的两种类型索引——聚集索引（Clustered Index编程客栈）和非聚集索引（Secondary Index）。

聚集索引（Clustered Index）

在InnoDB中，聚集索引的叶子节编程客栈点存储的是完整的行记录。因此，InnoDB的每个表必须有且只有一个聚集索引：

• 如果表定义了主键（Primary Key），则主键默认就是聚集索引；
• 如果表未定义主键，但存在非空的唯一索引（NOT NULL UNIQUE），则第一个满足条件的唯一索引将被用作聚集索引；
• 如果上述条件都不满足，InnoDB会自动创建一个隐藏的 row_id 列作为聚集索引。

非聚集索引（Secondary Index）

非聚集索引，也称为普通索引或二级索引，是指除聚集索引之外的其他索引。在InnoDB中，非聚集索引的叶子节点存储的是索引键值和其对应的聚集索引键值（而不是行指针）。这与MyISAM不同，MyISAM的普通索引叶子节点存储的是记录指针而非主键值。

在补充了InnoDB引擎的聚集索引和非聚集索引理论之后，下面我们来看回表查询的过程。

回表查询的过程

当一个查询使用非聚集索引时，数据库会先通过非聚集索引找到符合条件的记录。非聚集索引的叶子节点包含索引键值以及对应的聚集索引键值（主键值）。

如果查询需要的字段不完全在非聚集索引中，则数据库引擎会根据非聚集索引中的聚集索引键值，再通过聚集索引定位到完整的行记录，以获取查询所需的字段数据。这种操作过程，就是回表查询的基本过程。

需要注意，回表查询发生的场景是很常见的，尤其是当查询字段包含不在非聚集索引中的列时（即非覆盖索引的情况）。在接下来的案例中，我们来看看哪些场景会发生回表，哪些场景又不会发生回表。

案例场景

1. 根据主键查询，不会回表

使用表的主键（聚集索引）查询数据，不会发生回表操作：

SELECT * FROM users WHERE id = 3;

根据关于聚集索引的理论，由于聚集索引的叶子节点存储的是完整的行记录，所以不需要进行回表。

2. 索引列和查询字段不匹配

如果查询中使用了索引列，但查询结果中还包括非索引列（字段不完全在索引），依然会触发回表。例如：

假设有以下索引覆盖：

CREATE INDEX idx_name ON users (name);

查询：

SELECT name, age FROM users WHERE name = 'John';

尽管索引可以快捷地定位记录，但如果查询的 age 列不在索引中，会回表读取数据。

3. 存在覆盖索引但查询的字段超出覆盖范围

覆盖索引指的是索引本身已经完全包含了查询所需的字段，在这种情况下不会发生回表查询；否则会发生。

例如，在 users 表中创建以下覆盖索引：

CREATE INDEX idx_users_name_age ON users (name, age);

对于如下查询：

SELECT name, age FROM users WHERE name = 'John';

索引 idx_users_name_age 已经覆盖了 name 和 age，索引本身已经可以满足查询结果了，此时不会产生回表。而以下查询：

SELECT name, age, address FROM users WH编程ERE name = 'John';

因为查询的字段 address 不在索引中，数据库需要通过索引定位到数据表中的记录，再去基表检索 address 数据，从而触发回表。

如何避免回表查询

既然我们已经了解了回表查询的存在，那么就需要防微杜渐。通常，为了优化查询性能，减少回表查询，可以尝试以下方法：

1. 创建覆盖索引

尽量创建覆盖索引，使查询所需的字段尽可能包含在索引中。例如，如果经常查询某些字段，可以在它们上创建联合索引：

CREATE INDEX idx_users_name_agmyPUIoQode ON users (name, age);

覆盖索引之所以能够避免回表，是因为只需要在一棵索引树上就能获取SQL所需的所有列数据，就无需回表查询了。常见的方法就是将被查询的字段，建立到联合索引中。

2. 减少查询字段

对于性能敏感的场景，可以减少查询中不必要的字段，只使用关键字段，以便避免索引范围之外的值返回到基表。这个最常见的建议就是尽量少用SELECT * FROM来查询，而是需要什么字段只查对应字段。

3. 分析执行计划

使用数据库的执行计划工具（如MySQL的 EXPLAIN）分析查询性能，确认是否发生了回表查询，可以据此优化索引设计和查询模式。

总结

回表查询是由于索引无法完全覆盖查询字段而发生的数据表回查行为。在优化查询时，可以通过创建覆盖索引或减少查询字段的方式来尽量避免回表查询，从而提高性能。分析执行计划是确定是否发生回表的有效手段。

以上就是MySQL中回表查询避免和优化指南的详细内容，更多关于MySQL回表查询避免和优化的资料请关注编程客栈(www.devze.com)其它相关文章！