接手这个系列第二章的时候我其实犹豫了一下要不要接着写索引。毕竟“MySQL高手”这个名头看着唬人市面上讲索引的文章也早就烂大街了。但真到了实际项目里我发现自己和大多数同事拉开的差距恰恰就在索引这块。第一章我们聊了InnoDB的架构、redo log和undo log把MySQL的底层运行逻辑捋了一遍。这一章我想换个角度不聊原理书上的空理论就聊你写SQL时真正会遇到的场景为什么别人的SQL几毫秒你的要跑几秒为什么明明建了索引执行计划却不用为什么加了索引反而变慢了把这些搞明白你才算是迈进了“高手”的门槛。这篇文章适合谁已经会用MySQL增删改查但写出来的SQL在数据量上来之后开始卡顿的开发者面试被问到索引原理时只能背出“B树”三个字但又说不出所以然的求职者以及那些被慢查询日志折磨到想摔键盘的运维和DBA。这篇文章不会教你背八股文而是帮你在理解原理的基础上真正能把SQL写快。1. 高手与普通开发者的分水岭先搞懂索引到底长什么样很多开发对索引的理解停留在“给字段加个索引查询就快了”然后就开始盲猜。加索引不是不行但你要知道加完之后数据是怎么被找到的否则你连为什么快都说不清出了问题更不知道怎么排查。1.1 为什么偏偏是B树而不是二叉树或哈希表面试里被问“MySQL为什么用B树”的频率仅次于“事务隔离级别”。你要真能在五分钟内把这事讲透就已经刷掉一大批背题的人了。首先要明白MySQL的数据最终是存在磁盘上的而磁盘的随机读写比内存慢好几个数量级。所以数据库索引的设计核心目标就一个尽量减少磁盘IO次数。每访问一层树结构就可能对应一次磁盘IO树的高度越低IO次数越少。二叉树的问题在于它太容易长歪了。有序插入时直接退化成链表树高能到几十层查一次得几十次IO谁也扛不住。红黑树虽然能自平衡但数据量大了之后树高依然偏大而且每个节点只存一个键值搜索路径长。那为什么不用哈希索引哈希的等值查找确实快O(1)的复杂度但它搞不定范围查询。你写WHERE age 18的瞬间哈希索引就傻眼了它只能全表扫。而业务SQL里范围查询占比极高所以哈希索引在InnoDB里只是辅助角色成不了主角。B树是怎么解决的它每个节点可以存很多个键值而且节点之间有指针串联叶子节点形成有序链表。同样存储2000万行数据B树三层就够了。三层意味着什么查一次最多三次磁盘IO这对机械硬盘来说是完全可以接受的开销。而且叶子节点的链表有序排列让范围查询和排序变得极其自然——找到起始位置顺着链表往后走就行。1.2 一张表其实有两套索引体系聚簇索引与二级索引这是无数人踩坑的地方。很多人以为InnoDB的表就是一个行集合加个索引就是额外copy一份数据。大错特错。InnoDB的每张表都有且只有一个聚簇索引它直接决定了行的物理存储顺序。什么字段会成为聚簇索引如果你定义了主键主键就是聚簇索引没定义主键MySQL会找第一个非空的唯一索引都找不到它就在内部生成一个隐藏的rowid。所以主键设计得好不好直接影响整张表的存储结构。聚簇索引的叶子节点存的是整行数据。也就是说你通过主键查数据沿着B树走到叶子拿到的一整行就是你要的所有字段不需要再去别的地方找。而你在其他字段上建的索引叫二级索引也叫辅助索引、普通索引。它的叶子节点不存整行数据只存两样东西该索引列的值 对应行的主键值。这是一个极其重要的设计后面讲回表和覆盖索引全都依赖这个结构。1.3 回表到底回的是什么现在场景来了。你建了一张表主键是id另外在name字段上建了普通索引CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT NOT NULL, KEY idx_name (name) ) ENGINEInnoDB;执行这条SQLSELECT * FROM user WHERE name 张三;MySQL的查询步骤是到idx_name这棵B树里找到name张三的叶子节点叶子节点里记录的是name张三对应的主键id值比如id1024拿着这个id1024再回到聚簇索引的B树里找在聚簇索引的叶子节点上取到整行数据。第3步这个“拿着主键再查一次”的动作就叫回表。回表不是免费的它等于一次额外的B树查询。数据量小的时候你感觉不到数据量大了回表次数一多性能就会肉眼可见地掉下来。这里插一句题外话既然二级索引的叶子存的是主键值那主键字段的类型就会直接影响所有二级索引的体积。如果你用VARCHAR(64)做随机主键所有二级索引的叶子节点都会白白变大内存和磁盘都更紧张。这也是我一直建议用自增整数或有序的UUID做业务主键的原因——不是为了好看是真的能省空间。2. 执行计划不会骗人EXPLAIN实战解读原理聊完了咱们落到实务。判断一条SQL好不好不要靠猜直接看执行计划。EXPLAIN这条命令是MySQL给开发者最厚道的礼物它把这个查询会怎么执行、用了哪些索引、扫了多少行、有没有额外排序全都摆在你面前。很多人在执行计划里只盯着type字段看看到个ref就欢呼看到ALL就叹气。这太粗糙了。执行计划是一个整体每个字段都在说一段故事。2.1 type字段的每一档分别意味着什么我按性能从好到差列一张表你直接对着看type含义说明system表中只有一行数据极特殊几乎遇不到const主键或唯一索引等值查询最多匹配一行最快eq_ref被驱动表通过主键或唯一索引关联join驱动场景下的最优形态ref普通二级索引等值查询非唯一索引可能匹配多行range索引范围扫描大于小于、between、in都算index遍历整个索引树不常见但至少比全表强ALL全表扫描数据量大时基本就是灾难我自己排查慢SQL时看到ALL不一定立刻判死刑但要警惕看到const和eq_ref基本上心里就有底了最常出问题的反而是ref和range——它们是合格的但往往存在回表、排序、临时表这些附加成本单纯看type是看不出来的。有个真实案例。某项目里一个订单列表查询type显示的明明是range订单状态字段走了索引但接口就是慢。后来才发现这个索引筛选出的数据占全表的70%MySQL一看这种方式还不如全表扫宁可继续往下走还要做文件排序。所以type只是参考要结合rows和Extra一起看才不会被表面现象骗过去。2.2 key、rows和Extra三个最容易被误读的字段先说rows。它表示MySQL估计要扫描的行数注意是“估计”不是“实际”。MySQL的优化器在生成执行计划时不会真的去数一遍数据它是根据索引的区分度和统计信息大致推算的。很多新手问我为什么我EXPLAIN看到的rows和实际结果集差那么多因为那是估算值如果你发现估算值和实际差了一两个数量级多半是表的统计信息已经过期了需要跑一次ANALYZE TABLE刷新。再说Extra这个字段里藏的信息比type值还关键。我平时重点看这几个Using index好消息覆盖索引不需要回表。Using index condition用了索引下推回表前先过滤一部分数据。Using where在存储引擎返回之后才做条件过滤这个要注意。Using temporary用了临时表多半是排序或去重搞出来的。Using filesort文件排序注意这不是真的在磁盘上排序它指的是无法利用索引顺序、需要额外做一次排序操作。数据量一旦变大它比你想的更伤性能。Using index for group-by分组查询直接走了索引很爽。我把这几个状态的优劣程度用一个简表列出来Extra关键字是好事还是坏事处理建议Using index好说明覆盖索引生效Using index condition好索引下推在起作用Using where中性看过滤时机有时可以改成下推Using temporary坏尽量改写SQL消除临时表Using filesort坏想办法让排序列走索引2.3 索引下推一个被大多数开发者忽略的隐形优化索引下推Index Condition PushdownICP是MySQL 5.6引入的。它解决的是这样一个问题假设联合索引是(age, name)你执行SELECT * FROM user WHERE age 20 AND name LIKE 张%;如果没有ICP过程是这样的存储引擎先按age从索引里把所有符合条件的记录捞出来然后回表取整行再到server层用name LIKE 张%过滤。大量回表发生在真正需要的数据之前浪费严重。有了ICP存储引擎在索引扫描阶段就会先用name LIKE 张%过滤一次只对最后真正符合条件的那几条去回表。什么意思呢就是说过滤下推到了存储引擎层回表次数大幅减少。这个优化是默认开启的不需要你手工控制。但很多人不知道它的存在遇到慢查询时把锅甩给MySQL其实SQL写得聪明一点ICP能帮你省掉大量成本。后面实操部分我会专门演示怎么利用这个机制改写SQL。3. 亲手把一个慢查询从3秒优化到毫秒级讲了这么多原理不实操都是空中楼阁。下面我用一个真实做过的优化案例带你把整个流程走一遍。整个过程我尽量还原当时的心路历程包括中间走弯路的部分。3.1 问题现场一个典型的连表分页查询某业务系统的消息列表页面用户翻到第100页的时候接口响应时间到了3秒以上。线上反馈越来越多我接手排查。原始SQL大概长这样表名和字段做了脱敏SELECT m.id, m.title, m.content, u.name AS sender_name, m.create_time, m.read_status FROM message m INNER JOIN user u ON m.sender_id u.id WHERE m.receiver_id 10086 ORDER BY m.create_time DESC LIMIT 4900, 100;两张表的体量message表3000万行user表100万行。单看这个SQL很多人的第一反应是“查用户信息给sender_id建索引就行”但我一看表结构sender_id上其实已经有索引了receiver_id上也有索引create_time也有索引。该建的全建了为什么还这么慢这里就暴露了一个关键问题索引建了不代表能用好更不代表这条SQL整体是高效的。三棵索引树在那摆着但优化器只能选一个作为主要扫描路径另外的条件和排序怎么处理完全看它怎么拼盘。3.2 用EXPLAIN定位问题出在LIMIT太深我直接上EXPLAIN看执行计划EXPLAIN SELECT m.id, m.title, m.content, u.name AS sender_name, m.create_time, m.read_status FROM message m INNER JOIN user u ON m.sender_id u.id WHERE m.receiver_id 10086 ORDER BY m.create_time DESC LIMIT 4900, 100;结果里的关键信息message表的type是ref走了receiver_id索引rows估算约50万。user表的type是eq_ref通过主键关联。表面看似乎还行但Extra里赫然写着Using filesort。问题已经很明显了。优化器选择了用receiver_id索引定位数据扫出50万行符合条件的记录然后把这些行主键回表拿到create_time再做一次文件排序最后才取出4900到5000这100行。整套流程里最伤的就是这个组合大范围扫描 回表 文件排序 深分页。LIMIT 4900, 100意味着前面的4900行全被扫了一遍、排了一遍然后被丢掉。数据深翻时这个成本成倍放大。3.3 第一轮优化覆盖索引消除回表我做的第一件事是给message表建一个联合索引把查询条件、排序字段、需要返回的id都装进去ALTER TABLE message ADD INDEX idx_receiver_time (receiver_id, create_time, id);为什么这么建因为二级索引的叶子节点本身就包含索引列的值和主键id。这里我只需要根据receiver_id定位数据再按create_time排序同时把id取出来就够下一步用了。这样在索引扫描阶段就能拿到排序好的id集合而且完全不需要回表去取create_time。改完之后我把SQL拆成两步。第一步用覆盖索引查出需要的idSELECT id FROM message WHERE receiver_id 10086 ORDER BY create_time DESC LIMIT 4900, 100;这一步的执行计划变成了Using index没有回表没有filesort。索引天然有序ORDER BY create_time DESC直接沿着索引倒着走就行。这里有一条我现在还在用的硬规则凡是等值条件 排序 分页这种组合尽量让等值字段和排序字段出现在同一个联合索引里。这样排序根本不需要额外做索引顺序本身就是答案。3.4 第二轮优化延迟关联和深分页的博弈第一轮优化后第一步查询是快了从原来的秒级降到几十毫秒但整个接口还有一半时间花在第二步——用这100个id关联user表并取详情。我再做了进一步拆分利用MySQL自身对子查询的连接优化SELECT m.id, m.title, m.content, u.name AS sender_name, m.create_time, m.read_status FROM message m INNER JOIN ( SELECT id FROM message WHERE receiver_id 10086 ORDER BY create_time DESC LIMIT 4900, 100 ) tmp ON m.id tmp.id INNER JOIN user u ON m.sender_id u.id ORDER BY m.create_time DESC;这种写法叫“延迟关联”deferred join或“先缩小后关联”。它的核心思想先通过覆盖索引和良好排序在外层把所有必要的处理做完得到一个很小的数据集然后再去关联其他表、取完整数据。这句话我建议你记住了以后遇到大表分页、大表关联的优化第一反应就应该是“能不能把关联的数据集先缩小”。深分页还有一个常用杀手锏就是范围条件代替LIMIT offset。当你不需要精确跳页只需要“上一页/下一页”时可以把LIMIT 4900, 100改写成基于上一页最后一条记录的时间戳SELECT ... FROM message m WHERE receiver_id 10086 AND create_time 2024-05-01 10:00:00 ORDER BY create_time DESC LIMIT 100;这招比延迟关联更彻底因为MySQL根本不会去扫描前面那4900行。不过它要求业务上能接受“滚动分页”而不是“跳页”不少产品经理其实可以接受只是开发没有去提。3.5 优化效果对比我只把关键数据贴出来你们感受一下方案耗时执行计划关键项原始SQL约3200msALL/ref filesort覆盖索引后第一步约40msUsing index无filesort延迟关联完整SQL约80ms子查询用覆盖索引外层主键关联滚动分页约15ms范围查索引天然有序把3秒降到80毫秒其实就是两件事覆盖索引解决了回表延迟关联缩小了关联范围。我没有给系统加任何缓存也没有升级硬件纯粹靠理解执行计划做出合理的SQL改写。4. 最容易让索引失效的六种SQL写法这一节我单独拎出来写是因为它在实际开发中比原理知识更加吃香。我在代码评审里见过太多这种问题索引建得很全SQL一写索引全废。你把这些坑记住等于给自己的SQL做了一次“免疫接种”。4.1 隐式类型转换索引亲眼看着你在误导优化器最经典的场景是字段类型是VARCHAR但你在SQL里传了一个数字SELECT * FROM user WHERE phone 13800138000;phone字段是VARCHAR(20)这看起来很正常对不对问题是MySQL会先把字段值转换成数字再比较。一旦对索引列做了类型转换优化器就直接放弃索引走全表扫描。这个坑我在生产环境见过多次每次都能把一张千万级表的查询拖到5秒以上。反过来也一样。字段是整数你传字符串MySQL会把字符串转成数字这种情况反而不会让索引失效。但这属于侥幸最好别依赖这种自动转换行为。规矩很简单SQL里写的类型要和表结构里字段的类型保持一致。4.2 对索引列使用函数或运算索引拿你没办法SELECT * FROM user WHERE DATE(create_time) 2024-05-01;create_time是DATETIME你可能想着加个日期索引就能快速查。但问题是这个条件对索引列做了DATE()函数运算破坏了索引列原本的有序性和可比性优化器没法用B树去二分查找了。解决方案有两类一类是改写为范围查询SELECT * FROM user WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00;另一类是在MySQL 5.7及以上版本里给函数计算结果建“函数索引”或生成列索引ALTER TABLE user ADD INDEX idx_create_date ((DATE(create_time)));注意这里DATE(create_time)外面套了两层括号这是函数索引的语法。不过老实讲能用范围查询改写的我都会优先改写函数索引的适用面窄一些。还有个容易被忽视的相似场景WHERE create_time 0 1755561600。对列做运算和套函数是一个性质都会让索引失效。记住一个判断标准索引列是否能保持“原始形态”出现在条件里凡是在列上做了任何加工索引大概率没了。4.3 前导模糊查询%在最前面索引就断了SELECT * FROM user WHERE name LIKE %张三%;这个SQL里%在开头意味着优化器不知道从哪里开始匹配没法用B树的前缀查找能力索引自然失效。但如果改成张三%虽然也没法完全覆盖所有情况但至少能用到索引范围扫描。说实话业务上确实存在“包含”这种搜索需求。真想解决我建议你去上全文索引或者专业的检索引擎而不是用一个注定慢的LIKE %xxx%硬扛。对于数据量小的表无所谓全表扫也就几十毫秒但等你数据量上了百万每次请求都全表扫你就知道什么叫做“温水煮青蛙”了。4.4 OR连接导致“一荣俱荣一损俱损”SELECT * FROM user WHERE name 张三 OR age 25;如果name有索引age没有索引优化器会怎么处理它没法只走name索引再合并因为OR表示两个条件的并集。只要有一个条件没法用索引那这个查询就干脆全部全表扫描。解决办法是把OR改写为UNIONSELECT * FROM user WHERE name 张三 UNION ALL SELECT * FROM user WHERE age 25;但更理性的做法是先问自己这个SQL是不是真的必须用OR有时你在OR两侧的条件来自不同索引改写为UNION ALL后反而两个条件各自走索引性能立刻翻倍。前提是结果集不重复重复的话要改成UNION DISTINCT多一步去重成本。如果你用的是MySQL 8.0的SKIP SCAN或者优化器自己选择索引合并它有时也能自动处理但这是优化器的心血来潮不能当成你的托管保险。4.5 范围查询一多联合索引后半段直接失效联合索引(a, b, c)执行SELECT * FROM t WHERE a 1 AND b 2 AND c 3;这里a能用等值匹配b用范围匹配但c就废了。为什么B树索引的排序规则是先按a排再按b排最后按c排。当b是一个范围时满足条件的区间内c是无序的没法用索引继续二分查找。这是理解联合索引最核心的一条规则也是最常被追问的面试题。排列顺序原则可以说得很简单等值条件放前面范围条件放后面尽量把区分度最高的字段放最前面。如果你把区分度低的字段放前面索引扫出来的中间结果集太大后面的字段排序帮助不明显整体性能就上不去。4.6 字段状态不均衡索引选择性太差优化器宁可选全表还有一个坑它不是“索引失效”而是“索引被优化器主动放弃”。比如一个status字段大部分行都是0只有千分之一是1你查WHERE status 1时优化器可能觉得走索引要回表几千次不如直接全表扫一遍还快。这个决定是优化器自己做的不是索引坏了。处理办法要么在这种低区分度字段上干脆别建索引要么考虑业务上把status1的数据单独拆表或加一个“部分索引”思路缓解。我在实际项目里常用一个招把低区分度的状态字段和高区分度的业务字段组成联合索引让联合索引整体区分度提升这样状态筛选就能跟其他条件绑定走索引。5. 实际调优中踩过的坑与排查技巧前面讲的是方法论和操作最后这部分我整理了一些实践中容易让人怀疑人生的案例。这些坑不一定在教科书上出现但你会很真实地遇到。5.1 坑一统计信息过期执行计划完全跑偏有一次我明明是等值查询索引也建了EXPLAIN却显示ALL。排查了很久最后发现是表的统计信息已经久未更新优化器手上拿着过期的数据分布以为扫全表比走索引便宜。处理方法是跑一下ANALYZE TABLE user;它会重新计算索引的基数cardinality也就是索引的区分度数据。跑完之后执行计划立刻恢复理智。这个操作成本不高在业务低峰期执行很安全。很多公司的日常巡检脚本里都没加这一步只有等到慢查询爆发了才想到。我的建议是对频繁增删改的大表至少一周跑一次ANALYZE TABLE能避免大量“莫名其妙变慢”的问题。5.2 坑二同样的SQL线上和测试环境的执行计划不一样这不是玄学。我在某公司排查时遇到一个奇怪现象同样一个SQL测试环境毫秒级线上环境秒级表的索引结构一模一样。后来才发现线上数据量是测试环境的几百倍数据分布完全不同优化器的成本估算结果自然天差地别。所以调优SQL时不要拿小数据量环境做基准测试。我现在的习惯是在能接受的前提下尽可能复制一份线上的数据子集到测试环境然后对比执行计划、rows估算、真实耗时。数据量差一两个数量级的时候执行计划的差异是非常大的有的SQL在小数据量下看不出问题大数据量下直接崩。5.3 坑三覆盖索引不是越多越好加的索引也是要还的我见过一个同学给一张表建了十多个索引表数据几千万行每次INSERT/UPDATE都要维护全部索引写入性能惨不忍睹。索引不是免费的每多一个二级索引数据写入时都要额外维护一棵B树。建索引前你要学会问三个问题这条查询真的频繁吗查询条件里的字段真的是必要组合吗能不能用现有索引的前缀覆盖它大原则是单表索引数尽量控制在5个以内联合索引能用就别建多个单列索引。写多读少的表索引更要克制。5.4 排查技巧optimizer trace 和 profile 是慢SQL断案神器先看执行计划是第一步但遇到复杂问题EXPLAIN不够用。我会开optimizer trace让它把优化器的决策过程全部记录下来SET optimizer_traceenabledon; SELECT * FROM user WHERE name 张三 AND age 20; SELECT * FROM information_schema.OPTIMIZER_TRACE;里面会详细列出优化器考虑了哪些索引、为什么选了这个方案、成本估算到底怎么算出来的。这比看EXPLAIN的信息量高一个数量级尤其适合排查执行计划“反常”的情况。另一个工具是PROFILE用来定位SQL执行到底卡在哪个阶段SET profiling 1; SELECT * FROM user WHERE name 张三; SHOW PROFILES; SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;它能告诉你时间消耗在Sending data、Sorting result还是Creating sort index上。配合执行计划慢SQL基本无处遁形。5.5 一个小习惯SQL改写先想业务再想索引最后我想说一个方法论层面的东西。很多人一上来就想着怎么改写SQL、怎么加索引但高手的第一步是先问这个查询业务上真的需要吗比如前面那个深分页案例产品经理可能根本不需要跳转到4900页他只需要“加载更多”。同样很多复杂的统计报表业务上完全可以用离线聚合替代在线查询。你用一个索引解决不了的痛点往往可以通过系统架构调整绕过去。这不是偷懒这是对全链路成本负责。我在实际项目里至少有一半所谓的“慢SQL优化”最后都变成了接口逻辑改造、缓存引入或业务需求重新对齐而不是去硬啃索引。再分享一个亲测有效的通用步骤遇到慢SQL按这个顺序排查基本不会跑偏先抓慢查询日志或从监控平台拉出SQL原文EXPLAIN看执行计划重点是type、key、rows、Extra四个字段用optimizer trace确认优化器的选择理由判断是索引缺失、索引失效、统计信息过期还是SQL本身需要改写小步验证每次只改一个变量改完对比执行计划和真实耗时上线前在接近线上的数据量环境里压一遍别在测试环境自我感动。说实话MySQL优化的东西学不完。索引这部分你掌握得越深后面学执行计划、锁机制、主从复制时就越轻松。这一章的内容是我自己从踩坑到逐步总结出来的东西写出来也是想把这些“弯路上的教训”直接摆到你面前。如果你在实操中遇到其他奇怪的慢查询欢迎顺着这个思路去排查很多时候答案就在执行计划里等着你。
