从一次“慢得离谱”的订单查询说起上个月帮一个电商团队处理线上慢查询订单表数据量刚过两千万一个订单列表接口每次请求都要跑接近1.5秒。看执行计划居然是全表扫描。检查表结构用户ID上有单列索引下单时间上也有单列索引看起来“该建的都建了”。但问题恰恰出在这里——一个查询里有多个过滤条件单列索引却一个都没用上。这个场景你应该不陌生。业务刚起步时一条SQL带两三个条件建单列索引也跑得挺快等数据量上来同样的SQL开始秒级返回你尝试在多个列上分别建索引结果没改善个别情况下反而更慢。这时候联合索引也叫复合索引、多列索引才是真正要用的东西。但要建“最优联合索引”绝不是把几个列塞进一条KEY里就完事——字段的排列顺序、条件类型、排序需求、覆盖程度每一样都直接影响最终效果。这篇文章基于我实际排查和调优的经验从原理到实操把这个过程完整走一遍希望能帮你理清从“有索引”到“索引可用”再到“索引最优”这三步之间到底差了些什么。1. 联合索引本质上是什么不是“多个单列索引叠加”先解决一个很多刚接触联合索引的人都会搞混的问题在 (a, b) 上建联合索引和分别在 a、b 上建两个单列索引是两种完全不同的东西。1.1 InnoDB 的索引底层一棵有序的 B 树InnoDB 的每个索引底层都是一棵 B 树。单列索引的叶子节点按这一列的值排序联合索引的叶子节点则按所有索引列依次排序——先按第一列排第一列相同的记录再按第二列排第二列再相同的又按第三列排以此类推。打个比方查字典。联合索引 (姓, 名) 就相当于字典先按姓氏拼音排列同姓的人再按名字排列。你要是想找“张三”可以快速定位想找“张”姓的所有人也能用前缀快速扫但如果你只知道“三”这个名字、不知道姓就没办法利用这个顺序了只能一页页翻。这个比喻基本把联合索引的行为规则讲清楚了也就是后面要说的最左前缀原则。1.2 为什么多个单列索引解决不了“多条件 AND”MySQL 8.0 之前以及 8.0 的大多数情况下一条 SQL 在优化阶段对一个表最多只能选用一个二级索引来过滤数据。所以下面这条查询SELECT * FROM orders WHERE user_id 12345 AND status 1 AND create_time 2024-06-01;即使(user_id)、(status)、(create_time)各有单列索引优化器也只能挑其中一个认为区分度最好的来用用完之后如果结果集还很大就只能在二级索引和聚簇索引之间来回回表把命中的完整行读出来再逐一用剩下两个条件过滤。以前的 MySQL 有一个交并索引Index Merge机制听着像是可以“一个查询用多个索引”了但实践里它并不总是可靠也常常退化成全表扫描。做索引设计时不要指望 Index Merge 来救你——它更像是一个优化器的兜底策略而不是你可以依赖的常规设计手段。1.3 联合索引的三个核心能力一个设计合理的联合索引同时具备三种功能过滤按多个等值条件快速缩小扫描范围。排序索引本身有序如果ORDER BY的字段正好是索引列的一部分就能省掉 filesort。覆盖如果查询所需字段全都包含在索引列里就能直接通过二级索引返回结果连回表都不需要这就是覆盖索引。在真正动手建联合索引之前先对齐这个认知你建的不是“几个列的集合”而是一个复合的有序数据结构。后面所有的设计原则都是围绕怎么让这个复合有序结构最大程度地服务于你的实际SQL。2. 最左前缀原则联合索引的“游戏规则”2.1 什么条件下联合索引才生效假设我们有联合索引(a, b, c)那么下面这些查询条件能用到它查询条件是否走索引用到了哪几列说明a ?走a从第一列开始a ? AND b ?走a, b连续的左侧前缀a ? AND b ? AND c ?走a, b, c全匹配b ?不走无跳过第一列b ? AND c ?不走无跳过第一列a ? AND c ?走ac 无法用到b 的跳跃导致 c 失配关键就一句话从联合索引的最左列开始连续的、中间的列不能断。少了中间的任意一列后面的列都白建。这个规则本身不难难的是实际 SQL 里经常混着范围条件、排序字段规则会有更多细节。2.2 范围条件会“截断”联合索引范围条件指、、、、BETWEEN等。看这条SELECT * FROM orders WHERE user_id 12345 AND create_time 2024-06-01 AND status 1;如果我们建了(user_id, create_time, status)user_id 12345等值命中第一列create_time 2024-06-01命中第二列但它是范围条件status 1是第三列用不上了因为前面第二列已经是一个范围索引在第二列排序靠前、但第三列无法保证有序。所以结果是这个索引只能帮你定位到“user_id12345 且 create_time 在某时间之后的记录”然后还得把这段范围内的记录回表再逐个过滤status。如果调整字段顺序为(user_id, status, create_time)user_id 12345等值命中第一列status 1等值命中第二列create_time 2024-06-01范围命中第三列。三个条件都参与索引过滤了后面的范围条件只要放在所有等值条件之后就不会截断前面的等值列而且它自己也用得上。注意这里说的“截断”是指排在范围列之后的其他索引列无法继续参与索引树的筛选但 MySQL 还有一种机制叫索引下推Index Condition PushdownICP会自动把后面的条件推到存储引擎层去做部分过滤一定程度上能减少回表次数但那是“挪到引擎层过滤”不是“走索引树过滤”这两者的效率仍有差别。后面单独展开。2.3 会让联合索引干脆失效的写法很多人在字段顺序上抠了半天结果一跑EXPLAIN发现 key 是 NULL全表扫描。常见原因我列一下对索引列使用函数WHERE DATE(create_time) 2024-06-01MySQL 没法直接利用create_time的有序性因为每一行都要先算函数值。应对方案改成create_time 2024-06-01 AND create_time 2024-06-02。实在有函数需求可以考虑 MySQL 8.0 的函数索引CAST(...)表达式索引或者在字段上冗余一个预处理列并建索引。隐式类型转换WHERE order_no 1234567890但order_no是 VARCHAR。MySQL 会把字符串列转成数字进行比较相当于对索引列做了隐式函数操作索引就失效了。实践里我见过很多次这种“查不了就是慢”的诡异问题查出来就是类型不匹配。LIKE 前缀模糊WHERE name LIKE %张%前缀没有确定字符索引无法定位LIKE 张%则可以用。OR 条件WHERE user_id 1 OR status 1两个条件分属不同列MySQL 往往无法直接用联合索引同时完成两个分支经常退化成全表扫描或索引合并。这些“索引生效”边界一定要先记牢否则后面讨论如何“最优”都没有意义。3. 字段顺序怎么排没有万能套路但有一个决策框架真正开始设计一个联合索引时第一步不是拍脑袋想“哪个列区分度高”而是先把你业务里要优化的那条 SQL 拿过来拆清楚里面有几类条件。3.1 第一优先级把所有等值条件放在最前面联合索引排字段时等值条件、IN应该排在最前面而且这些等值条件之间内部怎么排需要参考区分度。为什么等值条件要优先因为等值条件下索引可以作为精确的定位器迅速收敛到某一片连续区间后面的列还可以继续精确或范围过滤。如果先把一个范围条件放在前面后面即便有更精确的等值条件也全部白搭。举个例子表结构CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, channel VARCHAR(16) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, KEY idx_user_channel_status_time (user_id, channel, status, create_time) ) ENGINEInnoDB;查询SELECT * FROM orders WHERE user_id 123456 AND channel app AND status 1 AND create_time 2024-01-01;user_id、channel、status 三个等值条件排前面create_time 这个范围条件沉底这就是标准的最优形态。3.2 等值条件之间区分度高的放前面如果同一条 SQL 里有多个等值条件比如user_id ? AND status ?两者在索引里的先后顺序有讲究吗有。假设user_id在某张表里每个用户平均只有 3 条订单而status只有 2 个值比如0/1那么(user_id, status)比(status, user_id)更好。原因很直观(user_id, status)先用 user_id 收敛到 3 条记录再在 3 条里精确找 status能快速定位极小范围。(status, user_id)先用 status 收敛到全表一半的记录这几百万条里再按 user_id 找。虽然联合索引内部依然能帮助过滤但中间需要跳跃扫描的区间范围要大得多。区分度的计算方法很简单SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_sel, COUNT(DISTINCT status) / COUNT(*) AS status_sel FROM orders;这个比值越高说明这个列的区分度越好。实际操作时我一般会把等值条件中区分度最高的放最前因为这个顺序可以减少 B 树在层级和叶子节点上的扫描开销也能让后续列更快进入精确匹配区间。3.3 排序字段放在等值条件之后顺序保持一致联合索引还有一个容易被忽略的巨大价值帮ORDER BY省掉 filesort。比如SELECT * FROM orders WHERE user_id 123456 ORDER BY create_time DESC LIMIT 10;如果只建(user_id)索引MySQL 会先捞出该用户的所有订单再做一次临时排序然后取前 10 条。用户订单上万条时这个 filesort 的成本并不低。如果建(user_id, create_time)索引InnoDB 在叶子节点上就保证了同一个 user_id 下的记录按 create_time 有序排列。查询时先定位到 user_id123456 在索引中对应的位置然后顺着索引顺序直接往回扫 10 条即可既过滤又排序还省掉临时表和文件排序。注意两点ORDER BY的字段必须排在联合索引中所有等值条件之后且方向要一致。如果索引是索引列按升序存储ORDER BY create_time ASC完全兼容ORDER BY create_time DESC在 MySQL 8.0 支持倒序索引但即使没有倒序索引InnoDB 也可以从后往前扫描问题不大。多个排序字段时必须和索引里的顺序完全一致比如(a, b)索引支持ORDER BY a ASC, b ASC但不支持ORDER BY b ASC, a ASC也不支持ORDER BY a ASC, b DESC除非建倒序索引。这个细节很多人在排列组合时踩坑。3.4 覆盖索引让查询干脆不用回表回表是什么InnoDB 的二级索引叶子节点存的是“索引列的值 主键值”如果你查的字段不在索引列里就要拿着主键回到聚簇索引主键索引的 B 树里再查一次完整行。回表次数一旦多了查询的 IO 成本就上来了。所以一个进阶技巧是把查询里需要返回的字段也加到联合索引尾部让整个查询所需的数据都在索引页里不用回表。这就是覆盖索引。举例SELECT order_no, amount FROM orders WHERE user_id 123456 AND create_time 2024-06-01;如果建(user_id, create_time, order_no, amount)这个联合索引那么从二级索引的叶子节点上就能直接拿到 order_no 和 amount连聚簇索引都不用回。这在大数据量分页场景下效果极其明显SELECT order_no, amount FROM orders WHERE user_id 123456 ORDER BY create_time DESC LIMIT 10 OFFSET 10000;没有覆盖索引时MySQL 要先找到 10010 条记录的主键回表 10010 次再排序取后 10 条。有覆盖索引时直接沿着二级索引顺序扫描 10010 个索引条目不需要任何回表就能定位到那一页。3.5 多个高频 SQL 之间怎么平衡业务里不可能只有一条 SQL。“最优联合索引”在设计时要从高频 SQL 集合来做取舍而不是单独看某一条。我的做法是打开慢查询日志或 performance_schema把 Top 30 的慢 SQL 拉出来分析把每条 SQL 的 WHERE 等值条件、范围条件、ORDER BY 字段、SELECT 字段拆成结构化清单找出“出现次数最多、影响最大”的那组条件组合优先为它设计联合索引让其他 SQL 尽量兼容这个索引兼容不了再考虑加第二个索引但严格控制一张表的索引数量。为什么控制数量因为每多一个索引INSERT/UPDATE/DELETE 时都要同步维护一棵 B 树索引太多会让写放大严重。尤其高频写入表5 个以上二级索引的维护成本非常可观。4. 用 EXPLAIN 和真实数据验证索引设计理论讲完了真正的挑战在于你以为会走索引的 SQL数据库优化器觉得不一定划算你以为用不上的索引优化器又偶尔会选。所以验证是索引调优最关键的环节。4.1 一个真实改造案例还是订单表下面是简化后的表结构和原始慢 SQLCREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, order_no VARCHAR(32) NOT NULL, channel VARCHAR(16) NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL ) ENGINEInnoDB; INSERT INTO orders (user_id, status, order_no, channel, amount, create_time) SELECT FLOOR(RAND() * 100000), FLOOR(RAND() * 2), UUID(), ELT(FLOOR(RAND() * 4) 1, app, web, h5, api), ROUND(RAND() * 1000, 2), NOW() - INTERVAL FLOOR(RAND() * 365) DAY FROM information_schema.columns LIMIT 1000000;慢 SQLSELECT order_no, amount FROM orders WHERE user_id 123456 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 10;改造前表里只有主键没有任何二级索引。执行计划自然显示typeALL, rows1000000耗时大约 800ms 左右。第一次先加单列索引(user_id)ALTER TABLE orders ADD KEY idx_user (user_id);再看执行计划定位到 user_id123456 后rows 显示 8但 Extra 里有Using where说明后面 status、create_time 的判断是在回表拿完整行后逐行过滤的。单用户数据量少时没问题可如果这个用户有几千上万条订单回表加过滤的成本会线性上升。然后改成联合索引ALTER TABLE orders DROP INDEX idx_user, ADD INDEX idx_user_status_time (user_id, status, create_time);再看这条 SQLEXPLAIN SELECT order_no, amount FROM orders WHERE user_id 123456 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 10;结果类似于idselect_typetabletypekeyrowsfilteredExtra1SIMPLEordersrangeidx_user_status_time5100.00Using index condition注意几个变化type从ALL变成rangekey变为idx_user_status_timerows大幅下降Extra里出现了Using index condition说明 MySQL 走到了 ICP把 create_time 的条件推给了存储引擎过滤。这里我需要特别说明一下即使status和create_time都在索引上因为 create_time 是范围条件在索引树层面真正用于精确定位的是user_id和status后面 create_time 的范围条件借由 ICP 在存储引擎层完成仍能大幅减少回表次数。所以字段顺序(user_id, status, create_time)在这条 SQL 上是合理的设计。接下来如果想让这条查询完全不回表把返回字段也塞进索引ALTER TABLE orders ADD INDEX idx_user_status_time_cover (user_id, status, create_time, order_no, amount);再次 EXPLAINExtra 会显示Using index表示覆盖索引扫描连Using index condition都可能没机会出现因为所有数据都从索引页取到了。这种极端情况下的优化效果对大数据量 LIMIT/OFFSET 分页特别友好。4.2 EXPLAIN 关键字段怎么读才不踩坑type从好到差大致是system const eq_ref ref range index ALL。看到ALL就该警觉。index虽然也在索引树上扫但通常意味着扫描了整棵索引树效果比ALL好一点但未必是好事。key实际选中的索引名注意它不是“优化器认为最好的索引”而是经过成本估算后觉得最划算的。rows优化器预估需要扫描的行数不是精确值但数量级变化能直观反映索引效果。filtered通过索引过滤后剩余行中满足条件的百分比。越高越好。Extra是一个信息量最大的字段几个高频值的含义Using where回表后还需要继续过滤。Using index覆盖索引扫描所有数据都从索引页获得。Using index conditionICP在存储引擎层用索引前置条件过滤减少回表。Using filesort需要额外的文件排序步骤一般要重点优化。Using temporary使用临时表多出现在 GROUP BY、DISTINCT、关联查询中尽量要消除。我见过不少人只看key不是 NULL 就认为“索引生效了”直到看到rows还是几十万、Extra里挂着Using filesort才意识到索引虽然被用了却是以很低效的方式用的。实际上key列只要不是 NULL 就算是走索引了但走得“好不好”完全看rows和Extra。4.3 验证时建一个足够“脏”的测试数据索引优化里一个反直觉的现象是数据量小的时候全表扫描反而比走索引快。优化器会根据行数、索引基数、查询成本来判断要不要走索引你建了索引它也可能不走。这不算索引设计失败只是成本决策。所以做 EXPLAIN 验证时最好把测试环境的表数据量做到生产级的十分之一到五分之一并且字段值分布要尽量贴近真实比如 status 不能全部是 0user_id 不能特别集中。不然你验证出来的执行计划和生产环境完全不是一回事。5. 联合索引设计里我踩过的四个典型坑经验分享部分。这些坑我在生产环境里不止一次见过有些自己踩过有些帮别人扫过雷统一记录一下。5.1 冗余索引两个索引的重复建设很常见的设计是表里已经建了(a, b, c)又建了一个(a)单列索引。问题是(a, b, c)的最左前缀本身就覆盖了a ?这种查询场景单列索引(a)完全冗余。还有更隐性的冗余(a, b, c)和(a, b)后者的能力前一个全都有。发现冗余索引的方法是查看sys.schema_unused_indexes或performance_schema的索引使用统计把一段时间内从未被使用、或使用次数极低的索引列出来再对照已有索引的字段前缀来判断是否能删。删除冗余索引能直接降低写入开销因为每次插入都要维护多棵 B 树。对写多读少的表这个收益非常明显。5.2 隐式类型转换索引建得再好也防不住这个坑我反复遇到。订单号、手机号这类字段用 VARCHAR 存储是常规操作但业务代码里查询时可能用了数字类型参数。比如SELECT * FROM orders WHERE order_no 123456789012345678;如果 order_no 是 VARCHARMySQL 会把列值隐式转成数字再比较索引失效。有个办法可以快速自查对疑似隐式转换的列执行EXPLAIN SELECT * FROM orders WHERE order_no 12345;如果字符串形式能走索引而数字形式不能那基本就是隐式类型转换在作祟。解决思路有两个一是代码里把参数类型对齐二是对确实需要数字比较的字段直接改成 BIGINT 存储。5.3 更新频繁的字段被放在了索引前列联合索引排序是按照列值顺序存储的如果第一列是login_count、last_login_time这种高频更新的列每次 UPDATE 都可能导致索引分裂或页重组产生大量随机 IO 和碎片。设计联合索引时如果业务对某字段的写入非常频繁我会优先考虑把它放在索引的后面位置即便区分度没那么高也要结合写放大来权衡。毕竟索引优化不是只看读性能还得维持读写平衡。5.4 冗余索引清理后没有回归验证这个和经验无关纯粹是流程问题。清理索引后一定要重新跑一遍核心 SQL 的执行计划集合最好做一个索引变更前后的回归对比尤其是涉及关联查询的时候某个索引被删除可能让另一个 SQL 的执行计划从ref变成ALL。我一般会维护一个“关键 SQL 清单”每条 SQL 配一个期望的type阈值比如至少range和期望的key索引名每次索引变更后用脚本批量EXPLAIN并对比差异防止索引“优化”变成“劣化”。最后再分享一点实操体会在真实业务环境里纯理论上的“最优联合索引”往往不存在存在的是“当前负载下的较优解”。同一个字段组合在读写比例不同的表上最优排法可能完全不同在数据分布变化后也可能需要重新验证。所以我的建议是每次设计联合索引时都按“等值优先、区分度次之、范围沉底、排序跟随、覆盖兜底”这个顺序做一轮自查然后用 EXPLAIN 和真实数据量验证最后再落到表上。这样做出来的联合索引不一定是最漂亮的但一定是最“经打”的。
