你有没有想过为什么给WHERE phone xxx加上一个索引之后一条慢到让人想砸键盘的 SQL能从几秒变成几十毫秒我第一次在 MySQL 里跑通这个实验时脑子里只有一句话这玩意到底做了什么后来把 B 树的结构一点点拆开才明白索引并不是什么玄学它本质上就是一套用空间换时间、而且完全顺着磁盘脾气设计的数据结构。这篇文章不打算堆公式我会从最原始的查找思路开始把 B 树一层层画出来再结合 MySQL InnoDB 的真实存储机制给你算一笔账。适合所有写过 SQL、知道怎么用索引但没真正搞懂原理的人也适合正准备面试被问到底层机制的同学。1. 一条慢 SQL 引发的追问3 秒变 0.01 秒索引到底做了什么1.1 一次让人印象深刻的线上故障之前我接手过一个内部后台系统用户列表页有个查询接口数据量大概 200 万行。某天业务方反馈说页面越来越卡查了一下慢查询日志发现有一条 SQL 平均耗时 2.8 秒SELECT * FROM user_info WHERE phone 13800138000;按常理说按手机号精确查询应该很快才对但当时user_info表的phone字段上压根没有索引。200 万行的表MySQL 只能老老实实从第一行扫到最后一行每一条记录都做一次字符串比较运气好命中在中间运气不好就是全表扫完。后来加了一行命令ALTER TABLE user_info ADD INDEX idx_phone (phone);再跑同一条 SQL耗时 0.02 秒。150 倍的差距就发生在一条 DDL 之后。如果你也遇到过这种场景你肯定好奇过索引到底是存了一份什么东西能让查询快这么多1.2 索引不是缓存也不只是排序表很多人对索引有个模糊的认知索引就像书的目录没目录就一页页翻有目录就能直接翻到对应页。这个类比大方向对但很容易让人误以为索引只是给数据排了个序。实际上索引是一套独立的存储结构。在 InnoDB 里一张表的数据本身按主键组织成聚簇索引而你在其他字段上建的二级索引是另外开辟空间存放的。这套结构不是简简单单排个序而是用了一种叫 B 树的多路平衡查找树来组织。为什么要用树而且非得是 B 树这就要从磁盘的物理特性说起。不看这个你后面所有对索引的理解都只是背结论。1.3 顺便破除三个常见误解索引不是缓存缓存是把热数据放到内存里减少 IO索引是改变数据的组织方式让每次查找需要读取的数据量更少。索引不是视图视图只是保存了一条 SQL 定义本身不存数据更不可能加速查询真正决定查询速度的是基表上的索引和 SQL 写法。索引不是越多越好每多一个索引写入时就要多维护一棵 B 树插入、更新、删除的开销都会上升这是后面要讲的空间换时间里被换掉的那部分空间和写入成本。2. 磁盘与内存的速度差一切索引设计的出发点2.1 一次磁盘 IO 到底有多慢先看一组数字。CPU 的 L1 缓存访问延迟大概在 1 纳秒左右内存随机访问延迟大约 100 纳秒而一次磁盘 IO 呢机械硬盘随机读写是 5 到 10 毫秒SSD 快一些也要 20 到 100 微秒。100 微秒是什么概念它相当于 1000 倍的内存延迟。如果你把内存访问看作从办公桌上拿一张纸那一次 SSD 随机读就是下楼到仓库翻一个箱子机械硬盘随机读更是开车去另一个城市取一份文件。这个差距是所有数据库数据结构选型时最底层、最不可忽视的约束。所以数据库设计的第一目标不是CPU 算得快而是尽量减少磁盘 IO 次数。B 树之所以胜出核心就赢在它能让查询时访问磁盘的次数稳定保持在一个极小的数字上。2.2 局部性原理与数据页磁盘喜欢整块读磁盘还有一个脾气它不喜欢一次读 1 个字节而喜欢一次读一整块。InnoDB 里这个块叫做页Page默认大小是 16KB。每次从磁盘加载数据至少读一页每次写回磁盘也至少写一页。这意味着一个查找算法如果能让相关的数据尽量落在同一页里就能用一次 IO 拿到更多有用信息这就是局部性原理在存储层面的体现。B 树的巧妙之处在于它把每个节点设计成恰好对应一个或多个页的大小一个节点里密密麻麻塞满了键值和指针一次磁盘 IO 就能把一个节点整个读进内存然后在这个节点内部做二分查找找到下一步该去哪个子节点。2.3 为什么要让树尽量矮树的高度决定了查询需要经过多少个节点也就决定了最坏情况下需要多少次磁盘 IO。二叉搜索树查找 1000 万条数据高度是 24 左右意味着最坏要读 24 次磁盘B 树呢高度 3 到 4 就够了最坏情况 3 到 4 次磁盘 IO。从 24 次降到 3 次这不是微调这是数量级的差距。所以在数据库这个场景里树矮就是王道。后面的所有数据结构对比本质上都在比同样的数据量谁的树更矮、IO 更少。3. 顺着手画 B 树从链表到分层索引3.1 查找的本质不断缩小范围先想一个最简单的查找方式一条单链表排好序找某个值最坏情况要遍历 N 个节点。怎么加速我们把链表一分为二中间加一层索引第一层只放少量锚点第二层才是完整链表。查找时先看锚点判断目标在前半段还是后半段然后钻进对应子链表。这就是二分查找的思路。B 树就是把这个思路递归下去每一层都比上一层更稀疏最底层的叶子节点存实际数据非叶子节点只存路标。3.2 一个节点的内部结构InnoDB 的 B 树节点里非叶子节点存的是一条条的键值 指针对。假设一个节点最多能存 4 个键那么它大概长这样[键1 | 指针1] [键2 | 指针2] [键3 | 指针3] [键4 | 指针4]其中每个指针指向下一层的某个节点指针之间的键值范围是严格排序的。比如根节点存了[10, 20, 30]那么指向左子树的指针里面所有键都小于 10指向第二个子树的指针里面所有键在 10 到 20 之间依此类推查找时在节点内部用二分法找到目标键应该落在哪个区间然后顺着指针进入下一层。每一层都把搜索范围缩小到一个子树而不是像全表扫描那样每条都翻一遍。3.3 插入与页分裂如何保持平衡B 树能一直保持矮靠的是插入时的自动分裂和平衡机制。假设一个叶子节点已经塞满了 4 个键再插入一个新键节点装不下了数据库就会把节点从中间拆成两个节点并把中间的那个键向上提升到父节点。这个操作叫页分裂Page Split。页分裂保证了树永远是平衡的从根到任意叶子路径长度完全相同。所以无论你查的是第一条数据还是最后一条IO 次数都在一个稳定区间内不会像二叉搜索树那样出现某条路径特别深的退化情况。3.4 叶子节点的双向链表范围查询的秘密B 树还有一个容易被忽略但极其关键的设计所有叶子节点通过双向链表串在一起。这意味着当你执行SELECT * FROM user_info WHERE age BETWEEN 20 AND 30;找到第一个满足age 20的叶子节点后不需要再回到树根重新查只要沿着叶子链表往后扫就能一路把 20 到 30 之间的所有数据都取出来。这个特性让 B 树在范围查询上把 B 树、哈希索引远远甩在身后。4. 数据结构大擂台为什么偏偏是 B 树赢了4.1 二叉搜索树 / AVL 树内存里的王者磁盘前的弟弟二叉搜索树BST理论上查找复杂度是 O(logN)看着很美。但它有个致命问题如果插入的数据是有序的BST 会退化成一条链表查找直接变成 O(N)。后来有了 AVL 树、红黑树这类自平衡树能通过旋转保持树高为 logN。但即使平衡了AVL 树每个节点只存一个键。1000 万条数据AVL 树高度约 24 层最坏情况查一次要访问 24 个节点。在内存里这没什么但在磁盘上这 24 次 IO 可比 B 树的 3 次 IO 慢太多。更关键的是AVL 树的节点大小和磁盘页不匹配。磁盘一次读 16KBAVL 树一个节点才几个字节一次 IO 读进来的大部分空间都浪费了局部性很差。4.2 B 树看起来像 B 树输在节点里存数据B 树和 B 树名字只差一个加号结构也很像都是多路平衡搜索树但有一个关键区别B 树每个节点都存实际数据而 B 树只有叶子节点存数据。这个区别导致两个后果同样的 16KB 页B 树节点要腾出空间存数据能存的键和指针数量就少扇出小树就更高IO 次数更多。B 树做范围查询时需要在树的不同层级之间反复横跳没法像 B 树那样沿着叶子链表线性扫描。所以 B 树虽然在一些特定场景比如文件系统依然有用但在关系型数据库的索引领域B 树凭借更高的扇出和更优的范围查询能力成了事实标准。4.3 哈希索引等值查询的独行侠范围查询的废人哈希索引用哈希函数把键值映射到槽位等值查询确实快理论上是 O(1)。但它有两个硬伤不支持范围查询age 20这种条件没法用哈希定位。不支持排序因为哈希值的顺序和键值大小完全无关。还有哈希冲突的问题冲突多了性能会退化。所以 InnoDB 默认的索引结构选 B 树而不是哈希索引。自适应哈希索引只是 InnoDB 在 B 树之上做的一个优化层不是用户可以主动创建的通用索引结构。4.4 各结构对比一张表看明白数据结构等值查询范围查询排序磁盘 IO 次数1000 万数据适合场景顺序扫描O(N)支持支持极高小表全表扫描AVL 树O(logN)支持支持约 24 次内存索引不适合磁盘B 树O(logN)支持但跨层跳跃一般约 4-5 次文件系统等场景B 树O(logN)叶子链表线性扫描天然有序2-4 次数据库索引主流方案哈希索引O(1)不支持不支持1 次精确等值查询5. 算一笔账三层 B 树怎么装下千万级数据5.1 16KB 数据页能塞下多少索引入口前面说了InnoDB 默认页大小 16KB。假设我们有一个二级索引索引键是bigint类型占 8 字节加上指向子节点的指针约 6 字节一条索引项大约 14 字节。一页能存的索引项数量大概是16384 / 14 ≈ 1170 个也就是说一个非叶子节点可以扇出约 1170 个分支。这个数字比大多数人想象的要大得多它是 B 树矮的关键。5.2 从根到叶子最多 3 次磁盘 IO我们来算三层 B 树能装多少数据第 1 层根节点1 页能存约 1170 个键第 2 层中间节点1170 个页每页 1170 个键总键数约 137 万第 3 层叶子节点137 万个页每页存 16KB 数据假设一行数据约 1KB一个叶子页能存约 16 行那么三层 B 树能承载137 万 × 16 ≈ 2190 万行查一条数据从根节点出发找到中间层再到叶子层最多 3 次磁盘 IO。如果根节点已经被 buffer pool 缓存实际只需要 2 次甚至 1 次磁盘 IO。这就是为什么 200 万行的表加了索引之后查询能从 2.8 秒变成 0.02 秒。全表扫描可能要读几万个数据页而 B 树只需要读 3 个页。5.3 聚簇索引与二级索引回表到底在回什么在 InnoDB 里表本身就是一棵 B 树主键索引的叶子节点存的是完整的一行数据这叫聚簇索引。而你建的普通索引比如idx_phone叶子节点存的不是整行数据而是主键值。查询时先用二级索引找到主键再到聚簇索引里根据主键取整行这个过程叫回表Table Lookup。举个例子SELECT * FROM user_info WHERE phone 13800138000;执行过程是走idx_phone这棵 B 树找到phone对应的主键 id再走主键这棵 B 树用 id 找到完整行数据如果查询的字段正好都在二级索引里就不需要回表这种查询叫覆盖索引Covering Index性能会进一步提升。5.4 为什么数据量翻 10 倍查询时间几乎不变B 树的层数增长是极其缓慢的。5 层 B 树能支撑的数据量是 3 层的 1170 倍也就是几十亿行级别。所以你会发现100 万行和 1000 万行的表在等值查询上的耗时几乎一样因为树的高度可能都是 3IO 次数没变。这也是 B 树在数据库领域统治这么多年的根本原因它不仅快而且性能是稳定可预期的不会因为数据量增长而线性劣化。6. 实测一把在 MySQL 里把 B 树拆开验证6.1 造一张测试表理论说再多不如实际跑一遍。我建议你本地开个 MySQL用下面的语句建一张表CREATE TABLE user_info ( id bigint unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, phone varchar(20) NOT NULL, age int NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_phone (phone), KEY idx_name_age (name, age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用存储过程或者随便什么办法插入几十万行测试数据然后对比有无索引的查询耗时。这里有个小技巧不要只测一次多跑几次取平均值因为第一次查询有冷缓存的影响后面的查询 buffer pool 会把数据页缓存住差异会被掩盖。6.2 EXPLAIN 看到的索引命中链路MySQL 里最常用的验证工具就是EXPLAINEXPLAIN SELECT * FROM user_info WHERE phone 13800138000;关键看这几个字段type如果是const或ref说明走了索引如果是ALL就是全表扫描。key显示实际用到的索引名比如idx_phone。rows优化器估计要扫描的行数走了索引之后这个数字会大幅下降。Extra如果出现Using index恭喜你这是覆盖索引如果出现Using index condition说明用了索引下推优化。实测的时候你会发现加索引前rows是几十万加索引后rows直接变成 1。这个变化比任何文字都直观。6.3 覆盖索引的实验我们再试一个查询EXPLAIN SELECT phone FROM user_info WHERE phone 13800138000;这条 SQL 只需要phone字段而idx_phone的叶子节点里就有 phone 和主键 id所以不需要回表Extra会显示Using index。把它和SELECT *对比你会发现即使同样走了索引覆盖索引的耗时还是明显更低因为省掉了一次聚簇索引的随机读。6.4 用 profiling 看真实 IO 差异如果还想更深入地看可以打开 profilingSET profiling 1; SELECT * FROM user_info WHERE phone 13800138000; SHOW PROFILE;这里能看到查询各阶段的耗时占比尤其是Sending data阶段的时间变化。加索引前这项通常是几百毫秒加索引后会降到毫秒级。这是我做索引调优时最常用的验证手段之一。7. 索引失效的套路全整理建了索引却走全表7.1 函数与运算别给索引列动手术最常见的一种索引失效是查询条件里对索引列使用了函数或运算。比如SELECT * FROM user_info WHERE YEAR(create_time) 2024; SELECT * FROM user_info WHERE age 1 30;第一条对create_time用了YEAR()函数第二条对age做了加法运算。B 树索引是按原始值排序的MySQL 无法直接根据一个被加工过的值去走树的路径只能全表扫描把每一行的create_time取出来算一遍YEAR()。解决方案是改写 SQL让索引列保持裸状态SELECT * FROM user_info WHERE create_time 2024-01-01 AND create_time 2025-01-01;7.2 隐式类型转换字符串列别传数字还有一个很隐蔽的坑索引列是varchar查询条件却传了数字。比如SELECT * FROM user_info WHERE phone 13800138000;MySQL 会把字符串类型的phone和数字比较时做隐式类型转换相当于对phone列施加了一个转换函数索引就失效了。正确写法必须加引号SELECT * FROM user_info WHERE phone 13800138000;判断一个 SQL 有没有隐式转换可以用EXPLAIN看type是不是从ref变成了ALL或者对比WHERE条件前后的 rows 估算值。7.3 最左前缀原则联合索引的顺序敏感联合索引idx_name_age (name, age)它的 B 树先按name排序name相同再按age排序。这导致一个规则查询条件必须从联合索引的最左列开始才能用到这个索引。下面两条 SQL第一条能走索引第二条不能-- 能走 idx_name_age SELECT * FROM user_info WHERE name 张三 AND age 30; -- 不能走 idx_name_age SELECT * FROM user_info WHERE age 30;因为索引中age的整体顺序是乱的只有name相同时age才有序。没有name作为前缀B 树没法定位到age30的起点。还有一个细节如果只给age建单独索引那么上面第二条 SQL 就能走那个单列索引。所以设计联合索引时要考虑实际查询条件里最常出现的是哪些列把它们放在前面。7.4 LIKE 前置、OR 拼接、NOT IN三个经典反面教材LIKE %张%以通配符开头B 树没法从根节点定位前缀只能全表扫。OR拼接比如WHERE phone 13800138000 OR age 30如果age没有索引MySQL 可能需要把两个条件的结果合并优化器往往选择全表扫描。NOT IN、这类否定条件下优化器经常判断索引扫描的代价比全表扫描高选择不走索引。这些场景不是说一定会失效而是要看优化器的成本估算和数据分布但作为经验遇到它们时多留个心眼用EXPLAIN确认一下最保险。7.5 选择性太低时优化器主动放弃索引最后一个失效很多人会忽略不是语法不支持而是优化器觉得走索引更慢。比如性别字段90% 是男。你在gender上建了索引查WHERE gender 男走索引的话需要大量回表每次回表又是一次随机 IO反而不如直接顺序扫描全表来得快。优化器算完成本后会主动选择全表扫描。这时候EXPLAIN显示type ALL但索引本身没坏只是优化器做了合理取舍。这类问题的解决思路不是纠结索引而是换一个选择性更高的查询方式或者使用覆盖索引让回表消失。8. 最后一点实操心得我自己在索引调优上踩过不少坑最后总结出三条比较实用的经验。第一先看慢查询日志再谈建索引。不要凭感觉给所有字段加索引slow_query_log里能直接看到耗时最高的 SQL把它们集中优化性价比最高。第二联合索引尽量靠左设计并考虑覆盖索引。一次查询能用索引解决就不要再回表Extra里能看到Using index是 SQL 优化的一个很舒服的状态。第三上线前用 EXPLAIN 过一遍核心 SQL。尤其是type字段出现ALL、key显示NULL的时候一定要追问一句为什么。很多时候一条本可以走索引的 SQL只是因为函数、类型转换或者 LIKE 写法就被打回了全表扫描而这个问题在数据量小的测试环境根本看不出来。索引不是银弹它是一套有成本、有规则的数据结构。理解了 B 树为什么矮、为什么叶子用链表串联、为什么节点大小要对齐磁盘页那些所谓的索引失效其实都不需要死记硬背顺着树的结构想一遍答案自己就出来了。