如何优化获取Top 100分销商的SQL查询以提升执行速度？

核心优化方向 #

针对你的SQL执行耗时久的问题，从减少中间计算、优化索引、修正业务逻辑、简化关联四个维度进行优化：

1. 合并冗余中间计算，减少临时表开销 #

原SQL拆分了items_total和orders_total两个CTE，可合并为一个子查询，避免重复扫描表和生成临时表：

-- 合并后的订单总额计算（直接按购买者ID聚合，减少后续关联次数）
SELECT 
    o.purchaser_id, 
    SUM(oi.quantity * p.price) AS total
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
GROUP BY o.purchaser_id

2. 添加关键索引，加速关联与过滤 #

为高频关联、过滤字段创建覆盖索引（避免回表查询）：

-- 订单明细：覆盖产品ID、订单ID、数量字段
CREATE INDEX idx_order_items_prod_order_qty ON order_items(product_id, order_id, quantity);
-- 订单：覆盖购买者ID字段
CREATE INDEX idx_orders_purchaser ON orders(purchaser_id);
-- 用户：覆盖推荐人ID、基础信息字段
CREATE INDEX idx_users_referred_info ON users(id, referred_by, first_name, last_name);
-- 用户分类：覆盖用户ID、分类ID字段
CREATE INDEX idx_user_category_user_cat ON user_category(user_id, category_id);

3. 简化分销商查询逻辑 #

原distributors CTE中的GROUP BY u.id属于冗余操作（已通过category_id=1过滤，用户与分类关联为多对一），同时去掉未使用的c.name字段：

SELECT 
    u.id AS dist_id, 
    u.first_name, 
    u.last_name
FROM users u
INNER JOIN user_category uc ON uc.user_id = u.id
WHERE uc.category_id = 1

4. 修正业务逻辑：包含分销商自身订单 #

原SQL仅统计推荐客户的订单，未包含分销商自身的订单，通过UNION ALL合并自身与推荐客户的关联关系：

优化后的完整SQL #

WITH order_totals AS (
    -- 预计算每个用户的订单总额
    SELECT 
        o.purchaser_id, 
        SUM(oi.quantity * p.price) AS total
    FROM orders o
    INNER JOIN order_items oi ON oi.order_id = o.id
    INNER JOIN products p ON p.id = oi.product_id
    GROUP BY o.purchaser_id
),
distributors AS (
    -- 获取所有分销商基础信息
    SELECT 
        u.id AS dist_id, 
        u.first_name, 
        u.last_name
    FROM users u
    INNER JOIN user_category uc ON uc.user_id = u.id
    WHERE uc.category_id = 1
),
related_users AS (
    -- 合并分销商自身 + 推荐的客户ID
    SELECT dist_id, dist_id AS user_id FROM distributors
    UNION ALL
    SELECT d.dist_id, uu.id AS user_id
    FROM distributors d
    INNER JOIN users uu ON uu.referred_by = d.dist_id
)
-- 最终聚合计算分销商总销售额并排名
SELECT
    d.first_name,
    d.last_name,
    COALESCE(SUM(ot.total), 0) AS total_sales,
    d.dist_id AS id,
    DENSE_RANK() OVER (ORDER BY COALESCE(SUM(ot.total), 0) DESC) AS `rank`
FROM distributors d
INNER JOIN related_users ru ON ru.dist_id = d.dist_id
LEFT JOIN order_totals ot ON ot.purchaser_id = ru.user_id
GROUP BY d.dist_id, d.first_name, d.last_name
ORDER BY total_sales DESC
LIMIT 100;

额外性能提升建议 #

• 调整MySQL配置：增大innodb_buffer_pool_size（建议设为内存的70%）、sort_buffer_size，减少磁盘IO
• 定期统计信息：执行ANALYZE TABLE orders, order_items, users, user_category;让优化器生成更优执行计划
• 避免大表排序：若数据量极大，可先计算分销商销售额并存入临时表，再排序取前100

内容的提问来源于stack exchange，提问作者Abd Qadr