MySQL跨年度大日期范围查询性能优化求助

一、核心索引优化 #

替换现有(ordered_at, sales_channel_id)索引，创建联合覆盖索引：

CREATE INDEX idx_sales_ordered_sku_qty ON order_items (sales_channel_id, ordered_at, sku, quantity);

同时给inventory表添加辅助索引：

CREATE INDEX idx_sku_id_model ON inventory (sku, id, model_id);

优化逻辑：

• sales_channel_id是等值过滤（IN条件），放在索引最前端，MySQL能快速筛选出目标渠道的记录；
• ordered_at作为范围条件放在第二位，保证日期范围查询能命中索引；
• 后续的sku和quantity是查询直接用到的字段，实现索引覆盖，完全不需要回表读取原表数据，即使大日期范围也能高效遍历索引。

二、重构关联查询，消除低效子查询 #

原查询中inventory.id = (SELECT min(id) FROM inventory WHERE sku = order_items.sku)是关联子查询，每条order_items记录都会触发一次子查询，720k条数据会产生720k次查询，这是核心性能瓶颈之一。改成预聚合方式：

方案1（MySQL 8.0+支持CTE） #

WITH inventory_min AS (
    SELECT sku, model_id
    FROM (
        SELECT 
            sku, 
            model_id, 
            id,
            ROW_NUMBER() OVER(PARTITION BY sku ORDER BY id ASC) AS rn
        FROM inventory
    ) t
    WHERE rn = 1
)
SELECT
    im.model_id,
    YEAR(oi.ordered_at) AS Year,
    MONTHNAME(oi.ordered_at) AS Month,
    CONCAT("Week ", FLOOR(((DAY(oi.ordered_at) - 1) / 7) + 1)) AS Week,
    SUM(oi.quantity) AS UnitsSold
FROM
    order_items oi
    JOIN inventory_min im ON im.sku = oi.sku
WHERE
    oi.ordered_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 23:59:59'
    AND oi.sales_channel_id IN(1, 2, 3, 4)
GROUP BY
    im.model_id, Year, MONTH(oi.ordered_at), Month, Week
ORDER BY
    im.model_id ASC,
    Year ASC,
    MONTH(oi.ordered_at) ASC,
    Week ASC;

方案2（兼容低版本MySQL） #

SELECT
    im.model_id,
    YEAR(oi.ordered_at) AS Year,
    MONTHNAME(oi.ordered_at) AS Month,
    CONCAT("Week ", FLOOR(((DAY(oi.ordered_at) - 1) / 7) + 1)) AS Week,
    SUM(oi.quantity) AS UnitsSold
FROM
    order_items oi
    JOIN (
        SELECT sku, model_id
        FROM (
            SELECT 
                sku, 
                model_id, 
                id,
                ROW_NUMBER() OVER(PARTITION BY sku ORDER BY id ASC) AS rn
            FROM inventory
        ) t
        WHERE rn = 1
    ) im ON im.sku = oi.sku
WHERE
    oi.ordered_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 23:59:59'
    AND oi.sales_channel_id IN(1, 2, 3, 4)
GROUP BY
    im.model_id, Year, MONTH(oi.ordered_at), Month, Week
ORDER BY
    im.model_id ASC,
    Year ASC,
    MONTH(oi.ordered_at) ASC,
    Week ASC;

优化点：只对inventory表做一次聚合计算，获取每个sku对应最小id的model_id，再和order_items关联，彻底避免重复查询。

三、修正分组与排序的冗余问题 #

1. 原查询GROUP BY中的order_items.model_id是错误的（order_items表无该字段），必须改为im.model_id（或inventory.model_id），否则会导致逻辑错误或额外计算开销；
2. ORDER BY中的Month字段是MONTHNAME的结果，其排序顺序和MONTH(oi.ordered_at)完全一致，可从排序条件中移除，减少排序计算量。

四、长期优化建议 #

• 预计算日期维度：如果频繁按年、月、周统计，可给order_items表添加生成列并建立索引：

  ALTER TABLE order_items ADD COLUMN year_ordered YEAR GENERATED ALWAYS AS (YEAR(ordered_at)) STORED;
  ALTER TABLE order_items ADD COLUMN month_ordered TINYINT GENERATED ALWAYS AS (MONTH(ordered_at)) STORED;
  ALTER TABLE order_items ADD COLUMN week_of_month TINYINT GENERATED ALWAYS AS (FLOOR(((DAY(ordered_at) - 1)/7)+1)) STORED;
  

  之后更新索引包含这些列，分组和排序时直接使用预计算值，避免每次调用日期函数。
• 预聚合报表数据：针对同比分析场景，可定时将历史数据的聚合结果存储到专用报表表中，查询时直接读取报表表，避免重复计算大量历史数据。
• 调整MySQL配置：确保innodb_buffer_pool_size足够大（建议设置为服务器内存的50%-70%），让大部分数据和索引缓存到内存，减少磁盘IO。

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