社区所有版块导航
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 CPU 飙升 100% 排查全过程

马哥Linux运维 • 12 月前 • 540 次点击  

半夜两点被电话吵醒:MySQL CPU 飙升 100% 排查全过程

"

适用场景:生产环境 MySQL 数据库 CPU 突然飙升至 100%,业务响应缓慢甚至超时,需要紧急定位和解决问题。

环境要求:MySQL 5.7+ / MariaDB 10.3+,Linux(RHEL/CentOS 7.9+ 或 Ubuntu 20.04+),Performance Schema 已启用,具备 root 或 DBA 权限。


1️⃣ 实施步骤

架构与数据流说明

问题诊断流程:

收到告警(CPU 100%)
    ↓
第一步:确认现象(CPU/内存/IO/网络)
    ↓
第二步:定位问题层级
    ├─ OS 层:系统进程/内核/硬件
    ├─ MySQL 层:慢查询/锁等待/连接数
    └─ 业务层:代码缺陷/流量突增
    ↓
第三步:快速止损
    ├─ Kill 慢查询
    ├─ 限流/降级
    └─ 扩容/切换从库
    ↓
第四步:根因分析
    ├─ 慢查询日志
    ├─ Performance Schema
    └─ EXPLAIN 分析
    ↓
第五步:永久修复(索引优化/SQL 改写/架构调整)

关键组件:

  • • Performance Schema:MySQL 内置性能监控工具,记录所有 SQL 执行统计
  • • 慢查询日志(Slow Query Log):记录执行时间超过阈值的 SQL
  • • PROCESSLIST:当前所有数据库连接和执行中的 SQL
  • • EXPLAIN:SQL 执行计划分析工具
  • • Profiling:单条 SQL 详细性能剖析

数据流向:

  1. 1. 应用发起大量查询 → MySQL 接收连接
  2. 2. MySQL 解析 SQL → 优化器生成执行计划
  3. 3. 执行计划不当(全表扫描/索引失效) → CPU 密集计算
  4. 4. CPU 资源耗尽 → 新请求排队 → 雪崩效应
  5. 5. 定位慢查询 → Kill 或优化 → CPU 恢复

Step 1: 紧急确认现象(第一时间执行)

目标: 确认 CPU 飙升是否由 MySQL 引起,初步判断严重程度

环境信息(示例):

  • • 主库:192.168.1.10 (MySQL 8.0.32 / 16C64G / CentOS 8.5)
  • • 告警时间:2024-01-15 02:13:45
  • • 告警内容:MySQL CPU 使用率 98%,持续 5 分钟
  • • 业务反馈:订单查询超时,支付接口响应慢

RHEL/CentOS 命令:

# 1. 查看整体 CPU 使用率
top -bn1 | head -20
# 预期输出(异常情况):
# %Cpu(s): 95.2 us,  3.1 sy,  0.0 ni,  1.2 id,  0.5 wa
# PID   USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
# 12345 mysql     20   0  16.2g   8.3g   6.1g S  98.3 13.2  45:32.12 mysqld
# 说明:mysqld 进程 CPU 占用 98.3%,问题确认!

# 2. 查看 CPU 各核心使用率(是否某个核心打满)
mpstat -P ALL 1 5
# 预期输出:
# 02:15:01 AM  CPU    %usr   %sys  %iowait    %idle
# 02:15:02 AM    0   99.00   1.00     0.00     0.00  ← CPU0 打满
# 02:15:02 AM    1   98.50   1.50     0.00     0.00  ← CPU1 打满
# 02:15:02 AM    2   12.00   1.00     0.00    87.00  ← CPU2 正常
# 说明:多核心同时打满,说明是并发慢查询;单核打满可能是单个耗时查询

# 3. 查看 MySQL 进程详细资源占用
pidstat -p $(pgrep mysqld) 1 5
# 预期输出:
# 02:15:02 AM   PID    %usr %system  %CPU   CPU  Command
# 02:15:03 AM 12345   96.00    2.00 98.00     0  mysqld
# 02:15:04 AM 12345   97.00    2.00 99.00     1  mysqld

# 4. 检查磁盘 IO(排除 IO 瓶颈)
iostat -xm 2 5
# 关键指标:
# %util:磁盘繁忙程度(> 80% 说明 IO 瓶颈)
# await:IO 平均等待时间(> 10ms 需关注)
# 预期输出(CPU 问题,IO 正常):
# Device  r/s   w/s   rMB/s   wMB/s  %util
# sda     45.2  12.3   3.2     1.5    32.1  ← 正常范围

# 5. 检查内存使用(排除 OOM)
free -h
# 预期输出:
#               total        used        free      shared  buff/cache   available
# Mem:           62Gi        45Gi        2.1Gi        128Mi       15Gi        16Gi
# Swap:          8.0Gi       1.2Gi       6.8Gi
# 说明:可用内存 16GB,无内存压力

# 6. 检查网络连接数(排除连接数耗尽)
ss -s
# 预期输出:
# Total: 1523 (kernel 1680)
# TCP:   1245 (estab 856, closed 234, orphaned 0, synrecv 0, timewait 234/0)
# 说明:连接数正常(MySQL max_connections 默认 151)

Ubuntu/Debian 命令:

# 基本命令相同
sudo apt install -y sysstat  # 安装 mpstat, iostat 等工具
top -bn1 | head -20
mpstat -P ALL 1 5

关键参数解释:

  1. 1. %us(user):用户态 CPU 占用,MySQL 查询计算主要消耗用户态
  2. 2. %sy(system):内核态 CPU 占用,过高说明系统调用频繁(如大量网络 IO)
  3. 3. %wa(iowait):等待 IO 的 CPU 时间,> 30% 说明 IO 瓶颈
  4. 4. %idle:空闲 CPU,接近 0 说明 CPU 完全打满

执行前验证:

# 确认监控工具已安装
which top mpstat iostat pidstat
# 预期输出:显示工具路径

# 确认 MySQL 进程 PID
pgrep -a mysqld
# 预期输出:12345 /usr/sbin/mysqld

执行后验证:

# 生成快照报告(保留现场)
SNAPSHOT_DIR=/tmp/mysql_cpu_issue_$(date +%Y%m%d_%H%M%S)
mkdir -p $SNAPSHOT_DIR

top -bn1 > $SNAPSHOT_DIR/top.txt
mpstat -P ALL 1 3 > $SNAPSHOT_DIR/mpstat.txt
iostat -xm 2 3 > $SNAPSHOT_DIR/iostat.txt
free -h > $SNAPSHOT_DIR/free.txt
ss -s > $SNAPSHOT_DIR/ss.txt

echo"系统快照已保存:$SNAPSHOT_DIR"

常见错误与处理:

# 错误1:CPU 高但 mysqld 进程 CPU 占用低
# 可能原因:其他进程抢占 CPU(如备份脚本、日志清理)
ps aux | sort -nrk 3 | head -10  # 按 CPU 占用排序

