跳到主要内容

生产排查

场景题的集中地。大厂面试常用「线上 MySQL 突然变慢，你怎么查」开场，然后顺着你的回答往深挖。

🔥 SQL 突然变慢​

先区分两种情况：

现象性质
一直慢SQL / 索引问题，查 explain
偶尔抖动，大部分时候很快系统层面的瞬时阻塞，explain 看不出来

「偶尔抖一下」的三个典型原因：

1. 刷脏页（flush）​

内存里的脏页太多，必须停下来刷盘。两种触发：

  • redo log 写满 —— 必须推进 checkpoint，此时所有更新全部阻塞，TPS 直接掉到 0。这是最严重的情形
  • Buffer Pool 没有空闲页 —— 要淘汰的页正好是脏页，必须先刷盘才能复用。如果一次要淘汰很多脏页，响应时间明显变长

排查：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';   -- 不应持续增长
SHOW ENGINE INNODB STATUS;    -- 看 BUFFER POOL AND MEMORY 段的 Modified db pages

配置：

  • innodb_io_capacity 必须配成磁盘真实的 IOPS（用 fio 测）。配低了 InnoDB 以为磁盘很慢，刷盘不积极，脏页堆积，最后集中爆发
  • innodb_max_dirty_pages_pct（默认 75%）控制脏页比例上限
  • innodb_flush_neighbors —— 刷一个脏页时顺带刷相邻的脏页。机械盘设 1（减少随机 IO），SSD 设 0（8.0 已默认 0）

2. 锁等待​

被别的事务持有的行锁 / 间隙锁 / MDL 阻塞。排查见下一节。

3. 大查询占满资源​

一条全表扫描或大 join 把 Buffer Pool 污染、把 IO 和 CPU 打满，影响所有其他查询。见排序、分组与 Join 原理里 BNL 污染 Buffer Pool 的机制。

🔥 锁等待怎么定位​

-- MySQL 8.0：最直接，一眼看出谁阻塞了谁
SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 通用：当前所有事务，按开始时间排序找长事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 看当前连接在干什么
SHOW FULL PROCESSLIST;
SELECT * FROM performance_schema.threads;

-- 死锁现场
SHOW ENGINE INNODB STATUS;    -- LATEST DETECTED DEADLOCK 段

MDL 锁等待（改表被阻塞）需要单独查：

SELECT * FROM performance_schema.metadata_locks;
-- 需要先开启：
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES' WHERE NAME='wait/lock/metadata/sql/mdl';

PROCESSLIST 的 State 列怎么读：

State含义
Waiting for table metadata lockMDL 锁等待，通常是有长事务或正在改表
Waiting for table level lock表锁等待
Sending data⚠️ 容易误解 —— 不是「在发数据」，而是「正在执行」（扫描 + 处理），是最常见的慢查询状态
Copying to tmp table用了临时表
Sorting result在排序
statistics优化器在收集统计信息决定执行计划

:::warning 版本差异会让人踩空 information_schema.innodb_locks 和 innodb_lock_waits 在 MySQL 8.0 被移除，换成了 performance_schema.data_locks / data_lock_waits。5.7 的文章和 8.0 的实操对不上，这是常见困惑点。 :::

🔥 为什么 kill 不掉​

KILL QUERY <thread_id> 或 KILL <thread_id> 执行了，但线程还在跑。

原理：kill 不是立即终止，而是：

  1. 把该线程的状态改成 THD::KILL_QUERY
  2. 给该线程发一个信号，让它从等待中醒来
  3. 线程自己在执行到「埋点」时检查这个状态，然后主动退出并做清理（回滚事务、释放锁）

所以 kill 的本质是「通知线程该退出了」，真正的退出要线程自己配合。

三种 kill 不掉的情形：

情形原因
线程没走到埋点比如陷在一个很长的 IO 等待里，或者在执行一个没有埋点的紧密循环
innodb_thread_concurrency 用满线程在「等待进入 InnoDB 执行」的队列里，这个等待循环每 10ms 才检查一次 kill 标志，且进不去就无法推进
大事务回滚kill 之后开始回滚，一个删了千万行的事务回滚可能要很久。此时在 PROCESSLIST 里状态是 Rolling back，只能等，强行重启 MySQL 后它还会继续回滚

:::tip 一个常见误解 很多人以为客户端按 Ctrl+C 就立刻停止了语句执行。实际上客户端做的是：另开一个连接，发一条 KILL QUERY。所以 Ctrl+C 和手动 kill 完全一样，同样受上述限制。

另一个相关误解：客户端断开连接 ≠ 服务端停止执行。连接断了但语句还在跑的情况很常见（结果集写回时才发现对端没了）。 :::

🔥 优化器选错索引​

现象：一条平时很快的 SQL 突然变慢，explain 发现它走了另一个索引（或全表扫描）。

根因：基数（cardinality）估算不准。

MySQL 用采样统计估算索引的区分度：选 N 个数据页（innodb_stats_persistent_sample_pages，默认 20），统计这些页上的不同值，再乘以页数得到整个索引的基数。

采样必然有误差，而且删除大量数据后统计信息可能严重失真（经典案例：反复 DELETE + INSERT 的表，统计信息会偏离实际）。

另外优化器的成本模型还要考虑回表代价 —— 走二级索引要回表，优化器估算的扫描行数如果偏大，就可能认为「全表扫描更便宜」。

四种解法（按优先级）：

-- 1. 重新采样统计信息（最轻量，优先试这个）
ANALYZE TABLE t;

-- 2. 强制指定索引（有效但不优雅：索引改名或删除后 SQL 会报错）
SELECT * FROM t FORCE INDEX(idx_a) WHERE ...;

