MySQL UNION查询性能优化全攻略：避开常见陷阱，提升查询效率
MySQL UNION查询优化核心要点：UNION操作可能导致索引失效，而UNION ALL则更可能利用索引。关键在于为每个子查询独立建立合适的组合索引，并将ORDER BY和LIMIT子句置于各查询内部，从而有效避免全表扫描与临时表磁盘写入。
UNION 可能导致索引失效，UNION ALL 才是索引利用的关键
一个普遍的认知误区是：只要子查询使用了索引，UNION 的整体性能就有保障。实际情况恰好相反。MySQL 在执行标准的 UNION 操作时，默认会进行结果集去重，这相当于在内部自动执行了 DISTINCT 操作。为了实现去重，数据库引擎必须将所有子查询的结果集收集到一个临时表中，然后进行排序和去重处理——这个过程使得原有数据表上的索引几乎无法发挥作用。
相比之下，UNION ALL 则直接合并结果，无需去重，每个独立的 SELECT 语句都可以充分利用自身的索引访问路径。因此，优化 UNION 查询的首要步骤是：
- 评估业务逻辑是否允许重复记录？ 例如，在合并访问日志、聚合多个互斥分表数据时，如果结果集不要求绝对唯一性，应优先选用
UNION ALL。 - 如何判断索引是否失效？使用
EXPLAIN分析执行计划时，若出现Using temporary; Using filesort提示，或者type列从高效的ref、range降级为ALL（全表扫描）或index（全索引扫描），这通常是UNION的去重机制导致的性能瓶颈。
为每个子查询独立设计高效索引，切勿依赖全局索引
不要期望 MySQL 能为整个 UNION 语句自动生成一个全局最优的索引方案。数据库会对每个 SELECT 分支进行独立的优化。这意味着，你必须针对每一个子查询的特定过滤条件和排序需求，单独构建最合适的组合索引。
通过一个实例来理解：
- 假设有两个查询：
SELECT id, name FROM t1 WHERE status=1 ORDER BY created_at LIMIT 10和SELECT id, name FROM t2 WHERE type='log' ORDER BY updated_at LIMIT 10。 - 最优的索引策略是：为表
t1创建(status, created_at, id, name)的复合索引，为表t2创建(type, updated_at, id, name)的复合索引。这样，查询可以直接通过索引完成条件过滤、结果排序和字段覆盖，实现最高效的索引覆盖扫描。 - 一个常见的错误是仅为
id, name建立索引。这种做法是无效的，因为WHERE子句中的过滤字段（如status,type）未包含在索引的前导列，查询引擎仍需回表查询主数据，性能等同于部分全表扫描。 - 此外，若子查询中使用了前导通配符的
LIKE '%keyword'或对字段应用了函数（例如DATE(created_at)），相关字段上的索引将大概率失效。遇到此类情况，应考虑调整查询逻辑或使用表达式索引（MySQL 8.0+）。
ORDER BY 与 LIMIT 必须内置于各子查询，禁止仅在外层使用
这是决定 UNION 查询性能的关键点。MySQL 不支持在 UNION 语句的最后仅使用一个全局的 ORDER BY ... LIMIT 来获取最终排序结果。如果这样写，引擎会先合并所有子查询的全部数据行，然后对这个庞大的临时结果集进行排序和截取，极易引发内存溢出与速度急剧下降。
正确的优化策略是“分阶段结果集裁剪”：
- 错误写法：
(SELECT ... UNION ALL SELECT ...) ORDER BY score DESC LIMIT 20。此方式先合并全部数据再排序，数据量大时性能极差。 - 正确写法：
(SELECT ... ORDER BY score DESC LIMIT 20) UNION ALL (SELECT ... ORDER BY score DESC LIMIT 20) ORDER BY score DESC LIMIT 20。请注意，先在每个子查询内部进行排序并限制返回行数，极大减少了需要合并的数据量，最后仅对合并后的小规模结果进行最终排序。 - 需要特别注意，最外层的
ORDER BY不可省略，因为UNION ALL不保证合并后的结果顺序。但由于内层已通过LIMIT大幅缩减了数据量，外层排序的负担已显著减轻。 - 如果参与
UNION的各子查询数据量级悬殊（例如一个小表联查一个大表），可以尝试根据比例动态调整大表分支的LIMIT值，防止其成为整个查询的性能短板。
警惕临时表引擎与排序缓冲区成为性能瓶颈
即便正确使用了 UNION ALL 并配备了完善的索引，如果查询返回的结果集过大，或者结果中包含了 TEXT、BLOB 等大型对象字段，MySQL 仍可能因内存不足而将中间结果写入磁盘临时表（在执行计划中表现为 Using temporary; Using filesort 并伴有 On disk 提示）。此时，以下两个服务器参数至关重要：tmp_table_size 和 max_heap_table_size。
- 首先，使用
SHOW VARIABLES LIKE 'tmp_table_size';查看当前配置。默认值（如16MB）对于处理稍复杂的联合查询往往不足。 - 一旦临时表的大小超过
tmp_table_size和max_heap_table_size中的较小值，MySQL 5.7 会将其转换为 MyISAM 引擎表存入磁盘，而 MySQL 8.0+ 则会转换为 InnoDB 引擎表，随之产生大量的磁盘 I/O 开销。 - 建立监控机制。定期执行
SHOW STATUS LIKE 'Created_tmp%';，重点关注Created_tmp_disk_tables这个状态变量是否持续增长，它能直接反映临时表落盘的频繁程度。 - 最后，始终坚持“按需查询”原则：杜绝使用
SELECT *，只查询业务必需的列；同时，合理定义字段长度，在满足业务前提下，将VARCHAR(500)改为VARCHAR(100)也能有效降低临时表的内存占用。
总而言之，在优化 UNION 查询时，仅仅创建索引是远远不够的。你必须像数据库调优专家一样，深入剖析每一个子查询的执行路径，任何细微的疏忽都可能导致一个本应高效的查询退化为“全表扫描集合体”，严重影响系统性能。
