先说我上周遇到的一件事。凌晨一点半值班手机被运维连环打爆说订单库的 CPU 飙到 100%支付接口大面积超时。我强撑着爬起来打开慢查询日志排在最前面的那条 SQL单次执行 47 秒而且一小时内跑了上千次等于每个请求都在拖着整个库往前走。这种场景相信每个后端开发都经历过也大概率会再经历几次。慢 SQL 从来不是数据库单方面的问题它背后往往是索引失效、查询方式不对、事务控制不合理甚至纯粹是写 SQL 的时候偷了个懒。过去十年我排查过的慢查询少说也有几百条绝大多数都能归结为某几个固定的套路。这篇文章我挑了 10 个最经典的线上案例把定位思路、优化 SQL、底层原理和踩坑经验完整拆开讲。不管你是后端开发、DBA还是正在学数据库的学生只要以后要碰 SQL这 10 个案例基本都能帮你省下大把排查时间。1. 案例1SELECT * 导致全表扫描网络和 CPU 一起崩溃1.1 问题现场一个用户信息查询接口批量查询用户资料。当时表user_profile已经有两千多万行单行数据量不小光头像 URL、个人签名、详细地址这些字段加起来就有接近 2KB。前端接口需要的人其实只有 user_id、昵称、头像三样但代码里一行SELECT *把所有字段全捞了出来。SELECT * FROM user_profile WHERE user_id IN (1001, 1002, ... 共500个id);上线初期用户量小谁也没在意。等数据量涨上去之后这个接口的响应时间从 50ms 一路涨到 2 秒以上慢查询日志里全是它的身影。1.2 定位过程先用EXPLAIN看执行计划发现typeALL也就是全表扫描。但更奇怪的是rows扫描行数其实不高只有 500 行左右因为没有对这 500 个 id 走索引MySQL 选择了全表扫描去匹配这 500 个主键值实际扫描了整张表的 2000 万行。再往深处看即使把扫描行数降下来SELECT *带来的网络传输量也非常惊人。500 行 × 2KB 1MB 的数据要从数据库搬到应用服务器还是在一个请求里。高峰期并发一上来数据库的网卡出口带宽先被打满CPU 也在持续做无谓的字段解析和拷贝。1.3 优化方案与效果SELECT user_id, nick_name, avatar_url FROM user_profile WHERE user_id IN (1001, 1002, ... 共500个id);同时在(user_id, nick_name, avatar_url)上建了一个覆盖索引。这样一来查询可以从覆盖索引直接返回结果连回表都省了。优化后接口耗时降到了 80ms 左右。这个案例的启示很简单SELECT *不只是规范问题在宽表场景下它就是实打实的性能杀手。你写起来省了几秒钟数据库和网络要为这几秒钟买单很久。注意如果 MySQL 开启了sql_modeONLY_FULL_GROUP_BY一些旧代码用SELECT *会直接报错。但即便不报错生产环境的宽表也一定要避免这种写法。2. 案例2对索引列做函数运算索引直接失效2.1 问题现场运营后台有个按天统计订单量的报表每天的查询条件都长这样SELECT COUNT(*), pay_status FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01 GROUP BY pay_status;create_time字段上明明建了普通索引但这条 SQL 就是慢单次执行 6.3 秒运营同学点一次报表要转半天圈。2.2 根因分析核心问题出在DATE_FORMAT(create_time, %Y-%m-%d)这个函数上。MySQL 的 B 树索引存储的是原始字段值当你对索引列做函数处理时优化器无法直接根据索引判断出符合条件的记录区间只能全表扫描再对每一行套函数计算。这种“对索引列做任何运算都会导致索引失效”的问题在几乎所有关系型数据库里都存在不光是 MySQL。2.3 优化方案把函数处理转移到查询条件这一侧改成范围查询SELECT COUNT(*), pay_status FROM order_info WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00 GROUP BY pay_status;改写后执行计划从ALL变成range索引生效单次查询从 6.3 秒降到 20ms 以内。如果你用的 ORM 框架是 MyBatis-Plus、Hibernate 这类写时间范围条件时要格外小心因为它们可能会自动生成对时间字段的函数处理。我的习惯是直接在 XML 里手写 SQL 条件不依赖框架帮我把日期格式化成函数。补充MySQL 5.7 以后支持生成列generated column可以把DATE(create_time)的结果存成一列并建索引但这属于改表方案适合无法改 SQL 的场景。能改写 SQL 就尽量改写。3. 案例3隐式类型转换手机号索引被偷偷废掉3.1 问题现场客服系统根据手机号查用户工单表结构如下CREATE TABLE work_order ( id bigint NOT NULL, phone varchar(20) NOT NULL, ... KEY idx_phone (phone) ) ENGINEInnoDB;运营在后台输入客户手机号查询时传过来的是数字类型SQL 长这样SELECT * FROM work_order WHERE phone 13800138000;注意这里没加单引号传入的是一个数字字面量。3.2 根因分析MySQL 的隐式类型转换规则是当字段类型和传入值类型不一致时会把字段值转换为传入值的类型再比较。这里phone是 VARCHAR传入的是数字MySQL 就会把索引列phone的值先转成数字再和13800138000比较函数运算让索引失效走了全表扫描。这种问题的特征非常明显EXPLAIN里possible_keys能看到idx_phone但key却是 NULL说明优化器知道有这个索引却用不上。3.3 优化方案把参数写成字符串SELECT * FROM work_order WHERE phone 13800138000;执行计划从ALL变成ref查询瞬间完成。这里还藏着一个更深的地雷手机号如果变成数字超过 int 范围时会被截断成错误的数字导致查询结果直接不对。我曾经见过有系统因为这个问题把 138 开头的手机号错误关联到别的用户身上比慢查询更严重。排查技巧写了个快速验证 SQLSELECT 12345 12345;返回 1说明两者相等但发生了隐式转换。只要看到EXPLAIN里possible_keys有索引但实际没用第一反应就应该是检查字段类型和传入参数类型。4. 案例4深分页 LIMIT 100000, 20越翻越慢是必然4.1 问题现场管理后台的日志列表分成一页 20 条。用户翻到第 5000 页的时候SQL 长这样SELECT * FROM operation_log ORDER BY id DESC LIMIT 100000, 20;页面打开从最初的几百 ms 变成 3 秒多再往后越翻越久。4.2 原理分析LIMIT 100000, 20 的实际执行逻辑是InnoDB 先按照id DESC扫出前 100020 行扔掉前 100000 行只返回最后的 20 行。扫描的前 100000 行完全没有被用到但扫描、排序、返回临时结果集的时间一点没少。offset 越大无效扫描就越多耗时几乎是线性增长。这个案例的关键点在于加索引解决不了问题。因为性能瓶颈不在排序本身而在“扫描并丢弃大量行”这个动作。你要做的是避免数据库做大量无效扫描。4.3 两种优化思路第一种是游标分页适合瀑布流、用户按顺序翻页的场景SELECT * FROM operation_log WHERE id 上一页最后一个id ORDER BY id DESC LIMIT 20;利用主键索引直接定位到上一页最后一条记录的位置再往后取 20 条数据库扫描的行数就等于 20。第二种是延迟关联适合必须支持用户跳转到任意页面的场景SELECT * FROM operation_log t1 INNER JOIN ( SELECT id FROM operation_log ORDER BY id DESC LIMIT 100000, 20 ) t2 ON t1.id t2.id;先走覆盖索引只查主键 ID这个阶段的扫描依然会扫 100020 行但因为只取主键字段不需要回表速度会快很多。拿到 20 个 id 后再回原表反查全部字段。我实测过1 亿行数据、offset 100 万的场景延迟关联从 3.2 秒降到 150ms 左右提升非常明显。注意游标分页不适用于“用户可以直接点击第 5000 页”的场景那是物理分页的刚需只能用延迟关联。5. 案例5OR 条件导致索引失效优化器直接摆烂5.1 问题现场营销活动查询页两个筛选条件SQL 如下SELECT * FROM campaign_order WHERE status 1 OR channel app;status和channel各有独立索引按道理两个条件都应该能走索引。但实际慢查询日志里这条 SQL 的typeALL全表扫描。5.2 根因分析MySQL 处理 OR 条件时如果某个分支没办法走索引整条 SQL 就只能全表扫描。这里两个字段虽然都有索引但优化器要考虑的是分别用两个索引去查找再做合并index_merge这个成本在某些情况下可能比全表扫描还高。尤其是表里大量数据都满足channel app的时候索引查出来的行数太多合并去重的开销非常可观。MySQL 5.7 版本的优化器对 OR 索引合并的支持很保守即便可以用也不一定选。MySQL 8.0 的优化器有所改进但实践中我还是见过大量放弃索引的情况。5.3 优化方案把 OR 改写为 UNION ALL让两个条件各自独立走索引SELECT * FROM campaign_order WHERE status 1 UNION ALL SELECT * FROM campaign_order WHERE channel app;注意如果业务上希望两条结果之间没有重复status1且channelapp的行只出现一次那就得用UNION去重。但UNION需要额外的排序去重开销建议在业务层面保证两个集合本身不重复后尽量用UNION ALL。改写后两个查询分别走ref执行计划终于不再是全表扫描。如果遇到的是同一列上的多个值直接用IN更高效SELECT * FROM campaign_order WHERE status IN (1, 2, 3);经验看到 SQL 里有 OR我第一反应不是改索引而是先问业务能不能拆成两个查询合并。实在拆不了再看能否引入UNION ALL。6. 案例6LIKE %关键词%前缀模糊匹配的隐藏代价6.1 问题现场电商后台的商品搜索框支持按商品名称模糊搜索SELECT * FROM product WHERE product_name LIKE %无线% ORDER BY sales_volume DESC LIMIT 20;product_name字段有索引但这条 SQL 依然慢到 2.8 秒。更麻烦的是商品表有 500 万行全表扫描的时候还有一次 filesort 排序复杂度直接爆表。6.2 为什么中间匹配用不了索引B 树索引的结构决定了它只能高效查找“从前缀开始的连续区间”。LIKE 无线%可以走索引优化器能顺着 B 树找到所有以“无线”两个字开头的记录。但LIKE %无线%意味着“可以是任何字符开头只要中间包含无线”这个查询条件在 B 树上完全没有锚点只能全字段扫描 逐行匹配。6.3 优化方案看业务形态来定我一般分三种处理第一如果搜索词一定出现在开头把 SQL 改成LIKE 无线%索引直接生效。很多商城的品牌筛选框其实是这种场景。第二如果业务必须搜中间字符MySQL 自带的 FULLTEXT 索引可以考虑但要处理好中文分词ALTER TABLE product ADD FULLTEXT INDEX ft_product_name(product_name); SELECT * FROM product WHERE MATCH(product_name) AGAINST(无线 IN NATURAL LANGUAGE MODE);第三如果数据量很大、搜索需求复杂那就别拿关系库硬扛把商品数据同步到搜索引擎或全文检索组件里去。我在实际项目里见过最坑的做法是业务方先LIKE %关键词%全表扫再把 500 万行结果拿到应用层做分页数据库 CPU 直接被打满。任何时候看到前缀 % 都要提醒自己这可能是全表扫描的前兆。注意MySQL 8.0 虽然支持全文索引但中文分词效果比较有限。真做商城搜索一般强烈建议独立搜索引擎不要为了省事把数据库当搜索引擎用。7. 案例7JOIN 时驱动表选错被驱动表没有索引9 秒变 0.6 秒7.1 问题现场订单列表页需要关联发货信息SQL 长这样SELECT o.order_id, o.amount, l.logistics_no, l.company FROM order_info o LEFT JOIN logistics l ON o.order_id l.order_id WHERE o.pay_status 1 ORDER BY o.create_time DESC LIMIT 20;order_info有 2000 万行logistics有 800 万行。这条 SQL 单次执行 9 秒接口直接超时。7.2 定位过程用EXPLAIN查看执行计划时第一行是order_infotypeALL第二行是logisticstypeALL两个表都在做全表扫描。这里的关键是理解 JOIN 的执行方式LEFT JOIN 时左表是驱动表驱动表有多少行被驱动表就要去索引里查多少次。logistics表的order_id没有索引导致每一行 order_info 都要全表扫一遍 logistics 表来做匹配。7.3 优化方案给logistics.order_id加索引ALTER TABLE logistics ADD INDEX idx_order_id(order_id);加完索引后再看执行计划logistics表的访问方式变成了ref查询耗时从 9 秒降到 0.6 秒。但这个案例真正想说的是能小表驱动大表就尽量小表驱动大表。如果把 WHERE 条件能过滤出很小的结果集可以先让过滤后的结果作为驱动表再去关联大表。比如先查订单子集再关联物流数据SELECT o.order_id, o.amount, l.logistics_no, l.company FROM ( SELECT order_id, amount FROM order_info WHERE pay_status 1 ORDER BY create_time DESC LIMIT 20 ) o LEFT JOIN logistics l ON o.order_id l.order_id;这种写法让驱动表只有 20 行连接成本可以忽略不计。经验两张表 JOIN 时一个最容易忽略的坑是连接字段的字符集不一致。比如 A 表 order_id 是 utf8mb4B 表 order_id 是 utf8MySQL 会做隐式字符集转换索引同样会失效。检查字符集应该和检查索引同步进行。8. 案例8大 IN 子查询物化成全表临时表没有索引能坑哭你8.1 问题现场BI 同事提了个取数需求找出近期登录过的用户的所有订单。SQL 写得很直观SELECT * FROM base_order WHERE user_id IN ( SELECT user_id FROM temp_user WHERE last_login_time 2024-01-01 );temp_user是一张导入的临时表500 万行last_login_time字段上有索引但user_id上没有索引。这条 SQL 跑了 2 分钟还没出结果。8.2 根因分析MySQL 在处理这个 IN 子查询时会先把子查询结果物化成一张内部临时表然后让外层的base_order.user_id去匹配物化表里的每一行。如果物化表本身没有索引这个匹配过程就是全表扫描。更糟糕的是子查询结果有几百万行拆出来的物化表也很大内存放不下就落到磁盘临时表I/O 开销直接放大几十倍。MySQL 8.0 虽然对子查询的优化器有所增强但遇到大结果集子查询时依然不能保证物化表自动建索引。8.3 优化方案改成 JOIN并确保被驱动表的关联列有索引SELECT b.* FROM base_order b INNER JOIN temp_user t ON b.user_id t.user_id WHERE t.last_login_time 2024-01-01;改完后给temp_user.user_id加上索引执行计划从全表扫描变成索引连接这条 SQL 从 2 分钟降到了 2 秒。还要注意IN列表本身过大比如几千个值的问题。MySQL 会把大 IN 列表膨胀成多个 OR 条件执行计划的索引合并成本很高。业务上应该分批查询一次几百个值分多次取回结果再在应用层合并。注意改写成 JOIN 之后如果子查询内部出现重复的 user_id结果集会比原来的 IN 查询多出重复行。改写之前要确认子查询是否可能产生重复值必要时加DISTINCT或业务上去重。9. 案例9单条 SQL 不慢但连接数被打满整个系统雪崩9.1 问题现场某结算系统白天突然出现大面积超时。看慢查询日志确实有慢 SQL但单条最长也就 3 秒不是那种几十秒的庞然大物。问题是3 秒的 SQL 在高峰期叠加了几百个并发数据库连接池瞬间被占满后面所有正常查询全部排队等待连接系统彻底雪崩。9.2 定位过程用SHOW PROCESSLIST查看在线会话发现大量线程状态是Copying to tmp table和Sending data连接数已经打到上限。这些 SQL 本身不算特别慢但它们同时占住了连接把整个数据库的并发处理能力拖垮了。9.3 优化方案这个案例的解法是组合拳我印象很深。第一给 MySQL 设置单条 SQL 的执行超时避免一条 SQL 无限占用连接。MySQL 5.7.8 之后支持SET GLOBAL max_execution_time 3000;这条参数只对只读 SELECT 生效能避免读 SQL 因为锁等待或者数据量暴涨而长时间挂起。注意它不限制 UPDATE 和 DELETE所以更新类的大事务还要另想办法。第二用pt-kill这类工具定时清理执行时间过长的会话pt-kill --hostlocalhost --userroot --ask-pass \ --busy-time 10 --kill --print这个工具可以每隔几秒扫描一次线程列表把执行超过 10 秒的 SQL 直接终止避免它们阻塞后面的请求。第三从应用层限制连接池。很多系统的maxActive配得比数据库max_connections还大等于应用一打满数据库就被榨干。合理的做法是让连接池的最大连接数不超过数据库max_connections的 80%并且预留一部分连接给 DBA 做运维排查。这个案例的教训是慢 SQL 的治理不能只看单条执行时间还要看它对连接池的影响。一条 1 秒的 SQL在 500 并发下就是 500 秒的连接占用系统不崩才怪。提示排查连接打满时第一件事不是杀会话而是看information_schema.processlist里哪条 SQL 出现频次最高找到源头再处理杀会话只是止血手段。10. 案例10大事务锁等待一条 UPDATE 让全表排队10.1 问题现场财务系统做月度对账一个事务里用循环逐条更新几万条对账记录START TRANSACTION; UPDATE settlement_detail SET status checked WHERE id ?; UPDATE settlement_detail SET status checked WHERE id ?; -- 循环执行几万次 COMMIT;表面上看每条UPDATE的执行时间只有几毫秒但整个事务跑下来后面的会话更新同一张表时开始疯狂等待。10.2 根因分析InnoDB 的行锁在事务提交或回滚时才会释放。这个几万次循环的大事务相当于把大量行的锁握在自己手里 40 分钟其他会话更新这些行时只能进入锁等待状态。慢查询日志里显示的等待时间几十秒但EXPLAIN根本看不出任何问题因为 SQL 本身没有索引问题问题出在锁的持有时间上。还有一个容易被忽略的点如果 UPDATE 语句的 WHERE 条件没有走索引InnoDB 的行锁会升级成对整张表的锁后面的所有写操作全部被堵住。10.3 优化方案把大事务拆小批量提交UPDATE settlement_detail SET status checked WHERE id IN (一批500个id) AND status ! checked;每批 500 条提交一次整体事务从 40 分钟降到了 3 分钟而且锁的持有时间大幅缩短其他会话基本感知不到阻塞。同时规范更新类 SQLUPDATE 和 DELETE 的 WHERE 条件必须走索引避免锁升级。排查锁等待时我习惯先看这几张表SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_lock_waits;这两张表能直接看到谁在等锁、谁持有锁、事务跑了多久比看慢日志更直接。重要长事务不仅引入锁等待还会让 undo log 无限膨胀。MySQL 的 purge 线程来不及清理旧版本数据最终导致 undo 表空间暴涨再拖下去整个实例都可能 hang 住。11. 定位慢 SQL 前先把这几样工具用顺手前面 10 个案例我反复提到了EXPLAIN、慢查询日志和一些系统表。这里统一整理一下我日常排查慢 SQL 最常用的工具和命令。首先是慢查询日志。MySQL 的慢日志可以现场动态开启不需要重启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设置超过多少秒的 SQL 会被记录线上建议先设 2 秒不要一开始就设 0.1 秒否则日志量会巨大。等稳定后再逐步下调阈值。其次是EXPLAIN这是分析单个 SQL 执行计划的核心。我一般重点看这几个字段字段含义重点关注值type访问类型const eq_ref ref range index ALLpossible_keys可能用到的索引有没有显示可用的索引key实际使用的索引有 possible_keys 但 key 为 NULL基本就是索引失效rows预估扫描行数越大越慢需要重点优化Extra附加信息Using filesort、Using temporary 必须处理Using index 是好消息type 从好到坏看到 ALL全表扫描或者 index全索引扫描基本可以断定这条 SQL 需要优化。看到Extra里出现Using filesort就要检查排序字段有没有索引出现Using temporary就要检查 GROUP BY 和 DISTINCT 有没有索引支撑。第三个工具是 performance_schema 和 sys 库。排查线上问题时我常查这两个视图-- 按总执行时间和执行次数排序找热门慢 SQL SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10; -- 查看哪些索引建了但从未被使用建议删除 SELECT * FROM sys.schema_unused_indexes;sys.statement_analysis这个视图相当于一个汇总版慢日志能把所有 SQL 按累计耗时排好序很适合系统变慢时快速锁定嫌疑对象。sys.schema_unused_indexes可以从另一个方向帮我们清理多余的索引减少写入开销。12. 一套日常排查套路十分钟锁定问题根因把前面的案例归纳之后我现在的排查流程基本固定成六步遇到任何慢 SQL 都能快速定位第一步从慢查询日志中把 Top 10 的 SQL 捞出来按单次耗时和累计耗时两个维度排序。单次耗时高的是“粗问题”累计耗时高的是“高频问题”两类都要看。第二步目标 SQL 跑一遍EXPLAIN重点看 type、rows、Extra 三列。type 是 ALL 或者 index直接往下走rows 远大于实际返回行数说明扫描范围太大Extra 里有 filesort 或 temporary说明排序或分组有问题。第三步排查索引是否失效。按顺序检查四类典型场景函数处理索引列、隐式类型转换、OR 条件、前导模糊查询。这四类占了慢 SQL 的一大半。如果是 JOIN还要看被驱动表的连接列是否有索引、字段类型和字符集是否一致。第四步尝试改写 SQL。核心思路就三个缩小返回列、缩小扫描范围、换一种连接方式。具体到写法就是去掉不必要的字段、把函数条件改成范围条件、把 OR 拆成 UNION ALL、把大的子查询改成 JOIN、把深分页改成游标分页或延迟关联。第五步回归验证。改写后不能只看耗时降了没降要看执行计划里的 type 是否从 ALL 变到 range 或 refrows 是否明显下降。两个都要确认缺一个都可能埋雷。第六步沉淀成规范。每修复一个慢 SQL就把根因和优化方案记录到团队的开发手册里形成 SQL 评审的检查项。后续开发写 SQL 时先自查DBA 再做代码评审从源头减少慢 SQL 产生的概率。最后我把这 10 个案例的常见问题汇总成一张速查表方便实际排查时对照问题特征典型表现首选优化方案SELECT * 查宽表网络开销大回表多只查需要的字段必要时用覆盖索引索引列套函数执行计划 typeALL改写为范围条件或使用生成列隐式类型转换possible_keys 有key 为 NULL参数类型与字段类型保持一致深分页offset 越大越慢游标分页或改造成延迟关联OR 多条件typeALL拆成 UNION ALLLIKE %关键词%全表扫描改前缀匹配或用全文索引独立搜索引擎JOIN 缺索引被驱动表 ALL给连接列加索引小表驱动大表大 IN 子查询子查询物化成全表改成 JOIN 索引连接数打满大量 Sending datamax_execution_time pt-kill 限流大事务锁等待锁等待超时分批提交UPDATE 走索引我在实际排查中最深的体会是慢 SQL 优化的核心不是背几个优化技巧而是建立一套固定的分析习惯。看到任何慢查询都能条件反射地想到先看执行计划、再判断索引状态、最后选择改写方向。这 10 个案例里的坑每一个都是我亲自踩过的有的还踩了不止一次。把表和字段名换成你自己的业务场景照着这个思路排查你也能在几分钟内找到慢 SQL 的病灶在哪里。