MySQL UNION查询优化与案例分析

MySQL UNION查询基础

在MySQL中，UNION操作符用于合并两个或多个SELECT语句的结果集。它会去除重复的行，除非使用UNION ALL，UNION ALL会保留所有行，包括重复行。

基本语法

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

上述语句将table1和table2中column1和column2的结果集合并。

数据类型一致性

参与UNION操作的各个SELECT语句的列数必须相同，并且对应列的数据类型必须兼容。例如：

SELECT id, name FROM users
UNION
SELECT product_id, product_name FROM products;

这里users表的id和products表的product_id数据类型应兼容，name和product_name数据类型也应兼容。

UNION查询执行原理

MySQL在处理UNION查询时，会按以下步骤进行：

执行各个SELECT语句：MySQL分别执行UNION操作符两侧的SELECT语句，生成各自的结果集。
合并结果集：如果是UNION（非ALL），MySQL会对结果集进行去重操作；如果是UNION ALL，则直接将结果集合并。

示例说明

假设有两个表orders1和orders2，结构如下：

CREATE TABLE orders1 (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE orders2 (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

执行以下查询：

SELECT order_id, order_date, amount FROM orders1
UNION
SELECT order_id, order_date, amount FROM orders2;

MySQL首先分别从orders1和orders2中获取数据，然后对合并后的结果进行去重（如果是UNION）。

UNION查询优化策略

合理使用索引

索引对于提升UNION查询性能至关重要。在参与UNION的SELECT语句所涉及的表上，确保对查询条件中的列建立索引。例如，对于以下查询：

SELECT customer_id, order_date FROM orders WHERE order_amount > 100
UNION
SELECT customer_id, order_date FROM historical_orders WHERE order_amount > 100;

在orders表和historical_orders表的order_amount列上建立索引可以显著提升查询性能。

CREATE INDEX idx_order_amount ON orders(order_amount);
CREATE INDEX idx_historical_order_amount ON historical_orders(order_amount);

避免使用不必要的列

只选择需要的列，减少数据传输和处理量。例如，原本查询：

SELECT * FROM products WHERE category = 'electronics'
UNION
SELECT * FROM products_backup WHERE category = 'electronics';

改为：

SELECT product_id, product_name, price FROM products WHERE category = 'electronics'
UNION
SELECT product_id, product_name, price FROM products_backup WHERE category = 'electronics';

这样可以减少数据量，提高查询速度。

优化子查询

如果UNION中的SELECT语句包含子查询，优化子查询对整体性能提升有帮助。例如：

SELECT product_id, product_name FROM products WHERE product_id IN (
    SELECT related_product_id FROM related_products WHERE main_product_id = 1
)
UNION
SELECT product_id, product_name FROM discontinued_products WHERE product_id IN (
    SELECT related_product_id FROM related_products WHERE main_product_id = 1
);

可以对related_products表的main_product_id和related_product_id列建立索引，优化子查询性能。

CREATE INDEX idx_main_product_id ON related_products(main_product_id);
CREATE INDEX idx_related_product_id ON related_products(related_product_id);

考虑使用临时表

在某些复杂的UNION查询中，使用临时表可以优化性能。例如，当UNION涉及多个复杂的子查询或计算时，可以先将中间结果存储在临时表中，然后再进行UNION操作。

CREATE TEMPORARY TABLE temp_orders1 AS
SELECT order_id, order_date, amount FROM orders WHERE order_date > '2023 - 01 - 01';

CREATE TEMPORARY TABLE temp_orders2 AS
SELECT order_id, order_date, amount FROM historical_orders WHERE order_date > '2023 - 01 - 01';

SELECT * FROM temp_orders1
UNION
SELECT * FROM temp_orders2;

这样可以减少重复计算，提升整体查询效率。

案例分析

案例一：简单数据合并

假设有两个部门的员工表department1_employees和department2_employees，结构相同：

CREATE TABLE department1_employees (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE department2_employees (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

需求是获取两个部门所有员工的信息，且去除重复（假设员工不会同时在两个部门）。

SELECT employee_id, employee_name, salary FROM department1_employees
UNION
SELECT employee_id, employee_name, salary FROM department2_employees;

优化建议：在employee_id列建立索引，因为employee_id可能在后续查询中作为过滤条件，同时在UNION操作中用于去重。

CREATE INDEX idx_employee_id1 ON department1_employees(employee_id);
CREATE INDEX idx_employee_id2 ON department2_employees(employee_id);

案例二：复杂数据合并与过滤

假设有orders表记录当前订单，historical_orders表记录历史订单。需求是获取金额大于100且在2023年的所有订单信息，包括当前订单和历史订单。

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE historical_orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

初始查询：

SELECT order_id, order_date, amount FROM orders
WHERE amount > 100 AND YEAR(order_date) = 2023
UNION
SELECT order_id, order_date, amount FROM historical_orders
WHERE amount > 100 AND YEAR(order_date) = 2023;

优化措施：

在orders表和historical_orders表的amount和order_date列建立复合索引。

CREATE INDEX idx_amount_date1 ON orders(amount, order_date);
CREATE INDEX idx_amount_date2 ON historical_orders(amount, order_date);

考虑到查询条件中YEAR(order_date)函数的使用，可能会导致索引失效。可以修改查询为：

SELECT order_id, order_date, amount FROM orders
WHERE amount > 100 AND order_date >= '2023 - 01 - 01' AND order_date < '2024 - 01 - 01'
UNION
SELECT order_id, order_date, amount FROM historical_orders
WHERE amount > 100 AND order_date >= '2023 - 01 - 01' AND order_date < '2024 - 01 - 01';

这样可以利用索引，提升查询性能。

案例三：UNION ALL与UNION的性能对比

假设有一个products表和products_backup表，结构如下：

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

CREATE TABLE products_backup (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

执行以下两个查询，对比性能：

使用UNION：

SELECT product_id, product_name, price FROM products
UNION
SELECT product_id, product_name, price FROM products_backup;

使用UNION ALL：

SELECT product_id, product_name, price FROM products
UNION ALL
SELECT product_id, product_name, price FROM products_backup;

在数据量较大且重复数据较少的情况下，UNION ALL通常会比UNION快，因为UNION需要额外的去重操作。可以通过EXPLAIN关键字查看执行计划来进一步分析性能差异。

EXPLAIN SELECT product_id, product_name, price FROM products
UNION
SELECT product_id, product_name, price FROM products_backup;

EXPLAIN SELECT product_id, product_name, price FROM products
UNION ALL
SELECT product_id, product_name, price FROM products_backup;

从执行计划中可以看到UNION操作的额外开销，如排序操作（如果需要去重）。

案例四：使用临时表优化UNION查询

假设有一个复杂的业务场景，需要从多个表中获取数据并合并。例如，从orders表获取已完成订单，从pending_orders表获取待处理订单，从cancelled_orders表获取取消订单，并且需要对这些订单数据进行一些计算和过滤。

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    status VARCHAR(20)
);

CREATE TABLE pending_orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE cancelled_orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

初始查询：

SELECT order_id, order_date, amount, 'completed' AS status FROM orders WHERE status = 'completed'
UNION
SELECT order_id, order_date, amount, 'pending' AS status FROM pending_orders
UNION
SELECT order_id, order_date, amount, 'cancelled' AS status FROM cancelled_orders;

优化方法：使用临时表。

CREATE TEMPORARY TABLE temp_completed_orders AS
SELECT order_id, order_date, amount, 'completed' AS status FROM orders WHERE status = 'completed';

CREATE TEMPORARY TABLE temp_pending_orders AS
SELECT order_id, order_date, amount, 'pending' AS status FROM pending_orders;

CREATE TEMPORARY TABLE temp_cancelled_orders AS
SELECT order_id, order_date, amount, 'cancelled' AS status FROM cancelled_orders;

SELECT * FROM temp_completed_orders
UNION
SELECT * FROM temp_pending_orders
UNION
SELECT * FROM temp_cancelled_orders;

这样可以将复杂的查询分解为多个简单的查询，先将结果存储在临时表中，再进行UNION操作，减少重复计算，提升性能。同时，对临时表中涉及过滤条件的列建立索引，如在temp_completed_orders表的status列建立索引。

CREATE INDEX idx_status ON temp_completed_orders(status);

性能分析工具

EXPLAIN关键字

EXPLAIN关键字可以帮助我们分析UNION查询的执行计划。例如，对于以下查询：

EXPLAIN SELECT product_id, product_name FROM products WHERE category = 'clothes'
UNION
SELECT product_id, product_name FROM products_backup WHERE category = 'clothes';

EXPLAIN的输出结果会显示查询的各个阶段，包括表的访问类型（如ALL、index等）、是否使用索引、数据扫描的行数等信息。通过分析这些信息，可以找出性能瓶颈，如未使用索引或全表扫描等问题。

SHOW STATUS

SHOW STATUS命令可以获取MySQL服务器的各种状态信息，包括查询相关的统计数据。例如，通过查看Handler_read_rnd_next的值可以了解查询是否进行了大量的随机读取，如果该值过高，可能意味着查询没有充分利用索引，需要优化。

SHOW STATUS LIKE 'Handler_read_rnd_next';

在执行UNION查询前后查看这些状态值，可以评估查询对系统资源的影响以及性能改进的效果。

Profiling

MySQL的查询剖析功能可以详细分析查询执行的各个阶段所花费的时间。首先需要开启查询剖析功能：

SET profiling = 1;

然后执行UNION查询：

SELECT order_id, order_date FROM orders WHERE amount > 50
UNION
SELECT order_id, order_date FROM historical_orders WHERE amount > 50;

查询执行完毕后，可以查看剖析结果：

SHOW PROFILES;

这将显示所有已执行查询的剖析信息，找到对应的UNION查询，然后查看其详细剖析：

SHOW PROFILE FOR QUERY query_id;

其中query_id是SHOW PROFILES结果中对应的查询ID。通过剖析结果，可以确定查询的哪些部分花费时间较长，从而针对性地进行优化。

常见问题及解决方法

数据类型不兼容问题

当参与UNION的SELECT语句对应列数据类型不兼容时，会出现错误。例如：

SELECT product_id, product_name FROM products
UNION
SELECT order_id, order_date FROM orders;

这里products表的product_id和orders表的order_id可能数据类型相同，但product_name（假设为VARCHAR）和order_date（DATE类型）数据类型不兼容。解决方法：确保对应列数据类型兼容，或者在SELECT语句中进行类型转换。例如：

SELECT product_id, product_name FROM products
UNION
SELECT order_id, CAST(order_date AS CHAR) AS order_date_str FROM orders;

这样将order_date转换为字符串类型，与product_name数据类型兼容。

索引失效问题

在UNION查询中，由于查询条件的书写不当，可能导致索引失效。例如：

SELECT product_id, product_name FROM products WHERE UPPER(category) = 'ELECTRONICS'
UNION
SELECT product_id, product_name FROM products_backup WHERE UPPER(category) = 'ELECTRONICS';

这里UPPER(category)函数的使用会导致category列上的索引失效。解决方法：修改查询条件，避免在索引列上使用函数。例如：

SELECT product_id, product_name FROM products WHERE category = 'electronics'
UNION
SELECT product_id, product_name FROM products_backup WHERE category = 'electronics';

这样可以利用category列上的索引，提升查询性能。

性能问题与大数据量

在处理大数据量时，UNION查询性能可能会受到严重影响。例如，当UNION涉及多个大表，且未进行优化时，查询可能会非常缓慢。解决方法：综合运用前面提到的优化策略，如合理使用索引、避免不必要的列选择、使用临时表等。同时，可以考虑对大表进行分区，将数据分散存储，减少单个查询的数据处理量。例如，按日期对orders表进行分区，在UNION查询时只涉及相关分区的数据，提高查询效率。

不同版本MySQL的UNION特性差异

MySQL 5.x系列

在MySQL 5.x系列中，UNION操作的执行性能和优化机制相对较为基础。对于复杂的UNION查询，尤其是涉及子查询和多表关联时，优化效果可能不如高版本。例如，在处理大数据量的UNION查询时，去重操作（对于UNION而非UNION ALL）可能会消耗较多的系统资源。

MySQL 8.x系列

MySQL 8.x在UNION查询优化方面有了显著提升。它引入了更智能的查询优化器，能够更好地处理复杂的UNION查询结构。例如，在处理包含子查询的UNION时，优化器能够更有效地选择执行计划，减少不必要的中间结果集生成。同时，MySQL 8.x在索引使用和数据去重算法上也进行了改进，提升了UNION查询的整体性能。

版本升级对UNION查询优化的影响

当从较低版本升级到MySQL 8.x时，部分原本在低版本中性能不佳的UNION查询可能会因为优化器的改进而获得性能提升。然而，也需要注意兼容性问题。例如，某些在低版本中依赖特定执行方式编写的查询，在高版本中可能因为优化器行为的改变而出现性能下降或结果不一致的情况。因此，在升级版本后，需要对关键的UNION查询进行性能测试和验证，确保业务不受影响。同时，可以利用高版本的新特性，如更好的索引优化和查询执行计划调整，进一步优化UNION查询。

与其他数据库的UNION对比

与Oracle的UNION对比

语法差异：Oracle的UNION语法与MySQL基本相同，但在一些细节上有差异。例如，Oracle允许在UNION操作中使用ORDER BY子句，但必须在整个UNION语句的最后，而MySQL在每个SELECT语句中都可以使用ORDER BY，不过如果要对合并后的结果排序，也需要在整个UNION语句最后使用ORDER BY。
性能优化：Oracle在处理大数据量的UNION时，其优化器更倾向于基于成本的优化策略，会考虑更多的系统参数和统计信息。MySQL则相对更注重索引的使用和简单的查询结构优化。在一些复杂的UNION场景下，Oracle可能通过其强大的优化器获得更好的性能，但MySQL通过合理的索引设计和简单查询结构也能实现高效的UNION操作。

与SQL Server的UNION对比

语法和功能：SQL Server的UNION语法与MySQL类似，但在一些功能上有所不同。例如，SQL Server支持在UNION操作中使用TOP关键字来限制返回的行数，而MySQL在UNION中使用LIMIT关键字实现类似功能。
性能特点：SQL Server在处理UNION查询时，对于内存和磁盘I/O的管理有其独特方式。它会根据查询的复杂度和数据量动态分配资源。MySQL则更依赖于文件系统和存储引擎的特性来管理数据读写。在某些场景下，如对内存使用要求较高的UNION查询，SQL Server可能表现更优，而MySQL在简单查询场景下通过其轻量级架构能快速响应。

通过对不同数据库UNION操作的对比，可以在选择数据库和编写UNION查询时，根据具体业务需求和数据特点做出更合适的决策。同时，在进行数据库迁移时，也能更好地调整UNION查询以适应新的数据库环境。

总结UNION查询优化要点

索引使用：对UNION查询涉及的表，在查询条件中的列上建立适当的索引，以减少数据扫描量。
列选择：只选择必要的列，避免传输和处理不必要的数据。
子查询优化：优化UNION中SELECT语句内的子查询，确保子查询高效执行。
临时表运用：在复杂UNION查询中，合理使用临时表存储中间结果，减少重复计算。
数据类型兼容性：确保参与UNION的SELECT语句对应列数据类型兼容，避免类型转换导致的性能问题。
避免函数使用：在索引列上避免使用函数，防止索引失效。
对比不同版本和数据库：了解不同版本MySQL以及其他数据库在UNION操作上的特性差异，根据实际情况优化查询。

通过综合运用这些优化要点，可以显著提升MySQL中UNION查询的性能，满足各种业务场景下对数据合并和查询的需求。同时，持续关注数据库技术的发展，不断学习和应用新的优化方法，也是确保数据库高效运行的关键。在实际项目中，需要根据具体的数据量、查询复杂度和业务需求，灵活选择和调整优化策略，以达到最佳的查询性能。