面试题：复杂场景下MySQL UNION查询的优化及案例分析

性能瓶颈点分析

全表扫描：由于涉及数据量较大，在 UNION 查询时，数据库可能对 orders 表和 returns 表进行全表扫描，这会导致性能低下。
排序开销：按日期排序需要将所有查询结果加载到内存中进行排序，如果数据量超出内存容量，会使用磁盘临时文件，极大影响性能。
UNION 操作本身开销：UNION 操作需要去除重复行，这也会增加额外的计算开销。

优化方案

添加索引：在 orders 表的 order_date 字段和 returns 表的 return_date 字段上添加索引。这可以加快按日期排序的速度，因为索引可以直接定位到符合条件的数据行，避免全表扫描。
```
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_returns_return_date ON returns(return_date);
```
使用 UNION ALL 代替 UNION：如果业务允许存在重复行，UNION ALL 不会进行去重操作，相比 UNION 会有更好的性能。
分批次查询与合并：可以先分别对 orders 表和 returns 表按日期范围进行分批次查询，然后再将这些小批次的结果合并。这样每次查询的数据量减少，减少内存和磁盘的压力。

优化后的 SQL 示例

使用 UNION ALL 的示例：

SELECT order_id AS id, user_id, order_date AS date, total_amount AS amount
FROM orders
UNION ALL
SELECT return_id AS id, user_id, return_date AS date, -return_amount AS amount
FROM returns
ORDER BY date;

分批次查询示例（假设按月份分批次，以 MySQL 为例）：

-- 先获取 orders 表按月份分批次的数据
SELECT order_id AS id, user_id, order_date AS date, total_amount AS amount
FROM orders
WHERE order_date BETWEEN '2023 - 01 - 01' AND '2023 - 01 - 31'
UNION ALL
SELECT order_id AS id, user_id, order_date AS date, total_amount AS amount
FROM orders
WHERE order_date BETWEEN '2023 - 02 - 01' AND '2023 - 02 - 28'
-- 依次类推其他月份

-- 再获取 returns 表按月份分批次的数据，并与上面结果合并
UNION ALL
SELECT return_id AS id, user_id, return_date AS date, -return_amount AS amount
FROM returns
WHERE return_date BETWEEN '2023 - 01 - 01' AND '2023 - 01 - 31'
UNION ALL
SELECT return_id AS id, user_id, return_date AS date, -return_amount AS amount
FROM returns
WHERE return_date BETWEEN '2023 - 02 - 01' AND '2023 - 02 - 28'
-- 依次类推其他月份

ORDER BY date;

UNION 查询优化通用思路

减少数据量：
- 通过合理的条件筛选，只查询必要的数据，避免全表扫描。
- 采用分批次查询的方式，将大数据量分割成小的数据块进行处理。
优化排序：
- 对用于排序的字段添加索引，加速排序过程。
- 如果可能，在子查询中进行部分排序，而不是对最终合并的大结果集排序。
避免不必要操作：
- 能用 UNION ALL 就不用 UNION，减少去重带来的性能开销。
- 检查查询中是否存在其他不必要的计算或转换操作，尽量简化查询逻辑。

星途面试题库

面试题：复杂场景下MySQL UNION查询的优化及案例分析

知识考点

面试题答案

性能瓶颈点分析

优化方案

优化后的 SQL 示例

UNION 查询优化通用思路