MySQL 8查询调优求助：临时表磁盘已满问题

1. 重构SQL，缩小临时表数据量 #

原SQL在MySQL 8严格模式下存在逻辑瑕疵（SELECT非聚合列未纳入GROUP BY），且会先关联两张表再分组，生成大量中间数据导致临时表膨胀。建议先在tranlog内完成聚合，再关联agent表过滤：

SELECT 
    t.sender, 
    a.fullName, 
    a.phoneNumber, 
    a.addressState, 
    a.businessName, 
    a.bvn, 
    t.max_date
FROM (
    -- 先聚合tranlog，仅保留每个sender的最大date
    SELECT sender, MAX(date) AS max_date
    FROM tranlog
    WHERE captureDate < '2022-03-01'
    GROUP BY sender
) t
INNER JOIN agent a 
    ON t.sender = a.realId
WHERE a.active = 'Y' AND a.thirdparty = 0;

这种写法会先将tranlog结果集压缩到每个sender一条记录，再关联agent，大幅降低中间临时表的体积。

2. 优化索引，避免全表扫描与不必要的临时表 #

给tranlog添加复合覆盖索引 #

当前tranlog的captureDate是单列索引，查询时需要回表获取sender和date字段，建议创建覆盖索引：

CREATE INDEX idx_tranlog_capture_sender_date ON tranlog(captureDate, sender, date);

该索引包含WHERE过滤条件、GROUP BY字段和聚合所需的date，查询时直接走索引无需访问主表，同时GROUP BY sender可利用索引排序，避免生成临时表做排序操作。

给agent添加复合覆盖索引 #

agent的过滤条件是active='Y'和thirdparty=0，关联字段为realId，同时需要返回多个业务字段，创建覆盖索引：

CREATE INDEX idx_agent_active_thirdparty_realId ON agent(active, thirdparty, realId, fullName, phoneNumber, addressState, businessName, bvn);

关联查询时直接从索引中获取所有所需字段，无需回表查询主表，减少数据读取量。

3. 调整临时表相关配置 #

• 确认参数生效：执行SHOW VARIABLES LIKE '%tmp_table_size%';和SHOW VARIABLES LIKE '%max_heap_table_size%';，确保修改后的3G值已生效（Windows下修改my.ini后需重启MySQL服务）。
• 更换临时表目录：将tmpdir指向剩余空间充足的磁盘，例如在my.ini中设置tmpdir=D:/MySQL_Temp，注意给该目录分配MySQL服务的读写权限。
• 尝试内存存储临时表：设置internal_tmp_disk_storage_engine=MEMORY，但仅适用于聚合结果能被内存容纳的场景，否则会触发内存溢出。

4. 分批处理超大数据集 #

从tranlog的自增ID来看，数据量接近5000万，一次性聚合压力过大时可分批处理：

• 按captureDate分阶段查询，例如先处理2020年数据，再处理2021年数据，最后合并结果。
• 按sender的字典序分段，例如WHERE sender BETWEEN 'A' AND 'M'，分批聚合后再合并。

5. 用执行计划排查瓶颈 #

执行EXPLAIN FORMAT=JSON查看查询计划，重点关注：

• type列是否为range或ref，若为ALL说明走了全表扫描，索引未生效。
• 是否存在Using temporary或Using filesort标记，这两个标记表示仍在生成临时表或磁盘排序，需进一步优化索引。

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