MySQL慢查询优化实战：一行SQL改写让接口从3秒降到50ms

生产环境的数据库出问题从来不会提前打招呼。上个月某天下午我正在工位上改需求突然连续收到几条接口超时告警打开监控面板一看订单管理后台的列表接口响应时间直接飙到 3 秒以上。这个接口平时虽然不算快但也稳定在 300ms 左右突然变成这样第一反应就是去看数据库慢查询。因为在 MySQL 的实际使用中绝大多数接口变慢的根因最后都会落到某一条 SQL 身上而我这次要分享的就是从 3s 到 50ms 的过程——整个过程只改了一行 SQL 条件。如果你也是天天跟 MySQL 打交道的后端开发、DBA或者正在学 MySQL 性能调优的读者这篇文章应该能帮你建立一条比较完整的慢查询排查思路。我不仅会复盘这条 SQL 是怎么变快的还会把执行计划、索引失效、慢查询日志这些工具串起来讲清楚保证你看完能直接拿去用。1. 事故现场一条把订单接口拖到 3 秒的查询1.1 订单表的结构和业务背景先交代一下背景。我们有张订单表order_info主要字段包括id、order_no、user_id、order_status、total_amount、create_time、pay_time等单表数据量大概在 500 万行左右。这个量级在 MySQL 里说实话不算恐怖只要索引用得对条条件查询都在毫秒级完成。但问题就出在当时的开发同学在写订单管理后台的列表查询时用了一个看起来很自然的条件SELECT order_no, user_id, total_amount, order_status, create_time FROM order_info WHERE DATE(create_time) 2024-05-20 ORDER BY create_time DESC LIMIT 20;从功能上讲这条 SQL 的意图很明确——查某一天产生的订单按时间倒序分页。业务上没有任何问题刚上线时数据量才几十万跑得也还行。但随着订单量涨到 500 万这条 SQL 的耗时开始指数级上升某一天直接突破 3 秒把整个接口都拖垮了。1.2 慢查询日志是怎么暴露问题的排查之前先得确认慢查询日志确实开着。MySQL 的慢查询日志是性能排查的第一现场我在处理线上问题时的习惯是先在数据库里执行SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;如果slow_query_log是 OFF就用下面的命令临时打开然后设置阈值SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这里有个细节long_query_time默认是 10 秒对大多数 OLTP 业务来说太宽松了。我一般会设成 1 秒让慢查询日志更敏感一些。设成 1 秒之后线上执行时间超过 1 秒的 SQL 都会被抓进去等排查完、把对应的 SQL 优化好了再决定要不要调回 2 秒或 3 秒避免日志文件增长过快。打开慢查询日志后很快就抓到这条问题 SQL。日志里记录的Query_time是 2.9sRows_examined是 500 多万——它把整张表都扫了一遍这显然不对劲。所以下一步就要用 EXPLAIN 看它到底发生了什么。2. 定位瓶颈EXPLAIN 到底告诉了我们什么2.1 一条命令看清数据库的查询路径拿到慢 SQL 之后第一件事永远是加 EXPLAIN看执行计划。这也是我在团队里反复强调的习惯任何 SQL 上线前都应该先 EXPLAIN 一遍哪怕只是看一眼有没有走索引。EXPLAIN SELECT order_no, user_id, total_amount, order_status, create_time FROM order_info WHERE DATE(create_time) 2024-05-20 ORDER BY create_time DESC LIMIT 20;执行计划里几个关键列显示得明明白白列名值含义typeALL全表扫描keyNULL没有用到任何索引rows5123456预估扫描行数约 512 万ExtraUsing where; Using filesort先过滤再文件排序看到typeALL和keyNULL这两项基本就能断定问题所在了这条 SQL 没走create_time上的索引而是把整张表全部扫了一遍。这也是慢查询里最常见的一类问题不是索引没建而是索引建了但没用上。2.2 函数包裹索引列为什么会失效为什么create_time明明有索引这个查询却不用关键在于条件里写了DATE(create_time)。DATE()是个函数MySQL 在处理这种条件时必须对每一行的create_time字段先调用一次函数算出结果后再跟后面的常量去比较。这就像一个按拼音排好的字典你现在不让我按拼音查非要让我把所有汉字先翻译成某种编码再比较大小那索引的有序性就完全派不上用场了。对 MySQL 来说DATE(create_time) 2024-05-20无法直接利用 B 树索引进行范围定位因为索引里存的是原始create_time值而不是函数处理后的值优化器只能选择全表扫描逐行计算DATE(create_time)再做等值判断。很多人会问为什么 MySQL 不先算出2024-05-20对应的时间范围然后直接查范围这个问题的答案是MySQL 的优化器确实有一些自动推导能力但对这种把索引列包在函数里的写法默认不会做反向推导。它把它当作一个无法优化的表达式来处理。所以我们在实际写 SQL 时最核心的一条原则就是索引列别碰函数要算就在常量那边算。2.3 全表扫描 filesort 的代价有多大如果说全表扫描只是慢那么 Extra 里的Using filesort就是在伤口上撒盐。因为查询结果要按create_time DESC排序如果扫描出来的 500 多万行数据无法利用索引顺序MySQL 就必须自己另起炉灶做一次排序。排序的数据量大到超过内存排序缓冲区时还会落到磁盘上做临时文件排序那个开销更是不可估量。我后来复盘计算过这条 SQL 的实际 IO 开销500 万行数据全扫就算每行只有几百字节也要读出来上 GB 的数据量。加上每行都要执行一次DATE()函数计算再加上一个大排序整体耗时接近 3 秒完全符合预期。更麻烦的是这个接口是订单管理后台的核心接口运营同学每天上班都挂在页面上一卡就是一天业务影响非常直接。3. 把函数从索引列上拿掉一行改写带来的 50ms3.1 改写思路计算留给常量索引列保持原样定位到根因之后优化方案其实就很朴素了。我们要查询的语义是2024-05-20 当天产生的订单这等价于一个半开区间create_time大于等于当天的 00:00:00小于第二天的 00:00:00。所以原来的那一行条件WHERE DATE(create_time) 2024-05-20改写成WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00注意上面这段多行展示是为了排版清晰实际上在业务代码里它就是一行条件字符串只是从DATE(create_time) ...换成了两个比较表达式。改动点只有这一处函数没了索引列干净了优化器就能直接走create_time上的索引做范围扫描。这里稍微提醒一句也有不少人会写成WHERE create_time BETWEEN 2024-05-20 00:00:00 AND 2024-05-21 00:00:00但 BETWEEN 是闭区间会把2024-05-21 00:00:00这一瞬间的数据也算进去。如果业务上对边界不敏感还好一旦有秒级、毫秒级的边界数据这种写法就会导致多算一条或者少算一条而且排查起来特别隐蔽。所以我的习惯是统一用和组合表达半开区间语义最准确。3.2 改写后的执行计划对比改完之后再看一眼执行计划列名优化前优化后typeALLrangekeyNULLidx_create_timerows51234561862ExtraUsing where; Using filesortUsing index condition这里有几个很重要的变化要展开说。type从ALL变成了range。range表示索引范围扫描优化器能在索引上直接定位到2024-05-20 00:00:00到2024-05-21 00:00:00这一段只需要读取这个范围内的索引项然后回表取出对应的行记录即可。rows从 512 万直接降到 1862这就是索引真正的价值——数据库只需要访问两千行左右的数据而不是五百万行。Extra里的Using filesort也消失了。因为查询走的是idx_create_time索引而我们要ORDER BY create_time DESC索引本身已经按create_time排好序了。优化器可以直接沿着索引倒序读取天然就满足排序要求不需要再额外排序。这个优化是顺带得到的但它同样重要因为省掉的是一次 500 万行的排序开销。Using index condition表示 MySQL 在存储引擎层利用索引过滤了一部分数据最终回表的行数已经很少整体开销可以忽略不计。3.3 为什么耗时能稳定在 50ms改完之后我又连续跑了 20 次这条 SQL记录下来的耗时在 42ms 到 55ms 之间波动平均在 48ms 左右。相比之前的 3 秒性能提升了约 60 倍。这个结果其实一点也不神秘。访问数据从 500 万行缩小到不到 2000 行IO 量从 GB 级别降到了 KB 级别排序开销直接消失查询时间自然就下来了。而且这条 SQL 后续的响应时间非常稳定没有出现时快时慢的情况。因为索引范围扫描的耗时跟索引范围内数据量的大小基本呈线性关系而当天新增订单数在几千条量级数据库压力很小时稳定在 50ms 是符合预期的。这里我也给一个判断标准在千万级以下的单表上一个带等值条件或者范围条件、走索引、返回几十行的查询如果耗时超过 200ms就要认真考虑是不是哪里没走对索引或者是不是查询字段太多导致回表次数过高。4. 从慢查询日志到执行计划一套可复用的排查流程4.1 用 mysqldumpslow 快速找到高危 SQL刚才我们说到了慢查询日志但生产环境的慢查询日志里通常不止一条 SQL尤其是大促期间几分钟就能刷出几十条。手工去日志文件里翻太慢我一般直接用mysqldumpslow工具做聚合。mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log-s t表示按查询耗时排序-t 10表示只看前 10 条。这个工具会把结构相同、参数不同的 SQL 聚合成一条并显示平均耗时、扫描行数等统计信息。这样能在几分钟内定位到当前对数据库压力最大的几类查询再针对性地去优化而不是眉毛胡子一把抓。在 MySQL 8.0 里也可以用performance_schema里的events_statements_summary_by_digest表去查 TOP SQL效果类似而且不用落盘性能开销更小。但对我来说mysqldumpslow已经足够简单直接配合慢日志文件落盘排查时也方便回溯历史。4.2 执行计划那些关键字段的速查用法EXPLAIN 输出的字段很多刚接触的人容易看得眼花缭乱。这里我给一个比较实用的优先级排序看执行计划时按这个顺序检查基本不会漏问题先看type再看key然后看rows最后看Extra。type直接告诉你这条 SQL 的访问方式好不好从优到劣大致是systemconsteq_refrefrangeindexALL。一般我们的底线是range意思是至少用了索引范围扫描。如果出现ALL就要意识到这是全表扫描SQL 基本是裸奔状态。key告诉我们实际走了哪个索引。如果这里显示 NULL说明没走任何索引。有时候type显示的是index这其实也不是什么好事它表示对索引进行了全索引扫描虽然比全表扫描好一点但本质上还是扫了大量数据。rows是优化器预估需要读取的行数这个数字越小越好。有时候统计信息不准确rows会偏差很大比如重复执行ANALYZE TABLE之后再查 EXPLAINrows会更接近真实情况。所以当我们觉得rows不对时可以刷新一下表的统计信息。Extra里最需要注意几个关键词Using filesort文件排序说明没有利用索引排序、Using temporary使用了临时表常见于 GROUP BY、DISTINCT 或子查询、Using index覆盖索引扫描这是个好消息说明不需要回表。前两个都是性能隐患看到就得琢磨怎么去掉。4.3 除了函数包裹还有哪些索引失效的常见坑这次案例是函数包裹索引列但日常工作里索引失效的场景远不止这一种。我把高频踩坑点整理一下你们写 SQL 的时候可以对照检查。隐式类型转换索引字段是varchar查询条件里却传了数字比如WHERE order_no 123456MySQL 会把字段隐式转成数字去比较导致索引失效。解决办法是传参时保持类型和字段一致或者用CAST显式转换但显式转换也别包在字段列上。对索引列做运算WHERE id 1 5这种对字段本身做算术运算的写法也一样会让索引失效。应该把计算挪到等号右边WHERE id 5 - 1。LIKE 前置通配符WHERE user_name LIKE %张因为匹配串以%开头B 树索引无法定位起始位置只能全扫。实在需要这种模糊匹配建议用全文索引或者走搜索引擎。OR 连接非索引列WHERE user_id 123 OR order_status 1即使user_id有索引只要order_status没用索引优化器就可能放弃user_id的索引去做全表扫描。典型解法是改写成两个查询 UNION ALL保证每个分支都用索引。联合索引不满足最左前缀比如建立了(user_id, create_time)的联合索引但查询条件只写了create_time这个索引是用不上的。NOT IN、、!这类反向条件在数据分布不均时优化器经常判断索引过滤效率太低干脆全扫。这些坑我在团队代码评审里反复强调过但总有人会踩。最简单有效的拦截手段就是上线之前跑一次 EXPLAIN看到ALL就打回重写成本最低。5. 优化之后的验收与防回潮别让 SQL 再慢回去5.1 用同量级数据做回归验证SQL 改完之后要做的第一件事不是高兴而是验证。我在优化这条 SQL 之后又做了两件验证一是拿线上同一时间段的数据多次执行看耗时是否稳定二是模拟了未来一个月的数据增长把索引范围内的行数增加到一个月的量级再看耗时是否还在可接受范围内。这个用未来量级做验证的习惯是我踩过坑之后养成的。当年优化过一条 SQL当时只跑了当天数据响应时间很漂亮。结果月底业务量翻倍那条 SQL 又慢回去了搞得我大半夜被叫起来处理。后来我凡是做 SQL 优化都会用历史数据把区间扩大几倍去压一遍确保优化后的方案有足够的容量余量。除了手动跑几次 SQL最好还能用压测工具模拟真实请求。比如用sysbench或者在测试环境回放线上流量重点观察优化后的 SQL 在并发场景下的响应时间分布。单条 SQL 快不代表并发下快锁竞争、IO 排队都会放大问题。5.2 监控慢查询阈值建立持续防线SQL 优化完成只是第一步真正难的是防止它回潮。我的做法是设置一个专门的慢查询监控视图重点关注三类指标慢查询数量、慢查询平均耗时、以及哪些 SQL 反复上榜。监控手段上可以用 MySQL 自带的performance_schema做采集再配合一套简单的告警规则。比如每分钟慢查询次数超过 10 次就告警单条查询耗时超过 2 秒就告警。如果你们还没建设监控体系最轻量的做法是写个脚本定时拉取慢查询日志解析出新增的慢 SQL推送到工作群。技术上不复杂但能极大缩短故障发现时间。这条优化后的 SQL 上线之后我在接下来两周持续观察了慢查询日志确认它没有再次出现在榜单里。新增的慢 SQL 反而是另外几个老查询这次一起被暴露了出来被我顺手加入了下一轮优化清单。这就是慢查询治理的常态化过程不断发现、不断优化、不断验证。5.3 SQL 评审时就把 EXPLAIN 放进流程里最后想聊一点管理层面上的经验。性能优化不能只靠事后救火把经验固化到研发流程里才能避免反复踩坑。我们团队现在有一条刚性要求所有涉及数据库查询的代码在提测之前必须附带 EXPLAIN 执行计划截图凡是出现typeALL、rows超过一万或者Extra里出现Using filesort的一律打回修改。这个门槛看起来有点严但实际执行下来开发同学反而觉得挺好因为在测试阶段就把潜在慢查询拦住了省得上线后提心吊胆。而且大家写 SQL 的水平也在提高很多人已经能主动意识到这个条件会不会让索引失效这是比任何优化工具都重要的东西。写在最后回看这次从 3s 到 50ms 的优化技术含量其实并没有多高核心就是一句话别让函数包裹索引列。但我还是想认认真真写出来因为这类问题太普遍了几乎每周都能遇到。改一行 SQL 很容易难的是搞清楚为什么慢、如何定位、以及怎么防止下次再犯。我个人在实际操作中的体会是做 MySQL 性能调优扎实的基础概念比各种花哨工具更重要。你只要真正理解了 B 树索引的排序结构理解了回表、filesort、临时表这些代价很多慢查询在你眼里就是透明的。遇到问题打开 EXPLAIN 一看基本能猜到八九分。最后再分享一个小技巧如果你改完 SQL 发现执行计划还是没走索引先别急着怀疑优化器智商执行一下ANALYZE TABLE让 MySQL 重新统计索引分布信息很多时候就是统计信息不准确坑了你。我这个习惯帮我解决了不少奇怪问题希望对你们也有用。