订单统计查询性能优化

题目

订单统计查询性能优化

信息

• 类型：问答
• 难度：⭐⭐

考点

索引优化,执行计划分析,SQL改写,连接查询优化

快速回答

优化要点：

• 分析执行计划确认性能瓶颈
• 为过滤条件和连接字段创建复合索引
• 改写SQL先聚合再连接
• 避免全表扫描和大数据集排序
• 考虑数据归档和分区策略

问题场景

现有订单统计查询：
SELECT c.customer_name, SUM(od.quantity * od.price) AS total_amount
FROM orders o
JOIN order_details od ON o.id = od.order_id
JOIN customers c ON o.customer_id = c.id
WHERE o.customer_id = 12345
AND o.order_date >= '2023-01-01'
GROUP BY c.customer_name;
当orders表500万行，order_details表3000万行时，查询耗时超过15秒。

原理说明

• 索引失效：未使用合适的索引导致全表扫描
• 连接顺序：先连接大表再过滤导致中间结果集过大
• 聚合开销：在大数据集上执行SUM和GROUP BY效率低
• 执行计划：通过EXPLAIN可查看查询执行路径

优化步骤

1. 分析执行计划

EXPLAIN SELECT ...

重点关注：
- type列：应避免ALL（全表扫描）
- key列：检查是否使用索引
- rows列：预估扫描行数
- Extra列：避免Using temporary; Using filesort

2. 索引优化

创建复合索引：

-- 优化WHERE和JOIN条件
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 优化连接和聚合
CREATE INDEX idx_order_details_order ON order_details(order_id);

索引设计原则：
- 最左前缀原则：customer_id必须在order_date前
- 覆盖索引：包含所有查询字段避免回表

3. SQL改写

方案1：先过滤再连接

SELECT c.customer_name, agg.total_amount
FROM customers c
JOIN (
  SELECT o.customer_id, SUM(od.quantity * od.price) AS total_amount
  FROM orders o
  JOIN order_details od ON o.id = od.order_id
  WHERE o.customer_id = 12345
  AND o.order_date >= '2023-01-01'
  GROUP BY o.customer_id
) agg ON c.id = agg.customer_id;

方案2：使用CTE分步处理

WITH filtered_orders AS (
  SELECT id, customer_id
  FROM orders
  WHERE customer_id = 12345
  AND order_date >= '2023-01-01'
)
SELECT c.customer_name, SUM(od.quantity * od.price)
FROM filtered_orders fo
JOIN order_details od ON fo.id = od.order_id
JOIN customers c ON fo.customer_id = c.id
GROUP BY c.customer_name;

4. 最佳实践

• 连接顺序：小表驱动大表，优先过滤后连接
• 避免SELECT ***：只选择必要字段
• 定期维护：
  ANALYZE TABLE orders; 更新统计信息
  OPTIMIZE TABLE order_details; 整理碎片
• 归档历史数据：将1年前数据迁移到历史表

常见错误

• 在WHERE条件中对索引列使用函数：
  WHERE YEAR(order_date) = 2023 → 索引失效
• 创建冗余索引：如单独创建(customer_id)和(customer_id, order_date)
• 忽略GROUP BY隐式排序：添加ORDER BY NULL避免排序开销

扩展知识

• 分区表：按order_date做范围分区加速过滤
• 覆盖索引：创建包含quantity和price的索引
  CREATE INDEX idx_order_details_cover ON order_details(order_id, quantity, price)
• 查询缓存：对重复查询开启缓存（注意数据实时性要求）
• 执行计划解读：
  ref：索引查找
  index：索引全扫描
  ALL：全表扫描（需优化）