跳至主要內容

慢查询诊断

SunnyFan大约 12 分钟约 3693 字

慢查询诊断

简介

慢查询是影响数据库性能的最常见问题之一，单条慢查询可能导致连接池耗尽、系统响应超时甚至服务不可用。系统化的慢查询诊断方法能够帮助开发和运维人员快速定位性能瓶颈，从被动救火转向主动优化。本文将围绕慢查询日志配置、EXPLAIN 执行计划分析、查询优化技巧和监控告警体系建设四个方面，全面介绍慢查询诊断与优化的实战方法。

特点

慢查询日志

慢查询日志是 MySQL 内置的性能诊断工具，记录所有执行时间超过指定阈值的 SQL 语句。

开启慢查询日志

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

-- 动态开启慢查询日志（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;           -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询
SET GLOBAL min_examined_row_limit = 100;  -- 至少扫描 100 行才记录
SET GLOBAL log_slow_admin_statements = 'ON';

-- 永久配置（写入 my.cnf）
-- my.cnf 配置
# my.cnf 慢查询日志配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_timestamps = SYSTEM

使用 mysqldumpslow 分析慢查询

# 按查询时间排序，显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按平均查询时间排序
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 按返回记录数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 过滤特定数据库的慢查询
mysqldumpslow -s t -t 10 -g "mydb" /var/log/mysql/slow.log

使用 pt-query-digest 深度分析

# 基础分析，输出最慢的 20 条查询
pt-query-digest /var/log/mysql/slow.log --limit 20

# 分析最近一小时的慢查询
pt-query-digest /var/log/mysql/slow.log --since '1h ago'

# 将分析结果保存到数据库
pt-query-digest /var/log/mysql/slow.log \
    --review h=localhost,D=query_review,t=slow_query_review \
    --history h=localhost,D=query_review,t=slow_query_history

# 分析特定时间段的慢查询并生成报告
pt-query-digest /var/log/mysql/slow.log \
    --since '2024-06-01 00:00:00' \
    --until '2024-06-01 23:59:59' \
    --limit 100 \
    --output slow-report.txt

# 捕获实时查询（通过 processlist）
pt-query-digest --processlist h=localhost --interval 30 --run-time 300

慢查询统计报表

-- 使用 performance_schema 分析慢查询（MySQL 5.7+）
SELECT
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_time_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 3) AS max_time_ms,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined,
    ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS avg_rows_sent,
    FIRST_SEEN,
    LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 1000000000  -- 超过 1 秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

EXPLAIN 分析

EXPLAIN 是分析 SQL 执行计划的核心工具，通过解读执行计划可以准确判断查询的性能瓶颈。

EXPLAIN 输出字段详解

-- 使用 EXPLAIN 分析查询
EXPLAIN
SELECT o.order_id, o.order_no, c.customer_name, od.product_name, od.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_date >= '2024-01-01'
  AND c.region = '华东'
  AND od.quantity > 10
ORDER BY o.order_date DESC
LIMIT 50;

EXPLAIN 输出字段说明

字段说明重点关注值
id查询序号数值越大越先执行
select_type查询类型DERIVED、SUBQUERY 需关注
table访问的表--
type访问类型ALL（全表扫描）需优化
possible_keys可能使用的索引为空表示无可用索引
key实际使用的索引为空表示未使用索引
key_len索引使用长度判断复合索引使用情况
ref索引比较的列--
rows预估扫描行数越小越好
filtered过滤比例越高越好
Extra额外信息Using filesort、Using temporary 需优化

访问类型（type）性能排序

type 值说明性能建议
system系统表，只有一行最优--
const常量查询，最多匹配一行极好--
eq_ref唯一索引扫描很好--
ref非唯一索引扫描好理想目标
range索引范围扫描较好可接受
index全索引扫描一般需评估
ALL全表扫描最差必须优化

EXPLAIN 分析实战

-- 案例1：全表扫描优化
-- 问题查询
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- type: ALL, key: NULL（因为使用了函数导致索引失效）

-- 优化后：避免在索引列上使用函数
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- type: range, key: idx_order_date

-- 案例2：复合索引最左前缀原则
-- 假设有索引 INDEX idx_customer_date (customer_id, order_date)
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-06-01';
-- type: ALL, key: NULL（跳过了最左列 customer_id）

-- 优化后：使用最左前缀
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND order_date = '2024-06-01';
-- type: ref, key: idx_customer_date

-- 案例3：覆盖索引优化
-- 问题查询（回表查询）
EXPLAIN SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 1;
-- Extra: NULL（需要回表）

-- 优化：创建覆盖索引
CREATE INDEX idx_customer_cover ON orders (customer_id, order_date, total_amount);
EXPLAIN SELECT customer_id, order_date, total_amount FROM orders WHERE customer_id = 1;
-- Extra: Using index（索引覆盖，无需回表）

优化技巧

索引优化

-- 1. 为 WHERE 条件和 JOIN 列创建合适的索引
-- 单列索引
CREATE INDEX idx_orders_status ON orders (status);

-- 复合索引（注意最左前缀原则）
CREATE INDEX idx_orders_composite ON orders (customer_id, status, order_date);

