社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

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

马哥Linux运维 • 1 年前 • 488 次点击  

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

适用场景 & 前置条件

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

前置条件:

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

环境与版本矩阵

组件
版本
说明
MySQL
5.7 / 8.0
生产推荐 8.0
pt-query-digest
3.x
Percona Toolkit
mysqltuner
1.9+
性能调优工具

快速清单

  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 > ref > range > index > ALL
key
实际使用的索引
应为非 NULL
rows
预估扫描行数
越小越好
Extra
额外信息
避免 Using filesort、Using temporary

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 字段优先
  • • 避免过多索引(影响写入性能)
  • 3. 定期分析:每周用 pt-query-digest 分析慢查询
  • 4. EXPLAIN 先行:上线前 EXPLAIN 所有 SQL
  • 5. 监控告警:慢查询速率 > 10/s 告警
  • 6. 索引维护:定期检查未使用索引并删除
  • 7. 参数调优:innodb_buffer_pool_size = 内存 * 0.7
  • 8. 读写分离:大量查询使用从库
  • 9. 分库分表:单表超过 1000 万行考虑分表
  • 10. 缓存层:热点数据加 Redis 缓存

  • 工具汇总

    工具
    用途
    命令示例
    EXPLAIN
    执行计划分析
    EXPLAIN SELECT ...
    pt-query-digest
    慢查询日志分析
    pt-query-digest slow.log
    mysqltuner
    参数优化建议
    ./mysqltuner.pl
    mysqldumpslow
    慢查询日志汇总
    mysqldumpslow -s t -t 10 slow.log
    sys schema
    内置性能分析
    SELECT * FROM sys.schema_unused_indexes

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

    文末福利


    网络监控是保障网络系统和数据安全的重要手段,能够帮助运维人员及时发现并应对各种问题,及时发现并解决,从而确保网络的顺畅运行。

    谢谢一路支持,给大家分享6款开源免费的网络监控工具,并准备了对应的资料文档,建议运维工程师收藏(文末一键领取)。

    图片
    备注:【监控合集】

    图片

    100%免费领取


    一、zabbix

    图片
    图片

    二、Prometheus


    图片

    内容较多,6款常用网络监控工具(zabbix、Prometheus、Cacti、Grafana、OpenNMS、Nagios)不再一一介绍, 需要的朋友扫码备注【监控合集】,即可100%免费领取。

    图片

     以上所有资料获取请扫码

    备注:【监控合集】

    图片

    100%免费领取

    (后台不再回复,扫码一键领取)


    Python社区是高质量的Python/Django开发社区
    本文地址:http://www.python88.com/topic/188568