索引下推 vs 覆盖索引：区别与最佳实践
索引下推 (Index Condition Pushdown, ICP) 和覆盖索引 (Covering Index) 都是 MySQL（尤其是 InnoDB 存储引擎）中用于优化查询性能的重要技术，但它们的作用机制和适用场景有本质区别。
一、 索引下推 (Index Condition Pushdown, ICP)
什么是索引下推？
索引下推是 MySQL 5.6 版本引入的一项优化。
-
• 作用机制： 在遍历索引时，将索引中包含的查询条件（非 WHERE子句中涉及到的所有列）推送到 存储引擎层 进行判断和过滤，而不是在 Server 层 进行。 -
• 优化效果： 减少了从存储引擎层返回给 Server 层的记录数，从而减少了 回表（根据主键查询完整行数据）的次数，或在无法避免回表时，减少了无效的回表操作。
适用场景
-
1. 复合索引（联合索引）。 -
2. WHERE条件中，只有部分列 是索引列，且条件中还有其他 非索引列 的等值或范围判断。 -
3. 查询的列 不全部 在索引中（即不是覆盖索引）。
举例说明
假设有一个联合索引 (name, age, country)，查询语句是：
SELECT * FROM user WHERE name = 'Tom' AND age > 20 AND country = 'China';
-
• 没有 ICP： 存储引擎只能根据 name = 'Tom'扫描索引，并将所有name = 'Tom'的完整记录返回给 Server 层，Server 层再判断age > 20和country = 'China'。 -
• 使用 ICP： 存储引擎在扫描索引时，会同时检查 name = 'Tom'和age > 20和country = 'China'（如果索引中包含这些列）。只有满足所有条件的索引条目才会被视为候选，从而减少了需要回表的次数。
二、 覆盖索引 (Covering Index)
什么是覆盖索引？
-
• 作用机制： 查询所需的所有列，都在索引中（无论是主键索引还是二级索引）。 -
• 优化效果： 避免了 回表 操作。MySQL 直接从二级索引中获取所有需要的数据，不需要再去主键索引中查找完整的行记录。 -
• 查询成本： 极大降低了 I/O 操作次数，是最高效的查询优化之一。在 EXPLAIN结果中，Extra列会显示Using index。
适用场景
-
1. 查询的 SELECT列表中的所有列（包括WHERE,ORDER BY,GROUP BY等子句中涉及的列），都包含在一个索引中。 -
2. 任何可以被设计为只返回索引列的查询。
举例说明
假设有一个联合索引 (name, age)，查询语句是：
SELECT name, age FROM user WHERE name = 'Tom';
-
• 效果： 数据库只需要扫描 (name, age)这个二级索引即可获取name和age的值，无需回表。
⚖️ 核心区别总结 (本质差异)
最佳实践
1. 优先使用覆盖索引
最佳实践是：能使用覆盖索引的地方，尽量使用覆盖索引。
-
• 原则： 尽量只查询需要的列，而不是使用 SELECT *。 -
• 设计： 当一个查询频繁且对性能要求高时，可以考虑为它创建一个包含所有查询所需字段的复合索引，以实现覆盖索引。
2. 在无法覆盖时考虑索引下推
如果无法实现覆盖索引（例如，需要返回的列太多，不适合建立包含所有列的索引），索引下推就成了次佳的优化手段。
-
• 原则： 利用 ICP 可以在复合索引上尽可能地过滤数据，减少回表次数。 -
• 注意： ICP 默认是开启的，但它只作用于二级索引。如果 WHERE条件中涉及的列能放在一个复合索引中，即使查询需要回表，ICP 也能帮助减少无效的回表操作。
3. 避免冲突和误解
-
• Using index和Using index condition不会同时出现。 -
• 如果 EXPLAIN出现Using index，说明是覆盖索引，查询效率最高，不需要 ICP。 -
• 如果 EXPLAIN出现Using index condition，说明使用了 ICP，但 仍需回表（所以没有Using index效率高）。
总结来说：覆盖索引是消灭回表，而索引下推是优化回表。