mysql 非主键索引叶子结点存的是主键id,那如果存主键id的地址，是不是就不用回表了？

在 MySQL 的 InnoDB 存储引擎中，非主键索引的叶子节点存储的是**主键ID**，而不是数据行的物理地址。这种设计是为了提高系统的灵活性和一致性。以下是为什么 InnoDB 选择存储主键ID，而不是物理地址，以及是否可以通过存储地址来避免回表操作的分析。 ### 1. 为什么非主键索引存主键ID而不是物理地址？ InnoDB 选择存储主键ID而不是物理地址，主要是为了应对**数据行位置变化**带来的问题。数据行的位置可能因为以下原因发生变化： - **行更新导致数据变大**：当数据行更新后需要更多空间，InnoDB 可能会将该行移动到其他页中。 - **页分裂**：当数据页满时，InnoDB 会分裂页并将部分行移到新的页中。 - **页合并**：当删除行后页面存在过多空闲空间，InnoDB 可能会将多个部分空的页合并，导致数据行移动。 - **表优化或分区调整**：如 `OPTIMIZE TABLE` 操作或表分区的调整，可能导致数据行的位置变化。 如果存储的是物理地址，一旦数据行的位置因上述原因发生变化，索引中的地址就会失效，必须更新大量索引，这会导致索引维护成本极高，尤其是在频繁更新或插入数据的表中。而通过存储主键ID，InnoDB 无论数据行的位置如何变化，都可以通过聚簇索引（主键索引）来快速定位到数据行，无需频繁修改索引结构。 ### 2. 如果存储主键ID的地址，是否可以避免回表？ 理论上，如果非主键索引直接存储数据行的物理地址，那么在查询时可以直接通过物理地址定位到完整的数据行，避免通过主键索引回表查询。但这种设计会带来以下问题： - **数据行位置变化问题**：当数据行位置发生变化时（如行更新、页分裂、页合并等），物理地址会失效，导致需要更新所有与之相关的索引。这大大增加了索引维护的复杂性和系统开销。 - **存储和查询的灵活性下降**：存储物理地址使得数据行的移动更为困难，数据的存储结构变得不灵活。 因此，InnoDB 选择存储主键ID，尽管这可能导致在某些查询中需要回表，但通过聚簇索引定位数据行的代价相对较低，而且避免了频繁的索引更新。 ### 3. 如何避免回表？ 虽然非主键索引存储主键ID在某些查询中需要回表，但我们可以通过设计**覆盖索引**来避免回表： - **覆盖索引**：如果非主键索引包含了查询所需的所有字段，MySQL 可以直接从索引中获取数据，而无需回表。例如： ```sql CREATE INDEX idx_name ON table_name (column1, column2); ``` 如果查询只涉及 `column1` 和 `column2`： ```sql SELECT column1, column2 FROM table_name WHERE column1 = 'value'; ``` InnoDB 可以直接通过 `idx_name` 获取所需数据，不需要通过主键索引回表查询。 ### 结论： 如果非主键索引存储的是物理地址，而不是主键ID，理论上可以避免回表，因为可以直接通过地址找到数据行。然而，InnoDB 存储主键ID的设计是为了应对数据行位置变化的情况，避免频繁更新索引，确保系统的灵活性和一致性。因此，InnoDB 选择了存储主键ID作为一种平衡方案。在实践中，通过设计覆盖索引可以避免不必要的回表操作。