MySQL优化：百万级数据下平均与利润率计算查询提速

原方案的核心问题 #

1. PriceAVG视图效率极低：使用相关子查询+DISTINCT，每条记录都要单独执行一次AVG计算，300万条记录会触发300万次聚合操作，完全是性能灾难。
2. 冗余逻辑：DISTINCT属于多余操作，按country+items分组本身就能得到唯一组合；Margin视图中的EXISTS子查询完全没必要，已经通过JOIN关联了PriceAVG，重复过滤只会增加开销。
3. 缺失索引：没有针对分组、JOIN、过滤字段的索引，导致所有操作都走全表扫描，大数据集下必然超时。

步骤1：重构PriceAVG视图 #

用GROUP BY直接一次性完成聚合，替代低效的相关子查询：

CREATE VIEW PriceAVG AS
SELECT 
    country, 
    items, 
    ROUND(AVG(priceEuro)) AS average 
FROM Prices
GROUP BY country, items;

此方案仅需一次全表扫描（或索引扫描）即可完成所有country+items组合的平均计算，性能提升数量级。

步骤2：优化Margin视图 #

简化过滤逻辑，用表别名简化语句，同时提前过滤加拿大数据以减少JOIN的数据量：

CREATE VIEW Margin AS
SELECT  
    p.country, 
    p.items, 
    p.priceEuro, 
    pa.average, 
    (pa.average - p.priceEuro) AS margin,
    p.url  
FROM Prices p
INNER JOIN PriceAVG pa 
    ON p.items = pa.items 
    AND p.country = pa.country
WHERE pa.country = 'Canada'
ORDER BY margin DESC;

如果仅需加拿大的利润率数据，还可以进一步直接聚合加拿大数据，无需依赖通用的PriceAVG视图，效率更高：

CREATE VIEW Margin AS
WITH CanadaItemAvg AS (
    SELECT 
        items, 
        ROUND(AVG(priceEuro)) AS average 
    FROM Prices
    WHERE country = 'Canada'
    GROUP BY items
)
SELECT  
    p.country, 
    p.items, 
    p.priceEuro, 
    ca.average, 
    (ca.average - p.priceEuro) AS margin,
    p.url  
FROM Prices p
INNER JOIN CanadaItemAvg ca 
    ON p.items = ca.items 
    AND p.country = 'Canada'
ORDER BY margin DESC;

步骤3：添加关键索引（最核心优化） #

给Prices表创建覆盖复合索引，覆盖分组、JOIN、聚合所需的所有字段，让数据库无需回表即可完成计算：

CREATE INDEX idx_prices_country_items_price ON Prices (country, items, priceEuro);

如果允许修改表结构，建议将country字段从text改为varchar(50)（国家名称长度有限），更小的数据类型会让索引体积更小，查询速度更快：

ALTER TABLE Prices MODIFY COLUMN country varchar(50);

额外优化建议 #

• 避免在视图中固定ORDER BY：如果不是所有场景都需要按margin排序，建议在查询视图时再添加ORDER BY，避免不必要的排序开销。
• 定期清理无效数据：如果表中有过期或重复的url/记录，清理后能减少数据量，提升整体查询效率。

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