优化JOIN后的GROUP BY查询：MySQL视图性能优化求助

看起来你遇到了自连接查询在添加GROUP BY后性能骤降的问题，结合你的场景（要用于视图），我给你几个针对性的优化方向：

1. 先过滤再连接，减少中间数据量 #

你当前的JOIN条件里包含h1.AssetClass = 'FX'和h2.AssetClass = 'FX'，可以先通过子查询把AssetClass为FX的数据集筛选出来，再进行自连接，这样能大幅减少后续连接处理的数据量：

WITH h_fx AS (
    SELECT Date, Currency, Account, Size, MaturityDate, SecID, Ticker
    FROM htable
    WHERE AssetClass = 'FX'
)
SELECT 
    h1.Date, 
    h1.Currency AS Currency1, 
    h2.Currency AS Currency2, 
    h1.Account, 
    SUM(h1.Size) AS Size1, 
    SUM(h2.Size) AS Size2, 
    h1.MaturityDate 
FROM h_fx h1
JOIN h_fx h2 
    ON h1.Date = h2.Date 
    AND h1.SecID = h2.SecID 
    AND SUBSTR(h1.Ticker, 7, 3) <> h1.Currency 
    AND SUBSTR(h2.Ticker, 7, 3) = h2.Currency
GROUP BY h1.Date, h1.Currency, h2.Currency, h1.Account, h1.MaturityDate
HAVING SUM(h1.Size) <> 0 AND SUM(h2.Size) <> 0

2. 消除JOIN条件中的函数调用，利用索引 #

SUBSTR(h1.Ticker, 7, 3)这类函数调用会导致MySQL无法使用Ticker列的索引，你可以通过计算列+索引来解决：
首先添加存储型计算列：

ALTER TABLE htable 
ADD COLUMN TickerSuffix VARCHAR(3) 
GENERATED ALWAYS AS (SUBSTR(Ticker, 7, 3)) STORED;

然后为计算列和相关查询列创建覆盖索引：

CREATE INDEX idx_htable_fx_suffix ON htable (AssetClass, Date, SecID, Currency, TickerSuffix)
INCLUDE (Account, MaturityDate, Size); -- MySQL 8.0+支持INCLUDE，低版本可以把这些列加到索引里

之后把查询中的SUBSTR(...)替换成TickerSuffix，就能让索引生效了。

3. 先聚合再连接，避免大表连接后聚合 #

当前的逻辑是先自连接再聚合，会产生大量中间结果集。你可以先分别计算h1和h2的聚合值，再进行连接，这会显著降低计算开销：

WITH h1_agg AS (
    SELECT 
        Date, 
        Currency, 
        Account, 
        SecID, 
        MaturityDate, 
        SUM(Size) AS Size1
    FROM htable
    WHERE AssetClass = 'FX' 
      AND TickerSuffix <> Currency
    GROUP BY Date, Currency, Account, SecID, MaturityDate
    HAVING Size1 <> 0
),
h2_agg AS (
    SELECT 
        Date, 
        Currency, 
        SecID, 
        SUM(Size) AS Size2
    FROM htable
    WHERE AssetClass = 'FX' 
      AND TickerSuffix = Currency
    GROUP BY Date, Currency, SecID
    HAVING Size2 <> 0
)
SELECT 
    h1.Date, 
    h1.Currency AS Currency1, 
    h2.Currency AS Currency2, 
    h1.Account, 
    h1.Size1, 
    h2.Size2, 
    h1.MaturityDate 
FROM h1_agg h1
JOIN h2_agg h2 
    ON h1.Date = h2.Date 
    AND h1.SecID = h2.SecID

4. 优化GROUP BY的索引匹配 #

你的GROUP BY列是h1.Date, h1.Currency, h2.Currency, h1.Account, h1.MaturityDate，但h2.Currency是来自连接表的列，无法通过索引直接优化分组。不过针对h1的聚合部分，调整联合索引为(AssetClass, Date, Currency, Account, MaturityDate, SecID)，可以让MySQL直接通过索引完成h1的分组计算，避免回表和排序。

5. 关闭GROUP BY的默认排序 #

MySQL默认会对GROUP BY的结果进行排序，如果你的业务不需要这个排序，可以添加ORDER BY NULL来跳过排序步骤，节省CPU和IO：

-- 在GROUP BY后添加
GROUP BY ...
ORDER BY NULL
HAVING ...

6. 针对视图的特殊优化 #

如果这个查询要用于视图，MySQL的普通视图是"逻辑视图"，每次查询视图都会重新执行底层SQL。如果数据量大且实时性要求不高，可以考虑：

• 使用MySQL 8.0+的物化视图：可以定期刷新数据，查询时直接读取物化结果。
• 手动创建汇总表：通过定时任务（比如事件调度器）定期执行优化后的查询，把结果写入汇总表，视图直接查询这个汇总表。

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