Q2 电商订单数据分析优化

场景描述

某电商平台有3张核心表:

-- 用户表(1亿条数据)
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  user_name VARCHAR(50),
  reg_date DATE
);

-- 订单表(10亿条数据)
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  status VARCHAR(20)
;

-- 订单明细表(100亿条数据)
CREATE TABLE order_details (
  order_detail_id BIGINT PRIMARY KEY,
  order_id BIGINT,
  product_id INT,
  quantity INT,
  price DECIMAL(10,2)
);

原始查询需求

获取2023年注册用户的:

  1. 总订单金额

  2. 最近一次下单日期

  3. 订单完成率(completed订单数/总订单数)

现有低效查询:

SELECT 
  u.user_id,
  (SELECT SUM(od.price) 
   FROM orders o 
   JOIN order_details od ON o.order_id = od.order_id
   WHERE o.user_id = u.user_id) AS total_amount,
  
  (SELECT MAX(order_date)
   FROM orders 
   WHERE user_id = u.user_id) AS last_order_date,
  
  (SELECT COUNT(*) FILTER (WHERE status = 'completed')::FLOAT / COUNT(*)
   FROM orders 
   WHERE user_id = u.user_id) AS completion_rate
FROM users u
WHERE u.reg_date BETWEEN '2023-01-01' AND '2023-12-31';

题目要求
  1. 分析原始查询的性能瓶颈

  2. 重写优化后的SQL查询

  3. 建议需要创建的索引

  4. 解释优化思路


参考答案

优化后查询

WITH order_aggs AS (
  SELECT 
    o.user_id,
    SUM(od.price) AS total_amount,
    MAX(o.order_date) AS last_order_date,
    COUNT(*) FILTER (WHERE o.status = 'completed')::FLOAT / COUNT(*) AS completion_rate
  FROM orders o
  JOIN order_details od ON o.order_id = od.order_id
  GROUP BY o.user_id
)
SELECT 
  u.user_id,
  oa.total_amount,
  oa.last_order_date,
  oa.completion_rate
FROM users u
LEFT JOIN order_aggs oa ON u.user_id = oa.user_id
WHERE u.reg_date BETWEEN '2023-01-01' AND '2023-12-31';

建议索引

CREATE INDEX idx_users_regdate ON users(reg_date);
CREATE INDEX idx_orders_userid ON orders(user_id);
CREATE INDEX idx_orders_userid_status ON orders(user_id, status);
CREATE INDEX idx_orderdetails_orderid ON order_details(order_id);

优化思路

  1. 消除重复子查询:将三个关联子查询合并为一次CTE聚合查询,减少orders表的扫描次数

  2. JOIN优化

    1. 使用LEFT JOIN替代相关子查询

    2. 先对orders和order_details进行JOIN后再聚合

  3. 索引优化

    1. users.reg_date索引快速定位2023年用户

    2. orders.user_id索引加速用户订单关联

    3. 复合索引orders(user_id, status)覆盖状态过滤

    4. order_details.order_id索引加速订单明细关联

  4. 执行计划优化

    1. 避免Nested Loop导致的大量重复扫描

    2. 减少临时中间表的生成

    3. 充分利用Hash Join和GroupAggregate

  5. 计算优化

    1. 使用一次COUNT(*)配合FILTER条件,避免多次扫描订单表

    2. 使用CTE物化中间结果集

性能对比

优化项

原始查询

优化后查询

orders表扫描次数

3次

1次

order_details扫描次数

3次

1次

JOIN类型

Nested Loop

Hash Join

临时表生成

多次

1次CTE

该优化可使执行时间从小时级降低到分钟级,特别是在海量数据场景下效果显著。 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值