1. 先从一次慢查询说起联合索引到底解决了什么问题如果你手里有一个百万级数据量的订单表每天被各种查询条件按不同的列组合筛选某天收到一个报警某个接口的SQL执行时间从几十毫秒涨到了三秒钟。你会怎么排查大概率会先看这条SQL的WHERE条件发现它同时用了user_id和order_status两个字段于是你在两个字段上分别建了索引。结果发现走了索引还是慢甚至有时候根本没走索引直接全表扫描。这就是联合索引要解决的核心问题当查询条件涉及多个列时如何让 MySQL 用一次索引查找就能把数据定位到足够小的范围而不是在多个单列索引之间来回折腾。所谓联合索引也叫复合索引就是把多个列合成一个索引结构。比如在(user_id, order_status)上建联合索引从逻辑上说这个索引先按user_id排序在user_id相同时再按order_status排序。它本质上还是一棵 B 树只是树里的键变成了“多个列拼接起来的一个复合键”。我在刚接触索引的时候有个误解觉得多个条件就用多个单列索引反正数据库不是会自动挑吗实测下来完全不是那么回事。MySQL 优化器在多个单列索引之间做选择时通常会选区分度最高的那一个然后用回表的方式去过滤其余条件这个过程的代价很多时候比全表扫描还大。联合索引之所以是优化多条件查询的主流方案是因为它从根本上改变了索引的遍历路径一次从左到右的索引查找直接命中满足全部条件的那一批叶子节点不需要多个索引之间进行交集计算也不需要大量回表。这篇文章我默认你是知道单列索引 B 树结构的我会把重点放在联合索引的匹配规则、失效场景、排序优化和真实排查方法上。零基础的同学建议先去补一下“索引是什么”和“回表是什么”再回来看这篇会顺很多。2. 最左前缀原则用得好是利器用不好是陷阱2.1 最左前缀到底是什么联合索引的排序规则决定了它的使用方式——这是整篇文章最核心的一条规则。假设我们有一个索引idx_user_status(user_id, order_status)它的物理存储逻辑是这样的所有数据先按user_id从小到大排列在user_id相同的记录里再按order_status排列。你可以把它理解成一本先按姓名拼音、再按电话号码排序的通讯录。那么这本“通讯录”能高效支持哪些查找按user_id查能用。因为整个索引的第一排序键就是它。按user_id和order_status一起查能用。先定位user_id再在内部定位order_status。只按order_status查不能用。因为order_status只在前一个字段相同的情况下才有序全局它是乱序的。这就是最左前缀原则联合索引的生效条件是查询条件从索引的最左列开始连续匹配若干列。缺少最左列索引直接失效中间跳过了某一列那么该列之后的条件也没法走索引。我刚开始学的时候总是记混后来找到一个辅助记忆的方式把联合索引想象成“多级抽屉”。第一个字段是第一级抽屉的编号第二个字段是第二级抽屉的编号。你要找某样东西必须先知道一级抽屉在哪才能继续往下一级找。跳过一级直接告诉别人“我在第二个抽屉”没人知道你在说什么。2.2 匹配列顺序安排等值优先排序靠前范围放最后知道了最左前缀原则下一个问题就是我到底该把哪个列放在联合索引的最左边我在多次调优后总结出一个好用的顺序判断方法按优先级从高到低排列经常作为等值条件的列放最左边。比如WHERE user_id ?这类条件能精确定位索引中的一段连续区间走索引的收益最大。需要排序的列紧随其后。如果ORDER BY order_time把order_time放在索引中靠前的位置可以利用索引的有序性避免文件排序。范围条件列放最后。比如、、BETWEEN、LIKE abc%。因为范围条件一旦被使用它后面的索引列就无法用于精确定位了。举个例子假设业务上有三种高频查询SELECT * FROM orders WHERE user_id 100 AND status PAID; SELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC; SELECT * FROM orders WHERE status PAID AND create_time 2024-01-01;这种情况下我建议建idx_user_status_time(user_id, create_time, status)。为什么这么排user_id是三种查询都用到的最左列而且是等值条件放最左没商量。create_time放在第二位。第二种查询的ORDER BY create_time能直接利用索引顺序避免filesort同时它也是第三种查询的范围条件列放中间不影响前面的user_id等值匹配。status放在最后。第一种查询里它在user_id等值的基础上做条件过滤第三位完全够用。实际上这个索引是靠user_id这个等值条件“定住”所有可能的分支让create_time和status都能在这棵子树里有序排列从而分别服务于排序和过滤。简单说最左列选区分度高、经常等值查询的列排序列紧跟其后范围列最后收尾。2.3 等值与范围的边界处理细节还有一个细节容易被忽略不等值查询也会中断匹配。比如WHERE user_id 100 AND status PAID这条 SQL 能用到联合索引里的user_id部分但status就走不了索引了。原因是 B 树的叶子节点里status是在user_id等值的前提下才有序的。现在user_id 100是一个范围这个范围内的status值分布仍然是无序的MySQL 无法通过索引结构快速定位到某个具体的status。更严格地说和甚至会影响下一列能否继续做等值匹配。我遇到过不少开发同学以为还能继续匹配后面的索引列实际上的右边界在极端情况下可能让优化器仍然遵循最左前缀规则的“范围截断”逻辑。为了保险起见我把规则总结成一句好记的话、IN算是“友好”条件可以继续往右匹配。、、BETWEEN、LIKE一旦出现它的右边就默认“到此为止”。3. 联合索引的常见失效场景这些坑我几乎全踩过3.1 LIKE 模糊查询为什么会“半失效”LIKE abc%这种写法如果abc列在联合索引的最左位置前缀是固定的MySQL 确实能走索引。但LIKE %abc%或者LIKE %abc这种前后通配的写法索引就完全失效了因为字符串的排序规则决定不了“任意位置包含 abc”的数据分布。我踩过最窝火的一个坑是明明联合索引里有这个列而且它也满足最左前缀结果执行计划显示还是全表扫描。后来查清楚了是我在代码里拼 SQL 时把参数直接拼成了LIKE % || keyword || %。这种写法执行计划基本不可能走索引因为在联合索引的匹配路径上%开头的条件无法定位到 B 树的一个起始点。我把这个经验总结为能用前缀匹配的就不要用包含匹配。如果业务真的需要中间模糊查询要么改用覆盖索引硬扫一遍要么上专门的全文检索方案别指望联合索引能救你。3.2 隐式类型转换和函数操作把索引列“弄脏”了WHERE user_id 100如果user_id列本身是BIGINT类型这个查询是可以走索引的MySQL 会把字符串100隐式转成数字。但反过来就不行如果列是VARCHAR条件是WHERE phone 13800138000MySQL 会把列值从字符串转成数字再去比较相当于对索引列做了隐式函数操作索引就失效了。我自己排查过一个线上问题一张用户表手机号是VARCHAR类型查询走了 5 秒多。执行计划显示typeALL全表扫。一开始百思不得其解最后把 SQL 改成WHERE phone 138001380000.02 秒就回来了。教训就是写 SQL 时条件值的类型必须和列的类型对齐。能避免隐式转换就避免不要指望优化器每次都能处理好。另外函数操作也是一样。WHERE DATE(create_time) 2024-01-01看着没问题吧但它等于在索引列上套了一个函数B 树里存的是原始时间值函数计算后的结果根本无法走索引查找路径。正确的写法是范围写法WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;3.3 排序和分组里的“隐性失效”联合索引在ORDER BY中能不能生效取决于排序字段是否和索引列顺序一致。比如索引是(a, b)你写ORDER BY b, a排序方向和顺序都对不上MySQL 就只能filesort。更隐蔽的是ORDER BY a DESC, b ASC这种混合排序。索引本身是按(a ASC, b ASC)排列的你要一个升序一个降序优化器没法在同一个索引顺序上做到两者兼顾大概率还是会走filesort。我实测过ORDER BY a ASC, b ASC和ORDER BY a ASC, b DESC两种写法的执行计划差异前者用到了索引避免排序后者直接出现了Using filesort。解决方案有两种思路如果你的 MySQL 版本较新8.0索引可以按(a ASC, b DESC)方式来声明让它更贴合业务需求。如果版本老旧可以尝试反向扫描或者干脆让应用层排序前提是查询结果集不大。GROUP BY的情况类似。GROUP BY a, b如果和索引顺序一致可以用索引完成分组避免临时表但GROUP BY b只有a没出现索引就无法帮上忙等同于最左前缀失效。4. 覆盖索引与回表从磁盘 IO 里看联合索引的性能真相4.1 回表为什么那么“贵”InnoDB 的二级索引叶子节点存的是“索引列的值 主键值”并没有存完整行。如果你查询的列在索引里都有MySQL 就只查索引这一棵树就搞定了这就是覆盖索引。但如果查询里出现了索引之外的列MySQL 就得拿着主键去主键索引聚簇索引里再查一遍完整行这个动作就叫回表。我举个例子来说明回表有多“贵”假设查 1000 行数据覆盖索引只需要读 1000 个索引叶子节点回表在最坏情况下要额外读 1000 次主键索引的 B 树页面。如果这 1000 条主键在磁盘上分布得乱七八糟每次回表都可能触发一次随机 IO1000 次随机 IO 的耗时足够让一条原本 10 毫秒的查询变成 300 毫秒甚至更多。所以在联合索引设计上我一直建议如果某个查询条件要覆盖高频业务尽量把SELECT需要的列也塞进索引里能覆盖就覆盖。4.2 联合索引天然适合做覆盖索引联合索引在覆盖索引这件事情上有着天然优势因为它的叶子节点里包含多个索引列的值。比如SELECT user_id, status FROM orders WHERE user_id 100 AND status PAID如果表上有(user_id, status)联合索引这个查询就不需要回表因为user_id和status都已经被索引包含了。但要注意覆盖索引也不是列越多越好。每多加一个索引列B 树就多存一份数据索引占用空间变大写入时更新二级索引的代价也变大。如果一张表有多个频率不高的查询为了覆盖它们而建设一个包含七八个字段的超宽联合索引往往得不偿失。我的建议是只覆盖真正的高频查询。优先覆盖过滤条件和排序字段最后再考虑SELECT中额外需要的列。如果某个字段经常出现在SELECT里但从不参与WHERE和ORDER BY它不是索引的优先候选列。4.3 一张小表引发的索引设计反思我曾经给某个模拟项目做优化一张订单表只有三万行但查询特别频繁并发量又高。当时有人建议把remark这种大文本字段也放进覆盖索引里直接被我否了。原因有两点TEXT和VARCHAR(1000)这类大字段放索引里索引页能容纳的条目数急剧减少B 树层级变深反而拖慢查询。大字段更新频繁的话二级索引维护成本呈指数上升。对小表而言很多时候全表扫描不一定比回表差。三万多行数据的全表扫描可能也就几十毫秒回表也只回几百行优化空间极其有限。真正值得精细化设计索引的是那种几百万行以上、查询条件多、且对延迟敏感的核心业务表。基于那次经验我把联合索引的列数上限控制在 4 到 6 个。超过 6 个列还试图用一个索引覆盖所有场景我的经验是大概率会失败不如拆成两三个更小的索引分别响应不同业务模块。5. 实战排查指南用 EXPLAIN 验证联合索引到底有没有生效5.1 EXPLAIN 里到底看什么无论你设计索引时想得多完美最终都要用EXPLAIN来验证。很多同学只知道看key字段有没有用上索引这远远不够。我会按以下顺序逐个字段排查type从好到坏大致是const、eq_ref、ref、range、index、ALL。如果出现ALL说明全表扫描出现index大概率是遍历了整棵索引树也不是好消息。key实际用到的索引名。如果为NULL索引没用上。key_len索引使用的字节数。这个字段非常关键它能告诉你联合索引中到底用到了几列。比如(a, b, c)三列联合索引如果key_len只等于a占用的字节数说明b和c并没有被用到。rows优化器预估要扫描的行数。数值越大说明定位到的范围越大。Extra出现Using index代表覆盖索引出现Using filesort代表排序没用上索引出现Using temporary代表用了临时表大概率是有GROUP BY或DISTINCT没能走索引。key_len的计算方式是固定的INT类型占 4 字节BIGINT占 8 字节VARCHAR(50)在utf8mb4下占50*42202字节左右NULL属性还会额外加 1 字节。当年看一个执行计划key_len是 4我还以为是INT类型的user_id生效了结果那一列其实是BIGINT应该显示 8说明实际生效的列和我想的完全不一样白白浪费了半天排查时间。5.2 常见问题速查表我把平时排查时遇到过的问题整理成了一张速查表方便你在执行计划不对时快速对照现象可能原因解决方向typeALL全表扫描条件列没有索引或函数/隐式转换破坏了索引检查列类型是否匹配去掉索引列上的函数操作key显示索引但key_len与预期不符联合索引用到一半后续列没生效检查条件是否满足最左前缀检查范围条件是否截断了右侧匹配ExtraUsing filesortORDER BY字段与索引顺序不一致调整ORDER BY字段顺序或调整联合索引设计ExtraUsing temporaryGROUP BY或DISTINCT无法利用索引让GROUP BY字段和索引顺序保持一致ExtraUsing index覆盖索引生效无需改动这是最优状态索引明明存在却不走优化器认为全表扫描更快检查数据量分布用FORCE INDEX临时验证但最终要靠优化 SQL 解决范围查询右侧列失效、、BETWEEN出现在联合索引中间调整索引列顺序把范围列放最后5.3 一次真实慢查询排查案例某次线上排查经历比较典型现场情况大致是这样一张流水表联合索引为(account_id, trade_time, trade_type)有一条 SQL 执行特别慢SELECT trade_type, SUM(amount) FROM trade_flow WHERE account_id 123 AND trade_time 2024-06-01 00:00:00 AND trade_time 2024-07-01 00:00:00 GROUP BY trade_type;我拿到执行计划一看key显示用了联合索引但是Extra里有Using temporary; Using filesort而且rows预估扫了十多万行。问题出现在GROUP BY trade_type上。这个联合索引里trade_type排在第三位前一位是范围条件trade_time。范围条件一旦生效trade_type在索引中就失去了全局有序性MySQL 只能把结果集中符合时间段的十多万行捞出来再放到临时表里做分组。解决方案是把索引改造成(account_id, trade_type, trade_time)。这时account_id定位账号trade_type做等值匹配或分组排序trade_time作为范围列最后收口。改造完之后执行计划里Using temporary消失了rows也从十万量级降到了几百。这个案例给了我一个很重要的提示排查慢查询不能只看索引有没有生效要看索引的列顺序是不是服务了整条 SQL 的全部需求尤其是GROUP BY和ORDER BY部分。6. 联合索引设计的完整流程从需求到交付的自检清单6.1 设计流程五步法经过了各种理论学习、实战踩坑和自我总结之后我现在设计一个联合索引会遵循一套固定流程篇幅并不长但非常有效列全表所有高频 SQL。注意不是所有 SQL而是频率排前 20 的 SQL。对每条 SQL拆分WHERE、ORDER BY、GROUP BY涉及的列。找出出现频率最高的等值条件列放在联合索引最左位置。把排序或分组列放在等值列后面。范围条件列放到最后。以某跨平台系统的订单查询接口为例我们当时的高频 SQL 分为三组按user_id查状态、按user_id查时间倒序列表、按order_no精确查订单详情。最后设计出的索引就是两个idx_user_time(user_id, create_time)服务于前两类查询。uk_order_no(order_no)唯一索引服务于第三类查询。这两个索引基本没有冗余因为它们的列在各自身份下都已经最大化利用了。6.2 自检清单每次提交索引变更给 DBA 之前我都会心里过一遍这份清单最左列是不是最高频的等值条件列如果最左列区分度不足比如性别字段联合索引的实际压缩效果会很差。有没有范围条件被夹在中间如果有就要拆出来放到最后。SQL 里的ORDER BY是否完全符合索引顺序有倒序或者混合方向就需要特别小心。SELECT列表能不能被覆盖能的话就顺手解决回表问题。有没有需要额外字段的SELECT如果需要评估加进索引的代价是否值得。这个索引会新增多少写放大写多读少的表索引数量绝不能贪多。这半年来我越来越认同一个观点联合索引不是设计出来就完事的它本质上是在跟你的 SQL 形态做长期对齐。业务变了SQL 变了索引也得跟着调整谁也不可能一次到位。7. 一些写在最后的实操心得聊了这么多最后说几个我个人的体会。第一*“SELECT” 是联合索引的隐形敌人。一个联合索引可能明明服务了WHERE条件但因为SELECT *要拿全字段导致每次都要回表整个查询就变成了“索引定位 大量随机 IO”的组合性能远不如直接查覆盖列。尤其是你在做接口性能优化时能少取字段就少取字段这比建一个几百字节的宽索引有效得多。第二别在一个字段上较劲。很多慢查询问题不是某个字段没索引而是索引列顺序放错了。你把它从第三位移到第一位可能问题就消失了。我见过有人在同一张表上建了六七个索引结果每个索引都有大量冗余列写入性能被拖垮查询也没有明显变快。倒不如删掉一半索引留下两三个按业务场景精细设计的联合索引。第三查询优化器的行为永远是最终的裁判。无论你理论推导得多完美EXPLAIN说不行就是不行。数据量的变化、值的分布、索引统计信息的更新都会影响优化器的选择。所以排查问题时我的习惯是先跑EXPLAIN再决定要不要改索引而不是先靠猜。如果你在优化一条慢查询时发现联合索引没生效先别急着怪 MySQL回头检查一下 SQL 里有没有隐式转换有没有函数包住索引列WHERE条件的顺序是否和你的索引顺序错位了——这些才是最常见的“暗坑”。把基础规则吃透比背多少案例都管用。