如何优化MySQL中多UNION ALL视图的查询性能

你当前写法最明显的冗余问题是每个子查询都单独加了DISTINCT：UNION ALL本身不会做全局去重，每个子查询单独排序去重会生成多次临时表，平白增加数倍计算量，属于完全可以避免的无效开销。

一、SQL语句层面的基础优化 #

• 移除所有子查询内部的DISTINCT：如果业务要求最终结果无重复，仅在最外层对所有UNION ALL的结果做一次去重即可；如果不同源表本身存储的是互斥数据（比如按月份、业务线拆分的分表，数据不会跨表重复），直接删除所有DISTINCT即可，这一步通常能直接减少30%以上的执行时间。
• 严格指定查询列，禁止拉取冗余字段：不要为了方便写SELECT *，只返回业务实际需要的列，尤其不要带出TEXT/BLOB类大字段，减少临时表内存占用和IO传输量。
• 无去重需求时绝对不用UNION：UNION会默认对全量结果做去重排序，开销远高于UNION ALL，不需要全局去重的场景一律用UNION ALL。

二、子查询索引优化 #

给每个参与合并的表建覆盖索引，让单个子查询不需要回表就能直接返回结果：

• 索引字段顺序规则：WHERE条件中用到的等值判断列 -> WHERE条件中用到的范围判断列 -> 所有SELECT需要返回的列
• 示例：如果table1的过滤条件是is_valid = 1 AND create_time > '2024-01-01'，需要返回的列是user_id, order_id, amount，就建联合索引idx_t1_cv (is_valid, create_time, user_id, order_id, amount)，子查询可以直接从索引树拿到所有需要的数据，避免全表扫描和回表，单表查询性能可以提升5-10倍。

三、MySQL参数与视图使用优化 #

• 调大内存临时表阈值：UNION ALL执行过程中会生成临时表，默认配置下内存临时表上限通常只有16M/64M，超过阈值就会写入磁盘，磁盘IO速度比内存慢100倍以上。可以根据服务器内存情况调大两个参数：

-- 临时表内存上限设为256M，内存充足可以调到512M/1G
SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;

• 不要强行修改视图算法：包含UNION ALL的视图不支持MERGE算法，不用修改默认的ALGORITHM = UNDEFINED配置，MySQL会自动选择适配的TEMPTABLE算法，配合上面的临时表参数调优即可。
• 禁止无过滤条件查询视图：查询视图时必须加WHERE过滤条件（比如时间范围、业务线维度），不要直接执行SELECT * FROM my_view拉取全量数据，否则会触发所有子查询全量计算，数据量稍大就会出现连接超时。

四、大数据量场景的架构级优化 #

如果参与UNION ALL的表超过5张，单表数据量超过百万，SQL层面优化到顶依然性能不足时，用以下方案替代：

• 用分区表替换多份表：如果多张表是按时间、业务线等固定维度拆分的同结构表，直接合并为单张分区表，查询时MySQL会自动做分区剪枝，只扫描符合条件的分区，完全不需要写UNION ALL语句，性能提升最明显。
• 构建统一汇总表：如果视图用于高频查询、报表场景，不要做实时UNION ALL计算，写定时任务将多张表的符合条件的数据增量同步到一张结构统一的汇总表，视图直接查询汇总表即可，查询延迟可以降到毫秒级。

优化后基础写法示例 #

CREATE OR REPLACE
    ALGORITHM = UNDEFINED 
    DEFINER = usr 
    SQL SECURITY DEFINER
VIEW my_view AS
SELECT 
   column1, 
   column2, 
   column3
FROM 
   table1
WHERE 
   condition1
UNION ALL
SELECT 
   column1, 
   column2, 
   column3
FROM 
   table2
WHERE 
   condition2
UNION ALL
SELECT 
   column1, 
   column2, 
   column3
FROM 
   table3
WHERE 
   condition3;

-- 确实需要全局去重时，仅在最外层做一次去重即可
-- CREATE OR REPLACE
-- ...
-- VIEW my_view AS
-- SELECT DISTINCT * FROM (
-- 上面的UNION ALL语句
-- ) t;

排查时可以先用EXPLAIN查看每个子查询的执行计划，确认是否走了覆盖索引、有没有出现全表扫描、Using temporary（磁盘临时表）的提示，针对性调整即可。

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