慢查询分析与优化：Explain、Profile 与查询重构

FreeGuideOnline 最新 2026-06-16

慢查询分析与优化：Explain、Profile 与查询重构

数据库的性能往往取决于最慢的那几条 SQL。当应用响应变慢，首先需要定位是哪些查询消耗了过多资源，并理解它们为什么慢，最后才能有针对性地改写或优化。本教程以 MySQL 为例（PostgreSQL、SQL Server 等思路类似），系统地介绍慢查询日志分析、执行计划解读、性能剖析工具以及查询重构的核心方法。

1. 开启与定位慢查询

想要优化，必须先抓到慢查询。MySQL 提供了慢查询日志功能，能够自动记录执行时间超过阈值的 SQL。

1.1 慢查询日志的核心配置

通过以下参数控制慢查询日志的行为：

  • slow_query_log：是否开启慢查询日志（ON/OFF）。
  • slow_query_log_file：日志文件存储路径。
  • long_query_time：慢查询时间阈值，单位为秒。例如设为 0.5 表示超过 500 毫秒的查询都会被记录。
  • log_queries_not_using_indexes：是否记录未使用索引的查询（建议开启，可以发现潜在风险）。
  • min_examined_row_limit：扫描行数超过该值的 SQL 才会被记录，可避免记录大量微小查询。

动态设置方法（无需重启）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

1.2 使用工具分析慢查询日志

直接查看原始日志效率很低，通常需要借助工具：

  • mysqldumpslow（官方自带）
    mysqldumpslow -s t -t 10 /path/to/slow.log
    按总时间排序，展示前 10 条。
  • pt-query-digest（Percona Toolkit）
    功能强大的日志分析器，可以生成详细的报表，找出最耗资源的查询模式。
  • 数据库内置视图：部分数据库如 PostgreSQL 有 pg_stat_statements，MySQL 企业版有 sys 库下的 statements_with_runtimes_in_95th_percentile 等。

拿到具体慢 SQL 后，下一步就是使用执行计划分析它为什么慢。

2. 使用 EXPLAIN 解析执行计划

EXPLAIN 是理解一条查询如何被执行的关键工具。通过它可以看到表的访问顺序、使用的索引、扫描行数、连接方式等信息。

2.1 基本用法

EXPLAIN SELECT ...;

或者对于正在运行的查询（MySQL 8.0+）：

EXPLAIN FOR CONNECTION connection_id;

对于 UPDATE、DELETE 等语句同样适用。

2.2 解读 EXPLAIN 输出中的重要列

  • id：操作的标识符，数字越大越先执行；相同数字从上到下执行。
  • select_type：查询类型，如 SIMPLE（简单查询）、PRIMARY（外层查询）、SUBQUERY（子查询）、DERIVED（派生表，如 FROM 子句中的子查询）等。当出现 DEPENDENT SUBQUERY 时需要特别警惕，因为可能每行外层数据都会执行一次子查询。
  • table：当前访问的表名或别名。
  • partitions：匹配的分区，非分区表为 NULL。
  • type：非常重要的连接类型，从优到劣依次为：
    NULL（无需访问表）> system/const（常量匹配，如主键等值查询）> eq_ref（唯一索引连接）> ref（非唯一索引等值连接）> range（索引范围扫描）> index（全索引扫描）> ALL（全表扫描）。
    一般至少需要达到 range 级别，出现 ALL 通常意味着缺少索引。
  • possible_keys：可能用到的索引候选。
  • key：实际选择的索引，如果为 NULL 则没有使用索引。
  • key_len：使用的索引字节数，可推断出用了复合索引的前几列。
  • ref：索引比较的列或常量。
  • rows：MySQL 估计需要检查的行数。这是一个估算值，行数越少越好。
  • filtered：表示通过条件过滤后剩余行数的百分比估算。配合 rows 可估算最终结果集大小。
  • Extra：非常重要的额外信息，常见的有：
    • Using index：覆盖索引，无需回表，性能好。
    • Using where：在存储引擎返回行后，Server 层进行过滤。
    • Using index condition：使用了索引条件下推（ICP），减少回表次数。
    • Using temporary：需要创建临时表，通常发生在 GROUP BY、ORDER BY 与索引不匹配时。
    • Using filesort：无法利用索引完成排序，需要额外文件排序，数据量大时很慢。
    • Using join buffer (Block Nested Loop)：连接没有使用索引，使用了连接缓冲，效率低下。

