企业级慢 SQL 排查完全指南：从定位到优化的完整实践

企业级慢 SQL 排查完全指南：从定位到优化的完整实践

文档目标：系统化讲解慢 SQL 的定义、排查方法、监控体系与优化策略，提供基于 MySQL + Prometheus + Grafana + SkyWalking 的生产级实现方案。
适用人群：DBA、后端开发、SRE、架构师
技术栈：MySQL, Prometheus, Grafana, SkyWalking, ELK, pt-query-digest, Docker, Kubernetes

---

一、什么是慢 SQL？

慢 SQL（Slow Query） 是指执行时间超过预设阈值的数据库查询语句。它会：

• ❌ 占用数据库连接，导致连接池耗尽
• ❌ 消耗大量 CPU 和 I/O 资源
• ❌ 引发连锁反应，拖慢整个应用
• ❌ 导致用户体验下降，甚至服务不可用

📊 行业标准：

• Web 应用：响应时间 > 100ms 即为慢 SQL
• 核心交易系统：> 50ms 需关注
• 报表类查询：可放宽至 1-5 秒，但需异步处理

---

二、慢 SQL 的常见原因

原因	占比	示例
缺少索引	40%	WHERE user_id = 123 无索引
索引失效	25%	WHERE YEAR(create_time) = 2023
查询返回过多数据	15%	SELECT * FROM orders LIMIT 10000
锁竞争	10%	长事务阻塞写操作
统计信息过期	5%	优化器选择错误执行计划
应用层问题	5%	N+1 查询、循环中查数据库

---

三、企业级慢 SQL 监控与排查架构

graph TD
    A[数据源] --> B[采集层]
    B --> C[存储层]
    C --> D[分析与可视化]
    D --> E[告警与优化]

    subgraph A [数据源]
        A1[MySQL Slow Log]
        A2[Performance Schema]
        A3[应用层埋点]
        A4[ProxySQL/MaxScale]
    end

    subgraph B [采集层]
        B1[Filebeat] --> A1
        B2[Prometheus MySQL Exporter] --> A2
        B3[OpenTelemetry] --> A3
    end

    subgraph C [存储层]
        C1[Elasticsearch] --> B1
        C2[Prometheus] --> B2
        C3[ClickHouse] --> B3
    end

    subgraph D [分析与可视化]
        D1[Kibana] --> C1
        D2[Grafana] --> C2
        D3[SkyWalking] --> C3
    end

    subgraph E [告警与优化]
        E1[Alertmanager] --> D
        E2[pt-query-digest] --> C1
        E3[SQL Review 工具]
    end

---

四、完整实现方案（生产环境可用）

1. 环境准备：Docker Compose 部署

# docker-compose.yml
version: '3.8'

services:
  # MySQL 数据库
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: testdb
    ports:
      - "3306:3306"
    volumes:
      - ./mysql/conf.d:/etc/mysql/conf.d
      - ./mysql/data:/var/lib/mysql
      - ./mysql/logs:/var/log/mysql

  # MySQL Exporter（Prometheus）
  mysql-exporter:
    image: prom/mysqld-exporter:latest
    environment:
      DATA_SOURCE_NAME: "root:rootpass@(mysql:3306)/"
    ports:
      - "9104:9104"
    depends_on:
      - mysql

  # Prometheus
  prometheus:
    image: prom/prometheus:latest
    ports:
      - "9090:9090"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    depends_on:
      - mysql-exporter

  # Grafana
  grafana:
    image: grafana/grafana:latest
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_USER=admin
      - GF_SECURITY_ADMIN_PASSWORD=monitor123
    volumes:
      - grafana_data:/var/lib/grafana
    depends_on:
      - prometheus

  # ELK（日志分析）
  elasticsearch:
    image: elasticsearch:8.11.3
    environment:
      - discovery.type=single-node
    ports:
      - "9200:9200"

  filebeat:
    image: elastic/filebeat:8.11.3
    volumes:
      - ./filebeat.yml:/usr/share/filebeat/filebeat.yml
      - ./mysql/logs:/var/log/mysql:ro
    depends_on:
      - elasticsearch

volumes:
  grafana_data:

---

2. MySQL 配置（开启慢查询日志）

# mysql/conf.d/slow.cnf
[mysqld]
# 开启慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 定义慢查询阈值（秒）
long_query_time = 0.1

# 记录未使用索引的查询
log_queries_not_using_indexes = ON

# 记录满行的扫描
log_throttle_queries_not_using_indexes = 10

# 最小检查行数才记录（避免记录太多）
min_examined_row_limit = 1000

# 使用微秒级时间戳
log_timestamps = SYSTEM

# 性能模式（用于实时监控）
performance_schema = ON

---

3. Prometheus 配置

# prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  # 采集 MySQL 指标
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']

  # 采集 Prometheus 自身
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

---

4. Filebeat 配置（采集慢日志到 Elasticsearch）

# filebeat.yml
filebeat.inputs:
  - type: filestream
    paths:
      - /var/log/mysql/mysql-slow.log
    parsers:
      - multiline:
          pattern: '^# Time:'
          match: after
          negate: true

