面试题：MySQL查询切分策略之专家难度

Question

假设有一个超大规模的电商数据库，其中商品表products（包含product_id, product_name, price等字段）和订单明细表order_items（包含order_item_id, order_id, product_id, quantity等字段）关联。需要实现一个复杂的查询，找出每个季度销量排名前5的商品及其销售总额，并且要在不同的MySQL实例间进行查询切分以保证高效。请阐述你会如何设计这个查询切分策略，包括如何处理跨实例数据聚合，以及最终实现该查询的详细方案和SQL代码。

Answer 1

设计查询切分策略

1. 按季度切分：由于需求是按季度统计，可按时间维度（季度）对订单明细表order_items进行切分。假设订单表中有记录订单创建时间的字段order_date，将不同季度的数据分布在不同的MySQL实例上。例如，通过在表创建时利用PARTITION BY RANGE (YEAR(order_date) * 10 + QUARTER(order_date))进行分区，每个分区放置在不同实例。
2. 按商品ID切分：对于商品表products，由于其数据量也较大，可按product_id进行哈希切分，将不同范围的product_id数据分布在不同实例。例如，通过MOD(product_id, N)，其中N为实例数量，将商品数据均匀分布在各实例。

处理跨实例数据聚合

1. 中间结果收集：每个实例先独立计算出本实例内每个季度销量排名前5的商品及其销售总额。可以使用窗口函数实现此计算。
2. 全局聚合：将各个实例的中间结果汇总到一个主实例上。在主实例上，对这些中间结果再次进行排名计算，得出最终每个季度销量排名前5的商品及其销售总额。

详细方案及SQL代码

1. 每个实例内计算中间结果

-- 在每个实例上执行，假设当前实例处理的order_items数据是对应某几个季度的
WITH RankedSales AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.quantity * p.price) AS total_sales,
        YEAR(oi.order_date) * 10 + QUARTER(oi.order_date) AS quarter_key,
        ROW_NUMBER() OVER (PARTITION BY YEAR(oi.order_date) * 10 + QUARTER(oi.order_date) ORDER BY SUM(oi.quantity) DESC) AS rn
    FROM
        products p
    JOIN
        order_items oi ON p.product_id = oi.product_id
    GROUP BY
        p.product_id, p.product_name, YEAR(oi.order_date) * 10 + QUARTER(oi.order_date)
)
SELECT
    product_id,
    product_name,
    total_quantity,
    total_sales,
    quarter_key
FROM
    RankedSales
WHERE
    rn <= 5;

2. 主实例上进行全局聚合

-- 在主实例上执行，假设将各实例的中间结果插入到临时表temp_ranked_sales中
WITH GlobalRankedSales AS (
    SELECT
        product_id,
        product_name,
        total_quantity,
        total_sales,
        quarter_key,
        ROW_NUMBER() OVER (PARTITION BY quarter_key ORDER BY total_quantity DESC) AS global_rn
    FROM
        temp_ranked_sales
)
SELECT
    product_id,
    product_name,
    total_quantity,
    total_sales,
    quarter_key
FROM
    GlobalRankedSales
WHERE
    global_rn <= 5;