MySQL 排错调优速查

[!abstract] 定位 面试高频考点，覆盖慢查询定位、紧急止血、索引优化、主从延迟排查。

---

一、CPU 飙高的排查流程

场景：MySQL CPU 95%，用户页面超时

graph TB
    A[CPU 飙高] --> B[SHOW FULL PROCESSLIST]
    B --> C{大量 Sending data?}
    C -->|是| D[找出慢 SQL]
    C -->|否| E{大量 Locked?}
    E -->|是| F[锁等待问题]
    E -->|否| G{大量 Creating tmp table?}
    G -->|是| H[内存不足 / 排序溢出]
    G -->|否| I{查看连接数}
    I --> J{连接数爆炸?}
    J -->|是| K[连接池泄漏 / 慢查询堆积]

    D --> L[EXPLAIN 分析]

1. 确认当前状态

-- 查看正在执行的查询
SHOW FULL PROCESSLIST;

-- 关注这几个 State：
-- Sending data    → 正在扫数据（慢查询高发区）
-- Sorting result  → 排序中，可能缺索引或内存不足
-- Locked          → 等待锁释放
-- Creating tmp table → 临时表溢出到磁盘

-- 查连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

2. 找出慢 SQL

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查慢查询日志路径，然后用 pt-query-digest 分析
-- 或者直接查 performance_schema（MySQL 5.6+）
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

---

二、EXPLAIN 精读

[!important] EXPLAIN 是 MySQL 调优的核心技能

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY created_at DESC;

字段	关键值	含义	优化方向
type	ALL	全表扫描 ❌	必须加索引
	index	全索引扫描 ❌	不如 ALL，尽量优化
	range	索引范围扫描 ⚠️	可接受
	ref	非唯一索引查找 ✅	理想状态
	eq_ref	唯一索引查找 ✅	JOIN 时的最优
	const	主键/唯一索引命中 ✅	最优
rows	数字	预估扫描行数	越大越危险，>10万 开始警惕
key	索引名	实际使用的索引	NULL = 没走索引
Extra	Using filesort	额外排序 ❌	优化 ORDER BY 的索引
	Using temporary	临时表 ❌	优化 GROUP BY / DISTINCT
	Using index	覆盖索引 ✅	不需要回表，最优

[!tip] 面试金句 「我判断一条 SQL 好坏，先看 type 至少要在 range 以上，再看 rows 是否小于 1 万，最后检查 Extra 有没有 filesort 或 temporary。」

---

三、紧急止血三板斧

[!warning] 原则：止血快，治本慢。先恢复服务，再优化 SQL。

板斧 1：限流（kill 堆积查询）

-- 先查堆积最多的 SQL 指纹
SELECT LEFT(info, 100) as query, COUNT(*) as cnt
FROM information_schema.processlist
WHERE command != 'Sleep'
GROUP BY 1 ORDER BY cnt DESC;

-- kill 掉长时间运行的慢查询
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist
WHERE time > 60 AND command != 'Sleep';

板斧 2：降低并发（调整连接池）

# 应用层：缩小连接池
# 如果连接数 max_connections=500，但 threads_running=100+ 已经卡死
# 立即在应用配置中将连接池缩小为 20，减少竞争

# DB 层：临时降低超时
SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

板斧 3：强制走索引（Hail Mary）

-- 如果 EXPLAIN 显示能走某索引但优化器没选（不太常见）
SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 12345;

-- 或者用 HINT 禁止全表扫描（危险，仅临时用）
SET SESSION sql_safe_updates = 1;  -- 禁止不带 WHERE 的 UPDATE/DELETE

[!warning] 能不能直接加索引？

• 短索引（几十万行以内）：可以加，MySQL 5.6+ 支持 Online DDL，不会锁表，但会消耗 IO 和 CPU
• 大表（千万级以上）：不要线上直接加！可能会锁表十几分钟。用 pt-online-schema-change 或 gh-ost 在线变更
• 凌晨低峰期：在从库先加，确认没问题再切主

---

四、常见故障场景速查

场景 1：主从延迟

-- 查延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master：延迟秒数
-- Slave_IO_Running / Slave_SQL_Running：都为 Yes 才正常

原因	排查	解决
主库大事务	SHOW BINLOG EVENTS 看 event 大小	拆分大事务
从库性能差	top / iostat 看 IO	升级规格 / 并行复制
从库单线程回放	MySQL 5.6-	升级 5.7+ 开启并行复制 slave_parallel_workers=4

场景 2：锁等待

-- 查看当前锁等待
SELECT * FROM information_schema.innodb_trx;          -- 活跃事务
SELECT * FROM information_schema.innodb_locks;         -- 锁信息（8.0 用 performance_schema.data_locks）
SELECT * FROM information_schema.innodb_lock_waits;    -- 锁等待关系

场景 3：死锁

-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LATEST DETECTED DEADLOCK" 段

---

五、面试常用参数

参数	推荐值	说明
innodb_buffer_pool_size	物理内存的 70-80%	最关键的参数，缓存数据和索引
innodb_flush_log_at_trx_commit	2（性能）/ 1（安全）	2：每秒刷盘，丢 1s 数据
sync_binlog	1	每次事务刷 binlog，配合主从
max_connections	500-1000	别设太大，消耗内存
slow_query_log	ON	必须开，long_query_time=1
innodb_file_per_table	ON	每个表独立表空间

---

六、一键诊断脚本

#!/bin/bash
# mysql-health.sh

mysql -e "
SELECT '=== 连接数 ===' AS '';
SHOW STATUS LIKE 'Threads_%';
SHOW VARIABLES LIKE 'max_connections';

SELECT '=== 当前查询 ===' AS '';
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5;

SELECT '=== 慢查询 Top 10 ===' AS '';
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

SELECT '=== 主从状态 ===' AS '';
SHOW SLAVE STATUS\G
"

#sre #mysql #面试