MySQL 慢查询日志配置与实战分析，让隐藏的性能杀手无处遁形

ynglan2026/02/27mysqlMySQL 慢查询性能优化数据库

MySQL 慢查询日志配置与实战分析，让隐藏的性能杀手无处遁形

一个后台管理系统，用户反馈页面加载很慢。查代码、查 Redis、查服务器负载，都正常。

最后发现是有一条报表 SQL，数据量大了之后从 200ms 变成了 40 秒。

但这条 SQL 平时不会被监控到，因为它在后台任务里，一天只跑一次，报警规则没覆盖到。

问题出在：慢查询日志没配置，或者配置了但没分析。

MySQL 慢查询日志基础

什么是慢查询日志

记录执行时间超过 long_query_time 的 SQL。可以帮我们：

发现哪些 SQL 是性能瓶颈
分析 SQL 的执行频率和耗时分布
评估优化效果

开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 开启所有查询（不包括使用索引的查询）
SET GLOBAL log_queries_not_using_indexes = 'ON';

永久生效需要在 my.cnf 配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

确认慢查询日志是否生效

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 如果值在增长，说明慢查询被记录了

-- 查看日志文件是否生成
SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询日志格式解析

慢查询日志内容：

# Time: 2026-02-27T10:30:00.123456Z
# User@Host: root[root] @ localhost []  Id: 12345
# Query_time: 3.543210  Lock_time: 0.000123 Rows_sent: 20  Rows_examined: 9865432
SET timestamp=1709026200;
SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1 AND o.created_at >= '2026-01-01';

关键字段：

Query_time: 执行时间
Lock_time: 锁等待时间
Rows_sent: 返回行数
Rows_examined: 扫描行数（最重要！）

Rows_examined 和 Rows_sent 的比值是重要指标。比值越大说明扫描了越多无效数据，SQL 越需要优化。

使用 mysqldumpslow 分析日志

MySQL 自带分析工具：

# 安装（如果没有）
yum install -y mysql

# 按执行时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按平均执行时间排序
mysqldumpslow -s a -t 10 /var/lib/mysql/slow.log

# 按查询次数排序（出现最频繁的）
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 匹配特定 SQL
mysqldumpslow -g 'orders' /var/lib/mysql/slow.log

常用参数：

-s: 排序方式，c（次数）、t（时间）、l（锁时间）、r（返回行数）
-t: 取前 N 条
-g: 匹配模式（正则）

使用 pt-query-digest 深度分析

Percona Toolkit 的 pt-query-digest 更强大：

# 安装
yum install -y percona-toolkit

# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log

# 输出到文件
pt-query-digest slow.log > slow_report.txt

# 只看执行时间最长的 20 条
pt-query-digest --order-by 'Query_time:cnt' --limit 20 slow.log

pt-query-digest 会把相似的 SQL 归类统计：

# Profile
# Rank  Query ID           Response time  Calls  R/Call  Item
# ====  ==================  ==============  =====  ======  =====
#    1  0x1234567890ABCDEF  45.2345 45.2%   1234  0.0367  SELECT orders
#    2  0xABCDEF1234567890   12.3456 12.3%    567  0.0218  SELECT users

慢查询优化实战

案例一：缺失索引导致全表扫描

日志显示：

# Query_time: 12.3456  Rows_examined: 5000000
SELECT * FROM orders WHERE user_name = '张三' AND status = 1;

分析：user_name 没有索引，全表扫描 500 万行。

解决方案：

-- 查看表结构
SHOW CREATE TABLE orders;

-- 添加索引
ALTER TABLE orders ADD INDEX idx_user_name_status (user_name, status);

-- 验证优化效果
EXPLAIN SELECT * FROM orders WHERE user_name = '张三' AND status = 1;

案例二：SELECT * 返回过多字段

日志显示：

# Query_time: 8.2345  Rows_sent: 50  Rows_examined: 2000000
SELECT * FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 10086;

分析：JOIN 多表返回所有字段，网络传输和内存占用都很大。

解决方案：

-- 只查需要的字段
SELECT o.id, o.order_no, o.total_amount,
       oi.quantity, oi.price,
       p.name AS product_name
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 10086;

案例三：深分页查询

日志显示：

# Query_time: 15.6789  Rows_examined: 100020  Rows_sent: 20
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;

分析：LIMIT 100000, 20 要跳过前 10 万行，MySQL 要扫描 10 万 + 20 行数据。

解决方案一：游标分页

-- 上一页最后一条的 created_at
SELECT * FROM orders
WHERE created_at < '2026-02-26 23:59:59'
ORDER BY created_at DESC
LIMIT 20;

解决方案二：延迟关联

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY created_at DESC
    LIMIT 100000, 20
) t ON o.id = t.id;

案例四：COUNT(*) 优化

日志显示：

# Query_time: 30.1234  Rows_examined: 8000000  Rows_sent: 1
SELECT COUNT(*) FROM orders WHERE user_id = 10086 AND status = 1;

分析：全表扫描计数。

解决方案：

-- 方案一：加索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 方案二：用 EXPLAIN 估算
EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 10086 AND status = 1;

-- 方案三：维护计数器表
CREATE TABLE order_stats (
    user_id BIGINT PRIMARY KEY,
    total_count INT DEFAULT 0,
    active_count INT DEFAULT 0
);
-- 写入时更新计数器

慢查询监控与告警

实时监控慢查询数量

-- 创建监控视图
CREATE VIEW v_slow_query_stats AS
SELECT
    VARIABLE_VALUE AS slow_queries,
    (SELECT VARIABLE_VALUE FROM performance_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime') AS uptime_seconds,
    VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM performance_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime') AS slow_query_rate
FROM performance_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Slow_queries';

SELECT * FROM v_slow_query_stats;

接入 Prometheus 监控

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']  # mysqld_exporter

使用 mysqld_exporter 暴露指标：

docker run -d \
  --name mysql_exporter \
  -p 9104:9104 \
  -e DATA_SOURCE_NAME="root:password@(localhost:3306)/" \
  prom/mysqld_exporter

关键告警规则：

# alertmanager.yml
groups:
  - name: mysql
    rules:
      - alert: MySQLHighSlowQueryRate
        expr: rate(mysql_global_status_slow_queries[5m]) > 10
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 慢查询速率过高"

慢查询优化 Checklist

收到慢查询告警后，按这个顺序排查：

确认 SQL 真实性：有时候监控采样有误差
EXPLAIN 分析执行计划：看是否全表扫描、是否 using filesort
检查索引：是否缺失、是否正确、是否失效
分析 Rows_examined：扫描行数和返回行数的比值
检查 SQL 类型：SELECT *？JOIN？子查询？深分页？
评估数据量：表数据是否增长到需要分库分表

总结

慢查询日志是数据库性能优化的基础数据来源：

配置：开启慢查询日志 + log_queries_not_using_indexes
分析：用 mysqldumpslow 或 pt-query-digest 归类统计
优化：加索引、改 SQL 结构、减少返回数据量
监控：接入 Prometheus，持续观察慢查询趋势

慢查询优化是一个持续过程，不是一次性工作。建议：

每周 review 一次慢查询日志
新功能上线前检查 SQL 性能
定期分析 Rows_examined 大的查询

把慢查询日志用起来，才能让隐藏的性能杀手无所遁形。

MySQL 慢查询日志配置与实战分析，让隐藏的性能杀手无处遁形

ON THIS PAGE