# 错误2:%wa(iowait)很高
# 说明:磁盘 IO 瓶颈,不是纯 CPU 问题
# 排查:iotop -oP  # 查看哪个进程在大量读写磁盘

# 错误3:Swap 使用率高
# 说明:内存不足导致频繁 Swap,影响性能
# 紧急处理:swapoff -a && swapon -a  # 清空 Swap(谨慎!)

Step 2: 定位慢查询(MySQL 层排查)

目标: 找到正在消耗大量 CPU 的 SQL 语句

RHEL/CentOS 命令:

# 1. 查看当前所有连接和正在执行的 SQL
mysql -uroot -p -e "SHOW FULL PROCESSLIST\G" | less
# 预期输出(发现慢查询):
# *************************** 12. row ***************************
#      Id: 987654
#    User: app_user
#    Host: 192.168.1.100:45678
#      db: ecommerce
# Command: Query
#    Time: 245  ← 执行了 245 秒!
#   State: Sending data
#    Info: SELECT * FROM orders o
#          LEFT JOIN order_items oi ON o.id = oi.order_id
#          WHERE o.create_time > '2024-01-01'
#          ORDER BY o.create_time DESC

# 2. 统计各状态的连接数
mysql -uroot -p -e "
SELECT state, COUNT(*) AS count
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
GROUP BY state
ORDER BY count DESC;"
# 预期输出:
# state              | count
# Sending data       | 45  ← 大量连接在执行查询
# Locked             | 12  ← 锁等待
# Sorting result     | 8   ← 排序操作

# 3. 找出执行时间最长的查询(Top 10)
mysql -uroot -p -e "
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 10;"
# 预期输出:显示最耗时的 10 个查询

# 4. 使用 Performance Schema 分析(MySQL 5.7+)
# 查询累计执行时间最长的 SQL(历史统计)
mysql -uroot -p -e "
SELECT digest_text AS query,
       count_star AS exec_count,
       ROUND(sum_timer_wait / 1000000000000, 2) AS total_time_sec,
       ROUND(avg_timer_wait / 1000000000000, 2) AS avg_time_sec,
       ROUND(max_timer_wait / 1000000000000, 2) AS max_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10\G"
# 预期输出:
# query: SELECT * FROM `orders` WHERE `create_time` > ?
# exec_count: 12456
# total_time_sec: 3456.78  ← 累计耗时 3456 秒!
# avg_time_sec: 0.28
# max_time_sec: 245.12

# 5. 查看当前正在执行的慢查询(实时)
mysql -uroot -p -e "
SELECT pps.thread_id,
       pps.event_name,
       pps.sql_text,
       ROUND(pps.timer_wait / 1000000000000, 2) AS exec_time_sec,
       pps.lock_time,
       pps.rows_examined,
       pps.rows_sent
FROM performance_schema.events_statements_current pps
JOIN information_schema.PROCESSLIST p ON pps.thread_id = p.id
WHERE pps.sql_text IS NOT NULL
  AND pps.timer_wait > 1000000000000  -- 执行时间 > 1 秒
ORDER BY pps.timer_wait DESC
LIMIT 10\G"

关键参数解释:

  1. 1. Time:SQL 已执行时间(秒),> 10 秒需重点关注
  2. 2. State: Sending data:正在扫描表数据,通常伴随全表扫描
  3. 3. rows_examined:扫描的行数,远大于 rows_sent 说明效率低
  4. 4. digest_text:SQL 模板(参数用 ? 替代),用于统计同类查询

执行后验证:

# 导出慢查询快照
mysql -uroot -p -e "SHOW FULL PROCESSLIST" >  $SNAPSHOT_DIR/processlist.txt
mysql -uroot -p -e "
SELECT digest_text, count_star, sum_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 50" > $SNAPSHOT_DIR/top_queries.txt

cat$SNAPSHOT_DIR/processlist.txt | grep "Sending data" | wc -l
# 预期输出:显示 Sending data 状态的连接数

常见错误与处理:

# 错误1:Performance Schema 未启用
mysql -e "SHOW VARIABLES LIKE 'performance_schema';"
# 输出:performance_schema | OFF
# 解决:编辑 /etc/my.cnf,添加 performance_schema=ON,重启 MySQL

# 错误2:慢查询日志未开启
mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"
# 输出:slow_query_log | OFF
# 解决:动态开启
mysql -e "SET GLOBAL slow_query_log = 1;"
mysql -e "SET GLOBAL long_query_time = 2;"# 记录执行时间 > 2 秒的查询

# 错误3:PROCESSLIST 为空或很少
# 可能原因:查询执行太快,抓不到快照
# 解决:使用 pt-query-digest 分析慢查询日志(历史数据)

Step 3: 紧急止损(Kill 慢查询或限流)

目标: 快速降低 CPU 负载,恢复业务

RHEL/CentOS 命令:

# 方案 A:Kill 特定慢查询(推荐,精准打击)

# 1. 找到慢查询的连接 ID
mysql -uroot -p -e "
SELECT id, user, host, db, time, LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE time > 10  -- 执行时间 > 10 秒
  AND command = 'Query'
  AND info NOT LIKE '%PROCESSLIST%'  -- 排除当前查询本身
ORDER BY time DESC;"
# 预期输出:
# id      | user     | time | query
# 987654  | app_user | 245  | SELECT * FROM orders ...
# 987655  | app_user | 198  | SELECT * FROM order_items ...

# 2. Kill 单个慢查询
mysql -uroot -p -e "KILL 987654;"
# 预期输出:Query OK, 0 rows affected

# 3. 批量 Kill 慢查询(谨慎使用!)
mysql -uroot -p -e "
SELECT CONCAT('KILL ', id, ';') AS kill_cmd
FROM information_schema.PROCESSLIST
WHERE time > 30  -- 执行时间 > 30 秒
  AND user = 'app_user'
  AND command = 'Query';" | grep KILL | mysql -uroot -p

# 验证 Kill 效果
watch -n 2 'mysql -uroot -p -e "SHOW PROCESSLIST" | grep "Sending data" | wc -l'
# 预期:数量逐渐减少

# 方案 B:限制用户并发连接数(防止雪崩)

# 1. 查看当前用户连接数
mysql -uroot -p -e "
SELECT user, COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY user
ORDER BY connections DESC;"
# 预期输出:
# user       | connections
# app_user   | 145  ← 接近 max_connections 上限!

# 2. 临时限制用户最大连接数
mysql -uroot -p -e "ALTER USER 'app_user'@'%' WITH MAX_USER_CONNECTIONS 20;"
# 说明:限制 app_user 最多 20 个连接(原默认 0 = 无限制)

# 3. 验证限制生效
mysql -uroot -p -e "SHOW VARIABLES LIKE 'max_user_connections';"

# 方案 C:应用层限流(需应用配合)

