这周帮同事排查一条慢查询的时候我发现他的表在联合索引上建错了顺序导致一个原本应该毫秒级返回的查询硬生生跑成了三秒多。这个场景我想大家都不陌生——人人都知道“索引能优化查询”但真正到了设计主键、设计联合索引的时候往往就只剩下“把常用字段放前面”这一句话。至于为什么放前面、放错了会怎样、主键到底应该怎么选很少有人能讲透。今天这篇就来把 MySQL 中主键索引和联合索引的原理掰开揉碎聊一遍你会理解 B 树到底长什么样、聚簇索引和非聚簇索引有什么区别、联合索引为什么要求最左前缀以及如何用 EXPLAIN 判断自己的索引设计是否合理。内容不绕弯子尽量用大白话还原底层逻辑适合所有写过 MySQL 的开发者也适合准备面试时临时抱佛脚的读者。1. 索引的底层世界观一张按顺序排好的目录1.1 没有索引时MySQL 是怎么找数据的MySQL 的数据存储在磁盘上InnoDB 引擎把数据组织成一个个 16KB 的页Page页之间通过指针串联。没有索引时你要查一行数据数据库只能从第一个页开始一个页一个页读下去每一行都做比较直到找到匹配的记录。这条路径叫全表扫描Full Table Scan。表只有几百行时感觉不到问题但表到了千万行每页假设存几百行一次查询可能先后读上万个页磁盘 IO 的耗时自然就上去了。磁盘 IO 和内存访问不一样内存随机读也许只要几十纳秒而磁盘一次随机读大概要几毫秒差了好几个数量级。所以“减少 IO 次数”就成了索引设计的核心目标。索引本质上就是把数据的关键信息单独提取出来做成一张有序的目录查询时先翻目录再按目录上的地址去取数据这样就不用把整本书从头翻到尾了。1.2 B 树MySQL 默认的索引结构MySQL 的 InnoDB 引擎默认使用 B 树来组织索引。B 树是一种平衡多路查找树它的特点是非叶子节点只保存索引键和指向子节点的指针不保存实际数据真正的数据或主键值都保存在叶子节点上并且所有叶子节点通过双向链表串在一起。这句话有很多人听过但你要理解它为什么这么设计。一个 16KB 的页能容纳很多索引键假设每个索引键加上指针占 20 字节那么一个非叶子节点就能存大约 800 个键。三层 B 树就能存下 800×800×800也就是五亿多条记录。换句话说一个几千万行的大表你从根节点出发最多只需要三次磁盘 IO 就能定位到叶子节点。第一层读根节点第二层读中间节点第三层读叶子节点。这是 B 树相对于 B 树和二叉树最大的优势树矮、层数少、IO 少。叶子节点用双向链表相连又让范围查询变得非常顺畅。比如你要查 id 从 100 到 200 的所有记录B 树只需要先定位到 100 所在的叶子节点然后顺着链表往后读就行不需要一层一层重新查找。1.3 聚簇索引和非聚簇索引到底差在哪这里需要引入两个概念聚簇索引Clustered Index和二级索引Secondary Index也叫辅助索引。聚簇索引的叶子节点存的是整行数据。二级索引的叶子节点存的是索引键 主键值。所以通过二级索引查询时如果想要的字段不在二级索引的叶子节点里就要拿着主键再回聚簇索引查一次这一步叫回表Bookmark Lookup。主键索引在 InnoDB 里天然就是聚簇索引二级索引则对应我们平时给普通字段建的索引。有人会觉得“主键索引不过是一个普通索引”其实不是。聚簇索引决定了整个表的数据在物理磁盘上的排列顺序它不仅仅是加速查询还决定了表的数据组织方式。了解这些之后接下来两部分我们分别深入主键索引和联合索引。顺序上先聊主键因为联合索引的叶子节点里存的就是主键值主键怎么设计会直接影响所有二级索引的体量。2. 主键索引整张表的“骨架”2.1 数据行为什么长在主键索引的叶子上InnoDB 表的数据行并不是独立散落在磁盘上的它们就是按主键顺序存储在聚簇索引的叶子节点里的。你创建一张表并定义主键时InnoDB 会以该主键为聚簇索引来构建整个表结构。如果没有显式主键InnoDB 会找一个非空唯一索引来充当聚簇索引如果也没有它就会生成一个隐藏的 6 字节 rowid 作为聚簇索引。这个机制带来的直接后果是主键的顺序基本决定了数据的物理写入顺序。插入一条记录时InnoDB 会把它放到对应主键位置所在的页上如果页满了就要做页分裂Page Split。后面讲到 UUID 主键的危害时会详细展开。2.2 主键等值查询为什么快当你执行SELECT * FROM user WHERE id 123时InnoDB 的查询流程是从 B 树根节点开始比较 id 与根节点里存储的键值确定应该走哪个子节点逐层下探到达叶子节点后用二分法在当前页内定位到目标记录然后直接返回整行数据。全程不需要回表因为叶子节点已经包含了该行的所有字段。这就是聚簇索引最大的优势按主键查询时一条查询的 IO 次数等于 B 树的高度。对千万级表来说是 3 到 4 次磁盘 IO和逻辑上扫描几十万甚至几百万行相比不是一个数量级。2.3 自增主键真的更好吗我见过不少团队在主键选择上全凭习惯有的用业务编号有的用 UUID有的用雪花 ID。从搜索性能和写入性能两个角度看自增主键通常是最稳妥的选择。自增主键是严格递增的新插入的行总是排在上一条数据的后面追加写入即可不需要频繁移动已有数据。UUID 是随机的字符串插入位置完全随机很容易触发页分裂和页重组产生大量碎片写入性能会明显下滑。还要注意UUID 比 BIGINT 占用的字节多主键越大二级索引叶子节点里存的主键值就越大整个表的索引占用空间就越大内存缓冲池里能缓存的有效索引数据也越少。当然自增主键并不适合所有场景。比如分库分表后需要全局唯一主键时多半会用雪花 ID 这类有序的分布式 ID。但即便如此也要尽量保证生成的 ID 是趋势递增的不要用纯随机 UUID。我自己的习惯是本地单库用自增 BIGINT分布式场景用雪花 ID 或类似方案并尽量把它设计成 BIGINT 类型而不是字符串。2.4 主键长度的隐形影响主键长度这个坑很多面试过 MySQL 的人都知道但实际建表时还是会忽略。主键不仅自己占据聚簇索引还会作为“引用地址”出现在每一个二级索引的叶子节点里。你定义了 5 个二级索引每个二级索引里都会保存主键值。所以主键每多 1 字节所有二级索引都会跟着多 1 字节。假设一张表有 500 万行数据如果用CHAR(32)的 UUID 做主键比用BIGINT做主键在每个二级索引上都要多出大约 (32-8)×500万 ≈ 1.2 亿字节的存储。这不是一个小数字。所以设计主键我有一条原则能用整型不用字符串能用短整型不用长整型但又要留足业务增长空间所以 BIGINT 是最常用的选择。3. 联合索引把多列“拼”成一个键3.1 联合索引在 B 树里怎么排列联合索引也叫复合索引比如在(a, b)两个字段上建立一个索引B 树里并不会存在两套独立的树而是把这俩字段拼接成一个组合键然后按组合键的字典序排序。具体排序规则是先按第一个字段 a 排序如果 a 相同再按 b 排序如果 a 也相同b 也相同再按主键排序。用一个生活化的例子来说一个购物清单先按品牌名排序同一品牌内部再按价格排序。那么你找“所有品牌的单价小于 100 的商品”时因为价格不是第一排序条件你就没法用这个清单快速定位但找“某个品牌的所有商品”或“某个品牌下某个价位的商品”就很方便。这就是联合索引排序规则的直观解释。3.2 最左前缀原则为什么 where b? 用不上索引有了上面的排序规则最左前缀原则就不难理解了。假设有联合索引(a, b)B 树首先保证 a 是有序的只有当 a 相等的时候b 才是有序的。换句话说b 的有序性完全依赖于 a。你单独拿 b 来查询等于在一个“先按 a 排a 内部再按 b 排”的目录里查价格目录里并不是全局按 b 排好的自然没法使用这个索引的查找能力。实践中具体如下表所示查询条件能否用到联合索引 (a, b)原因WHERE a 1能用a 是第一列直接定位WHERE a 1 AND b 2能用先按 a 找再按 b 找WHERE b 2不能有效利用b 不是第一排序键无法定位WHERE a 1 ORDER BY b能用a 确定后 b 有序排序也能用WHERE a BETWEEN 1 AND 3 AND b 2部分能用a 走 rangeb 无法用于定位这里要额外说一句WHERE b 2 AND a 1这种情况因为优化器会重排条件顺序通常也能使用索引。我们说的“最左前缀”指的是索引定义里最左边的列必须出现在查询条件中而不强求 WHERE 条件书写顺序。3.3 联合索引的列顺序等值在前范围靠后设计联合索引时列顺序选择的优先级大概是先看等值条件再看范围条件最后看排序需求同时考虑字段的选择性。选择性是指某个字段去重后的值的分布情况。比如性别字段通常只会有两三个值选择性很低身份证号基本每行都不同选择性很高。联合索引第一列应该尽量放选择性高的字段因为第一列决定了 B 树里分叉的有效性。举一个例子一张用户订单表查询场景是WHERE user_id ? AND status ? ORDER BY create_time DESC。这里 user_id 区分度最高且通常是等值条件放在联合索引第一位status 是等值条件但区分度低放在第二位create_time 是排序字段放在最后一位可以顺便替代 ORDER BY 的 filesort。最终联合索引可以设计为(user_id, status, create_time)。反过来如果把 status 放第一位索引中会有大量 status 相同的记录定位 user_id 时需要在相同 status 范围内再做一次二次筛选效率明显降低。3.4 联合索引如何帮 ORDER BY 省掉文件排序文件排序Filesort是 MySQL 无法利用索引顺序直接返回有序结果时在内存或磁盘上额外做的一次排序操作。数据量少时可能无所谓数据量大时filesort 往往比查询本身还贵。联合索引因为天然按列顺序排序相当于已经排好序了可以省掉这步额外动作。比如索引(user_id, create_time)下执行SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20先通过 user_id 定位到对应叶子节点区间。由于同一 user_id 下 create_time 已经按顺序排列直接反向扫这个有序链表即可。取前 20 条不需要 filesort。如果换成ORDER BY create_time, user_id因为索引排序规则是 user_id 优先而这里查询要求 create_time 优先顺序对不上MySQL 就无法直接利用索引排序只能走 filesort。面试里容易被问的“为什么建立索引后排序还是慢”多半就是这个原因。4. 从原理到实操三个优化案例4.1 案例一UUID 主键引起的写入抖动背景一张日志表日均写入 200 万行前期开发图省事直接用 UUID字符串做主键结果每隔一段时间插入延迟就会飙高。排查时发现两个问题一是随机 UUID 导致数据插入位置随机频繁触发页分裂二是主键太长导致大量磁盘碎片缓冲池被索引数据大量占用。解决将主键改为自增 BIGINTid。原 UUID 字段保留加一个唯一索引uniq_token用于业务上的幂等校验。通过ALTER TABLE ... DROP PRIMARY KEY, ADD PRIMARY KEY(id), ADD UNIQUE KEY uniq_token(token)完成迁移实际生产会在低峰期做在线 DDL 或使用工具。结果写入延迟和抖动明显减少磁盘空间占用也下降了不少。这个案例说明一个看似不起眼的主键选择对写入吞吐量的影响可能比大多数二级索引设计还大。4.2 案例二联合索引让排序查询提速百倍背景一个消息中心表核心查询是SELECT * FROM message WHERE user_id ? ORDER BY send_time DESC LIMIT 20。刚开始只在 user_id 上建了普通索引执行计划里出现Using filesort用户量大时接口响应不稳定。解决将普通索引idx_user_id(user_id)调整为联合索引idx_user_id_send_time(user_id, send_time)。调整后 EXPLAIN 显示 Extra 变成了Using index condition或直接用索引顺序返回数据filesort 消失。为什么因为同一 user_id 下 send_time 已经有序ORDER BY 不需要额外排序。这个优化之所以立竿见影核心逻辑就是前面说的联合索引不仅仅是“多列查得快”它还能把排序需求“吃”进索引里。4.3 案例三覆盖索引避免回表背景一个高频接口需要查询SELECT user_name, avatar FROM user_profile WHERE age ?表数据 2000 万行每次查询都要回表导致很多随机读。解决在原索引基础上加一个覆盖索引(age, user_name, avatar)。二级索引的叶子节点里有 (user_name, avatar, 主键 id)查询的字段都能在索引页里找到回表被省掉。EXPLAIN 中 Extra 显示Using index表示该查询只扫描索引即可返回结果不需要访问聚簇索引。这种方案适合查询字段固定、数据量大的高频业务。但要注意覆盖索引的列不宜过多因为叶子节点里存的列越多索引占空间越大。4.4 索引失效场景速查原理理解了以后很多“索引失效”的问题其实都能推理出来场景原因结果WHERE name LIKE %张%前导通配符导致无法比较大小范围无法使用 name 索引WHERE UPPER(name) ZHANG对索引列用了函数B 树中保存的是原始值无法使用索引WHERE phone 12345678901phone 是 VARCHAR隐式类型转换将列转为数字无法使用索引WHERE age 1 30索引列参与了运算破坏了有序比较无法使用索引WHERE a 1 OR b 2OR 两侧条件不一致优化器难以用单索引并集可能全表扫描WHERE city ? AND age ?索引是 (age, city)city 不是最左列age 需经过 city 筛选后才有序这些场景我列出来并不是为了让你死记硬背而是想说明一个底层逻辑B 树索引本质上依赖有序键值的大小比较任何破坏“列本身作为排序键”的行为比如对它做函数、运算、格式转换都会让索引失去意义。5. 常见问题与排查技巧实录5.1 用 EXPLAIN 判断索引是否真的生效理论聊再多落到现场还得靠工具。MySQL 里排查索引问题最常用的一条命令就是EXPLAIN SELECT ...。重点关注以下几列列名含义常见取值type访问类型ALL、index、range、ref、eq_ref、constkey实际使用的索引NULL 表示没用索引rows预估扫描行数越小越好Extra附加信息Using index、Using filesort、Using temporarytype ALL全表扫描基本可以判定索引没起作用。type ref使用了非唯一索引进行等值匹配比较理想。type range索引范围查询也可以。type const主键或唯一索引等值匹配最快。Extra 里出现Using filesort或Using temporary说明排序或分组没有充分利用索引需要考虑联合索引的表意是否覆盖了排序字段。5.2 优化器选错了索引怎么办虽然 MySQL 的优化器通常很聪明但偶尔也会出现统计数据不准、选错索引的情况。这时可以先尝试ANALYZE TABLE 表名;重新更新索引的基数统计信息。如果还不行可以在 SELECT 语句里使用FORCE INDEX(索引名)强制走某个索引但不要把它当成长期方案更根本的是看索引设计是否合理。我自己遇到过很多次 MySQL 选择了主键而非二级索引的情况明明二级索引选择性更高优化器却因为统计信息偏差选择了更大的扫描范围。遇到这种问题第一步永远是刷新统计信息而不是急着改 SQL。5.3 索引是不是越多越好索引越多写入表时需要更新的索引也就越多。每插入一条记录除了聚簇索引所有二级索引都要同步维护插入更新速度会明显下降。另外索引本身就占磁盘空间也会占用缓冲池内存。所以索引是典型的“空间换时间”不是免费的午餐。我见过有表有十几个索引几乎每个字段都建了一个结果写入性能惨不忍睹。常规建议是单表索引数量一般控制在 5 个以内单条 SQL 涉及的联合索引尽量覆盖到等值、范围、排序三类需求。建索引前先想清楚最核心的几条查询路径而不是把所有字段都铺一遍索引。5.4 我的排障流程总结近期几次线上慢查询排查我的固定套路是拿到慢查询 SQL先看 WHERE、JOIN、ORDER BY 的字段。用EXPLAIN看 type 和 Extra。如果 type 是 ALL检查查询字段是否在某个联合索引的最左列。如果 Extra 有 Using filesort考虑把排序字段纳入联合索引。如果走了索引但 rows 仍然很大检查索引列的选择性。修正 SQL 或索引后对比前后 EXPLAIN 和实际耗时。这套流程并不复杂但每一步都需要索引原理支撑。比如第 3 步如果你不理解最左前缀原则可能根本想不起来去看联合索引的字段顺序第 4 步如果你不理解 B 树的排序特性也不会把 filesort 和索引设计联系起来。结尾最后再分享一个我个人的体会很多人用完 EXPLAIN 看到 type 是 ref 就觉得万事大吉其实忽略了 Extra 里的 Using filesort。用上面的消息中心案例来说最初那条 SQL 其实已经用上了 user_id 的索引type 是 ref但是排序时仍然需要把满足条件的多行数据一股脑拿出来再做排序。等到数据量一上来filesort 瞬间就变成了瓶颈。所以排查慢查询时我总会顺手把排序、分组、去重这些“隐藏需求”一起考虑进索引设计里。这也是这篇文章想强调的核心索引优化不是把 WHERE 字段堆进索引完事而是要理解 B 树是有序的聚簇索引决定数据物理布局联合索引的列顺序决定查询与排序能力。把这些底层逻辑串起来你再去设计索引就不再是背规则而是真正从数据结构出发做决策。 MySQL索引底层原理：主键与联合索引优化实战
这周帮同事排查一条慢查询的时候我发现他的表在联合索引上建错了顺序导致一个原本应该毫秒级返回的查询硬生生跑成了三秒多。这个场景我想大家都不陌生——人人都知道“索引能优化查询”但真正到了设计主键、设计联合索引的时候往往就只剩下“把常用字段放前面”这一句话。至于为什么放前面、放错了会怎样、主键到底应该怎么选很少有人能讲透。今天这篇就来把 MySQL 中主键索引和联合索引的原理掰开揉碎聊一遍你会理解 B 树到底长什么样、聚簇索引和非聚簇索引有什么区别、联合索引为什么要求最左前缀以及如何用 EXPLAIN 判断自己的索引设计是否合理。内容不绕弯子尽量用大白话还原底层逻辑适合所有写过 MySQL 的开发者也适合准备面试时临时抱佛脚的读者。1. 索引的底层世界观一张按顺序排好的目录1.1 没有索引时MySQL 是怎么找数据的MySQL 的数据存储在磁盘上InnoDB 引擎把数据组织成一个个 16KB 的页Page页之间通过指针串联。没有索引时你要查一行数据数据库只能从第一个页开始一个页一个页读下去每一行都做比较直到找到匹配的记录。这条路径叫全表扫描Full Table Scan。表只有几百行时感觉不到问题但表到了千万行每页假设存几百行一次查询可能先后读上万个页磁盘 IO 的耗时自然就上去了。磁盘 IO 和内存访问不一样内存随机读也许只要几十纳秒而磁盘一次随机读大概要几毫秒差了好几个数量级。所以“减少 IO 次数”就成了索引设计的核心目标。索引本质上就是把数据的关键信息单独提取出来做成一张有序的目录查询时先翻目录再按目录上的地址去取数据这样就不用把整本书从头翻到尾了。1.2 B 树MySQL 默认的索引结构MySQL 的 InnoDB 引擎默认使用 B 树来组织索引。B 树是一种平衡多路查找树它的特点是非叶子节点只保存索引键和指向子节点的指针不保存实际数据真正的数据或主键值都保存在叶子节点上并且所有叶子节点通过双向链表串在一起。这句话有很多人听过但你要理解它为什么这么设计。一个 16KB 的页能容纳很多索引键假设每个索引键加上指针占 20 字节那么一个非叶子节点就能存大约 800 个键。三层 B 树就能存下 800×800×800也就是五亿多条记录。换句话说一个几千万行的大表你从根节点出发最多只需要三次磁盘 IO 就能定位到叶子节点。第一层读根节点第二层读中间节点第三层读叶子节点。这是 B 树相对于 B 树和二叉树最大的优势树矮、层数少、IO 少。叶子节点用双向链表相连又让范围查询变得非常顺畅。比如你要查 id 从 100 到 200 的所有记录B 树只需要先定位到 100 所在的叶子节点然后顺着链表往后读就行不需要一层一层重新查找。1.3 聚簇索引和非聚簇索引到底差在哪这里需要引入两个概念聚簇索引Clustered Index和二级索引Secondary Index也叫辅助索引。聚簇索引的叶子节点存的是整行数据。二级索引的叶子节点存的是索引键 主键值。所以通过二级索引查询时如果想要的字段不在二级索引的叶子节点里就要拿着主键再回聚簇索引查一次这一步叫回表Bookmark Lookup。主键索引在 InnoDB 里天然就是聚簇索引二级索引则对应我们平时给普通字段建的索引。有人会觉得“主键索引不过是一个普通索引”其实不是。聚簇索引决定了整个表的数据在物理磁盘上的排列顺序它不仅仅是加速查询还决定了表的数据组织方式。了解这些之后接下来两部分我们分别深入主键索引和联合索引。顺序上先聊主键因为联合索引的叶子节点里存的就是主键值主键怎么设计会直接影响所有二级索引的体量。2. 主键索引整张表的“骨架”2.1 数据行为什么长在主键索引的叶子上InnoDB 表的数据行并不是独立散落在磁盘上的它们就是按主键顺序存储在聚簇索引的叶子节点里的。你创建一张表并定义主键时InnoDB 会以该主键为聚簇索引来构建整个表结构。如果没有显式主键InnoDB 会找一个非空唯一索引来充当聚簇索引如果也没有它就会生成一个隐藏的 6 字节 rowid 作为聚簇索引。这个机制带来的直接后果是主键的顺序基本决定了数据的物理写入顺序。插入一条记录时InnoDB 会把它放到对应主键位置所在的页上如果页满了就要做页分裂Page Split。后面讲到 UUID 主键的危害时会详细展开。2.2 主键等值查询为什么快当你执行SELECT * FROM user WHERE id 123时InnoDB 的查询流程是从 B 树根节点开始比较 id 与根节点里存储的键值确定应该走哪个子节点逐层下探到达叶子节点后用二分法在当前页内定位到目标记录然后直接返回整行数据。全程不需要回表因为叶子节点已经包含了该行的所有字段。这就是聚簇索引最大的优势按主键查询时一条查询的 IO 次数等于 B 树的高度。对千万级表来说是 3 到 4 次磁盘 IO和逻辑上扫描几十万甚至几百万行相比不是一个数量级。2.3 自增主键真的更好吗我见过不少团队在主键选择上全凭习惯有的用业务编号有的用 UUID有的用雪花 ID。从搜索性能和写入性能两个角度看自增主键通常是最稳妥的选择。自增主键是严格递增的新插入的行总是排在上一条数据的后面追加写入即可不需要频繁移动已有数据。UUID 是随机的字符串插入位置完全随机很容易触发页分裂和页重组产生大量碎片写入性能会明显下滑。还要注意UUID 比 BIGINT 占用的字节多主键越大二级索引叶子节点里存的主键值就越大整个表的索引占用空间就越大内存缓冲池里能缓存的有效索引数据也越少。当然自增主键并不适合所有场景。比如分库分表后需要全局唯一主键时多半会用雪花 ID 这类有序的分布式 ID。但即便如此也要尽量保证生成的 ID 是趋势递增的不要用纯随机 UUID。我自己的习惯是本地单库用自增 BIGINT分布式场景用雪花 ID 或类似方案并尽量把它设计成 BIGINT 类型而不是字符串。2.4 主键长度的隐形影响主键长度这个坑很多面试过 MySQL 的人都知道但实际建表时还是会忽略。主键不仅自己占据聚簇索引还会作为“引用地址”出现在每一个二级索引的叶子节点里。你定义了 5 个二级索引每个二级索引里都会保存主键值。所以主键每多 1 字节所有二级索引都会跟着多 1 字节。假设一张表有 500 万行数据如果用CHAR(32)的 UUID 做主键比用BIGINT做主键在每个二级索引上都要多出大约 (32-8)×500万 ≈ 1.2 亿字节的存储。这不是一个小数字。所以设计主键我有一条原则能用整型不用字符串能用短整型不用长整型但又要留足业务增长空间所以 BIGINT 是最常用的选择。3. 联合索引把多列“拼”成一个键3.1 联合索引在 B 树里怎么排列联合索引也叫复合索引比如在(a, b)两个字段上建立一个索引B 树里并不会存在两套独立的树而是把这俩字段拼接成一个组合键然后按组合键的字典序排序。具体排序规则是先按第一个字段 a 排序如果 a 相同再按 b 排序如果 a 也相同b 也相同再按主键排序。用一个生活化的例子来说一个购物清单先按品牌名排序同一品牌内部再按价格排序。那么你找“所有品牌的单价小于 100 的商品”时因为价格不是第一排序条件你就没法用这个清单快速定位但找“某个品牌的所有商品”或“某个品牌下某个价位的商品”就很方便。这就是联合索引排序规则的直观解释。3.2 最左前缀原则为什么 where b? 用不上索引有了上面的排序规则最左前缀原则就不难理解了。假设有联合索引(a, b)B 树首先保证 a 是有序的只有当 a 相等的时候b 才是有序的。换句话说b 的有序性完全依赖于 a。你单独拿 b 来查询等于在一个“先按 a 排a 内部再按 b 排”的目录里查价格目录里并不是全局按 b 排好的自然没法使用这个索引的查找能力。实践中具体如下表所示查询条件能否用到联合索引 (a, b)原因WHERE a 1能用a 是第一列直接定位WHERE a 1 AND b 2能用先按 a 找再按 b 找WHERE b 2不能有效利用b 不是第一排序键无法定位WHERE a 1 ORDER BY b能用a 确定后 b 有序排序也能用WHERE a BETWEEN 1 AND 3 AND b 2部分能用a 走 rangeb 无法用于定位这里要额外说一句WHERE b 2 AND a 1这种情况因为优化器会重排条件顺序通常也能使用索引。我们说的“最左前缀”指的是索引定义里最左边的列必须出现在查询条件中而不强求 WHERE 条件书写顺序。3.3 联合索引的列顺序等值在前范围靠后设计联合索引时列顺序选择的优先级大概是先看等值条件再看范围条件最后看排序需求同时考虑字段的选择性。选择性是指某个字段去重后的值的分布情况。比如性别字段通常只会有两三个值选择性很低身份证号基本每行都不同选择性很高。联合索引第一列应该尽量放选择性高的字段因为第一列决定了 B 树里分叉的有效性。举一个例子一张用户订单表查询场景是WHERE user_id ? AND status ? ORDER BY create_time DESC。这里 user_id 区分度最高且通常是等值条件放在联合索引第一位status 是等值条件但区分度低放在第二位create_time 是排序字段放在最后一位可以顺便替代 ORDER BY 的 filesort。最终联合索引可以设计为(user_id, status, create_time)。反过来如果把 status 放第一位索引中会有大量 status 相同的记录定位 user_id 时需要在相同 status 范围内再做一次二次筛选效率明显降低。3.4 联合索引如何帮 ORDER BY 省掉文件排序文件排序Filesort是 MySQL 无法利用索引顺序直接返回有序结果时在内存或磁盘上额外做的一次排序操作。数据量少时可能无所谓数据量大时filesort 往往比查询本身还贵。联合索引因为天然按列顺序排序相当于已经排好序了可以省掉这步额外动作。比如索引(user_id, create_time)下执行SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20先通过 user_id 定位到对应叶子节点区间。由于同一 user_id 下 create_time 已经按顺序排列直接反向扫这个有序链表即可。取前 20 条不需要 filesort。如果换成ORDER BY create_time, user_id因为索引排序规则是 user_id 优先而这里查询要求 create_time 优先顺序对不上MySQL 就无法直接利用索引排序只能走 filesort。面试里容易被问的“为什么建立索引后排序还是慢”多半就是这个原因。4. 从原理到实操三个优化案例4.1 案例一UUID 主键引起的写入抖动背景一张日志表日均写入 200 万行前期开发图省事直接用 UUID字符串做主键结果每隔一段时间插入延迟就会飙高。排查时发现两个问题一是随机 UUID 导致数据插入位置随机频繁触发页分裂二是主键太长导致大量磁盘碎片缓冲池被索引数据大量占用。解决将主键改为自增 BIGINTid。原 UUID 字段保留加一个唯一索引uniq_token用于业务上的幂等校验。通过ALTER TABLE ... DROP PRIMARY KEY, ADD PRIMARY KEY(id), ADD UNIQUE KEY uniq_token(token)完成迁移实际生产会在低峰期做在线 DDL 或使用工具。结果写入延迟和抖动明显减少磁盘空间占用也下降了不少。这个案例说明一个看似不起眼的主键选择对写入吞吐量的影响可能比大多数二级索引设计还大。4.2 案例二联合索引让排序查询提速百倍背景一个消息中心表核心查询是SELECT * FROM message WHERE user_id ? ORDER BY send_time DESC LIMIT 20。刚开始只在 user_id 上建了普通索引执行计划里出现Using filesort用户量大时接口响应不稳定。解决将普通索引idx_user_id(user_id)调整为联合索引idx_user_id_send_time(user_id, send_time)。调整后 EXPLAIN 显示 Extra 变成了Using index condition或直接用索引顺序返回数据filesort 消失。为什么因为同一 user_id 下 send_time 已经有序ORDER BY 不需要额外排序。这个优化之所以立竿见影核心逻辑就是前面说的联合索引不仅仅是“多列查得快”它还能把排序需求“吃”进索引里。4.3 案例三覆盖索引避免回表背景一个高频接口需要查询SELECT user_name, avatar FROM user_profile WHERE age ?表数据 2000 万行每次查询都要回表导致很多随机读。解决在原索引基础上加一个覆盖索引(age, user_name, avatar)。二级索引的叶子节点里有 (user_name, avatar, 主键 id)查询的字段都能在索引页里找到回表被省掉。EXPLAIN 中 Extra 显示Using index表示该查询只扫描索引即可返回结果不需要访问聚簇索引。这种方案适合查询字段固定、数据量大的高频业务。但要注意覆盖索引的列不宜过多因为叶子节点里存的列越多索引占空间越大。4.4 索引失效场景速查原理理解了以后很多“索引失效”的问题其实都能推理出来场景原因结果WHERE name LIKE %张%前导通配符导致无法比较大小范围无法使用 name 索引WHERE UPPER(name) ZHANG对索引列用了函数B 树中保存的是原始值无法使用索引WHERE phone 12345678901phone 是 VARCHAR隐式类型转换将列转为数字无法使用索引WHERE age 1 30索引列参与了运算破坏了有序比较无法使用索引WHERE a 1 OR b 2OR 两侧条件不一致优化器难以用单索引并集可能全表扫描WHERE city ? AND age ?索引是 (age, city)city 不是最左列age 需经过 city 筛选后才有序这些场景我列出来并不是为了让你死记硬背而是想说明一个底层逻辑B 树索引本质上依赖有序键值的大小比较任何破坏“列本身作为排序键”的行为比如对它做函数、运算、格式转换都会让索引失去意义。5. 常见问题与排查技巧实录5.1 用 EXPLAIN 判断索引是否真的生效理论聊再多落到现场还得靠工具。MySQL 里排查索引问题最常用的一条命令就是EXPLAIN SELECT ...。重点关注以下几列列名含义常见取值type访问类型ALL、index、range、ref、eq_ref、constkey实际使用的索引NULL 表示没用索引rows预估扫描行数越小越好Extra附加信息Using index、Using filesort、Using temporarytype ALL全表扫描基本可以判定索引没起作用。type ref使用了非唯一索引进行等值匹配比较理想。type range索引范围查询也可以。type const主键或唯一索引等值匹配最快。Extra 里出现Using filesort或Using temporary说明排序或分组没有充分利用索引需要考虑联合索引的表意是否覆盖了排序字段。5.2 优化器选错了索引怎么办虽然 MySQL 的优化器通常很聪明但偶尔也会出现统计数据不准、选错索引的情况。这时可以先尝试ANALYZE TABLE 表名;重新更新索引的基数统计信息。如果还不行可以在 SELECT 语句里使用FORCE INDEX(索引名)强制走某个索引但不要把它当成长期方案更根本的是看索引设计是否合理。我自己遇到过很多次 MySQL 选择了主键而非二级索引的情况明明二级索引选择性更高优化器却因为统计信息偏差选择了更大的扫描范围。遇到这种问题第一步永远是刷新统计信息而不是急着改 SQL。5.3 索引是不是越多越好索引越多写入表时需要更新的索引也就越多。每插入一条记录除了聚簇索引所有二级索引都要同步维护插入更新速度会明显下降。另外索引本身就占磁盘空间也会占用缓冲池内存。所以索引是典型的“空间换时间”不是免费的午餐。我见过有表有十几个索引几乎每个字段都建了一个结果写入性能惨不忍睹。常规建议是单表索引数量一般控制在 5 个以内单条 SQL 涉及的联合索引尽量覆盖到等值、范围、排序三类需求。建索引前先想清楚最核心的几条查询路径而不是把所有字段都铺一遍索引。5.4 我的排障流程总结近期几次线上慢查询排查我的固定套路是拿到慢查询 SQL先看 WHERE、JOIN、ORDER BY 的字段。用EXPLAIN看 type 和 Extra。如果 type 是 ALL检查查询字段是否在某个联合索引的最左列。如果 Extra 有 Using filesort考虑把排序字段纳入联合索引。如果走了索引但 rows 仍然很大检查索引列的选择性。修正 SQL 或索引后对比前后 EXPLAIN 和实际耗时。这套流程并不复杂但每一步都需要索引原理支撑。比如第 3 步如果你不理解最左前缀原则可能根本想不起来去看联合索引的字段顺序第 4 步如果你不理解 B 树的排序特性也不会把 filesort 和索引设计联系起来。结尾最后再分享一个我个人的体会很多人用完 EXPLAIN 看到 type 是 ref 就觉得万事大吉其实忽略了 Extra 里的 Using filesort。用上面的消息中心案例来说最初那条 SQL 其实已经用上了 user_id 的索引type 是 ref但是排序时仍然需要把满足条件的多行数据一股脑拿出来再做排序。等到数据量一上来filesort 瞬间就变成了瓶颈。所以排查慢查询时我总会顺手把排序、分组、去重这些“隐藏需求”一起考虑进索引设计里。这也是这篇文章想强调的核心索引优化不是把 WHERE 字段堆进索引完事而是要理解 B 树是有序的聚簇索引决定数据物理布局联合索引的列顺序决定查询与排序能力。把这些底层逻辑串起来你再去设计索引就不再是背规则而是真正从数据结构出发做决策。