上周三晚上接到报警,网站打开特别慢,有的页面直接超时。登上去一看,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 分析执行计划,发现全表扫描就加索引,深分页用延迟关联或游标分页解决。这套流程基本能应对大部分慢查询问题。
数据库优化是个持续的事,数据量涨了、查询模式变了都可能引入新的性能问题。定期看看慢查询日志,别等到线上报警了才想起来查。






