为什么命中索引，查询还是慢？

-- 示例1：不好：需要回表，随机IO
CREATE INDEX idx_name ON users(last_name);
SELECT * FROM users WHERE last_name = 'Bill'

-- 示例2：好，覆盖索引，避免回表
CREATE INDEX idx_name ON users(last_name, first_name, email);
SELECT last_name,first_name,email FROM users WHERE last_name = 'Bill'

数据库索引的基本原理

以 InnoDB 为例，PostgreSQL 原理类似但细节有差异

InnoDB 采用聚簇索引设计：表数据按主键的顺序物理存储，索引的叶子节点仅存储主键值+索引列。

示例 1 查询分析

第一个例子中创建的是普通索引（仅对 last_name建索引），执行SELECT *查询时：

• 步骤 1：命中普通索引

先在 idx_name 索引中，通过 last_name='Bill'快速定位到满足条件的索引项。但普通索引的叶子节点只存 last_name值和主键，不存其他列（如 first_name、email等）。

• 步骤 2：回表查全量数据

SELECT *要求返回所有列，因此必须根据主键id去聚簇索引（表数据的物理存储）中查找完整行数据（即“回表”）。

随机 I/O 的根源

聚簇索引的叶子节点是按主键顺序存储的，但主键的物理位置可能分散（比如插入时主键是自增的，但如果数据有删除、更新，或主键非连续，物理页在磁盘上可能不连续）。

回表时，每次根据主键找对应行都要做一次 I/O，且这些 I/O 的物理位置无规律（随机），因此称为「随机 I/O」。随机 I/O 效率极低，因为磁盘寻址耗时远高于顺序读。

示例 2 查询分析

第二个例子中，创建的是覆盖索引idx_name_covering（索引包含 last_name, first_name, email）因为索引本身包含了查询要的所有数据，所以不需要再去聚簇索引中查完整行（跳过了“回表”步骤）。

顺序 I/O 读取索引

索引的叶子节点是按 B+ 树的顺序结构存储的（叶子节点层形成一个有序链表），因此扫描索引时是顺序 I/O——磁盘寻址是连续的，效率远高于随机 I/O。

总结

• 普通索引查询若要返回全量列，必须回表查聚簇索引，而聚簇索引的物理存储可能分散 → 随机 I/O。
• 覆盖索引直接在索引中包含查询所需全量列，无需回表，且索引本身的顺序结构让 I/O 更高效 → 顺序 I/O。

这也解释了“为什么明明命中了索引，还是慢”——如果触发回表，额外的随机 I/O 会成为性能瓶颈。而覆盖索引通过“索引包含所有查询列”规避了回表，是优化查询的关键手段之一。