MySQL 上亿数据查询优化：策略与实践

-- 假设我们有一个订单表 orders，需要优化查询
-- 查询近30天内每个商家的总销售额
 
-- 方法1: 使用WITH子句提前计算数据，减少重复计算
WITH subquery AS (
    SELECT
        shop_id,
        SUM(amount) AS total_sales
    FROM
        orders
    WHERE
        order_date >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY
        shop_id
)
SELECT
    *
FROM
    subquery;
 
-- 方法2: 使用索引优化查询
-- 假设我们对(order_date, shop_id)字段建立了索引
SELECT
    shop_id,
    SUM(amount) AS total_sales
FROM
    orders
WHERE
    order_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY
    shop_id;
 
-- 方法3: 使用索引覆盖优化查询
-- 假设我们在(order_date, shop_id, amount)上建立了索引，并且amount字段是紧跟shop_id的
SELECT
    shop_id,
    SUM(amount) AS total_sales
FROM
    orders
WHERE
    order_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY
    shop_id;

在这个例子中，我们展示了三种可能的优化策略。第一种方法使用了公用表表达式（Common Table Expression, CTE）来预先计算数据，减少了重复的分组和排序操作。第二种方法假设我们对order_date和shop_id字段建立了索引，并且查询优化器能够利用这个索引来加快查询速度。第三种方法进一步假设我们在包含amount字段的索引中实现了索引覆盖，从而避免了额外的排序操作，进一步提高了查询效率。