1. 写在前面一次让我失眠的慢查询先讲一段真实经历。前两年我负责一个电商系统的订单模块平时数据量大概几十万行查询都挺快。结果到了某次大促预热运营要做一波用户分层推送跑一个“查最近30天有订单但没复购的用户列表”的需求。开发同学写了一条SQL在本地测试数据上跑得飞快但上到生产库前端接口直接超时数据库CPU飙到80%。我当时拿到那条SQL第一反应是“这么简单的条件怎么慢成这样”。结果EXPLAIN一看全表扫描扫描行数四百多万临时表、文件排序一个不少。后来花了一个下午从索引调整、改写JOIN、优化分页三步入手把这条查询从8秒压到了0.3秒。那个下午之后我最大的感受是MySQL查询优化这件事不是背几条优化口诀就完了你得真正理解数据是怎么被查找、被读取、被排序的。所以这篇内容我不打算写成“MySQL优化大全”之类的罗列帖而是按一次真实的排查和优化流程来拆解——从怎么定位慢查询到怎么分析执行计划再到索引设计、SQL写法、分页与JOIN优化最后是我踩过的一些坑。你可以当它是“一条慢查询从发现到上线的完整抢救记录”。无论你是刚接触数据库的初级开发还是已经写了几年SQL但没系统梳理过的同学只要你想让自己写的查询经得起数据量增长的考验这篇内容应该都能帮到你。2. 定位问题慢查询日志与EXPLAIN的正确打开方式2.1 先回答一个关键问题你的库里到底有没有慢查询很多团队到了线上接口超时才想起来看数据库。其实MySQL自身提供了相当完整的观测手段其中最基础的就是慢查询日志。我建议把这件事做成“默认配置”而不是“故障时才开启”不然事故一来你连现场都没有。慢查询日志的核心配置就三个参数slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1long_query_time设为1秒意思是执行时间超过1秒的SQL会被记录下来。这里有个细节单位是秒而且只记录“执行时间超过阈值”的语句不包括等待锁的时间。所以如果你遇到一条SQL本身执行只要0.2秒但排队等了2秒它不会进慢日志这是很多人排查时容易忽略的点。日志开启后我习惯用mysqldumpslow来做初步聚合mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这条命令会按总执行时间排序把最慢的10条SQL汇总出来。-s t表示按时间排序-t 10表示取前10条。汇总结果会合并同类的SQL比如只差查询条件的也会归为一组方便你快速找到“病根”。2.2 EXPLAIN只看结果不够要会看执行路径拿到慢SQL之后第一步不是猜而是用EXPLAIN看执行计划。我见过太多人只看一个type字段就下结论实际上EXPLAIN里值得关注的信息相当多。EXPLAIN SELECT order_id, user_id, amount FROM order_table WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10;这条结果里几个重点字段我逐个说type访问类型。从上到下分别是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描这是最需要警惕的。key实际用了哪个索引。如果为NULL说明没有用索引。rowsMySQL预估需要扫描的行数。这个数值是预估的不一定准但能反映大概量级。Extra这里面信息量最大。看到Using filesort说明排序没有走索引看到Using temporary说明用了临时表看到Using where一般说明存储引擎层返回后 Server 层再做条件过滤看到Using index反而是好事表示覆盖索引生效。我列一个自己的速查表这几年排查慢查询全靠它EXPLAIN特征问题常见原因typeALL全表扫描缺少索引、索引失效Using filesort排序未走索引ORDER BY字段未建索引Using temporary使用临时表GROUP BY/DISTINCT/子查询不当rows很大但key有效索引区分度太低字段重复率太高keyNULL没用到索引函数操作、隐式类型转换Extra出现Using index覆盖索引生效查询列都在索引中2.3 我建议你养成的5类必查场景实战中故障通常集中在几个固定场景。与其等报警不如定期把下面这几类SQL拉出来审一遍分页查询尤其是深度分页LIMIT的偏移量越大越危险。关联查询JOIN字段两侧是否都有索引驱动表和被驱动表选得对不对。排序查询ORDER BY字段是否真的能走索引。聚合统计GROUP BY或DISTINCT是否触发了临时表。范围查询 排序组合很多联合索引死在这个组合上。一句话优化之前先精确锁定问题不要靠猜。慢查询日志和EXPLAIN就是你最好的“病灶扫描仪”。3. 核心手段索引设计与失效场景的深度拆解3.1 索引设计三条前置准则很多优化文章一上来就讲B树原理但实际做优化时光懂原理不够还得有可操作的准则。我个人把索引设计收敛成三条第一查询频率决定索引优先级。不是每个字段都适合建索引而是按“查询需求”来。最优先考虑WHERE条件里的等值匹配和排序字段范围查询次之像那种 SELECT 列表里的字段只在做覆盖索引时考虑。第二索引不是越多越好。每多一个索引写入时要多维护一棵B树更新、插入成本都会上升。我见过有人给一张表加了七八个索引结果查询没快多少写入反而变慢。第三联合索引要符合最左前缀原则。比如你要建一个(user_id, status, create_time)的联合索引那查询条件必须从user_id开始匹配才有意义跳过第一列直接用status或者create_time这个索引基本就废了。3.2 覆盖索引性价比最高的优化手段之一覆盖索引是我工作中用到频率最高的优化技巧。所谓覆盖索引就是SQL要查的列全部包含在某个索引里这样查询可以直接从索引中取值不需要回表查数据行。举个例子订单表order_table有user_id和amount两个字段我经常要按用户查订单金额。如果只建(user_id)单列索引查询时找到用户ID后还得回表读amount。但如果建的是(user_id, amount)联合索引那这条查询直接走索引就能拿到amount省了一次回表。-- 建联合索引 ALTER TABLE order_table ADD INDEX idx_user_amount (user_id, amount); -- 这条查询会走覆盖索引 SELECT user_id, amount FROM order_table WHERE user_id 12345;EXPLAIN里出现Using index就代表覆盖索引生效。我实测过一个场景五百万行的订单表同样的查询条件覆盖索引比单列索引回表快了接近一倍。但要注意覆盖索引不是建的列越多越好因为联合索引本质也是一棵B树索引体积太大反而增加IO成本。一般建议只覆盖高频查询要用的列。3.3 索引失效的6个典型场景这是整个索引部分最值钱的段落。我下面列出的每一个场景都是我在真实排障时遇到过的。场景一隐式类型转换。最常见也最“阴”的。表里user_id是varchar类型但查询条件写WHERE user_id 12345数字没有引号MySQL会把字段转换为数字再比较导致索引失效。解决办法是规范写SQL字段是varchar就加引号。场景二对索引列做函数操作。比如查最近30天的订单有人会写WHERE DATE(create_time) DATE_SUB(NOW(), INTERVAL 30 DAY)这么写确实能查出数据但create_time字段被函数包了一层索引就废了。正确写法是用范围查询WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY)场景三前导模糊查询。LIKE %关键字%这种写法因为不知道开头字符B树无法定位起始位置只能全扫描。若非用不可考虑全文索引或者存一个倒排。场景四联合索引不满足最左前缀。我在3.1里已经提过这里强调一下很多人建了联合索引但写SQL时跳过了第一列等于白建。场景五OR连接条件。例如WHERE status 1 OR user_id 12345哪怕两侧都有单列索引优化器也可能选择全表扫描。原因是需要对两个索引的结果做合并代价未必比全表扫低。解决办法是拆成两条SQL用UNION ALL或者用IN改写。场景六范围查询后接索引列。联合索引(age, status)查询WHERE age 20 AND status 1。因为age是范围条件后面的status索引列无法精确定位只能对范围内逐条过滤。这种情况下还不如改成(status, age)反而能两个字段都用上索引。3.4 联合索引的字段顺序一次优化前后的对比我拿一个真实的例子来展示联合索引字段顺序的影响。某订单表要支持一个查询筛选“某个用户、某个状态、按创建时间倒序”的订单。一开始建的索引是(user_id, status, create_time)查询长这样SELECT order_id, amount FROM order_table WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 10;这时候索引其实是能用的但问题出在ORDER BY create_time DESC上。create_time在联合索引最右侧前面被user_id和status两个等值条件定位后剩余数据已经是一个较小的集合排序只要在内存里完成性能还可以。如果换成WHERE user_id 12345 ORDER BY create_time DESC一个等值条件一个排序字段那么联合索引(user_id, create_time)就是最理想的设计——等值条件定位排序字段天然有序连filesort都省了。这种细微差别在实际开发中很容易被忽略但影响很大。所以联合索引字段顺序的通用原则是等值条件放前面范围条件放后面排序字段要么紧跟等值条件要么单独考虑。4. SQL写法层面的实战优化分页、JOIN、子查询与聚合4.1 深度分页问题LIMIT 100000, 10为什么慢得离谱分页是业务里最常见的需求也是慢查询高发区。很多人写分页时没意识到底层发生了什么。MySQL执行LIMIT 100000, 10时并不是“直接跳到第100000行然后取10条”而是先扫描前面的100000行全部丢掉再往后取10条。这个“扫描并丢弃”的过程数据量一大就是灾难。针对这个问题的优化手段我推荐两种。方法一延迟关联Deferred Join。先利用覆盖索引找到目标行的主键ID再用主键ID回表取数据SELECT t.order_id, t.amount FROM ( SELECT order_id FROM order_table WHERE user_id 12345 ORDER BY create_time DESC LIMIT 100000, 10 ) tmp JOIN order_table t ON tmp.order_id t.order_id;内层查询只查order_id如果order_id是主键或者有覆盖索引扫描和排序的成本会大幅降低外层再通过主键回表只取那10行数据。方法二书签法Seek Method。适合按时间线或递增主键的列表。记录上一页最后一条数据的位置下一页直接用条件过滤SELECT order_id, amount FROM order_table WHERE user_id 12345 AND create_time 2024-11-01 10:00:00 ORDER BY create_time DESC LIMIT 10;这个方案的核心思路是不要用偏移量而是用“上一页的终点”作为这一页的起点。翻页越深性能优势越大。缺点是如果中途有数据插入或删除页码可能略有偏差适合资讯流这种对精确页码不敏感的场景。4.2 JOIN优化小表驱动大表不是口号是机制JOIN性能问题本质是“驱动顺序”和“关联字段索引”的问题。MySQL的关联查询一般是先选一张表作为驱动表然后对另一张表做关联查找。驱动表通常希望是“小表”因为查询过程中驱动表会被访问多次小表访问成本低。这里我得纠正一个常见误解很多人说“小表驱动大表”是指 SQL 里左边的表要小其实MySQL优化器会自己评估。但优化器评估依赖统计信息如果统计信息不准确或者JOIN条件两边都没有索引它也可能做出糟糕的决定。JOIN优化的实操要点关联字段两端必须有索引这是最基本的。MySQL官方文档里也建议为JOIN的关联列建索引。能用等值JOIN就不要用非等值JOIN。非等值关联会触发嵌套循环性能断崖式下降。避免多级JOIN。超过三张表关联时建议拆成两步查询在应用层做数据组装。我做过一次优化三条SQL分别查订单、用户、商品然后在应用层用Map合并查询总耗时从最初的4秒降到了0.6秒。很多人觉得一次JOIN搞定最简单但复杂业务场景下拆查询内存合并反而是高性价比方案。4.3 子查询和IN的优化什么时候该改写IN子查询和EXISTS的选择网上讨论很多。我的实践经验是子查询结果集小、外层表大时用IN通常更合适因为MySQL会先把子查询结果物化然后对外层表逐行匹配。外层表小、子查询表大时用EXISTS更合适因为对外层每行只判断一次是否存在可以及早终止。但更常见的问题是不当的IN列表会引发临时表和长列表扫描。比如SELECT * FROM order_table WHERE user_id IN (SELECT user_id FROM user_vip WHERE expire_time NOW());如果user_vip很大这个IN子查询可能被优化成半连接或物化效果不稳定。我一般习惯改写为JOINSELECT o.* FROM order_table o JOIN user_vip v ON o.user_id v.user_id WHERE v.expire_time NOW();改写后优化器对JOIN路线的掌控更明确配合索引性能一般优于子查询。但也要注意JOIN可能因为重复匹配导致结果变多如果user_vip里user_id不唯一要用DISTINCT或GROUP BY去重。4.4 ORDER BY与GROUP BY如何让排序走索引ORDER BY走索引的条件是字段顺序和索引列完全一致方向和索引的升降序一致。例如索引(a, b)ORDER BY a, b能走索引ORDER BY a DESC, b DESC也能走但如果a升序b降序部分版本就没办法了。GROUP BY本质上也有排序操作所以它对索引的要求和ORDER BY类似。一个常见优化是把分组字段和统计字段放到联合索引里让排序直接走索引。另外提醒一下能指定NULL排序规则吗MySQL 8.0里可以ORDER BY create_time DESC NULLS LAST这种写法但在旧版本不行。如果业务对NULL位置有要求建议存入一个默认值或用表达式转换避免排序时出现意外行为。4.5 COUNT查询的效率差异COUNT(*)和COUNT(1)、COUNT(字段)的区别也算高频面试题了。MyISAM引擎维护着表的精确行数COUNT(*)直接返回InnoDB因为事务隔离得实时扫描统计所以大数据量下COUNT(*)也会慢。实践经验上有两个优化方向对超大数据表别直接COUNT(*)改成从汇总表查计数或者定时把计数刷到缓存。如果只是判断“是否存在”用EXISTS或者LIMIT 1代替COUNT命中即返回避免全扫。-- 判断是否存在优于 COUNT(*) SELECT 1 FROM order_table WHERE user_id 12345 AND status 1 LIMIT 1;5. 实操复盘从一次线上慢查询到稳定上线5.1 背景某跨平台系统的订单查询突然变慢某次值班监控告警一个订单列表接口从平均200ms涨到了5秒。这个接口的业务是按用户ID查询某段时间内的订单列表支持状态筛选和分页。数据量这时已经到了480万行。初步定位用了两步先看慢查询日志抓到那条SQL再用EXPLAIN查看执行计划发现typeALL全表扫描并且有Using filesort。原SQL大致是SELECT order_id, user_id, status, amount, create_time FROM order_table WHERE user_id U20241001 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 20;表上原本只有主键索引其他字段都没有索引。480万行全表扫描文件排序不快才怪。5.2 优化过程从索引到SQL逐层收网第一步加联合索引。因为查询条件里有等值匹配user_id、范围匹配status、排序字段create_time我把索引设计为(user_id, create_time, status)。为什么把status放最后因为它是一个IN条件多值匹配放中间反而可能导致排序字段用不上。实测调整后文件排序消失执行计划变成Using index condition。第二步改造分页。虽然这次查询只取20条偏移量不大但如果用户翻到很深的页还是会退化。我把原本直接LIMIT 200000, 20的分页改成了书签式前端记录最后一条的create_time下一页直接传这个时间点。第三步覆盖索引优化。将查询列里高频会用到的amount也加进索引让部分统计类查询直接走覆盖索引。这一步需要权衡写入性能但因为订单表是只读扩展型写入所以可以接受。最终优化结果接口从5秒降到0.4秒慢查询日志里这条SQL彻底消失。5.3 一次优化前后执行计划对比优化前的EXPLAIN关键信息字段优化前优化后typeALLrefpossible_keysNULLidx_user_time_statuskeyNULLidx_user_time_statusrows4820000231ExtraUsing where; Using filesortUsing index conditionrows从482万降到231差了四个数量级。这就是索引带来的真实差距。6. 排查实录慢查询整治的边界与陷阱6.1 统计信息不准确导致执行计划走偏加了索引数据量不大时执行计划看着正常但某天数据量突然涨到千万级优化器可能还是选择全表扫描。原因一般是统计信息过旧。解决方式是执行ANALYZE TABLE order_table;更新统计信息。我遇到过两次类似情况一条SQL前一天还走索引后一天就全表扫描了原因就是大批量导入数据后没更新统计信息。6.2 优化器不一定按你的思路来你以为SQL写得“规范”了就万事大吉但优化器会基于代价模型做决策。有时候你明明写了符合索引的查询EXPLAIN却告诉你它选择了一个你没想到的执行路径。这种情况下不要硬刚试试MySQL 8.0提供的OPTIMIZER_TRACE或者EXPLAIN ANALYZEEXPLAIN ANALYZE SELECT order_id, user_id, amount FROM order_table WHERE user_id U20241001 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE会返回实际执行时间、扫描行数、各阶段耗时比普通EXPLAIN更接近真实。用它来对比候选SQL的执行代价选择让优化器“觉得”最划算的方式效果比强行改写SQL好得多。6.3 数据量幻觉本地快不代表生产快开发环境数据量通常只有几万行执行计划和生产库千万级数据完全不一样。我见过太多次“本地测试没问题上生产就超时”的翻车现场。应对办法是把“数据量敏感性”刻在脑子里写完SQL至少问自己一句如果这张表有一千万行这条查询还能不能扛住扛不住就提前做优化而不是等事故现场给你上课。6.4 不要为一条慢查询盲目加索引最后这个坑比较常见。加索引快但会占用空间、拖慢写入。如果一张表已经有五六个索引再来一条慢查询先考虑改写SQL再考虑加索引。把加索引当“最后手段”而不是“第一反应”数据库整体健康度会好得多。7. 慢查询场景排查速查表把我的经验总结成下面这张表方便排查时直接对照。适合打印出来贴在工位旁边或者存到笔记里当检索手册业务场景典型慢原因优先优化方案按非索引字段过滤全表扫描加索引或改成有索引的查询条件LIKE前缀模糊索引失效全文索引/倒排深度分页扫描大量无用行延迟关联/书签法JOIN关联驱动表过大/无索引小表驱动大表关联列索引排序filesort联合索引匹配排序字段聚合统计临时表调整GROUP BY/索引设计IN子查询物化临时表改写JOIN判断存在COUNT全扫LIMIT 1 或 EXISTS隐式类型转换索引失效修正类型写法大批量导入后变慢统计信息过旧ANALYZE TABLE这张表不是万能药但它能帮你把80%的问题快速定位到正确的排查方向。我个人在优化工作里最深的一条体会是查询优化不能靠“背技巧”你得建立起一套自己的“观察-假设-验证”流程。每次拿到一条慢查询先看执行计划再验证数据分布然后选最小成本的改动方案改完用EXPLAIN对比和真实压测来确认效果。这样一次次循环下来慢慢就形成感觉了。最后分享一下我在实战中经常用的一个小习惯每做完一次查询优化我会把“优化前-优化后”的EXPLAIN和执行时间记录下来附上改动原因和坑点。时间久了这就是一份属于你自己的MySQL查询优化手册比任何网上教程都实用。如果你的团队有数据库负责人这份记录也能帮DBA快速理解你的意图少走很多沟通弯路。
