mysql如何优化UNION查询_mysql union all与索引配合

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 查询时，仅仅创建索引是远远不够的。你必须像数据库调优专家一样，深入剖析每一个子查询的执行路径，任何细微的疏忽都可能导致一个本应高效的查询退化为“全表扫描集合体”，严重影响系统性能。