1. 执行计划解读从EXPLAIN的第一行开始聊SQL优化绕不开执行计划。我遇到过太多人一上来就说“我这条SQL好慢帮我看看”结果EXPLAIN出来连type是ALL、Extra里有Using filesort都没注意到。其实读懂执行计划就等于拿到了数据库优化器给你画的那张“实际路线图”——它怎么走、用什么索引、读多少行、要不要排序全写在上面。先记住一句话执行计划不是让你猜的是让你读的。MySQL的EXPLAIN你只要学会看那几列关键字段就能把90%以上的慢SQL问题定位出来。我用一个例子来带你过一遍。假设有张用户订单表结构简化成这样CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_create_time (create_time) );然后你执行的是这一条EXPLAIN SELECT id, order_no, amount FROM order_info WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;EXPLAIN出来的结果通常会有这些列我逐个拆开讲。1.1 type优化器选择了什么访问路径type这一列是最先要看的它直接告诉你MySQL打算怎么找你要的这批数据。从好到差大致是system const eq_ref ref range index ALL。system和const通常是主键或者唯一索引的等值查询最多只会回一行数据属于最优状态。eq_ref主要出现在多表JOIN的场景里被驱动表通过主键或唯一索引去匹配也很不错。ref则是普通二级索引的等值查找像刚才那条SQL如果走到idx_user_id就是ref级别。range是范围扫描比如WHERE create_time BETWEEN 2024-01-01 AND 2024-03-01或者IN (1,2,3)这种。index代表全索引扫描就是把整个索引树从头到尾过一遍虽然比全表扫描稍好但通常意味着你没把索引用好。最差的是ALL全表扫描数据一大就基本等于灾难。回到例子WHERE user_id 10086命中了idx_user_id这个二级索引type列显示的应该是ref。如果这里显示的ALL那基本可以断定这条SQL要出问题了——哪怕表只有几十万行等值查询走不上索引也够你喝一壶。这里提醒一下type为range不一定就慢。范围查询本身有代价但如果过滤掉的基数足够高range是完全可以接受的。真正要警惕的是type从ref降级成index或ALL这种降级往往意味着索引选型或者SQL写法有问题。1.2 key与possible_keys它“能用的”和“实际用的”possible_keys列列出的是MySQL在这个SQL里认为可能用上的索引key列才是它最终挑中的那个。不少人在这一步就迷糊了——明明possible_keys里有索引为什么key是空的原因通常是优化器用成本模型算了一遍觉得走这个索引还不如全表扫描快。说个我踩过的坑曾经有条统计SQL条件列上有索引但那张表一共就几百行优化器脑子一转全表扫描成本比走索引回表低得多于是直接放弃索引。这时候你死磕“为什么不用索引”没什么意义问题根本不在SQL上而在表的基数上。数据量太小的时候优化器是聪明的它知道索引扫描加回表代价有多大。还有一个要注意的点possible_keys多不代表好事。如果一条SQL有多个候选索引优化器选错了这时候你才需要介入用FORCE INDEX或者USE INDEX做修正。但我更建议你先看统计信息是不是过期了而不是一上来就强制索引。1.3 rows与filtered优化器心里的“工作量”rows是优化器估算的需要读取的行数filtered是经过WHERE条件过滤后剩余的比例。两个乘起来大约就是最终返回数据的预估量。这两个值不是真实值是抽样统计和成本模型的估算结果。所以当你看到rows忽大忽小和实际返回行数差了很多的时候多半是表的统计信息过期了。我通常的做法是跑一下ANALYZE TABLE order_info让优化器重新“掂量掂量”很多诡异的索引不生效问题其实跑个ANALYZE就治好了。再补充一个容易被忽视的细节ORDER BY create_time DESC和LIMIT往往会让优化器选择不同的执行路径。如果where条件的索引是idx_user_id排序又会触发filesort它就会在两个方案之间博弈走索引过滤再排序还是全扫再排序。这种场景下我会单独建联合索引来处理后面索引策略部分会细说。2. 索引策略建索引之前先搞懂它怎么工作索引策略是SQL优化的核心。很多开发者对索引的理解停留在“查询慢就加索引”这一步但真正到了要设计索引的时候连“回表”“最左前缀”“覆盖索引”这些词都只是听过没真正理解。这一章我把索引背后的机制讲透。2.1 B树的“扇出”与回表成本InnoDB的索引底层是B树叶子节点有序排列并且每页默认16KB。B树牛在哪儿三层高度通常就能存几千万条数据。为什么能做到因为非叶子节点只存索引键和指针一页16KB能放下上千个键值“扇出”极大。但光有B树还不够你要知道InnoDB的两种索引结构。聚簇索引的主键叶子节点直接存整行数据二级索引的叶子节点存的是主键值。也就是说你用user_id索引查到了id还得再拿id回主键树里翻一遍整行这个动作就叫“回表”。回表是有代价的每一次回表都对应一次主键B树的随机查找。如果一行数据在主键树里离散分布物理存储的位置又不同磁盘IO就成了瓶颈。所以判断索引好不好用有一个很好的视角尽量在一个索引里就拿到你想要的所有信息回表次数越少越省事。说到随机IO用个生活中的类比你把一份资料归档在了三个不同楼层的柜子里第一次去B楼查到编号又要跑到A楼拿原件再跑到C楼查另一份文件来回上下跑自然比一次在同一个柜子里全拿到要慢得多。B树设计和IO模型就是不断在“少跑腿”这件事上做文章。2.2 联合索引与最左前缀为什么必须以“列的顺序”为前提联合索引的本质不是给每列各建一棵树而是把多列拼成一个键值放在同一棵B树里排序。比如KEY idx_user_status_time(user_id, status, create_time)实际排序规则是先按user_id排相同的再按status排再相同的继续按create_time排。所以“最左前缀”不是MySQL的规定而是B树排序逻辑的必然结果——你跳过第一列直接按第二列查询这棵树的第二列没有全局有序性索引只能退化成全索引扫描。很多人记不住这个原则我建议你反过来理解你建的联合索引本质上是在回答“我先按谁找再按谁筛”的问题最左边的列就是查询的第一站。联合索引设计的实战经验我总结成三条等值条件列放前面范围条件列放后面。WHERE user_id ? AND create_time BETWEEN ? AND ?这种联合索引应该建在(user_id, create_time)上如果反过来范围条件会让后续列的排序失去作用。高频查询中的“固定筛选列”优先。比如订单表里status经常和user_id一起出现那把status放到第二列还是第三列取决于业务里有没有更多查询条件。不要盲目建“大而全”的联合索引。列数越多占用空间越大写入时维护索引的成本也越高。三层索引可能因为你多加一列从三层涨到四层反而拖慢全表写入。2.3 覆盖索引把“回表”这件事直接省掉覆盖索引指的是查询所需的所有列在一个二级索引里全部能找到MySQL根本不需要回主键表。拿前面的例子来说如果你建了KEY idx_user_status_time(user_id, status, create_time)然后执行SELECT create_time FROM order_info WHERE user_id 10086 AND status 1;这一步会走索引但是因为select的列只有create_time恰好全部在索引里Extra字段会显示Using index回表彻底省掉。这是我在优化高并发查询里最常用的一招。像报表统计、列表展示页如果你能把SELECT的列约束到索引范围内查询性能会非常稳定。但注意覆盖索引也不是越多越好它的本质是拿空间换时间。设计时优先覆盖你线上QPS最高、最频繁的那几条SQL不要试图每条SQL都“全覆盖”。2.4 索引下推不建索引也能少回表索引下推Index Condition PushdownICP是MySQL 5.6引入的优化很多人可能没注意到。简单说以前联合索引里虽然有多列但MySQL只能在索引里定位到最左前缀后续条件要回表后再过滤。开了ICP之后引擎在遍历索引的过程中就能直接判断后面几列的条件是否满足不满足的连回表都省了。比如KEY idx_user_status(user_id, status)执行WHERE user_id 10086 AND status 1在ICP生效时status的过滤发生在索引遍历阶段回表次数会明显减少。你可以通过EXPLAIN的Extra里有没有Using index condition来判断。能在不新建索引的情况下解决问题这种优化是最划算的。3. 索引失效那些看上去没毛病却慢得离谱的SQL这一章我把它称为“避坑实录”。索引失效的原因查来查去就那几类但是每一条背后都有具体的机制在起作用。知道了“为什么失效”比死记“别这么写”要管用得多。3.1 函数运算和隐式类型转换对索引列做运算或者套函数最常见也最隐蔽。像WHERE YEAR(create_time) 2024你会发现即便create_time上建了索引type也是ALL。为什么因为索引树里存的是原始值不是函数算出来的值。你要把create_time的所有值都套一遍YEAR函数才能找出等于2024的索引根本没法做范围定位。解决方案是改写成WHERE create_time 2024-01-01 AND create_time 2025-01-01或者改成WHERE create_time BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59。隐式类型转换也是重灾区。最常见的是varchar类型的字段你用数字去比较WHERE order_no 12345。MySQL会把两边都转成浮点数或者字符串去比较一旦对索引列做了隐式转换索引就失效了。排查方法很简单看EXPLAIN里的type有没有降级或者看SQL的where条件里字段类型和传入参数类型是不是一致。3.2 前导通配符与OR的“连锁反应”LIKE %abc这种前导通配符不走索引是因为B树只能按前缀去定位前缀不确定它就只能扫叶子节点慢慢找。如果你确实要从前缀匹配可以考虑把条件拆出来单独存一列或者使用全文索引、ES这类搜索方案别为难MySQL。再说OR。WHERE user_id 10086 OR status 1这条SQL乍一看两个字段都有索引但OR的一个特点就是如果其中任何一个条件没有可用的索引整个查询大概率会退化成全表扫描。即使两边都有索引MySQL也可能选择把两个索引结果合并起来再取交集/并集成本并不低。这时候我用UNION ALL把两个条件拆开执行反而能各走各的索引。3.3 IN、NOT IN以及优化器的“固执”IN和NOT IN的问题要分情况。IN通常能走索引但如果IN列表特别大或者表数据量占整体比例超过一定程度优化器会认为扫描整表更快于是弃用索引。这个阈值没有固定值取决于统计信息和成本模型。有一种场景很气人表数据量只有几万行你建好了索引但是优化器总觉得走索引回表更慢不给用。这时候直接使用FORCE INDEX解决SELECT * FROM order_info FORCE INDEX(idx_user_id) WHERE user_id IN (...);但我还是要提醒一句FORCE INDEX是最后的干预手段不是日常操作。先检查统计信息是否过期再考虑改SQL最后才强制索引。如果统计信息正常但你仍然需要强制索引那说明你的索引设计没有完全贴合查询模式这种情况下我会重新设计索引而不是一直靠强制。3.4 联合索引设计不当导致的“半失效”联合索引的最左前缀已经说过了但实际工作中还有更隐蔽的坑。比如你建了KEY idx_user_status(user_id, status)查询条件是WHERE status 1 AND user_id 10086这样是能走索引的因为优化器会识别等值条件的顺序并自动调整。但如果你写成WHERE user_id ! 10086 AND status 1排序结构直接错位status条件跟着失效。还有就是范围条件后面的列会失效。WHERE user_id 10086 AND create_time 2024-01-01 AND status 1如果索引是(user_id, create_time, status)那status就没法用来定位了因为create_time已经是一个范围这个范围里status并不排序。想补救可以直接把status挪到create_time前面或者改用IN条件比如create_time IN (2024-01-01,2024-01-02)因为IN本质是多个等值条件的集合。4. 实战案例三类真实慢SQL从定位到优化前面把原理讲透了这一章直接看案例。我选了三个工作中特别常见的慢SQL场景从定位到优化方案全程走一遍你看完就能举一反三。4.1 深分页的“原地打转”LIMIT 100000, 20为什么这么慢有一个列表页的接口用户翻到第5000页的时候响应直接卡到3秒多。SQL大概是这样的SELECT id, order_no, amount, create_time FROM order_info ORDER BY create_time DESC LIMIT 100000, 20;EXPLAIN一看type是ALLExtra是Using filesort跑了全表和文件排序。但即使我建好create_time索引type变成range深分页的问题也没根治——因为MySQL得先读完前100020行再把前100000行丢掉只返回20条。这个“先取再丢”的过程行数越大消耗越夸张。优化方案有两种。第一种是延迟关联先把分页范围缩小到主键维度再回表拿整行。改造后的SQL长这样SELECT t.id, t.order_no, t.amount, t.create_time FROM ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 100000, 20 ) tmp JOIN order_info t ON tmp.id t.id;子查询里只需要返回主键大大减少了排序和临时存储的开销。实测下来同样翻到第5000页响应从3秒多降到了200毫秒以内。还有一种方案是“游标式分页”适合APP列表的“加载更多”场景。客户端把上一次的最后一条id传回来SQL改成SELECT id, order_no, amount, create_time FROM order_info WHERE create_time 2024-01-01 12:00:00 ORDER BY create_time DESC LIMIT 20;配合索引每页扫描的数据量非常小性能曲线几乎恒定。注意这里要用create_time做游标而不是id是因为排序字段要唯一且稳定用id排序也可以但业务上通常需要按时间倒序所以游标就是排序键本身。4.2 组合查询的“选择了错误的索引”有个运营后台的筛选接口传入的参数组合包括用户ID、订单状态、下单时间范围分页展示。最开始的SQL是SELECT id, order_no, amount FROM order_info WHERE user_id 10086 AND status IN (1, 2) AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 50;EXPLAIN发现possible_keys里有idx_user_id和idx_create_time最终选了idx_user_idrows只有几百行Extra却出现了Using filesort。问题不在选择谁而在于”选谁都解决不完“。走idx_user_id能快速定位用户但status和create_time的过滤要回表之后才做然后还要排序。走idx_create_time呢排序省了但user_id和status过滤成本又上来了。我最终建了一个联合索引KEY idx_user_status_time(user_id, status, create_time)。为什么把create_time放最后因为排序字段放在索引中能让B树的叶子节点天然有序省掉filesort。这里status用的是IN它本质是多个等值条件不会破坏后面的create_time排序。改造完再看EXPLAINtype变成rangeExtra里Using filesort消失查询时间从800多毫秒降到30毫秒左右。4.3 大表关联查询的驱动顺序与索引选择再分享一个案例。两张表做JOIN一张订单主表几百万行一张用户扩展表几万行SELECT o.id, o.order_no, u.nickname FROM order_info o JOIN user_info u ON u.id o.user_id WHERE o.create_time 2024-01-01 ORDER BY o.id DESC LIMIT 100;原SQL慢得离谱EXPLAIN发现被驱动表user_info没有走主键匹配驱动表也扫了很大的范围。JOIN的问题核心是驱动顺序MySQL会选小表驱动大表。但这条SQL里用户表本来就小问题出在order_info表需要先用create_time过滤然后再跟user_info关联。优化手段有两个方向。一个是保证被驱动表的关联列有索引user_info.id是主键已经有索引所以问题大概率还是出在驱动表过滤能力不足。我给order_info建了KEY idx_create_time(create_time)让驱动表能快速甩掉大部分数据。二是如果JOIN的字段符合同一个业务维度直接设计联合索引让过滤和关联都走索引。关联表查询里还有一个“隐式排序”的问题如果子查询或者JOIN里用了DISTINCT、GROUP BY或者ORDER BY会触发临时表或者文件排序。说到这里我再强调一句JOIN性能的瓶颈九成出在“驱动表过滤”和“被驱动表关联”这两个环节上盯死这对组合就抓住了大头。5. 慢SQL排查工具箱从发现到根治的完整流程有了前面的基础再配一套完整的排查流程和工具你才能真正建立自己的SQL优化体系。这一章我按“发现慢SQL → 抓取执行计划 → 定位瓶颈 → 设计索引 → 验证效果”这条链路把工具链串一遍。5.1 慢查询日志系统给你递的“举报信”MySQL自带的慢查询日志是最直接的发现入口。打开方式很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;生产环境不建议设得太低1秒是比较合理的起点排查完别忘了恢复。慢日志里会记录执行时间、锁等待时间、扫描行数、返回行数。我最看重的是“扫描行数/返回行数”这个比例如果一条查询扫描了100万行只返回20行那说明索引筛选性极差属于重点整治对象。配合mysqldumpslow工具可以把慢日志按执行次数和总耗时排序mysqldumpslow -s t -t 10 /var/log/mysql/slow.log这样能快速找出“执行次数最多”和“累计耗时最长”的头号嫌疑犯。5.2 EXPLAIN ANALYZE与PROFILE把执行过程“放大镜”一样看EXPLAIN本身是估算而MySQL 8.0的EXPLAIN ANALYZE会真实执行SQL并返回每步的耗时、行数、循环次数。用法是EXPLAIN ANALYZE SELECT id, order_no, amount FROM order_info WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;输出里每一行都是一个执行节点带有实际耗时和实际行数。我特别喜欢看actual time和loops这两列。比如sort节点如果耗时占比特别高那排序部分就是主瓶颈这时你的优化重点就该放在消除filesort上而不是纠结扫了多少行。如果是MySQL 5.7及更早版本用PROFILE也能达到类似效果SET profiling 1; 执行你的SQL; SHOW PROFILE FOR QUERY 1;它会列出从Sending data到Sorting result各阶段的耗时占比。之前有次排查我靠PROFILE发现一条SQL的“Sending data”占了70%时间原因居然是查询拿出来的行数太多网络传输成了瓶颈——这时根本不是索引问题而是应用端没加合理分页。5.3 统计信息与OPTIMIZER_TRACE看清优化器的“内心戏”如果走了索引但性能仍然不行或者干脆没走索引别急着操作先看看优化器是怎么想的。用SET optimizer_trace enabledon执行完SQL后查SELECT * FROM information_schema.OPTIMIZER_TRACE;这会输出优化器的完整决策链路包括全表扫描成本、索引扫描成本、各项排序代价。你能清楚地看到它为什么放弃了某个索引是rows估算太高还是回表成本估算太离谱。统计信息过期也会导致优化器判断失误。此时先做ANALYZE TABLE再次EXPLAIN对比很多时候问题自己就没了。在你调整索引、重建表结构之后也建议顺手ANALYZE避免旧统计信息继续影响决策。5.4 一套可以直接复用的排查SOP最后我把日常工作中的排查流程固定成一套动作团队里新人来了按照这个顺序走基本不会跑偏慢查询日志捞出目标SQL确认执行时间、扫描行数。EXPLAIN看type、key、rows、Extra判断当前走了什么索引、有没有排序或临时表。如果rows和实际返回行数差距巨大先ANALYZE TABLE刷新统计信息。如果优化器选错索引用OPTIMIZER_TRACE看成本计算过程。结合业务查询模式设计联合索引优先满足最常用、最高频的筛选排序组合。用EXPLAIN ANALYZE验证优化前后的实际耗时和扫描行数务必用生产环境或者接近生产的数据量去测。这套流程走下来大部分慢SQL都能在一个小时之内定位到根因。我在实际项目里踩过最深的坑就是以为建了索引就一劳永逸。其实索引不是银弹它也要占空间、要维护、写性能会受损。SQL优化是个循证的过程每一个判断都要有执行计划或数据分布支撑而不是靠直觉。真正走得远的人都是能把“执行计划”和“索引策略”这两件事融会贯通的人。