MySQL慢查询定位与SQL优化实战：DBA核心技能全解析

1. 从一条慢查询说起：为什么SQL优化是DBA的看家本领

做MySQL运维这行十来年，我被问得最多的问题不是“主从怎么搭”，也不是“备份怎么恢复”，而是“这条SQL怎么这么慢”。说实话，搭建高可用架构、配置监控告警这些活儿，只要照着文档走，大部分人都能搞定。但一条SQL摆在面前，能不能在几分钟内定位问题、给出优化方案，这才是区分一个DBA是“会装数据库”还是“懂数据库”的分水岭。

SQL优化这件事，表面上看是调索引、改写法，实际上它牵扯到的东西远比想象中多。你需要理解InnoDB的存储结构，知道B+树索引是怎么组织的，明白优化器为什么会选错执行计划，还得清楚统计信息、字符集、隐式转换这些看似边缘的因素是怎么把一条好好的查询拖垮的。更关键的是，在生产环境里，你往往没有太多试错空间——一条SQL改错了，可能直接导致业务雪崩。

这篇内容适合谁看？如果你已经会写基本的SQL，能看懂执行计划，但在面对复杂慢查询时还缺乏系统性的排查思路，那这篇东西就是为你准备的。如果你是完全的新手，建议先补一下索引的基础知识再来看，不然可能会有些吃力。我不会讲太多教科书上的理论，而是按照实际工作中处理问题的顺序，把SQL优化的完整链路拆开来讲，包括怎么定位问题SQL、怎么分析执行计划、怎么设计索引、怎么改写查询，以及那些只有踩过坑才知道的注意事项。

2. 慢查询定位：先找到敌人再开枪

2.1 开启慢查询日志的正确姿势

很多人知道要开慢查询日志，但配置得并不对。我见过不少环境，slow_query_log是开了，但long_query_time设成了10秒，结果一天下来日志里空空如也，还以为数据库没问题。实际上，10秒的阈值太宽松了，生产环境里超过1秒的查询就值得关注，超过3秒的基本上就是问题SQL。

我的习惯是分阶段设置阈值。刚接手一个新系统时，先把long_query_time设为0.5秒，跑一两天，把所有的“可疑分子”都抓出来。然后根据业务特点调整到1秒或2秒，作为长期监控的阈值。这样既能抓到问题，又不会让日志膨胀得太快。

配置的时候有几个参数需要一起考虑：

[mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 10 min_examined_row_limit = 100

这里重点说两个。log_queries_not_using_indexes这个参数要慎用，在表数据量不大或者查询频率极高的系统里，它会产生大量日志，把磁盘写满。所以一定要配合log_throttle_queries_not_using_indexes使用，限制每分钟记录的条数。min_examined_row_limit也很实用，它让那些扫描行数很少的查询不被记录，避免噪音干扰。

注意：慢查询日志本身对性能有轻微影响，但在大多数场景下可以忽略。真正需要担心的是日志文件写满磁盘，所以一定要配置日志轮转，或者用logrotate定期清理。

2.2 用pt-query-digest做慢查询分析

光有慢查询日志还不够，几百兆的日志文件用肉眼去看是不现实的。我一般用pt-query-digest来做聚合分析，它能按照查询的“指纹”把同类SQL归并到一起，按总耗时排序，让你一眼看出哪些SQL是真正的“耗时大户”。

pt-query-digest --since '2024-01-01 00:00:00' --until '2024-01-02 00:00:00' \ /var/log/mysql/slow.log > /tmp/slow_report.txt

看报告的时候，不要只盯着“单次最慢”的SQL。有些SQL单次执行只要200毫秒，但一天执行了50万次，累计消耗的CPU时间远超那些偶尔跑10秒的报表查询。所以重点看两个指标：总耗时占比和执行次数。总耗时占比高的，说明它是资源消耗大户；执行次数高的，说明它可能是高频核心业务，优化收益最大。

报告里还会显示每个查询的“响应时间分布”，包括最小、最大、平均和各个百分位。如果一条SQL的P99远高于平均值，说明它偶尔会变慢，这通常和锁等待、Buffer Pool命中率波动或者统计信息不准有关，需要进一步排查。

2.3 实时抓取问题SQL的应急手段

有时候慢查询日志还没来得及分析，线上已经在报警了。这时候需要快速定位当前正在执行的慢SQL。最直接的办法是查information_schema.processlist或者用SHOW FULL PROCESSLIST，但这两个只能看到当前瞬间的状态，对于执行时间很短的SQL可能抓不到。

更靠谱的方式是查performance_schema里的events_statements_current表：

SELECT THREAD_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS duration_ms, ROWS_EXAMINED, ROWS_SENT FROM performance_schema.events_statements_current WHERE TIMER_WAIT > 1000000000 ORDER BY TIMER_WAIT DESC;

这个查询能列出当前执行超过1秒的SQL，并且能看到扫描行数和返回行数。如果ROWS_EXAMINED远大于ROWS_SENT，说明索引效率很低，大量行被扫描后又被过滤掉了。

还有一个我常用的“土办法”：连续多次执行SHOW FULL PROCESSLIST，把结果输出到文件，然后对比几次输出，找出那些一直存在的查询。这个方法虽然笨，但在没有部署监控工具的环境里特别管用。

3. 执行计划解读：看懂优化器的内心戏

3.1 EXPLAIN输出字段的深度解读

EXPLAIN是SQL优化的核心工具，但很多人只看type和key两个字段，这就浪费了其他重要信息。我按照重要程度把EXPLAIN的输出字段排个序，并解释每个字段该怎么看。

type字段里，index和ALL都表示全扫描，区别是index扫的是索引树，ALL扫的是数据页。很多人以为index比ALL好，其实在大多数情况下两者性能差不多，因为都要扫描全部数据。真正需要警惕的是ALL出现在大表上。

rows字段是优化器的估算值，不一定准确。如果发现rows很小但实际执行很慢，很可能是统计信息过期了，需要执行ANALYZE TABLE更新统计信息。反过来，如果rows很大但实际很快，可能是优化器低估了索引的选择性。

Extra字段里的信息最丰富。Using filesort表示需要额外排序，如果排序的数据量大，会用到磁盘临时文件，性能急剧下降。Using temporary表示用了临时表，常见于GROUP BY和UNION操作。Using index是好消息，表示覆盖索引，不需要回表。Using where表示在存储引擎层过滤后，Server层还要再过滤一次。

3.2 EXPLAIN FORMAT=JSON的进阶用法

普通EXPLAIN的输出是“概览”，而EXPLAIN FORMAT=JSON会给出更详细的成本信息。我一般在两种情况下用它：一是普通EXPLAIN看不出问题，需要看优化器的成本计算；二是需要对比不同索引方案的预估成本。

EXPLAIN FORMAT=JSON SELECT o.order_id, o.user_id, o.amount FROM orders o WHERE o.user_id = 10086 AND o.status = 'paid' AND o.created_at > '2024-01-01';

输出里会包含query_cost字段，表示优化器估算的总成本。还有read_cost和eval_cost，分别表示读取成本和评估成本。如果read_cost远大于eval_cost，说明IO是瓶颈；反之则是CPU计算是瓶颈。

更重要的是，JSON格式会显示优化器考虑过的所有索引方案，以及为什么最终选择了某个索引。这个信息在排查“为什么没走我建的索引”时特别有用。

3.3 执行计划的常见异常与应对

异常一：type=ALL但表并不大。这种情况不一定要优化。如果表只有几百行，全扫描比走索引还快，因为走索引需要回表，反而多了随机IO。我一般以1000行为分界线，超过1000行的表出现ALL就要警惕了。

异常二：key有值但rows很大。说明索引选择性不好。比如在性别字段上建索引，只有两个值，优化器可能觉得走索引还不如全扫描。这时候需要考虑建联合索引，或者用覆盖索引减少回表。

异常三：Extra出现Using filesort但排序数据量不大。如果rows只有几十行，filesort的开销可以忽略。但如果rows上千，就需要考虑通过索引来消除排序。具体做法是让ORDER BY的字段顺序和索引顺序一致。

异常四：possible_keys有值但key为NULL。说明优化器认为走索引的成本比全扫描还高。常见原因包括：统计信息过期、索引列参与了函数运算、隐式类型转换导致索引失效。需要逐一排查。

4. 索引设计实战：从原理到落地

4.1 B+树索引的底层逻辑

要设计好索引，得先明白InnoDB的索引是怎么组织的。InnoDB使用B+树作为索引结构，这棵树有几个关键特点：所有数据都存在叶子节点，非叶子节点只存索引键和指针；叶子节点之间用双向链表连接，方便范围查询；树的高度通常只有3到4层，意味着查一条记录最多只需要3到4次磁盘IO。

聚簇索引的叶子节点存的是完整的数据行，所以按主键查询是最快的。二级索引的叶子节点存的是索引列的值和主键值，所以通过二级索引查询需要“回表”——先查二级索引找到主键，再拿主键去聚簇索引查完整数据。回表是随机IO，次数多了性能就下来了。

这就引出了覆盖索引的概念：如果查询需要的所有列都在索引里，就不需要回表了。比如索引是(user_id, status, amount)，查询是SELECT amount FROM orders WHERE user_id = 100 AND status = 'paid'，那么直接从索引就能拿到amount，不需要回表。

4.2 联合索引的最左前缀原则

联合索引的列顺序至关重要，因为B+树是按照索引定义的列顺序来排序的。比如索引(a, b, c)，数据先按a排序，a相同再按b排序，b相同再按c排序。这就决定了查询条件必须包含最左边的列，才能用上索引。

-- 索引 (user_id, status, created_at) -- 能用上索引：条件包含最左列 user_id SELECT * FROM orders WHERE user_id = 100; -- 能用上索引：条件包含 user_id 和 status SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'; -- 能用上索引：条件包含全部三列 SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND created_at > '2024-01-01'; -- 用不上索引：缺少最左列 user_id SELECT * FROM orders WHERE status = 'paid'; -- 部分用上索引：user_id 用于查找，created_at 无法用于查找（因为跳过了 status） SELECT * FROM orders WHERE user_id = 100 AND created_at > '2024-01-01';

最后一种情况需要特别注意。虽然created_at在索引里，但因为跳过了status，它只能用于ICP（索引条件下推）过滤，不能用于缩小扫描范围。如果user_id = 100的数据量很大，这个查询还是会扫描很多行。

4.3 索引设计的四步法

我在实际工作中总结了一个索引设计的四步法，按照这个流程走，基本不会出大问题。

第一步：确定查询模式。把需要优化的SQL按业务模块分类，找出WHERE条件、JOIN条件、ORDER BY和GROUP BY的字段。高频查询优先处理，低频的报表查询可以单独考虑。

第二步：评估字段选择性。选择性 = 不重复值数量 / 总行数。选择性越接近1，索引效果越好。比如主键的选择性是1，性别字段的选择性接近0。对于选择性低的字段，一般不建议单独建索引，但可以作为联合索引的一部分。

第三步：设计联合索引列顺序。原则是：等值条件在前，范围条件在后；选择性高的在前，选择性低的在后。但也要考虑最左前缀原则，确保高频查询能用上索引。

第四步：验证和调整。建完索引后用EXPLAIN验证，看type是否提升、rows是否下降、Extra是否出现Using index。如果效果不理想，考虑调整列顺序或者增加覆盖列。

4.4 索引失效的七种典型场景

即使建了索引，也不一定能用上。下面这七种情况是我踩坑最多的，列出来供你参考。

场景一：隐式类型转换。如果字段是VARCHAR类型，查询时传了数字，MySQL会把字段转成数字再比较，导致索引失效。

-- phone 是 VARCHAR 类型 -- 索引失效 SELECT * FROM users WHERE phone = 13800138000; -- 索引有效 SELECT * FROM users WHERE phone = '13800138000';

场景二：在索引列上使用函数。任何对索引列的函数操作都会导致索引失效，因为B+树是按原始值排序的，函数计算后的值无法用于查找。

-- 索引失效 SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01'; -- 索引有效 SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

场景三：使用不等于条件。!=和<>通常会导致全表扫描，因为优化器认为几乎所有的行都可能满足条件。

场景四：使用OR连接不同字段。如果OR两边的字段都有索引，优化器可能用索引合并；但如果有一边没有索引，整个查询就会全扫描。

场景五：LIKE以通配符开头。LIKE '%abc'无法使用索引，因为B+树是从左到右匹配的。LIKE 'abc%'可以用索引。

场景六：联合索引不满足最左前缀。前面已经详细说过，这里不再赘述。

场景七：优化器认为全扫描更便宜。当表数据量很小，或者索引选择性很差时，优化器可能主动选择全扫描。这种情况不一定是问题，但如果表很大还出现全扫描，就需要检查统计信息了。

5. SQL改写技巧：让查询跑得更快

5.1 子查询改JOIN的实战案例

MySQL对子查询的优化一直不太理想，尤其是IN和EXISTS子查询，在旧版本里经常被改写成半连接或者物化临时表，性能很差。我一般会把子查询改写成JOIN，让优化器有更多选择。

-- 原始查询：子查询 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active'); -- 改写为JOIN SELECT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active';

改写之后，优化器可以选择把users作为驱动表，也可以用orders作为驱动表，灵活性更高。在MySQL 8.0里，优化器对子查询的处理已经好了很多，但在复杂场景下，手动改写仍然更可控。

5.2 LIMIT分页的优化方案

深分页是另一个经典问题。LIMIT 1000000, 20这样的查询会先扫描100万零20行，然后丢弃前100万行，只返回最后20行。扫描的行数越多，性能越差。

优化的思路是“延迟关联”：先用覆盖索引查出主键，再用主键去关联原表。

-- 原始查询：深分页 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 优化后：延迟关联 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) AS t ON o.id = t.id;

