聊一个后端同学几乎每天都会碰到的经典问题MySQL 里的主键索引和联合索引在底层到底是怎么工作的以前排查慢查询时我见过有人给表建了一堆索引结果 EXPLAIN 出来还是全表扫描根子就在于对这两个数据结构只停留在“会用”的层面没有真正理解它们背后的物理存储和查找逻辑。这篇分享不聊虚的直接从 InnoDB 的 B 树讲起把主键索引和联合索引的工作原理拆开来看主键索引为什么能把查询做到“一次定位”联合索引那棵 B 树是怎么存储多列的“最左前缀”为什么是联合索引的灵魂还有回表、覆盖索引、索引下推这些绕不开的概念。不管你是刚入行的新人还是写了好几年 SQL 的“老手”这篇文章都值得花十分钟读一遍弄清楚这些底层逻辑之后建索引和排查慢查询的思路会清晰很多。1. 主键索引和联合索引到底解决了什么问题1.1 没有索引的查询全表扫描是怎么发生的我们要先明确一个前提MySQL 默认的 InnoDB 引擎不会“凭空知道”某一行数据在哪里。当你执行SELECT * FROM user WHERE id 100时如果表上没有索引InnoDB 只能从表的第一条数据开始逐条读出数据页然后判断 id 是否等于 100。这就是全表扫描。全表扫描的问题在于它的时间复杂度是 O(n)n 是表的行数。当表只有几百行时无所谓但表一旦膨胀到几百万、几千万行每一次查询都要把全部数据从磁盘读到内存里过滤一遍IO 开销会直接把数据库压垮。我在实际业务中见过一个很有趣的例子一张订单明细表有 800 万行因为漏建了索引一个简单的按用户 ID 查询居然要跑 3 秒多这就是典型全表扫描导致的性能问题。所以索引存在的第一层意义很朴素把“逐行翻一遍”变成“直接翻到目标所在的位置”。这就好比你去图书馆找一本书没有目录时只能一排排书架上找有了目录和索引导航你能直接知道书在哪个区、哪一行。1.2 索引的本质空间换时间用“排序跳转”换速度索引本质上是一份经过排序的“副本数据”它不存储整行的全部字段而是存储“索引列的值 指向真实数据行的位置信息”。有了排序索引就能用类似于二分查找的方式快速缩小范围。但这里有个关键点MySQL 里的索引并不是简单的二分查找树而是B 树。为什么不用二叉树、红黑树或哈希表一个直接原因是磁盘 IO 的代价。数据库的数据量远大于内存索引也存放在磁盘上。二叉树每个节点只存一个关键字树的高度随数据量增长很快查询一次可能需要跳转很多次磁盘哈希表虽然单点查询快到 O(1)但它天生不支持范围查询和排序。而 B 树的每个节点可以存放多个关键字同时所有叶子节点通过指针串成一个有序链表既能把树高度控制得很低又能很好地支持范围查找比如WHERE id BETWEEN 100 AND 200。这个选择直接决定了后续所有索引机制所以我们后面聊的主键索引、联合索引、回表全都是建立在 B 树这个基础之上的。2. InnoDB 的 B 树与聚簇索引2.1 InnoDB 数据文件长什么样在 InnoDB 里一张表的数据并不是“一行行平铺”在这个文件里的而是按页Page为单位组织的。页是磁盘与内存交互的最小单位默认大小通常是 16KB。每个数据页里存放着若干行记录页与页之间通过链表连接。当你在表上创建了主键InnoDB 会立刻生成一棵 B 树这棵树的叶子节点存放的是整行数据。也就是说数据本身就是按主键的顺序在页里排列的。这种“按主键组织整张表”的索引结构专业说法叫聚簇索引Clustered Index。注意聚簇索引不是一种可选的索引类型而是 InnoDB 存储表的默认方式。这里有一个生活化的类比聚簇索引就像一本按拼音排序的字典正文字本身就是按拼音顺序编排的“查一个字的读音”只需要按拼音定位找到那一页就能看到整个字的解释不需要再去翻别的地方。2.2 主键索引就是聚簇索引很多人会把“主键索引”和“聚簇索引”当成两个概念来回切换其实在 InnoDB 中主键索引就是聚簇索引本身。它的叶子节点存的是完整的一行记录而每个索引叶子节点中存储的键值就是主键值。这意味着两件事通过主键查询时只需要从 B 树根节点开始沿着分支向下走到对应的叶子节点就能拿到完整的数据行整个过程不需要额外的“回表”动作。因为表数据只能按一种物理顺序存储所以聚簇索引在一张表里只能有一个。其他索引的叶子节点不存整行数据而是存“主键值 索引列的值”这种索引就是二级索引或普通索引。2.3 如果表没有显式主键InnoDB 怎么办很多人在建表时忽略主键设计觉得随便建个表能跑就行。这里要提醒一下InnoDB 如果没有找到主键它不会让表变成“无索引”状态而是会自己想办法。具体规则是先找有没有非空的唯一索引如果有就把它当作聚簇索引如果也没有就在后台生成一个隐藏的 6 字节 row_id 作为聚簇索引键整个表按这个隐藏 row_id 排序存储。这个隐藏 row_id 对用户是不可见的所以如果你建表时既没主键也没合适的唯一键后续无法通过某个业务列高效定位数据全表扫描会频繁发生。而且隐藏键对运维排查很不友好因为你在数据字典里看不到它。我的习惯是每张业务表都必须显式指定主键并且尽量用一个与业务无关的自增整数或有序 ID而不是用很长的字符串。3. 主键索引的底层工作过程3.1 一次主键查询走了什么路径假设我们有这样一张用户表CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, nickname varchar(32) DEFAULT NULL, email varchar(64) DEFAULT NULL, mobile varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;然后执行SELECT * FROM user WHERE id 12345;这条 SQL 的查询路径大概是这样的MySQL 会先从 B 树的根节点开始把根节点页读入内存根据 id12345 与节点中的关键值比较决定走哪个子节点。假设这棵 B 树的高度是 3那么需要读取 3 个页最后在叶子节点中找到 id12345 对应的完整行。这里要注意B 树的高度通常很低。几百万行数据的表树高也就 3 到 4 层。也就是说通过主键查询一般只需要 3 到 4 次磁盘 IO 就能拿到数据。相比全表扫描这个查询效率非常惊人。3.2 自增主键 vs UUID 主键怎么选既然主键决定了数据行的物理存储顺序主键的生成方式就变得很重要。最常见的是自增整数主键和 UUID 主键。自增主键的优点是插入时新数据总是追加在 B 树的最右边不需要频繁地挪动已有数据页缺点是分布式场景下扩展会麻烦一些。UUID 主键虽然能全局唯一但它是完全随机的插入时往往要插在 B 树的中间位置导致页分裂频率明显提高写入性能和索引碎片问题都会更严重。在实际项目中我倾向于把主键设计成“业务无关、趋势递增”的数字。对于分布式场景可以换成雪花 ID 之类的有序 ID而不是直接用 32 位随机字符串。一张表主键越长二级索引占用的空间也越大这一点常常被忽略。3.3 聚簇索引下的页分裂与碎片聊到自增主键就不得不提页分裂。当你往 B 树里插入数据时如果某个叶子节点已经写满了 16KB再插入新的记录InnoDB 就要申请一个新的页并把原有节点中大约一半的数据搬过去。这个过程叫页分裂。页分裂本身不是坏事它保证了节点不会过于膨胀。但如果频繁发生会造成很多物理上不连续的碎片页索引扫描效率会下降。所以对于写多读少的表我一般会关注表的碎片率定期可以考虑用OPTIMIZE TABLE做一次整理。4. 联合索引的核心一棵 B 树如何存多列4.1 联合索引其实还是一棵 B 树联合索引也叫复合索引是在多个列上建立的一个索引。它在 InnoDB 里同样是一棵 B 树只不过这棵树的每个节点不是只保存一个列的值而是保存多个列的值。比如我们在 (nickname, email) 上建联合索引索引节点里的键就是由 nickname 和 email 拼接而成。这里要特别注意拼接的顺序先按第一个字段 nickname 排序如果 nickname 相同再按第二个字段 email 排序。可以这样理解联合索引在内部先把所有数据按照定义好的列顺序排好序然后这个排序结果里第一列是绝对有序的第二列只有在第一列相同的情况下才局部有序。4.2 最左前缀原则是怎么来的因为联合索引的排序规则是先按第一列排再按第二列排这就决定了只有在查询条件中包含了最左边的列时MySQL 才能高效利用这个索引。举个例子联合索引是(a, b, c)下面这些查询条件能否走索引WHERE a 1 AND b 2 AND c 3完全能走因为 a 是索引第一列索引顺序完全匹配WHERE a 1 AND c 3能走但只充分使用 a 这个字段去缩小范围c 条件无法在索引里直接定位只能作为“索引内的过滤条件”WHERE b 2 AND c 3完全没法用这个索引来定位因为 b 不是最左列整棵 B 树的排序跟 b 无关。这就是所谓的最左前缀原则Leftmost Prefix。它不是 MySQL 故意设置的限制而是由联合索引的底层排序逻辑自然决定的。索引节点里第一个列的取值决定了整个索引的层级走向如果查询条件里没有第一列就无法利用这棵树的排序结构。4.3 为什么“跳过第一列”会导致索引失效底层逻辑是什么刚开始接触最左前缀时很多人会困惑为什么联合索引是(a, b)我用 b 来查就不行呢我们用生活中的例子类比通讯录里通常先按姓排序再按名排序。如果让你找“张三”你可以很快定位到张姓区域再找名但如果让你直接找所有叫“三”的人你只能翻遍整个通讯录因为名没有全量排序。联合索引的 B 树也是同理它按 a 排好a 是全局有序b 只是在同一个 a 值内部有序。所以只查 b 等于某个值时整个 B 树对 b 的「全局有序性」是不成立的无法利用树的剪枝能力快速跳过范围。4.4 联合索引列顺序的设计策略既然列顺序这么重要那到底应该把哪个列放在联合索引最前面我的经验是看两个维度查询频率最常用、最常出现在 WHERE 等值条件里的列优先放前面。区分度区分度越高的列越适合放前面比如手机号比性别更适合放在首列。因为区分度高意味着相同值的行数少索引能更精确地缩小范围。但这两个维度有时会冲突。比如“状态”列可能频繁出现在 WHERE 里但区分度很低而“订单号”区分度很高却不常查。这种情况下我会先满足查询频率因为一个索引如果最左列根本不在业务查询里出现那这个联合索引就形同虚设连定位能力都没有。区分度的影响可以在满足最左列之后通过后续列的顺序来优化。5. 回表、覆盖索引与索引下推5.1 二级索引的完整查询流程联合索引本质是二级索引它的叶子节点不存整行数据而是存“索引列的值 主键值”。当我们用联合索引查找时过程分两步先到联合索引这棵 B 树中找到符合条件的记录拿到主键值再拿主键值去聚簇索引主键索引里查一次拿到完整的那一行。第二步非常关键它有个专门的名字叫回表Bookmark Lookup。为了演示创建一个订单查询场景CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(32) NOT NULL, amount decimal(10,2) DEFAULT NULL, status tinyint DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_status (user_id, status) ) ENGINEInnoDB;执行SELECT * FROM order_info WHERE user_id 888 AND status 1;这条 SQL 会先走idx_user_status这棵 B 树找到满足user_id888 AND status1的叶子节点取出里面的主键 id。接下来再用这若干主键 id 回到主键索引的 B 树里查完整数据。如果符合条件的行有 10000 条理论上就要回表 10000 次而每次回表都是一次主键树的搜索流程。当数据量很大时这种额外的 IO 开销不容小觑。5.2 覆盖索引让查询不需要回表回表是二级索引使用的代价怎么避免最直接的办法是让查询所需要的列全部包含在二级索引的叶子节点里。因为二级索引叶子节点里已经有“索引列 主键”如果查询的字段恰好只有这些MySQL 在执行时可以直接从索引里取不需要再回到主键索引。比如上面的例子改成SELECT user_id, status FROM order_info WHERE user_id 888 AND status 1;这个查询要的字段只有 user_id 和 status它们都在idx_user_status里叶子节点中存在一份。MySQL 可以直接从联合索引的 B 树中读取并返回不需要回表。这种“查询列全部能在索引中找到”的情况就叫覆盖索引Covering Index。实际优化中覆盖索引的使用场景非常多。有一次排查线上慢 SQL某列表页只展示用户 ID、订单号、状态三个字段原查询是SELECT *导致大量回表。把它改成只查这三个字段并且建了 (user_id,status) 联合索引后查询时间直接从 200 多毫秒降到了十几毫秒。这里面的核心收益就是覆盖索引减少了回表 IO。5.3 索引下推ICP是什么回表次数能不能进一步减少MySQL 5.6 以后引入了索引下推Index Condition PushdownICP优化。简单说就是当联合索引包含多列时MySQL 会把一部分 WHERE 条件“下推”到存储引擎层在遍历索引节点时就判断这些条件而不是先把所有满足最左前缀的记录都拉回服务器层再筛选。看一个具体例子。索引(user_id, status)查询条件SELECT * FROM order_info WHERE user_id 888 AND status 1;如果没有索引下推存储引擎拿到所有user_id888的记录后会把每条记录都交给 Server 层由 Server 层判断 status 是否等于 1。如果user_id888有 10000 条记录这 10000 条都要经过主键回表并传到 Server。而有索引下推时存储引擎在遍历联合索引的叶子节点时会先判断 status 是否为 1只有满足条件的记录才回表。这样回表次数大大减少性能提升非常明显。这也是为什么一些 WHERE 条件虽然没有完全遵循最左前缀但依然可以利用索引做部分过滤的原因。6. 索引失效场景与 EXPLAIN 实测排查6.1 范围查询对联合索引的影响联合索引设计中最容易踩坑的就是范围查询。假设索引是(a, b, c)执行SELECT * FROM t WHERE a 1 AND b 10 AND c 3;a 和 b 可以走索引但 c 很难直接走索引排序定位因为 b 是一个范围值在 b 10 的范围内b 已经不是一个精确条件c 的排序状态在这个范围内是不保证全局有序的。所以理论上这时候联合索引只能用到(a, b)两列。这也是一个常见优化思路遇到范围查询优先把范围查询列放在联合索引靠后的位置把等值查询列放在前面。尽可能让索引中的前缀列都是等值匹配后面的范围列可以用索引减少回表但再后面的列往往发挥不了索引定位作用。6.2 函数操作、隐式类型转换索引为何失效B 树的索引是建立在原始列值上的。如果你在索引列上使用函数或者让列与不同类型的值比较MySQL 没法直接使用这个列的原始排序结构。例如SELECT * FROM user WHERE DATE(create_time) 2025-01-01;即使 create_time 有索引DATE() 函数把值转换过了索引顺序对转换后的结果并不连续MySQL 只能全表扫描。另外如果某个字段是字符串类型查询时用了整型数字比如WHERE mobile 13800138000MySQL 虽然会尝试隐式转换但转换可能让索引无法高效利用。我的排查习惯是检查索引字段是否有类型转换如果有优先改成字段本身与查询值的类型一致或在应用层处理好类型再传入。6.3 用 EXPLAIN 判断是否真的走了索引排查 SQL 是否走对索引最直接的工具是EXPLAIN。比如执行EXPLAIN SELECT * FROM order_info WHERE user_id 888 AND status 1;输出中关键的几个字段要理解type表示访问类型从好到差常见有const、eq_ref、ref、range、index、ALL。ALL表示全表扫描这是最需要警惕的。key实际使用的索引名。rows预估扫描的行数越小越好。ExtraUsing index表示覆盖索引生效Using index condition表示用到了索引下推Using where表示存储引擎返回后 Server 层还要过滤。我在实际优化中最常用的判断标准先看 key再看 Extra。如果 key 是 NULL 或者 type 是 ALL大概率索引没有用上这时候就要倒推 SQL 条件是否满足最左前缀以及是否有隐式类型转换。6.4 常见索引失效场景速查下面是一个简化版速查表能帮你快速定位大部分索引没走上的原因场景失效原因解决思路WHERE 中不用联合索引首列不满足最左前缀调整查询条件或重建索引顺序对索引列使用函数B 树排序被破坏避免在列上套函数改为范围查询索引列隐式类型转换原始值与查询值无法直接比较使用与字段类型一致的参数LIKE 以通配符开头不满足前缀匹配改前缀匹配或使用全文索引范围查询后面的列索引内局部有序被打破把范围列放到索引靠后位置OR 条件包含非索引列优化器可能放弃索引拆分 SQL 或重建复合索引6.5 实际操作中的一条优化流程我整理了一份自己常用的优化清单遇到慢查询时按这个思路走基本不会跑偏先用慢查询日志定位出具体 SQL看 EXPLAIN确认 type、key、rows、Extra分析 WHERE 条件是否满足最左前缀如果不满足考虑调整查询或重建联合索引的顺序看 SELECT 的字段是否过多尝试把查询字段缩到索引覆盖范围内提升覆盖索引命中率检查排序字段是否与索引顺序一致能利用索引排序就不要让 MySQL 在内存里再排一次上线前用大数据量表做一次压测避免在小数据量下看着走索引上线后却走了全表扫描。把这套流程跑熟之后你会发现大部分慢查询其实都不是 MySQL“不行”而是建索引的人没有把联合索引的物理排序逻辑想清楚。最后分享一个我个人的实际体会建索引时不要贪多每一张表上的联合索引都要结合真实业务查询来设计。每次新增一个索引之前先问自己两个问题——这个索引的首列是否频繁出现在业务 WHERE 条件里这个索引是否能让高频查询覆盖索引如果答案是否定的这个索引大概率就是冗余的。以前我维护某套系统时线上表建了十几个普通索引后来花了两天时间梳理业务查询删掉了一半冗余索引不仅查询没变慢写入性能还好了不少磁盘占用也小了很多。把主键索引和联合索引放在 B 树和聚簇索引的框架里去理解很多之前觉得玄乎的优化技巧其实都顺理成章了。 聊一个后端同学几乎每天都会碰到的经典问题MySQL 里的主键索引和联合索引在底层到底是怎么工作的以前排查慢查询时我见过有人给表建了一堆索引结果 EXPLAIN 出来还是全表扫描根子就在于对这两个数据结构只停留在“会用”的层面没有真正理解它们背后的物理存储和查找逻辑。这篇分享不聊虚的直接从 InnoDB 的 B 树讲起把主键索引和联合索引的工作原理拆开来看主键索引为什么能把查询做到“一次定位”联合索引那棵 B 树是怎么存储多列的“最左前缀”为什么是联合索引的灵魂还有回表、覆盖索引、索引下推这些绕不开的概念。不管你是刚入行的新人还是写了好几年 SQL 的“老手”这篇文章都值得花十分钟读一遍弄清楚这些底层逻辑之后建索引和排查慢查询的思路会清晰很多。1. 主键索引和联合索引到底解决了什么问题1.1 没有索引的查询全表扫描是怎么发生的我们要先明确一个前提MySQL 默认的 InnoDB 引擎不会“凭空知道”某一行数据在哪里。当你执行SELECT * FROM user WHERE id 100时如果表上没有索引InnoDB 只能从表的第一条数据开始逐条读出数据页然后判断 id 是否等于 100。这就是全表扫描。全表扫描的问题在于它的时间复杂度是 O(n)n 是表的行数。当表只有几百行时无所谓但表一旦膨胀到几百万、几千万行每一次查询都要把全部数据从磁盘读到内存里过滤一遍IO 开销会直接把数据库压垮。我在实际业务中见过一个很有趣的例子一张订单明细表有 800 万行因为漏建了索引一个简单的按用户 ID 查询居然要跑 3 秒多这就是典型全表扫描导致的性能问题。所以索引存在的第一层意义很朴素把“逐行翻一遍”变成“直接翻到目标所在的位置”。这就好比你去图书馆找一本书没有目录时只能一排排书架上找有了目录和索引导航你能直接知道书在哪个区、哪一行。1.2 索引的本质空间换时间用“排序跳转”换速度索引本质上是一份经过排序的“副本数据”它不存储整行的全部字段而是存储“索引列的值 指向真实数据行的位置信息”。有了排序索引就能用类似于二分查找的方式快速缩小范围。但这里有个关键点MySQL 里的索引并不是简单的二分查找树而是B 树。为什么不用二叉树、红黑树或哈希表一个直接原因是磁盘 IO 的代价。数据库的数据量远大于内存索引也存放在磁盘上。二叉树每个节点只存一个关键字树的高度随数据量增长很快查询一次可能需要跳转很多次磁盘哈希表虽然单点查询快到 O(1)但它天生不支持范围查询和排序。而 B 树的每个节点可以存放多个关键字同时所有叶子节点通过指针串成一个有序链表既能把树高度控制得很低又能很好地支持范围查找比如WHERE id BETWEEN 100 AND 200。这个选择直接决定了后续所有索引机制所以我们后面聊的主键索引、联合索引、回表全都是建立在 B 树这个基础之上的。2. InnoDB 的 B 树与聚簇索引2.1 InnoDB 数据文件长什么样在 InnoDB 里一张表的数据并不是“一行行平铺”在这个文件里的而是按页Page为单位组织的。页是磁盘与内存交互的最小单位默认大小通常是 16KB。每个数据页里存放着若干行记录页与页之间通过链表连接。当你在表上创建了主键InnoDB 会立刻生成一棵 B 树这棵树的叶子节点存放的是整行数据。也就是说数据本身就是按主键的顺序在页里排列的。这种“按主键组织整张表”的索引结构专业说法叫聚簇索引Clustered Index。注意聚簇索引不是一种可选的索引类型而是 InnoDB 存储表的默认方式。这里有一个生活化的类比聚簇索引就像一本按拼音排序的字典正文字本身就是按拼音顺序编排的“查一个字的读音”只需要按拼音定位找到那一页就能看到整个字的解释不需要再去翻别的地方。2.2 主键索引就是聚簇索引很多人会把“主键索引”和“聚簇索引”当成两个概念来回切换其实在 InnoDB 中主键索引就是聚簇索引本身。它的叶子节点存的是完整的一行记录而每个索引叶子节点中存储的键值就是主键值。这意味着两件事通过主键查询时只需要从 B 树根节点开始沿着分支向下走到对应的叶子节点就能拿到完整的数据行整个过程不需要额外的“回表”动作。因为表数据只能按一种物理顺序存储所以聚簇索引在一张表里只能有一个。其他索引的叶子节点不存整行数据而是存“主键值 索引列的值”这种索引就是二级索引或普通索引。2.3 如果表没有显式主键InnoDB 怎么办很多人在建表时忽略主键设计觉得随便建个表能跑就行。这里要提醒一下InnoDB 如果没有找到主键它不会让表变成“无索引”状态而是会自己想办法。具体规则是先找有没有非空的唯一索引如果有就把它当作聚簇索引如果也没有就在后台生成一个隐藏的 6 字节 row_id 作为聚簇索引键整个表按这个隐藏 row_id 排序存储。这个隐藏 row_id 对用户是不可见的所以如果你建表时既没主键也没合适的唯一键后续无法通过某个业务列高效定位数据全表扫描会频繁发生。而且隐藏键对运维排查很不友好因为你在数据字典里看不到它。我的习惯是每张业务表都必须显式指定主键并且尽量用一个与业务无关的自增整数或有序 ID而不是用很长的字符串。3. 主键索引的底层工作过程3.1 一次主键查询走了什么路径假设我们有这样一张用户表CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, nickname varchar(32) DEFAULT NULL, email varchar(64) DEFAULT NULL, mobile varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;然后执行SELECT * FROM user WHERE id 12345;这条 SQL 的查询路径大概是这样的MySQL 会先从 B 树的根节点开始把根节点页读入内存根据 id12345 与节点中的关键值比较决定走哪个子节点。假设这棵 B 树的高度是 3那么需要读取 3 个页最后在叶子节点中找到 id12345 对应的完整行。这里要注意B 树的高度通常很低。几百万行数据的表树高也就 3 到 4 层。也就是说通过主键查询一般只需要 3 到 4 次磁盘 IO 就能拿到数据。相比全表扫描这个查询效率非常惊人。3.2 自增主键 vs UUID 主键怎么选既然主键决定了数据行的物理存储顺序主键的生成方式就变得很重要。最常见的是自增整数主键和 UUID 主键。自增主键的优点是插入时新数据总是追加在 B 树的最右边不需要频繁地挪动已有数据页缺点是分布式场景下扩展会麻烦一些。UUID 主键虽然能全局唯一但它是完全随机的插入时往往要插在 B 树的中间位置导致页分裂频率明显提高写入性能和索引碎片问题都会更严重。在实际项目中我倾向于把主键设计成“业务无关、趋势递增”的数字。对于分布式场景可以换成雪花 ID 之类的有序 ID而不是直接用 32 位随机字符串。一张表主键越长二级索引占用的空间也越大这一点常常被忽略。3.3 聚簇索引下的页分裂与碎片聊到自增主键就不得不提页分裂。当你往 B 树里插入数据时如果某个叶子节点已经写满了 16KB再插入新的记录InnoDB 就要申请一个新的页并把原有节点中大约一半的数据搬过去。这个过程叫页分裂。页分裂本身不是坏事它保证了节点不会过于膨胀。但如果频繁发生会造成很多物理上不连续的碎片页索引扫描效率会下降。所以对于写多读少的表我一般会关注表的碎片率定期可以考虑用OPTIMIZE TABLE做一次整理。4. 联合索引的核心一棵 B 树如何存多列4.1 联合索引其实还是一棵 B 树联合索引也叫复合索引是在多个列上建立的一个索引。它在 InnoDB 里同样是一棵 B 树只不过这棵树的每个节点不是只保存一个列的值而是保存多个列的值。比如我们在 (nickname, email) 上建联合索引索引节点里的键就是由 nickname 和 email 拼接而成。这里要特别注意拼接的顺序先按第一个字段 nickname 排序如果 nickname 相同再按第二个字段 email 排序。可以这样理解联合索引在内部先把所有数据按照定义好的列顺序排好序然后这个排序结果里第一列是绝对有序的第二列只有在第一列相同的情况下才局部有序。4.2 最左前缀原则是怎么来的因为联合索引的排序规则是先按第一列排再按第二列排这就决定了只有在查询条件中包含了最左边的列时MySQL 才能高效利用这个索引。举个例子联合索引是(a, b, c)下面这些查询条件能否走索引WHERE a 1 AND b 2 AND c 3完全能走因为 a 是索引第一列索引顺序完全匹配WHERE a 1 AND c 3能走但只充分使用 a 这个字段去缩小范围c 条件无法在索引里直接定位只能作为“索引内的过滤条件”WHERE b 2 AND c 3完全没法用这个索引来定位因为 b 不是最左列整棵 B 树的排序跟 b 无关。这就是所谓的最左前缀原则Leftmost Prefix。它不是 MySQL 故意设置的限制而是由联合索引的底层排序逻辑自然决定的。索引节点里第一个列的取值决定了整个索引的层级走向如果查询条件里没有第一列就无法利用这棵树的排序结构。4.3 为什么“跳过第一列”会导致索引失效底层逻辑是什么刚开始接触最左前缀时很多人会困惑为什么联合索引是(a, b)我用 b 来查就不行呢我们用生活中的例子类比通讯录里通常先按姓排序再按名排序。如果让你找“张三”你可以很快定位到张姓区域再找名但如果让你直接找所有叫“三”的人你只能翻遍整个通讯录因为名没有全量排序。联合索引的 B 树也是同理它按 a 排好a 是全局有序b 只是在同一个 a 值内部有序。所以只查 b 等于某个值时整个 B 树对 b 的「全局有序性」是不成立的无法利用树的剪枝能力快速跳过范围。4.4 联合索引列顺序的设计策略既然列顺序这么重要那到底应该把哪个列放在联合索引最前面我的经验是看两个维度查询频率最常用、最常出现在 WHERE 等值条件里的列优先放前面。区分度区分度越高的列越适合放前面比如手机号比性别更适合放在首列。因为区分度高意味着相同值的行数少索引能更精确地缩小范围。但这两个维度有时会冲突。比如“状态”列可能频繁出现在 WHERE 里但区分度很低而“订单号”区分度很高却不常查。这种情况下我会先满足查询频率因为一个索引如果最左列根本不在业务查询里出现那这个联合索引就形同虚设连定位能力都没有。区分度的影响可以在满足最左列之后通过后续列的顺序来优化。5. 回表、覆盖索引与索引下推5.1 二级索引的完整查询流程联合索引本质是二级索引它的叶子节点不存整行数据而是存“索引列的值 主键值”。当我们用联合索引查找时过程分两步先到联合索引这棵 B 树中找到符合条件的记录拿到主键值再拿主键值去聚簇索引主键索引里查一次拿到完整的那一行。第二步非常关键它有个专门的名字叫回表Bookmark Lookup。为了演示创建一个订单查询场景CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(32) NOT NULL, amount decimal(10,2) DEFAULT NULL, status tinyint DEFAULT NULL, PRIMARY KEY (id), KEY idx_user_status (user_id, status) ) ENGINEInnoDB;执行SELECT * FROM order_info WHERE user_id 888 AND status 1;这条 SQL 会先走idx_user_status这棵 B 树找到满足user_id888 AND status1的叶子节点取出里面的主键 id。接下来再用这若干主键 id 回到主键索引的 B 树里查完整数据。如果符合条件的行有 10000 条理论上就要回表 10000 次而每次回表都是一次主键树的搜索流程。当数据量很大时这种额外的 IO 开销不容小觑。5.2 覆盖索引让查询不需要回表回表是二级索引使用的代价怎么避免最直接的办法是让查询所需要的列全部包含在二级索引的叶子节点里。因为二级索引叶子节点里已经有“索引列 主键”如果查询的字段恰好只有这些MySQL 在执行时可以直接从索引里取不需要再回到主键索引。比如上面的例子改成SELECT user_id, status FROM order_info WHERE user_id 888 AND status 1;这个查询要的字段只有 user_id 和 status它们都在idx_user_status里叶子节点中存在一份。MySQL 可以直接从联合索引的 B 树中读取并返回不需要回表。这种“查询列全部能在索引中找到”的情况就叫覆盖索引Covering Index。实际优化中覆盖索引的使用场景非常多。有一次排查线上慢 SQL某列表页只展示用户 ID、订单号、状态三个字段原查询是SELECT *导致大量回表。把它改成只查这三个字段并且建了 (user_id,status) 联合索引后查询时间直接从 200 多毫秒降到了十几毫秒。这里面的核心收益就是覆盖索引减少了回表 IO。5.3 索引下推ICP是什么回表次数能不能进一步减少MySQL 5.6 以后引入了索引下推Index Condition PushdownICP优化。简单说就是当联合索引包含多列时MySQL 会把一部分 WHERE 条件“下推”到存储引擎层在遍历索引节点时就判断这些条件而不是先把所有满足最左前缀的记录都拉回服务器层再筛选。看一个具体例子。索引(user_id, status)查询条件SELECT * FROM order_info WHERE user_id 888 AND status 1;如果没有索引下推存储引擎拿到所有user_id888的记录后会把每条记录都交给 Server 层由 Server 层判断 status 是否等于 1。如果user_id888有 10000 条记录这 10000 条都要经过主键回表并传到 Server。而有索引下推时存储引擎在遍历联合索引的叶子节点时会先判断 status 是否为 1只有满足条件的记录才回表。这样回表次数大大减少性能提升非常明显。这也是为什么一些 WHERE 条件虽然没有完全遵循最左前缀但依然可以利用索引做部分过滤的原因。6. 索引失效场景与 EXPLAIN 实测排查6.1 范围查询对联合索引的影响联合索引设计中最容易踩坑的就是范围查询。假设索引是(a, b, c)执行SELECT * FROM t WHERE a 1 AND b 10 AND c 3;a 和 b 可以走索引但 c 很难直接走索引排序定位因为 b 是一个范围值在 b 10 的范围内b 已经不是一个精确条件c 的排序状态在这个范围内是不保证全局有序的。所以理论上这时候联合索引只能用到(a, b)两列。这也是一个常见优化思路遇到范围查询优先把范围查询列放在联合索引靠后的位置把等值查询列放在前面。尽可能让索引中的前缀列都是等值匹配后面的范围列可以用索引减少回表但再后面的列往往发挥不了索引定位作用。6.2 函数操作、隐式类型转换索引为何失效B 树的索引是建立在原始列值上的。如果你在索引列上使用函数或者让列与不同类型的值比较MySQL 没法直接使用这个列的原始排序结构。例如SELECT * FROM user WHERE DATE(create_time) 2025-01-01;即使 create_time 有索引DATE() 函数把值转换过了索引顺序对转换后的结果并不连续MySQL 只能全表扫描。另外如果某个字段是字符串类型查询时用了整型数字比如WHERE mobile 13800138000MySQL 虽然会尝试隐式转换但转换可能让索引无法高效利用。我的排查习惯是检查索引字段是否有类型转换如果有优先改成字段本身与查询值的类型一致或在应用层处理好类型再传入。6.3 用 EXPLAIN 判断是否真的走了索引排查 SQL 是否走对索引最直接的工具是EXPLAIN。比如执行EXPLAIN SELECT * FROM order_info WHERE user_id 888 AND status 1;输出中关键的几个字段要理解type表示访问类型从好到差常见有const、eq_ref、ref、range、index、ALL。ALL表示全表扫描这是最需要警惕的。key实际使用的索引名。rows预估扫描的行数越小越好。ExtraUsing index表示覆盖索引生效Using index condition表示用到了索引下推Using where表示存储引擎返回后 Server 层还要过滤。我在实际优化中最常用的判断标准先看 key再看 Extra。如果 key 是 NULL 或者 type 是 ALL大概率索引没有用上这时候就要倒推 SQL 条件是否满足最左前缀以及是否有隐式类型转换。6.4 常见索引失效场景速查下面是一个简化版速查表能帮你快速定位大部分索引没走上的原因场景失效原因解决思路WHERE 中不用联合索引首列不满足最左前缀调整查询条件或重建索引顺序对索引列使用函数B 树排序被破坏避免在列上套函数改为范围查询索引列隐式类型转换原始值与查询值无法直接比较使用与字段类型一致的参数LIKE 以通配符开头不满足前缀匹配改前缀匹配或使用全文索引范围查询后面的列索引内局部有序被打破把范围列放到索引靠后位置OR 条件包含非索引列优化器可能放弃索引拆分 SQL 或重建复合索引6.5 实际操作中的一条优化流程我整理了一份自己常用的优化清单遇到慢查询时按这个思路走基本不会跑偏先用慢查询日志定位出具体 SQL看 EXPLAIN确认 type、key、rows、Extra分析 WHERE 条件是否满足最左前缀如果不满足考虑调整查询或重建联合索引的顺序看 SELECT 的字段是否过多尝试把查询字段缩到索引覆盖范围内提升覆盖索引命中率检查排序字段是否与索引顺序一致能利用索引排序就不要让 MySQL 在内存里再排一次上线前用大数据量表做一次压测避免在小数据量下看着走索引上线后却走了全表扫描。把这套流程跑熟之后你会发现大部分慢查询其实都不是 MySQL“不行”而是建索引的人没有把联合索引的物理排序逻辑想清楚。最后分享一个我个人的实际体会建索引时不要贪多每一张表上的联合索引都要结合真实业务查询来设计。每次新增一个索引之前先问自己两个问题——这个索引的首列是否频繁出现在业务 WHERE 条件里这个索引是否能让高频查询覆盖索引如果答案是否定的这个索引大概率就是冗余的。以前我维护某套系统时线上表建了十几个普通索引后来花了两天时间梳理业务查询删掉了一半冗余索引不仅查询没变慢写入性能还好了不少磁盘占用也小了很多。把主键索引和联合索引放在 B 树和聚簇索引的框架里去理解很多之前觉得玄乎的优化技巧其实都顺理成章了。 
