正文

MySQL慢查询排查实战从information_schema到PerconaToolkit监控工具选型与生产环境性能优化方案

/0 浏览量
0921

MySQL慢查询排查实战从information_schema到PerconaToolkit监控工具选型与生产环境性能优化方案

深夜十二点，你收到报警，生产环境的响应时间飙升到5秒以上。这时候，你心里一定在疯狂吐槽为什么没有早点做好监控。

别急，慢查询排查这事儿，我教你几招，让你从手忙脚乱变成稳如老狗。

先别慌，information_schema是你的老朋友

很多新手遇到慢查询，第一反应是打开Percona Toolkit各种高大上的工具。但说真的，information_schema才是MySQL自带的瑞士军刀，它不需要安装任何东西，开箱即用。

查慢查询日志配置状态

SHOW VARIABLES LIKE 'slow_query%';

你会看到这样的输出：

+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/mysql/slow.log       |
| long_query_time     | 2                              |
+---------------------+--------------------------------+

这里有个坑要注意：long_query_time默认是10秒，很多团队把这个值改成了2秒甚至0.1秒来捕获更多慢查询。但千万别设成0，否则所有的查询都会被记录下来，磁盘IO直接炸掉。

实时查看正在执行的慢查询

SELECT 
    id,
    user_host,
    left(info, 80) AS query_preview,
    time,
    state,
    lock_time,
    rows_sent,
    rows_examined
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

这个查询能帮你快速定位当前最”慢”的语句。info字段只取了前80个字符，避免输出太长刷屏，但你可以根据需要调整。

用mysqldumpslow解析慢查询日志

slow.log文件直接打开看，那叫一个痛苦。 几千行的日志，密密麻麻全是SQL，眼睛都要看花了。

这时候，MySQL自带的mysqldumpslow工具就派上用场了：

# 按查询次数排序，取最慢的前10条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按查询时间排序，取最慢的前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 只看包含特定关键词的慢查询
mysqldumpslow -s t -t 10 -g "ORDER BY" /var/log/mysql/slow.log

输出结果大概是这样的：

Count: 150  Time=5.20s (780s)  Lock=0.00s (0s)  Rows=0.0 (0), user[db]@host
  SELECT * FROM orders WHERE status = 'pending' ORDER BY create_time DESC

Count: 80  Time=3.10s (248s)  Lock=0.00s (0s)  Rows=100.0 (8000), user[db]@host
  SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.create_time > '2024-01-01'

看清楚了吗？相同模式的查询被聚合在一起了，让你一眼就能看出哪些SQL最坑。

pt-query-digest：慢查询分析的核武器

如果说mysqldumpslow是瑞士军刀，那pt-query-digest就是手术刀，精准、专业、还带解剖功能。

这是Percona Toolkit的核心工具，需要先安装：

# CentOS/RHEL
yum install percona-toolkit

# Ubuntu/Debian
apt-get install percona-toolkit

基础用法

pt-query-digest /var/log/mysql/slow.log

输出内容非常详尽，分几个部分：

Overall部分 — 告诉你总共分析了多少条查询，时间范围等

# Total: 150 unique queries, 15.2M rows
# Exec time: 1h 23m 15s max, 3.3s avg, 3ms total

Query #1部分 — 按执行时间排序的第一条慢查询

# Query 1: 0.15 QPS, 0.01x concurrency, ID 0x1234 at byte 12345
# This item is included in the report because it qualifies the 'Top' criteria.
# Scores: Apnd: 85.2 / VIndex: 0.0 / Verb: 45 / Keys: 60
# Query_time distribution
  #   1ms
  #  10ms
  # 100ms
  #    1ms  ################################################################
  #   10ms
  #  100ms
  #     1s
  #    10s  ########################################
  #   100s  ################################
