如何解决MySQL SELECT UNION查询中的Using temporary、Using filesort问题

嘿，我来帮你搞定这个UNION查询里的临时表和文件排序问题！

首先得搞明白为啥会出现这俩情况：

• Using temporary：UNION默认会自动去重，MySQL得创建临时表来合并两个子查询的结果，然后剔除重复数据，这就触发了临时表的使用。
• Using filesort：要么是去重过程中需要排序来识别重复项，要么是你的查询隐含了排序需求，MySQL不得不通过文件排序来处理结果。

接下来给你几个实用的解决办法：

1. 用UNION ALL替代UNION（优先考虑） #

如果你的业务场景允许不做去重（或者两个子查询的结果本来就没有重复数据），直接把UNION改成UNION ALL就行。因为UNION ALL只是简单合并结果，不会去重，也就不需要临时表来处理重复项，自然能避免Using temporary，大部分情况下也能消除Using filesort。

修改后的查询示例（补全你没写完的部分）：

SELECT * FROM (
    SELECT a.id AS id, a.user_id AS user_id, aa.power * a.amount AS total_power, a.group_number AS group_number, a.amount AS total_amount, aa.type AS TYPE 
    FROM units a 
    JOIN unit_diagrams aa ON a.diagram_id = aa.id 
    WHERE a.group_id = 0 AND a.current_x = 1 AND a.current_y = 1 AND a.movement_start_time = 0 
    UNION ALL -- 替换成UNION ALL
    SELECT MIN(b.id) AS id, MIN(b.user_id) AS user_id, SUM(bb.power * b.amount) AS total_power, MAX(b.group_number) AS group_number, SUM(b.amount) AS total_amount, bb.type AS TYPE
    FROM units b 
    JOIN unit_diagrams bb ON b.diagram_id = bb.id 
    -- 这里补充你的WHERE条件和GROUP BY（因为用到了聚合函数）
) AS combined_result
-- 若需要排序，显式添加ORDER BY，比如 ORDER BY total_power DESC

2. 优化子查询的索引 #

如果必须用UNION去重，那得尽量减少临时表的数据量，优化子查询的执行效率：

• 对第一个子查询：给units表的group_id、current_x、current_y、movement_start_time建联合索引，让过滤更快；同时确保units.diagram_id和unit_diagrams.id有索引（主键默认就是索引，没问题）。
• 对第二个子查询（用到了聚合函数）：如果有GROUP BY，给分组字段建索引；如果聚合计算的字段能被索引覆盖（比如联合索引包含分组字段和聚合用到的字段），能进一步提升效率。

3. 明确排序需求，避免隐式排序 #

如果业务需要结果有序，别依赖MySQL的隐式排序，直接在最外层或者子查询里显式写ORDER BY，并且确保排序字段有索引。这样MySQL可以利用索引排序，避免文件排序的开销。就算必须用UNION去重，让子查询结果先有序，也能降低合并时的排序成本。

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