MySQL 慢查询分析与优化：从诊断到调优的实战手册

适用场景 & 前置条件

适用场景：SQL 性能优化、慢查询定位、索引优化、数据库调优。

前置条件：

• • MySQL 5.7+ / 8.0+
• • root 或 PROCESS 权限
• • 理解索引、执行计划概念

环境与版本矩阵

快速清单

1. 1. 启用慢查询日志
2. 2. 配置慢查询阈值
3. 3. 分析慢查询日志
4. 4. 使用 EXPLAIN 分析执行计划
5. 5. 创建/优化索引
6. 6. 优化 SQL 语句
7. 7. 配置数据库参数
8. 8. 监控慢查询告警

1. 启用慢查询日志

查看当前配置

SHOW VARIABLES LIKE'slow%';
-- slow_query_log         | OFF
-- slow_query_log_file    | /var/lib/mysql/slow.log
-- long_query_time        | 10.000000

SHOW VARIABLES LIKE'log_queries_not_using_indexes';
-- log_queries_not_using_indexes | OFF

动态启用（立即生效，重启失效）

SETGLOBAL slow_query_log ='ON';
SETGLOBAL long_query_time =2;  -- 超过 2 秒记录
SETGLOBAL log_queries_not_using_indexes ='ON';  -- 记录未使用索引的查询

永久配置（修改 my.cnf）

# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 100# 至少扫描 100 行才记录

# 重启 MySQL
sudo systemctl restart mysqld

2. 慢查询日志分析

查看慢查询日志

# 查看最后 20 条
tail -n 20 /var/lib/mysql/slow.log

# 实时监控
tail -f /var/lib/mysql/slow.log

日志示例：

# Time: 2025-10-24T10:15:30.123456Z
# User@Host: app[app] @  [192.168.1.100]
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 1000  Rows_examined: 500000
SETtimestamp=1729764930;
SELECT*FROM orders WHERE user_id =12345AND status ='pending';

关键指标：

• • Query_time：查询耗时（秒）
• • Lock_time：锁等待时间
• • Rows_sent：返回行数
• • Rows_examined：扫描行数（关键，与 Rows_sent 差距越大越差）

使用 pt-query-digest 分析

安装：

# RHEL/CentOS
sudo yum install -y percona-toolkit

# Ubuntu
sudo apt install -y percona-toolkit

分析慢查询日志：

# 生成报告
pt-query-digest /var/lib/mysql/slow.log > slow-report.txt

# 只看 TOP 10
pt-query-digest /var/lib/mysql/slow.log --limit 10

# 分析指定时间范围


    

pt-query-digest /var/lib/mysql/slow.log \
  --since '2025-10-24 00:00:00' \
  --until'2025-10-24 23:59:59'

报告示例：

# Query 1: 0.50 QPS, 2.50s avg time, ID 0xABC123
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100    1000
# Exec time     80    2500s     0.5s     10s    2.5s    5.0s    1.2s    2.0s
# Rows sent     50   50000       1     100      50      80      20      50
# Rows examine  90  500000     100   10000     500    1000     300     500

SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G

关键指标：

• • QPS：每秒查询次数
• • Exec time：总执行时间（pct=占比）
• • Rows examined/Rows sent：扫描行数与返回行数比值（理想接近 1）

3. EXPLAIN 执行计划分析

基础 EXPLAIN

EXPLAIN SELECT*FROM orders WHERE user_id =12345AND status ='pending'\G

输出示例：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL               # 全表扫描（差）
possible_keys: NULL
          key: NULL              # 未使用索引（差）
      key_len: NULL
          ref: NULL
         rows: 500000            # 预估扫描行数
     filtered: 1.00
        Extra: Using where

关键字段详解

type 访问类型（从优到差）：

• • const：主键/唯一索引常量查询（最优）
• • eq_ref：唯一索引 JOIN
• • ref：非唯一索引查询
• • range：范围查询（BETWEEN, IN, >）
• • index：全索引扫描
• • ALL：全表扫描（最差）

EXPLAIN 分析实战

案例 1：全表扫描优化

问题 SQL：

EXPLAIN SELECT*FROM orders WHERE user_id =12345\G
-- type: ALL
-- rows: 500000
-- key: NULL

优化：添加索引

CREATE INDEX idx_user_id ON orders(user_id);

-- 再次 EXPLAIN
EXPLAIN SELECT*FROM orders WHERE user_id =12345\G
-- type: ref
-- rows: 100
-- key: idx_user_id  # 使用索引

案例 2：联合索引优化

问题 SQL：

SELECT*FROM orders
WHERE user_id =12345AND status ='pending'
ORDERBY created_at DESC;

-- EXPLAIN 结果：
-- type: ref
-- key: idx_user_id
-- Extra: Using where; Using filesort  # filesort 性能差

优化：创建覆盖索引

CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 再次 EXPLAIN
-- type: ref
-- key: idx_user_status_created
-- Extra: Using index  # 索引覆盖（最优）

4. 索引优化策略

联合索引最左前缀原则

-- 索引：idx_abc(a, b, c)

