慢 SQL 这个问题基本每个用 MySQL 的团队都会碰上。索引没建对、查询写得太随意、数据量一上来原来秒出的接口直接卡到超时。MySQL 本身不复杂但“快”和“慢”之间往往就差一个索引或者一条 SQL 的写法。这篇文章我会从诊断慢查询开始把 explain 怎么读、索引怎么设计、SQL 怎么改写、事务和锁对查询的影响这些事串起来配合实际案例讲清楚。适合刚接手 MySQL 优化的人也适合写了几年 SQL 但从来没认真看过执行计划的开发同学。1. 优化之前先把“慢”量化出来很多人找我帮忙看慢 SQL第一句话就是“这个查询好慢帮我看看”。但到底多慢一天执行多少次慢的时候 CPU、IO 是什么状态全都没概念。没有量化数据优化就是盲人摸象。所以第一步永远是先让 MySQL 自己告诉你哪些 SQL 慢。1.1 慢查询日志怎么开最省事MySQL 默认是关闭慢查询日志的生产环境直接开也没什么心理负担因为这个功能本身很轻。关键是阈值要设置好我一般用 1 秒作为初始标准业务本身就慢的可以放宽到 2 秒。-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启重启失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;log_queries_not_using_indexes这个参数容易被忽略但它非常有用。它会额外记录所有没有走索引的查询哪怕执行时间只有几十毫秒。这类 SQL 往往是潜在的地雷数据量一涨就直接炸。生产环境可以开着不过日志量会大不少记得配合日志切割。慢查询日志落在文件里格式大概是这样的# Query_time: 3.207795 Lock_time: 0.000181 # Rows_sent: 215 Rows_examined: 482914 SELECT ... FROM orders WHERE customer_id 8873 ORDER BY create_time DESC;注意Rows_examined和Rows_sent的对比。482914 行扫描出来最后只返回 215 行这种查询多来几次再好的磁盘也扛不住。这就是优化的第一手证据。1.2 先会用 explain 再说优化拿到慢 SQL别急着改先看看执行计划。MySQL 的EXPLAIN会告诉你这条 SQL 是怎么执行的是走索引还是全表扫预估扫多少行有没有临时文件和文件排序。EXPLAIN SELECT * FROM orders WHERE customer_id 8873 ORDER BY create_time DESC;输出里重点看这几个字段type访问类型。const、eq_ref最好ref和range也算健康ALL就是全表扫描基本是重点怀疑对象。key实际用到的索引。如果是 NULL说明没走任何索引。rows预估扫描行数。这个数字太大比如几十万上百万后续要优化空间就很大。Extra这里经常藏着问题。Using filesort说明排序没走索引Using temporary说明用了临时表Using where说明虽然走了索引但还有条件在引擎层过滤Using index是最理想的状态覆盖索引扫描连回表都省了。注意rows是预估值不是精确值而且它基于统计信息和采样有误差正常。但量级很有参考价值——预估 50 万行和预估 500 行代表了完全不同的执行路径。我曾经排过一个线上问题一张用户表才 20 万行数据一条查询跑了 8 秒。explain 一开type 是 ALLrows 是 19.8 万Extra 里躺着Using filesort。表确实小但查询条件里的字段一个索引都没建每次请求全表扫完还要内存排序。这属于最典型也最好解决的慢查询建对索引八秒变五毫秒。执行计划就是帮你定位这类问题的地图一定要养成习惯。2. 索引设计和使用的几个核心细节索引是 MySQL 查询优化的基石。很多开发同学知道“查询慢要加索引”但加到什么程度、什么字段顺序、什么时候索引会失效心里没数。这一节把最核心的几个原则讲透都是可以直接拿去用的。2.1 联合索引的最左前缀到底是什么联合索引可能是被误解最多的概念。书上总说“最左前缀原则”听起来很玄其实一句话就能说清MySQL 把联合索引的多个字段按顺序拼成一个“字典序”的复合键查询条件里必须从最左边的字段开始连续匹配才能用上这个索引。举例有一个索引(a, b, c)WHERE a 1走索引。WHERE a 1 AND b 2走索引。WHERE b 2 AND c 3不走索引因为跳过了a。WHERE a 1 AND c 3只用到a这一列c用不上。为什么因为索引是有序排列的先按a排a相同再按b排再按c排。跳过了b直接拿c去匹配相当于在一本按“姓氏-名字”排序的电话簿里只知道名不知道姓没法二分查找只能把整个电话簿翻一遍。所以设计联合索引时字段顺序一定要把区分度高、查询最频繁的放在最前面。比如订单表最常见查询是“某个用户的订单按时间倒序”那么(customer_id, create_time)就是一个很自然的联合索引。2.2 覆盖索引回了多少表覆盖索引的意思是查询需要的所有字段都包含在索引里MySQL 只需要扫索引页不需要再回到聚簇索引主键索引里取整行数据。Extra里的Using index就是标记。这是优化回表开销最直接的手段。尤其对于那些查询频繁、行宽大字段很多的表覆盖索引能把 IO 成本压得很低。比如你要统计某段时间内的订单数量SELECT COUNT(*) FROM orders WHERE status 1 AND create_time BETWEEN 2024-01-01 AND 2024-01-31;如果只建了(status, create_time)联合索引InnoDB 的二级索引页里就包含了这两个字段COUNT 直接扫索引就能算出来不用回表查整行。但如果 SELECT 里带上了amount、customer_name这种不在索引里的字段每命中一条记录都要回一次表IO 多好几倍。经验对于大表高频查询不要轻易写SELECT *。不是说我反对写星号而是当你想利用覆盖索引时多出来的字段可能让整条 SQL 从“索引扫描”退化成“索引扫描回表”。你只需要把 SELECT 的字段列表精简到真正需要的那几个覆盖索引就有机会生效。2.3 函数操作和隐式转换是索引杀手索引列上做函数操作这是最常见、也最隐蔽的索引失效原因。最典型的是对日期字段做格式化比较-- 这种写法create_time 上的索引完全失效 SELECT * FROM orders WHERE DATE(create_time) 2024-06-01; -- 改成范围查询索引正常 SELECT * FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;同理LEFT(name, 3) abc、YEAR(create_time) 2024这类写法都会让 MySQL 对每一条记录先做运算再比较索引自然就没有用武之地。优化思路是改写成“无函数的范围条件”或者把计算列拆出来建生成列索引。隐式类型转换也经常坑人。最常见的是字符串和数字比较-- phone 字段是 varchar 类型传入数字 13800138000 SELECT * FROM users WHERE phone 13800138000;MySQL 会把 phone 列转成数字再比较索引直接失效。解决办法是代码里规范参数类型或者写 SQL 时老老实实加引号SELECT * FROM users WHERE phone 13800138000;判断索引是否失效最好的办法还是 explain 看一眼。key 字段为空type 是 ALL那八成就是踩了这两种情况之一。3. SQL 改写的实战技巧索引和 SQL 是共同作用的。索引建得好SQL 写得稀烂一样快不起来SQL 写得聪明有些时候还能弥补索引的不足。改写不是炫技每一处改写背后都有明确的代价考量。3.1 大表 JOIN 和子查询怎么处理以前我见过不少人喜欢把关联查询写得特别“优雅”一层子查询套一层看起来短执行起来惨不忍睹。MySQL 处理子查询的方式并不总是高效的尤其是IN (SELECT ...)这种有时候会被改写成相关子查询外部每一行都要去执行一次内部查询。比如-- 这写法数据量一大就容易慢 SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE level 3 );如果customers表 level 3 的用户有几千个orders表几百万行这种子查询很容易扫得很痛苦。改写方案是拆出来用 JOINSELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id c.id WHERE c.level 3;这里还有一个容易被忽略的点JOIN 写得好不好取决于驱动表的顺序。MySQL 优化器一般会自己选但如果你发现执行计划里驱动表选错了可以用STRAIGHT_JOIN强制指定顺序。驱动表的原则是用小表驱动大表外层扫描少内层走索引整体成本就低。3.2 分页深度越翻越慢的问题分页慢是业务系统里的高频痛点。LIMIT 1000000, 20这种写法MySQL 不是只读 20 条而是把前 100 万条全扫出来丢掉再取最后 20 条。越往后面翻扫的数据越多自然越来越慢。常用优化手段是把“偏移量”变成“基于索引位置的过滤”-- 传统写法深度分页很慢 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 改写记住上一页最后一条的位置 SELECT * FROM orders WHERE id 1000010 ORDER BY id LIMIT 20;这种“游标式”分页需要业务接口配合把上一页的最后一个 id 传到下一页。它有一个前提id 是连续自增的或者你总能用某个唯一字段做排序。实际项目里很多表的主键是自增整数这个方案实现成本很低但收益巨大。如果你是业务开发建议优先把列表接口改成这种模式尤其数据表超过百万行以后。3.3 OR、IN 和 UNION 的选择OR的常见问题是条件里的几个字段如果不在同一个索引里MySQL 可能被逼着做全表扫。比如SELECT * FROM users WHERE name 张三 OR phone 13800138000;如果 name 和 phone 各有一个单独索引MySQL 理论上可以用index_merge把两个索引结果合并但这是优化器行为不受你控制。更稳定、也更容易预估的写法是把 OR 拆成 UNION ALLSELECT * FROM users WHERE name 张三 UNION ALL SELECT * FROM users WHERE phone 13800138000;注意这里用UNION ALL而不是UNION。UNION 会做去重必然引入排序或哈希操作UNION ALL 只是简单拼接。你本来就不需要去重的时候用 UNION 纯粹是浪费。对于IN只要列表长度可控几百上千以内MySQL 走索引没问题。真正要小心的是 IN 列表特别大比如上万甚至十万个 id这时候生成的索引范围访问成本会很高。我踩过这类坑一个批量查询接口前端传来上万条 id一条 SQL 把 InnoDB 的 range 优化征信都查崩了。这种场景更适合分批查比如每批 1000 个多查几次或者干脆落到临时表里 JOIN。分批不是退步是对数据库的保护。4. 事务和锁也会影响查询效率查询慢不全是索引和 SQL 的问题。很多“间歇性变慢”的故障最后查到根因根本不在查询本身而是事务和锁在捣乱。这个维度经常被忽略但它在高并发场景里非常致命。4.1 长事务一个阻塞半边天长事务的意思是一个事务长时间不提交持有锁不放。InnoDB 是行级锁听起来很乐观但一个事务更新 100 万行行锁要扩大到表锁MySQL 内部有锁升级和间隙锁机制期间其他事务的写入全部排队。更麻烦的是 MVCC 的多版本链。长事务存在时undo log 里要保留大量历史版本其他 session 的普通 SELECT 虽然不会真的被阻塞但需要顺着版本链找到自己可见的版本这个过程如果要遍历很多版本CPU 消耗会明显上升。所以长事务不只是影响写读也会跟着变慢。确认长事务很简单SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;看到长时间未结束的事务去代码里找对应的连接是不是忘了 COMMIT。常见原因包括代码里手动开了事务但异常分支没回滚、一个事务里做了太多外部 RPC 调用、连接池里连接复用时事务状态没清理干净。4.2 行锁等待和间隙锁锁等待导致的慢查询往往表现为“时而快时而慢”而且慢 SQL 日志里Lock_time很大。比如 A 事务更新了某行没提交B 事务去更新同一行就要等 A 提交等多久 Lock_time 就是多久。排查锁等待有两个常用办法。一个是看当前有哪些锁SELECT * FROM performance_schema.data_lock_waits;另一个是看有哪些事务在跑SELECT * FROM sys.innodb_lock_waits;sys.innodb_lock_waits已经把阻塞关系整理好了能直接看到是谁阻塞了谁哪个事务持有锁哪个事务在等待。这个输出会告诉你“阻塞者”和“等待者”顺着去业务侧定位代码就行。间隙锁的问题更隐蔽。在 RR 隔离级别下InnoDB 做范围查询时会锁住不存在的间隙。比如UPDATE orders SET status 2 WHERE amount 1000如果这个条件命中范围很大间隙锁覆盖的范围可能很广导致其他插入操作被阻塞。处理思路是尽量让更新条件更精确、能走唯一索引就走唯一索引、适当情况下把隔离级别降到 RC但要注意业务是否依赖 RR 的可重复读语义不能盲降。经验凡是发现 SQL 本身执行计划没问题、索引也走了但日志里 Lock_time 异常高就要优先查锁等待。先看 innodb_trx再看 lock_waits基本能定位。这个排查路径我走了很多年屡试不爽。5. 常见问题速查和优化案例实录这一节把实际工作中碰到的高频问题和对应的优化手段整理成表再拆两个完整的优化案例给大家一个从现象到方案的完整思路。遇到类似问题可以直接对着查。5.1 高频问题速查表现象可能原因优先排查点常用优化方案查询突然变慢explain 走 ALL索引缺失或索引失效explain 的 type、key建索引或改写索引失效写法列表接口越翻越慢深度分页分页 SQL 的 LIMIT 偏移量游标式分页排序慢Extra 有 Using filesort排序字段没索引EXPLAIN 的 Extra排序字段建索引或改排序方式COUNT 很慢InnoDB 不像 MyISAM 存计数表行数过大用单独计数表或走二级索引 COUNT偶尔卡顿Lock_time 高事务锁等待查 innodb_lock_waits缩短事务减小锁范围表数据很多查询基数不高统计信息不准确EXPLAIN rows 与真实数据差异ANALYZE TABLE 刷新统计信息JOIN 很慢驱动表选错explain 第一行的表STRAIGHT_JOIN 或改写 JOIN 条件大 IN 列表查询慢索引 range 范围过大慢日志里 IN 长度分批查询或临时表 JOIN时快时慢不稳定缓存失效或锁阻塞看 Query_time 和 Lock_time 分别定位阻塞或调整缓存策略这张表不是标准答案但它覆盖了我在生产环境里见到的绝大多数慢查询形态。核心思路是先分清时间是花在“扫描”还是“等待”上——扫描靠索引和 SQL 改写解决等待靠事务和锁解决。5.2 案例一一条带 JOIN 和排序的报表 SQL背景是一个订单报表接口每天定时跑一次跑完生成 Excel。随着订单量突破 300 万这条 SQL 从最初的 3 秒恶化到 47 秒。原始 SQL 大概长这样SELECT u.name, u.phone, o.amount, o.create_time FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 2000;explain 看下来orders 表走了(status, create_time)联合索引问题出在ORDER BY和LIMIT的组合上。如果你想彻底搞懂 LIMIT 2000 对这个排序的影响要看一个细节因为 LEFT JOIN usersorders 每一条匹配后都要去 users 表回查 profile 字段然后全部排完序再取前 2000 条。300 万行的排序是内存放不下的直接落临时文件。优化思路分两步。第一步把排序和取数的条件尽量压在前半段完成先只拿主键或排序字段SELECT o.id, o.amount, o.create_time FROM orders o WHERE o.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 2000;第二步拿到这 2000 个主键后再去 JOIN users 补全用户信息SELECT u.name, u.phone, t.amount, t.create_time FROM ( SELECT o.id, o.amount, o.create_time FROM orders o WHERE o.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 2000 ) t LEFT JOIN users u ON t.user_id u.id -- 注意 t 里要带上 user_id ORDER BY t.create_time DESC;初看 SQL 变长了但它把“大表排序”和“大表 JOIN”分开了。排序只走 orders 的二级索引JOIN 只针对 2000 条结果回表临时文件排序整个被跳过了。优化后整体耗时降到 4 秒以内。这个案例的核心思想是能推迟的关联不要在排序前做。5.3 案例二COUNT(*) 在千万级大表上的优化业务需求是统计某个状态下用户数用于管理后台展示。表 2000 万行原来的查询SELECT COUNT(*) FROM orders WHERE status 4;status 上是有索引的但每次都实时扫描2000 万行就算走二级索引也要秒级返回而且会顶住大量 IO。更要命的是这个数字被好几个页面实时调用频率非常高。我的处理思路是先问业务方这个数字的实时性要求有多高。对方表示误差一分钟之内完全可接受。好那这就不是查询优化问题是计数策略问题了。方案是建立一个统计表CREATE TABLE orders_status_count ( status TINYINT PRIMARY KEY, cnt BIGINT NOT NULL ); -- 每新增、变更一条订单时维护 cnt或者在应用层定期汇总然后统计页面直接读这个表。更新维护的成本远低于每次都扫 2000 万行。如果数据量更大、更新频率更高可以把“计数”改成“定时任务每 5 分钟统计一次刷进统计表”这个方案我实际用了很多年从来没被投诉过。这里我想多说一句很多慢查询的根因其实是用“对实时性要求很高的复杂查询”去实现“其实可以接受延迟的业务需求”。优化不是只能死磕 SQL 和索引有时候重新审视业务需求、调整数据产出方式才是成本最低收益最大的方案。6. 我的实操心得和几条铁律做了这么多年 MySQL 优化踩过的坑不少有几个心得想分享给后来者。它们不深奥但每一条都是真金白银换来的。第一永远不要凭感觉改 SQL。先开慢查询日志先 explain先看锁等待把问题的“物理形状”摸清楚再动手。我见过有人在完全没看执行计划的情况下给一张表连续加了 8 个索引结果查询还是慢因为慢的原因根本不在索引。优化之前先诊断这是一切的前提。第二一次只能改一个变量。改一条 SQL、加一个索引、调整一个事务改一个就压测验证一个。同时改三个东西出问题时你根本不知道是谁的锅。这个原则适用于所有性能优化的现场MySQL 尤其如此。第三索引宁缺毋滥。很多开发同学觉得“索引多不坏事”但每个索引都会拖慢写入、占磁盘空间、增加优化器选择成本。一张 500 万行的表如果上面挂了 15 个索引写入性能一定会受影响。我在实际项目中习惯于先删除从未被使用的冗余索引比如排查performance_schema或sys.schema_unused_indexes你会发现很多索引根本没人用。删掉之后写入和查询都更稳。第四优化是一个循环不是一锤子买卖。数据量在涨、业务模式在变今天的最优写法半年后可能就是瓶颈。建议每隔一段时间重复这个循环慢日志收集一次、把 Top N 慢 SQL 拿出来 explain 一遍、对明显异常的做索引或改写调整。这是个常态运维动作不复杂但坚持下来的团队很少遇到真正的数据库事故。最后再分享一个小技巧。很多团队把“SQL 优化”当成 DBA 的活但我建议让一线开发同学都熟练使用 explain。门槛真的不高花一个下午把执行计划看懂之后写 SQL 的思维方式都会不一样——你会开始考虑表之间的关联成本、索引的匹配方式、排序要不要落地。这个意识建立起来之后很多慢查询在代码评审阶段就被拦掉了根本不会带到生产环境里。