上周被拉去排查一个慢查询某项目的订单表已经 800 万行一条很普通的查询SELECT * FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;跑一次要 2 秒多。表上明明有联合索引 idx_user_time(user_id, create_time)EXPLAIN 也显示用上了索引但实际大部分时间耗在回表上。这类问题我不是第一次遇到了。很多人对 MySQL 主键索引和联合索引的理解停留在知道有 B 树、知道最左前缀的层面真到设计索引、排查慢 SQL 的时候全靠试。这篇文章想把这两类索引的底层原理讲透联合索引为什么讲究列顺序主键索引为什么影响整张表所有索引索引下推和覆盖索引又是怎么帮我们省回表成本的适合正在被慢查询折磨的开发者也适合准备数据库面试的人我会把原理和实操放在一起讲但核心始终是那一棵 B 树。1. 从两个真实查询说起联合索引列顺序如何影响性能1.1 准备一张演示表两个顺序相反的联合索引为了方便说明先造一张订单演示表。注意这张表上我故意挂了两个列顺序相反的联合索引真实生产环境不要这么干后面会专门解释为什么这是冗余设计。CREATE TABLE user_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_time (user_id, create_time), KEY idx_time_user (create_time, user_id) ) ENGINEInnoDB;假设业务特征是这样的10 万个活跃用户平均每个用户 80 条订单订单的时间分布集中在最近半年。数据量、数据分布都清楚了下面看两个典型查询。1.2 同一条查询为什么索引顺序变了代价就变查询一查某个用户最近的订单。SELECT id, order_no, amount, create_time FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;走 idx_user_time 时索引树先定位到 user_id123 这个位置然后沿着叶子节点链表向后扫描 create_time 大于指定值的记录扫出来的数据天然就是按 (user_id, create_time) 排序的连 ORDER BY 的额外排序都省了。这个路径的扫描范围很小——主键等值定位一次剩下只需要读取该用户自己的那批索引条目。走 idx_time_user 时情况就完全不同了。create_time 是联合索引的第一列user_id 不是索引的前缀列所以 MySQL 只能先定位到 create_time 2024-06-01 这个索引区间这个区间可能横跨几十万甚至上百万条索引条目扫描过程中再把 user_id123 的记录过滤出来。虽然 MySQL 8.0 的索引下推ICP能在存储引擎层帮我们过滤 user_id减少一部分回表但区间起点只能按 create_time 定位中间要扫的索引条目数量级完全不一样。查询二统计某个时间段内的订单量。SELECT COUNT(*) FROM user_orders WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30;这次反转了。idx_time_user 可以精准定位到 6 月 1 日这个起点只扫一个月的索引区间计数器加上去就完事而 idx_user_time 因为 create_time 不是第一列完全无法直接从时间上定位只能把整棵索引树扫一遍把所有 create_time 落在范围内的条目数出来。1.3 两种查询模式对照查询模式索引 (user_id, create_time)索引 (create_time, user_id)某用户 时间范围等值定位后扫小区间无需排序先扫大时间范围再过滤扫描量大纯时间范围统计无法直接定位整树扫描时间定位精准扫描量小同样是两个字段的联合索引只是顺序换了一下同一批查询的代价可能差两个数量级。这里的差别不是 MySQL 优化器偷懒而是底层存储结构决定的联合索引在 B 树里的排序方式、聚簇索引和二级索引之间的协作方式共同决定了什么查询能走捷径。要真正理解得先看清主键索引到底长什么样。2. 主键索引的底层形态聚簇索引与二级索引的协作关系2.1 B 树在 InnoDB 里的存储单元页与层高先说基础。InnoDB 的数据是按页组织的默认一页 16KB。一颗 B 树里非叶子节点页只存索引键和指向子页的指针它们负责路由真正的数据全部落在叶子节点页。查找时从根节点开始每层做一次二分定位逐层向下最后在叶子页里找到目标记录。一颗 B 树通常只有 2 到 4 层。做个粗略估算主键如果是 BIGINT8 字节加上子页指针约 6 字节一个 16KB 的内部页大概能放 1000 个目录项叶子页按每行 200 字节算一个页能放 80 行左右。三层 B 树大致可以支撑 1000 × 1000 × 80也就是 8000 万量级的数据。这里有个重要推论树越高一次查找要访问的页越多。而前两层因为数据量小大概率常驻在 buffer pool 里真正可能产生磁盘 IO 的往往是最底层那一次——这也是为什么三层和四层的差距在热数据场景下会非常明显。2.2 聚簇索引整张表就是一棵主键树InnoDB 里每张表必然有一棵聚簇索引树这棵树就是表本身。叶子页里存的是完整的一行记录行数据按主键顺序物理排列。主键等值查询和主键范围查询之所以快是因为直接在聚簇索引上定位一次查找就能拿到整行。一个常见的误解是没建主键就没有聚簇索引。实际规则是建了主键用主键做聚簇索引没有主键InnoDB 会选第一个非空唯一索引再没有就生成一个隐藏的自增 rowid 列。也就是说聚簇索引永远存在主键只是最理想的那种选择。这个设计也解释了为什么主键一旦被更新整个行可能都要移动位置——因为聚簇索引的叶子页是按主键顺序组织的行迁移的成本很高。2.3 二级索引只存索引字段和主键值普通索引、联合索引、唯一索引在 InnoDB 里都属于二级索引。二级索引也是一棵 B 树但叶子节点不存整行只存两部分索引列的值加上主键值。查询时先在二级索引里找到目标主键再拿着主键去聚簇索引里定位完整行这个额外的查找动作就叫回表。这里有个关键问题为什么二级索引的叶子不直接存行的物理地址因为物理地址不稳定。页分裂、行迁移、碎片整理都会改变记录的存放位置如果二级索引存了地址每次行移动都要把所有相关索引一起改。InnoDB 的选择是让二级索引存主键这个逻辑标识行怎么移动都由聚簇索引自己维护二级索引完全不用感知。所以回表本质上是一次额外的 B 树查找扫描大量二级索引条目时回表次数会成倍放大查询成本。2.4 主键大小会传染到每一个二级索引既然每个二级索引的叶子节点都要带一份主键值那么主键越长每一张二级索引的代价就越大。二级索引叶子页能放的条目数变少树就可能变高、IO 变多buffer pool 里能缓存的索引页也变少。这是个连锁反应。我见过一张表主键用 CHAR(36) 的 UUID表上挂了 6 个二级索引数据量 2000 万。后来把主键改成 BIGINT 自增并清洗数据单表空间直接降了将近 40%几个高频查询的扫描行数也明显下降。所以主键设计不只是选个唯一值的问题它直接影响整张表所有索引的体积和效率。后面第五节我会专门展开主键选择的几种方案。3. 联合索引的构建规则最左前缀背后的排序逻辑3.1 联合索引的排序方式先排第一列再排第二列如果说清楚了一棵 B 树怎么存主键、怎么存二级索引联合索引的原理就顺理成章了。联合索引本质上也是一棵二级索引 B 树只是叶子和内部节点里同时包含多列的值。所有条目先按第一列排序第一列相同的再按第二列排序以此类推。用一个生活类比理解联合索引 (last_name, first_name) 就像按姓名排列的电话簿。你想找张伟只需要先在电话簿里翻到姓张的区域再在这个区域内按名找非常快但如果你只知道对方叫伟不知道姓什么就没办法翻到任何特定位置因为整个电话簿不是按名排序的。3.2 五类典型查询哪些命中哪些不命中以索引 (a, b, c) 为例MySQL 解析 WHERE 条件时的实际行为如下查询条件索引中的定位方式实际效果a 1 AND b 2 AND c 3a、b、c 依次等值定位全部命中最优a 1 AND b 2a 等值定位b 范围定位完整命中b 之后的列无法定位a 1 ORDER BY ba 等值定位b 天然有序连排序都省了b 2 AND c 3无法从 a 开始定位联合索引整体用不上特殊情况见 3.3a 1 AND b 2a 范围定位b 无法定位b 不能用于定位但可交给 ICP 过滤这里要纠正一个流传很广的说法范围条件后面的索引列会失效。更准确的说法是范围条件后面的列无法继续在索引上做定位但索引下推机制仍然可以在存储引擎扫描时过滤这些列减少回表。换句话说不是完全没用而是定位能力降级成了过滤能力。这正好呼应第 4 节要讲的 ICP。再解释一个容易绕晕的点为什么 b 2 AND c 3 这种查询对 (a, b, c) 索引这么不友好因为索引整体按 a 排序a 没有约束时所有 a 值交织在一起b 2 的记录在整个索引里并没有连续区间。就像电话簿里伟散落在很多姓氏下面你没法从第一个伟一路顺下去读。3.3 8.0 的松散索引扫描最左前缀的一个例外MySQL 8.0.13 之后引入了 Skip Scan松散索引扫描专门处理查询条件没有覆盖联合索引第一列的情况典型场景就是 WHERE b 2 而索引是 (a, b)。它的思路很直接既然 a 的不同取值个数不多那就把每个 a 值都枚举出来对每个 a 值在索引内部做一个 (a具体值, b2) 的等值定位。相当于把一个大问题拆成若干个小区间来查。代价也很明显扫描成本跟 a 的 distinct 数量线性相关。如果 a 有 10 万个不同值这个方案还不如直接扫全索引如果 a 只有 3 个值效果就很好。所以优化器只有在判断第一列区分度很低、枚举成本可控时才会选择它。这并不意味着最左前缀规则被废弃了而是多了一种在特定数据分布下的兜底手段。4. 索引下推与覆盖索引省回表的两条隐蔽通道4.1 索引下推把过滤动作下沉到存储引擎回表很贵所以一切能减少回表次数的机制都值得理解。索引下推ICP是 MySQL 5.6 引入的优化默认开启。它的含义是原本需要回到 Server 层才能做的 WHERE 过滤现在可以提前到存储引擎扫描索引的时候做。举个例子索引 (a, b, c)查询 WHERE a 1 AND c 5。a 用于索引定位c 因为在 a 之后又缺少 b 的等值条件无法参与定位。没有 ICP 时存储引擎会把所有 a1 的索引条目连同主键值回传给 Server 层Server 层再逐条判断 c5命中的才回表拿整行这意味着大量无效回表。有了 ICP存储引擎在扫描叶子节点时就把 c5 过滤掉只把满足条件的条目交给 Server 层。回表次数从a1 的全部行降为a1 AND c5 的真实结果集。EXPLAIN 里如果 Extra 列出现 Using index condition就说明走的是索引下推。注意它和索引失效完全不是一回事——恰恰相反它是在索引无法完整定位时尽量把索引的剩余价值榨干。4.2 覆盖索引整个查询在一棵索引树里完成覆盖索引是另一个减少回表的手段逻辑更简单如果查询要的列全部包含在某个二级索引里MySQL 就直接读索引叶子页根本不需要回表。EXPLAIN 的 Extra 列会显示 Using index。举一个典型场景订单表上有 (user_id, create_time, amount) 联合索引查询是 SELECT amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 10:30:00。索引叶子节点里有 user_id、create_time也有 amount三列都齐了查询全程不需要碰聚簇索引。对于高频的点查接口这种设计收益非常可观。但覆盖索引不是免费的。索引列越多索引体积越大写入时维护成本越高。更关键的是别指望 SELECT * 也能覆盖因为 SELECT * 通常意味着把整行都查出来二级索引里根本放不下。所以覆盖索引要挑高频且字段固定的查询来做。这也是为什么我一直建议大家写 SQL 时按需查列而不是顺手 SELECT *。4.3 从 EXPLAIN 的 Extra 列判断执行路径排查慢查询时比起看 MySQL 到底用没用索引更重要的是看查询实际走了什么路径。EXPLAIN 输出里我重点看这几项字段含义重点关注type访问类型const/ref 优于 range再优于 index 和 ALLkey实际选择的索引注意是不是被另一个看起来更对的索引抢走了key_len实际用于定位的索引字节数判断联合索引用了哪几列rows预估扫描行数和实际结果集对比差距越大越可疑Extra附加信息Using index / Using index condition / Using filesort 三种都要认看一个具体例子mysql EXPLAIN SELECT id, amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01\G *************************** 1. row *************************** type: range key: idx_user_time key_len: 9 rows: 120 Extra: Using index conditionkey_len 是 9说明 user_idINT4 字节和 create_timeDATETIME5 字节都参与了定位。如果这里 create_time 没有参与定位key_len 只会显示 4rows 会明显变大。我对 key_len 的判断方法是把 key_len 和联合索引的列宽度对应上就能知道优化器实际吃到索引的哪一列这是排查索引设计问题最直接的证据。5. 实战设计经验字段顺序、主键选择与索引取舍5.1 字段排列的四条经验法则讲完原理落到设计上。联合索引字段顺序我一般按下面四条原则排等值条件的列放前面范围条件的列放后面。等值列可以精准定位范围列只能圈定区间让区间尽量小才是关键。多个等值列之间区分度高的通常放前面。如果 user_id 能筛掉 99% 的数据而 type 只能筛掉一半优先把 user_id 放前面。把 ORDER BY、GROUP BY 用到的列考虑进去。索引本身有序能契合排序需求就不用 filesort这是很多排序查询的关键优化。前缀列尽量短。索引整体宽度决定叶子页密度字符串字段做前缀索引或放后面都是减少体积的手段。举个组合例子业务查询是 WHERE user_id ? AND status ? ORDER BY create_time DESC那优先考虑 (user_id, create_time) 或者 (user_id, status, create_time)。user_id 等值定位后create_time 天然有序排序省了status 如果是一个低基数字段可以放 create_time 后面用 ICP 过滤也可以根据 status 的过滤能力决定要不要把它提前。没有放之四海而皆准的公式核心是拿真实数据分布去验证。5.2 主键三选一自增、UUID、趋势递增ID主键选择是另一个影响全局的决定。方案写入方式索引体积适用场景BIGINT 自增顺序追加页分裂最少最小绝大多数内部业务表UUID 字符串随机写入页分裂频繁碎片多大拖累所有二级索引多端生成、需要防枚举的业务趋势递增ID雪花类近似顺序页分裂较少中分布式系统需要全局唯一自增主键为什么写入友好因为新增记录的主键越来越大聚簇索引总是往最新页的尾部追加新页顺序写满即可。UUID 主键则不同新记录可能落在树的任何位置随机触发页分裂造成空间碎片和大量额外写 IO。而且 UUID 哪怕换成 BINARY(16) 存储随机性依然存在页分裂问题并不会消失。另外有个容易被忽视的点主键必须永远不变。因为聚簇索引叶子页按主键排序改主键几乎等于移动整行牵连所有二级索引更新。所以业务上看起来唯一的字段例如手机号、邮箱、身份证号都不适合直接做主键因为它们都有被修改的可能性。这也是代理主键这种设计能成为主流的原因之一。5.3 用系统视图找出冗余索引冗余索引是线上数据库最常见的隐形杀手。识别逻辑其实很朴素如果索引 A 的前缀列和索引 B 完全一致而 B 的列覆盖更全那么 A 大概率是冗余的。举例表上同时有 (a) 和 (a, b) 两个索引。所有只用 a 的查询走 (a, b) 也能满足最左前缀但 (a) 的存在让每次写入都要多维护一棵 B 树。这就是典型的冗余应该删掉 (a)。同理有 (a, b, c) 又有 (a, b) 时后者通常冗余。注意(a) 和 (b) 不是冗余关系(b, a) 和 (a, b) 也不是因为最左前缀方向不同。MySQL 5.7.23 之后的 sys schema 里有一张 schema_redundant_indexes 视图可以直接查出冗余索引对不用自己一条条比对。没有 sys schema 的版本也可以查 information_schema.STATISTICS按 TABLE_NAME 分组对比前缀列。清理冗余索引对写入性能的提升往往比加新索引还明显。5.4 三个常见设计误区最后汇总三个我在实际项目里反复见过的误区。第一个误区是索引越多越好。每个索引都是一棵 B 树每次 INSERT、UPDATE、DELETE 都要全量维护一遍。索引过多会直接放大写入延迟索引页还会占用 buffer pool 空间挤压真正需要缓存的数据页。索引不是保险是成本。第二个误区是对覆盖索引的期望过高。有人为了覆盖把十几个列全塞进一个联合索引索引宽度爆炸叶子页密度骤降反而拖慢扫描。覆盖索引只适合高频、字段固定的查询一般撑死三到五列再多就要重新评估。第三个误区是忽略数据分布的变化。三个月前区分度高的字段可能因为新业务模式变成低基数字段之前的索引设计随之失效。慢查询排查时如果发现 rows 和预期差很多别着急加索引先看看数据分布是不是变了统计信息是不是过期了再决定是优化索引还是优化查询。最后说一个我这些年养成的习惯。每次写完一条新查询我不会急着上线而是先跑一遍 EXPLAIN重点看两个数字key_len 是否用到了我预期的那几列rows 是否比实际结果集大不了多少。如果看到 Using filesort或者 key 突然变成了别的索引我都会停下来想想是统计信息过期了还是索引设计本身有冲突。索引设计没有银弹数据分布变了、查询模式变了之前的规则都可能要推翻。但只要把聚簇索引、二级索引、最左前缀这几层原理放在脑子里排查慢查询时你就有了一条清晰的推理链而不是靠猜。