news 2026/10/8 3:10:24

MySQL 索引从原理到落地：B+ 树、联合索引、失效场景与优化清单

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
MySQL 索引从原理到落地：B+ 树、联合索引、失效场景与优化清单
个人主页： > for_ever_love__ <（欢迎各位大佬莅临😊）
其他栏目: > 大模型开发从0到1 <
其他栏目: > iOS项目总结大全 <
其他栏目: > 我想学python了 <
其他栏目: > iOS UI <

文章目录

  • MySQL 索引从原理到落地：B+ 树、联合索引、失效场景与优化清单
    • 一、索引解决的是什么问题
    • 二、为什么是 B+ 树
      • 对比哈希
      • 对比二叉树
      • 对比 B 树
      • B+ 树的三个关键设计
      • 那为什么不用跳表？
    • 三、聚簇索引 vs 二级索引：回表是怎么回事
      • 聚簇索引（主键索引）
      • 二级索引（非聚簇索引）
      • 回表与覆盖索引
    • 四、联合索引与最左匹配原则
      • 建联合索引的两条经验
      • 一个判断题
    • 五、让索引失效的 7 种写法
    • 六、索引优化检查清单
      • 该建索引的字段
      • 不该建索引的字段
      • 四个具体优化手段
    • 七、小结

MySQL 索引从原理到落地：B+ 树、联合索引、失效场景与优化清单

很多人对索引的理解停在「加个索引就快了」。但真到了线上，常常是：索引加了，查询还是慢；或者索引没加错，只是写法让它悄悄失效了。

这篇文章按一条线讲下来：索引到底解决了什么问题 → MySQL 为什么选 B+ 树 → 聚簇索引和二级索引差在哪 → 联合索引的最左匹配怎么判断 → 哪些写法会让索引失效 → 最后给一份能直接照着做优化的检查清单。

示例统一用一张用户表：

CREATETABLE`user`(`id`BIGINTNOTNULLAUTO_INCREMENT,`name`VARCHAR(64)NOTNULL,`age`INTNOTNULL,`city`VARCHAR(32)NOTNULL,`gender`TINYINTNOTNULL,`created`DATETIMENOTNULL,PRIMARYKEY(`id`),KEY`idx_city_age`(`city`,`age`))ENGINE=InnoDB;

一、索引解决的是什么问题

没有索引时，WHERE name = '张三'只能从第一行开始逐行比对，也就是全表扫描，复杂度 O(N)。100 万行的表，最坏要比对 100 万次。

有索引之后，数据库维护了一份额外的数据结构，把「值 → 行位置」的映射按有序方式组织起来，查找退化为在有序结构上的定位，复杂度降到 O(logN)。

代价也很直接：

收益代价
查询从 O(N) 降到 O(logN)索引本身占磁盘空间
ORDER BY/GROUP BY可以直接利用有序性每次增删改都要同步维护索引
减少磁盘 IO 次数索引建多了，写入会明显变慢

所以索引不是越多越好，它是一次「用空间和写入性能换读取性能」的交易。

二、为什么是 B+ 树

MySQL InnoDB 默认用 B+ 树。为什么不是别的？逐个比一下就清楚了。

对比哈希

哈希表单点查询 O(1)，比 B+ 树还快。但它只支持等值查询，范围查询完全退化——WHERE age > 20在哈希表里只能全表扫。业务里范围查询遍地都是，所以哈希索引只能是配角（Memory 引擎支持，InnoDB 有自适应哈希作为内部加速，但你没法显式创建）。

对比二叉树

二叉搜索树每个节点只有两个分叉。100 万数据，树高约 20 层，意味着最多 20 次磁盘 IO。而且频繁插入删除后容易退化成链表。

对比 B 树

B 树是多路平衡，解决了树高问题，但它的每个节点都存完整数据行。一个节点大小固定（InnoDB 页默认 16KB），存了数据就存不下多少键值，导致扇出变小、树变高。更麻烦的是 B 树的叶子节点之间没有链表相连，做范围查询要反复回到上层节点。

B+ 树的三个关键设计

  1. 非叶子节点只存键值和子节点指针，不存数据。这样一个 16KB 的页能塞下上千个键值，扇出极大，千万级数据树的高度也只有 3~4 层——一次查询最多 3~4 次磁盘 IO。
  2. 所有数据都在叶子节点，且叶子在同一层。所以任何一条记录的查询路径长度都一样，性能稳定。
  3. 叶子节点之间用双向链表串起来。范围查询时顺着链表扫就行，不用回头找父节点。

那为什么不用跳表？

跳表在内存里（比如 Redis 的 ZSet）表现很好，但它的指针跳转是随机的。放到磁盘上，一次跳转就可能是一次随机 IO，而 B+ 树一个节点是一个连续的页，一次 IO 能读进上千个键值。磁盘场景下 B+ 树完胜。

一句话总结：B+ 树是为「减少磁盘 IO 次数」而生的结构，扇出大、树高低、范围查询友好，这三点正好命中数据库的痛点。

