诊断步骤
- 找到占用 CPU 最多的 MySQL 线程
-- 找出 MySQL 进程 PID pidof mysqld -- 查看线程 CPU top -H -p <pid>
- 将系统线程 ID 映射到 MySQL 内部线程
SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = <os_tid>\G
- 找出当前正在执行的高耗 CPU 语句
SELECT thread_id, processlist_id, processlist_state, processlist_info FROM performance_schema.threads WHERE processlist_command != 'Sleep' ORDER BY processlist_time DESC LIMIT 20;
- 检查是否有全表扫描 / 无索引 SQL，进行索引优化（见故障5）
- 检查
innodb_buffer_pool_hit_rate，低于 99% 说明缓冲池不够SHOW STATUS LIKE 'Innodb_buffer_pool_read%';