本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
性能优化（执行计划 / 慢查询 / 常见陷阱） · 经典性能问题

数据库 CPU 100%，怎么定位？(SHOW PROCESSLIST + EXPLAIN + perf)

How to diagnose 100% database CPU usage? (SHOW PROCESSLIST + EXPLAIN

答案语言

考察要点

这道题面试官想看你是否具备一套体系化、由表及里、从应用层到系统层的数据库性能诊断方法论。考察点包括：

  1. 实时诊断能力：能否快速定位当前系统中消耗 CPU 最严重的 SQL 线程。
  2. 深度分析能力：能否利用 EXPLAIN 精准分析 SQL 执行计划，找出性能瓶颈。
  3. 底层排查能力：当 SQL 层面无法解释时，是否懂得使用 perf 等操作系统工具，从 mysqld 进程的函数调用层面定位 CPU 消耗的根源。

核心概念

定位 CPU 100% 问题，本质上是找到是哪个或哪些线程（thread_id）在消耗 CPU，这些线程在执行什么 SQL，以及这条 SQL 为何如此消耗 CPU。

1. SHOW [FULL] PROCESSLIST

这是实时诊断的第一步，用于查看当前 MySQL 服务器正在执行的线程。

  • 核心字段解读: | 字段 | 描述 | 关键排查点 | | :--- | :--- | :--- | | Id | 连接/线程的唯一标识符。 | 用于 KILL 掉问题线程，或关联其他 performance_schema 表。 | | User | 发起连接的用户名。 | 定位请求来源。 | | Host | 发起连接的客户端主机名和端口。 | 定位请求来源。 | | db | 当前连接的数据库。 | - | | Command | 线程正在执行的命令类型。 | Query 表示正在执行 SQL。 | | Time | 线程处于当前状态的秒数。 | 核心指标。Time 值非常大的查询通常是重点怀疑对象。 | | State | 线程状态。 | 核心指标。Sending data, Copying to tmp table, Sorting result, executing 等状态持续时间长，都意味着高 CPU 消耗。 | | Info | 线程正在执行的 SQL 语句。 | 定位问题 SQL 的直接来源。默认截断，使用 SHOW FULL PROCESSLIST 查看完整 SQL。 |

  • 常见高 CPU 消耗的 State：

    • Copying to tmp table [on disk]: GROUP BY, UNION, 或子查询等操作无法使用索引，需要创建临时表。如果内存不足，会创建磁盘临时表，导致大量 I/O 和 CPU 消耗。
    • Sorting result: 对结果集进行排序，无法利用索引的有序性，需要进行文件排序（filesort），非常消耗 CPU。
    • Sending data: 正在从结果集中读取数据并发送给客户端。如果涉及大量数据扫描，即使有索引，也可能消耗大量 CPU 进行数据处理和网络传输。
    • executing: 正在执行查询。对于复杂计算、函数调用、正则表达式匹配等，此状态会持续很长时间。

2. EXPLAIN

这是 SQL 性能分析的基石，用于分析 MySQL 如何执行一条 SQL 语句。

  • 核心字段解读: | 字段 | 描述 | 关键排查点 | | :--- | :--- | :--- | | type | 访问类型，表示查询的效率。 | 性能从好到坏：system > const > eq_ref > ref > range > index > ALL。出现 ALL (全表扫描) 或 index (全索引扫描) 需高度警惕。 | | key | 实际使用的索引。 | 如果为 NULL，表示没有使用索引。 | | rows | 预估扫描的行数。 | 数值越大，潜在的 CPU 和 I/O 消耗越高。 | | Extra | 额外信息，包含性能瓶颈的关键线索。 | Using filesort: 文件排序，CPU 杀手。<br>Using temporary: 使用临时表，CPU 和 I/O 杀手。<br>Using where: 通过 WHERE 条件过滤，但如果伴随 ALL 或 index 类型，意味着在存储引擎层拉取了大量数据到 Server 层进行过滤，消耗 CPU。 |

3. perf

当 EXPLAIN 无法完全解释 CPU 消耗时（例如，查询计划看似合理，但 CPU 依然高企），perf 作为 Linux 系统级的性能分析工具，能直达 mysqld 进程内部，分析是哪个函数在消耗 CPU。

  • 工作原理：perf 通过采样 mysqld 进程的函数调用栈，统计出各个函数的 CPU 使用时间占比。
  • 常用命令：
    • perf top -p <mysqld_pid>: 实时显示 mysqld 进程中 CPU 占用最高的函数。
    • perf record -F 99 -p <mysqld_pid> --call-graph dwarf -g -- sleep 10: 在 10 秒内以 99Hz 的频率采样，并记录调用栈。
    • perf report: 分析 perf.data 文件，生成交互式的分析报告。

通过 perf，我们可以将 CPU 消耗定位到具体的内部函数，如 JOIN::exec, filesort, handler::ha_rnd_next (全表扫描)，甚至是某个 UDF (用户自定义函数) 的执行体。

定位方法与步骤

这是一个标准的、三步走的诊断流程：

第 1 步：实时定位问题 SQL (SHOW PROCESSLIST)

  1. 登录数据库，立即执行 SHOW FULL PROCESSLIST;。
  2. 重点关注 Time 字段值大、且 Command 为 Query 的线程。
  3. 观察其 State 字段，如果是 Sorting result, Copying to tmp table 等，基本可以锁定为问题线程。
  4. 记录下该线程的 Id 和 Info (完整的 SQL 语句)。
sql
 1-- 示例：发现一个执行了 300 秒的查询
 2mysql> SHOW FULL PROCESSLIST;
 3+-----+------+-----------------+------+---------+------+--------------------+------------------------------------------------------------------+
 4| Id  | User | Host            | db   | Command | Time | State              | Info                                                             |
 5+-----+------+-----------------+------+---------+------+--------------------+------------------------------------------------------------------+
 6| 123 | app  | 10.0.1.10:12345 | my_db| Query   | 300  | Sorting result     | SELECT * FROM orders ORDER BY amount DESC LIMIT 10;              |
 7+-----+------+-----------------+------+---------+------+--------------------+------------------------------------------------------------------+

第 2 步：分析执行计划 (EXPLAIN)

拿到上一步的问题 SQL，使用 EXPLAIN 进行分析。

sql
 1EXPLAIN SELECT * FROM orders ORDER BY amount DESC LIMIT 10;

  • 分析场景 1：全表扫描 + 文件排序

    text
     1+--+-----------+------+----+---------------+------+---------+------+---------+-------------+
     2|id|select_type|table |type|possible_keys|key   |key_len|ref   |rows      |Extra        |
     3+--+-----------+------+----+---------------+------+---------+------+---------+-------------+
     4|1 |SIMPLE     |orders|ALL |NULL           |NULL  |NULL   |NULL  |10000000  |Using filesort|
     5+--+-----------+------+----+---------------+------+---------+------+---------+-------------+

    诊断结论：type 是 ALL (全表扫描)，Extra 中有 Using filesort。MySQL 需要扫描一千万行数据，然后在内存或磁盘上进行排序，这是典型的 CPU 消耗大户。 优化方案：在 amount 列上创建索引 ALTER TABLE orders ADD INDEX idx_amount(amount);。

  • 分析场景 2：未使用最优索引 假设 orders 表有 (user_id) 和 (create_time) 两个单列索引。

    sql
     1EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time;

    如果优化器选择了 idx_user_id 索引，它仍然需要对 user_id = 123 的所有结果进行文件排序。 优化方案：创建联合索引 ALTER TABLE orders ADD INDEX idx_user_create(user_id, create_time);，这样可以利用索引的有序性，避免文件排序。

第 3 步：底层根源深潜 (perf)

如果 EXPLAIN 计划看起来很理想（例如 type 是 range，rows 也不大），但 CPU 依然居高不下，就需要 perf 上场。

  1. 在服务器上找到 mysqld 的进程 ID: pidof mysqld。
  2. 使用 perf top 实时观察：
    bash
     1# perf top -p $(pidof mysqld)
    观察输出，如果看到类似 Item_func_str_to_date::val_str 或某个 UDF 函数名排在前面，说明 CPU 消耗在数据类型转换或自定义函数上。
  3. 如果问题是瞬时的，使用 perf record 记录一段时间的调用栈，然后用 perf report 分析。
    bash
     1# 生成火焰图，更直观
     2# perf record -F 99 -p $(pidof mysqld) -g -- sleep 30
     3# perf script | ./FlameGraph/stackcollapse-perf.pl | ./FlameGraph/flamegraph.pl > mysql-cpu.svg
    火焰图（Flame Graph）会清晰地展示出 CPU 时间主要消耗在哪个函数调用链上。
    text
     1(ASCII Flame Graph 示意图)
     2             +-------------------------------------------------+
     3mysqld (100%)|                     execute_sql_command         |
     4             +-------------------------------------------------+
     5             |                mysql_execute_command            |
     6             +-------------------------------------------------+
     7             |                     dispatch_command            |
     8             +-------------------------------------------------+
     9             |                        do_query                 |
    10             +-------------------------------------------------+
    11             |                          JOIN::exec             |
    12             +-------------------------------------------------+
    13             |      sub_select       |       filesort          |  <-- 瓶颈在这里
    14             +-----------------------+-------------------------+
    诊断结论：通过火焰图，即使 EXPLAIN 看不出问题，也能发现大量的 CPU 时间消耗在了 filesort 函数上，从而证实了排序是瓶颈。

常见低分回答

  • 只说 top 和 SHOW PROCESSLIST：这是最表层的回答，没有展现出分析和解决问题的深度。只知道现象，不知道如何探究根源。
  • 看到慢查询就 KILL：不经分析直接 KILL 查询是一种粗暴且危险的操作，可能导致业务中断或数据不一致，治标不治本。
  • 误解 EXPLAIN 的 Extra：例如，看到 Using where 就认为有问题，实际上 Using where 很常见。关键是它和什么一起出现（如 type: ALL）。或者看到 Using index 就认为万事大吉，但 Using index 配合 type: index 可能是全索引扫描，性能也未必好。
  • 分不清 CPU 密集型和 I/O 密集型：当被问到 CPU 100% 时，却大谈特谈磁盘 I/O、缺页中断等，说明对问题的定性不清。虽然两者可能相关，但出发点应聚焦于 CPU。
  • 对 perf 等系统工具一无所知：这表明候选人的知识范围仅限于 MySQL 应用层，缺乏底层系统调试能力，对于解决疑难杂症能力有限。

工程实践与优化

  • 主动监控优于被动排查：在生产环境中，应部署完善的监控系统（如 Prometheus + Grafana，或 Percona PMM），对 MySQL 的 CPU 使用率、活跃线程数、慢查询数、QPS/TPS 等关键指标设置告警阈值，做到提前发现、主动优化。
  • 开启并善用慢查询日志：配置 slow_query_log = ON, long_query_time = 1 (或更小)，并设置 log_queries_not_using_indexes = ON。定期使用 pt-query-digest 或类似工具分析慢查询日志，形成周报，持续优化。
  • 拥抱 Performance Schema：MySQL 5.7+ 的 Performance Schema 提供了比 SHOW PROCESSLIST 更丰富、更细粒度的性能数据。例如，events_statements_summary_by_digest 表可以聚合统计各类 SQL 的执行次数、总耗时、锁等待、临时表使用等情况，是定位高频次、累积耗时长的“隐形杀手”SQL 的利器。
  • 使用 EXPLAIN ANALYZE：MySQL 8.0.18+ 引入了 EXPLAIN ANALYZE，它会真实地执行查询并返回带有实际执行时间和行数的执行计划。这比 EXPLAIN 的估算值要准确得多，尤其是在复杂查询中，能提供更精确的诊断信息。
  • 避免在数据库中进行复杂计算：尽量将复杂的字符串处理、大量的计算逻辑放在应用层完成，让数据库专注于其核心优势——数据存储和检索。

常见追问与延伸题目

  1. 追问：如果 CPU 不高，但数据库响应很慢，可能是什么原因？怎么排查？

    • 回答要点：这通常是 I/O 瓶颈或锁等待。
      • I/O 瓶颈：使用 iostat -x 1 或 iotop 查看磁盘的 %util、await 是否过高。在 MySQL 内部，检查 Innodb_buffer_pool_wait_free、Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads 的比率，如果后者占比高，说明 Buffer Pool 命中率低，导致大量物理 I/O。
      • 锁等待：SHOW PROCESSLIST 中会看到大量线程处于 locked 状态。通过查询 information_schema.INNODB_TRX, information_schema.INNODB_LOCKS, information_schema.INNODB_LOCK_WAITS 这三张表，可以定位到谁在持有锁（阻塞者）以及谁在等待锁（被阻塞者），从而分析死锁或长事务。

  2. 追问：你刚才提到的文件排序（filesort），能详细讲讲它的原理和优化方法吗？

    • 回答要点：
      • 原理：当 ORDER BY 的字段没有索引，或无法利用现有索引的有序性时，MySQL 必须将查询结果加载到内存（sort_buffer_size）中进行排序。如果数据量太大，内存放不下，就需要利用磁盘临时文件进行分块、排序、合并（归并排序），这个过程称为文件排序。
      • 两种算法：
        • 单路排序：一次性读取所有需要的列到 sort_buffer，排序后直接返回。优点是快，缺点是消耗内存。
        • 双路排序（回表排序）：先只读取排序列和主键到 sort_buffer，排序后，再根据排好序的主键逐行回表查询其他列。优点是节省 sort_buffer 空间，缺点是需要两次 I/O，更慢。MySQL 根据 max_length_for_sort_data 参数决定使用哪种。
      • 优化方法：核心是避免排序或让排序更快。
        1. 创建索引：为排序列和 WHERE 条件列创建合适的联合索引，利用索引的天然有序性，EXPLAIN 中 Extra 不会出现 Using filesort。
        2. 增大 sort_buffer_size：如果无法避免排序，适当增大该参数，让更多排序在内存中完成，减少磁盘 I/O。但这会增加每个连接的内存消耗。
        3. 减少 SELECT 的列：只查询必要的列，特别是避免 SELECT *，可以减少排序时需要处理的数据量，更容易触发单路排序。

  3. 追问：如果数据库是主从架构，CPU 100% 发生在主库还是从库，排查思路上有什么不同？

    • 回答要点：
      • 主库 CPU 100%：排查思路如上所述，主要由业务的写入和查询请求导致。定位到问题 SQL 后，优化上线即可。
      • 从库 CPU 100%：原因更复杂。
        1. SQL 线程应用 relay log 慢：可能是主库上执行很快的并发 DML，在从库上因为单线程（MySQL 5.6 之前）或多线程并行度不够（5.7+ 基于 DATABASE 或 LOGICAL_CLOCK 的并行复制）而堆积，导致延迟和 CPU 升高。需要检查 Seconds_Behind_Master，并考虑升级到 GTID + writeset 并行复制（MySQL 8.0）。
        2. 从库上有额外的查询：如果从库承担了读请求（读写分离），那么排查思路和主库类似，需要分析从库上的慢查询。
        3. 主从表结构不一致：例如，主库的表有索引，而从库的表忘记加了，导致同一条 UPDATE 或 DELETE 在主库上是 range 更新，在从库上变成全表扫描，从而拖慢从库并打高 CPU。需要用 pt-table-checksum 等工具检查主从一致性。
← Previous
突然出现大量慢 SQL，从哪里开始排查？(系统层 → MySQL 层 → SQL 层)
Next →
数据库连接数打满，怎么办？应急 + 根因分析？