一个SQL引发的CPU飙升问题排查实录

在数据库运维或开发中，SQL语句的性能优化是至关重要的环节。如果SQL语句设计不当，可能会导致CPU资源被过度占用，从而影响整个系统的性能。本文将通过一个实际案例，深入分析由SQL引发的CPU飙升问题，并探讨排查和解决的方法。

一、问题背景

某天凌晨，系统监控告警显示数据库服务器的CPU使用率持续飙升至90%以上，严重影响了业务的正常运行。经过初步排查，发现数据库中的某些查询操作耗时过长，可能是导致CPU高负载的主要原因。

初步信息收集

1. 数据库类型：MySQL 5.7。
2. 表结构：涉及一张名为orders的大表，存储订单数据，包含数百万条记录。
3. SQL语句：以下是一个高频执行的查询语例：

   SELECT o.order_id, o.customer_id, o.total_amount, c.name 
   FROM orders o 
   LEFT JOIN customers c ON o.customer_id = c.id 
   WHERE o.status = 'pending' AND o.created_at > '2023-01-01';
   

该SQL语句用于查询所有状态为“pending”且创建时间晚于指定日期的订单及其对应的客户名称。

二、问题排查步骤

1. 查看慢查询日志

首先，检查MySQL的慢查询日志（Slow Query Log），确认是否存在执行时间过长的SQL语句。通过以下命令启用并查看慢查询日志：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

调整long_query_time参数以捕获更短时间内的慢查询：

SET GLOBAL long_query_time = 1;

从慢查询日志中发现，上述SQL语句的执行时间长达数秒，远超正常范围。

2. 分析执行计划

使用EXPLAIN命令分析SQL语句的执行计划，了解其查询效率：

EXPLAIN SELECT o.order_id, o.customer_id, o.total_amount, c.name 
FROM orders o 
LEFT JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'pending' AND o.created_at > '2023-01-01';

执行结果如下： | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |----|-------------|-------|-------|---------------|------|---------|------|------|-------------| | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 500K | Using where | | 1 | SIMPLE | c | eq_ref| PRIMARY | PRIMARY | 4 | db.o.customer_id | 1 | NULL |

从结果可以看出：

• orders表的查询类型为ALL，即全表扫描，这表明没有合适的索引被使用。
• customers表的查询类型为eq_ref，表示通过主键索引进行高效查找。

3. 检查索引

进一步检查orders表的索引情况：

SHOW INDEX FROM orders;

结果显示，orders表仅存在主键索引，而status和created_at字段未建立任何索引。

4. 模拟高并发场景

为了验证SQL语句在高并发场景下的表现，使用sysbench工具模拟大量并发查询。测试结果显示，在多线程环境下，该SQL语句对CPU资源的消耗显著增加。

三、问题解决方案

针对上述问题，我们采取以下措施优化SQL性能：

1. 添加复合索引

根据查询条件status和created_at，为orders表添加复合索引：

ALTER TABLE orders ADD INDEX idx_status_created_at (status, created_at);

复合索引可以显著减少全表扫描的开销，提升查询效率。

2. 重写SQL语句

优化SQL语句，避免不必要的列查询。例如，如果不需要c.name字段，可以直接省略：

SELECT o.order_id, o.customer_id, o.total_amount 
FROM orders o 
WHERE o.status = 'pending' AND o.created_at > '2023-01-01';

3. 监控与调优

• 定期检查慢查询日志，及时发现潜在性能问题。
• 使用pt-query-digest工具分析查询性能瓶颈。
• 调整MySQL配置参数，如innodb_buffer_pool_size和query_cache_size，以提高缓存命中率。

四、效果验证

实施上述优化措施后，再次运行SQL语句并观察其性能表现：

• 查询时间从原来的数秒缩短至毫秒级别。
• CPU使用率恢复正常水平，稳定在30%左右。

此外，通过压力测试验证，在高并发场景下，系统响应速度明显提升，资源利用率更加合理。

五、总结

本次问题的根本原因是SQL语句缺乏适当的索引支持，导致查询过程中发生全表扫描，从而引发CPU资源的过度消耗。通过添加复合索引和优化SQL语句，成功解决了性能瓶颈。

在日常开发和运维中，建议定期审查数据库的索引设计和查询逻辑，提前发现并解决潜在问题，确保系统的稳定性和高效性。