SQL 优化：一条查询从30秒到0.1秒

上周，生产环境数据库 CPU 突然飙到 100%，查了半天发现是一条慢查询导致的。

这条 SQL 执行时间：30秒。

优化后：0.1秒。

提升了 300倍。

问题 SQL

先看原始 SQL：

SELECT u.id, u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2024-01-01' AND u.status = 'active' AND o.created_at >= '2024-01-01' GROUP BY u.id ORDER BY total_amount DESC LIMIT 100;

看起来没问题对吧？但它跑了 30 秒。

第一步：查看执行计划

先用 EXPLAIN 看看执行计划：

EXPLAIN SELECT ...;

结果：

+----+-------------+-------+------+---------------+------+---------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----------+-----------------------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where; Using filesort | | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 5000000 | Using where | +----+-------------+-------+------+---------------+------+---------+------+----------+-----------------------------+

问题明显了：

• type = ALL：全表扫描
• rows = 1000000：扫描了100万行用户
• rows = 5000000：扫描了500万行订单
• Using filesort：排序没用索引

第二步：加索引

问题1：user 表没索引

WHERE 条件用了 created_at 和 status，但都没索引。

加一个联合索引：

CREATE INDEX idx_users_created_status ON users(created_at, status);

为什么是联合索引？因为两个字段都在 WHERE 里，联合索引可以同时用上。

问题2：order 表没索引

JOIN 条件是 user_id，WHERE 条件有 created_at。

加索引：

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

这样 JOIN 和 WHERE 都能用上索引。

优化后的执行计划

+----+-------------+-------+------+---------------------------+---------------------------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------+---------------------------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | u | range| idx_users_created_status | idx_users_created_status | 9 | NULL | 50000 | Using where; Using filesort | | 1 | SIMPLE | o | ref | idx_orders_user_created | idx_orders_user_created | 8 | u.id | 5 | Using where | +----+-------------+-------+------+---------------------------+---------------------------+---------+------+-------+-----------------------------+

好多了：

• type = range/ref：用上了索引
• rows = 50000：user 表扫描行数从100万降到5万
• rows = 5：每个用户平均5个订单

执行时间：从 30秒 降到 2秒。

但还不够快。

第三步：优化SQL写法

问题3：LEFT JOIN 有坑

仔细看，WHERE 里有 o.created_at >= '2024-01-01'。

这意味着：如果用户没订单，o.created_at 是 NULL，不满足条件，会被过滤掉。

既然如此，用 INNER JOIN 更合适：

SELECT u.id, u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2024-01-01' AND u.status = 'active' AND o.created_at >= '2024-01-01' GROUP BY u.id ORDER BY total_amount DESC LIMIT 100;

INNER JOIN 比 LEFT JOIN 快，因为不用处理 NULL。

执行时间：从 2秒 降到 0.5秒。

问题4：ORDER BY 太慢

ORDER BY total_amount 是聚合后的字段，没法用索引。

但我们只需要 TOP 100，可以先在子查询里筛选：

SELECT u.id, u.name, u.email, order_count, total_amount FROM ( SELECT u.id, u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2024-01-01' AND u.status = 'active' AND o.created_at >= '2024-01-01' GROUP BY u.id HAVING total_amount IS NOT NULL ORDER BY total_amount DESC LIMIT 100 ) t;

加了 HAVING 过滤 NULL，减少排序的数据量。

执行时间：从 0.5秒 降到 0.2秒。

第四步：终极优化

我发现一个问题：每次查询都要重新计算 COUNT 和 SUM。

如果数据不需要实时，可以用物化视图或定期汇总。

创建一个汇总表：

CREATE TABLE user_order_stats ( user_id INT PRIMARY KEY, order_count INT, total_amount DECIMAL(10,2), updated_at TIMESTAMP, INDEX idx_total_amount (total_amount) );

定时任务每小时更新一次：

INSERT INTO user_order_stats (user_id, order_count, total_amount, updated_at) SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_amount, NOW() FROM orders WHERE created_at >= '2024-01-01' GROUP BY user_id ON DUPLICATE KEY UPDATE order_count = VALUES(order_count), total_amount = VALUES(total_amount), updated_at = VALUES(updated_at);

查询变成：

SELECT u.id, u.name, u.email, s.order_count, s.total_amount FROM users u INNER JOIN user_order_stats s ON u.id = s.user_id WHERE u.created_at >= '2024-01-01' AND u.status = 'active' ORDER BY s.total_amount DESC LIMIT 100;

执行时间：0.1秒。

优化总结

总共提升了 300倍。

SQL 优化的通用方法

1. 先看执行计划：EXPLAIN 是你的好朋友
2. 加索引：WHERE、JOIN、ORDER BY 的字段都应该有索引
3. 避免全表扫描：type = ALL 就是红灯
4. 选对 JOIN 类型：能用 INNER 就别用 LEFT
5. 减少计算量：汇总表、缓存、预计算
6. LIMIT 用好：不需要全部数据就别查全部
7. 避免 SELECT *：只查需要的字段

常见的慢查询原因

• 没索引：最常见的原因
• 索引失效：用了函数、OR、!= 等
• 数据量太大：该分表了
• JOIN 太多：超过3个表就要小心
• 子查询太深：能改成 JOIN 就改

总结

SQL 优化不是魔法，就是：

1. 找到慢在哪
2. 加索引
3. 改写法
4. 预计算

大部分慢查询都能优化，只要你愿意花时间。

工具推荐：Percona Toolkit - MySQL 性能分析工具