三、聚簇索引 vs 二级索引：回表是怎么回事

InnoDB 的索引按物理存储分两类。

聚簇索引（主键索引）

  • 以主键为键构建 B+ 树，叶子节点存的是整行完整数据；
  • 一张表只能有一个；
  • 没显式定义主键时，InnoDB 会依次找「第一个不含 NULL 的唯一索引」，再找不到就自动生成一个隐藏的 row_id。

二级索引（非聚簇索引）

  • 以普通字段为键构建 B+ 树，叶子节点只存主键值；
  • 可以有多个。

回表与覆盖索引

假设执行：

SELECTname,ageFROMuserWHEREcity='杭州';

idx_city_age (city, age)里没有name。流程是：

  1. 在idx_city_age里定位到city='杭州'的叶子，拿到一批主键 id；
  2. 拿着这些 id 回到聚簇索引查完整行，取出name。

第 2 步就是回表。如果命中的行数很多，回表就是几十上百次额外的主键查找，代价不小。

但如果查询改成：

SELECTageFROMuserWHEREcity='杭州';-- 只要 age

age已经在idx_city_age里了，二级索引的叶子上直接就有答案，不用回表——这就是覆盖索引，EXPLAIN的 Extra 列会显示Using index。

优化第一招：把查询需要的字段尽量塞进索引里，让查询变成覆盖索引。

四、联合索引与最左匹配原则

idx_city_age (city, age)是联合索引。它在 B+ 树里的排序规则是：先按 city 排，city 相同再按 age 排。

由此推出最左匹配原则：查询条件必须从索引最左边的列开始，才能用上这个索引。

对着上面的表，判断一下：

SQL能否用上idx_city_age原因
WHERE city='杭州'✅ 用到 city命中最左列
WHERE city='杭州' AND age=25✅ 两列都用完全匹配
WHERE age=25❌跳过了最左列 city
WHERE city='杭州' AND age>20✅ 两列都用范围列可以在最后
WHERE age=25 AND city='杭州'✅优化器会自动调整顺序，和顺序无关

最后一行是常见误区——最左匹配看的是「有没有包含最左列」，不是「WHERE 里的书写顺序」。MySQL 优化器会重排条件。

建联合索引的两条经验

  1. 区分度高的列放前面。区分度 = 不同值的数量 / 总行数。比如city有几千个值、gender只有 2 个值，那(city, gender)比(gender, city)过滤效果好得多。
  2. 尽量让索引覆盖查询字段，避免回表。

一个判断题

索引(a, b, c)，条件是WHERE a=1 AND c<10，索引怎么走？

  • a=1命中索引；
  • 因为跳过了b，c无法继续用索引定位；
  • 结果：只用 a 过滤，剩下的数据在 a=1 的结果集里逐行判断 c<10。

（MySQL 5.6 之后的索引下推 ICP 会把c<10的判断下推到存储引擎层，减少回表次数，但c本身仍然没用于定位。）

五、让索引失效的 7 种写法

这些是最常踩的坑，每条都配一个反例。

1. 跳过最左列

SELECT*FROMuserWHEREage=25;-- 用不上 idx_city_age

2. 左模糊 / 全模糊匹配

SELECT*FROMuserWHEREnameLIKE'%三';-- ❌ 失效SELECT*FROMuserWHEREnameLIKE'张%';-- ✅ 能用索引

B+ 树是按前缀有序组织的，%在最前面就无从定位起点。

3. 对索引列做函数运算

SELECT*FROMuserWHEREYEAR(created)=2026;-- ❌SELECT*FROMuserWHEREcreated>='2026-01-01'ANDcreated<'2027-01-01';-- ✅

4. 隐式类型转换

-- phone 是 VARCHAR，却传了数字SELECT*FROMuserWHEREphone=13800138000;-- ❌ 触发 CAST，索引失效SELECT*FROMuserWHEREphone='13800138000';-- ✅

这条特别隐蔽，因为 SQL 不会报错，只是悄悄变慢。

5. 索引列参与算术运算

SELECT*FROMuserWHEREage+1=26;-- ❌SELECT*FROMuserWHEREage=25;-- ✅

6. OR 连接中存在无索引列

SELECT*FROMuserWHEREcity='杭州'ORgender=1;-- gender 无索引 → 整体不走索引

7. 优化器主动放弃

当某个条件的区分度太低，优化器判断「走索引还要回表，不如直接全表扫」，就会放弃索引。典型例子是给gender这种只有两个值的列单独建索引——100 万行里命中 50 万行，回表 50 万次，还不如顺序扫。

判断索引到底用没用，别猜，用EXPLAIN：

EXPLAINSELECTageFROMuserWHEREcity='杭州';

重点看三列：type（ref/range尚可，ALL是全表扫）、key（实际用到的索引）、Extra（Using index= 覆盖索引）。

六、索引优化检查清单

该建索引的字段

  • WHERE里高频出现的条件列；
  • ORDER BY/GROUP BY的列——B+ 树本身有序，能省掉排序；
  • 多表JOIN的关联列；
  • 区分度高的列（经验上超过 30% 才值得单独建）。