# 1. 通知应用方启用熔断/降级
# 示例:暂时关闭复杂查询接口,返回缓存数据或提示"系统繁忙"

# 2. 在数据库代理层限流(如 ProxySQL)
# ProxySQL 配置示例:
# INSERT INTO mysql_query_rules (rule_id, active, match_pattern, max_qps, apply)
# VALUES (1, 1, '^SELECT.*FROM orders.*', 100, 1);  -- 限制 orders 表查询 QPS 100

# 方案 D:临时切换从库(分流查询)

# 1. 修改应用配置,将部分读查询路由到从库
# 或使用读写分离中间件(ProxySQL/MaxScale)

# 2. 验证从库负载
mysql -h192.168.1.11 -uroot -p -e "SHOW FULL PROCESSLIST;" | wc -l

关键参数解释:

  1. 1. KILL id:终止指定连接,正在执行的 SQL 会回滚
  2. 2.  KILL QUERY id:仅终止当前 SQL,保留连接
  3. 3. MAX_USER_CONNECTIONS:限制单个用户的最大连接数

执行后验证:

# 验证 CPU 负载下降
top -bn1 | grep mysqld
# 预期:CPU 占用从 98% 降至 30% 以下

# 验证活跃连接数
mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_running';"
# 预期输出:Threads_running | 8  (从 145 降至个位数)

# 验证业务恢复
# 测试订单查询接口响应时间
time curl -s "http://api.example.com/orders?user_id=123" > /dev/null
# 预期:real 0m0.245s(从超时恢复到正常)

常见错误与处理:

# 错误1:Kill 后连接数不减少
# 原因:应用有自动重连机制,立即建立新连接
# 解决:同时限制 MAX_USER_CONNECTIONS 或修改应用配置

# 错误2:Kill 后业务完全不可用
# 原因:误 Kill 了正常查询或关键连接
# 解决:检查 Kill 条件(time > 30),避免误杀

# 错误3:CPU 短暂下降后再次飙升
# 原因:未解决根本问题(慢查询仍会再次执行)
# 解决:继续 Step 4 分析根因并优化 SQL

Step 4: 根因分析(EXPLAIN + 慢查询日志)

目标: 分析慢查询的执行计划,找到性能瓶颈

RHEL/CentOS 命令:

# 1. 提取慢查询原始 SQL(从 PROCESSLIST 或慢查询日志)
# 假设慢查询为:
SLOW_SQL="SELECT * FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.create_time > '2024-01-01'
ORDER BY o.create_time DESC"

# 2. 使用 EXPLAIN 分析执行计划
mysql -uroot -p ecommerce -e "EXPLAIN $SLOW_SQL\G"
# 预期输出(问题示例):
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: o
#    partitions: NULL
#          type: ALL  ← 全表扫描!
# possible_keys: NULL
#           key: NULL  ← 未使用索引!
#       key_len: NULL
#           ref: NULL
#          rows: 5000000  ← 扫描 500 万行!
#      filtered: 33.33
#         Extra: Using where; Using temporary; Using filesort  ← 临时表 + 文件排序
# *************************** 2. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: oi
#          type: ALL
#           key: NULL
#          rows: 15000000  ← 订单明细表 1500 万行!
#         Extra: Using where; Using join buffer (Block Nested Loop)

# 关键问题:
# 1. type=ALL:全表扫描
# 2. key=NULL:未使用索引
# 3. rows=5000000:扫描行数巨大
# 4. Extra: Using temporary; Using filesort:使用临时表和文件排序(极慢)

# 3. 使用 EXPLAIN ANALYZE 查看实际执行统计(MySQL 8.0.18+)
mysql -uroot -p ecommerce -e "EXPLAIN ANALYZE $SLOW_SQL\G"
# 预期输出:
# -> Sort: o.create_time DESC  (actual time=245123.456..245123.678 rows=1234567 loops=1)
#     -> Stream results  (actual time=0.123..23456.789 rows=1234567 loops=1)
#         -> Nested loop left join  (actual time=0.234..22345.678 rows=1234567 loops=1)
#             -> Filter: (o.create_time > '2024-01-01')  (actual time=0.123..12345.678 rows=1234567 loops=1)
#                 -> Table scan on o  (actual time=0.056..10234.567 rows=5000000 loops=1)
# 说明:排序花费 245 秒(245123.456 ms),全表扫描 500 万行花费 10 秒

# 4. 查看表结构和索引
mysql -uroot -p ecommerce -e "SHOW CREATE TABLE orders\G"
# 预期输出:
# CREATE TABLE `orders` (
#   `id` bigint NOT NULL AUTO_INCREMENT,
#   `user_id` bigint DEFAULT NULL,
#   `create_time` datetime DEFAULT NULL,  ← 缺少索引!
#   `status` varchar(20) DEFAULT NULL,
#   PRIMARY KEY (`id`)
# ) ENGINE=InnoDB;

mysql -uroot -p ecommerce -e "SHOW INDEX FROM orders\G"
# 预期输出:仅有主键索引,create_time 无索引

# 5. 查看表统计信息
mysql -uroot -p ecommerce -e "
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'ecommerce'
  AND table_name IN ('orders', 'order_items');"
# 预期输出:
# table_name    | table_rows | data_length  | index_length
# orders        | 5000000    | 2147483648   | 104857600  (2GB 数据 + 100MB 索引)
# order_items   | 15000000   | 8589934592   | 314572800  (8GB 数据 + 300MB 索引)

# 6. 分析慢查询日志(历史数据)
# 安装 pt-query-digest(Percona Toolkit)
yum install -y percona-toolkit

# 分析最近 1 小时的慢查询
pt-query-digest --since 1h /var/lib/mysql/slow.log | less
# 预期输出(Top 查询摘要):
# # Profile
# # Rank Query ID           Response time  Calls  R/Call  V/M   Item
# # ==== ================== ============== ====== ======= ===== ====
# #    1 0x1234ABCD5678EF  3456.78 56.2%  12456  0.2777  0.12  SELECT orders order_items
# #    2 0xABCD1234EF5678   890.12 14.5%   5678  0.1567  0.08  SELECT users

# 查看具体 SQL
pt-query-digest --since 1h /var/lib/mysql/slow.log --limit 1

关键参数解释:

  1. 1. type=ALL:全表扫描,最坏的访问类型(应优化为 ref/range/index)
  2. 2. key=NULL:未使用索引,需添加索引
  3. 3. rows:预计扫描行数,越大越慢
  4. 4. Extra: Using filesort:无法使用索引排序,需在内存或磁盘排序(极慢)
  5. 5. Using temporary:使用临时表,通常由 GROUP BY / DISTINCT / UNION 引起

执行后验证:

# 保存执行计划
mysql -uroot -p ecommerce -e "EXPLAIN $SLOW_SQL" > $SNAPSHOT_DIR/explain.txt