-- 3. 改写 SQL 引导优化器
-- 例：ORDER BY b LIMIT 1 改成 ORDER BY b, a LIMIT 1
-- 让优化器认为「反正都要排序，不如选扫描行数少的那个索引」

-- 4. 新建一个更合适的索引，或删掉那个误导优化器的索引

提高采样精度：

SET GLOBAL innodb_stats_persistent_sample_pages = 64;   -- 默认 20
-- 代价是 ANALYZE 更慢

SHOW INDEX FROM t 的 Cardinality 列就是采样估算值，和 SELECT COUNT(DISTINCT col) 对比能看出偏差多大。

MySQL 8.0 的直方图（Histogram） 补上了一块短板 —— 它能为没有索引的列提供数据分布信息，让优化器更好地估算过滤比例：

ANALYZE TABLE t UPDATE HISTOGRAM ON col1, col2 WITH 32 BUCKETS;

连接数满了​

ERROR 1040 (HY000): Too many connections

此时新连接全部被拒绝，包括运维要用的连接（除了 SUPER 权限预留的那一个）。

处理顺序：

  1. 用预留的 super 权限连接进去，SHOW PROCESSLIST 找 Command = Sleep 的空闲连接
  2. 优先 kill 处于事务外的 sleep 连接 —— information_schema.innodb_trx 里没有对应 trx_mysql_thread_id 的就是事务外的，kill 它们无损
  3. 事务内的 sleep 连接（trx_state = RUNNING 但 trx_query = NULL）不能随便 kill —— 会回滚，可能导致业务数据不一致
  4. 实在进不去，用 --skip-grant-tables 重启（高危，跳过所有权限验证，必须同时关闭外部网络访问）

根因往往在应用侧：连接池配置过大、慢查询堆积导致连接积压、未正确释放连接（连接泄漏）。

长连接的内存问题：MySQL 执行过程中临时使用的内存是挂在连接对象上的，只在连接断开时释放。全是长连接时内存会持续涨，最终可能被 OOM kill（表现为 MySQL 异常重启）。两种应对：

  • 定期断开重连（或连接池设置 maxLifetime）
  • 5.7+ 用 mysql_reset_connection —— 重新初始化连接资源但不重连、不重新认证，保留连接但释放内存

全表扫描会把内存打满吗​

高频问题，答案是不会，而且理由很能体现对架构的理解。

server 层不会：MySQL 是「边读边发」的流式处理：

取一行 → 写进 net_buffer（大小 net_buffer_length，默认 16KB）
  → 满了就发给客户端 socket
  → 发送成功后清空 net_buffer，继续取下一行
  → 如果 socket 发送缓冲区满了（客户端读得慢），就停下来等

所以内存里最多只有 net_buffer 那么多数据，不会把 200GB 的表读进内存。

此时 PROCESSLIST 里的状态是 Sending to client —— 表示「在等客户端接收」。看到这个状态要怀疑客户端处理太慢（比如用了非游标模式一次性接收全部结果）。

:::tip MySQL 客户端的两种模式

  • 本地缓存模式（默认）—— mysql_store_result，一次性把全部结果读到客户端内存。大结果集会打爆客户端
  • 游标模式 —— mysql_use_result，一行行读。mysql -h... --quick 用的是这个

所以「全表扫描打爆的是客户端，不是服务端」。 :::

InnoDB 层会有影响，但有机制缓解 —— 全表扫描会把大量数据页读进 Buffer Pool。靠的就是架构与存储引擎里讲的 young/old 区 + innodb_old_blocks_time 机制：全表扫描的页「读入后立刻访问、然后不再使用」，停留时间小于 1 秒，永远进不了 young 区，所以不会把热点数据挤出去。

慢查询定位流程​

-- 1. 确认慢日志开启
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;   -- 记录未用索引的查询

-- 2. 分析（按总耗时排序，不是单次耗时）
-- mysqldumpslow -s t -t 20 slow.log
-- pt-query-digest slow.log

-- 3. 实时看 top SQL（8.0）
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec,
       AVG_TIMER_WAIT/1e9 AS avg_ms, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- 4. 单条分析
EXPLAIN ANALYZE SELECT ...;   -- 8.0.18+，给出真实执行耗时和行数

按总耗时排序是关键 —— 执行 10 万次的 50ms 查询（总 5000 秒）比执行一次的 10 秒查询重要得多。

排查的通用框架​

被问「线上数据库变慢怎么查」，按这个顺序答：

  1. 确认范围 —— 是单条 SQL 慢还是整个实例慢？是突然的还是渐变的？和发版 / 流量高峰 / 定时任务的时间点对得上吗？
  2. 看系统指标 —— CPU / IO / 内存 / 网络，top、iostat -x 1、vmstat。IO 打满和 CPU 打满指向完全不同的方向
  3. 看数据库状态 —— SHOW PROCESSLIST 找长查询和锁等待，innodb_trx 找长事务，QPS / TPS / 连接数 / Buffer Pool 命中率
  4. 定位到具体 SQL —— 慢日志 + events_statements_summary_by_digest
  5. 分析执行计划 —— EXPLAIN ANALYZE，看 type / rows / Extra
  6. 给出结论和修复 —— 加索引 / 改 SQL / 调参数 / 限流 / 扩容，并说明各自的代价和生效时间

最后一步很重要：面试官想听的不只是「怎么查到」，还有「查到之后怎么快速止血、再怎么根治」。止血手段（kill 大查询、限流、临时加索引、切读流量）和根治手段（改 SQL、改表结构、改架构）要分开说。

最后于 更新