1. 索引到底是什么从一本书的目录说起先别急着打开 Navicat 敲CREATE INDEX我建议你先把索引这个概念在脑子里揉碎了再动手。很多人对索引的理解停留在“查询快”三个字上但一旦面试官问“为什么加了索引查询就快快在哪个环节底层是什么数据结构”就答不上来了。这种半懂不懂的状态恰恰是生产环境出问题的根源。我用一个特别朴素的类比来解释。你手里有一本 500 页的技术书想找“BTree 的页分裂”这个概念在第几页。没有目录的话你得从第 1 页翻到第 500 页运气好翻得快运气差一上午就搭进去了。有了目录之后你先翻到目录页找到“BTree”这个章节的页码然后直接翻到那一页。省掉的是什么是大量无关页面的扫描。MySQL 的索引就是这个目录只不过它比纸质书的目录精密得多——书上每一页都有对应的目录项查找时通过二分等方式快速定位到目标数据页。但如果你以为索引就是一个排好序的列表那就把问题想简单了。MySQL 的索引选用的是一棵 B 树而不是红黑树、不是哈希表、也不是普通的二叉树。为什么偏偏是它这里涉及磁盘 IO 的原理、InnoDB 存储引擎的页结构、主键和二级索引的配合机制甚至还有查询优化器怎么挑索引的策略。这一整套逻辑捋顺了你才能真正理解索引是“怎么工作的”以及更重要的——索引什么时候会失效。这篇文章适合三类人第一类是刚入行的后端开发想把索引原理搞明白别面试一问就露怯第二类是工作了两三年的业务开发写 SQL 经常遇到慢查询想系统地排查和优化第三类是运维或 DBA 转岗的同学需要从原理层理解 MySQL 的索引机制为日常的建表、改表、优化提供底层判断依据。2. 为什么偏偏是 B 树一次磁盘 IO 引发的数据结构选型2.1 从二叉树到 B 树的演进逻辑先摆一个最基础的事实MySQL 的数据最终是存在磁盘上的而磁盘随机读写的速度比内存慢好几个数量级。内存访问是纳秒级磁盘访问是毫秒级中间隔着十万八千里。所以数据库设计的第一性原理就是尽量减少磁盘 IO 次数。如果你用普通的二叉搜索树做索引树的高度会随着数据量的增加而迅速增长。假设你有 100 万条数据二叉树的高度大约是 20 层。查找一条数据最多需要访问 20 个节点意味着最多 20 次磁盘 IO。看起来还行但数据量到 1 亿呢高度大概 27 层磁盘 IO 就要 27 次。每次 10 毫秒那就是 270 毫秒这个延迟对绝大多数业务来说已经不可接受了。为了降低树的高度就得让每个节点多“装”一些数据于是有了多路搜索树。B 树就是其中代表——每个节点可以持有多个子节点数据量相同的情况下树的高度大大降低。但 B 树有个问题它的所有节点都存数据包括内部节点。这意味着你做范围查询的时候得在中序遍历的过程中反复跳转不同的节点离散 IO 很严重。B 树的改进在于只有叶子节点存数据内部节点只存索引键。这带来两个好处。第一内部节点能容纳更多的键树更矮更宽磁盘 IO 次数进一步减少。第二叶子节点通过双向链表串联范围查询、排序查询只需要顺着链表往下扫就行不需要回溯到上层节点。这两个特性正好踩中了数据库查询的两个高频场景等值查询和范围查询。2.2 页与磁盘 IO 的匹配逻辑InnoDB 存储引擎的数据操作最小单位是页Page默认大小是 16KB。磁盘读写也有自己的最小单位叫扇区一般是 512 字节或者 4KB。操作系统和硬件之间还有一层文件系统文件系统一般按 4KB 的块来管理。InnoDB 设计成 16KB 的页本质上就是为了让一次磁盘 IO 能读回足够多的有用数据。一个 16KB 的页能存多少索引键呢假设主键是 BIGINT占用 8 字节加上指向子节点的指针 6 字节左右InnoDB 中页面指针按 6 字节设计单个索引项大约 14 字节。那么一个页大约能存 16384 / 14 ≈ 1170 个索引项。如果叶子节点每个页放 16KB 的数据一行的数据假设 1KB那一个页大概能放 16 行。三层的 B 树能存多少数据1170 × 1170 × 16 ≈ 2190 万行。你回想一下刚才二叉树 1 亿数据要扫 27 层B 树这个量级只需要 3 次磁盘 IO。这就是为什么 MySQL 选择 B 树而不是其他数据结构的根本原因——在磁盘 IO 为瓶颈的前提下用最少的 IO 次数定位到目标数据。这不是理论上的花拳绣腿而是实打实的工程设计。2.3 为什么不用哈希索引和红黑树有人会问哈希索引的等值查询不是 O(1) 吗为什么 InnoDB 不默认用哈希因为哈希索引天生不支持范围查询WHERE age 20 AND age 30这种 SQL 用哈希索引就废了。另外哈希索引对排序也无能为力数据在哈希表里是散列的没有办法按顺序扫描。虽然 InnoDB 内部有自适应哈希索引作为加速等值查询的辅助结构但它是在 B 树之上的补充不可能替代 B 树。红黑树的问题前面提过树高随着数据量线性增长范围查询也不太友好。AVL 树虽然平衡性好但为了维持平衡需要频繁旋转插入和删除的成本很高数据库这种写多读多的场景不合适。还有跳表Redis 里面用得不少内存存储场景下表现优秀但落到磁盘上节点指针的局部性远不如 B 树的数据页紧凑。3. InnoDB 里的 B 树到底长什么样3.1 聚簇索引主键决定的数据物理排列InnoDB 的表实际上就是一棵 B 树树的叶子节点存的是完整的行数据。这个以主键为索引键的 B 树就叫聚簇索引Clustered Index。每一张 InnoDB 表有且只有一个聚簇索引你回想一下它的特点就明白了叶子节点就是整行数据怎么可能有多个如果你建表时没有指定主键InnoDB 会找第一个非空的唯一索引作为聚簇索引如果也没有它会悄悄生成一个隐藏的ROW_ID作为聚簇索引键。这个隐藏主键是 6 字节的全局自增。我建议你不要依赖这个隐藏主键因为它是 InnoDB 内部维护的你在业务层无法控制。更严重的问题是如果你用一个随机值当主键插入时可能频繁触发页分裂性能会很难看。聚簇索引还有个会被忽略的特点数据行是按照主键顺序物理排列的。这个“物理排列”不是严格意义上的按照磁盘地址连续存放而是指逻辑上相邻的主键值会落在相邻的页里。按主键范围查询时InnoDB 能利用叶子节点的链表直接顺序扫描性能极好。3.2 二级索引与回表除了聚簇索引你在业务上建的索引都叫二级索引Secondary Index也叫辅助索引。二级索引的叶子节点存的不再是完整行数据而是当前索引列的值 对应行的主键值。举个例子你在user_name字段上建了索引那这棵 B 树的叶子节点就是一个个(user_name, id)的组合。查询的时候先按照user_name在二级索引的 B 树里找到对应的主键 id然后再拿着这个 id 去聚簇索引里查完整的行数据。这个过程叫“回表”。回表不是免费的每次回表都是一次 B 树搜索走一次聚簇索引的 IO 路径。如果查询命中了 1000 条数据就意味着除了二级索引的扫描外还要额外做 1000 次聚簇索引查找。这里就有了覆盖索引Covering Index的概念。如果查询需要的所有列都包含在二级索引里那就不需要回表了。比如SELECT id, user_name FROM t WHERE user_name 张三索引(user_name)的叶子节点里有id和user_name直接返回结果就行。这条 SQL 连聚簇索引都不用碰效率自然高。3.3 页的内部结构与页分裂一个数据页内部并不是简单地堆数据它有页头、页尾、用户记录区域、空闲区域、页目录等结构。页目录的存在是为了在页内部做二分查找快速定位到某条用户记录的位置。你可以把页想象成一个房间页目录就是房间里的检索表帮你快速知道某个值的记录在这个房间的哪个位置。页和页之间通过双向链表连接。叶子节点内部的记录通过单向链表连接链表按主键或索引键排序。插入数据时如果当前页满了就要申请一个新页然后重新分配原来页里的记录。这个过程就是页分裂。页分裂会带来一个问题新页在磁盘上的物理位置可能并不和原页相邻导致逻辑有序但物理离散。页分裂是 InnoDB 写入性能的一个隐藏杀手。如果你的主键是随机的 UUID每次插入大概率会落在已有页的中间位置触发页分裂。分裂操作涉及数据拷贝、链表调整、页空间的重新分配代价很大。反过来如果你用自增主键新插入的行总是追加在当前最后一个页的末尾分摊下来页分裂的概率就小得多。4. 实操索引设计与 SQL 优化实战4.1 联合索引与最左前缀原则联合索引是业务开发中用得最多也最容易出错的索引结构。建议你从两个角度去理解它一是 B 树的排序规则二是查询优化器的匹配逻辑。联合索引(a, b, c)在 B 树里先按 a 排序a 相同时再按 b 排序b 相同时再按 c 排序。这个排序规则决定了只有查询条件里包含了最左侧的 a 列这个索引才能被用来定位或扫描。比如WHERE a 1、WHERE a 1 AND b 2、WHERE a 1 AND b 2 AND c 3都能用到索引但WHERE b 2和WHERE c 3用不上因为没有最左侧的 a 列。这个规则叫最左前缀原则。我见过不少人把这个原则理解反了以为“只要查询条件里包含联合索引的任意列就能用索引”这是错的。MySQL 的索引匹配是从最左边开始逐列匹配的中间跳过的列会让后面的索引列失效。比如WHERE a 1 AND c 3只有 a 能用索引c 用不上。4.2 索引下推一个被低估的优化再补充一个容易被忽略的点索引下推Index Condition PushdownICP。它发生在联合索引匹配了一部分条件后剩余条件能不能在索引层面先过滤掉一部分行。举个例子索引是(name, age)查询是WHERE name 张三 AND age 20。在没有 ICP 的时候MySQL 先用name 张三查到一批主键然后回表拿完整行再在服务层过滤age 20。有了 ICP 之后MySQL 直接在二级索引的叶子节点上判断age 20不符合的直接跳过不回表。回表次数少了IO 自然就下来了。ICP 是 MySQL 5.6 引入的功能默认开启的。如果你的 MySQL 版本太老或者某些查询没有走 ICP性能差距会非常明显。这也是为什么我强烈建议联合索引的列顺序一定要把等值查询的列放在前面范围查询的列放在后面。4.3 合理的主键设计主键是聚簇索引的索引键它的设计直接影响整张表的写入和查询性能。我这里给出几条实践原则。第一条主键尽量用自增整数或 BIGINT。自增主键按序插入减少页分裂。第二条尽量不要用 UUID 或随机字符串做主键。随机值导致页分裂频繁还会让聚簇索引的叶子节点存储变大因为每个二级索引的叶子节点都要复制一份主键值。第三条主键越短越好。因为每个二级索引的叶子节点都存了一份主键主键越长二级索引的存储空间越大。有人会说业务上需要全局唯一的 ID而且不想暴露自增 ID 给外部比如订单号。这种情况你可以做主键是业务 ID 或雪花 ID 生成的 BIGINT但要注意它的生成是否递增趋势。雪花 ID 整体是趋势递增的对比纯粹的 UUID 字符串还是要好很多。如果确实用了随机 ID可以考虑用INSERT DELAYED或者异步批量插入的方式缓冲一下降低页分裂带来的性能损耗。4.4 分析一条 SQL 的索引命中情况空谈理论没有意思我拿一个实际例子拆解一遍。假设有一张订单表CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_created (user_id, created_at), KEY idx_order_no (order_no) ) ENGINEInnoDB;第一条 SQLSELECT id, order_no, amount FROM orders WHERE user_id 1001 AND created_at 2024-01-01 00:00:00;这条 SQL 能用上idx_user_created。user_id走等值匹配created_at走范围匹配联合索引的两个列都发挥了作用。二级索引上就能找到id和order_no吗不能order_no不在索引里所以还需要回表拿order_no。如果我把 SQL 换成SELECT id, order_no FROM orders WHERE user_id 1001 AND created_at 2024-01-01 00:00:00;这个还是回表因为二级索引叶子节点只有(user_id, created_at, id)没有order_no。但如果我把索引改成(user_id, created_at, order_no)那么这条 SQL 就能完全走覆盖索引连聚簇索引都不用查。第二条 SQLSELECT * FROM orders WHERE order_no NO123456;这个走idx_order_no单列索引查到主键id后回表取整行数据。单列索引如果能覆盖查询就最理想但SELECT *基本不可能覆盖所以回表是常态。第三条 SQLSELECT * FROM orders WHERE created_at 2024-06-01 00:00:00;这条 SQL 用不上idx_user_created。因为created_at不是联合索引的最左列它没法单独走这个索引。如果没有其他索引MySQL 只能全表扫描。这就是最左前缀原则的一个典型场景你想在时间范围上做查询但没有在created_at上建独立的索引。5. 索引失效场景与避坑指南5.1 函数操作导致索引失效这是索引失效里最容易踩的坑。如果你在 WHERE 条件中对索引列做了函数操作MySQL 大概率会放弃索引。看这个例子SELECT * FROM orders WHERE DATE(created_at) 2024-11-01;created_at字段虽然建了索引但DATE()函数的引入让索引列变成了函数的结果索引的有序性被破坏优化器认为走索引还不如全表扫描。应该改写为SELECT * FROM orders WHERE created_at 2024-11-01 00:00:00 AND created_at 2024-11-02 00:00:00;类似的还有对字符串列的隐式类型转换。比如order_no是 VARCHAR 类型查询条件写成WHERE order_no 123456MySQL 会把字符串转成数字再比较隐式转换导致了函数处理索引失效。正确的做法是查询参数保持和字段类型一致。有人会问那如果我就是需要按天查 created_at 呢两个解决方案一是在建表时就冗余一个created_date日期列并建索引二是在查询里写成范围条件。总之不要在索引列上套函数。5.2 范围查询后置导致的索引列截断联合索引里如果出现范围查询范围查询之后的条件列就失效了。例如索引是(a, b, c)查询是WHERE a 1 AND b 10 AND c 3。这种情况下a走等值匹配b走范围匹配但c无法继续匹配。因为 B 树在 b 列上是先按 b 排序的在 b 的某个范围内c 并不是全局有序的。这里有例外情况。如果 b 的条件是等值匹配b 10那么 c 依然可以用索引因为 b 相等的情况下 c 是有序的。MySQL 8.0 的索引跳跃扫描Skip Scan也能在一定程度上突破最左前缀的限制但它是有限度的依赖统计信息和优化器的选择不建议作为业务设计的依靠。我给你的建议是在定义联合索引时把等值条件放前面范围条件放后面最后再考虑排序或覆盖的需求。如果范围条件的列在很多查询里都是第一过滤条件单独给它建一个索引可能是更合理的方案。5.3 LIKE 模糊查询与 OR 的坑LIKE abc%可以用索引因为前缀是固定的B 树能沿着有序的索引树找到以 abc 开头的区间。但LIKE %abc和LIKE %abc%用不上索引因为通配符在最前面无法利用索引的有序性做定位。这里的避坑技巧是如果你确实需要后缀模糊搜索可以用覆盖索引加上反向冗余字段或者考虑改用全文索引。多数业务场景下LIKE %abc%出现在搜索功能里建议引入全文检索或搜索引擎。OR的坑在于如果 OR 两侧的字段不是同一个索引或者有一侧没索引优化器可能直接选全表扫描。例如WHERE user_id 1001 OR status 0user_id有索引status没有那么这条 SQL 大概率不走索引。改成UNION ALL或者在status上也建索引情况才会改善。5.4 深分页导致的慢查询索引失效不只是“没用上索引”有时候是“用上了但还是慢”。深分页就是典型场景。LIMIT 1000000, 20如果表的数据量很大MySQL 会先扫描到第 1000020 行再丢弃前 100 万行。二级索引匹配后回表 100 万次这个查询慢得理所当然。解决深分页有几个方案。第一个是延迟关联先只在二级索引上拿到主键 id 列表再和聚簇索引做关联取完整数据。第二个是基于游标的分页也就是记录上一页最后一条记录的某个唯一键下一页直接用WHERE id last_id LIMIT 20。第三种是针对具体业务场景加时间维度过滤把分页范围控制在一个可接受的时间窗口内。延迟关联的写法类似这样SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE user_id 1001 ORDER BY id LIMIT 1000000, 20 ) tmp ON o.id tmp.id;这个写法的妙处在于子查询只在二级索引和主键 ID 上操作不回表天然轻量拿到 20 个主键后再一次性回表取完整数据回表次数从 100 万次降到了 20 次。6. 常见问题与排查技巧实录6.1 我建的索引为什么没生效这是一个在生产环境反复出现的问题。你在某个字段上建了索引EXPLAIN一看 type 还是ALL或者 possible_keys 里就没有你的索引。排查思路按以下顺序走。第一步检查字段类型是否匹配。VARCHAR 和 INT 之间的隐式转换是最常见的原因。第二步检查是否对索引列做了函数或计算操作。第三步检查 WHERE 条件的顺序和联合索引的最左前缀是否匹配。第四步检查表的统计信息是否过旧有时候ANALYZE TABLE能解决问题。第五步检查数据的分布。如果优化器判断索引选择性太差比如性别字段90% 都是同一个值它宁愿全表扫描也不走索引。最后一点常常被忽略。字段只有两个值的时候走索引需要大量的随机 IO还不如顺序扫描来得快优化器的判断在大多数时候是正确的。你想强制走索引可以试试FORCE INDEX但我不建议长期依赖更合理的做法是调整查询条件或者换一个选择性更高的索引。6.2 EXPLAIN 关键字段的解读EXPLAIN 是排查慢查询的第一利器。不要只盯着 type 是不是ALL多看几个字段的组合。type字段的常见值从好到差依次是system、const、eq_ref、ref、range、index、ALL。ref和range说明用上了非唯一索引的部分匹配或范围匹配index表示全索引扫描不一定很慢但通常也意味着索引体积大ALL表示全表扫描这是我们要尽量避免的。key字段表示优化器实际选用的索引rows字段是优化器估算的需要扫描的行数filtered表示经过条件过滤后剩余行数的百分比。这几个字段要结合起来看。比如type是refrows是 100说明扫描了 100 行如果type是ALLrows是 1200 万那就是一次全表扫描不用想都知道慢。还有一个Extra字段很值得看。出现Using index说明用了覆盖索引出现Using filesort说明需要额外的排序操作这就提醒你可能需要优化 ORDER BY 字段的索引设计出现Using temporary则需要排查是不是 GROUP BY 或 DISTINCT 导致的临时表。6.3 一条慢 SQL 的优化全过程我分享一个实际案例。有一张流水表数据量大概 2500 万行业务上需要按用户 ID 和时间范围拉取流水列表。原始 SQL 如下SELECT * FROM payment_logs WHERE user_id 88888 AND create_time BETWEEN 2024-10-01 00:00:00 AND 2024-10-31 23:59:59 ORDER BY id DESC LIMIT 10;最初这张表只有一个主键索引(id)这条 SQL 跑了 2.8 秒才返回结果。EXPLAIN显示 type 是ALL扫描了 2500 万行。优化方案分为两步。第一步建联合索引(user_id, create_time)。改了之后查询先按user_id定位再在create_time上做范围过滤type 变成了rangerows 估算从 2500 万降到了 300 多。查询时间从 2.8 秒降到了 80 毫秒。第二步业务上还需要在结果集里按id排序。由于联合索引的最后一个列不是idMySQL 需要做一次filesort。数据量降到 300 行后filesort 的代价已经很小了。但如果你要拉取大范围的数据并排序建议把联合索引设计成(user_id, create_time, id)让排序也在索引层面完成。这个案例有很强的代表性先缩小扫描范围再解决排序和回表问题。大多数慢查询只要你把扫描行数降下来哪怕后续有点 filesort 或者回表性能都在可接受范围内。6.4 维护索引的注意事项索引不是建完就完事了。日常维护里有几个点需要注意。第一个是冗余索引。idx_a存在的情况下你又在(a, b)上建了idx_ab那么idx_a基本是冗余的。因为 MySQL 可以用idx_ab的最左前缀a来覆盖idx_a的查询场景。冗余索引白白增加写入开销和存储空间需要定期清理。可以通过sys.schema_unused_indexes视图找到从未被使用的索引。第二个是重复索引。同一个字段被重复建了两遍数据字典里不会报错但写入时会维护多棵 B 树性能和空间都受影响。第三个是索引分裂频繁的大表。如果表的数据量已经非常庞大主键近似随机分布写入性能会持续恶化。这种情况要考虑归档旧数据、调整主键生成策略或者引入分布式中间件做数据分片。第四个是ALTER TABLE ... ADD INDEX的锁问题。在 MySQL 8.0 中ALGORITHMINPLACE可以避免复制整个表数据但依然会占用一定的读写锁时机。建议在低峰期执行或使用pt-online-schema-change在线变更工具。7. 写在最后索引设计里取舍才是核心能力B 树的原理翻来覆去就那几张图矮胖的树、串起来的叶子节点、聚簇与二级索引的回表关系。原理本身不复杂真正复杂的是一张业务表里怎么权衡存储、写入、查询、排序各方需求设计出最优的索引组合。我在实际工作中的体会是索引设计不是静态的它是动态迭代的。上线前你以为的查询路径和上线后真实流量打出来的行为很可能不一样。所以不要怕一开始建的索引不完美只要把EXPLAIN和慢查询日志的监控跑起来发现问题快速调整就好。反而是那些“上线前什么都不建出问题后一次性加一堆索引”的做法才是最危险的。还有一个建议每次给一张大表加索引之前先问自己三个问题。第一这个索引能覆盖当前业务里最热的几个查询吗第二写入放大插入、更新、删除时索引同步维护的开销能不能接受第三有没有更小巧的方案比如调整 SQL 写法、增加冗余字段或者改一下业务查询路径就能避免引入新索引这三个问题想清楚了你再动手建索引踩坑的概率会小很多。最后再分享一个调试小技巧写完一条查询 SQL 后先用EXPLAIN看一眼rows和Extra再想想业务方对响应时间的预期。如果rows已经很小但查询还是慢问题大概率出在filesort或者using temporary上往这个方向排查思路就对了。