processors:
  - dissect:
      tokenizer: "# User@Host: %{mysql.user} @ %{mysql.host} []  Id: %{mysql.id}\n# Query_time: %{mysql.query_time:float}  Lock_time: %{mysql.lock_time:float} Rows_sent: %{mysql.rows_sent:int}  Rows_examined: %{mysql.rows_examined:int}\nSET timestamp=%{mysql.timestamp};\n%{mysql.query}"

  - timestamp:
      field: mysql.timestamp
      layouts:
        - 'unix'
      test:
        - '1700000000'

output.elasticsearch:
  hosts: ["elasticsearch:9200"]

---

五、慢 SQL 排查五大方法

方法 1：使用 EXPLAIN 分析执行计划

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 更详细的分析（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

-- 使用 ANALYZE（MySQL 8.0+）实际执行并统计
ANALYZE SELECT * FROM orders WHERE user_id = 123;

关键字段解读：

字段	说明
type	访问类型：ALL（全表扫描）最差，index > range > ref > eq_ref > const
possible_keys	可能使用的索引
key	实际使用的索引
rows	预估扫描行数
Extra	额外信息：Using filesort、Using temporary 表示性能问题

---

方法 2：利用 Performance Schema 实时监控

-- 查看最近的慢查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000 AS total_sec,
    AVG_TIMER_WAIT / 1000000000 AS avg_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%orders%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

---

方法 3：使用 pt-query-digest 分析慢日志

# 安装 Percona Toolkit
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

# 分析慢日志
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

# 输出示例：
# 1.67s user time, 200ms system time, 24.60M rss, 107.10M vsz
# 680 total, 2 unique, 0.00 QPS, 0.00x concurrency

---

方法 4：应用层埋点（OpenTelemetry）

# Python Flask 示例
from opentelemetry import trace
from opentelemetry.instrumentation.mysql import MySQLInstrumentor
import mysql.connector

# 启用 MySQL 自动埋点
MySQLInstrumentor().instrument()

# 你的查询
conn = mysql.connector.connect(
    host="mysql",
    user="root",
    password="rootpass",
    database="testdb"
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM orders WHERE user_id = 123")

结合 SkyWalking 或 Jaeger 可视化 SQL 调用链。

---

方法 5：使用 Grafana 可视化监控

导入推荐仪表盘：

• ID 7362: MySQL Overview (by Percona)
• ID 14449: MySQL Performance Schema

关键 PromQL 查询：

指标	PromQL
慢查询数量	rate(mysql_slow_queries_total[5m])
平均查询时间	rate(mysql_global_status_slow_queries[5m]) / rate(mysql_global_status_questions[5m])
全表扫描次数	rate(mysql_global_status_select_scan[5m])
临时表创建数	rate(mysql_global_status_created_tmp_tables[5m])

---

六、慢 SQL 优化策略

1. 索引优化

• ✅ 为 WHERE、JOIN、ORDER BY 字段创建索引
• ✅ 使用复合索引遵循“最左前缀”原则
• ✅ 避免索引失效：!=、NOT IN、LIKE '%abc'、函数操作

-- 错误：索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 正确：使用范围查询
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2. SQL 重写

• ✅ 避免 SELECT *，只查需要的字段
• ✅ 用 EXISTS 替代 IN（大数据集）
• ✅ 分页优化：用 WHERE id > last_id LIMIT 100 替代 OFFSET

3. 表结构优化

• ✅ 合理使用分区表（按时间）
• ✅ 冷热数据分离
• ✅ 归档历史数据

4. 配置优化

# my.cnf
innodb_buffer_pool_size = 70% of RAM
innodb_log_file_size = 256M
query_cache_type = 0  # MySQL 8.0 已移除

---

七、自动化告警规则

# alert_rules.yml
groups:
  - name: mysql_alerts
    rules:
      # 1. 慢查询激增
      - alert: HighSlowQueryRate
        expr: rate(mysql_slow_queries_total[5m]) > 5
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "慢查询速率过高"
          description: "每分钟慢查询超过5次"

      # 2. 全表扫描过多
      - alert: ExcessiveTableScan
        expr: rate(mysql_global_status_select_scan[5m]) > 100
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "全表扫描次数过多"
          description: "可能存在缺失索引"

      # 3. 连接数接近上限
      - alert: HighConnectionUsage
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "数据库连接数使用率过高"
          description: "连接数使用率超过80%"

---

八、最佳实践建议

1. 建立 SQL 审核流程：

   • 开发 → 测试 → DBA 审核 → 上线
   • 使用 SQLAdvisor、SOAR 等自动化工具

2. 定期优化：

   • 每周分析 pt-query-digest 报告
   • 每月 Review 索引使用情况

3. 容量规划：

   • 监控数据增长趋势
   • 提前扩容或分库分表

4. 应急预案：

   • 配置 max_execution_time（MySQL 5.7+）
   • 使用 ProxySQL 实现 SQL 拦截

---

九、总结：慢 SQL 治理五步法

步骤	动作
1. 监控	开启慢日志 + Prometheus + Grafana
2. 告警	设置 QPS、延迟、错误率阈值
3. 定位	EXPLAIN + pt-query-digest + SkyWalking
4. 优化	索引、SQL 重写、配置调优
5. 预防	SQL 审核 + 自动化测试 + 容量规划

🔔 记住：

• 慢 SQL 是可以预防的
• 监控是第一步，优化是持续过程
• 让工具代替人工，建立自动化治理流程

现在，立即检查你的数据库，让每一行 SQL 都高效运行！