-- 2. 使用覆盖索引消除回表
CREATE INDEX idx_orders_cover ON orders (customer_id, order_date)
INCLUDE (order_no, total_amount);

-- 3. 避免索引失效的常见陷阱
-- 错误：隐式类型转换
SELECT * FROM orders WHERE order_no = 12345;  -- order_no 是 VARCHAR 类型
-- 正确：类型匹配
SELECT * FROM orders WHERE order_no = '12345';

-- 错误：索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-06-15';
-- 正确：范围查询
SELECT * FROM orders WHERE create_time >= '2024-06-15 00:00:00'
  AND create_time < '2024-06-16 00:00:00';

-- 错误：LIKE 前缀通配符
SELECT * FROM customers WHERE customer_name LIKE '%张%';
-- 正确：前缀匹配
SELECT * FROM customers WHERE customer_name LIKE '张%';

-- 错误：OR 条件导致索引失效
SELECT * FROM orders WHERE customer_id = 1 OR total_amount > 10000;
-- 正确：使用 UNION 代替
SELECT * FROM orders WHERE customer_id = 1
UNION
SELECT * FROM orders WHERE total_amount > 10000;

SQL 改写优化

-- 1. 避免 SELECT *
-- 错误
SELECT * FROM orders WHERE order_id = 1;
-- 正确：只查询需要的列
SELECT order_id, order_no, total_amount FROM orders WHERE order_id = 1;

-- 2. 分页优化：深度分页性能问题
-- 错误：深度分页扫描大量数据
SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 20;
-- 正确方案1：使用 Deferred Join
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders ORDER BY order_id LIMIT 1000000, 20
) tmp ON o.order_id = tmp.order_id;
-- 正确方案2：使用游标分页（记住上次位置）
SELECT * FROM orders WHERE order_id > 1000000 ORDER BY order_id LIMIT 20;

-- 3. 子查询优化为 JOIN
-- 错误：相关子查询
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_details od WHERE od.order_id = o.order_id AND od.quantity > 100
);
-- 正确：改写为 JOIN
SELECT DISTINCT o.* FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
WHERE od.quantity > 100;

-- 4. 批量操作代替循环单条
-- 错误：循环执行单条插入
-- INSERT INTO logs (...) VALUES (...);  执行 10000 次
-- 正确：批量插入
INSERT INTO logs (log_time, level, message) VALUES
('2024-06-15 10:00:00', 'INFO', 'msg1'),
('2024-06-15 10:00:01', 'INFO', 'msg2'),
('2024-06-15 10:00:02', 'WARN', 'msg3');
-- ... 更多记录

统计信息与索引维护

-- 更新表统计信息（优化器依赖统计信息选择执行计划）
ANALYZE TABLE orders;
ANALYZE TABLE order_details;
ANALYZE TABLE customers;

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'orders';

-- 查看索引的基数（Cardinality 越高，索引选择性越好）
SHOW INDEX FROM orders;

-- 优化表（重建表、整理碎片、更新统计信息）
OPTIMIZE TABLE orders;

-- 强制使用特定索引（仅在确认优化器选错索引时使用）
SELECT * FROM orders FORCE INDEX (idx_order_date)
WHERE order_date >= '2024-01-01' LIMIT 100;

监控告警

建立完善的慢查询监控告警体系，实现慢查询的自动发现和趋势预警。

Prometheus + Grafana 监控

# prometheus.yml - MySQL 监控配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics

# mysqld-exporter 启动参数
# DATA_SOURCE_NAME="user:password@(localhost:3306)/" ./mysqld_exporter

自定义慢查询监控脚本

import pymysql
import time
import json
import smtplib
from datetime import datetime, timedelta
from email.mime.text import MIMEText
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

