MySQL查询运行超30分钟占用5GB临时存储报错无剩余空间优化咨询

首先明确原始查询的核心问题：

• 仅按a.login分组，但SELECT子句中包含a.Time、a.Symbol、a.Volume、a.price、b.currency、b.group等多个非聚合、非分组字段，严格SQL模式下本身不合法，执行时会返回随机值，同时会生成大量无效临时数据
• 执行逻辑为先关联全量符合条件的两张表数据，再做分组聚合，中间结果集行数等于符合条件的deals表行数乘以每个login对应的daily表行数，数据量爆炸导致临时空间占用过高
• 缺少匹配查询逻辑的覆盖索引，查询过程中频繁回表读数据，进一步降低执行效率

对应的优化方案如下： #

1. 修正SQL逻辑，匹配实际业务需求 #

场景1：需要按login维度做聚合统计 #

先对deals表做聚合，减少关联数据量后再关联daily表，修正后的SQL如下：

SELECT 
    a.login, 
    b.currency,
    b.`group`, 
    a.total_commission
FROM (
    SELECT 
        login, 
        SUM(commission) AS total_commission
    FROM deals 
    WHERE `Action` IN (0,1)
    GROUP BY login
) AS a
INNER JOIN daily AS b ON a.login = b.login;

如果确实需要每个login的Time、Symbol、Volume、Price等字段，需要补充对应的聚合规则（比如取最新时间、求和交易量等），不能直接返回未聚合的字段。

场景2：需要返回每笔deals对应的daily信息 #

不需要加GROUP BY，直接关联即可，修正后的SQL如下：

SELECT 
    a.login, 
    b.currency,
    b.`group`, 
    a.Time,
    a.Symbol, 
    a.commission, 
    a.Volume/10000 AS volume,
    a.price
FROM deals AS a
INNER JOIN daily AS b ON a.login = b.login 
WHERE a.`Action` IN (0,1);

2. 新增覆盖索引，避免回表查询 #

给deals表新增覆盖索引： #

CREATE INDEX idx_action_login_cover ON deals(`Action`, `Login`, `Time`, `Symbol`, `Commission`, `Volume`, `Price`);

该索引可以直接覆盖WHERE过滤、关联条件以及SELECT需要的所有字段，查询时不需要回表读主键数据。

给daily表新增覆盖索引： #

CREATE INDEX idx_login_currency_group ON daily(`Login`, `Currency`, `Group`);

daily表原主键是(Datetime,Login)，关联字段Login位于联合主键第二位，无法直接命中索引，新增索引后关联时可以直接命中，且覆盖需要返回的Currency、Group字段，不需要回表。 #

3. 辅助配置优化（可选） #

如果磁盘空间充足但临时表写入频繁，可以调整MySQL配置：

• 将tmpdir参数指向剩余空间更大的磁盘分区
• 适当调大sort_buffer_size、join_buffer_size参数，减少磁盘临时表的使用概率

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