定位mysql cpu高问题

以下是定位 MySQL 数据库高 CPU 问题的系统性排查步骤和解决方法：

1. 确认 CPU 使用来源

首先确认高 CPU 是否由 MySQL 进程引起：

# 查看系统进程 CPU 占用排名（按 P 键按 CPU 排序）
top -c
# 或使用更直观的工具
htop

• 若 mysqld 进程 CPU 占用率持续超过 80%，需深入排查 MySQL 内部问题。

2. 检查 MySQL 当前活动线程

通过 MySQL 客户端查看正在执行的线程和查询：

-- 显示完整查询语句（避免截断）
SHOW FULL PROCESSLIST;

• 关注以下状态：
  • Sending data：可能涉及全表扫描或复杂 JOIN。
  • Creating sort index：排序操作未走索引。
  • Locked：锁等待阻塞。
  • Copying to tmp table：使用临时表（尤其是磁盘临时表）。
• 示例输出：

3. 分析慢查询

3.1 启用慢查询日志

-- 检查慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志（需有权限）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录执行超过 1 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

3.2 分析慢查询日志

使用 mysqldumpslow 工具或 pt-query-digest（Percona Toolkit）：

# 统计最耗时的慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 使用 pt-query-digest 生成详细报告
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

• 重点关注：
  • 执行时间长的查询。
  • 扫描行数（Rows_examined）远大于返回行数（Rows_sent）的查询。
  • 未使用索引的查询（key: NULL）。

4. 检查索引有效性

4.1 确认查询是否走索引

使用 EXPLAIN 分析可疑查询的执行计划：

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

• 关键字段：
  • type：ALL 表示全表扫描，ref 或 range 表示索引使用。
  • key：实际使用的索引。
  • rows：预估扫描行数。

4.2 检查缺失索引

-- 查看表的索引信息
SHOW INDEX FROM orders;

-- 检查冗余或重复索引（使用工具）
pt-duplicate-key-checker --user=root --password=xxx --database=your_db

5. 临时表和排序操作

5.1 检查临时表使用

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

• 关键指标：
  • Created_tmp_disk_tables：磁盘临时表数量（应尽量减少）。
  • Created_tmp_tables：内存临时表数量。

5.2 优化排序和临时表

• 增大 sort_buffer_size 和 tmp_table_size（避免磁盘临时表）：

  # my.cnf 配置
  tmp_table_size = 256M
  max_heap_table_size = 256M
  sort_buffer_size = 4M
  

6. 锁和事务冲突

6.1 检查锁等待

-- 查看当前锁信息
SHOW ENGINE INNODB STATUS;

-- 查看未提交的长事务
SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

6.2 处理锁争用

• 终止长时间未提交的事务：

  KILL <trx_mysql_thread_id>;
  

• 优化事务逻辑，减少锁持有时间。

7. 配置优化

7.1 检查关键参数

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 缓冲池大小（建议设为物理内存的 70%~80%）
SHOW VARIABLES LIKE 'max_connections';         -- 最大连接数（避免过高）

7.2 调整配置示例

# my.cnf 配置优化示例
innodb_buffer_pool_size = 16G
max_connections = 200
thread_cache_size = 50
query_cache_type = 0          -- 关闭查询缓存（高并发场景可能适得其反）

8. 高级诊断工具

8.1 使用 performance_schema

-- 查看高负载 SQL 的统计信息
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

8.2 使用 sys 库

-- 安装 sys 库（MySQL 5.7+ 默认包含）
USE sys;

-- 查看 Top SQL
SELECT * FROM sys.statement_analysis LIMIT 10;

9. 系统级排查

9.1 I/O 瓶颈

# 检查磁盘 I/O 负载
iostat -x 1

• 关键指标：%util（接近 100% 表示磁盘饱和）。

9.2 内存不足

# 检查内存和 Swap 使用
free -h

• 若 free 内存不足，可能触发频繁磁盘交换（Swap），间接导致 CPU 升高。

总结：问题排查流程图

1. 确认 MySQL 进程 CPU 高          → 是 → 进入步骤 2
   ↓ 否 → 排查其他进程或系统问题
2. 检查 PROCESSLIST                → 发现可疑查询 → 优化 SQL
   ↓ 无
3. 分析慢查询日志                  → 定位慢 SQL → 优化索引/改写 SQL
   ↓ 无
4. 检查临时表和排序操作            → 调整缓冲区大小
   ↓ 无
5. 检查锁和事务                   → 终止长事务/优化事务逻辑
   ↓ 无
6. 检查配置参数                   → 调整缓冲池/连接数
   ↓ 无
7. 使用高级工具（performance_schema/sys）→ 定位隐藏问题

通过以上步骤，可以系统性地定位 MySQL 高 CPU 的根源原因，并针对性地优化 SQL、索引或配置。