你的 MySQL 索引可能白建了！深度拆解 B+ 树底层原理 + 8 条实战优化黄金法则

深入理解 MySQL B 树索引从底层原理到实战优化摘要索引是 MySQL 查询优化的核心武器但用不好反而会成为性能累赘。本文从 InnoDB 数据页结构出发深入剖析 B 树索引的底层实现原理系统讲解聚簇索引、二级索引、联合索引的工作机制并结合实战场景梳理索引使用的六大黄金法则助你真正掌握这把快速查询的秘籍。一、没有索引的世界查询有多慢在理解索引之前我们先想象一下没有索引的世界。InnoDB 将数据存储在大小为16KB的数据页中每个数据页内的记录按照主键值从小到大组成一个单向链表而各个数据页之间通过双向链表关联。页内还维护了一个页目录Page Directory支持通过二分法快速定位记录。┌─────────────────────────────────────────────────────────────┐ │ 页10 │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ 最小记录 │───→│ 记录1 │───→│ 记录2 │───→ ... │ │ │ (infimum)│ │ c11 │ │ c13 │ │ │ └─────────┘ └─────────┘ └─────────┘ │ │ │ │ 页目录: [槽0 → 最小记录] [槽1 → 记录1] [槽2 → 记录2] ... │ └─────────────────────────────────────────────────────────────┘ ↓ 双向链表 ┌─────────────────────────────────────────────────────────────┐ │ 页28 │ │ ┌─────────┐ ┌─────────┐ │ │ │ 记录3 │───→│ 记录4 │───→ ... │ │ │ c14 │ │ c15 │ │ │ └─────────┘ └─────────┘ │ └─────────────────────────────────────────────────────────────┘注意页与页之间在物理存储上可能并不连续只靠双向链表逻辑关联。查找的两种方式场景搜索条件查找方式时间复杂度单页查找主键 xxx页目录二分法 → 遍历槽内记录O(log n)单页查找非主键列 xxx从最小记录开始遍历单链表O(n)多页查找任意条件从第一页开始遍历所有页O(N)当表中有上亿条记录、需要成千上万个数据页时没有索引意味着必须沿着双向链表逐页遍历。这种全表扫描的速度等到结果返回恐怕已经是猴年马月。二、索引的演进从页目录到 B 树2.1 在一个页内查找如果所有记录能放进一个页查找主键时效率很高——借助页目录二分定位再遍历少量记录即可。但如果是非主键列页目录就无能为力了只能从头遍历。2.2 跨页查找的困境跨页查找分为两步定位记录所在的页在页内定位具体记录没有索引时第 1 步只能靠遍历所有页完成。问题的根源在于页与页之间没有按主键大小排序的规律我们不知道目标记录在哪个页里。2.3 为数据页建立目录既然页内可以通过目录快速查找那我们也可以为数据页本身建立一个更高层的目录目录项索引 ┌──────────────────────┐ │ key1 → page_no10 │ │ key4 → page_no28 │ │ key5 → page_no9 │ └──────────────────────┘每个目录项包含两个部分key该页中用户记录的最小主键值page_no页的编号把这些目录项连续存储比如放到一个数组里就可以通过二分法快速定位目标页。这个**“页的目录”**就是索引的雏形。但简易方案有两个致命问题目录项需要连续存储的大块空间记录多了不现实增删记录导致页分裂时目录项需要大量移动牵一发而动全身三、InnoDB 的 B 树索引方案InnoDB 的设计者灵光一现目录项和用户记录长得差不多何不复用数据页来存储目录项记录类型区分InnoDB 用record_type字段区分记录类型record_type含义0普通用户记录1目录项记录2最小记录 (infimum)3最大记录 (supremum)3.1 聚簇索引Clustered Index当目录项也存储在数据页中时整个结构就自然形成了一棵树根节点页33目录项: 1→页30, 320→页32内节点页30目录项: 1→页10, 5→页28, 12→页9内节点页32目录项: 320→页31叶子节点页10用户记录叶子节点页28用户记录叶子节点页9用户记录叶子节点页31用户记录聚簇索引的两大核心特征按主键排序页内记录按主键排成单向链表页之间按主键排成双向链表同层目录项页也按主键排序叶子节点存储完整用户记录所有列的数据包括隐藏列都存放在叶子节点索引即数据数据即索引。在 InnoDB 中聚簇索引就是数据的存储方式叶子节点包含了完整的用户记录。聚簇索引由 InnoDB自动创建不需要手动建立。B 树的惊人容量假设一个叶子节点页能存 100 条记录一个内节点页能存 1000 条目录项树高度最大记录数最多页面查找次数1 层10012 层100 × 1,000 10 万23 层100 × 1,000² 1 亿34 层100 × 1,000³ 1000 亿4一般表很难超过 3~4 层这意味着通过主键查找最多只需3~4 次页面内查找3.2 二级索引Secondary Index聚簇索引只能按主键查找。如果想按其他列如c2查找就需要再建一棵 B 树二级索引与聚簇索引的区别特性聚簇索引二级索引排序依据主键值索引列值如 c2叶子节点内容完整用户记录索引列 主键目录项内容主键 页号索引列 主键 页号是否需要回表不需要需要二级索引查找过程以 c24 为例 1. 在二级索引 B 树中找到 c24 的记录 → 获得主键值 2. 用主键值到聚簇索引中查找完整记录 ← 这就是回表回表二级索引叶子节点只存了索引列和主键要获取完整记录必须再到聚簇索引中查一次。使用二级索引需要访问2 棵 B 树。目录项的唯一性二级索引内节点的目录项记录除了索引列和页号外还包含主键值确保同一层中目录项除页号外是唯一的避免插入时不知道该进哪个分支的尴尬。3.3 联合索引Composite Index同时为多个列如c2,c3建立索引形成联合索引先按c2排序c2相同再按c3排序目录项c2 c3 页号叶子节点c2 c3 主键⚠️重要联合索引 ≠ 分别为各列建索引。联合索引只建1 棵B 树而分别建索引会建多棵B 树。联合索引排序示意图联合索引 (name, birthday, phone_number) 的叶子节点记录排序 Aaron, 1980-01-01, 13800138000, id1 Aaron, 1980-01-02, 13800138001, id2 ... Ashburn, 1990-09-27, 15123983239, id100 Ashburn, 1990-09-28, 15123983240, id101 ... Baird, 1985-05-05, 13912345678, id2003.4 B 树 vs B 树为什么是 B这是面试中的经典问题。B 树在 B 树基础上做了关键优化特性B 树B 树数据存储位置所有节点都存数据只有叶子节点存完整数据叶子节点关系相互独立叶子节点通过双向链表连接内节点作用既存索引又存数据只存目录项索引更瘦查找稳定性可能在任意层找到必须到叶子节点才找到范围查询需要中序遍历效率低直接遍历叶子链表效率高节点容量相对小更大树更矮IO 更少B 树的核心优势内节点更小不存完整数据一个页能存更多目录项树更矮胖减少磁盘 IO范围查询极快叶子节点组成有序双向链表范围查找只需顺序遍历链表查询性能稳定任何查询都必须到叶子节点性能可预测更适合磁盘存储内节点全是索引可以大量缓存在内存中B 树 vs B 树结构对比 B 树 B 树 [10] [10] / \ / \ [3,8] [15,20] [10] [20] | | | | / \ / \ 数据 数据 数据 数据 [3] [8] [15] [25] | | | | 数据链 → 3 → 8 → 10 → 15 → 20 → 25四、InnoDB 数据页结构一览要真正理解索引必须先理解数据页。InnoDB 的数据页是 16KB 的存储单位结构如下┌──────────────────────────────────────────┐ │ File Header (38 bytes) │ ← 页号、上一页、下一页、页类型等 ├──────────────────────────────────────────┤ │ Page Header (56 bytes) │ ← 页内记录数、空闲空间位置等 ├──────────────────────────────────────────┤ │ Infimum Supremum (26 bytes) │ ← 虚拟的最小/最大记录页内链表的边界 ├──────────────────────────────────────────┤ │ User Records │ ← 实际的用户记录按主键排序的链表 │ │ │ │ ├──────────────────────────────────────────┤ │ Free Space │ ← 尚未使用的空闲空间 ├──────────────────────────────────────────┤ │ Page Directory │ ← 页目录记录分组的槽信息二分法用 ├──────────────────────────────────────────┤ │ File Trailer (8 bytes) │ ← 校验和用于检测页是否完整写入 └──────────────────────────────────────────┘ ↓ 总计: 16KB (16384 bytes)关键字段说明组成部分作用File Header记录页号、上一页/下一页指针双向链表、页类型0x45BF 表示数据页、表空间 ID 等Page Header记录页内状态信息slot 数量、第一条用户记录位置、空闲空间偏移量等Infimum/Supremum两个虚拟记录分别比任何用户记录都小/大作为页内单链表的边界User Records实际存储的用户记录或目录项记录按主键从小到大形成单向链表Page Directory将页内记录分组每组最后一条记录的偏移量形成一个数组支持二分查找File Trailer存储校验和LSN 的低 32 位用于判断页写入是否完整原子性保障页目录Page Directory工作原理InnoDB 将页内记录按主键分成若干组Slot每组最多 8 条记录每组最后一条记录的地址偏移量存入 Page Directory。查找时在 Page Directory 中用二分法定位到目标组在组内遍历找到具体记录页内记录分布与 Page Directory 记录链表: infimum → R1 → R2 → R3 → R4 → R5 → R6 → R7 → R8 → R9 → supremum ↑ ↑ Page Directory: [slot0→infimum] [slot1→R4] [slot2→R8] [slot3→supremum] 查找 R6二分法定位 slot1R4~ slot2R8之间的组 → 遍历 R5, R6, R7, R8 → 找到 R6五、索引使用的代价与适用场景5.1 索引的代价索引虽好但不是免费的午餐空间代价每棵 B 树的每个节点都是 16KB 的数据页索引越多占用磁盘空间越大时间代价每次 INSERT/DELETE/UPDATE 都要维护所有索引对应的 B 树可能触发页分裂、记录移位、页面回收等操作结论一个表上索引越多存储空间越大增删改性能越差。5.2 六大适用场景以联合索引idx_name_birthday_phone_number (name, birthday, phone_number)为例① 全值匹配SELECT*FROMperson_infoWHEREnameAshburnANDbirthday1990-09-27ANDphone_number15123983239;所有索引列都用上效率最高。WHERE 子句中条件的顺序不影响MySQL 查询优化器会自动调整。② 匹配左边的列最左前缀SELECT*FROMperson_infoWHEREnameAshburn;SELECT*FROMperson_infoWHEREnameAshburnANDbirthday1990-09-27;可以用到索引。但如果跳过左边的列SELECT*FROMperson_infoWHEREbirthday1990-09-27;-- ❌ 用不上因为记录是先按name排序的name不同的记录中birthday可能是无序的。必须是从最左边连续的列。WHERE name ? AND phone_number ?只能用到name。③ 匹配列前缀字符串前缀SELECT*FROMperson_infoWHEREnameLIKEAs%;-- ✅ 可用索引SELECT*FROMperson_infoWHEREnameLIKE%As%;-- ❌ 全表扫描字符串是按字符逐个比较的前缀是有序的后缀或中间串则无序。④ 匹配范围值SELECT*FROMperson_infoWHEREnameAsaANDnameBarlow;利用 B 树叶子节点的有序性先定位范围起点再沿链表向后扫描直到超出范围。对多列同时范围查找时只有最左列能用索引范围查找。name范围查找后返回的记录birthday可能无序。⑤ 精确匹配某一列 范围匹配另一列SELECT*FROMperson_infoWHEREnameAshburnANDbirthday1980-01-01ANDbirthday2000-12-31ANDphone_number15100000000;name精确匹配 → 可用索引birthdayname精确后结果按birthday排序 → 范围可用索引phone_numberbirthday范围结果中可能无序 → 用不上索引⑥ 用于排序 ORDER BYSELECT*FROMperson_infoORDERBYname,birthday,phone_numberLIMIT10;如果 ORDER BY 列的顺序与联合索引一致可以直接从索引中按顺序取数据避免 filesort文件排序。排序注意事项顺序必须一致ORDER BY name, birthday✅ORDER BY birthday, name❌ASC/DESC 不能混用必须全升序或全降序排序列不能属于不同索引排序列不能是表达式ORDER BY UPPER(name)❌⑦ 用于分组 GROUP BYSELECTname,birthday,phone_number,COUNT(*)FROMperson_infoGROUPBYname,birthday,phone_number;分组顺序与索引一致时可以直接利用 B 树的预排序特性避免内存中的分组计算。5.3 回表的代价与覆盖索引SELECT*FROMperson_infoWHEREnameAsaANDnameBarlow;执行过程在二级索引中找到name在范围内的记录 →顺序 IO记录物理上集中拿到每条记录的id到聚簇索引中查完整记录 →随机 IOid 可能分散在各页需要回表的记录越多二级索引性能越低。当回表记录超过一定比例时优化器可能选择全表扫描。覆盖索引Covering Index—— 告别回表SELECTname,birthday,phone_numberFROMperson_infoWHEREnameAsaANDnameBarlow;查询列全部在索引中不需要回表查聚簇索引极大提升性能。最佳实践避免使用SELECT *尽量只查询需要的列增加使用覆盖索引的概率。六、索引优化实战EXPLAIN 分析在实际工作中我们可以通过EXPLAIN语句来验证索引是否生效。EXPLAINSELECT*FROMperson_infoWHEREnameAshburn;EXPLAIN 关键字段解读字段含义优化目标type访问类型从优到劣: system const eq_ref ref range index ALLpossible_keys可能用到的索引看优化器考虑了哪些索引key实际使用的索引重点关注key_len索引使用的字节长度越短越快但越短可能用得越少rows预估扫描行数越小越好Extra额外信息Using index表示覆盖索引Using filesort表示需要额外排序Using where表示过滤条件典型输出示例------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | person_info| ref | idx_name_birthday_phone_number| idx_name_birthday_phone_number| 303 | const | 10 | Using where | ------------------------------------------------------------------------------------------------------------------------------------优化器选择全表扫描的典型情况查询范围过大如WHERE name A使用SELECT *需要大量回表没有LIMIT限制结果集大小七、索引设计黄金法则1. 只为搜索、排序或分组的列创建索引出现在WHERE、ORDER BY、GROUP BY、JOIN 连接条件中的列才需要索引。查询列表SELECT 后的列不需要单独建索引。2. 考虑列的基数Cardinality列的基数 该列不重复值的个数。列值分布基数索引效果2, 5, 8, 2, 5, 8, …低3❌ 效果差值太集中唯一 ID高行数✅ 效果最佳基数太低的列如性别、状态位建索引收益很小。3. 索引列的类型尽量小能用INT不用BIGINT能用MEDIUMINT不用INT。数据类型小 → CPU 比较更快占用空间少 → 一个页存更多记录 → 减少 IO主键尤其要省空间二级索引叶子节点都会存一份主键值4. 对字符串使用前缀索引KEYidx_name(name(10))只索引字符串的前 10 个字符节省空间、减少比较时间。⚠️ 前缀索引不能用于排序因为无法区分前缀相同的记录的后续字符。5. 让索引列在比较表达式中单独出现WHEREmy_col*24-- ❌ 用不了索引WHEREmy_col4/2-- ✅ 可以用索引WHEREUPPER(name)ABC-- ❌ 用不了索引6. 主键建议 AUTO_INCREMENT自增主键插入时依次递增每插满一页就换下一页随机主键如 UUID会导致频繁页分裂和记录移位性能损耗大CREATETABLEperson_info(idINTUNSIGNEDNOTNULLAUTO_INCREMENT,...PRIMARYKEY(id));7. 避免冗余和重复索引-- 冗余索引idx_name_birthday_phone_number 已包含 nameKEYidx_name(name(10));-- 重复索引主键本身就有聚簇索引UNIQUEuidx_id(id);-- 重复INDEXidx_id(id);-- 重复8. 优先使用覆盖索引查询列表只包含索引列彻底避免回表-- ✅ 覆盖索引只查索引包含的列SELECTname,birthday,phone_numberFROMperson_infoWHEREnameAshburn;-- ❌ 需要回表查询了索引不包含的 country 列SELECT*FROMperson_infoWHEREnameAshburn;八、总结┌─────────────────────────────────────────────────────────────┐ │ MySQL B 树索引知识图谱 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 底层原理 │ │ ├── 数据页 (16KB) 页目录 → 二分查找 │ │ ├── 页分裂保证下一页主键 上一页主键 │ │ ├── 目录项记录 (record_type1) 复用数据页 │ │ └── 多级目录形成 B 树 → 3~4 层即可存数亿记录 │ │ │ │ 索引类型 │ │ ├── 聚簇索引叶子节点存完整记录索引即数据 │ │ ├── 二级索引叶子节点存索引列主键需回表 │ │ └── 联合索引按多列排序≠ 分别建索引 │ │ │ │ 使用技巧 │ │ ├── 全值匹配 / 最左前缀 / 前缀匹配 / 范围查询 │ │ ├── 排序 分组顺序一致 │ │ └── 覆盖索引避免回表 │ │ │ │ 设计法则 │ │ ├── 只为搜索/排序/分组列建索引 │ │ ├── 高基数、小类型、前缀索引 │ │ ├── 索引列单独出现主键自增 │ │ └── 避免冗余重复索引 │ │ │ └─────────────────────────────────────────────────────────────┘核心要点回顾B 树是 MySQL InnoDB 的核心数据结构理解它的分层目录结构是优化查询的基础聚簇索引即数据二级索引需要回表覆盖索引可以省去回表开销最左前缀原则是联合索引使用的铁律顺序决定能否命中索引索引有代价增删改时需要维护多棵 B 树不要滥用索引善用 EXPLAIN分析实际执行计划验证索引是否按预期生效掌握这些原理和法则后你就能在真实业务中设计出高效、精简的索引策略让 MySQL 的查询性能真正飞起来。推荐阅读延伸《高性能 MySQL》第 5 章创建高性能的索引MySQL 官方文档Optimization and IndexesInnoDB 存储引擎内部结构详解