class SlowQueryMonitor:
    """慢查询监控与告警"""

    def __init__(self, db_config: dict, alert_config: dict):
        self.db_config = db_config
        self.alert_config = alert_config
        self.thresholds = {
            "slow_query_count_per_minute": 10,
            "avg_query_time_ms": 5000,
            "max_query_time_ms": 30000,
            "full_table_scan_count": 5
        }

    def check_slow_queries(self) -> dict:
        """检查最近 5 分钟的慢查询情况"""
        connection = pymysql.connect(**self.db_config)
        cursor = connection.cursor()

        # 查询最近 5 分钟的慢查询统计
        cursor.execute("""
            SELECT
                DIGEST_TEXT AS query_pattern,
                COUNT_STAR AS exec_count,
                ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
                ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_time_ms,
                SUM_ROWS_EXAMINED AS total_rows_examined,
                SUM_ROWS_SENT AS total_rows_sent
            FROM performance_schema.events_statements_summary_by_digest
            WHERE LAST_SEEN >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
              AND AVG_TIMER_WAIT > 1000000000
            ORDER BY AVG_TIMER_WAIT DESC
            LIMIT 20
        """)

        slow_queries = []
        for row in cursor.fetchall():
            slow_queries.append({
                "query": row[0][:200] if row[0] else "N/A",
                "count": row[1],
                "avg_time_ms": row[2],
                "max_time_ms": row[3],
                "rows_examined": row[4],
                "rows_sent": row[5]
            })

        # 检查全表扫描
        cursor.execute("""
            SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, SUM_TIMER_WAIT
            FROM performance_schema.table_io_waits_summary_by_table
            WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
              AND COUNT_READ > 10000
            ORDER BY SUM_TIMER_WAIT DESC
            LIMIT 10
        """)

        full_scans = []
        for row in cursor.fetchall():
            full_scans.append({
                "schema": row[0],
                "table": row[1],
                "read_count": row[2]
            })

        cursor.close()
        connection.close()

        return {
            "timestamp": datetime.now().isoformat(),
            "slow_queries": slow_queries,
            "full_table_scans": full_scans,
            "slow_query_count": len(slow_queries),
            "needs_alert": len(slow_queries) > self.thresholds["slow_query_count_per_minute"]
        }

    def send_alert(self, report: dict):
        """发送告警通知"""
        subject = f"[慢查询告警] {report['timestamp']} 发现 {report['slow_query_count']} 条慢查询"

        body_lines = [
            f"慢查询监控告警报告",
            f"时间: {report['timestamp']}",
            f"慢查询数量: {report['slow_query_count']}",
            f"",
            f"=== TOP 慢查询 ==="
        ]

        for i, q in enumerate(report["slow_queries"][:5], 1):
            body_lines.extend([
                f"",
                f"#{i} 平均耗时: {q['avg_time_ms']}ms, 最大耗时: {q['max_time_ms']}ms",
                f"   执行次数: {q['count']}, 扫描行数: {q['rows_examined']}",
                f"   SQL: {q['query']}"
            ])

        if report["full_table_scans"]:
            body_lines.append(f"\n=== 全表扫描警告 ===")
            for scan in report["full_table_scans"]:
                body_lines.append(
                    f"  {scan['schema']}.{scan['table']} - 读取次数: {scan['read_count']}"
                )

        logger.warning(subject)
        logger.info("\n".join(body_lines))

    def run(self, interval: int = 60):
        """持续监控"""
        logger.info(f"启动慢查询监控，检查间隔: {interval}秒")
        while True:
            try:
                report = self.check_slow_queries()
                if report["needs_alert"]:
                    self.send_alert(report)
                else:
                    logger.debug(f"正常: 慢查询数 {report['slow_query_count']}")
            except Exception as e:
                logger.error(f"监控检查失败: {e}")
            time.sleep(interval)


# 使用示例
if __name__ == "__main__":
    db_config = {
        "host": "localhost", "port": 3306,
        "user": "monitor", "password": "monitor_pwd",
        "database": "performance_schema", "charset": "utf8mb4"
    }
    alert_config = {"smtp_host": "smtp.example.com", "recipients": ["dba@example.com"]}
    monitor = SlowQueryMonitor(db_config, alert_config)
    monitor.run(interval=60)

慢查询告警规则配置

告警级别条件通知方式处理时效
P0 紧急单条查询超过 60 秒电话 + 短信 + IM15 分钟内
P1 严重5 分钟内慢查询超过 50 条短信 + IM30 分钟内
P2 警告平均查询时间超过 5 秒IM + 邮件2 小时内
P3 提示发现全表扫描或未使用索引邮件1 天内

优点

缺点

总结

慢查询诊断是数据库性能优化的核心能力，建议建立从日志收集、自动分析、告警通知到优化验证的完整流程。日常运维中应定期使用 pt-query-digest 分析慢查询日志，重点关注 TOP N 慢查询的优化效果。在进行优化时，务必先用 EXPLAIN 分析执行计划，确认问题根因后再制定优化方案，避免盲目添加索引。同时，应建立慢查询基线指标，通过趋势分析提前发现性能退化，将性能问题消灭在萌芽阶段。最终目标是构建自驱动的性能优化体系，让慢查询的发现、分析和优化形成可持续迭代的改进循环。

关键知识点

  • 数据库主题一定要同时看数据模型、读写模式和执行代价。
  • 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
  • 高可用、备份、迁移和治理与查询优化同样重要。
  • 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

  • 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
  • 上线前准备回滚脚本、备份点和校验方案。
  • 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
  • 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

  • 脱离真实数据分布讨论索引或分片。
  • 只看单条 SQL，不看整条业务链路的事务和锁。
  • 把测试环境结论直接等同于生产环境结论。
  • 脱离真实数据分布设计索引。

进阶路线

  • 继续向执行计划、存储引擎、复制机制和数据治理层深入。
  • 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
  • 沉淀成数据库设计规范、SQL 审核规则和变更流程。
  • 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

  • 当你准备把《慢查询诊断》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
  • 适合数据建模、查询优化、事务控制、高可用和迁移治理。
  • 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

  • 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
  • 所有优化结论都结合执行计划、样本数据和监控指标验证。
  • 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

  • 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
  • 检查执行计划是否走错索引、是否发生排序或全表扫描。
  • 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

  • 如果把《慢查询诊断》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
  • 《慢查询诊断》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
  • 相比默认实现或替代方案，采用《慢查询诊断》最大的收益和代价分别是什么？

延伸阅读

Copyright © 2026 SunnyFan