接口越跑越慢保姆级 MySQL 慢 SQL 优化教程照着做速度立刻起飞先说个我自己的经历。之前负责的一个订单查询接口上线时说好的 100ms 内返回跑了一个多月压测直接飙到 2 秒多。查了网关、查了 Redis、查了服务端日志最后把 SQL 捞出来单独执行好家伙全表扫了快一千万行。后来花了半天时间把慢查询日志打开、分析了执行计划、补了索引、改了 SQL接口直接回到 80ms。整个过程一点都不玄学MySQL 慢 SQL 优化就是这么回事——把每一步做对效果立刻就能看到。这篇就把我实际用过的整套排查和优化流程给你过一遍照着操作就行。1. 慢SQL定位先把慢查询日志用起来1.1 打开慢查询日志的完整参数很多同学上来就盯着业务代码抠内存、抠循环其实最该先做的是把 MySQL 的慢查询日志打开让数据库自己告诉我们哪些 SQL 是坏孩子。这个日志记录的是执行时间超过指定阈值的 SQL 语句是排查慢 SQL 的第一手证据。我的习惯是直接在 MySQL 配置文件my.cnfLinux 下通常是/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf里加下面这几项然后重启 MySQL 服务slow_query_log 1 slow_query_log_file /var/log/mysql/slow-query.log long_query_time 1 log_queries_not_using_indexes 1这几个参数的含义我拆开解释一下slow_query_log总开关1 表示开启0 表示关闭。slow_query_log_file日志文件存放路径。注意 MySQL 进程的运行账号要有这个目录的写权限否则开关打不开。long_query_time阈值单位是秒。我习惯设成 1也就是超过 1 秒的 SQL 都会被记下来。生产环境如果 SQL 整体比较快也可以设成 0.5但别设成 0否则日志量太大会影响本身性能。log_queries_not_using_indexes把没有走索引的 SQL 也记下来。这个开关挺关键的因为有些 SQL 执行得不算慢但是每查一次就全表扫一次等到数据量上来迟早要出问题。如果不想重启数据库生产环境一般也不建议随便重启可以用SET GLOBAL动态修改SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;注意用SET GLOBAL方式改long_query_time之后已经存在的连接不会生效需要新开连接才有效。我在实操中就踩过这个坑改完等了半天日志里啥也没记以为开关坏了其实是旧的 Navicat 连接还拿着旧的会话配置重新连接一下就好了。1.2 分析慢日志的两种姿势日志打开之后过个一两天再去看文件里会积累不少记录。每一条大概长这样# Time: 2024-01-15T10:23:45.123456Z # UserHost: app_user[app_user] [192.168.1.10] Id: 12345 # Query_time: 2.345678 Lock_time: 0.001234 Rows_sent: 10 Rows_examined: 9812345 SET timestamp1705314225; SELECT * FROM order_info WHERE user_id 10001 ORDER BY create_time DESC LIMIT 10;别被这一大坨吓到重点抓三个信息Query_time执行耗时、Rows_examined扫描行数、Rows_sent返回行数。如果 Rows_examined 和 Rows_sent 差距巨大比如扫了 900 多万行才返回 10 行那基本可以判定这条 SQL 的查询方式是地毯式搜索优化空间很大。手工看日志在小流量下没问题但日志一多就费劲了。更高效的做法是用pt-query-digestPercona Toolkit 里的工具做聚合分析pt-query-digest /var/log/mysql/slow-query.log它能按执行总耗时平均耗时出现次数对慢 SQL 排序还会把同一条模板的 SQL 归类到一起帮你快速找到真正最该优化的那一批。我用它处理过一份几千行的慢日志最终直接锁定 7 条共性 SQL省了整整一下午。如果是个人学习环境装个 Percona Toolkit 也不复杂用包管理器装就行。提示慢日志只记录执行完成的 SQL如果一个查询卡死或超时被客户端断开可能不会出现在日志里。这类问题要去查information_schema.processlist或SHOW ENGINE INNODB STATUS。2. EXPLAIN读懂执行计划定位扫描方式和索引命中情况2.1 先学会看 type 这个字段拿到一条慢 SQL 之后第一个标准动作就是给它做一次体检——用EXPLAIN查看执行计划。MySQL 会告诉你它打算怎么执行这条 SQL先查哪张表、走哪个索引、扫多少行、需不需要额外排序。举例EXPLAIN SELECT * FROM order_info WHERE user_id 10001 ORDER BY create_time DESC LIMIT 10;输出结果里最关键的是type字段它表示 MySQL 在表里找数据的方式。从好到差大致是这样一个顺序system const eq_ref ref range index ALL我按自己判断的经验给你翻译一下const/eq_ref直接用主键或唯一索引定位速度最快说明 SQL 写得很好。ref走了普通索引非唯一索引匹配到多行通常也还不错。range索引范围扫描比如BETWEEN、、、IN这类条件基本可接受。index看起来走了索引但实际是遍历了整棵索引树效果和全表扫描差别不大常见于ORDER BY走了某个索引但没有 WHERE 条件的场景。ALL全表扫描。这是红色警报。我见过不少人一看到key字段有值就以为 SQL 没问题这是误区。key有值只能说明用上了索引但type是index或者rows特别大依然可能是全量遍历。比如type index配合ORDER BY create_time的场景MySQL 是把索引树从头扫到尾再取前 N 条数据量一大照样慢。2.2 重点关注 Extra 里的三个效率杀手Extra字段里藏着 MySQL 没有说出口的潜台词。我在优化时特别关注这三类Using filesort表示 MySQL 需要额外做一次文件排序来满足ORDER BY。看到这个优化方向通常是让排序字段和查询条件走同一个索引。因为 B 树本身是有序的如果WHERE条件和ORDER BY能组成联合索引排序就不用额外做了。Using temporary表示 MySQL 用了临时表常见于GROUP BY、DISTINCT、多表连接需要去重或排序的场景。临时表如果在磁盘上建性能会大幅下滑。我们要做的是尽量通过索引消除或减小临时表。Using index这个反而是好消息表示覆盖索引生效查询只在索引里就能拿到所需列完全不用回表。这是优化时最想要的结果之一。rows字段也很重要它表示 MySQL 预估要扫描的行数。这个数字和实际值通常相差不大是判断 SQL 是否浪费的关键依据。比如预估扫描 500 万行、实际只返回 10 行显然是一笔亏本买卖。注意EXPLAIN只是预估值不是实际执行值。想拿真实数据可以用EXPLAIN ANALYZEMySQL 8.0.18 支持它会真正执行 SQL 并返回每步实际耗时和扫描行数。生产环境慎用但测试环境强烈建议用。3. 为什么索引会失效最常见场景与根因分析3.1 用 B 树的结构理解索引失效很多新手背了一堆索引失效口诀但遇到没背过的场景就懵了。我建议从底层原理去理解MySQL 的 InnoDB 引擎用的是B 树索引叶子节点按索引列的值排好序内部节点只存路标用来定位。走索引的本质就是沿着这棵有序树快速定位——但这要求查询条件与索引列的顺序、类型、形式保持匹配。如果查询条件让 MySQL 无法在索引树上按序查找索引就直接失效。举个最基础的例子SELECT * FROM user WHERE age 1 30;即使age字段单独建了索引这个 SQL 也是全表扫描。因为索引树里存的是age值本身不是age 1的结果MySQL 无法通过索引找到所有满足age 1 30的行只能逐个扫描判断。这就像你有一本按笔画排序的字典想知道笔画数加 1 等于 30的字是哪些根本没法用目录。所以最稳妥的用法是把计算移到等号右侧写成age 29。但要注意不要在索引列上做任何运算不管是1、-1、函数处理、类型转换统统不行。3.2 一张表列清楚常见失效场景为了你排查时对照方便我把高频的索引失效场景和优化方式整理成一张表场景错误示例原因正确做法索引列参与运算WHERE age 1 30索引树中存的是原始值无法按运算结果定位改成WHERE age 29索引列套函数WHERE DATE(create_time) 2024-01-01函数作用在索引列上破坏有序性改成WHERE create_time 2024-01-01 AND create_time 2024-01-02隐式类型转换WHERE user_id 10001user_id 是 BIGINT字符串和数字比较时类型不一致MySQL 隐式把列转成字符串后再比较索引失效保证参数类型和列类型一致前导模糊查询WHERE name LIKE %张%索引按前缀排序前导通配符导致无法按序查尽量改LIKE 张%业务必需则考虑全文索引或搜索引擎OR 条件中有非索引列WHERE age 29 OR status 1status 无索引OR 只要一个分支无法走索引整条就退化为全表扫描拆分两条 SQL 或把status也加索引或改用UNION联合索引未用前导列联合索引(a, b, c)条件只写WHERE b 1B 树先按第一列排序跳过第一列导致无法定位条件中必须带上联合索引第一列或调整索引顺序范围查询后的列联合索引(a, b)WHERE a 100 AND b 2a 的范围条件之后b 无法继续有序查找把 b 移到条件中范围查询之前的等值位置或调整索引设计这张表是我查慢 SQL 时最常用的对照清单。你不需要每条都背下来只要记住一条核心原则索引列要独立出现在查询条件中保持类型一致、不加函数、不套运算、不搞前后模糊索引基本就不会白建。3.3 联合索引设计的几点经验联合索引的字段顺序很重要这也是新手最容易犯迷糊的地方。我给出的判断依据是先看等值条件再看排序需求最后才考虑范围条件。举个例子业务场景是查某个用户在某段时间内的订单按时间倒序分页。表结构大概是order_info(id, user_id, create_time, ...)查询是SELECT * FROM order_info WHERE user_id 10001 AND create_time BETWEEN 2024-01-01 AND 2024-02-01 ORDER BY create_time DESC LIMIT 20;索引设计我建议是(user_id, create_time)。为什么因为user_id是等值条件放最前面create_time既是范围条件又是排序字段跟在后面后MySQL 可以在满足user_id 10001的范围内利用索引的有序性直接倒序取 20 条既过滤了行又省掉了ORDER BY的文件排序一次搞定。反过来如果建(create_time, user_id)MySQL 要先按 create_time 做范围扫描再在范围内过滤 user_id显然更费劲。所以等值条件优先、排序字段次之、范围条件最后这个原则实战中真的能省掉我不少试错时间。4. 几个典型慢SQL的完整优化过程4.1 深分页问题的改写分页接口越翻越慢是慢 SQL 里非常典型的一类。问题场景是SELECT * FROM order_info ORDER BY create_time DESC LIMIT 100000, 20;这条 SQL 慢在LIMIT 100000, 20意味着 MySQL 要先把前 100000 行找出来并排序然后再丢掉它们、只留下后面的 20 行返回。数据量越大翻得越深浪费越多。优化的思路是先定位主键或唯一键再用主键查完整行SELECT t1.* FROM order_info t1 INNER JOIN ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 100000, 20 ) t2 ON t1.id t2.id;内层子查询只走(create_time, id)联合索引扫描的数据量远小于全行数据先拿到 20 个主键外层再用主键回表取完整记录。我实测过把 300ms 的深分页优化到 90ms 左右效果立竿见影。如果业务能接受上一页最后一条的时间这种形式更推荐游标分页SELECT * FROM order_info WHERE create_time 上一页最后一条的create_time ORDER BY create_time DESC LIMIT 20;这种方式天然跳过前面的所有记录不需要深翻数据量再大也几乎恒定耗时。缺点是没法直接跳到任意页适合无限滚动或下一页场景。我当时和产品聊了下很多场景其实并不需要跳页直接改游标分页体验更好、压力也更小。4.2 隐式类型转换导致索引失效另一个常见坑是隐式类型转换。有一个真实案例user_id是BIGINT但接口层传参时不小心把参数拼成了字符串SQL 变成了这样SELECT * FROM order_info WHERE user_id 10001;MySQL 在比较 BIGINT 和字符串时会尝试把字符串转为数字但如果无法干净转换就可能在索引列这一侧做类型转换导致索引失效。即使能转成数字也有风险。执行计划里type直接变成ALL我可没少见。解决方式分两层。最直接的接口传参时保证类型是数值类型WHERE user_id 10001。如果是在框架里拼 SQL可以在代码里做一次强转或校验。如果你负责的代码里大量存在这个问题不想挨个改也有一个动态方案在创建索引时考虑把列定义为与查询一致的字符串类型或者干脆在 SQL 里显式写明转换比如WHERE user_id CAST(10001 AS UNSIGNED)。但一定要避免在左边列上做CAST因为函数/转换作用于索引列时索引照样失效。4.3 ORDER BY 引起的 Using filesort再来看一个容易忽略的细节。假设查询条件是user_id排序是create_time索引只建了(user_id)单列索引SELECT * FROM order_info WHERE user_id 10001 ORDER BY create_time DESC LIMIT 10;执行计划里会出现Using filesort。为什么单列索引只保证user_id有序create_time在全局没有顺序。MySQL 找到所有user_id 10001的行之后必须在内存或磁盘上再按create_time排序。优化方式就是建联合索引(user_id, create_time)。这样 InnoDB 在索引内部已经按 user_id 分组组内再按 create_time 排序。查询到对应分组后顺序就已经是排好的直接取前 10 条。Extra里就没有Using filesort了。我在多个项目里验证过这个改动的收益通常在几十毫秒到几百毫秒之间索引本身也只多了几十 KB 空间非常划算。4.4 表连接的驱动顺序与索引策略多表连接变慢除了索引缺失还可能是驱动表选得不对。举个典型场景SELECT * FROM user u LEFT JOIN order_info o ON u.id o.user_id WHERE u.user_type 2;如果左边user表只有几千条右边order_info有几百万条那o.user_id必须建索引否则右边每匹配一次就要扫一次全表整体耗时就是几千乘几百万的量级接口不慢才怪。反过来经典的小表驱动大表原则连接时尽量让 MySQL 以小表作为驱动表通常也是 JOIN 顺序左侧的表大表作为被驱动表被驱动表的连接字段必须有索引。遇到 MySQL 自己选错驱动表可以用STRAIGHT_JOIN强制指定顺序但生产上我一般先检查统计信息和索引让优化器自己选实在不行再手动干预。5. 借助索引下推、覆盖索引与查询重写5.1 覆盖索引干掉回表所谓覆盖索引就是查询需要的所有列都能在索引里拿到不需要回表。InnoDB 的索引分为聚簇索引主键索引叶子节点存整行数据和二级索引叶子节点存索引列 主键值。比如你SELECT user_id, create_time而(user_id, create_time)本身就是个联合索引那 MySQL 扫完索引就收工了Extra会显示Using index。但如果要SELECT *二级索引只能提供索引列和主键剩下的字段必须拿着主键再回聚簇索引里取性能就多了几次随机 I/O。所以写 SQL 时尽量不要无脑SELECT *只查需要的字段配合合适的联合索引就可能让这条查询全程不回表。有一个真实的优化案例一条统计 SQL 把SELECT *改成SELECT user_id, status, COUNT(*)再配一个(user_id, status)联合索引时间从 1.8 秒降到 180ms效果非常直观。5.2 索引条件下推的隐形加速MySQL 5.6 引入的Index Condition Pushdown索引条件下推简称 ICP是个不太被提及但作用很大的优化。它的逻辑是在二级索引扫描过程中先把索引里能判断的 WHERE 条件直接过滤掉减少不必要的回表。举个例子联合索引(city, age)查询WHERE city 杭州 AND age 20。没有 ICP 时MySQL 会先把所有city 杭州的记录都回表再在表上过滤age 20有 ICP 时age 20会在索引扫描阶段就先用上回表次数大幅减少。这个功能默认开启optimizer_switch里的index_condition_pushdownon但前提仍然是你的 WHERE 条件里索引列的类型和顺序匹配。所以别只想着加索引数据结构设计对了ICP 才能帮你省下大量 I/O。5.3 MIN/MAX/COUNT 的隐式开销统计类 SQL 也很容易翻车。COUNT(*)在 InnoDB 里不是直接返回总行数的需要逐行统计。如果你只是做简单的页面数字展示可以用 MySQL 8.0 的直方图或单独维护计数表来加速但从写法上讲一个常见优化是让统计范围走覆盖索引SELECT COUNT(*) FROM order_info WHERE user_id 10001;把(user_id)单列索引微调成(user_id, id)甚至直接建(user_id, status)这种覆盖索引统计全程在索引中完成不碰数据行。注意COUNT(*)和COUNT(1)在 InnoDB 上性能差不多但COUNT(某个字段)会跳过 NULL 值语义上可能不同别乱替换。6. 有些慢SQL其实不是SQL的问题6.1 锁等待查询很快但接口很慢有时候你单独执行 SQL耗时 50ms但接口就是慢到 2 秒。这种表里表外不一致的情况往往出在锁等待上。比如你有一个批量更新接口开了事务更新了 order_info 的 1000 行但一直没提交那么另一个查询接口想拿这些行的FOR UPDATE或一致性读时就可能卡住。此时日志里Lock_time会很高或者是接口整体耗时很长但 SQL 单看不慢。排查命令我常看这几张系统表-- 查看当前正在执行的事务 SELECT * FROM information_schema.innodb_trx; -- 查看事务等待 SELECT * FROM information_schema.innodb_lock_waits;如果发现事务长时间RUNNING不提交基本就是它锁住了别人。处理方式不是去杀事务而是先定位是哪个接口开启的事务没及时提交。常见原因有三类事务里夹杂了外部调用HTTP、RPC、异常路径没有ROLLBACK、事务粒度设计过大。提示SELECT ... FOR UPDATE、UPDATE、DELETE都是加锁操作一旦持锁事务不提交后续请求全部排队等待。排查接口性能问题时锁等待和慢 SQL 同样重要千万别只看 SQL 执行时间。6.2 连接池打满SQL 再快也没用连接池打满也是接口慢的常见原因。即使每条 SQL 只跑 20ms如果应用拿到连接前要排队 1 秒接口照样是 1 秒。检查方法SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Threads_running;Threads_connected接近max_connections说明连接池快撑爆了。Threads_running很高说明很多连接同时在工作而不是闲置。遇到连接池打满不只是调大连接数那么简单还要看慢 SQL 是否占用了连接太长、事务是否未及时释放、连接池的minimumIdle和maximumPoolSize配得合不合理。连接池不是越大越好我见过一台机器配 200 个连接把数据库直接拖垮的。合理的做法是先按压测得出 QPS 和单请求 SQL 耗时的乘积再加上冗余来估算连接数。6.3 N1 查询模式很多接口慢不是单条 SQL 慢而是发了太多条 SQL。典型的是在循环里查数据库ListUser users userMapper.selectByIds(ids); for (User user : users) { ListOrder orders orderMapper.selectByUserId(user.getId()); // 处理逻辑 }如果用户有 100 个这里就产生了 1 100 条 SQL。每一条单独看都是毫秒级合起来就成了秒级。优化方式是批量查ListLong userIds users.stream().map(User::getId).collect(Collectors.toList()); MapLong, ListOrder orderMap orderMapper.selectByUserIds(userIds) .stream().collect(Collectors.groupingBy(Order::getUserId));SQL 改成WHERE user_id IN (...)一次查出全部订单再在内存里分组。这个改动对接口性能的提升非常明显我遇到过一个接口从 3 秒降到 200ms完全没改数据库索引。6.4 大事务与大字段还有一种情况是杂糅型的慢事务里同时更新了几十万行或者一条 SQL 要读取一个大TEXT/BLOB字段。SELECT *的时候大字段会把磁盘 I/O 拉得很高。即使走了索引回表读大字段也可能造成大量的页读取。针对大字段我通常建议在表设计阶段就把大字段拆到单独的表比如order_info主表和order_detail_text扩展表用主键关联。查询列表页时只查主表详情页再取大字段。如果表已经上线可以尝试把SELECT *改成SELECT 需要的字段避免把无用的大字段也捞出来。7. 一套能直接上手的优化检查单7.1 从现象到结论的关键链路把上面的经验浓缩成一套排查链路你照着这个顺序做绝大多数接口变慢的问题都能定位到看慢日志确认是否真的存在慢 SQL还是锁等待、连接池等其他问题。抓 SQL 单独执行用 EXPLAIN 看执行计划重点关注 type、rows、Extra。检查索引与表结构联合索引顺序、隐式类型转换、函数包裹、覆盖索引。看事务状态innodb_trx、innodb_lock_waits排除锁等待。检查连接池与并发Threads_connected、Threads_running、应用侧连接池配置。代码层排查循环查库、N1、大批量数据的内存处理。7.2 我常用的几条诊断 SQL为了方便你复制我把诊断时会用到的 SQL 列在这里-- 查看慢日志开关状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 查看当前数据库连接数和活跃数 SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Threads_running; -- 查看执行中的事务重点看 trx_started、trx_state SELECT * FROM information_schema.innodb_trx\G; -- 查看锁等待 SELECT * FROM information_schema.innodb_lock_waits\G; -- 当前正在跑的 SQL超过阈值可以抓 Threads_running SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC;7.3 一次完整的复盘案例最后讲一个我近期处理的真实案例方便你把前面所有内容串起来。现象一个报表接口每天 10 点准时变慢从 300ms 涨到 5 秒。排查过程先开慢日志第二天拿到 30 多条慢 SQL聚合后发现同一个模板占比 80%SELECT * FROM report_data WHERE biz_date ? ORDER BY update_time DESC LIMIT 20。单独 EXPLAINtype 是ALLrows 预估 248 万行Extra 有Using filesort。也就是说没走任何索引还要全量排序。看表结构发现report_data表有多个业务在用biz_date列有索引但字段类型是VARCHAR接口传参是字符串这部分没问题问题在于统计报表表里biz_date和update_time没有联合索引。优化动作把 SQL 的ORDER BY update_time改成ORDER BY biz_date业务上按日期倒序即可新增联合索引(biz_date, update_time)。再 EXPLAINtype 变成rangerows 降到 1000 以内Extra 没有Using filesort了。接口压测在 180ms 左右。那一次我最大的体会是别急着改 SQL 逻辑先让 EXPLAIN 给你指方向。索引方向对了可能就是一个联合索引的事方向不对SQL 写得再巧妙也白搭。如果你手头的接口也出现了越跑越慢的情况按我上面这套链路走一遍大概率能在半小时内定位到根因。慢 SQL 优化不是什么高深功夫核心就是让 MySQL 少干活。数据量小的时候全表扫描也能跑数据量一起来每一条 SQL 的问题都会暴露无遗。早一点把慢日志和 EXPLAIN 用起来早一点养成写 SQL 前先想索引的习惯以后是真的能少加很多班。