跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
覆盖索引是包含查询所需的每一列的索引。先理解索引工作原理的基础知识会有帮助——MySQL 索引的工作原理介绍了 B 树结构、键长度和选择性规则,这些在这里同样适用。当 MySQL 可以完全从索引中回答查询——而无需访问实际的表行——这被称为仅索引扫描。在大表上,这是可用的最大的单查询优化之一。

回表查找的工作原理(以及为什么开销很大)

常规的索引查找分两步:
  1. MySQL 搜索索引以找出哪些行匹配
  2. 对每个匹配的行,MySQL 从表中获取完整的行(即”回表查找”)
第 2 步是开销大的部分。InnoDB 的聚簇索引意味着表数据按主键物理排序。当您通过二级索引查找行时,主键值是分散的——每次回表查找很可能是对不同磁盘页的随机 I/O。在大规模下,这些随机读取主导了查询时间。 覆盖索引完全消除了第 2 步。

在 EXPLAIN 中识别覆盖索引

EXPLAIN 会告诉您何时使用了覆盖索引:查找 Extra: Using index。如果您还不熟悉 EXPLAIN 的输出,解读 MySQL 中的 EXPLAIN介绍了所有关键列。
Using index——MySQL 只读取了索引。没有回表查找。 现在添加一个不在索引中的列:
Extra 现在为空——MySQL 使用索引找到行,然后获取完整的行以取得 order_total。回表查找又回来了。

设计覆盖索引

目标是包含查询涉及的每一列——WHERE、ORDER BY、GROUP BY 和 SELECT 列表中的列。列的顺序仍然遵循 WHERE 和 ORDER BY 过滤的最左前缀规则:
  • user_id 在最前——等值过滤列
  • created_at 第二——排序列(索引已排好序,无需 filesort)
  • status 最后——只出现在 SELECT 中,不参与过滤或排序
SELECT 列放在最后,因为它们不影响找到哪些行以及按什么顺序找到——它们只是顺带被带上。

覆盖索引何时值得

常见问题

覆盖索引对 SELECT * 有帮助吗?

没有。索引只有在包含 SELECT 列表中的每一列时才能覆盖查询。SELECT * 选择所有列,所以您需要索引所有列——到那时您实际上是把整张表复制成了一个索引,这没有用处。

覆盖索引仍然能加速 SELECT * 查询吗?

可以,部分地。即使 MySQL 必须做回表查找,索引也缩小了需要获取的行的范围。改进来自 WHERE 过滤,而不是来自消除回表查找。

我如何知道覆盖索引是否有帮助?

运行 EXPLAIN 并检查 Extra。如果您看到 Using index condition 或什么都没有(没有 Using index),并且 rows 很大,那么覆盖索引可能有帮助。EXPLAIN ANALYZE 显示实际行数与估计行数的对比,这对确认优化器的选择很有用。

故障排除

另请参阅