# 使用 EXPLAIN FORMAT=JSON 获取更详细信息
mysql -uroot -p ecommerce -e "EXPLAIN FORMAT=JSON $SLOW_SQL\G" > $SNAPSHOT_DIR/explain_json.txt

常见错误与处理:

# 错误1:EXPLAIN 显示使用了索引但仍然慢
# 原因:索引不合适或基数太低
mysql -e "SHOW INDEX FROM orders WHERE Key_name='idx_create_time';"
# 查看 Cardinality(基数),接近总行数说明区分度高

# 错误2:EXPLAIN 无法执行(语法错误)
# 解决:简化 SQL,逐步添加条件定位问题子句

# 错误3:rows 和实际扫描行数不符
# 原因:统计信息过期
mysql -e "ANALYZE TABLE orders;"
# 更新表统计信息

Step 5: SQL 优化与索引添加

目标: 根据 EXPLAIN 结果优化 SQL 和添加索引

RHEL/CentOS 命令:

# 问题诊断:原 SQL 的问题
# 1. WHERE o.create_time > '2024-01-01' → create_time 无索引,全表扫描
# 2. ORDER BY o.create_time DESC → 无法使用索引排序,文件排序
# 3. SELECT * → 返回所有字段,数据量大
# 4. LEFT JOIN → 两表连接无索引,嵌套循环连接

# 优化方案 1:添加索引

# 1. 在 orders.create_time 上创建索引(覆盖 WHERE 和 ORDER BY)
mysql -uroot -p ecommerce -e "
CREATE INDEX idx_create_time ON orders(create_time);"
# 预期输出:Query OK, 0 rows affected (45.23 sec)
# 说明:5000000 行数据建索引约需 45 秒

# 2. 在 order_items.order_id 上创建索引(优化 JOIN)
mysql -uroot -p ecommerce -e "
CREATE INDEX idx_order_id ON order_items(order_id);"
# 预期输出:Query OK, 0 rows affected (2 min 15.67 sec)

# 3. 验证索引创建成功
mysql -uroot -p ecommerce -e "SHOW INDEX FROM orders WHERE Key_name='idx_create_time'\G"
# 预期输出:
# Key_name: idx_create_time
# Column_name: create_time
# Cardinality: 4987654  ← 接近总行数,区分度高

# 4. 再次 EXPLAIN 查看执行计划
mysql -uroot -p ecommerce -e "EXPLAIN $SLOW_SQL\G"
# 预期输出(优化后):
#          type: range  ← 从 ALL 改进为 range
#           key: idx_create_time  ← 使用了索引!
#          rows: 1234567  ← 从 500 万降至 123 万
#         Extra: Using index condition  ← 使用索引条件下推

# 优化方案 2:改写 SQL

# 原 SQL 问题:SELECT * 返回所有字段
# 优化:只选择需要的字段
OPTIMIZED_SQL="
SELECT o.id, o.user_id, o.create_time, o.status,
       oi.product_id, oi.quantity, oi.price
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.create_time > '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 1000;"# 添加 LIMIT,避免返回过多数据

# 验证优化效果
time mysql -uroot -p ecommerce -e "$OPTIMIZED_SQL" > /dev/null
# 预期输出:
# real    0m0.567s  ← 从 245 秒降至 0.5 秒!
# user    0m0.012s
# sys     0m0.008s

# 优化方案 3:分页查询(避免深分页)

# 原 SQL 问题:ORDER BY + LIMIT offset 大导致回表
# 示例:LIMIT 100000, 100 需要扫描 100100 行

# 优化:使用延迟关联(Deferred Join)
OPTIMIZED_SQL_V2="
SELECT o.id, o.user_id, o.create_time, o.status
FROM orders o
INNER JOIN (
    SELECT id
    FROM orders
    WHERE create_time > '2024-01-01'
    ORDER BY create_time DESC
    LIMIT 100000, 100
) t ON o.id = t.id;"
# 说明:子查询仅查询主键,避免回表;外层查询根据主键快速定位

# 优化方案 4:使用覆盖索引

# 创建联合索引(覆盖查询所需所有字段)
mysql -uroot -p ecommerce -e "
CREATE INDEX idx_create_time_status ON orders(create_time, status);"

# 改写 SQL 仅查询索引字段
OPTIMIZED_SQL_V3="
SELECT id, create_time, status
FROM orders
WHERE create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 1000;"

mysql -uroot -p ecommerce -e "EXPLAIN $OPTIMIZED_SQL_V3\G"
# 预期输出:
#         Extra: Using index  ← 覆盖索引,无需回表!

关键参数解释:

  1. 1. CREATE INDEX idx_name ON table(column):创建单列索引
  2. 2. CREATE INDEX idx_name ON table(col1, col2):创建联合索引(最左前缀原则)
  3. 3. LIMIT offset, rows:分页查询,offset 过大导致性能问题
  4. 4. 覆盖索引:查询字段全在索引中,无需回表,Extra 显示 Using index

执行后验证:

# 对比优化前后性能
echo"优化前:" > $SNAPSHOT_DIR/performance_compare.txt
time mysql -uroot -p ecommerce -e "$SLOW_SQL" > /dev/null 2>> $SNAPSHOT_DIR/performance_compare.txt

echo"优化后:" >> $SNAPSHOT_DIR/performance_compare.txt
time mysql -uroot -p ecommerce -e "$OPTIMIZED_SQL" > /dev/null 2>> $SNAPSHOT_DIR/performance_compare.txt

cat$SNAPSHOT_DIR/performance_compare.txt
# 预期输出:
# 优化前:real 3m45.234s
# 优化后:real 0m0.567s
# 提升:99.7%

常见错误与处理:

# 错误1:索引创建时间过长,影响业务
# 解决:使用 ALGORITHM=INPLACE(MySQL 5.6+),在线 DDL
mysql -e "
CREATE INDEX idx_create_time ON orders(create_time)
ALGORITHM=INPLACE, LOCK=NONE;"
# LOCK=NONE:允许读写,不锁表

# 错误2:创建索引后磁盘空间不足
# 检查磁盘空间
df -h /var/lib/mysql
# 索引大小约为数据大小的 20%-50%

# 错误3:索引未生效(仍然全表扫描)
# 原因1:统计信息未更新
mysql -e "ANALYZE TABLE orders;"

# 原因2:查询条件不满足索引使用规则
# 示例:WHERE create_time + INTERVAL 1 DAY > NOW()  ← 字段参与计算,索引失效
# 解决:改写为 WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 DAY)

Step 6: 监控验证与预防

目标: 确认问题解决,建立监控防止再次发生

RHEL/CentOS 命令:

# 1. 持续监控 CPU 使用率
watch -n 5 'top -bn1 | grep mysqld | awk "{print \$9}"'
# 预期:CPU 占用稳定在 10%-30%

# 2. 监控慢查询数量
watch -n 10 'mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE \"Slow_queries\";"'
# 预期输出:
# Slow_queries | 12456  ← 数量不再快速增长

