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. 应用发起大量查询 → MySQL 接收连接
2. MySQL 解析 SQL → 优化器生成执行计划
3. 执行计划不当（全表扫描/索引失效） → CPU 密集计算
4. CPU 资源耗尽 → 新请求排队 → 雪崩效应
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. %us（user）：用户态 CPU 占用，MySQL 查询计算主要消耗用户态
2. %sy（system）：内核态 CPU 占用，过高说明系统调用频繁（如大量网络 IO）
3. %wa（iowait）：等待 IO 的 CPU 时间，> 30% 说明 IO 瓶颈
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. Time：SQL 已执行时间（秒），> 10 秒需重点关注
2. State: Sending data：正在扫描表数据，通常伴随全表扫描
3. rows_examined：扫描的行数，远大于 rows_sent 说明效率低
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. KILL id：终止指定连接，正在执行的 SQL 会回滚
2. KILL QUERY id：仅终止当前 SQL，保留连接
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. type=ALL：全表扫描，最坏的访问类型（应优化为 ref/range/index）
2. key=NULL：未使用索引，需添加索引
3. rows：预计扫描行数，越大越慢
4. Extra: Using filesort：无法使用索引排序，需在内存或磁盘排序（极慢）
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. CREATE INDEX idx_name ON table(column)：创建单列索引
2. CREATE INDEX idx_name ON table(col1, col2)：创建联合索引（最左前缀原则）
3. LIMIT offset, rows：分页查询，offset 过大导致性能问题
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. 全表扫描：未使用索引，需遍历所有行
2. 文件排序：无法使用索引排序，需在内存或磁盘排序
3. 临时表：GROUP BY / DISTINCT / UNION 创建临时表
4. 嵌套循环连接：多表 JOIN 无索引，笛卡尔积爆炸

执行流程：

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

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

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

为什么会半夜突然发生？

1. 定时任务：如每日凌晨的数据统计任务执行慢查询
2. 数据量增长：表行数超过阈值，查询性能突变（如从 100 万增至 1000 万）
3. 索引失效：统计信息过期、字段类型变更导致索引不可用
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 分钟
2. 索引创建后磁盘空间不足（< 10%）
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. 开启慢查询日志并定期分析

   # /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. 合理设计索引（遵循原则）
   • 高频查询的 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. 连接数过多（即使每个查询很快，大量并发也会打满 CPU）
2. 锁等待（大量线程等待锁释放，消耗 CPU）
3. 复制延迟（从库应用 binlog 慢，CPU 高）
4. 系统层面问题（如其他进程抢占 CPU）

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

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

解决：使用 EXPLAIN ANALYZE（MySQL 8.0.18+）查看实际执行统计。

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

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

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

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 ORDER BY id LIMIT 100000, 100;

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

-- 优化方案2：使用书签记录（记住上次查询的最大 ID）
SELECT * FROM orders WHERE id > 1234567 ORDER BY id LIMIT 100;

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

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

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

1. 表数据量很小（< 1000 行），全表扫描更快
2. 字段区分度很低（如性别字段只有男/女）
3. 频繁更新的字段（索引维护开销大）
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}')

for id in $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

本文系统地梳理了 MySQL CPU 飙升的紧急排查、根因分析与优化全过程。在生产环境中，类似的问题往往需要快速定位与精准解决，希望这份指南能为你的运维工作提供切实帮助。更多深度技术讨论与实战案例，欢迎在 云栈社区 的 数据库/中间件/技术栈 板块与大家交流。