前阵子线上一个订单查询接口突然变慢慢查询日志里一条SQL跑了2.8秒执行计划一看是全表扫描。这条SQL本身不复杂就是按用户、店铺、状态三个条件去查订单结果却慢得离谱。问题出在索引设计上表里有三个单列索引但MySQL一次查询基本只能用一个索引剩下的条件只能回表之后逐行过滤数据量一上来就崩。这次排查让我把联合索引彻底吃透了。这篇文章围绕mysql联合索引这个话题把那次优化过程和背后的原理完整梳理一遍从最左前缀到底层B树结构从字段顺序怎么排到用EXPLAIN验证结果顺带把排序、分页和几个常见的坑一起说清楚。适合刚接触联合索引的开发者也适合已经会用但没深究过原理的人。1. 为什么单列索引救不了这场慢查询先想清楚联合索引要解决的问题1.1 一次典型的慢查询现场先复现一下当时的场景。订单表结构长这样CREATE TABLE t_order ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL COMMENT 用户ID, store_id int(11) NOT NULL COMMENT 店铺ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消, create_time datetime NOT NULL COMMENT 下单时间, total_amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_store_id (store_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;管理后台有个常用查询查某个用户在某家店铺的所有已支付订单按下单时间倒序分页返回。SELECT * FROM t_order WHERE user_id 100 AND store_id 500 AND status 1 ORDER BY create_time DESC LIMIT 0, 20;按常理说三个条件分别有索引MySQL应该能很快定位数据才对。但EXPLAIN的结果很尴尬列值说明typeref走到了索引keyidx_user_id只用了user_id那一个索引key_len4只消费了user_id这4个字节rows2103回表后还要过滤两千多行原因在于InnoDB的辅助索引是B树结构单列索引的每个键值只保存一个字段。MySQL优化器在大多数情况下只能选择一个索引作为访问路径选了idx_user_id之后store_id和status两个条件就只能在回表拿到完整行记录后再做逐行过滤。如果user_id100能关联到几万条订单回表成本就很可观。这时候联合索引的思路就来了与其让MySQL在一个索引上定位后再去碰运气不如把查询中最常用的过滤字段组合起来建成一棵支持多字段定位的B树直接把过滤条件压缩在索引查找阶段完成。1.2 联合索引的底层形态多字段组合键的B树很多人把联合索引想象成多棵树这是最常见的误解。联合索引在底层依然是一棵B树只是树节点里存的键从单个字段变成了多个字段的组合。假设我们建立的联合索引是(user_id, store_id, status)那么B树里的每个键都是这样的三元组(100, 500, 1)表示user_id100、store_id500、status1的记录。多字段组合键的大小关系怎么定MySQL会按照字段定义的顺序逐个比较先比较user_iduser_id大的就排在后面如果user_id相同再比较store_idstore_id也相同再比较status。这个过程很像查英文字典先按首字母排首字母相同看第二个字母第二个字母相同再看第三个一层一层往下推。正是这种逐字段比较的排序方式决定了联合索引的几个重要特性。第一个特性索引中的字段天然携带顺序信息。(user_id, store_id, status)这个索引相当于在B树里维护了一个按user_id分组、组内按store_id排序、同店铺内再按status排序的有序结构。这个有序性是后面优化ORDER BY、GROUP BY、范围查询的基础。第二个特性辅助索引的叶子节点除了存组合键还会附上主键id。也就是索引叶子上的完整数据其实是组合键 主键。因为辅助索引不存储完整行记录查询最终还是要根据主键回聚簇索引取全行数据。但如果查询需要的列恰好全部包含在索引键里那就不必回表了这个在第三部分会专门讲对应的是覆盖索引。1.3 最左前缀不是规则而是排序方式的必然结果网上讲联合索引几乎必提最左前缀原则。但很多人只是把它当成一条死记硬背的规则没有理解背后的原因。其实只要想明白上面说的组合键排序方式最左前缀就是一个顺理成章的结论。B树的查找依赖有序性和二分定位。对于联合索引(user_id, store_id, status)查询条件里带user_id能走索引。因为第一层排序字段确定后可以在B树里直接定位到user_id对应的那一段叶子区间。查询条件里带user_id和store_id也能走索引。在user_id定位之后store_id是组内有序的可以继续精确缩小范围。查询条件里只有store_id却用不了这个索引。因为整个B树的第一排序字段是user_id没有user_id作为入口store_id在整棵树里是乱序的MySQL无法定位应该从哪个叶子页开始找。这个逻辑用生活类比最好理解你有一本两级的目录先按姓氏笔画排再按名字笔画排。如果只知道名字叫建国想在一堆人里找他是很难的因为建国并没有在全局形成有序排列只有先知道姓氏才能在姓氏确定的子范围内快速定位名字。所以最左前缀本质上不是MySQL给人定的规矩而是组合键的存储和排序方式决定的必然结果。需要注意的是所谓最左指的是从索引第一个字段开始、连续的字段组合才有效。例如(user_id, store_id, status)这个索引查询条件能否用联合索引原因WHERE user_id 100能第一个字段等值定位WHERE user_id 100 AND store_id 500能前两个字段连续命中WHERE user_id 100 AND status 1部分能中间跳过了store_idstatus难以参与定位WHERE store_id 500 AND status 1不能缺少最左字段user_id部分能的情况值得多说一句user_id100可以定位但跳到status时由于store_id没被限制同user_id下status是非全局有序的所以status条件无法作为索引定位条件只能等回表后再过滤。这时候可以考虑用第3.3节讲的索引下推来减少回表量。2. 字段顺序怎么排区分度、查询频率和排序需求的三角博弈2.1 区分度决定哪个字段站在最左边建联合索引时大家问得最多的问题就是到底哪个字段放最前面最朴素的想法是区分度高的放前面。所谓区分度是指某个字段取值的分散程度。计算公式很简单SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_ratio, COUNT(DISTINCT store_id) / COUNT(*) AS store_ratio, COUNT(DISTINCT status) / COUNT(*) AS status_ratio FROM t_order;ratio越接近1说明该字段能过滤掉的数据比例越高越接近0说明字段取值高度集中区分度低。在我们这个场景里假设数据分布是这样的字段不同取值数区分度备注user_id10万0.9很大比例用户都有订单store_id1000.01店铺数量有限status30.000002枚举值极少从过滤效率看user_id明显应该放最左因为它把结果集从几十万行缩小到几千行status放最左边意义不大因为它只能分出三堆数据而且这三堆在全表里几乎是均匀分散的。但这不意味着区分度的优先级永远最高。首先要保证的是查询条件里必须出现的字段要放在左边否则索引根本用不上。区分度是在满足这个前提之后再去做排序。举个例子如果业务上有按店铺维度统计的报表但报表查询里不会带user_id条件那一个以(user_id, store_id, status)开头的联合索引对这条报表SQL就没有任何帮助你还得额外给store_id建单列索引或者专门建一个以store_id开头的联合索引。2.2 等值优先、范围靠后字段顺序的黄金法则除区分度之外另一个决定字段顺序的关键因素是条件的类型。这里有一个经验法则我后来在实际工作中反复验证过非常有用等值条件优先放在联合索引前面范围条件放在等值条件后面排序字段紧跟在前面的等值字段后面区分度高的字段放在区分度低的字段前面为什么范围条件必须靠后因为范围条件一旦用了它后面的字段就基本失去了索引定位能力。比如WHERE user_id 100 AND store_id 500 AND status 1如果联合索引是(user_id, store_id, status)执行流程是user_id 100精确定位到user100的叶子区间store_id 500在当前区间内继续用二分定位找到store_id500这个临界点status 1到这里就卡住了。因为user_id相同、store_id已经是一个范围status在这个范围内并不有序。MySQL无法再对status做有序二分只能把store_id 500的所有记录都拿出来回表之后逐一过滤status本质上范围条件把一个精确的点定位变成了区间扫描后续字段的有序性被切断了。所以凡是WHERE里出现范围条件的字段BETWEEN、、、LIKE prefix%排序时都应该尽量放到联合索引的末尾让更精确的等值字段有机会参与定位。ORDER BY字段的安排也一样。如果要按下单时间排序而create_time正好是联合索引的第三个字段那就要求前两个字段在前面已经通过等值条件固定下来这样create_time在组内才是有序的。否则排序还得额外做filesort。2.3 从三个单列索引到一个联合索引一个完整的设计过程回到开头的慢查询结合上面的原则我当时的索引设计过程是这样的第一步梳理高频SQL的过滤条件。管理后台订单查询几乎总会带user_id因为所有后台操作都基于某个用户展开。store_id是商户筛选条件status是状态筛选条件create_time用来排序和分页。这三个字段里user_id是查询的必选入口store_id和status是高频筛选。第二步按等值和区分度排序字段。user_id必须放最左而且它的区分度最高。store_id和status都是等值条件从区分度看store_id比status高一些所以先放store_id再放status。create_time是范围排序字段放末尾。第三步设计最终索引ALTER TABLE t_order ADD INDEX idx_user_store_status_create (user_id, store_id, status, create_time);这个索引同时解决了三个问题过滤了user_id、store_id、status三个等值条件create_time在索引内有序ORDER BY create_time DESC可以走索引顺序免掉filesort如果查询只取特定几个列还可能形成覆盖索引连回表都省了。这里有个容易走极端的误区既然索引这么好能不能把所有可能用到的字段全塞进去不行。联合索引每个字段都会占据B树节点空间字段越长一个16KB的索引页能容纳的键就越少树的层数可能增加单次扫描的I/O量也会变大。索引是用来服务的不是用来收藏的一个联合索引塞六七个字段往往只会让所有查询都变慢。只把真正高频、高收益的字段加进去才是正路。3. EXPLAIN 实测key_len、覆盖索引和索引下推的真实表现3.1 用 key_len 判断联合索引真正用到了几个字段理论说完了上点实战。联合索引建好后怎么确认SQL真的走到了索引、走满了几个字段EXPLAIN里的key_len是最准确的证据。key_len表示MySQL在索引查找时实际使用的字节数。对于联合索引它的大小取决于本次查询真正消费了前面几个字段。EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND store_id 500 AND status 1;结果列值说明typeref使用非唯一索引等值匹配keyidx_user_store_status_create命中了联合索引key_len9user_id(4) store_id(4) status(1)rows1预期扫描行数只剩1条ExtraNULL需要回表为什么是9字节因为user_id是INT NOT NULL占4字节store_id是INT NOT NULL占4字节status是TINYINT NOT NULL占1字节合计9字节。查询条件里三个字段都是等值全部参与了索引定位所以key_len正好是三者之和。如果某个查询只用了user_idkey_len就只会是4只用了user_id和store_idkey_len就是8。通过这个指标你可以快速判断联合索引到底被吃了多少非常直观。顺便记一个通用的字节数口诀方便自己估算INT4字节可空多1字节BIGINT8字节可空多1字节TINYINT1字节可空多1字节VARCHAR(n) utf8mb44n 2字节可空再多1字节比如一个VARCHAR(50) NOT NULL的字段在utf8mb4下占4*502202字节。搞懂这些看到key_len是202还是404就知道SQL走了索引几个字段。3.2 覆盖索引让索引变成一棵袖珍数据表联合索引还有一个单列索引很难发挥的隐藏优势覆盖索引。如果查询需要的所有列都包含在索引键里InnoDB根本不需要回表直接从索引叶子节点上取数据就可以了。EXPLAIN的Extra列会出现Using index。看个例子EXPLAIN SELECT user_id, store_id, status FROM t_order WHERE user_id 100 AND store_id 500;这个查询的SELECT列恰好都在idx_user_store_status_create这个索引里所以列值keyidx_user_store_status_createExtraUsing indexUsing index意味着这条SQL的所有数据都从索引上拿到了连聚簇索引都不碰。辅助索引本身比聚簇索引小得多如果这类查询频率高用覆盖索引可以极大减少I/O。但要注意覆盖索引并不能随意贪多。比如SELECT total_amount这个字段没在索引里Extra就会变成NULL或者Using filesort之类MySQL必须回表取数。所以设计联合索引时把高频查询里最常见的几个返回列考虑进去是一个很有价值的优化思路但也别把所有列都塞进索引索引膨胀的代价可能超过覆盖带来的收益。3.3 索引下推5.6以后联合索引的战斗力来源MySQL 5.6引入了索引下推Index Condition Pushdown对联合索引的查询场景是非常大的增强。这个特性专门处理索引里包含某个字段但它没法用于定位的情况。还是用前面的例子联合索引(user_id, store_id, status)查询WHERE user_id 100 AND store_id 500 AND status 1。前面说过status在store_id范围条件下无法参与精确定位。5.6之前MySQL的处理方式是在索引上只定位user_id100和store_id500这一段的记录然后每条记录回表拿到完整行后再过滤status。5.6之后MySQL会把status 1这个条件下推到存储引擎层。存储引擎在扫描索引的过程中对索引里保存的status字段先做一次过滤把明显不符合的记录直接跳过只有status1的索引项才执行回表。这样回表次数会大幅减少。EXPLAIN里这种现象会显示列值ExtraUsing index condition注意区别Using index覆盖索引连回表都没有Using index condition索引下推仍然可能回表但回表前先利用索引字段过滤了一批什么都没有老老实实回表再过滤我见过不少开发者把Using index condition当成覆盖索引其实不是一回事。但对优化来说索引下推已经是很大的进步了尤其在联合索引的靠后字段上做范围加过滤时能省掉大量回表。4. 排序与分组的优化空间避免 filesort 和临时表4.1 ORDER BY 能否走索引看的是前导等值 排序字段连续联合索引的有序性对ORDER BY的优化非常关键。MySQL如果要排序但无法利用索引的有序结构就会额外执行filesort把数据装到sort_buffer里排序。数据量大时这个排序可能还要落盘慢得很。什么时候ORDER BY可以走索引核心口诀前导字段等值 排序字段连续。举个例子。索引idx_user_create(user_id, create_time)SELECT * FROM t_order WHERE user_id 100 ORDER BY create_time DESC;因为user_id是等值条件create_time在这个联合索引里已经是组内有序的所以MySQL可以直接倒序扫索引不需要filesort。EXPLAIN的Extra列不会出现Using filesort。再看一个走不了索引的SELECT * FROM t_order WHERE user_id 100 ORDER BY store_id;当前索引是idx_user_create(user_id, create_time)但排序字段store_id不在索引里或者虽然在一个联合索引里但顺序不连续MySQL只能先把user_id100的所有记录找出来再在sort_buffer里按store_id排序。还有个常见细节排序方向和联合索引字段顺序一致时才能走索引。索引默认升序如果ORDER BY create_time ASC没问题ORDER BY create_time DESC在8.0之前也基本可以反向扫描问题不大。但如果ORDER BY create_time ASC, id DESC这种混合方向就很麻烦很可能会触发filesort。4.2 GROUP BY 其实也在利用索引的有序性很多人没意识到GROUP BY本质上也是一种排序加聚合操作。MySQL需要把分组字段相同的记录排到一起才能统计每组的数量。如果GROUP BY字段符合联合索引的排列顺序MySQL就可以直接扫描索引完成分组省掉临时表和filesort。SELECT user_id, status, COUNT(*) FROM t_order GROUP BY user_id, status;如果联合索引是idx_user_store_status_create(user_id, store_id, status, create_time)GROUP BY user_id, status并不完全匹配索引顺序因为中间隔着store_id。MySQL要按user_id和status分组但store_id在索引里排在status前面这会导致分组连续性被破坏大概率还是需要临时表。如果GROUP BY user_id, store_id则和索引完全匹配可以走索引扫描完成分组。对GROUP BY的另一个忠告别对索引字段做函数运算。比如GROUP BY MONTH(create_time)即使create_time在索引里加了函数之后索引列的值已经被改造过了MySQL没法直接利用B树的有序性索引就失效了。这种场景要么改SQL在应用层做一次月份映射要么干脆冗余一个month字段再对这个字段建索引。4.3 深分页场景下联合索引和延迟关联的配合LIMIT 0, 20这种浅分页联合索引配合得很好。但到了LIMIT 100000, 20这种深分页即使索引条件全都命中MySQL也要先扫描并跳过前面的100000条记录再把第100001到100020条返回。比如SELECT * FROM t_order WHERE user_id 100 ORDER BY create_time LIMIT 100000, 20;即便走idx_user_create索引扫描前10万条索引项、回表10万次也够喝一壶的。标准解法是延迟关联也叫先查主键再回表SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE user_id 100 ORDER BY create_time LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询只取主键id这个过程如果索引能覆盖id和create_time就在索引范围内直接扫描不需要每条都回表跳过的10万条都是轻量级跳跃。等确定好最后20个id再做一次回表把完整行取出来。这样就把回表10万次降到了回表20次代价天差地别。这个技巧配合联合索引尤其好用因为联合索引的叶子节点天然带有主键子查询SELECT id本身很可能就是覆盖索引查询。5. 踩坑记录误用联合索引的典型场景与修正方案5.1 范围条件会截断后面的索引字段这是我在新手上路时踩得最惨的一个坑。当时的联合索引是(store_id, create_time, status)查询是SELECT * FROM t_order WHERE store_id 500 AND create_time 2024-01-01 AND status 1;我以为三个字段都在索引里应该全走索引。EXPLAIN一看key_len只等于store_id的4字节加create_time的8字节status完全没用上Extra还出现了Using index condition最后一部分记录只能靠索引下推过滤。问题就出在create_time是范围条件。它一旦生效后面的status在create_time 某时间点的区间内并不是有序的无法继续精确定位。修正方案有两个方向调整索引顺序为(store_id, status, create_time)。让两个等值条件先精确过滤create_time放最后做范围排序。这正是前面2.2节说的等值优先、范围靠后。如果status本身有几个固定取值可以用IN()把范围条件改写为等值匹配集合后面再详细讲。5.2 隐式类型转换和字符集不一致会让索引悄悄失效联合索引设计得再好也架不住SQL写法上的隐形攻击。最常见的是隐式类型转换。假如store_id字段类型是VARCHARSELECT * FROM t_order WHERE store_id 500;MySQL会把字符串列转成数字进行比较结果就是store_id列上被套了一层函数WHERE CAST(store_id AS signed) 500索引列的函数运算会让B树的有序定位失效索引直接报废。判断方法还是看EXPLAIN。正常情况下type应该是ref如果变成了ALL或者key_len不涨反跌就要怀疑类型转换。另一个隐蔽问题是JOIN时两边字符集不一致。比如左表字段是utf8mb4右表字段是latin1MySQL做关联时要隐式转换字符集同样可能导致索引失效。所以在建表时统一字符集和排序规则是避免索引悄悄消失的底线。5.3 冗余索引维护成本和查询收益的平衡表里已经有联合索引(user_id, store_id, status)时再单独建一个(user_id)索引绝大多数场景下都是冗余的。因为user_id ?这个查询完全可以用前面的联合索引定位联合索引最左字段就是user_id。但我也遇到过一种例外情况user_id查询极其高频而联合索引的store_id和status字段都很长导致整个索引页比单列user_id索引大不少。在这种情况下单独建一个user_id单列索引让MySQL在跑简单查询时可以读更少的索引页反而可能更快。所以这个平衡要结合真实查询频率来评估不能一刀切。不过冗余索引的成本是实打实的。每增加一个索引INSERT、UPDATE、DELETE都要多维护一棵B树写入性能会下降索引本身也会占用buffer pool内存和磁盘空间。我在线上见过一张表有十几个索引写入慢到报警的情况不在少数。建索引永远要考虑这笔写入的开销换来的查询优化值不值。5.4 用 IN() 代替范围条件让后续字段继续生效最后分享一个比较高阶的小技巧在合适的场景里用IN()代替范围条件可以让联合索引的后续字段继续发挥作用。回到5.1的例子。如果status字段只有几个固定值而且业务上能接受待支付、已支付这一组状态把范围查询改写为SELECT * FROM t_order WHERE store_id 500 AND create_time IN (2024-01-01, 2024-01-02, 2024-01-03) AND status 1;IN()在优化器眼里本质上是多个等值条件的集合所以它不像、BETWEEN那样会截断后面字段的定位能力。同一个联合索引(store_id, create_time, status)下IN条件后面的status依然可以走索引定位key_len会多算上status的字节数。用这个手法有两点要注意IN()的列表不能太大否则优化器可能认为全表扫描更划算另外IN()对排序的支持不如真正的范围条件灵活如果同时要ORDER BY create_time还是建议等值字段放前面、范围字段放末尾的经典结构。那次线上慢查询修复之后接口响应时间从2.8秒降到了几十毫秒。回过头看这次优化的核心不是加了一个索引这么简单而是把查询模式、索引底层结构、字段排序逻辑串起来重新想了一遍。我现在的习惯是任何涉及新索引的变更先拿慢查询日志里的真实SQL去EXPLAIN确认key_len和rows都收敛了才敢上生产大表加索引尽量用在线DDL工具避免锁表影响业务。索引不是越多越好而是要在过滤、排序、覆盖这三件事之间找到平衡。希望这篇实践整理能让你下次做联合索引优化时少走几步弯路。
MySQL联合索引优化实战：从最左前缀到B+树原理与踩坑记录
前阵子线上一个订单查询接口突然变慢慢查询日志里一条SQL跑了2.8秒执行计划一看是全表扫描。这条SQL本身不复杂就是按用户、店铺、状态三个条件去查订单结果却慢得离谱。问题出在索引设计上表里有三个单列索引但MySQL一次查询基本只能用一个索引剩下的条件只能回表之后逐行过滤数据量一上来就崩。这次排查让我把联合索引彻底吃透了。这篇文章围绕mysql联合索引这个话题把那次优化过程和背后的原理完整梳理一遍从最左前缀到底层B树结构从字段顺序怎么排到用EXPLAIN验证结果顺带把排序、分页和几个常见的坑一起说清楚。适合刚接触联合索引的开发者也适合已经会用但没深究过原理的人。1. 为什么单列索引救不了这场慢查询先想清楚联合索引要解决的问题1.1 一次典型的慢查询现场先复现一下当时的场景。订单表结构长这样CREATE TABLE t_order ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL COMMENT 用户ID, store_id int(11) NOT NULL COMMENT 店铺ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消, create_time datetime NOT NULL COMMENT 下单时间, total_amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_store_id (store_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;管理后台有个常用查询查某个用户在某家店铺的所有已支付订单按下单时间倒序分页返回。SELECT * FROM t_order WHERE user_id 100 AND store_id 500 AND status 1 ORDER BY create_time DESC LIMIT 0, 20;按常理说三个条件分别有索引MySQL应该能很快定位数据才对。但EXPLAIN的结果很尴尬列值说明typeref走到了索引keyidx_user_id只用了user_id那一个索引key_len4只消费了user_id这4个字节rows2103回表后还要过滤两千多行原因在于InnoDB的辅助索引是B树结构单列索引的每个键值只保存一个字段。MySQL优化器在大多数情况下只能选择一个索引作为访问路径选了idx_user_id之后store_id和status两个条件就只能在回表拿到完整行记录后再做逐行过滤。如果user_id100能关联到几万条订单回表成本就很可观。这时候联合索引的思路就来了与其让MySQL在一个索引上定位后再去碰运气不如把查询中最常用的过滤字段组合起来建成一棵支持多字段定位的B树直接把过滤条件压缩在索引查找阶段完成。1.2 联合索引的底层形态多字段组合键的B树很多人把联合索引想象成多棵树这是最常见的误解。联合索引在底层依然是一棵B树只是树节点里存的键从单个字段变成了多个字段的组合。假设我们建立的联合索引是(user_id, store_id, status)那么B树里的每个键都是这样的三元组(100, 500, 1)表示user_id100、store_id500、status1的记录。多字段组合键的大小关系怎么定MySQL会按照字段定义的顺序逐个比较先比较user_iduser_id大的就排在后面如果user_id相同再比较store_idstore_id也相同再比较status。这个过程很像查英文字典先按首字母排首字母相同看第二个字母第二个字母相同再看第三个一层一层往下推。正是这种逐字段比较的排序方式决定了联合索引的几个重要特性。第一个特性索引中的字段天然携带顺序信息。(user_id, store_id, status)这个索引相当于在B树里维护了一个按user_id分组、组内按store_id排序、同店铺内再按status排序的有序结构。这个有序性是后面优化ORDER BY、GROUP BY、范围查询的基础。第二个特性辅助索引的叶子节点除了存组合键还会附上主键id。也就是索引叶子上的完整数据其实是组合键 主键。因为辅助索引不存储完整行记录查询最终还是要根据主键回聚簇索引取全行数据。但如果查询需要的列恰好全部包含在索引键里那就不必回表了这个在第三部分会专门讲对应的是覆盖索引。1.3 最左前缀不是规则而是排序方式的必然结果网上讲联合索引几乎必提最左前缀原则。但很多人只是把它当成一条死记硬背的规则没有理解背后的原因。其实只要想明白上面说的组合键排序方式最左前缀就是一个顺理成章的结论。B树的查找依赖有序性和二分定位。对于联合索引(user_id, store_id, status)查询条件里带user_id能走索引。因为第一层排序字段确定后可以在B树里直接定位到user_id对应的那一段叶子区间。查询条件里带user_id和store_id也能走索引。在user_id定位之后store_id是组内有序的可以继续精确缩小范围。查询条件里只有store_id却用不了这个索引。因为整个B树的第一排序字段是user_id没有user_id作为入口store_id在整棵树里是乱序的MySQL无法定位应该从哪个叶子页开始找。这个逻辑用生活类比最好理解你有一本两级的目录先按姓氏笔画排再按名字笔画排。如果只知道名字叫建国想在一堆人里找他是很难的因为建国并没有在全局形成有序排列只有先知道姓氏才能在姓氏确定的子范围内快速定位名字。所以最左前缀本质上不是MySQL给人定的规矩而是组合键的存储和排序方式决定的必然结果。需要注意的是所谓最左指的是从索引第一个字段开始、连续的字段组合才有效。例如(user_id, store_id, status)这个索引查询条件能否用联合索引原因WHERE user_id 100能第一个字段等值定位WHERE user_id 100 AND store_id 500能前两个字段连续命中WHERE user_id 100 AND status 1部分能中间跳过了store_idstatus难以参与定位WHERE store_id 500 AND status 1不能缺少最左字段user_id部分能的情况值得多说一句user_id100可以定位但跳到status时由于store_id没被限制同user_id下status是非全局有序的所以status条件无法作为索引定位条件只能等回表后再过滤。这时候可以考虑用第3.3节讲的索引下推来减少回表量。2. 字段顺序怎么排区分度、查询频率和排序需求的三角博弈2.1 区分度决定哪个字段站在最左边建联合索引时大家问得最多的问题就是到底哪个字段放最前面最朴素的想法是区分度高的放前面。所谓区分度是指某个字段取值的分散程度。计算公式很简单SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_ratio, COUNT(DISTINCT store_id) / COUNT(*) AS store_ratio, COUNT(DISTINCT status) / COUNT(*) AS status_ratio FROM t_order;ratio越接近1说明该字段能过滤掉的数据比例越高越接近0说明字段取值高度集中区分度低。在我们这个场景里假设数据分布是这样的字段不同取值数区分度备注user_id10万0.9很大比例用户都有订单store_id1000.01店铺数量有限status30.000002枚举值极少从过滤效率看user_id明显应该放最左因为它把结果集从几十万行缩小到几千行status放最左边意义不大因为它只能分出三堆数据而且这三堆在全表里几乎是均匀分散的。但这不意味着区分度的优先级永远最高。首先要保证的是查询条件里必须出现的字段要放在左边否则索引根本用不上。区分度是在满足这个前提之后再去做排序。举个例子如果业务上有按店铺维度统计的报表但报表查询里不会带user_id条件那一个以(user_id, store_id, status)开头的联合索引对这条报表SQL就没有任何帮助你还得额外给store_id建单列索引或者专门建一个以store_id开头的联合索引。2.2 等值优先、范围靠后字段顺序的黄金法则除区分度之外另一个决定字段顺序的关键因素是条件的类型。这里有一个经验法则我后来在实际工作中反复验证过非常有用等值条件优先放在联合索引前面范围条件放在等值条件后面排序字段紧跟在前面的等值字段后面区分度高的字段放在区分度低的字段前面为什么范围条件必须靠后因为范围条件一旦用了它后面的字段就基本失去了索引定位能力。比如WHERE user_id 100 AND store_id 500 AND status 1如果联合索引是(user_id, store_id, status)执行流程是user_id 100精确定位到user100的叶子区间store_id 500在当前区间内继续用二分定位找到store_id500这个临界点status 1到这里就卡住了。因为user_id相同、store_id已经是一个范围status在这个范围内并不有序。MySQL无法再对status做有序二分只能把store_id 500的所有记录都拿出来回表之后逐一过滤status本质上范围条件把一个精确的点定位变成了区间扫描后续字段的有序性被切断了。所以凡是WHERE里出现范围条件的字段BETWEEN、、、LIKE prefix%排序时都应该尽量放到联合索引的末尾让更精确的等值字段有机会参与定位。ORDER BY字段的安排也一样。如果要按下单时间排序而create_time正好是联合索引的第三个字段那就要求前两个字段在前面已经通过等值条件固定下来这样create_time在组内才是有序的。否则排序还得额外做filesort。2.3 从三个单列索引到一个联合索引一个完整的设计过程回到开头的慢查询结合上面的原则我当时的索引设计过程是这样的第一步梳理高频SQL的过滤条件。管理后台订单查询几乎总会带user_id因为所有后台操作都基于某个用户展开。store_id是商户筛选条件status是状态筛选条件create_time用来排序和分页。这三个字段里user_id是查询的必选入口store_id和status是高频筛选。第二步按等值和区分度排序字段。user_id必须放最左而且它的区分度最高。store_id和status都是等值条件从区分度看store_id比status高一些所以先放store_id再放status。create_time是范围排序字段放末尾。第三步设计最终索引ALTER TABLE t_order ADD INDEX idx_user_store_status_create (user_id, store_id, status, create_time);这个索引同时解决了三个问题过滤了user_id、store_id、status三个等值条件create_time在索引内有序ORDER BY create_time DESC可以走索引顺序免掉filesort如果查询只取特定几个列还可能形成覆盖索引连回表都省了。这里有个容易走极端的误区既然索引这么好能不能把所有可能用到的字段全塞进去不行。联合索引每个字段都会占据B树节点空间字段越长一个16KB的索引页能容纳的键就越少树的层数可能增加单次扫描的I/O量也会变大。索引是用来服务的不是用来收藏的一个联合索引塞六七个字段往往只会让所有查询都变慢。只把真正高频、高收益的字段加进去才是正路。3. EXPLAIN 实测key_len、覆盖索引和索引下推的真实表现3.1 用 key_len 判断联合索引真正用到了几个字段理论说完了上点实战。联合索引建好后怎么确认SQL真的走到了索引、走满了几个字段EXPLAIN里的key_len是最准确的证据。key_len表示MySQL在索引查找时实际使用的字节数。对于联合索引它的大小取决于本次查询真正消费了前面几个字段。EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND store_id 500 AND status 1;结果列值说明typeref使用非唯一索引等值匹配keyidx_user_store_status_create命中了联合索引key_len9user_id(4) store_id(4) status(1)rows1预期扫描行数只剩1条ExtraNULL需要回表为什么是9字节因为user_id是INT NOT NULL占4字节store_id是INT NOT NULL占4字节status是TINYINT NOT NULL占1字节合计9字节。查询条件里三个字段都是等值全部参与了索引定位所以key_len正好是三者之和。如果某个查询只用了user_idkey_len就只会是4只用了user_id和store_idkey_len就是8。通过这个指标你可以快速判断联合索引到底被吃了多少非常直观。顺便记一个通用的字节数口诀方便自己估算INT4字节可空多1字节BIGINT8字节可空多1字节TINYINT1字节可空多1字节VARCHAR(n) utf8mb44n 2字节可空再多1字节比如一个VARCHAR(50) NOT NULL的字段在utf8mb4下占4*502202字节。搞懂这些看到key_len是202还是404就知道SQL走了索引几个字段。3.2 覆盖索引让索引变成一棵袖珍数据表联合索引还有一个单列索引很难发挥的隐藏优势覆盖索引。如果查询需要的所有列都包含在索引键里InnoDB根本不需要回表直接从索引叶子节点上取数据就可以了。EXPLAIN的Extra列会出现Using index。看个例子EXPLAIN SELECT user_id, store_id, status FROM t_order WHERE user_id 100 AND store_id 500;这个查询的SELECT列恰好都在idx_user_store_status_create这个索引里所以列值keyidx_user_store_status_createExtraUsing indexUsing index意味着这条SQL的所有数据都从索引上拿到了连聚簇索引都不碰。辅助索引本身比聚簇索引小得多如果这类查询频率高用覆盖索引可以极大减少I/O。但要注意覆盖索引并不能随意贪多。比如SELECT total_amount这个字段没在索引里Extra就会变成NULL或者Using filesort之类MySQL必须回表取数。所以设计联合索引时把高频查询里最常见的几个返回列考虑进去是一个很有价值的优化思路但也别把所有列都塞进索引索引膨胀的代价可能超过覆盖带来的收益。3.3 索引下推5.6以后联合索引的战斗力来源MySQL 5.6引入了索引下推Index Condition Pushdown对联合索引的查询场景是非常大的增强。这个特性专门处理索引里包含某个字段但它没法用于定位的情况。还是用前面的例子联合索引(user_id, store_id, status)查询WHERE user_id 100 AND store_id 500 AND status 1。前面说过status在store_id范围条件下无法参与精确定位。5.6之前MySQL的处理方式是在索引上只定位user_id100和store_id500这一段的记录然后每条记录回表拿到完整行后再过滤status。5.6之后MySQL会把status 1这个条件下推到存储引擎层。存储引擎在扫描索引的过程中对索引里保存的status字段先做一次过滤把明显不符合的记录直接跳过只有status1的索引项才执行回表。这样回表次数会大幅减少。EXPLAIN里这种现象会显示列值ExtraUsing index condition注意区别Using index覆盖索引连回表都没有Using index condition索引下推仍然可能回表但回表前先利用索引字段过滤了一批什么都没有老老实实回表再过滤我见过不少开发者把Using index condition当成覆盖索引其实不是一回事。但对优化来说索引下推已经是很大的进步了尤其在联合索引的靠后字段上做范围加过滤时能省掉大量回表。4. 排序与分组的优化空间避免 filesort 和临时表4.1 ORDER BY 能否走索引看的是前导等值 排序字段连续联合索引的有序性对ORDER BY的优化非常关键。MySQL如果要排序但无法利用索引的有序结构就会额外执行filesort把数据装到sort_buffer里排序。数据量大时这个排序可能还要落盘慢得很。什么时候ORDER BY可以走索引核心口诀前导字段等值 排序字段连续。举个例子。索引idx_user_create(user_id, create_time)SELECT * FROM t_order WHERE user_id 100 ORDER BY create_time DESC;因为user_id是等值条件create_time在这个联合索引里已经是组内有序的所以MySQL可以直接倒序扫索引不需要filesort。EXPLAIN的Extra列不会出现Using filesort。再看一个走不了索引的SELECT * FROM t_order WHERE user_id 100 ORDER BY store_id;当前索引是idx_user_create(user_id, create_time)但排序字段store_id不在索引里或者虽然在一个联合索引里但顺序不连续MySQL只能先把user_id100的所有记录找出来再在sort_buffer里按store_id排序。还有个常见细节排序方向和联合索引字段顺序一致时才能走索引。索引默认升序如果ORDER BY create_time ASC没问题ORDER BY create_time DESC在8.0之前也基本可以反向扫描问题不大。但如果ORDER BY create_time ASC, id DESC这种混合方向就很麻烦很可能会触发filesort。4.2 GROUP BY 其实也在利用索引的有序性很多人没意识到GROUP BY本质上也是一种排序加聚合操作。MySQL需要把分组字段相同的记录排到一起才能统计每组的数量。如果GROUP BY字段符合联合索引的排列顺序MySQL就可以直接扫描索引完成分组省掉临时表和filesort。SELECT user_id, status, COUNT(*) FROM t_order GROUP BY user_id, status;如果联合索引是idx_user_store_status_create(user_id, store_id, status, create_time)GROUP BY user_id, status并不完全匹配索引顺序因为中间隔着store_id。MySQL要按user_id和status分组但store_id在索引里排在status前面这会导致分组连续性被破坏大概率还是需要临时表。如果GROUP BY user_id, store_id则和索引完全匹配可以走索引扫描完成分组。对GROUP BY的另一个忠告别对索引字段做函数运算。比如GROUP BY MONTH(create_time)即使create_time在索引里加了函数之后索引列的值已经被改造过了MySQL没法直接利用B树的有序性索引就失效了。这种场景要么改SQL在应用层做一次月份映射要么干脆冗余一个month字段再对这个字段建索引。4.3 深分页场景下联合索引和延迟关联的配合LIMIT 0, 20这种浅分页联合索引配合得很好。但到了LIMIT 100000, 20这种深分页即使索引条件全都命中MySQL也要先扫描并跳过前面的100000条记录再把第100001到100020条返回。比如SELECT * FROM t_order WHERE user_id 100 ORDER BY create_time LIMIT 100000, 20;即便走idx_user_create索引扫描前10万条索引项、回表10万次也够喝一壶的。标准解法是延迟关联也叫先查主键再回表SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE user_id 100 ORDER BY create_time LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询只取主键id这个过程如果索引能覆盖id和create_time就在索引范围内直接扫描不需要每条都回表跳过的10万条都是轻量级跳跃。等确定好最后20个id再做一次回表把完整行取出来。这样就把回表10万次降到了回表20次代价天差地别。这个技巧配合联合索引尤其好用因为联合索引的叶子节点天然带有主键子查询SELECT id本身很可能就是覆盖索引查询。5. 踩坑记录误用联合索引的典型场景与修正方案5.1 范围条件会截断后面的索引字段这是我在新手上路时踩得最惨的一个坑。当时的联合索引是(store_id, create_time, status)查询是SELECT * FROM t_order WHERE store_id 500 AND create_time 2024-01-01 AND status 1;我以为三个字段都在索引里应该全走索引。EXPLAIN一看key_len只等于store_id的4字节加create_time的8字节status完全没用上Extra还出现了Using index condition最后一部分记录只能靠索引下推过滤。问题就出在create_time是范围条件。它一旦生效后面的status在create_time 某时间点的区间内并不是有序的无法继续精确定位。修正方案有两个方向调整索引顺序为(store_id, status, create_time)。让两个等值条件先精确过滤create_time放最后做范围排序。这正是前面2.2节说的等值优先、范围靠后。如果status本身有几个固定取值可以用IN()把范围条件改写为等值匹配集合后面再详细讲。5.2 隐式类型转换和字符集不一致会让索引悄悄失效联合索引设计得再好也架不住SQL写法上的隐形攻击。最常见的是隐式类型转换。假如store_id字段类型是VARCHARSELECT * FROM t_order WHERE store_id 500;MySQL会把字符串列转成数字进行比较结果就是store_id列上被套了一层函数WHERE CAST(store_id AS signed) 500索引列的函数运算会让B树的有序定位失效索引直接报废。判断方法还是看EXPLAIN。正常情况下type应该是ref如果变成了ALL或者key_len不涨反跌就要怀疑类型转换。另一个隐蔽问题是JOIN时两边字符集不一致。比如左表字段是utf8mb4右表字段是latin1MySQL做关联时要隐式转换字符集同样可能导致索引失效。所以在建表时统一字符集和排序规则是避免索引悄悄消失的底线。5.3 冗余索引维护成本和查询收益的平衡表里已经有联合索引(user_id, store_id, status)时再单独建一个(user_id)索引绝大多数场景下都是冗余的。因为user_id ?这个查询完全可以用前面的联合索引定位联合索引最左字段就是user_id。但我也遇到过一种例外情况user_id查询极其高频而联合索引的store_id和status字段都很长导致整个索引页比单列user_id索引大不少。在这种情况下单独建一个user_id单列索引让MySQL在跑简单查询时可以读更少的索引页反而可能更快。所以这个平衡要结合真实查询频率来评估不能一刀切。不过冗余索引的成本是实打实的。每增加一个索引INSERT、UPDATE、DELETE都要多维护一棵B树写入性能会下降索引本身也会占用buffer pool内存和磁盘空间。我在线上见过一张表有十几个索引写入慢到报警的情况不在少数。建索引永远要考虑这笔写入的开销换来的查询优化值不值。5.4 用 IN() 代替范围条件让后续字段继续生效最后分享一个比较高阶的小技巧在合适的场景里用IN()代替范围条件可以让联合索引的后续字段继续发挥作用。回到5.1的例子。如果status字段只有几个固定值而且业务上能接受待支付、已支付这一组状态把范围查询改写为SELECT * FROM t_order WHERE store_id 500 AND create_time IN (2024-01-01, 2024-01-02, 2024-01-03) AND status 1;IN()在优化器眼里本质上是多个等值条件的集合所以它不像、BETWEEN那样会截断后面字段的定位能力。同一个联合索引(store_id, create_time, status)下IN条件后面的status依然可以走索引定位key_len会多算上status的字节数。用这个手法有两点要注意IN()的列表不能太大否则优化器可能认为全表扫描更划算另外IN()对排序的支持不如真正的范围条件灵活如果同时要ORDER BY create_time还是建议等值字段放前面、范围字段放末尾的经典结构。那次线上慢查询修复之后接口响应时间从2.8秒降到了几十毫秒。回过头看这次优化的核心不是加了一个索引这么简单而是把查询模式、索引底层结构、字段排序逻辑串起来重新想了一遍。我现在的习惯是任何涉及新索引的变更先拿慢查询日志里的真实SQL去EXPLAIN确认key_len和rows都收敛了才敢上生产大表加索引尽量用在线DDL工具避免锁表影响业务。索引不是越多越好而是要在过滤、排序、覆盖这三件事之间找到平衡。希望这篇实践整理能让你下次做联合索引优化时少走几步弯路。