2.3 EXPLAIN FORMAT=JSON

在 MySQL 5.6+ 中，使用 EXPLAIN FORMAT=JSON 可获得更详细、结构化信息，包括查询成本（query_cost）等，非常适合程序化分析和对比。

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;

3. 使用 SHOW PROFILE 与 Performance Schema 进行精细剖析

执行计划能告诉我们“怎么做”，但要弄清“时间花在哪”，需要剖析查询内部各阶段的耗时。

3.1 SHOW PROFILE

MySQL 社区版曾支持 SHOW PROFILE，但在 8.0 中被逐步弃用，建议转向 Performance Schema。不过较早版本中依然可用：

SET profiling = 1;
SELECT * FROM orders WHERE user_id = 100;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

输出会显示 Sending data、Sorting result、Creating tmp table 等阶段的耗时，帮助我们识别瓶颈是 CPU、IO 还是锁等待。

3.2 Performance Schema 与 sys 库

Performance Schema 是更现代、低开销的监控基础设施，配合 sys 库让分析更容易。

  • 查看最近慢查询的详细耗时： 
    SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
  • 分析某个查询各阶段耗时（通过事件跟踪）：
    启用 events_statements_history_long 以及 events_stages_* 后，可以像 profiling 一样分析。

3.3 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）

EXPLAIN ANALYZE 会实际执行查询，返回真实的执行时间和每步的行数、耗时（以 Tree 结构展示）。比单纯 EXPLAIN 预估更准确，对定位具体哪一步慢非常有效。

EXPLAIN ANALYZE SELECT ...

输出示例片段：

-> Nested loop inner join  (cost=2.58 rows=10) (actual time=0.124..0.232 rows=10 loops=1)
    -> Index lookup on orders using idx_user_id (user_id=100)  (cost=0.28 rows=5) (actual time=0.056..0.068 rows=5 loops=1)
    -> Index lookup on users using PRIMARY (user_id=orders.user_id)  (cost=0.27 rows=1) (actual time=0.026..0.028 rows=1 loops=5)

actual time 和 rows 显示实际执行情况，如果与预估相差巨大，说明统计信息可能过时。

4. 查询重构：常见的优化模式

理解了查询为什么慢之后，可以有目的地进行改写。重构不是盲目的，要结合执行计划和业务逻辑。

4.1 避免 SELECT *

只选择需要的列，尤其当表有较多大字段（TEXT、BLOB）时。更关键的是，控制列数有利于使用覆盖索引。

优化前：

SELECT * FROM users WHERE email = 'test@example.com';

优化后（若在 email 上有索引）：

SELECT id, name, email FROM users WHERE email = 'test@example.com';

如果 (email, name) 是复合索引，即可成为覆盖索引，避免回表。

4.2 分解复杂的 JOIN

将多个大表的 JOIN 拆分为多次简单查询，在应用层组合数据，有时比一个复杂 SQL 更高效，尤其是在分布式或数据量极大时。

例如，不推荐：

SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
JOIN products p ON o.product_id = p.id
WHERE o.create_time > '2024-01-01';

可以考虑先查询 orders 得到 user_id 和 product_id 的列表，再去分别查询对应的用户和产品信息，但需要权衡网络开销和代码复杂度。

4.3 优化子查询：转为 JOIN 或 EXISTS

很多子查询会导致派生表无法使用索引，或出现 DEPENDENT SUBQUERY。

不推荐：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 10000);

推荐改写为 EXISTS 或 JOIN：

SELECT u.* FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 10000;

EXISTS 写法：

SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 10000);

通常 JOIN 或 EXISTS 都能利用索引，效率远高于 IN + 子查询（特别是 MySQL 5.6 以前的版本）。

4.4 分页优化：避免大偏移量

当执行 LIMIT 1000000, 20 时，MySQL 会扫描前面 100 万行并丢弃，造成巨大开销。

