引用自 chatgpt

回表概览

在MySQL中，回表（也称为回查表）是指在使用非聚簇索引进行查询时，MySQL需要根据索引中的信息再次访问表中的实际数据记录。这通常发生在查询的结果需要返回不在索引覆盖范围内的字段时。

聚簇索引与非聚簇索引回顾

• 聚簇索引（Clustered Index）：数据表的记录按照聚簇索引的键值进行物理排序。聚簇索引的叶节点包含了实际的数据行。
• 非聚簇索引（Non-Clustered Index）：数据表的记录并不按照非聚簇索引的键值进行物理排序。非聚簇索引的叶节点包含了索引键值和一个指向实际数据记录的指针（行定位符）。
• 二者底层一般都使用B+Tree数据结构

回表的过程

在使用非聚簇索引进行查询时，如果查询的字段不完全包含在索引中，MySQL需要先通过非聚簇索引找到匹配的索引键，然后根据索引中的行定位符（通常是主键或者内部行指针）去表中读取实际数据行，这个过程就称为回表。

回表示例

考虑一个包含以下字段的表 Employees：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(255),
    FirstName VARCHAR(255),
    BirthDate DATE,
    HireDate DATE,
    Department VARCHAR(255)
);

并在 LastName 字段上创建一个非聚簇索引：

CREATE INDEX IDX_LastName ON Employees (LastName);

假设我们有以下查询：

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName = 'Smith';

在这个查询中：

1. 使用非聚簇索引查找： MySQL首先使用 IDX_LastName 索引来查找 LastName 为 'Smith' 的所有记录。索引中存储了 LastName 字段和指向实际数据行的指针。

2. 回表： 由于查询需要返回 EmployeeID 和 FirstName 字段，而这些字段不在 IDX_LastName 索引覆盖的范围内，MySQL必须根据索引找到的指针回到表中读取这些字段的实际数据。

避免回表

为了避免回表，可以通过覆盖索引（Covering Index）来实现。覆盖索引是指一个索引包含了查询所需的所有字段，这样查询时可以完全通过索引获取数据，而不需要回表。

例如，如果我们经常查询 LastName、FirstName 和 EmployeeID，可以创建一个包含这些字段的复合索引：

CREATE INDEX IDX_LastName_FirstName_EmployeeID
ON Employees (LastName, FirstName, EmployeeID);

现在，当执行以下查询时：

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName = 'Smith';

MySQL可以完全通过 IDX_LastName_FirstName_EmployeeID 索引获取所有需要的数据，而不需要回表。

优缺点

回表的优点：

• 减少了索引的大小，因为索引中只包含键值和指针，而不是所有的数据字段。
• 可以更灵活地设计索引，而不必考虑所有查询的覆盖情况。

回表的缺点：

• 增加了查询的I/O操作次数，因为需要先访问索引再访问实际数据表。
• 在大数据量和高并发情况下，回表操作可能会显著降低查询性能。

总结

回表是指在使用非聚簇索引进行查询时，MySQL需要根据索引找到的行定位符回到表中读取实际数据行的过程。虽然回表可以使索引更加灵活和紧凑，但它也增加了查询的I/O操作次数，可能会影响性能。通过设计覆盖索引，可以在一定程度上避免回表，从而提高查询效率。在实际应用中，索引设计需要综合考虑查询需求和性能要求，以达到最佳的优化效果。 [chatgpt]: http://qhou.site/ "引用自 chatgpt"