MySQL联合索引失效怎么解决？从key_len逆向分析

大家好我是数据库小学妹 上周帮同事查一个慢查询300万行的订单表查询耗时6秒。SELECTorder_id,user_id,amount,statusFROMordersWHEREuser_id1024ANDstatuspaidORDERBYcreate_timeDESCLIMIT50;表上有一个联合索引ALTERTABLEordersADDINDEXidx_user_status_time(user_id,status,create_time);三个字段都在索引里查询条件用了user_id和status排序用了create_time。EXPLAIN 的结果让我愣了一下。type 是 refkey 是 idx_user_status_time看起来走了索引。但 key_len 只有 5 字节。这个联合索引有三个字段user_id 是 bigint 占8字节status 是 varchar 加排序规则至少占几十字节key_len 不可能只有5。顺着 key_len 查下去发现联合索引的匹配过程比最左前缀四个字要复杂得多。key_len 是什么很多人看 EXPLAIN 只看 type 和 rows忽略了 key_len。看 key_len 能直接判断联合索引匹配到了哪一列。key_len 表示优化器实际使用的索引字节数不是索引的总长度而是 WHERE 条件中能匹配到的索引部分的长度。还是 idx_user_status_time(user_id, status, create_time) 这个索引。先算每个字段在索引中占多少字节。user_id 是 int4字节允许 NULL 加1字节标记。status 是 varchar(20)utf8mb4 编码下每个字符最多4字节20×42varchar长度标记82字节允许NULL加1字节。create_time 是 datetimeMySQL 8.0占5字节允许NULL加1字节。不同查询条件下的 key_len 预期值查询条件实际匹配列预期 key_len说明WHERE user_id 1024仅 user_id5只用了第一列WHERE user_id 1024 AND status ‘paid’user_id status88前两列都匹配WHERE user_id 1024 AND status ‘paid’ AND create_time ‘2026-07-01’三列94第三列做范围扫描回到同事那个查询WHERE 里有 user_id 和 status 两个条件理论上 key_len 应该是 88。实际只有 5。说明联合索引只用了第一列 user_idstatus 完全没被匹配上。为什么 status ‘paid’ 这个等值条件用不上索引第二列问题出在字符集。这张表默认字符集是 utf8mb4但 status 字段建表时被单独指定成了 utf8。查询条件传入的字符串走的是连接字符集 utf8mb4和索引定义的 utf8 不一致。MySQL 遇到字符集不匹配时会做隐式转换把索引列的值转成查询条件的字符集再比较。这个转换让第二列的 B 树排序失效优化器只能停在第一列。修好字符集后key_len 从5变成了88。查询从6秒降到0.08秒。看 key_len 就能知道联合索引实际用到了哪一列而不是定义了几列。最左前缀从左开始用遇到范围就断MySQL教程都会讲最左前缀原则。大多数人都这么记。用起来基本够用但有些边界情况会出问题。最左前缀的本质是联合索引的B树按照索引列的组合值排序。先按第一列排第一列相同的按第二列排第二列相同的按第三列排。(user_id, status, create_time) 在B树中的排序 (1, paid, 2026-07-01) (1, paid, 2026-07-02) (1, unpaid, 2026-06-15) (2, paid, 2026-07-03) (2, shipped, 2026-07-01)这个排序决定了查询能走多远。三列等值匹配索引完美利用WHEREuser_id1024ANDstatuspaidANDcreate_time2026-07-15-- key_len 用到三列前两列等值第三列范围扫描索引完全利用WHEREuser_id1024ANDstatuspaidANDcreate_time2026-07-01-- key_len 用到三列第三列做范围扫描第一列等值第二列范围第三列等值。第三列无法利用索引排序只能在内存中过滤WHEREuser_id1024ANDcreate_time2026-07-01ANDstatuspaid-- 第二列范围查询第三列失效key_len 只用到前两列范围查询之后的列无法被B树的排序结构利用。上面第三个例子就是create_time 在 status 范围查询之后索引排序用不上只能在 server 层做 filesort。我搞错过一次。WHERE 条件的顺序是 status, user_id, create_time和索引定义顺序不同。我以为 MySQL 会自动调整顺序匹配结果 EXPLAIN 显示只用了第一列。MySQL 8.0 的优化器会自动调整 WHERE 条件顺序来匹配索引但前提是优化器知道该用哪个索引。如果 WHERE 条件中间跳过了某一列优化器可能直接放弃这个索引。索引下推ICP不是失效是帮你省回表有时候 EXPLAIN 的 Extra 列显示 “Using index condition”但 key_len 只覆盖了部分列。这是索引下推Index Condition Pushdown, ICP。WHERE 条件中有部分列无法利用索引匹配时MySQL 不会立刻回表而是在存储引擎层用索引中已有的数据做进一步过滤减少回表次数。-- 联合索引 idx_user_status(user_id, status)SELECT*FROMordersWHEREuser_id1024ANDstatusLIKEp%;user_id 等值匹配status LIKE 前缀匹配。‘p%’ 是前缀匹配B树可以快速定位到以 ‘p’ 开头的 status 值。EXPLAIN 显示 Using index condition意味着先用 user_id 等值定位在这个范围内用 status LIKE ‘p%’ 在存储引擎层过滤只有过滤通过的行才回表取完整数据。没有 ICP 的话所有 user_id 1024 的行都要回表然后在 server 层过滤 status。我一开始看到 Using index condition 以为是坏信号查了文档才知道是 MySQL 在帮我减少回表。ICP 是 MySQL 5.6 引入的默认开启。覆盖索引不用回表当查询需要的所有列都在索引里时MySQL 不需要回表查数据页直接从索引返回结果。EXPLAIN 的 Extra 列显示 “Using index”。-- 联合索引 idx_user_status_time(user_id, status, create_time)SELECTuser_id,status,create_timeFROMordersWHEREuser_id1024ANDstatuspaid;只需要三个字段恰好都在联合索引里。MySQL 不需要回表直接在索引的B树上就能拿到所有数据。每次回表就是一次随机I/O。数据不在 buffer pool里时每次随机读可能要几毫秒。覆盖索引完全避免了回表因为索引树比数据页小得多更可能全在buffer pool里。我曾经优化过一个查询把 SELECT * 改成只查需要的3个字段EXPLAIN 从 Using where 变成了 Using index。当时觉得没什么大不了后来发现那个查询每天跑上千次改完 CPU 降了15%。SELECT *是覆盖索引的天敌。只要SELECT *就一定需要回表。代码审查时看到SELECT *第一反应就是这个查询能不能改成只查需要的列。联合索引列顺序选错扫描范围差很多联合索引最容易被忽视的是列顺序。同样三个字段排列顺序不同扫描范围可以差几十倍。一张100万行的用户行为表需要支持以下查询-- 查询1高频按用户查最近行为WHEREuser_id?ORDERBYaction_timeDESC-- 查询2中频按用户和动作类型查WHEREuser_id?ANDaction_type?-- 查询3低频按动作类型查WHEREaction_type?方案A(user_id, action_type, action_time)方案B(user_id, action_time, action_type)方案C(action_type, user_id, action_time)方案A对查询1和查询2都高效。user_id 等值后查询1用 action_time 排序无需 filesort查询2用 action_type 等值过滤。查询3无法走索引。方案B对查询1高效。user_id 等值后 action_time 可直接用于排序。查询2也能走索引action_type 用 ICP 过滤。查询3同样无法走索引。方案C对查询3高效。action_type 作为第一列可直接走索引。但对查询1和查询2效率较低action_type 范围扫描后再定位 user_id扫描范围更大。选哪个看查询频率。查询1占80%以上方案B最优。查询3频率不低的话可能需要两个联合索引。我踩过的坑是按区分度最高的列放前面建索引。user_id 区分度最高100万个不同值action_type 只有10个。我选了方案C。结果查询1和查询2全慢了它们的频率远高于查询3我为了优化低频查询牺牲了高频查询。联合索引的列顺序应该按查询频率排不是按区分度。区分度原则只在查询频率相近时有参考价值。避坑清单定期用 key_len 验证索引使用情况。别建完索引就不管了。挑慢查询日志里的SQL跑 EXPLAIN看 key_len 是否符合预期。key_len 远小于索引总长度说明有列没被利用可能是字符集不一致、隐式类型转换、或者范围查询打断了后续列。有次我对一个 int 字段传了字符串参数EXPLAIN 显示 key_len 只有第一列的长度。排查了半小时才发现是隐式类型转换惹的祸后面的列全部失效。从那以后ORM 里传参我都会检查类型不依赖框架自动转换。列顺序按查询频率排不是按区分度。网上教程说区分度高的列放前面只对了一半。区分度影响选择性查询频率影响实际命中率。区分度低但命中率高的列放前面往往收益更大。判断查询频率可以看慢查询日志里各SQL的出现频次或者用 performance_schema 的统计表。一个表的联合索引不要超过5个。每个索引都增加 INSERT/UPDATE/DELETE 的代价。一张表8个联合索引写入性能降了60%我亲眼见的。联合索引建起来简单三列加个索引就行。用起来要注意的地方不少。列顺序、字符集、隐式转换、覆盖索引、ICP处理不好任何一个查询都会慢出几个数量级。我现在的习惯是拿到一个查询先画出来它需要走索引的列和排序的列再决定联合索引的列顺序。建完跑一遍 EXPLAIN盯着 key_len 看是否用到了预期的列。最后用 EXPLAIN ANALYZEMySQL 8.0.18看实际执行时间和预估是否一致。你的项目里有没有建了索引却没生效的情况怎么查出来的评论区说说看。我是数据库小学妹咱们下篇见