优化方法：

  1. 基于覆盖索引的延迟关联
    先取主键，再关联回原表： 
    SELECT t.* FROM orders t
JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20) tmp
ON t.id = tmp.id;
  2. “游标”法（推荐用于滚动分页）
    记录上一页最后一条的排序字段值，下一页用 WHERE 条件定位： 
    SELECT * FROM orders 
WHERE create_time < '2024-03-01 12:00:00' 
ORDER BY create_time DESC 
LIMIT 20;
    这需要保证排序字段唯一或非空，否则可能丢数据。

4.5 合理使用 UNION 代替 OR 条件

当在 OR 两边是不同列的条件时，往往导致索引失效。可拆分为 UNION 利用各自索引：

原查询：

SELECT * FROM users WHERE phone = '123' OR email = 'abc@test.com';

改写为：

SELECT * FROM users WHERE phone = '123'
UNION
SELECT * FROM users WHERE email = 'abc@test.com';

注意：如果结果可能重复且需要去除，使用 UNION，否则用 UNION ALL 更高效。

4.6 避免在索引列上使用函数或运算

对索引列进行函数操作会导致索引失效：

-- 无法使用 idx_create_time
SELECT * FROM orders WHERE DATE(create_time) = '2024-03-01';

应改为：

SELECT * FROM orders WHERE create_time >= '2024-03-01' AND create_time < '2024-03-02';

同样，隐式类型转换也会导致索引失效，例如在字符串列上比较数字。

4.7 COUNT 优化

  • 统计总行数若允许近似值，可直接使用 EXPLAIN 中的 rows 估算，或查询 information_schema 的 TABLE_ROWS（不精确但很快）。
  • 非精确统计但需要快速获取大表行数，可使用 COUNT(*) 配合覆盖索引快速扫描。

5. 案例实战演示

假设慢查询日志中出现以下 SQL（执行耗时 > 2 秒）：

SELECT u.nickname, o.order_no, o.amount 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' 
ORDER BY o.create_time DESC 
LIMIT 20;

分析步骤：

  1. 查看执行计划
    EXPLAIN 结果可能显示 orders 表上的 type 为 ALL，且 Extra 出现 Using temporary; Using filesort。

  2. 问题定位：

    • orders.user_id 缺少索引，导致全表扫描连接。
    • 排序字段 o.create_time 没有索引，需要额外排序。

  3. 创建索引：

    ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_create_time (create_time);

  4. 优化查询结构：
    用 INNER JOIN 替换 LEFT JOIN 如果业务上 status=‘active’ 的用户一定有订单？但这里场景不明，暂且保留。
    进一步考虑分页优化，使用延迟关联：

    SELECT t.* FROM (
  SELECT u.nickname, o.order_no, o.amount, o.id
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE u.status = 'active'
  ORDER BY o.create_time DESC
  LIMIT 20
) t;

    或者如果仅需展示少量字段，建立覆盖索引：INDEX (user_id, create_time, order_no, amount)。

  5. 验证
    使用 EXPLAIN ANALYZE 确认实际执行时间大幅下降，且 type 变为 ref 或 range，无 Using filesort。

6. 日常优化检查清单

  • 是否开启了慢查询日志并定期分析？
  • 统计信息是否及时更新？（ANALYZE TABLE）
  • 对于高频 OLTP 查询，是否尽量使用了覆盖索引？
  • 是否避免了大事务中的查询，长事务可能导致锁等待和 undo 膨胀？
  • 是否合理设置了 join_buffer_size、sort_buffer_size 等参数？
  • 分区表是否正确修剪了分区？执行计划中 partitions 列是否符合预期？
  • 是否留意 Using temporary 和 Using filesort？通常需要调整索引或改写 SQL。

总结

慢查询优化是一个系统过程：
日志定位 → EXPLAIN 分析 → 工具剖析 → 查询重构 → 验证效果。
工具是辅助，核心在于理解数据库引擎的执行方式，并养成书写“对索引友好”的 SQL 习惯。当掌握 EXPLAIN、PROFILE（或 Performance Schema）和各类改写技巧后，大部分性能问题都能迎刃而解。