Post Title

问题场景

运营同事投诉："订单日报表加载要等半分钟，能不能优化一下？"

原始 SQL：

SELECT 
    DATE(o.created_at) as date,
    u.region,
    COUNT(DISTINCT o.id) as order_count,
    SUM(o.total_amount) as revenue,
    AVG(o.total_amount) as avg_order_value
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at BETWEEN '2026-06-01' AND '2026-06-30'
  AND o.status IN ('paid', 'shipped', 'completed')
GROUP BY DATE(o.created_at), u.region
ORDER BY date, revenue DESC;

数据量：orders 表 500 万行，users 表 50 万行，order_items 表 2000 万行。

Step 1：EXPLAIN 分析（找出瓶颈）

EXPLAIN SELECT ...;

结果：
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+
| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL  | NULL    | NULL | 5000000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ALL  | PRIMARY       | NULL  | NULL    | NULL | 500000  | Using where; Using join buffer |
|  1 | SIMPLE      | oi    | ALL  | NULL          | NULL  | NULL    | NULL | 20000000| Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+

全是 ALL（全表扫描）。问题找到了。

Step 2：建索引（对症下药）

-- orders 表：覆盖 WHERE + GROUP BY
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- order_items 表：加速 JOIN
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- users 表（已有主键索引，但 JOIN 字段需要确认）
-- user_id 已经是主键，OK

优化后 EXPLAIN：orders 表用上了 idx_orders_status_date，rows 从 500 万降到 8 万。但还有 Using temporary; Using filesort。

Step 3：消灭临时表和文件排序

-- 问题在 GROUP BY DATE(o.created_at), u.region
-- DATE() 函数破坏了索引的有序性

-- 方案：把日期提取到查询条件中
SELECT 
    o.order_date,  -- 直接用一个 DATE 类型字段（不调用函数）
    COALESCE(u.region, 'Unknown') as region,
    COUNT(DISTINCT o.id) as order_count,
    SUM(o.total_amount) as revenue
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2026-06-01' AND '2026-06-30'
  AND o.status IN ('paid', 'shipped', 'completed')
GROUP BY o.order_date, u.region
ORDER BY o.order_date;

Step 4：移除不必要的 JOIN

-- order_items 的 JOIN 完全没用！因为是用 COUNT(DISTINCT o.id) 而不是 COUNT(*)
-- 而且 AVG(o.total_amount) 也不依赖 order_items

-- 直接删掉：
-- LEFT JOIN order_items oi ON o.id = oi.order_id  ← 删掉这行

Step 5：覆盖索引（终极优化）

-- 把查询需要的字段全放进索引，避免回表
CREATE INDEX idx_orders_covering ON orders(
    status, order_date, user_id, total_amount
);
-- MySQL 可以直接从索引中获取所有需要的数据，不需要读数据页！

最终 SQL 与性能对比

SELECT 
    o.order_date,
    COALESCE(u.region, 'Unknown') as region,
    COUNT(DISTINCT o.id) as order_count,
    SUM(o.total_amount) as revenue
FROM orders o USE INDEX(idx_orders_covering)
LEFT JOIN users u ON o.user_id = u.id  
WHERE o.order_date BETWEEN '2026-06-01' AND '2026-06-30'
  AND o.status IN ('paid', 'shipped', 'completed')
GROUP BY o.order_date, u.region
ORDER BY o.order_date;

指标	优化前	优化后
执行时间	23.6 秒	0.03 秒
扫描行数	25,500,000	1,200
Extra	Using where; Using temporary; Using filesort; Using join buffer	Using index

慢查询优化的四步法

EXPLAIN 分析：看 type（range > ref > ALL）、rows、Extra
建索引：覆盖 WHERE、JOIN、ORDER BY、GROUP BY 的字段
改写 SQL：消除不必要的 JOIN，避免在 WHERE 中调用函数
覆盖索引：让 MySQL 从索引中直接拿数据，不需要回表

记住：不是所有的慢查询都需要在应用代码层面解决，90% 的情况是 SQL 写法和索引的问题。