子查询里的SELECT id用到了覆盖索引，不需要回表，扫描速度很快。拿到20个主键后，再回表查完整数据，只需要20次随机IO。这个方案在数据量大的时候效果非常明显。

还有一种方案是用“游标分页”，也就是记住上一页最后一条记录的ID，下一页从这条记录之后开始查。这种方式性能最好，但需要前端配合，不能跳页。

5.3 COUNT查询的优化思路

COUNT(*)在InnoDB里需要扫描所有行，因为InnoDB不保存精确的行数。如果表很大，COUNT(*)会非常慢。优化的思路有几种：

如果只需要估算值，可以用EXPLAIN的rows字段，或者查information_schema.tables里的table_rows。这两个都是估算值，但速度极快。

如果需要精确值，可以考虑维护一个计数表，在增删数据时同步更新。这种方式适合对实时性要求不高的场景。

如果一定要实时COUNT，尽量缩小范围。比如加上时间条件，只统计最近一个月的数据，这样扫描的行数会少很多。

5.4 UNION ALL替代UNION

UNION会对结果去重，需要用到临时表和排序，开销很大。如果业务上能保证没有重复数据，或者重复数据可以接受，就用UNION ALL。

-- 有去重开销 SELECT id FROM table_a UNION SELECT id FROM table_b; -- 无去重开销 SELECT id FROM table_a UNION ALL SELECT id FROM table_b;

