什么是回表？

回表（Table Lookup 或 Back to Table）

回表是数据库查询优化中的一个概念，指的是在使用非聚集索引（Secondary Index 或 Non-Clustered Index）进行查询时，数据库引擎需要通过非聚集索引找到数据行的位置（通常是主键或行标识符），然后再根据这些位置从聚集索引或数据文件中读取完整的数据行的过程。

过程解释

1. 非聚集索引查询：

   • 当一个查询使用了非聚集索引时，首先通过非聚集索引找到符合条件的记录。非聚集索引的叶子节点包含的是索引键值和数据行的位置标识符（例如，主键值或行指针）。

2. 回表操作：

   • 找到索引键值对应的行标识符后，数据库引擎再根据这些标识符回到表中，查找完整的数据行，以获取查询所需的其他列的数据。

示例

假设有一个名为 employees 的表，包含以下字段：employee_id（主键），name 和 department。表结构和数据如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

INSERT INTO employees (employee_id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'Engineering'),
(3, 'Charlie', 'Sales');

创建非聚集索引

假设我们在 name 字段上创建了一个非聚集索引：

CREATE INDEX idx_name ON employees(name);

查询示例

执行以下查询：

SELECT department FROM employees WHERE name = 'Alice';

查询过程

1. 通过非聚集索引查找：

   • MySQL 使用 idx_name 非聚集索引来查找 name 为 ‘Alice’ 的记录。在 idx_name 索引中，找到索引键值 ‘Alice’，对应的 employee_id 为 1。

2. 回表操作：

   • 根据找到的 employee_id，MySQL 回到 employees 表中，查找 employee_id 为 1 的完整数据行，以获取 department 列的值 ‘HR’。

为什么需要回表？

回表操作是因为非聚集索引中不包含所有的数据列，仅包含索引键值和数据行的位置标识符。如果查询需要的列不在非聚集索引中，数据库必须回到表中读取完整的数据行。

覆盖索引

为了避免回表操作，可以使用覆盖索引。覆盖索引是指一个非聚集索引包含了查询所需的所有列，从而使查询不需要回表即可获取所有数据。

例如，为了避免上述查询中的回表操作，可以创建包含 name 和 department 列的覆盖索引：

CREATE INDEX idx_name_department ON employees(name, department);

现在执行以下查询时：

SELECT department FROM employees WHERE name = 'Alice';

查询过程如下：

1. 通过非聚集索引查找并覆盖：
   • MySQL 使用 idx_name_department 非聚集索引直接获取 name 为 ‘Alice’ 的记录，并读取 department 列的值 ‘HR’。
   • 因为 idx_name_department 索引已经包含了 department 列，无需回表。

总结

• 回表：在使用非聚集索引查询时，通过索引查找到行标识符后，再根据标识符回到表中读取完整数据行的过程。
• 覆盖索引：通过创建包含查询所需所有列的非聚集索引，避免回表操作，提高查询性能。

理解回表和覆盖索引的概念，可以帮助数据库设计人员优化查询性能，减少不必要的 I/O 操作。