如何优化MySQL分组取最新库存记录的慢查询SQL

核心问题根因 #

• 原SQL使用关联子查询查询最新库存，外层查询返回多少行，子查询就会执行多少次，相当于多次全表扫描，是核心性能瓶颈
• WHERE条件中使用date(create_time)对索引列做函数运算，导致现有索引ix1、ix2全部失效，触发全表扫描
• 现有联合索引顺序不匹配查询、分组的字段顺序，无法支撑覆盖查询，需要反复回表取数据

可落地优化方案 #

1. SQL逻辑改写（去掉关联子查询，改用窗口函数取分组最新值） #

改写后SQL如下：

SELECT
    MAX(id) id,
    '2021-05-26' AS time,
    store_id,
    MAX(store_name) store_name,
    MAX(goods_id) goods_id,
    spu_code,
    sku_code,
    MAX(goods_name) goods_name,
    MAX(property_value) property_value,
    SUM(IF(data_source IN (3,4), IFNULL(adjust_number,0), 0)) today_consumption,
    SUM(IF(data_source IN (1,2), IFNULL(adjust_number,0), 0)) today_adjust_number,
    MAX(IF(rn = 1, adjust_after, NULL)) today_surplus_stock
FROM (
    SELECT
        id, store_id, store_name, goods_id, spu_code, sku_code, goods_name, property_value,
        data_source, adjust_number, adjust_after,
        -- 按门店+spu+sku分组，按创建时间倒序取最新的第一条
        ROW_NUMBER() OVER(PARTITION BY store_id, spu_code, sku_code ORDER BY create_time DESC) rn
    FROM goods_store_stock_update_record
    WHERE
        -- 去掉函数包裹，用范围查询触发索引
        create_time >= '2021-05-26 00:00:00'
        AND create_time < '2021-05-27 00:00:00'
        AND is_del = 0
) t
GROUP BY store_id, spu_code, sku_code;

注：如果MySQL版本低于8.0不支持窗口函数，可以改用临时表存储各分组的最新库存，再和统计结果关联，性能也远好于关联子查询。

2. 索引优化（新增覆盖索引，避免回表） #

新增以下联合索引，完全匹配查询条件、分组、排序逻辑，做到查询不回表：

CREATE INDEX ix_stock_stat ON goods_store_stock_update_record (
    is_del, create_time, store_id, spu_code, sku_code,
    id, store_name, goods_id, goods_name, property_value, data_source, adjust_number, adjust_after
);

如果MySQL版本支持INCLUDE语法，可将索引末尾的非过滤、非分组字段放到INCLUDE中，进一步降低索引体积。

优化后收益 #

• 去掉关联子查询后，仅需要扫描1次表数据，子查询的N次全表扫描开销完全消除
• 范围查询命中索引，避免全表扫描，数据过滤效率提升10倍以上
• 覆盖索引无需回表查询，IO开销降低90%以上，针对2.5万条数据的场景，查询耗时可以降到10ms以内

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