MySQL 5.6查询商品各地点最新采购成本语句优化求助

问题根因 #

你原有语句使用的是相关子查询，MySQL 5.6对这类依赖外层查询字段的子查询优化能力极弱：外层每扫描1行数据，内层就会触发1次子查询，表数据量越大，执行次数越高，最终导致查询长时间无响应。

优化后适配MySQL 5.6的查询语句 #

用非相关子查询先预计算每个<地点,商品>组合在指定日期前的最大采购日期，再关联原表取成本数据，子查询仅需执行1次，性能提升非常明显：

SELECT 
    t1.Location,
    t1.Item,
    MAX(t1.Cost) AS Cost,
    t1.Date
FROM 替换为你的实际表名 t1
INNER JOIN (
    SELECT 
        Location,
        Item,
        MAX(Date) AS max_date
    FROM 替换为你的实际表名
    WHERE Date <= '替换为你要查询的指定日期'
    GROUP BY Location, Item
) t2 ON t1.Location = t2.Location 
    AND t1.Item = t2.Item 
    AND t1.Date = t2.max_date
GROUP BY t1.Location, t1.Item, t1.Date;

进一步性能提升建议 #

给表建如下联合覆盖索引，避免查询回表，可将性能提升到最优：

CREATE INDEX idx_loc_item_date_cost ON 替换为你的实际表名 (Location, Item, Date, Cost);

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