子查询中复合索引失效的典型表现是EXPLAIN显示type为ALL或index、key为NULL、Extra含Using temporary或Using filesort；根本原因是写法破坏最左前缀匹配或引入不可下推逻辑，如子查询用SELECT *、外层ORDER BY未在索引中、或嵌套中先过滤后筛字段。
子查询里复合索引不生效的典型表现
执行 EXPLAIN 后发现子查询的 type 是 ALL 或 index，key 为 NULL，Extra 出现 Using temporary 或 Using filesort——这说明复合索引没被用上，哪怕你已经在主表建了 idx_user_status_time 这样的索引。
根本原因不是索引不存在，而是子查询的写法破坏了最左前缀匹配，或引入了无法下推的计算逻辑。比如：
-
WHERE status IN (1,2,3) AND user_id = 12345写成WHERE user_id = 12345 AND status IN (1,2,3)才能命中(user_id, status, create_time)的前两列 - 子查询里用了
SELECT *，导致无法走覆盖索引，必须回表取全字段 - 外层
ORDER BY或LIMIT作用在子查询结果上，但子查询本身没带ORDER BY create_time，优化器无法复用索引排序能力
让子查询触发复合索引覆盖扫描的关键写法
核心是：子查询必须满足「条件过滤 + 排序 + 字段投影」三者全部落在同一复合索引的最左前缀范围内。
以订单表 orders 和索引 idx_user_status_time (user_id, status, create_time) 为例：
- ✅ 正确写法（可覆盖）：
SELECT order_id, user_id, status, create_time FROM orders WHERE user_id = 12345 AND status IN (1,2,3) ORDER BY create_time DESC LIMIT 10
- ❌ 错误写法（不覆盖）：
SELECT * FROM orders WHERE user_id = 12345 AND status IN (1,2,3)
——*引入非索引字段，强制回表 - ⚠️ 危险写法：
SELECT order_id FROM (SELECT * FROM orders WHERE user_id = 12345) t WHERE status IN (1,2,3)
—— 子查询先全量扫user_id，再在外层过滤status，索引失效
嵌套子查询中复合索引失效的隐藏陷阱
MySQL 8.0+ 对派生表（derived table）有物化优化，但旧版本或复杂嵌套仍可能绕过索引。尤其注意以下三点：
- 子查询带
GROUP BY或DISTINCT时，即使字段都在索引里，也可能触发Using temporary，因为索引无法直接支持去重逻辑 - 使用
IN (SELECT ...)时，若子查询返回大量值，优化器可能放弃使用索引而改用哈希连接，此时应改用EXISTS或提前用JOIN重写 -
UNION后的子查询各自独立优化，不能共享同一复合索引的排序能力；如需统一排序，必须在外层显式加ORDER BY，且该字段需在所有子查询的索引末尾位置
验证子查询是否真走覆盖索引
别只看 key 是否非空，重点盯住三个信号：
-
type是range或ref，不是ALL或index -
Extra显示Using index（不是Using index condition），说明完全避免回表 -
rows值显著下降（比如从 983164 降到 125），且与实际匹配行数接近
如果 EXPLAIN 显示 Using index condition，代表用了 ICP（Index Condition Pushdown），部分条件在引擎层过滤，但仍有回表可能——这不是真正的覆盖扫描。