# 3. 监控活跃连接数
watch -n 5 'mysql -uroot -p -e "SHOW STATUS LIKE \"Threads_running\";"'
# 预期:Threads_running 稳定在 5-20

# 4. 验证业务指标
# 查询订单接口 QPS 恢复
mysql -uroot -p -e "
SELECT COUNT(*) AS qps
FROM information_schema.PROCESSLIST
WHERE db='ecommerce' AND time < 1;" | tail -1
# 预期:QPS 恢复到正常水平(如 500-1000)

# 5. 启用慢查询日志长期监控
mysql -uroot -p -e "
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;"# 记录未使用索引的查询

# 验证配置生效
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query%';"
# 预期输出:
# slow_query_log          | ON
# slow_query_log_file     | /var/lib/mysql/slow.log

# 6. 配置慢查询日志自动分析(Crontab)
cat > /usr/local/bin/analyze_slow_log.sh <<'EOF'
#!/bin/bash
REPORT_FILE=/var/log/mysql_slow_report_$(date +%Y%m%d).txt
pt-query-digest --limit 20 /var/lib/mysql/slow.log > $REPORT_FILE
# 发送报告到邮件或监控系统
mail -s "MySQL 慢查询日报" dba@example.com $REPORT_FILE
EOF

chmod +x /usr/local/bin/analyze_slow_log.sh

# Crontab 配置:每天早上 8 点分析昨天的慢查询
0 8 * * * /usr/local/bin/analyze_slow_log.sh

2️⃣ 最小必要原理

核心机制:

MySQL CPU 飙升的本质是 SQL 执行效率低下导致 CPU 资源被大量消耗。主要原因:

  1. 1. 全表扫描:未使用索引,需遍历所有行
  2. 2. 文件排序:无法使用索引排序,需在内存或磁盘排序
  3. 3. 临时表:GROUP BY / DISTINCT / UNION 创建临时表
  4. 4. 嵌套循环连接:多表 JOIN 无索引,笛卡尔积爆炸

执行流程:

  1. 1. SQL 解析:MySQL 接收 SQL,解析语法树
  2. 2. 查询优化:优化器选择执行计划(全表扫描 vs 索引扫描)
  3. 3. 执行引擎:InnoDB 引擎扫描数据、过滤、排序、返回
  4. 4. CPU 消耗点:
  • • 数据扫描:遍历 B+ 树节点、磁盘 IO
  • • 条件过滤:逐行判断 WHERE 条件
  • • 排序:QuickSort / MergeSort 算法(CPU 密集)
  • • 连接:嵌套循环或 Hash Join(CPU + 内存)

为什么加索引能解决问题?

  • • 索引是 B+ 树结构,查找复杂度 O(log N),远优于全表扫描 O(N)
  • • 例如:1000 万行数据,全表扫描需读取 1000 万行;使用索引仅需 log₂(10000000) ≈ 23 次磁盘 IO

为什么会半夜突然发生?

  1. 1. 定时任务:如每日凌晨的数据统计任务执行慢查询
  2. 2. 数据量增长:表行数超过阈值,查询性能突变(如从 100 万增至 1000 万)
  3. 3. 索引失效:统计信息过期、字段类型变更导致索引不可用
  4. 4. 并发突增:活动促销、爬虫攻击导致查询量暴增

3️⃣ 可观测性

监控指标

Linux 原生监控:

# 1. 实时监控 MySQL CPU(每秒刷新)
watch -n 1 'ps aux | grep mysqld | grep -v grep | awk "{print \$3}"'

# 2. 监控慢查询增长趋势
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" > /tmp/slow_queries_before.txt
sleep 60
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" > /tmp/slow_queries_after.txt
diff /tmp/slow_queries_before.txt /tmp/slow_queries_after.txt
# 计算增长率:(after - before) / 60 秒

# 3. 监控 MySQL 关键性能指标
mysqladmin -uroot -p extended-status -r -i 1 | grep -E "Questions|Queries|Slow_queries|Threads_running|Innodb_rows_read"
# 预期输出(每秒):
# | Questions       | 1234   ← 每秒查询数
# | Slow_queries    | 5      ← 每秒慢查询数
# | Threads_running | 12     ← 活跃线程数
# | Innodb_rows_read| 567890 ← 每秒扫描行数(过高说明效率低)

# 4. 监控锁等待(排除锁问题)
mysql -uroot -p -e "
SELECT r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G"

Prometheus 监控配置:

# /etc/prometheus/prometheus.yml
scrape_configs:
-job_name:'mysqld_exporter'
static_configs:
-targets:
-'192.168.1.10:9104'
labels:
instance:'mysql-master'
cluster:'production'

Prometheus 告警规则:

# /etc/prometheus/rules/mysql_cpu_alerts.yml
groups:
-name:mysql_performance
interval:10s
rules:
# 告警1:MySQL CPU 使用率持续 > 80%
-alert:MySQLCPUHigh
expr:rate(process_cpu_seconds_total{job="mysqld_exporter"}[1m])*100>80
for:5m
labels:
severity:critical
annotations:
summary:"MySQL CPU 使用率过高({{ $value | humanize }}%)"
description:"实例:{{ $labels.instance }},持续 5 分钟 CPU > 80%"
action:"1. 检查慢查询:SHOW FULL PROCESSLIST\n2. Kill 慢查询或限流"

# 告警2:慢查询数量激增
-alert:MySQLSlowQueriesIncreasing
expr:rate(mysql_global_status_slow_queries[5m])>10
for:3m
labels:
severity:warning
annotations:
summary:"慢查询数量激增(每秒 {{ $value | humanize }} 条)"
action:"分析慢查询日志:pt-query-digest /var/lib/mysql/slow.log"

# 告警3:活跃连接数过多
-alert:MySQLThreadsRunningHigh
expr:mysql_global_status_threads_running>50
for:5m
labels:
severity:warning
annotations:
summary:"MySQL 活跃连接数过多({{ $value }} 个)"
description:"可能存在慢查询或连接泄漏"

# 告警4:扫描行数与返回行数比例过高
-alert:MySQLRowsExaminedTooHigh
expr:rate(mysql_global_status_innodb_rows_read[5m])/rate(mysql_global_status_questions[5m])>1000
for:5m
labels:
severity:warning
annotations:
summary:"平均每个查询扫描 {{ $value | humanize }} 行"
description:"可能存在全表扫描或缺失索引"

性能基准测试

场景1:索引优化前后对比

# 优化前(无索引)
time mysql -uroot -p ecommerce -e "
SELECT * FROM orders
WHERE create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 1000;" > /dev/null

# 预期输出:
# real    3m45.234s  ← 耗时 3 分 45 秒

# 创建索引
mysql -uroot -p ecommerce -e "CREATE INDEX idx_create_time ON orders(create_time);"

