去年我在排查一个线上慢查询时遇到一张300万行的订单表。运营后台要按商家、时间范围和支付状态筛订单SQL跑完要2.3秒。当时的修复动作很简单——加了一个MySQL索引查询耗时立刻掉到40毫秒。但真正让我难受的不是“加索引”这个动作而是之后几周我一直在想为什么加了这个B树索引就变快换一种SQL写法索引又会失效如果只停留在“慢就加索引”的层面你迟早会被“索引失效”折磨到怀疑人生。这篇内容我分成四个大块先把“索引到底在解决什么问题”讲清楚然后把B树从二叉搜索树到B树再到B树的演进路线完整走一遍接着进到InnoDB引擎内部看聚簇索引和二级索引是怎么配合的最后给出一套可以直接上手的索引操作实战方法和踩坑记录。适合刚接触索引的初学者也适合那些会用索引但经常被“为什么不用索引”卡住的后端开发。1. 从一次慢查询开始索引到底快在哪1.1 一条SQL从2.3秒到40毫秒的现场我给那张订单表加的索引是联合索引包含商家ID、下单时间、状态三个字段。加之前EXPLAIN的结果是typeALL优化器在300万行里做了全表扫描rows显示31万——扫描范围大得吓人。加完之后同样的SQL再跑type变成了refrows直接从31万掉到几千查询时间从2.3秒变成40毫秒。这个过程中发生变化的核心不是数据总量变少了而是定位数据的方式变了。全表扫描是“把所有数据页从头翻到尾”走索引则是“沿着B树的路径直接摸到数据所在的叶子页”。理解这一点是理解索引一切知识的前提。1.2 为什么磁盘IO比数据量更值得关注数据存放在磁盘上而InnoDB引擎读写磁盘的最小单位不是一行而是一个“页”默认大小16KB。哪怕你只想查一行数据存储引擎也要把这个数据所在的整个页从磁盘加载到内存。一次磁盘随机读的耗时大约在几毫秒到十几毫秒内存随机读则是几十纳秒到几百纳秒这里差了差不多5个数量级。所以MySQL查询优化的核心目标就是减少磁盘IO次数。全表扫描300万行假如每页能装100行就要读3万个页而走索引假设B树只有3层最多读3个页就能定位到目标数据所在的叶子页再顺着叶子链表读相邻数据。一个是3万次IO一个是3次IO差距自然就是几个数量级。1.3 索引是有代价的空间和写放大索引不是免费午餐它本质上拿“空间”和“写入成本”换“查询速度”。每建一个索引InnoDB就额外维护一棵B树每次INSERT、UPDATE、DELETE都要同步修改所有相关索引。索引越多写入越慢磁盘占用也越大。所以开发里最忌讳的行为就是对着一堆不常查的字段各建一个单列索引。正确思路是为高频查询场景设计联合索引让一个索引覆盖多个查询条件。后面第4章我会详细说建索引前的判断维度这里先记住一个结论——索引是给查询设计的不是给表设计的。2. B树是怎么一步步走到今天的设计2.1 二叉搜索树和它的“退化”隐患最早大家想用树结构来加速查找二叉搜索树BST是最朴素的选择。它的规则很简单左子树所有节点值小于根节点右子树所有节点值大于根节点。理想情况下查找是二分式的时间复杂度O(logN)。但BST有个致命问题它不保证平衡。如果插入顺序碰巧是有序的比如按主键1、2、3、4顺序插入树会直接退化成一个链表查找复杂度变成O(N)。后来出现了AVL树和红黑树通过旋转操作维持树高平衡解决了退化问题。可它们依然是二叉树——每个节点只存一个键。假设有1亿条数据满打满算也要大约27层。如果这棵树长在磁盘上最坏情况就是一次SQL要触发27次磁盘随机IO这在MySQL这种高并发场景里根本没法接受。2.2 B树多叉化之后范围查询成了新痛点B树的关键改进是让一个节点不再只存一个键而是存一组键和对应的一组子节点指针。通俗点说二叉树是“一个抽屉只放一张卡片”B树是“一个抽屉放一摞卡片并且每张卡片都指向下一层的子抽屉”。这样一来同样的数据量下树的高度大幅下降一次查询需要的磁盘IO次数也大幅减少。但B树有一个隐藏痛点它的数据或者说不论是键值还是行数据分散在每一层节点上。也就是说一次单点查找可能要跨多层节点跳转更麻烦的是范围查询比如查出某一区间的所有记录B树需要做中序遍历在父子节点之间反复回溯。而相邻记录之间的物理位置也不连续很可能触发大量随机IO。范围查询是数据库里出现频率极高的操作这个问题绕不过去。2.3 B树的关键设计数据进叶子叶子串成链B树是在B树基础上的进一步改造核心变化有两个。第一个变化非叶子节点只存键和指针不存数据。这样每个页里能容纳的索引条目比B树多得多。举个例子假设主键是BIGINT占8字节指针占6字节一个索引条目约14字节一个16KB的页就能放下大约1170个索引条目。三层B树意味着根节点有1170个分支第二层每个节点又有1170个分支第三层叶子页放数据记录假设每页放100行记录三层就能支撑约1.37亿条数据。第二个变化所有数据都放在叶子节点且叶子节点之间用指针按顺序串联。单点查询从上往下最多走三层范围查询则在定位到第一个满足条件的叶子后顺着叶子链表顺序往后扫相邻记录在物理上也大概率相邻极大减少了随机IO。这就是B树在InnoDB里的核心优势单点查找快、范围查找更快、树矮可预测。2.4 三种数据结构在一张表里的真实对比把三种结构的差异放到一张表里你会看得更清楚结构单点查找范围查询写入维护磁盘IO量级BST/AVL/红黑树O(logN)但树高约27层中序遍历需回溯频繁旋转高B树O(logN)但数据分散各层跨层中序遍历随机IO多节点分裂合并中B树O(logN)树高3~4层叶子链表顺序扫描节点分裂合并低我特意把AVL和红黑树放在一起对比是因为很多资料会混淆。红黑树在内存场景里很优秀比如Linux内核用它管理进程调度但MySQL的数据存储在磁盘上核心成本是磁盘IO而不是CPU比较次数。B树把树高压缩到三四层让最耗时的磁盘访问变成常数级这才是它最终胜出的根本原因。3. InnoDB引擎里索引的两种形态聚簇与二级3.1 聚簇索引表本身就是一棵B树InnoDB和MyISAM最大的区别之一就是InnoDB把表数据和索引放在同一棵B树里。这张表的B树被称为聚簇索引它的叶子节点直接存放整行数据。换言之InnoDB的表不是“数据文件索引文件”的独立组合而是“一棵以主键为排序键的大树行数据挂在叶子节点上”。聚簇索引有非常实际的影响。只要你通过主键查数据比如WHERE id 100直接走这棵树定位叶子页取出的就是完整行不需要任何额外跳转。所以InnoDB表强烈建议必须有主键——如果没有显式主键InnoDB会找一个非空唯一索引充当聚簇索引实在找不到它还会生成一个隐藏的6字节rowid来建树。3.2 二级索引、回表与覆盖索引除聚簇索引以外其他索引统称二级索引或者叫辅助索引。二级索引也是一棵B树但叶子节点放的不是整行数据而是“索引列的值主键值”。这就引出回表的概念。比如你给mobile字段建了索引执行SELECT * FROM user WHERE mobile 138xxxxMySQL会先走二级索引找到对应的主键值再拿这个主键值去聚簇索引里定位整行数据。这一步回表本质上又是一次主键索引查询。代价比直接走主键索引多了一轮。想省掉回表就要用到覆盖索引。覆盖索引不是某种特殊索引类型而是一种查询状态SELECT需要的所有字段都包含在某个二级索引的叶子节点里。比如有联合索引(mobile, name)执行SELECT name FROM user WHERE mobile 138xxxx因为二级索引叶子节点本身就同时存了mobile和name查询直接在二级索引上完成不需要回表聚簇索引EXPLAIN里的Extra会显示Using index。这个优化在线上高频查询里非常常用能省掉一次完整的树查找。3.3 主键索引、唯一索引和普通索引到底差在哪面试里经常被问到主键索引和唯一索引的区别这里把三个概念一次理清楚索引类型约束能力是否允许NULL每张表数量叶子内容主键索引唯一且非空否只能一个整行数据唯一索引只保证唯一允许且可多个NULL可多个索引列主键值普通索引无约束允许可多个索引列主键值这里有两个容易踩的点。第一个唯一索引允许NULL而且允许多个NULL——因为MySQL把每个NULL都当作不同的值处理不参与唯一性比较。第二个InnoDB里主键索引就是聚簇索引其他索引都是二级索引。你如果做“唯一索引和非唯一索引”的性能对比会发现存储结构相似但唯一索引对查询有一定的提前终止优化写入时多一次唯一性校验性能差异不大但确实存在。3.4 为什么推荐自增主键页分裂的代价B树的叶子页是按主键顺序排列的插入新行时如果主键是自增的新数据总是追加到叶子链表尾部最省事。如果用UUID或随机字符串做主键新行的主键值会随机落在B树中间位置导致某个叶子页空间不足强行发生页分裂。页分裂的代价不光是多一次磁盘写它会让原来的页产生碎片影响后续扫描效率。对一个高写入的系统来说自增主键几乎是必须的。这个“为什么”理解了以后你会明白为什么很多规范里直接写“InnoDB表用自增主键”而不是空喊口号。4. 索引操作实战建索引、看索引、验证索引4.1 常用的索引SQL写法建索引的方式有好几种我列一下日常工作最常用的写法:-- 普通索引 CREATE INDEX idx_mobile ON user(mobile); -- 唯一索引 CREATE UNIQUE INDEX uk_account ON user(account); -- 联合索引 CREATE INDEX idx_merchant_time ON orders(merchant_id, create_time); -- 建表时直接指定 CREATE TABLE article ( id BIGINT PRIMARY KEY AUTO_INCREMENT, category_id INT NOT NULL, create_time DATETIME NOT NULL, KEY idx_category_time (category_id, create_time) );如果想给很长的字符串字段建索引比如文章标题或URL可以只用字段的前N个字符做前缀索引CREATE INDEX idx_url_prefix ON article(url(64));前缀索引能显著节省空间但代价是可能无法用于ORDER BY排序也无法完全覆盖查询列。要不要用得看字段长度和区分度。4.2 动手之前要先过的四道判断现在这条SQL值不值得建索引、该建什么索引我通常按下面四个维度过一遍。第一查询频率够不够高。如果SQL只是临时跑一次全表扫描也无所谓没有建索引必要如果是接口核心路径每秒钟跑几十次那就要认真设计索引。第二区分度够不够高。区分度指“这个字段有多少不同值”。性别、状态这类只有两三个值的字段区分度极低建索引后B树虽然有结构但过滤后仍要回表访问大量行优化器宁可全表扫描也不会走它。第三字段长度合不合理。太长的字段优先考虑前缀索引或放后面。第四能不能顺便做覆盖索引。如果高频查询的返回字段比较固定把它们一起放进联合索引里可以直接免掉回表。4.3 EXPLAIN是唯一的验证标准索引建得好不好不能靠感觉要用EXPLAIN看执行计划。最核心的几列我先说明type表示访问类型从好到差大概有system、const、eq_ref、ref、range、index、ALLkey表示优化器实际选择的索引rows是预估扫描行数Extra里会出现Using index覆盖索引、Using where回表后过滤、Using index condition索引下推等信息。举个实际例子EXPLAIN SELECT * FROM article WHERE category_id 5;如果typerefkeyidx_category_timerows很小说明索引生效。如果typeALLkey为NULL说明这条SQL在选择全表扫描。这时候不要急着骂优化器先检查SQL写法是不是让索引失效了。4.4 查看与删除索引的正确姿势日常维护里还要会查看和清理索引-- 查看表结构时能看到所有索引 SHOW CREATE TABLE user; -- 查看更详细的索引信息 SHOW INDEX FROM user; -- 删除索引 DROP INDEX idx_mobile ON user; ALTER TABLE user DROP INDEX idx_mobile;线上清理无用索引时建议在低峰期操作。InnoDB删除索引虽然不像加索引那样大动干戈但仍会影响写入。顺序是先确认没有慢SQL在用这个索引再放低峰窗口执行执行后观察一段时间慢日志。5. 索引失效场景全集每个坑都帮你踩过一遍5.1 最左前缀不是规则是B树排序逻辑的必然联合索引的排序规则是先按第一列排序第一列相同再按第二列排序依次类推。所以当你查询条件缺少联合索引最左边的列时后面的列即使有序在整体上也是无序的B树没法利用它们进行快速定位。拿(idx_merchant_time)也就是(merchant_id, create_time)来说-- 走索引等值匹配merchant_id SELECT * FROM orders WHERE merchant_id 1024; -- 走索引先等值再范围 SELECT * FROM orders WHERE merchant_id 1024 AND create_time 2024-01-01; -- 不走索引或者说优化器没法高效使用因为缺了merchant_id SELECT * FROM orders WHERE create_time 2024-01-01;同理联合索引(a, b, c)里你用WHERE a 1 AND c 3只有a列能用来定位用WHERE a 1 AND b 2a用了范围后b列的有序性在大范围里被打破b通常也排不上用场。这就是“范围查询右侧列失效”的原理。再说一个进阶点MySQL 5.6以后引入的索引下推ICP可以在部分条件下缓解这一问题。比如联合索引(a, b)执行WHERE a 1 AND b LIKE abc%虽然b列无法参与定位但存储引擎在回表之前会先用二级索引里存在的b值做一次过滤减少回表次数。EXPLAIN的Extra里出现Using index condition就是这个机制在工作。5.2 一张表看清常见的失效SQL我把线上最常见的索引失效写法整理在一张表里方便你对照自查场景SQL示例失效原因改写建议LIKE前缀通配WHERE name LIKE %张B树顺序扫描无法从中间开始改name 张 AND name 姓范围查询对索引列做函数运算WHERE YEAR(create_time)2024索引存的是原始值不是函数结果改create_time 2024-01-01 AND create_time 2025-01-01隐式类型转换WHERE mobile 13800138000varchar列与数字比较列上发生CAST改mobile 13800138000OR连接非索引条件WHERE id1 OR name张三要么全表扫要么用index_merge合并拆SQL用UNION合并结果违反最左前缀WHERE create_time 2024-01-01缺少merchant_id联合索引首列缺失根据其他等值条件补最左列对索引列做运算WHERE id 1 100索引无法定位计算后的结果改id 99这里有个重要提醒上面说的“失效”不是绝对的物理规则而是优化器成本估算的结果。同样一条SQL数据分布不同优化器可能走索引也可能全表扫描。所以任何“失效”结论都要结合EXPLAIN验证。5.3 优化器说“我不想用索引”时怎么办有时候SQL写法没问题索引也确实存在但EXPLAIN里type还是ALL。这种情况通常是优化器觉得全表扫描更便宜。常见原因有三个表行数太小扫描全表就几个页比走索引加回表更省字段区分度太低比如一个字段90%的值都是0统计信息过期导致优化器估算偏了。处理办法分几步先确认统计信息要不要更新执行ANALYZE TABLE your_table后重新EXPLAIN再检查索引设计是否符合查询模式比如查询是等式匹配还是范围匹配最后才考虑用FORCE INDEX强制走索引——这一招要谨慎它能绕过优化器但也可能引入新的性能问题。SELECT * FROM orders FORCE INDEX(idx_merchant_time) WHERE merchant_id 1024 AND create_time 2024-01-01;我的经验是FORCE INDEX更像手术刀不是日常菜刀。一旦发现需要靠强制索引才能稳定走索引优先考虑改写SQL或重新设计联合索引而不是跟优化器硬刚。5.4 一次生产环境联合索引调优复盘最后分享一个真实调优案例。运营后台有个高频查询从300万行的订单表里按商家、下单时间范围、支付状态筛选列表。原表里已经有create_time单列索引但查询依然很慢EXPLAIN出来typeindex意思是在做索引的全量扫描扫完后再逐行判断其他条件。排查过程是这样的先看慢日志定位SQL发现WHERE条件里merchant_id 1024 AND create_time BETWEEN ... AND ... AND status 1。然后看发现create_time索引虽然能范围定位但merchant_id和status都得靠回表后过滤。针对这个场景我把索引改成了(merchant_id, create_time, status)。为什么要把merchant_id放最左因为它是等值匹配区分度又高能在B树里直接裁剪出极小的搜索范围。create_time放中间负责范围定位。status放最后等值过滤剩余行。优化后type从index变成了refrows从30万掉到几百查询时间从2.3秒降到40毫秒。这个案例里最值得学习的不是“最后建了什么索引”而是“为什么排这个顺序”。MySQL联合索引的列顺序本质上是把等值条件高区分度的列放前面范围条件放中间辅助过滤放后面。顺序排对了一棵B树能撑起一套查询模式排反了索引就会沦为摆设。我个人在多次踩坑之后的体会是MySQL索引不是一个孤立的数据结构它是存储引擎、磁盘IO、SQL优化器三者之间的协调器。每次做索引优化都应该用EXPLAIN验证后再上线不要看执行时间下结论——第一次跑缓存是冷的第二次跑缓存是热的时间差会把你的判断带偏。日常巡检慢日志时如果发现某条高频SQL总是走全表扫描先别急着加索引把WHERE和SELECT字段摊开设计一个能覆盖这个查询模式的联合索引往往比狂加一堆单列索引管用得多。