索引失效？你可能踩了这些 MySQL 联合索引的坑

加了索引不代表查询就会快，用错索引比没加索引更让人头疼。

“为什么这条 SQL 明明命中了索引，但执行计划里却显示全表扫描？”
“联合索引的顺序到底有什么讲究？”
“为什么把 WHERE 条件换个写法，查询就突然变慢？”

如果你也遇到过这些问题，那这篇文章就是为你准备的。我们聚焦 MySQL InnoDB 引擎，聊聊联合索引那些容易踩的坑。

一、最左前缀原则：联合索引的“命门”

联合索引（复合索引）是将多个列组合在一起建立的索引。假设我们有一个用户订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    status TINYINT NOT NULL,
    create_time DATETIME NOT NULL,
    INDEX idx_user_status_time (user_id, status, create_time)
);

联合索引 idx_user_status_time 内部会按 (user_id, status, create_time) 的顺序进行排序存储。

最左前缀原则：查询条件只有从索引的最左列开始匹配，才能利用上这个索引。

• ✅ 能用到索引：WHERE user_id = 100（匹配最左列）
• ✅ 能用到索引：WHERE user_id = 100 AND status = 1（匹配前两列）
• ✅ 能用到索引：WHERE user_id = 100 AND status = 1 AND create_time > '2024-01-01'（匹配全部列）
• ❌ 用不到索引：WHERE status = 1（跳过最左列）
• ❌ 用不到索引：WHERE create_time > '2024-01-01'（跳过最左列）

如果跳过最左列，MySQL 会选择全表扫描，因为联合索引的排序并不是按照 status 或 create_time 开始的。

容易忽视的细节：范围条件后的列会失效

对于 WHERE user_id = 100 AND status > 1 AND create_time > '2024-01-01'，索引可以用于 user_id 和 status 的范围查询，但是 create_time 的条件无法继续使用索引来减少扫描范围。这是因为 status 已经使用了范围条件，MySQL 在遇到第一个范围条件后，后面的列就不能再用于索引的等值匹配或缩小范围。

二、覆盖索引：最棒的优化手段

如果一个查询只需要访问索引中的列，而无需回表（即回到聚簇索引读取完整行数据），我们称之为覆盖索引。这是提升查询性能的有效方式。

-- 需要回表（假设查询 * 或者非索引列）
SELECT * FROM orders WHERE user_id = 100 AND status = 1;

-- 如果只查询索引包含的列，就可以避免回表
SELECT user_id, status, create_time FROM orders
WHERE user_id = 100 AND status = 1;

在第二个查询中，所有需要的列都在 idx_user_status_time 中，Extra 字段会显示 Using index，性能显著优于 Using index condition（索引条件下推）或回表。

实践建议：在频繁查询的场景下，可以酌情将 SELECT 的列放进联合索引中，但要注意索引大小和维护成本。

三、索引下推（ICP）：MySQL 5.6 之后的“隐藏加速器”

Index Condition Pushdown (ICP) 是 MySQL 5.6 引入的优化特性。当索引无法完全覆盖查询，但可以用索引中的列过滤部分条件时，MySQL 会将这部分条件下推到存储引擎层进行过滤，减少回表的次数。

举个例子：

SELECT * FROM orders WHERE user_id = 100 AND status LIKE '%error%';

因为 status LIKE '%error%' 是模糊查询，且以通配符开头，无法利用索引直接定位。但是有了 ICP 之后，存储引擎在扫描索引时，会先检查 status 列是否匹配 LIKE '%error%'，只对满足条件的记录才回表读取完整行。如果没有 ICP，则需要把所有符合 user_id = 100 的记录全部回表，再由服务器层过滤 status。

可以通过执行计划中的 Extra 列看到 Using index condition，这就表示 ICP 生效了。

四、实战案例：查询慢，如何诊断与优化？

场景

查询某个用户最近的已完成订单，分页展示：

SELECT * FROM orders
WHERE user_id = 123 AND status = 2
ORDER BY create_time DESC
LIMIT 10;

分析

当前索引为 idx_user_status_time (user_id, status, create_time)。乍一看，索引似乎完美匹配查询条件。但执行 EXPLAIN 后发现，Extra 出现了 Using filesort，表示额外的排序操作。

问题根源：联合索引中，create_time 是在 status 之后的，而且 status 是等值查询。那么对于同一个 user_id 和 status，索引中的 create_time 是有序的。按理说能避免 filesort。然而，如果优化器没有选择这个索引，或者因为某些原因导致排序未利用索引，我们需要确认：

• 检查执行计划中 key 是否为 idx_user_status_time。
• 确保 status 确实用到了等值条件（= 2）。
• 如果依然有 Using filesort，可能是类型转换问题（如字段类型不一致）或索引统计信息不准，可尝试 ANALYZE TABLE orders。

更优的索引设计可能是直接在 (user_id, status, create_time) 上建立索引，但如果 status 过滤性不强，可能需要调整顺序为 (user_id, create_time)，然后把 status 作为过滤条件，并通过覆盖索引避免回表。

五、几个让你“意外”的索引失效场景

1. 对索引列进行函数操作或运算

   -- 失效
   SELECT * FROM orders WHERE YEAR(create_time) = 2024;
   -- 建议改为
   SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
   

2. 隐式类型转换

   -- user_id 是 INT，但传入字符串
   SELECT * FROM orders WHERE user_id = '123';
   -- 这种情况通常索引不会失效，但反过来如果字段是字符串，传入数字则会失效：
   -- phone 是 VARCHAR，WHERE phone = 13800138000 会导致全表扫描。
   

3. 负向查询和不等于 NOT IN、!=、<> 在很多情况下会让 MySQL 放弃索引，转而全表扫描，特别是过滤性较低时。

4. 联合索引列顺序不合理 应把区分度最高的列放在最前面，但也要兼顾业务查询的最左前缀。

结语

索引不是创建了就一劳永逸，它是一门平衡的艺术：在查询速度和写入开销之间，在索引个数和空间占用之间。理解最左前缀、覆盖索引、索引下推这些核心概念，能够帮助你写出真正高性能的 SQL，也能在遇到慢查询时快速定位问题。

下一次当你准备创建联合索引时，记得问自己三个问题：

• 这个查询的条件从哪一列开始？
• 我是否能让索引覆盖所有返回的列？
• 有没有不当的写法让索引失效？

处理好这些，你就能避开绝大多数索引的坑。

---

希望这篇关于 MySQL 索引的梳理对你的日常工作有所帮助，欢迎在实践中不断优化与总结。