### [MySQL 慢查询排查优化全记录:从 CPU 99% 到 0.05 秒](https://www.jiyueip.com/article/13867) **Published:** 2026-08-06T13:29:16 **Author:** 斑斓 **Excerpt:** 线上 MySQL CPU 飙到 99%,一条 JOIN 查询跑 40 秒。从 PROCESSLIST 定位、慢… 上周三晚上接到报警,网站打开特别慢,有的页面直接超时。登上去一看,CPU 飙到 99%,全是 mysqld 在吃。查了下慢查询日志,一条 JOIN 查询跑了 40 多秒,之前数据量小的时候没感觉,表里积了百万级数据之后直接爆了。 花了一天时间排查优化,把问题解决了,整个过程记录一下,希望能帮到遇到类似问题的朋友。 ## 发现问题 先看服务器状态,我用的雨云机器,控制台监控能看到 CPU 和内存曲线,周三晚上 9 点多 CPU 突然拉满。SSH 连上去 `top` 一看,mysqld 占了 90% 多的 CPU。 看一下当前的查询: ``` SHOW PROCESSLIST; ``` 好几条查询的状态都是 Sending data,卡在那不动。 Sending data 不一定是真在发送数据,很多时候是在做磁盘读取或者临时表排序,反正就是慢。 ## 开慢查询日志 如果之前没开慢查询日志,先开起来: ``` SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; ``` long\_query\_time 设为 1 秒,超过 1 秒的查询都会被记录。生产环境也可以设 0.1,抓得更细,但日志会比较大。 等个几分钟,看看慢查询日志里有什么: ``` tail -f /var/log/mysql/slow.log ``` 果然,刷出来的全是同一条查询: ``` SELECT o.id, o.order_no, u.username, u.phone, p.name as product_name FROM orders o LEFT JOIN users u ON o.user_id = u.id LEFT JOIN products p ON o.product_id = p.id WHERE o.status = 1 ORDER BY o.created_at DESC LIMIT 20; ``` 这条查询关联了三张表,orders 表大约 120 万行,users 表 8 万行,products 表 3000 行。数据量不算特别大,但查询时间超过了 8 秒。 ## 用 EXPLAIN 分析 在查询前面加 EXPLAIN 看执行计划: ``` EXPLAIN SELECT o.id, o.order_no, u.username, u.phone, p.name as product_name FROM orders o LEFT JOIN users u ON o.user_id = u.id LEFT JOIN products p ON o.product_id = p.id WHERE o.status = 1 ORDER BY o.created_at DESC LIMIT 20; ``` 结果大概是这样的: ``` +----+-------+--------+------+---------+------+--------+---------------------------+ | id | table | type | key | key_len | rows | Extra | +----+-------+--------+------+---------+------+--------+---------------------------+ | 1 | o | ALL | NULL | NULL | 1.2M | Using where; Using filesort| | 1 | u | eq_ref | PRIMARY| 4 | 1 | NULL | | 1 | p | eq_ref | PRIMARY| 4 | 1 | NULL | +----+-------+--------+------+---------+------+--------+---------------------------+ ``` 问题很明显了: orders 表的 type 是 ALL,全表扫描 120 万行。key 是 NULL,说明没走索引。Extra 里有 Using filesort,排序也没走索引,在内存里排的。数据量一大内存排不下就会用磁盘临时文件,慢到离谱。 ## 加索引 WHERE 条件用了 status,ORDER BY 用了 created\_at。最直接的办法是给这俩字段建联合索引: ``` ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); ``` 建完索引再跑一遍 EXPLAIN: ``` +----+-------+-------+---------------------+---------+------+-------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-------+-------+---------------------+---------+------+-------+-------+ | 1 | o | range | idx_status_created | 4 | 8200 | Using index condition | | 1 | u | eq_ref| PRIMARY | 4 | 1 | NULL | | 1 | p | eq_ref| PRIMARY | 4 | 1 | NULL | +----+-------+-------+---------------------+---------+------+-------+-------+ ``` type 从 ALL 变成了 range,扫描行数从 120 万降到了 8200,filesort 也没了。查询时间从 8 秒降到了 0.05 秒。 这里有个点要注意:联合索引的字段顺序很重要。(status, created\_at) 和 (created\_at, status) 效果完全不一样。原则是把等值查询的字段放前面,范围查询和排序的字段放后面。这里 status 是等值查询,created\_at 是排序,所以 status 在前。 ## 覆盖索引优化 查询里还 SELECT 了 order\_no 字段,虽然走了索引但还是要回表去取 order\_no。如果把这个字段也加到索引里,就不用回表了: ``` ALTER TABLE orders ADD INDEX idx_status_created_no (status, created_at, id, order_no); ``` 这样查询所需的数据全在索引里能拿到,Extra 会显示 Using index,这就是覆盖索引。不过索引字段不能加太多,索引本身也占磁盘空间,写入时也要维护索引,得找平衡。 ## 坑:LIMIT 大偏移量 这个查询带 LIMIT 20,第一页很快。但是翻到后面,比如 LIMIT 100000, 20,又慢了。MySQL 要先扫描前 10 万行再丢弃,然后返回 20 行。 解决办法是用延迟关联: ``` SELECT o.id, o.order_no, u.username, u.phone, p.name as product_name FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 100000, 20 ) AS tmp ON o.id = tmp.id LEFT JOIN users u ON o.user_id = u.id LEFT JOIN products p ON o.product_id = p.id; ``` 子查询里只查 id,走覆盖索引,速度快很多。拿到 20 个 id 之后再跟其他表 JOIN,总共就 20 行数据,几乎不耗时。 更彻底的方案是游标分页,记住上一页最后一条的 created\_at 值,下一页直接 WHERE created\_at < 上一页的值 LIMIT 20,完全跳过偏移量。缺点是不能跳页,但对于无限滚动场景来说刚好。 ## 其他优化 EXPLAIN 里如果看到 Using temporary 和 Using filesort,说明用了临时表和文件排序,通常需要优化 GROUP BY 或 ORDER BY。确保排序字段有索引,且排序方向跟索引一致。 SELECT 别写 \*,需要什么字段查什么字段。SELECT \* 会让优化器没法用覆盖索引,而且传输的数据量也大。 定期用 ANALYZE TABLE 更新表统计信息, especially 在大批量导入或删除数据之后。统计信息不准会导致优化器选错索引。 ``` ANALYZE TABLE orders; ``` ## 服务器配置调优 除了 SQL 层面的优化,MySQL 自身配置也很重要。几个常用的参数: ``` [mysqld] innodb_buffer_pool_size = 1G innodb_log_file_size = 256M query_cache_type = 0 query_cache_size = 0 max_connections = 200 tmp_table_size = 64M max_heap_table_size = 64M ``` innodb\_buffer\_pool\_size 是最重要的一个,建议设为可用内存的 50%-70%。MySQL 8.0 把查询缓存去掉了,query\_cache 相关参数设 0 就行。 如果优化之后还是不够快,可能就是服务器配置跟不上了。这时候要么升级配置,要么上读写分离。雨云控制台可以一键升级配置,不用迁移数据,比较方便。地址 rain.jiyueip.com,优惠码 admin01 五折。 ## 最后 这次排查总结下来就几步:看 PROCESSLIST 找到卡住的查询,开慢查询日志定位具体 SQL,EXPLAIN 分析执行计划,发现全表扫描就加索引,深分页用延迟关联或游标分页解决。这套流程基本能应对大部分慢查询问题。 数据库优化是个持续的事,数据量涨了、查询模式变了都可能引入新的性能问题。定期看看慢查询日志,别等到线上报警了才想起来查。 **Tags:** Linux服务器, MySQL运维, VPS **Categories:** 行业洞察 ---