这个优化看起来简单，但在实际中经常被忽略。我见过一个报表查询，把UNION改成UNION ALL之后，执行时间从8秒降到了0.5秒。

6. 常见问题排查与避坑指南

6.1 统计信息不准导致的执行计划突变

这是最让人头疼的问题之一。同一条SQL，昨天跑得好好的，今天突然变慢了，EXPLAIN一看，索引换了。这通常是因为统计信息过期，优化器估算的行数严重偏离实际。

InnoDB的统计信息是采样估算的，默认采样20个数据页。对于大表来说，这个采样比例可能不够，导致估算不准。可以通过调整innodb_stats_persistent_sample_pages参数来增加采样页数，但会增加ANALYZE TABLE的耗时。

我的做法是：对核心大表，在业务低峰期定期执行ANALYZE TABLE，比如每天凌晨一次。同时监控执行计划的变化，如果发现某条SQL的执行计划突然变了，及时介入。

6.2 字符集不一致引发的索引失效

这个问题很隐蔽。如果JOIN的两张表字符集不同，MySQL会做隐式转换，导致索引失效。比如一张表是utf8mb4，另一张是utf8，JOIN的时候就会出问题。

排查方法是看SHOW CREATE TABLE的输出，确认所有相关表的字符集一致。建表时统一用utf8mb4，排序规则用utf8mb4_general_ci或utf8mb4_0900_ai_ci，避免混用。

