MySQL8同表多次关联查询优化：车辆分车型库存龄分时段统计咨询

优化核心逻辑 #

替换你当前多次LEFT JOIN子查询的写法，改用条件聚合实现，仅需一次表关联即可完成全区间统计，性能和可维护性都有明显提升。

优化后查询语句 #

SELECT 
    vm.class,
    SUM(CASE WHEN stock_age BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS t1c,
    SUM(CASE WHEN stock_age BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS t2c,
    SUM(CASE WHEN stock_age BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS t3c,
    SUM(CASE WHEN stock_age BETWEEN 91 AND 120 THEN 1 ELSE 0 END) AS t4c,
    SUM(CASE WHEN stock_age BETWEEN 121 AND 150 THEN 1 ELSE 0 END) AS t5c,
    SUM(CASE WHEN stock_age BETWEEN 151 AND 180 THEN 1 ELSE 0 END) AS t6c,
    SUM(CASE WHEN stock_age >= 181 THEN 1 ELSE 0 END) AS t7c
    -- 若需要统计到360+天，按相同规则继续添加CASE WHEN逻辑即可
FROM vehicle_models vm
LEFT JOIN (
    SELECT 
        model_id,
        DATEDIFF(IFNULL(retail_date, NOW()), wholesale_date) AS stock_age
    FROM a3s186jg7ffmm0q8.vehicles
) v ON vm.id = v.model_id
GROUP BY vm.class
ORDER BY vm.class;

优化效果说明 #

• 性能提升：原写法需要扫描N次车辆、车型表（N为区间数量），优化后仅需扫描1次，数据量越大性能优势越显著
• 可维护性提升：新增/修改统计区间仅需增减对应SUM(CASE...)行，无需重复编写子查询与关联逻辑
• 代码简洁：去掉了冗余的重复子查询、DISTINCT去重逻辑，结构更清晰

进阶优化建议（针对超大数据量场景） #

可以在vehicles表中新增存储类型的生成列预计算库存龄，再搭配联合索引，进一步减少查询时的计算开销：

-- 新增自动同步的库存龄生成列
ALTER TABLE a3s186jg7ffmm0q8.vehicles 
ADD COLUMN stock_age INT GENERATED ALWAYS AS (DATEDIFF(IFNULL(retail_date, NOW()), wholesale_date)) STORED;
-- 建立联合索引，覆盖关联字段与统计字段
CREATE INDEX idx_model_stockage ON a3s186jg7ffmm0q8.vehicles(model_id, stock_age);

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