# 优化后(有索引)
time mysql -uroot -p ecommerce -e "
SELECT * FROM orders
WHERE create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 1000;" > /dev/null

# 预期输出:
# real    0m0.567s  ← 耗时 0.567 秒,提升 99.7%

场景2:并发压力测试

# 使用 mysqlslap 测试并发性能
mysqlslap --user=root --password=xxx \
    --host=192.168.1.10 \
    --concurrency=50,100,200 \
    --iterations=5 \
    --query="SELECT * FROM orders WHERE create_time > '2024-01-01' LIMIT 100" \
    --create-schema=ecommerce \
    --verbose

# 预期输出:
# Concurrency: 50
#   Average number of queries per client: 100
#   Average time per query: 0.0123 seconds
# Concurrency: 100
#   Average time per query: 0.0234 seconds  ← 并发增加,延迟上升
# Concurrency: 200
#   Average time per query: 0.0678 seconds  ← 超过阈值,性能下降明显

4️⃣ 常见故障与排错

故障编号
症状
诊断命令
可能根因
快速修复
永久修复
#1
CPU 100% 但无慢查询
SHOW PROCESSLIST
 全是 Sleep
1. 连接池配置过大
2. 连接泄漏未释放
重启应用释放连接
SET GLOBAL wait_timeout=60;
优化应用连接池配置
启用连接超时自动回收
#2
单核 CPU 打满
mpstat -P ALL
 显示 CPU0 100%
1. 单个耗时查询
2. 锁等待串行执行
KILL
 该查询
分析锁等待
优化 SQL 或拆分查询
调整事务隔离级别
#3
CPU 高且 IO 高
iostat -xm
 %util > 80%
磁盘 IO 瓶颈(非纯 CPU 问题)
临时增加 innodb_io_capacity
迁移到 SSD
升级硬件或优化查询减少 IO
#4
优化后仍然慢
EXPLAIN
 显示使用了索引
1. 索引选择性差
2. 数据分布不均
使用 FORCE INDEX 强制索引
创建更合适的联合索引
重新设计表结构
#5
定时任务导致 CPU 飙升
凌晨 2 点固定时间发生
定时统计任务执行慢查询
暂停定时任务
错峰执行
优化统计查询
使用增量统计或物化视图
#6
索引未生效
EXPLAIN
 显示 type=ALL
1. 字段参与计算
2. 隐式类型转换
3. OR 条件
改写 SQL 避免计算
统一字段类型
规范 SQL 编写规范
启用 SQL 审核

详细故障排查案例

案例 1:索引存在但未生效

现象:

# 已创建索引


    

mysql -e "SHOW INDEX FROM orders WHERE Key_name='idx_user_id';"
# 输出:idx_user_id 存在

# 但 EXPLAIN 显示未使用
mysql -e "EXPLAIN SELECT * FROM orders WHERE user_id = '123'\G"
# 输出:
# type: ALL  ← 全表扫描!
# key: NULL

根因分析:

# 原因:隐式类型转换
# user_id 字段类型为 bigint,但查询条件用了字符串 '123'
# MySQL 将索引列转换为字符串比较,导致索引失效

# 查看字段类型
mysql -e "SHOW CREATE TABLE orders\G" | grep user_id
# 输出:`user_id` bigint(20) DEFAULT NULL

# 正确查询(去掉引号)
mysql -e "EXPLAIN SELECT * FROM orders WHERE user_id = 123\G"
# 输出:
# type: ref  ← 使用了索引!
# key: idx_user_id

解决方案:

# 1. 修改应用代码,确保类型匹配
# 错误:WHERE user_id = '123'
# 正确:WHERE user_id = 123

# 2. 或修改字段类型为 varchar(不推荐,影响性能)

案例 2:联合索引顺序错误

现象:

# 已创建联合索引
mysql -e "SHOW INDEX FROM orders WHERE Key_name='idx_status_create_time';"
# 输出:idx_status_create_time (status, create_time)

# 但以下查询未使用索引
mysql -e "EXPLAIN SELECT * FROM orders WHERE create_time > '2024-01-01'\G"
# 输出:key: NULL  ← 未使用索引

根因分析:

# 联合索引遵循 "最左前缀原则"
# 索引 (status, create_time) 只能匹配:
# 1. WHERE status = ?
# 2. WHERE status = ? AND create_time > ?
# 无法匹配:WHERE create_time > ?(跳过了 status)

# 解决方案:调整索引顺序或创建新索引
mysql -e "CREATE INDEX idx_create_time ON orders(create_time);"

5️⃣ 变更与回滚剧本

灰度策略

步骤:

# 阶段 1:线下验证(测试环境)
# 1. 在测试环境复现问题并验证优化方案
# 2. 对比优化前后性能(EXPLAIN + 实际执行时间)
# 3. 压力测试验证并发性能

# 阶段 2:线上灰度(从库先行)
# 1. 在从库创建索引(不影响主库)
mysql -h192.168.1.11 -uroot -p -e "CREATE INDEX idx_create_time ON orders(create_time);"

# 2. 修改应用配置,将部分查询路由到从库
# 3. 观察 1 小时,确认无异常

# 阶段 3:主库变更(业务低峰期)
# 1. 选择凌晨 3-5 点业务低峰期
# 2. 使用在线 DDL 避免锁表
mysql -h192.168.1.10 -uroot -p -e "
CREATE INDEX idx_create_time ON orders(create_time)
ALGORITHM=INPLACE, LOCK=NONE;"

# 3. 实时监控 CPU/慢查询/连接数
watch -n 5 'top -bn1 | grep mysqld'

# 阶段 4:全量上线
# 1. 修改应用代码,使用优化后的 SQL
# 2. 发布新版本,灰度 10% → 50% → 100%
# 3. 持续监控 24 小时

回滚条件与命令

回滚触发条件:

  1. 1. 创建索引导致主库锁表超过 1 分钟
  2. 2. 索引创建后磁盘空间不足(< 10%)
  3. 3. 优化后性能反而下降(QPS 下降 > 20%)

回滚步骤:

# 1. 删除新创建的索引
mysql -uroot -p -e "DROP INDEX idx_create_time ON orders;"

# 2. 恢复应用配置(使用旧版本 SQL)
# 回滚代码版本或修改配置

# 3. 验证回滚效果
mysql -uroot -p -e "SHOW INDEX FROM orders;"
# 确认索引已删除

# 4. 重新评估优化方案

数据与配置备份

备份脚本:

#!/bin/bash
# 文件名:backup_before_optimization.sh
# 用途:SQL 优化前备份表结构和统计信息

BACKUP_DIR=/data/backup/mysql_optimization_$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

# 1. 备份表结构
mysqldump -uroot -p --no-data ecommerce orders order_items > $BACKUP_DIR/schema.sql