# Tables
  # Show CREATE for 'orders'
  CREATE TABLE `orders` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) NOT NULL,
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status_create` (`status`, `create_time`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

这一段很关键，它直接告诉你表的CREATE语句，让你能检查索引是否合理。

按维度过滤和聚合

# 只分析过去24小时的慢查询
pt-query-digest --since 24h /var/log/mysql/slow.log

# 只看特定数据库的查询
pt-query-digest --filter '$db eq "my_database"' /var/log/mysql/slow.log

# 只看执行时间超过10秒的查询
pt-query-digest --min-exec-time 10 /var/log/mysql/slow.log

# 生成HTML报告，方便分享给团队
pt-query-digest --report-type html --output-file /tmp/slow_report.html /var/log/mysql/slow.log

实时捕获慢查询

除了分析历史日志，pt-query-digest还能实时捕获正在执行的慢查询：

# 实时监控30秒的慢查询
pt-query-digest --processlist --no-report --sleep 1 --count 30 \
  h=localhost,u=root,p=your_password

这个功能在排查突发性能问题时特别有用，能让你看到”正在发生的慢查询长什么样”。

除了Percona Toolkit，还有哪些工具可选？

工具选对了，事半功倍。给你盘点一下市面上主流的MySQL监控工具：

工具 特点 适用场景
Percona Toolkit 开源、功能全、命令灵活 日常排查、脚本自动化
pt-online-schema-change 在线修改表结构不锁表 DDL变更、大表加索引
MySQL Enterprise Monitor 商业版、图形化、功能强大 企业级监控、预算充足
PMMA（Percona Monitoring and Management） 开源、Prometheus + Grafana架构 现代化监控、告警集成
MyTOP 类top命令，实时查看 快速查看当前负载
MySQL Workbench 官方工具、图形化 开发环境、小团队

我的建议是：先用Percona Toolkit搭基础，再接PMMA做可视化监控。

PMMA的架构图大概是这样的：

MySQL实例
    ↓ 采集（MySQL Monitor Agent）
Prometheus
    ↓ 存储 + 查询
Grafana
    ↓ 展示 + 告警
运维人员

它的好处是完全开源免费，而且跟阿里云、AWS这些云平台的监控体系能打通，告警可以直接推送到钉钉、企业微信或者PagerDuty。

生产环境性能优化的实战套路

工具只是手段，真正的内功在于优化思路。下面这套流程，是我踩了无数个坑总结出来的：

第一步：找到瓶颈

-- 查看当前系统负载
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Slow_queries';

-- 查看InnoDB缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 计算命中率
-- 命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
-- 理想值应该 > 99%

如果Threads_running持续大于CPU核心数，说明有连接等待，需要排查锁或者慢查询。

第二步：分析慢查询根因

用EXPLAIN逐条分析最慢的SQL：

EXPLAIN FORMAT=JSON 
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.create_time > '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 100;

输出类似这样：

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "o",
          "access_type": "range",
          "possible_keys": ["idx_create_time"],
          "key": "idx_create_time",
          "used_key_parts": ["create_time"],
          "rows": 500000,
          "filtered": 100.0,
          "index_condition": "o.create_time > '2024-01-01'"
        }
      },
      {
        "table": {
          "table_name": "u",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "used_key_parts": ["id"],
          "rows": 1,
          "filtered": 100.0,
          "attached_condition": "u.id = o.user_id"
        }
      }
    ]
  }
}

看几个关键字段：

  • access_type：range表示范围扫描，比ALL（全表扫描）好，但还不够
  • rows：预估扫描行数，50万行偏多
  • key：实际使用的索引

第三步：优化策略

针对上面的例子，常见的优化手段：

1. 添加覆盖索引

-- 假设查询只需要这几个字段
ALTER TABLE orders 
ADD INDEX idx_covering (create_time, user_id, status);

覆盖索引的好处是不需要回表，直接从索引树拿数据，速度快很多。

**2. 避免SELECT * **

-- 优化前
SELECT * FROM orders WHERE user_id = 123;

-- 优化后
SELECT id, order_no, status, create_time 
FROM orders 
WHERE user_id = 123;

3. 分页优化

-- 优化前：深分页性能极差
SELECT * FROM orders LIMIT 100000, 20;

-- 优化后：使用游标分页
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;

4. 大表加索引用pt-osc

# 在线加索引，不锁表
pt-online-schema-change \
  --alter "ADD INDEX idx_status (status)" \
  --execute \
  D=mydb,t=orders,u=root,p=your_password

这个工具的原理是：创建新表 → 复制数据 → 创建索引 → 切换表，整个过程对业务几乎无感知。

第四步：配置调优

慢查询多不一定是SQL的问题，也可能是配置没调好。

-- 查看关键配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'query_cache_size';

innodb_buffer_pool_size 建议设为物理内存的 70%~80%，这是影响MySQL性能最大的单一参数。

innodb_flush_log_at_trx_commit：

  • 1：最安全，每次事务提交都刷盘，性能稍差
  • 2：折中方案，每秒刷盘一次，性能更好
  • 0：最不安全，靠操作系统控制刷盘，性能最好但可能丢数据

生产环境建议用 2，既保证性能又兼顾数据安全。

日常巡检的自动化脚本

手工排查只能救火，建立自动化巡检机制才能防火。

写一个巡检脚本，每天凌晨自动跑：

#!/bin/bash
# mysql_daily_check.sh

LOG_DIR="/var/log/mysql_checks"
DATE=$(date +%Y%m%d)
MYSQL_USER="monitor"
MYSQL_PASS="your_password"
MYSQL_HOST="127.0.0.1"

mkdir -p $LOG_DIR

# 1. 慢查询统计
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "
  SELECT 
    DATE_FORMAT(event_time, '%Y-%m-%d %H:00') AS hour_slot,
    COUNT(*) AS query_count,
    ROUND(AVG(query_time), 2) AS avg_time,
    ROUND(MAX(query_time), 2) AS max_time
  FROM mysql.slow_log
  WHERE event_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
  GROUP BY hour_slot
  ORDER BY hour_slot;
" > $LOG_DIR/slow_log_hourly_${DATE}.txt

# 2. 连接数统计
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "
  SHOW STATUS LIKE 'Threads_connected';
  SHOW STATUS LIKE 'Threads_running';
  SHOW STATUS LIKE 'Max_used_connections';
" > $LOG_DIR/connections_${DATE}.txt

# 3. InnoDB状态
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "
  SHOW ENGINE INNODB STATUS\G
" > $LOG_DIR/innodb_status_${DATE}.txt 2>&1

# 4. 表锁等待
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "
  SELECT * FROM information_schema.innodb_locks;
  SELECT * FROM information_schema.innodb_lock_waits;
" > $LOG_DIR/locks_${DATE}.txt

echo "巡检完成，时间：$(date)"

把这个脚本加到crontab里：

0 2 * * * /opt/scripts/mysql_daily_check.sh

每天早上上班前，花5分钟扫一眼报告，小问题当天解决，大问题提前规划。

总结一下

排查MySQL慢查询，核心就三件事：找问题、分析原因、优化解决。

information_schema是基础，能让你不用装任何工具就快速上手；Percona Toolkit是利器，能把杂乱无章的日志整理得明明白白；而优化思路才是真功夫，要懂得从索引、SQL写法、配置参数多个维度去排查。

最后提醒你一件事：别等报警了才去查慢查询。把监控做起来，把巡检自动化，把优化常态化。当问题还在萌芽阶段就被发现并解决时，你才能真正睡个安稳觉。

加油，生产环境的守护者。

-- 展开阅读全文 --