1. 为什么单列索引总在关键时刻“失灵”先理解复合索引的底层结构先说个我印象很深的咨询。某同学问我“表里明明建了索引为什么一条简单的查询还是慢得离谱”我打开他发的建表语句一看三个查询条件分别建了三个独立索引表里数据几百万行SQL跑出来用了好几秒。他说每个条件都有索引怎么就不生效。这就是典型的单列索引困境。数据库面对一条带多个过滤条件的SQL最多只能利用其中一个索引来快速定位剩下的条件还得靠回表之后在内存里慢慢过滤。你建了三个索引但每次查询只能“单挑”一个另外两个是摆设。这个时候就需要复合索引。复合索引也叫联合索引、多列索引本质上是把多个列按指定顺序拼成一个索引结构。听起来不就是把几列放到一起建索引吗没那么简单。它能否发挥作用取决于你对它的理解是否到位。如果把单列索引理解成一本书的目录那复合索引就是一本多级目录——先按第一层分类找再在分类里按第二层找逐级缩小范围。问题在于这本目录的查阅规则极其严格用错了顺序目录就跟废纸一样。举个例子。联合索引(a, b, c)在数据库内部并不是分别给 a、b、c 各建一个索引而是把所有行按照“先 a 后 b 再 c”的顺序排序后构建一棵 B 树。这意味着索引里每条记录的逻辑顺序是(a值, b值, c值, 主键值)。你可以把它想象成电话号码簿先按姓氏排姓氏相同的按名字排名字再相同就按中间字排。你要找一个“姓张、名三、中间字是丰”的人能直接翻到张姓区域再在张姓区域里找三字辈最后定位到张三丰。但如果你想找所有“中间字是丰”的人这本电话簿就帮不上忙了——因为中间字不是第一排序关键字整个目录里它根本没有独立组织。这就是复合索引最核心的原理跨列的组合排序是有先后顺序的你的查询条件必须从最左列开始连续匹配索引才能逐级生效。也就是常说的“最左前缀原则”。最左前缀原则让很多初学者栽跟头核心原因在于理解得太表面。它不只是说“where条件里要有第一列”而是说查询条件必须能够从索引最左列开始形成一段连续的匹配区间。你要么命中a要么命中a, b要么命中a, b, c如果条件里直接跳过 a 或者跳过了中间的 b索引就断链了。理解了这一点你才能解释很多看似“诡异”的现象为什么(a, b, c)索引下的where b 1 and c 2完全不走索引为什么where a 1 and c 2只走了一部分索引为什么where b 1 order by a依然慢得像全表扫描。这些都是因为查询条件没有形成从左到右的连续前缀。2. 复合索引的列顺序设计别上来就谈区分度先搞清楚优先级复合索引设计时最经典的问题就是列的顺序到底怎么排网上很多文章会告诉你“把区分度高的列放前面”这个说法本身没有错但它经常被错误地放大导致一堆唯区分度论的设计翻车。先说一个被忽视的真相区分度原则的前提是查询条件里这些列的“位置”都是平等的。但在真实业务 SQL 里条件列的定位完全不同有的条件是等值查询有的是范围查询有的是排序字段有的只是偶尔出现在过滤条件里。它们在索引设计里的优先级完全不一样。我在实际项目里通常按下面的优先级来排。第一优先级等值查询的列尤其是那种“每条 SQL 都一定会带”的等值列无条件放最前面。比如订单查询里user_id、tenant_id、status这种固定过滤条件。如果一个查询是where user_id u123 and status 1那user_id就应该在status前面。因为等值条件一旦确定索引就能把搜索范围精确缩到极小这是索引效率的最大来源。第二优先级排序字段。SQL 里如果有order by排序字段应该尽量设计进索引里并且排在等值条件后面。原因很简单B树本身是有序结构如果排序字段已经作为索引的后续列查询结果在索引扫描时天然有序数据库就省掉了 filesort文件排序这一步。这往往是查询性能提升最明显的地方比区分度带来的收益大得多。第三优先级范围查询的列。、、between、like abc%这类范围条件对索引的使用比较特殊范围列之后的索引列无法继续用于精确定位只会退化成“索引内过滤”。所以在列顺序上范围查询列要尽量放在后面同时要接受一个现实它后面的列没法再走索引树定位了。这里必须单独讲讲“区分度”这个被滥用最多的概念。单看一张表status可能只有 3 种值user_id可能有一百万种值于是很多人下意识把user_id放前面。但如果每一条查询都是where status 1 and user_id u123那status放在前面完全没问题因为等值条件下即使只有 3 种值也能很快把范围缩到三分之一然后user_id继续精确匹配。真正出问题的场景是某一条 SQL 里status作为等值条件存在而另一条 SQL 里它又是范围条件或者不存在。这时候要结合真实查询模式决定。我建议大家在设计过程中拿一张纸写清楚这条 SQL 最核心的过滤条件是什么有没有排序有没有范围查询统计下来哪些列出现频率最高。别盯着区分度表看半天那是舍本逐末。3. 一个典型案例订单查询优化从秒级到毫秒级讲理论容易飘还是拿一个我实际经手的例子来说。某模拟项目的订单表数据量大概 300 万行核心表结构长这样CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, amount decimal(12,2) DEFAULT NULL, product_name varchar(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务方反馈说后台订单列表页打开很慢查一下发现是一条分页查询跑出来的SELECT id, user_id, status, create_time, amount, product_name FROM orders WHERE user_id u123 AND status 1 ORDER BY create_time DESC LIMIT 20;这是一条再典型不过的查询两个等值条件一个排序字段分页只需要 20 条。业务方的预期是毫秒级返回但实际执行计划显示全表扫描跑了 2.8 秒。为什么慢因为表里当时只在user_id上建了一个单列索引。这条 SQL 能把这个索引用上定位到u123的全部订单可能几千行然后必须在内存里做两层过滤加排序。数据量一大性能就崩了。优化方案很简单建立一条对应的复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);执行计划立刻变了走索引idx_user_status_time扫描行数从全表 300 万变成几十行Extra里不再出现Using filesort和Using where查询耗时稳定在 50 毫秒以内。这里面有一个值得琢磨的地方为什么排序字段create_time要放在status后面因为user_id和status都是等值匹配定位到具体位置后create_time作为索引的第三列天然有序。MySQL 在索引扫描时直接按顺序取前 20 条返回排序这一步就省掉了。如果你把create_time放前面或者干脆不加这个字段都会退化成先找出所有满足等值条件的行再走文件排序——虽然行数通常不多但绝对不是最优。再补一个反向验证。如果有一类查询是where status 1 order by create_time desc但 user_id 不固定那这个索引就失效了因为条件里缺少最左列user_id。这时候与其执着于一个索引通吃所有 SQL不如拆开设计单独为status和create_time建一个索引。现实里很多表的一条 SQL 和另一条 SQL 的过滤条件重叠度并不高强行为了“复用”而设计一个大而全的索引反而让每一条查询都用得不舒服。索引设计要跟着真实 SQL 走不是凑列的堆叠游戏。4. 索引“明明建了却不走”复合索引最常见的失效场景排查优化过几条查询之后我发现大多数数据库性能问题根本不是“没索引”而是“索引没被正确使用”。下面这几个坑都是我实际踩过的。4.1 最左前缀不满足复合索引(a, b, c)条件是where b 2直接全表扫。因为索引默认从 a 开始匹配跳过 a 的话 b 和 c 的排序对于定位毫无意义。很多人以为“我把 b 也建了索引”但实际上复合索引里 b 是有序的但它是“在 a 相同的前提下有序”a 不定b 的全局顺序就不存在。类比一下一本两层目录的书你想翻到第二层目录里的某个词但没指定第一层分类根本无从定位。4.2 范围条件后面的列全部断链这是最隐蔽的坑。索引(a, b, c)条件是where a 1 and b 100 and c 5。很多人以为 a、b、c 三列都能用到索引实际上b 100这个范围条件限定了 b 的区间之后 c 的排序在 b 的“某个范围内”是乱序的所以 c 不能再用于精确定位只能作为索引内过滤。执行计划里这种现象非常容易误判——索引明明被用上了key_len显示只用了两层。要诊断的话看执行计划的key_len字段凡是 c 参与了等值匹配key_len 一定会包含 c 的长度否则就是没生效。这类问题最实用的解法是调整列顺序把范围列尽量往后面放把后续等值列放在它前面。比如上面的查询索引设计成(a, c, b)才是正解。注意这不是让你把 b 从索引里去掉只是调整优先级。4.3 隐式类型转换和函数运算索引列上做任何运算都会破坏索引匹配。最常见的是类型不一致比如user_id是varchar查询里写成where user_id 123456MySQL 会把列转成数字再比较索引失效。检查办法很简单执行explain后看key_len如果比预期短或者直接显示NULL多半就是类型对不上。函数运算也是一样where DATE(create_time) 2024-01-01这种写法直接让create_time列失去索引能力。正确做法是改写为范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-024.4 OR 条件导致的索引失效where a 1 or b 2如果a和b不在同一个索引里优化器往往会选择全表扫描因为它需要对两个独立分支的结果做合并。MySQL 在某些版本里支持索引合并Index Merge但这是优化器自己决定的不可控。稳妥的方案是把 OR 改写为 UNION ALL或者单独建一个覆盖两个字段的复合索引。排查这些问题的标准工具就是EXPLAIN我每次都重点看三列字段关注点type出现ALL基本就是全表扫描ref或range才是健康的index要警惕可能是全索引扫描key实际使用了哪个索引注意看是不是你以为的那个key_len计算索引实际消耗的字节数能直接看出到底用到了索引的哪几列key_len的计算是个技巧活。拿常见的idx_user_status_time (user_id varchar(32), status tinyint, create_time datetime)来说utf8mb4字符集下 varchar 每字符最多 4 字节32 字符就是 128 字节加上变长字段的 2 字节tinyint是 1 字节datetime是 5 字节MySQL 5.6 版本。如果这一列允许 NULL还要额外加 1 字节。理论值大约是 128215136 字节。当你看到key_len只有 131 甚至更短时就说明后面的列根本没进入索引匹配。这个技巧用来验证“复合索引到底用到了第几层”非常准。5. 复合索引的进阶用法覆盖索引、冗余索引与设计取舍索引优化的下一个层级是让索引除了“定位”之外连“取数据”都一并代劳。5.1 覆盖索引连回表都省掉InnoDB 的索引分为聚簇索引和二级索引。聚簇索引就是主键索引叶子节点存整行数据二级索引的叶子节点只存索引列和主键值。当你通过二级索引查到主键后还需要再通过主键去聚簇索引里查一次完整行数据这个过程叫回表。如果能把这个回表省掉查询性能还能再上一个台阶。做法很直接让查询需要的所有列都在索引里。比如上面的查询如果改成只查id, user_id, status, create_time而索引正好包含这四个列那么二级索引扫描完直接拿到全部数据执行计划的Extra里会显示Using index。这种情况下索引既是过滤器又是数据源一石二鸟。覆盖索引的设计价值在统计类查询上格外明显。select count(*) from orders where user_id u123 and status 1这种语句如果索引能覆盖全部条件列MySQL 直接数索引里的记录数就行速度极快。我在实际项目中给报表类查询设计索引时会特意把select里经常出现的列也塞进索引尾部哪怕它不参与过滤——这是很多人容易忽略的优化点。5.2 索引不是越多越好成本与收益的权衡复合索引优化做得越多越要警惕索引膨胀。每个索引都是 B 树都有独立的存储结构和维护成本。插入一条数据所有索引都要同步更新删除一条数据所有索引都要做清理。索引越多写入越慢磁盘占用越高。对一个高频写入的表无脑堆索引会让整体性能劣化。我在索引评审时有一个习惯把“冗余索引”清理掉。比如一个表已经有(a, b)索引再建一个(a)索引就完全没有必要因为(a, b)的最左前缀已经覆盖了(a)的能力。这是最常见也最容易被忽视的浪费。真正的完整索引列表应该是在满足全部查询模式的前提下尽可能少的数量。每加一个索引都要问自己一句现有索引真的覆盖不了这个场景吗5.3 实操心得SQL 与索引的对应关系应该写成文档优化告一段落后我建议把核心 SQL 和索引的对应关系记下来做成一张表贴在设计文档里。包括每条核心 SQL 长什么样、走了哪个索引、key_len是多少、预计扫描行数是多少。这样后续任何人改 SQL 或者加新需求第一反应是去查这张表而不是凭感觉写查询、寄希望于优化器。这个习惯帮我避免了很多次“SQL 写得没问题但就是慢”的返工。另外还有一个容易被忽略的小细节MySQL 8.0 支持降序索引order by create_time desc可以直接用(user_id, status, create_time desc)来精确匹配排序方向执行计划里不再需要反向扫描。如果你用的还是 5.7 及以下版本反向扫描也能处理但性能略逊一筹。版本升级之后这类排序场景值得重新审视。