6.3 锁等待导致的查询变慢

有时候SQL本身没问题，执行计划也正常，但就是慢。这时候要怀疑锁等待。用下面的查询可以看到当前的锁等待情况：

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

找到阻塞的线程后，可以KILL掉阻塞的会话，或者联系业务方确认是否可以提交或回滚。但KILL操作要谨慎，最好先确认阻塞会话在做什么，避免误杀重要事务。

6.4 常见问题速查表

6.5 几个只有踩过坑才知道的经验

经验一：不要迷信“索引越多越好”。每个索引都会增加写操作的开销，因为插入、更新、删除都需要维护索引。我见过一个表建了十几个索引，写入性能惨不忍睹。一般来说，单表的索引数量控制在5个以内比较合理。

经验二：联合索引的列顺序比索引数量更重要。一个设计良好的联合索引，往往能替代多个单列索引。比如(a, b, c)可以支持a、(a,b)、(a,b,c)三种查询模式，比单独建三个索引更高效。

经验三：优化之后一定要回归测试。我遇到过好几次，优化了一条SQL，结果另一条SQL变慢了。这是因为索引是共享资源，改了索引会影响所有用到这个表的查询。所以每次调整索引，都要把相关SQL都跑一遍。

经验四：生产环境改索引要用Online DDL。MySQL 5.6之后支持Online DDL，可以在不锁表的情况下加索引。但要注意，Online DDL在开始和结束阶段仍然需要短暂的元数据锁，所以最好在业务低峰期执行。

经验五：EXPLAIN的结果不一定准。优化器是基于统计信息估算的，实际执行时可能因为数据分布、Buffer Pool状态等因素而不同。所以EXPLAIN只是参考，最终还是要看实际执行时间。

7. 从SQL优化到SQL治理

SQL优化做到一定程度，就不能只盯着单条SQL了，得从“治理”的角度来考虑问题。我现在的做法是建立一套SQL审核机制：所有上线的SQL都要经过审核，检查是否有全表扫描、是否有隐式转换、是否用了SELECT *、是否有深分页。审核不通过的SQL不允许上线。

这套机制刚开始推行的时候，开发同学很抵触，觉得增加了工作量。但运行一段时间后，线上的慢查询数量下降了70%以上，数据库的CPU负载也明显降低。后来开发同学反而主动来找我帮忙看SQL，因为他们发现提前发现问题比上线后救火轻松多了。

SQL治理还包括定期巡检。我每周会跑一次慢查询分析，把新增的慢查询找出来，逐一分析原因。如果是业务增长导致的，就优化SQL；如果是数据量增长导致的，就考虑归档历史数据或者分表。

还有一个容易被忽略的点是连接池配置。很多慢查询其实不是SQL本身的问题，而是连接池太小，导致请求排队。我一般会根据业务峰值QPS和平均查询耗时来估算连接池大小，公式是：连接数 = QPS × 平均耗时（秒）。比如QPS是1000，平均耗时50毫秒，那么连接数至少需要50个。当然还要留一些余量，防止突发流量。

最后说一个我个人的习惯：每次优化完一条SQL，我都会把优化前后的执行计划、执行时间、扫描行数记录下来，整理成一个案例库。时间长了，这个案例库就成了我排查问题的“字典”，遇到类似的问题可以直接参考，效率高很多。而且这些案例在团队内部共享，也能帮助其他同学快速成长。