# 2. 备份索引信息
mysql -uroot -p -e "SHOW INDEX FROM orders" > $BACKUP_DIR/indexes_orders.txt
mysql -uroot -p -e "SHOW INDEX FROM order_items" > $BACKUP_DIR/indexes_order_items.txt

# 3. 备份表统计信息
mysql -uroot -p -e "SHOW TABLE STATUS FROM ecommerce WHERE Name IN ('orders', 'order_items')" > $BACKUP_DIR/table_stats.txt

# 4. 备份慢查询日志(优化前最后 1 小时)
cp /var/lib/mysql/slow.log $BACKUP_DIR/slow_log_before.log

echo"备份完成:$BACKUP_DIR"

6️⃣ 最佳实践

  1. 1. 开启慢查询日志并定期分析
    # /etc/my.cnf
    [mysqld]
    slow_query_log=1
    slow_query_log_file=/var/lib/mysql/slow.log
    long_query_time=2# 记录执行时间 > 2 秒的查询
    log_queries_not_using_indexes=1# 记录未使用索引的查询
  2. 2. 合理设计索引(遵循原则)
  • • 高频查询的 WHERE / ORDER BY / JOIN 字段必须加索引
  • • 联合索引遵循最左前缀原则
  • • 避免过多索引(影响写入性能),单表索引数 < 5 个
  • • 定期检查无用索引:pt-duplicate-key-checker
  • 3. SQL 编写规范
    -- 避免 SELECT *,只查询需要的字段
    SELECT id, name, create_time FROM orders;

    -- 避免字段参与计算(导致索引失效)
    -- 错误:WHERE DATE(create_time) = '2024-01-15'
    -- 正确:WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16'

    -- 使用 LIMIT 限制返回行数
    SELECT*FROM orders LIMIT 1000;

    -- 避免隐式类型转换
    -- 字段类型 bigint,条件必须用数字:WHERE user_id = 123
  • 4. 定期更新统计信息
    # 每周执行一次
    mysql -uroot -p -e "ANALYZE TABLE orders, order_items, users;"
  • 5. 配置合理的连接池
    # 应用配置(以 Spring Boot 为例)
    spring:
    datasource:
    hikari:
    maximum-pool-size:20# 最大连接数(根据实际并发调整)
    minimum-idle:5# 最小空闲连接
    connection-timeout:30000# 连接超时 30 秒
    idle-timeout:600000# 空闲连接超时 10 分钟
  • 6. 启用 Query Cache(谨慎,MySQL 8.0 已移除)
    # MySQL 5.7 及以下版本
    query_cache_type=1
    query_cache_size=128M
    # 注意:高并发写入场景不适合 Query Cache
  • 7. 使用 Profiling 分析单条 SQL
    SET profiling =1;
    SELECT*FROM orders WHERE create_time >'2024-01-01' LIMIT 1000;
    SHOW PROFILES;
    SHOW PROFILE FOR QUERY 1;  -- 查看第 1 条查询的详细耗时

  • 7️⃣ FAQ

    Q1: CPU 高一定是慢查询导致的吗?A: 不一定,还可能是:

    1. 1. 连接数过多(即使每个查询很快,大量并发也会打满 CPU)
    2. 2. 锁等待(大量线程等待锁释放,消耗 CPU)
    3. 3. 复制延迟(从库应用 binlog 慢,CPU 高)
    4. 4. 系统层面问题(如其他进程抢占 CPU)

    Q2: 为什么有时 EXPLAIN 看起来没问题,但查询仍然慢?A: EXPLAIN 仅显示执行计划,不代表实际执行情况:

    1. 1. 统计信息不准确(rows 列仅为估算值)
    2. 2. 数据分布不均(热点数据导致实际扫描行数远超预期)
    3. 3. 锁等待或磁盘 IO 慢(EXPLAIN 不体现)

    解决:使用 EXPLAIN ANALYZE(MySQL 8.0.18+)查看实际执行统计。

    Q3: 如何判断是否需要添加索引?A: 满足以下任一条件应考虑添加索引:

    1. 1. 查询执行时间 > 2 秒
    2. 2. EXPLAIN 显示 type=ALL(全表扫描)
    3. 3. rows 列 > 10000 且 filtered < 10%
    4. 4. Extra 包含 Using filesort 或 Using temporary

    Q4: 联合索引如何设计?A: 遵循原则:

    1. 1. 等值查询字段在前,范围查询字段在后
    • • 示例:WHERE status=1 AND create_time > '2024-01-01'
    • • 索引:(status, create_time)
  • 2. 选择性高的字段在前
    • • 示例:user_id(区分度高)+ status(只有几个值)
    • • 索引:(user_id, status)
  • 3. 覆盖查询所需字段(避免回表)
    • • 示例:SELECT id, status FROM orders WHERE user_id=123
    • • 索引:(user_id, status, id)

    Q5: 如何避免深分页性能问题?A: 优化方案:

    -- 问题 SQL(深分页)
    SELECT*FROM orders ORDERBY id LIMIT 100000, 100;

    -- 优化方案1:使用子查询延迟关联
    SELECT*FROM orders o
    INNERJOIN (SELECT id FROM orders ORDERBY id LIMIT 100000, 100) t
    ON o.id = t.id;

    -- 优化方案2:使用书签记录(记住上次查询的最大 ID)
    SELECT*FROM orders WHERE id >1234567ORDERBY id LIMIT 100;

    Q6: 如何处理突发流量导致的 CPU 飙升?A: 应急措施:

    1. 1. 限流:在接入层(Nginx/网关)限制 QPS
    2. 2. 降级:关闭非核心功能,返回缓存数据
    3. 3. 扩容:快速增加从库,分流查询
    4. 4. 熔断:暂时返回错误,保护数据库

    Q7: 什么情况下不应该加索引?A:

    1. 1. 表数据量很小(< 1000 行),全表扫描更快
    2. 2. 字段区分度很低(如性别字段只有男/女)
    3. 3. 频繁更新的字段(索引维护开销大)
    4. 4. BLOB/TEXT 类型字段(需使用前缀索引)

    8️⃣ 附录:关键脚本

    脚本 1:MySQL 慢查询实时监控

    #!/bin/bash
    ##############################################################################
    # 文件名:mysql_slow_query_monitor.sh
    # 版本:v1.0.0
    # 用途:实时监控 MySQL 慢查询并告警
    # 说明:每分钟检查一次,发现慢查询自动 Kill 并记录
    ##############################################################################

    set -euo pipefail

    # 配置变量
    MYSQL_USER="root"
    MYSQL_PASSWORD="password"
    SLOW_THRESHOLD=30  # 慢查询阈值(秒)
    MAX_KILL_COUNT=5   # 单次最多 Kill 数量(防止误杀)
    LOG_FILE="/var/log/mysql_slow_monitor.log"
    ALERT_EMAIL="dba@example.com"

    log_msg() {
    echo"[$(date '+%Y-%m-%d %H:%M:%S') ] $*" | tee -a "$LOG_FILE"
    }

    # 查询慢查询列表
    SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
    SELECT id, user, host, db, time, LEFT(info, 200) AS query
    FROM information_schema.PROCESSLIST
    WHERE time > $SLOW_THRESHOLD
      AND command = 'Query'
      AND info NOT LIKE '%PROCESSLIST%'
    ORDER BY time DESC
    LIMIT $MAX_KILL_COUNT;" 2>/dev/null)

    if [ -z "$SLOW_QUERIES" ]; then
    # 无慢查询,退出
    exit 0
    fi

    log_msg "检测到慢查询:"
    echo"$SLOW_QUERIES" | tee -a "$LOG_FILE"

    # 提取慢查询 ID 并 Kill
    SLOW_IDS=$(echo"$SLOW_QUERIES" | awk 'NR>1 {print $1}')

    foridin$SLOW_IDS; do
        log_msg "Killing query ID: $id"
        mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "KILL $id;" 2>/dev/null || log_msg "Failed to kill $id"
    done

    # 发送告警邮件
    echo"$SLOW_QUERIES" | mail -s "MySQL 慢查询告警""$ALERT_EMAIL"

    log_msg "慢查询处理完成"

    Crontab 配置:

    # 每分钟执行一次
    * * * * * /usr/local/bin/mysql_slow_query_monitor.sh

    脚本 2:MySQL 性能诊断一键脚本

    #!/bin/bash
    ##############################################################################
    # 文件名:mysql_performance_diagnosis.sh
    # 版本:v2.0.0
    # 用途:MySQL 性能问题一键诊断
    # 说明:收集系统、MySQL 性能指标,生成诊断报告
    ##############################################################################

    set -euo pipefail

    # 配置变量
    MYSQL_USER="root"
    MYSQL_PASSWORD="password"
    REPORT_DIR="/tmp/mysql_diagnosis_$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$REPORT_DIR"

    echo"========== MySQL 性能诊断开始 =========="
    echo"报告目录:$REPORT_DIR"

    # 1. 系统信息
    echo"1. 收集系统信息..."
    uname -a > "$REPORT_DIR/01_system_info.txt"
    top -bn1 | head -30 >> "$REPORT_DIR/01_system_info.txt"
    free -h >> "$REPORT_DIR/01_system_info.txt"
    df -h >> "$REPORT_DIR/01_system_info.txt"
    mpstat -P ALL 1 5 > "$REPORT_DIR/02_cpu_usage.txt"
    iostat -xm 2 5 > "$REPORT_DIR/03_io_usage.txt"

    # 2. MySQL 全局状态
    echo"2. 收集 MySQL 全局状态..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL STATUS;" > "$REPORT_DIR/04_mysql_status.txt"
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL VARIABLES;" > "$REPORT_DIR/05_mysql_variables.txt"

    # 3. 当前连接和查询
    echo"3. 收集当前连接和查询..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW FULL PROCESSLIST\G" > "$REPORT_DIR/06_processlist.txt"

    # 4. InnoDB 状态
    echo"4. 收集 InnoDB 状态..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW ENGINE INNODB STATUS\G" > "$REPORT_DIR/07_innodb_status.txt"

    # 5. 慢查询统计(Performance Schema)
    echo"5. 分析慢查询..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
    SELECT digest_text, count_star, sum_timer_wait, avg_timer_wait, max_timer_wait
    FROM performance_schema.events_statements_summary_by_digest
    ORDER BY sum_timer_wait DESC
    LIMIT 50;" > "$REPORT_DIR/08_slow_queries.txt"

    # 6. 表统计信息
    echo"6. 收集表统计信息..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
    SELECT table_schema, table_name, table_rows, data_length, index_length
    FROM information_schema.tables
    WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    ORDER BY data_length DESC
    LIMIT 50;" > "$REPORT_DIR/09_table_stats.txt"

    # 7. 索引使用情况
    echo"7. 分析索引使用情况..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
    SELECT object_schema, object_name, index_name, count_star, sum_timer_wait
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE index_name IS NOT NULL
    ORDER BY sum_timer_wait DESC
    LIMIT 50;" > "$REPORT_DIR/10_index_usage.txt"

    # 8. 慢查询日志分析(如果启用)
    if [ -f /var/lib/mysql/slow.log ]; then
    echo"8. 分析慢查询日志..."
        pt-query-digest --limit 20 /var/lib/mysql/slow.log > "$REPORT_DIR/11_slow_log_digest.txt" 2>/dev/null || echo"pt-query-digest 未安装" > "$REPORT_DIR/11_slow_log_digest.txt"
    fi

    # 9. 生成总结报告
    echo"9. 生成总结报告..."
    cat > "$REPORT_DIR/00_summary.txt" <<EOF
    ========== MySQL 性能诊断总结 ==========
    诊断时间:$(date)
    主机名:$(hostname)
    MySQL 版本:$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT VERSION();" | tail -1)

    关键指标:
    - CPU 使用率:$(top -bn1 | grep "Cpu(s)" | awk '{print $2}')
    - 内存使用:$(free -h | grep Mem | awk '{print $3 "/" $2}')
    - 磁盘使用:$(df -h / | tail -1 | awk '{print $5}')
    - MySQL 连接数:$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW STATUS LIKE 'Threads_connected';" | tail -1 | awk '{print $2}')
    - 活跃查询数:$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW STATUS LIKE 'Threads_running';" | tail -1 | awk '{print $2}')
    - 慢查询数:$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | tail -1 | awk '{print $2}')

    详细报告请查看:$REPORT_DIR
    EOF

    cat"$REPORT_DIR/00_summary.txt"
    echo"========== MySQL 性能诊断完成 =========="

    9️⃣ 扩展阅读

    官方文档:

    • • MySQL 性能优化官方文档:https://dev.mysql.com/doc/refman/8.0/en/optimization.html
    • • MySQL EXPLAIN 详解:https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

    深入技术博客:

    • • 美团技术团队:《MySQL 索引原理及慢查询优化》:https://tech.meituan.com/2014/06/30/mysql-index.html
    • • 阿里云 DBA 团队:《MySQL CPU 飙升问题排查实战》:https://developer.aliyun.com/article/

    性能分析工具:

    • • Percona Toolkit:https://www.percona.com/software/database-tools/percona-toolkit
    • • MySQLTuner:https://github.com/major/MySQLTuner-perl
    • • pt-query-digest:https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

    监控方案:

    • • Prometheus + mysqld_exporter:https://github.com/prometheus/mysqld_exporter
    • • PMM(Percona Monitoring and Management):https://www.percona.com/software/database-tools/percona-monitoring-and-management

    文末福利


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

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

    图片
    备注:【监控合集】

    图片

    100%免费领取


    一、zabbix

    图片
    图片

    二、Prometheus


    图片

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

    图片

     以上所有资料获取请扫码

    备注:【监控合集】

    图片

    100%免费领取

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


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