不该建索引的字段

  • 数据量很小的表（几百行，全表扫比走索引还快）；
  • 几乎不出现在查询条件里的列；
  • 频繁增删改的列——每次写入都要维护 B+ 树；
  • 区分度极低的列（性别、状态位），除非放在联合索引的尾部。

四个具体优化手段

  1. 覆盖索引：把查询字段纳入索引，消除回表，EXPLAIN出Using index为准。
  2. 前缀索引：对很长的字符串只取前 N 个字符建索引，兼顾体积和区分度。
    ALTERTABLEuserADDKEYidx_name(name(10));
    注意前缀索引无法用于 ORDER BY / GROUP BY，也无法做覆盖索引。
  3. 主键用自增 ID 而不是 UUID。自增 ID 顺序插入，新记录总是追加到当前页末尾，页分裂少、碎片少、磁盘利用率高。UUID 是随机值，插入位置随机，会导致频繁的页分裂和大量碎片，而且 UUID 占 36 字节，让每个页能装的键值变少，间接把树变高。
  4. 联合索引按区分度从高到低排列，并尽量覆盖查询字段。

七、小结

压缩成五句：

  1. 索引是用空间和写入性能换读取性能的交易，不是越多越好。
  2. MySQL 选 B+ 树，是因为它扇出大、树高 3~4 层、叶子有链表，把磁盘 IO 次数压到最低。
  3. 聚簇索引叶子存整行，二级索引叶子存主键；拿不到完整数据就要回表，能避免就避免。
  4. 联合索引遵守最左匹配——看的是有没有包含最左列，不是 WHERE 的书写顺序。
  5. 索引失效大多栽在：函数/运算/隐式转换/左模糊/跳过最左列/区分度太低。

建议的下一步：挑一条你项目里最慢的 SQL，跑一遍EXPLAIN，对照第五节的 7 条逐项排查，通常能直接定位到问题。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/10/8 3:10:21

Text-to-CAD实战指南：从自然语言到三维模型的关键技术解析

我入行做结构设计那会儿&#xff0c;最磨人的环节不是方案想不出来&#xff0c;而是“想出来了还得把它画出来”。一个板厚2mm、带四个安装孔和两处限位凸台的钣金支架&#xff0c;从打开CAD到建模完成&#xff0c;熟练工也得半小时起步&#xff0c;粗心点的工程师可能磨蹭一个…

作者头像 李华
网站建设 2026/10/8 3:08:46

HarmonyOS 6 ArkUI无障碍事件实战：让TalkBack双击自定义卡片生效

前阵子做 HarmonyOS 6 适配时&#xff0c;我遇到一个很典型的无障碍问题&#xff1a;自定义卡片在 TalkBack 下能朗读内容&#xff0c;但用户双击之后完全没有反应。第一反应是事件绑定写错了&#xff0c;后来把 onClick 改成无障碍动作&#xff0c;问题当场解决。这个案例说明…

作者头像 李华
网站建设 2026/10/8 3:07:46

牛客网SQL入门题深度拆解：从表连接、窗口函数到面试实战

先说个真实感受&#xff1a;我见过太多人把牛客网SQL题当成“刷题工具”&#xff0c;一顿操作猛如虎&#xff0c;把前50道入门题背得滚瓜烂熟&#xff0c;结果一到面试现场&#xff0c;面试官换了个业务背景问同样的问题&#xff0c;直接就懵了。牛客网SQL入门题的价值不在于“…

作者头像 李华
网站建设 2026/10/8 3:07:26

MySQL内存占用过高？从参数到底层原理的排查与优化指南

MySQL内存占用过高&#xff0c;这活儿我前前后后接了几十次求助了。很多朋友装完MySQL顺手把配置一填&#xff0c;也不管默认参数适不适合自己机器&#xff0c;跑两天发现内存吃了好几个G&#xff0c;第一反应就是“MySQL是不是有毛病”。其实大部分时候MySQL挺冤的&#xff0c…

作者头像 李华
网站建设 2026/10/8 3:07:26

MySQL内存占用排查与调优：从原理到实战

MySQL这种问题&#xff0c;干运维的兄弟十有八九都遇到过。进程一启动&#xff0c;内存蹭蹭往上涨&#xff0c;看着free命令里那点剩余内存心都凉了。更气人的是&#xff0c;网上搜出来的答案要么是复制粘贴的官方文档&#xff0c;要么就是“重启大法”&#xff0c;看完了也不知…

作者头像 李华
网站建设 2026/10/8 3:07:26

用MMC5603NJ打造电子指南针：从I2C读取到航向角输出全流程

说到电子指南针&#xff0c;很多人第一反应是 HMC5883L&#xff0c;但这几年我反而更常用 MMC5603NJ 这种小封装三轴地磁传感器。它没有以前那套复杂的外围&#xff0c;I2C 直接出数据&#xff0c;一颗芯片就能把指南针示例跑起来。这篇文章我就拿 MMC5603NJ 地磁传感器当主角&…

作者头像 李华