一、监控告警与初步诊断

当监控系统触发CPU告警时，需立即启动三级响应机制：

基础指标确认：通过top -Hp <mysql_pid>查看具体线程CPU占用，结合SHOW PROCESSLIST识别活跃会话。特别注意State字段为Sending data、Copying to tmp table等高消耗状态。
性能视图分析：执行SHOW ENGINE INNODB STATUS获取锁等待信息，重点关注TRANSACTIONS和LATEST DETECTED DEADLOCK章节。使用performance_schema中的events_statements_summary_by_digest统计SQL执行频率。
Java应用关联：通过JMX监控获取连接池状态（如HikariCP的activeConnections），检查是否有连接泄漏导致线程堆积。使用Arthas的thread -n 3命令查看Java进程内CPU占用最高的线程堆栈。

二、慢查询深度排查

1. 慢查询日志分析

-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 设置阈值
SET GLOBAL log_queries_not_using_indexes = 'ON';

通过mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log提取TOP10慢查询，重点关注：

全表扫描（type: ALL）
临时表创建（Using temporary）
文件排序（Using filesort）
索引失效场景（如函数操作字段WHERE DATE(create_time) = ...）

2. EXPLAIN进阶分析

对可疑SQL执行EXPLAIN FORMAT=JSON获取更详细执行计划，重点关注：

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "orders",
      "access_type": "ALL",  // 全表扫描警告
      "rows": 1200000,       // 预估扫描行数
      "filtered": 10.00      // 条件过滤率
    }
  }
}

特别关注rows * filtered值，该值过大表明需要优化索引或SQL写法。

3. 索引优化策略

复合索引设计：遵循最左前缀原则，将高频查询条件放在索引左侧
覆盖索引：通过SELECT id,name FROM table避免回表操作
索引下推：MySQL 5.6+支持的ICP特性可减少上层服务处理的数据量
索引选择性：使用SHOW INDEX FROM table查看Cardinality值，选择性低的字段（如状态码）不适合单独建索引

三、锁竞争与并发控制

1. 锁类型识别

-- 查看当前锁等待
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%lock%';
-- 查看事务锁信息
SELECT * FROM sys.innodb_lock_waits;

重点关注：

行锁等待：通常由未提交事务或索引缺失导致
元数据锁（MDL）：DDL操作与DML冲突时的典型表现
全局读锁：FLUSH TABLES WITH READ LOCK等操作导致

2. 死锁处理

通过SHOW ENGINE INNODB STATUS获取死锁日志，分析事务回滚链。Java应用中需注意：

事务粒度控制：避免在循环中开启事务
锁顺序一致性：多个资源操作需按固定顺序获取锁
乐观锁替代：对高频更新场景使用version字段实现CAS操作

四、资源泄漏与配置问题

1. 连接泄漏检测

// HikariCP配置示例
@Bean
public DataSource dataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:mysql://...");
    config.setMaximumPoolSize(20);
    config.setConnectionTimeout(30000);
    config.setLeakDetectionThreshold(5000); // 连接泄漏检测阈值
    return new HikariDataSource(config);
}

当连接超过阈值未归还时，日志会输出调用栈信息，帮助定位泄漏代码。

2. 配置参数调优

关键参数调整建议：
| 参数 | 推荐值 | 影响 |
|———|————|———|
| innodb_buffer_pool_size | 物理内存的50-70% | 缓存表数据和索引 |
| innodb_io_capacity | 200-1000 | 控制后台刷新脏页速度 |
| tmp_table_size | 64M-256M | 内存临时表大小阈值 |
| query_cache_size | 0（MySQL 8.0已移除） | 查询缓存易引发锁竞争 |

五、Java应用层优化

1. ORM框架调优

批量操作：使用JPA的@Modifying配合@Query实现批量更新
二级缓存：对读多写少场景启用Hibernate二级缓存
延迟加载：合理设置fetch策略避免N+1查询问题

2. 异步化改造

对耗时操作采用消息队列解耦：

@RestController
public class OrderController {
    @Autowired
    private RocketMQTemplate rocketMQTemplate;
    @PostMapping("/orders")
    public ResponseEntity<?> createOrder(@RequestBody Order order) {
        // 同步保存核心数据
        orderRepository.save(order);
        // 异步处理非核心逻辑
        rocketMQTemplate.convertAndSend("ORDER_EVENT", order);
        return ResponseEntity.ok().build();
    }
}

六、高级诊断工具链

Percona Toolkit：pt-query-digest分析慢查询日志
Prometheus + Grafana：构建可视化监控面板
SysBench：压力测试定位性能瓶颈
pt-mysql-summary：生成数据库健康检查报告

七、预防性措施

混沌工程：定期模拟CPU满载场景测试系统容错能力
容量规划：基于历史监控数据建立预测模型
自动化巡检：通过脚本定期检查锁等待、慢查询等指标
金丝雀发布：新版本上线时逐步增加流量观察性能变化

当完成上述排查后，需通过vmstat 1、iostat -x 1等命令验证系统整体负载情况，确保CPU占用下降且无其他资源瓶颈。对于复杂场景，建议结合分布式追踪系统（如SkyWalking）进行全链路分析，定位性能问题的最终根源。

MySQL生产环境CPU飙升至100%：从现象到根因的全链路排查指南【Java场景实战】