mysql如何根据执行计划进行索引选择与调整

EXPLaiN中key与possible_keys不一致是因为优化器基于成本选择最优索引，而非简单“能用就用”；key只显示最终选定索引，可能因行数预估更少而选时间范围索引而非状态索引。

怎么看 EXPLAIN 输出里的 key 和 possible_keys 不一致？

mysql 选索引不是“哪个能用就用哪个”，而是基于成本估算。即使 possible_keys 列出多个索引，key 只显示最终选中的那个——它可能不是你预期的最“宽”或“最左”的索引。

常见误导：看到 possible_keys 包含 idx_user_status 和 idx_user_created_at，但 key 是后者，就以为“没走状态索引”。其实是因为查询中 created_at > '2024-01-01' 返回行数远少于 status = 'active'（比如后者占全表 60%），优化器判定用时间范围索引更便宜。

• 用 EXPLAIN format=jsON 查看 used_range_access 和 rows 预估，比只看 key 更可靠
• 如果 possible_keys 为空但实际有索引，检查字段是否用了函数（如 WHERE YEAR(created_at) = 2024）或隐式类型转换（如字符串列和数字比较）
• key_len 值比索引定义长度小，说明只用了前缀（比如 VARCHAR(255) 字段建了前缀索引 INDEX(name(50))，key_len 就是 50 × 字符编码字节数）

复合索引的最左匹配到底卡在哪儿？

“最左前缀原则”不是语法限制，而是 B+ 树检索路径决定的：必须从索引第一列开始连续提供等值条件，才能高效定位数据页。一旦中间出现范围查询（>、BETWEEN、LIKE 'abc%'），后续列就无法用于索引查找，只能用于过滤（Extra: using index condition）。

SELECT * FROM orders  WHERE user_id = 123    AND status IN ('paid', 'shipped')    AND created_at > '2024-05-01';

对这个查询，INDEX(user_id, status, created_at) 比 INDEX(user_id, created_at, status) 更优——因为 status IN 算等值匹配（MySQL 8.0+ 支持多值等值查找），而 created_at > 是范围，放最后才不打断前缀匹配。

• 排序需求也要纳入索引设计：ORDER BY created_at DESC 跟在等值条件后，可被同一复合索引覆盖，避免 Using filesort
• select * 时，覆盖索引失效，但若只查索引列（如 SELECT user_id, status），INDEX(user_id, status, created_at) 就能完全避免回表
• 不要为每个 WHERE 字段单独建索引，优先合并：三个单列索引不如一个设计合理的三列复合索引（除非查询模式差异极大）

type 是 range 还是 ref，为什么影响这么大？

type 直接反映扫描方式：ref 表示通过索引等值查找定位到具体数据页（快），range 表示按索引顺序扫描一段区间（慢，尤其区间大时）。两者性能差距常达数量级。

典型陷阱：明明写了 WHERE category_id = 5 AND price BETWEEN 100 AND 500，EXPLAIN 却显示 type: range。原因往往是索引顺序错了——如果建的是 INDEX(price, category_id)，优化器只能先按 price 范围扫描，再在结果里过滤 category_id，变成 range；改成 INDEX(category_id, price) 后，category_id = 5 定位到子树，再在该子树内做 price 范围扫描，type 就变成 ref。

• type: ALL 或 index 是全表/全索引扫描，必须干预；type: const 或 eq_ref 是理想状态（主键/唯一索引等值查找）
• Extra: Using index 表示覆盖索引，Using where 表示存储引擎返回数据后 Server 层还要过滤——后者意味着索引没包含所有 WHERE 条件列
• 当 rows 预估值远大于实际返回行数，可能是统计信息过期，执行 ANALYZE table table_name 更新

什么时候该删索引？

索引不是越多越好。写多读少的表，每个额外索引都会拖慢 INSERT/UPDATE/delete，还占用内存和磁盘。重点删这三类：

• 重复索引：INDEX(a,b) 和 INDEX(a) 同时存在，后者无意义
• 低效索引：SHOW INDEX FROM table_name 查 Cardinality，如果某列唯一值极少（如 is_deleted TINYINT 只有 0/1），且从不单独用于查询，这个单列索引基本无效
• 长期未用索引：开启 performance_schema，查 sys.schema_unused_indexes 视图（MySQL 5.7+），但注意它只统计 Server 层执行计划记录，不捕获直接命中缓存的查询

调整索引本质是平衡读写开销。一个被高频查询用到的复合索引，即使让单条 UPDATE 慢 5ms，也通常值得；但一个三个月没被 EXPLAIN 选中的索引，留着只是浪费。