-- 有效使用索引
SELECT*FROM t WHERE a =1;                  # 使用 a
SELECT*FROM t WHERE a =1AND b =2;        # 使用 a, b
SELECT*FROM t WHERE a =1AND b =2AND c =3;  # 使用 a, b, c

-- 无法使用索引（跳过 a）
SELECT*FROM t WHERE b =2;                  # 不使用索引
SELECT*FROM t WHERE c =3;                  # 不使用索引

索引选择性

定义：选择性 = DISTINCT(column) / COUNT(*)，越接近 1 越好。

查看选择性：

SELECT
COUNT(DISTINCT user_id) /COUNT(*) AS user_id_selectivity,
COUNT(DISTINCT status) /COUNT(*) AS status_selectivity
FROM orders;
-- user_id_selectivity: 0.85  # 好
-- status_selectivity: 0.02   # 差（只有几种状态）

索引建议：

• • 高选择性字段优先建索引（如 user_id）
• • 低选择性字段不单独建索引（如 status）
• • 联合索引：高选择性在前（user_id, status）

索引检查与优化

查看未使用的索引：

-- MySQL 8.0+
SELECT*FROM sys.schema_unused_indexes;

查看索引大小：

SELECT
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size/1024/1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name ='your_db'
ORDERBY stat_value DESC;

删除重复索引：

    
-- 检查重复索引
SELECT*FROM sys.schema_redundant_indexes;

-- 删除
ALTER TABLE orders DROP INDEX idx_user_id_old;

5. SQL 优化技巧

避免 SELECT *

-- 差
SELECT*FROM orders WHERE user_id =12345;

-- 好（减少网络传输）
SELECT id, user_id, total_amount FROM orders WHERE user_id =12345;

分页优化

-- 差（深度分页慢）
SELECT*FROM orders ORDERBY id LIMIT 100000, 20;

-- 好（使用主键范围）
SELECT*FROM orders WHERE id >100000ORDERBY id LIMIT 20;

IN vs EXISTS

-- 小表驱动大表：用 IN
SELECT*FROM orders WHERE user_id IN (SELECT id FROM users WHERE active =1);

-- 大表驱动小表：用 EXISTS
SELECT*FROM orders o
WHEREEXISTS (SELECT1FROM users u WHERE u.id = o.user_id AND u.active =1);

避免函数/计算破坏索引

-- 差（索引失效）
SELECT*FROM orders WHEREDATE(created_at) ='2025-10-24';

-- 好（使用索引）
SELECT*FROM orders
WHERE created_at >='2025-10-24 00:00:00'
AND created_at <'2025-10-25 00:00:00';

6. 数据库参数优化

InnoDB 缓冲池

-- 查看当前值
SHOW VARIABLES LIKE'innodb_buffer_pool_size';

-- 推荐：物理内存 50-80%
SETGLOBAL innodb_buffer_pool_size =8G;  # 16GB 内存服务器

查询缓存（MySQL 5.7，8.0 已移除）

SHOW VARIABLES LIKE'query_cache%';
-- MySQL 8.0 不再支持 query_cache

连接数

SHOW VARIABLES LIKE'max_connections';
SETGLOBAL max_connections =500;

7. 监控与告警

Prometheus + mysqld_exporter

安装 mysqld_exporter：

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xf mysqld_exporter-*.tar.gz
cd mysqld_exporter-*

# 创建监控用户
mysql -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password';"
mysql -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

# 启动
export DATA_SOURCE_NAME='exporter:password@(localhost:3306)/'
./mysqld_exporter &

PromQL 慢查询告警：

# 慢查询速率
rate(mysql_global_status_slow_queries[5m]) > 10

# 慢查询占比
rate(mysql_global_status_slow_queries[5m]) / rate(mysql_global_status_questions[5m]) > 0.05

实时慢查询监控

-- 查看当前执行中的慢查询
SELECT
    id,
user,
    host,
    db,
    command,
time,
    state,
    info
FROM information_schema.processlist
WHEREtime>2-- 超过 2 秒
ORDERBYtimeDESC;

-- 杀死慢查询
KILL 12345;  -- id 为 12345 的查询

8. 实战案例

案例：订单查询优化

初始 SQL（耗时 5 秒）：

SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status ='pending'
ORDERBY o.created_at DESC
LIMIT 20;

EXPLAIN 分析：

table | type | key  | rows   | Extra
orders| ALL  | NULL | 500000 | Using where; Using filesort
users | ref  | PRIMARY | 1   | NULL

问题：

1. 1. orders 全表扫描
2. 2. Using filesort（排序未使用索引）

优化步骤：

1. 1. 创建联合索引：

CREATE INDEX idx_status_created ON orders(status, created_at);

1. 2. 再次 EXPLAIN：

table | type | key                | rows | Extra
orders| ref  | idx_status_created | 1000 | Using index
users | ref  | PRIMARY            | 1    | NULL

1. 3. 结果：耗时从 5 秒降至 50ms。

最佳实践

1. 1. 慢查询阈值：生产环境设为 1-2 秒
2. 2. 索引原则：

• • 频繁查询字段建索引
• • WHERE、JOIN、ORDER BY 字段优先
• • 避免过多索引（影响写入性能）

工具汇总

文档版本：1.0测试环境：MySQL 8.0测试日期：2025-10