★ FEATURED ARTICLE MySQL索引这种东西看起来就是一条CREATE INDEX的事真到生产环境里排查慢查询才发现该不该加、加到哪几列、怎么让优化器真用它每一步都有讲究。我干了十几年后端开发和数据库运维帮人处理的索引问题比我自己写过的SQL还多2025年了索引的底层模型没变但 MySQL 8.x 的优化器行为、在线 DDL 的成熟度、直方图这些新工具已经变了玩法。这篇主要聊基于 InnoDB 的 B 树索引实战姿势重点包括 where a and b 怎么建复合索引、order by 排序为什么不走索引、那些经典的索引失效场景以及主键/唯一索引的选型和索引日常维护。适合天天写业务 SQL 的开发也希望给做性能优化的 DBA 一点参考。1. 索引提速的本质B 树和一次查询背后的 IO 成本1.1 为什么索引能“神奇”地变快很多人知道索引是 B 树但没想过它到底省在哪里。InnoDB 的数据是按页存的默认每页 16KB。一张 500 万行的表假设每行 1KB那光数据就要约 8 万个页。没有索引时一条查询得从第一个页挨个往后扫平均扫一半就是 4 万个页这些页并不都在内存里大概率要从磁盘读每次随机 IO 按 10ms 算单线程跑这条 SQL 光读盘就要几十秒。生产环境里这种全表扫描的慢查询慢到让人怀疑数据库是不是挂了。有了 B 树索引后就不一样。B 树的非叶子节点只存索引键和指向下层页的指针不存完整数据所以一个 16KB 的页能塞下大量索引项。做个保守估算假设一个非叶子节点能分 500 个叉三层树就能支撑约 1.25 亿条叶子记录。也就是说千万级数据量的表走索引定位目标通常只需要 3 次左右的随机 IO。从 4 万次降到 3 次这就是索引“神奇”的真相不是魔法是数据结构把随机访问路径砍短了。这个原理我特别喜欢拿查字典类比全表扫描是从头翻到尾找目标词索引扫描是先翻目录确定页码再直接翻到那一页。唯一区别是 B 树能保证范围查询也高效因为叶子节点之间用双向链表串起来了查到起点后顺着链表往后走就行适合做BETWEEN和 这类范围条件。1.2 聚簇索引与二级索引为什么查询还有个“回表”InnoDB 表和 MyISAM 比较大的不同是 InnoDB 表的数据文件本身就按主键做成了聚簇索引结构。主键索引的叶子节点直接存完整行数据其他索引二级索引的叶子节点不存行数据而是存主键值。这个设计意味着你用一个非主键索引查询时通常是两段式先通过二级索引找到主键值再用主键回聚簇索引取整行这就是术语里的“回表”。举个例子表里有普通索引KEY idx_name(name)执行SELECT * FROM user WHERE name张三优化器会先走idx_name找到那些主键 id再拿 id 回主键索引取整行数据。回表本身有代价如果命中的行数很多比如 name 对应一万行那就要回表一万次性能照样会很差。理解了这一点就自然能理解“覆盖索引”的价值如果查询的字段全部包含在索引里MySQL 压根不需要回表直接拿着二级索引的叶子数据就返回了。判断 SQL 有没有吃到这个红利也很简单看 EXPLAIN 里 Extra 列有没有出现Using index。后面我会专门讲怎么利用这个机制把查询做快。2. 建索引之前先做这三件“准备工作”2.1 先算区分度区分度低的列别急着建我见过太多上来就在状态、性别、删除标记这类列上建索引的同学。不是说完全不能建但建之前得先算算区分度。区分度有个简单算法COUNT(DISTINCT col) / COUNT(*)。比如一张表有 500 万用户gender 只有“男、女、未知”三个值区分度大概是 0.0000006这种索引建了基本是浪费。为什么因为索引的作用是缩小扫描范围假如一个值能命中 200 万行优化器用这个索引扫描和全表扫描最终付出的 IO 成本差不了多少。在多数情况下优化器会直接放弃这个索引转头去走全表扫甚至 type 会显示 ALL。真正值得建单列索引的是像订单号、用户邮箱、身份证、手机号这类区分度接近 1 的列一两行命中走索引的价值一下就出来了。2.2 估算数据量和写入频率判断“这笔买卖”划不划算索引不是免费的午餐。每建一个索引等于隐式建了一棵 B 树插入一行数据时除了往聚簇索引里插所有二级索引也得同步维护。如果一张表有 6 个索引插入一条记录就要同时维护 7 棵树。写入越频繁、索引越多插入延迟就会被放得越大可能还会引发页分裂和大量随机写。所以建索引前想清楚数据量表只有几百行、几千行加不加索引区别不大优化器大概率照样全表扫如果表已经几百万行查询又确确实实卡在慢查询日志里那这笔“空间换时间”的买卖才划算。批量导入数据时这点尤其明显。我每次往大表里导数据习惯“先删掉与导入无关的索引导完再重建”实测导入时间能省一大半。别小看这些细节在千万级表上重建一个索引也不过几分钟但带着索引硬导可能拖到几十分钟甚至更久。2.3 看现有 SQL把高频查询列出来建索引最怕“拍脑袋”。正确姿势是先拉出业务里高频 SQL尤其是慢查询日志里反复出现的那些把 where 条件、order by、group by、join 的 on 字段都列出来再做合并设计。很多索引问题不是“缺索引”而是“索引建错了方向”——建在低频查询上高频查询反而一直全表扫。建议每次改动索引前先对目标 SQL 做一轮 EXPLAIN看清当前执行计划里的 type、key、rows、Extra。这个动作花不了几秒钟但能避免很多无意义的尝试。等真实跑过一轮之后再回头决定加哪个索引你就不会对着一条 SQL 猜测半天了。3. 复合索引实战where a and b 应该怎么建3.1 单独在两个列上建索引的坑热搜词里那个“mysql where条件a and b应该怎么建索引”的问题确实经典。很多人第一反应是在 a、b 上各建一个单列索引直觉上两个索引双管齐下总比一个强。但在 InnoDB 里大部分情况下优化器最终只会挑其中一个索引走另一个索引压根不会被用到偶发情况下会走 index merge索引合并但 merge 也不是免费的需要把两个结果集做交集性能通常不如一个精心设计的复合索引。我处理过一个真实 case一张订单表where user_id ? and status ?查询特别频繁开发分别在 user_id 和 status 上各建了一个索引。EXPLAIN 看执行计划只走了 user_id 索引估算能筛出几万行再在内存里过滤 status效果差强人意。把两个单列索引替换成(user_id, status)复合索引后同一个 SQL 的扫描行数直接从几万降到个位数性能提升了一个数量级。所以遇到多个条件并存的查询优先考虑复合索引而不是单列索引的简单叠加。3.2 设计复合索引的顺序等值放前范围排序靠后最常见的业务需求是“按照某个用户查订单再过滤状态最后按时间倒序”比如SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY created_at DESC LIMIT 20;这种情况下最合适的索引是KEY idx_user_status_time (user_id, status, created_at DESC)。为什么这么排两个等值条件 user_id、status 放在前面能一次性把数据收缩到目标用户的目标状态集合created_at 放在最后是因为它是排序字段索引天然有序正好能替代 filesort避免额外排序。MySQL 8 里已经支持降序索引DESC在索引里不是装饰。如果排序是降序就建降序如果业务里同时存在升序和降序还可以建混序索引比如(user_id ASC, status ASC, created_at DESC)。这在 5.7 及更早版本里做不到8.0 之后才能真正按所写顺序存储排序效率不一样。3.3 范围查询右边的索引会“断片”复合索引里还有个特别隐蔽的坑等值条件可以随便排但一旦中间夹了范围条件比如age 10、created_at 2025-01-01右边的字段就基本失效了没法继续用于过滤或排序。举个例子索引(type, time, name)查询SELECT * FROM log WHERE type A AND time 2025-01-01 AND name testMySQL 能用索引定位到 typeA 的所有记录在这基础上利用 time 的范围条件扫描一段索引区间但再往后的 name 条件没法在索引里继续精确过滤了只能把筛选出来的数据回表后再次过滤。这不是索引“坏了”而是 B 树索引的有序性在遇到范围条件时被切断了——你没法在一个已经按范围扫描的区间里保持对下一字段的全局有序。解法是把范围条件尽量往后放。同样是上面三条条件如果调整成索引(type, name, time)type 等值、name 等值都能在索引里精确命中time 的范围只用做最后的区间收拢效果就会好很多。等值条件在前、范围条件在后这是设计复合索引的黄金法条。4. 排序与索引order by 和 filesort4.1 什么情况下排序可以用索引order by走不走索引核心看“排序字段是否能复用索引的有序性”。如果排序字段满足最左前缀且前面的字段都是等值条件索引本身就是按这些字段排好序的自动省掉 filesort。EXPLAIN 里 Extra 列不再出现Using filesorttype 也基本会变成 range 或 ref。我从实践中总结出的判断顺序很简单先看 where 条件里有哪些等值条件再看 order by 字段能不能“接得上”。比如索引(category, create_time)查询WHERE categorynews ORDER BY create_time DESCcategory 变成等值create_time 在索引内部自然有序排序直接省掉。但如果 where 里的 category 是范围条件那 create_time 在索引中的顺序就无法保证filesort 必然出现。4.2 覆盖索引一次查询连“正文页”都不用翻前面提过覆盖索引这里详细说说。假设业务频繁执行SELECT id, user_id, status FROM orders WHERE status 1如果只建(status)索引查出来的每条记录都要回表拿 user_id 和 status。但如果把查询涉及的字段全部收进索引比如建(status, user_id, id)那走这个索引时叶子节点里已经带着这些字段Extra 会显示Using index回表完全省掉。覆盖索引最适合高频小查询。注意不是让你把 select 里的所有列都堆进索引那样会导致索引非常臃肿写入慢、空间大反而因小失大。通常只在“高频且返回行数少”的查询上做覆盖索引是一个比较均衡的策略。顺带一提这也是为什么我一直不建议业务里直接SELECT *SELECT *会把大量不需要的列拖进来基本堵死了覆盖索引的路只能回表拿整行。4.3 filesort 本身也不是洪水猛兽不少同学看到 Extra 里出现Using filesort就紧张其实要分情况。filesort 分为内存排序和磁盘排序两类如果结果集只有几十行、几百行在 sort buffer 里很快就能排完性能损失可以忽略真正怕的是要排序的数据量远超sort_buffer_size不得不走磁盘临时文件这时候延迟才会飙升。遇到排序慢我习惯先看两个点一是结果集多大二是 sort buffer 分配了多少。MySQL 8 里可以用EXPLAIN ANALYZE直接看实际执行耗时和行数比纯靠猜要准得多。调大系统级sort_buffer_size有效果但别盲目全局拉高那玩意儿是每个连接都可能分配的内存拉太高容易把内存撑爆。最后还有一招如果排序确实没办法用索引可以考虑在业务层把数据导出来排很多场景比数据库硬扛更合适。5. 索引失效场景与排查手册5.1 高频失效 case函数、隐式转换、like 三兄弟索引失效是面试题常客但真正排查慢查询时你会发现脏 SQL 就集中在那么几类模式里。函数操作最典型。比如在时间列上写了WHERE DATE(create_time) 2025-01-01等于把所有行的 create_time 都套了个函数再比较索引无法直接用于查找。正确写法是改成范围create_time 2025-01-01 AND create_time 2025-01-02。范围查询是 B 树最擅长的索引照样能走。隐式转换也非常隐蔽。表里 phone 列是 varchar但 SQL 写了WHERE phone 18612345678MySQL 会把字符串列转成数字去比较看起来差不多实际上会让该列上的索引失效。这种情况在接口传参里特别常见“明明建了索引却不走”的排查里很大一部分就栽在这。检查办法很简单看 EXPLAIN 的 warnings 有没有字符集或类型转换相关的提示。like 是老三样里的最后一个。LIKE %test因为最前面的通配符不确定无法确定索引扫描起点优化器只能放弃索引LIKE test%则可以正常走索引。如果业务确实需要后缀匹配别硬塞给 like可以想想冗余字段、倒排索引或者全文索引的可行方案。5.2 复合索引执行计划里那些不显眼的 Extra索引失效不只在单列上出复合索引上也会“半失效”。常见表现是明明索引里有三个字段EXPLAIN 的 key_len 却只显示了第一个字段的长度或者 Extra 里出现Using where这通常意味着优化器能走索引定位一部分数据但后面的字段没法在索引里继续过滤只能回表后过滤。出现这种情况先把 SQL 和索引结构摆出来对照哪几个条件是等值、哪几个是范围、哪个排序字段在范围之后。然后按“等值在前、范围居中、排序列最后”的规则重排十有八九能把问题修掉。记住Using index conditionICP和Using where是不同的ICP 是把部分条件压到存储引擎层提前过滤比纯粹回表逐行过滤聪明得多。5.3 索引明明存在优化器却不走怎么办还有一种更让人抓狂的情况索引建得好好的统计信息也没毛病但优化器就是不走索引非选全表扫。常见原因有两个。一个是表数据量太小。几千行的表InnoDB 全部缓存在内存里全表扫比走索引还快优化器当然选全表扫。这种情况不是 bug不用纠结。另一个是统计信息不准。数据经过了大量增删改但 InnoDB 的统计信息没来得及更新优化器估算的扫描行数和实际情况差太远。处理方法是执行ANALYZE TABLE table_name让优化器重新收集统计信息。MySQL 8 还支持直方图能在字段分布不均匀时帮优化器做出更聪明的选择尤其是存在数据倾斜的列上直方图价值很明显。不建议一上来就FORCE INDEX硬怼那是最后的临时手段。生产环境里我更愿意通过调整 SQL 写法或索引结构来根治问题因为FORCE INDEX一旦编码进业务代码后面数据量变了、统计信息变了它还会继续强行走那个方案迟早出大问题。6. 主键索引、唯一索引与索引日常维护6.1 主键索引和唯一索引到底有什么不同主键索引和唯一索引经常被搞混它们确实很像都要求列或列组合的值不重复。但有几处本质区别。第一主键是聚簇索引决定整张表的数据物理排列顺序唯一索引只是二级索引不影响表数据本身如何存储。第二一张表只能有一个主键但可以有很多唯一索引。第三主键列不允许为 NULL而 MySQL 里唯一索引列允许存在一个 NULL 值多个 NULL 在唯一索引里不算重复这是 MySQL 的一个比较特殊的实现。这也给关联查询带来潜在影响。如果另一张表用一列非主键的唯一索引去关联主表查询时往往需要回表性能上比直接用主键关联要差。所以能直接用主键的就别绕着弯去用唯一索引。6.2 UUID 主键为什么不如自增主键“友好”扩展一下主键选型的问题。自增主键写入时新记录基本是往 B 树最右侧追加页分裂的概率低写入效率稳定。UUID 或随机字符串主键则不同主键值是随机的每次插入都要在树中间找位置大概率引发页分裂、页重写和大量随机 IO。数据量几百万时可能体感不明显上了千万级写入吞吐的差距就很扎心了。所以没有特殊业务要求时我一般推荐用自增或类似单调递增的值做主键。业务需要对外暴露 ID 又不想泄露自增规律时可以单独加一个business_no并建唯一索引让它做业务查询入口主键继续用无意义自增列。6.3 索引碎片、冗余索引和在线 DDL索引用得久了也会“脏”。频繁删改数据会让 B 树页产生碎片索引空间膨胀扫描效率下降。常见的维护手段是重建索引或用OPTIMIZE TABLE整理页面。要注意对大表执行这种操作时锁和 IO 开销都不小一定要放在业务低峰期做而且先评估执行时间。MySQL 5.6 之后支持在线 DDL5.7 开始加索引默认可以ALGORITHMINPLACE, LOCKNONE也就是多数情况可以不停写操作在线加索引。但我实际操作中还是建议大表加索引前先看内容确认磁盘、IO、主从延迟情况再在低峰期执行。真要是几十 T 的超大表效率达不到预期时pt-online-schema-change 这类工具依然值得保留在工具箱里。冗余索引也需要定期清理。MySQL 8 的 sys 库里自带视图一条 SQL 就能查看从未使用的索引和冗余索引SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;我最怕的项目状态是索引越建越多“为了某些可能出现的查询”随手加索引。定期用这套视图扫一遍把明显冗余的索引删掉能减少不少写入和存储的隐性成本。7. 常见问题与排查思路实录7.1 常见问题速查表我把这些年排查索引问题的高频场景整理成了一个简表碰到类似情况可以对着查。症状可能原因排查/处理方向明明有索引却全表扫统计信息过旧、数据量太小、隐式转换EXPLAIN看 type 和 key_lenANALYZE TABLE更新统计where 两个条件只走了一个索引单列索引各自独立检查执行计划考虑改成复合索引复合索引排序没生效排序字段前出现了范围条件调整索引列顺序等值在前、排序在后索引字段被函数套住查询条件对列做了运算改写成范围条件或等值条件order by 出现 Using filesort排序字段不在索引中考虑把排序列加入复合索引或做覆盖索引写入慢二级索引过多清理无用的冗余索引批量写入时先删索引后建7.2 一个真实慢查询的完整定位过程之前处理过一张 500 万行的订单表业务反馈有个页面打开要好几秒定位后是这条 SQLSELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY created_at DESC LIMIT 20;第一次 EXPLAIN 的结果很典型索引只用了 user_id 单列索引type 是 refrows 估算近 20 万Extra 里带着Using filesort。我说难怪慢先按 user_id 扫出 20 万行再在内存里排序最后取 20 条返回大量时间都浪费在排序和无关行的读取上。当时的处理方案是把单列索引换成复合索引(user_id, status, created_at DESC)。再次 EXPLAINrows 降到几十Extra 里的 filesort 消失页面响应时间直接从 3 秒降到 30 毫秒以内。整个过程看起来简单但核心就是两点用复合索引把等值过滤精准化再用索引天然的有序性干掉 filesort。7.3 一些可以抄作业的经验技巧最后分享几个我自己坚持了多年的检查习惯。每次写完一条复杂查询先EXPLAIN看一眼重点确认 type 不是 ALL、rows 是否合理、Extra 有没有Using filesort和Using temporary。没有特殊原因typeALL走了全表就是危险信号。再有就是对sys.schema_unused_indexes做季度巡检把超过一个季度都没使用的索引标记出来和业务确认后删除。还有一条测试环境练索引设计时一定要用和生产同量级的数据。几千行的测试表和千万行的生产表优化器的选择完全不同你在一张小表上测出“索引没效果”不代表生产环境也这样反之亦然。这个坑我见过太多团队踩真等上了线才发现问题代价就不是几条 EXPLAIN 能挽回的了。
阅读完成 · 觉得有帮助？
