面试题：MySQL中如何针对UNION查询进行性能优化

索引使用

分析字段：对UNION涉及的表中，SELECT子句里用于过滤、连接等操作的字段添加索引。例如，假设有两个表table1和table2，结构如下：

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    category VARCHAR(20)
);
CREATE TABLE table2 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    category VARCHAR(20)
);

添加索引示例：如果查询是根据category字段进行过滤，可对两个表的category字段添加索引。

CREATE INDEX idx_table1_category ON table1(category);
CREATE INDEX idx_table2_category ON table2(category);

查询语句结构调整
- 减少字段选择：只选择需要的字段，避免使用SELECT *。例如原始查询：
```
SELECT * FROM table1
UNION
SELECT * FROM table2;
```
优化后：
```
SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;
```
- 消除重复数据方式调整：如果确定数据不会重复，使用UNION ALL代替UNION。UNION会对合并后的数据进行去重操作，这会增加性能开销。例如：
```
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;
```

子查询优化

避免嵌套子查询：如果UNION中有子查询，尽量将子查询展开或优化。例如，原始查询有如下子查询：

SELECT id, (SELECT COUNT(*) FROM related_table WHERE related_table.id = table1.id) AS count
FROM table1
UNION
SELECT id, (SELECT COUNT(*) FROM related_table WHERE related_table.id = table2.id) AS count
FROM table2;

优化方式可以通过连接操作来代替子查询：

SELECT table1.id, COUNT(related_table.id) AS count
FROM table1
LEFT JOIN related_table ON table1.id = related_table.id
GROUP BY table1.id
UNION
SELECT table2.id, COUNT(related_table.id) AS count
FROM table2
LEFT JOIN related_table ON table2.id = related_table.id
GROUP BY table2.id;

数据库配置优化
- 调整内存参数：根据数据库服务器的硬件资源，合理调整与查询性能相关的内存参数，如缓冲池大小等。例如在MySQL中，可以调整innodb_buffer_pool_size参数，增加其值可以提高数据缓存能力，减少磁盘I/O，从而提升查询性能。具体调整数值需要根据服务器内存大小和业务负载情况进行测试和评估。

分区表使用（如果适用）

分析数据特点：如果表数据量非常大，可以考虑对UNION涉及的表进行分区。例如，假设table1和table2按日期进行分区。假设表中有一个date字段记录数据日期。
创建分区表示例：在MySQL中创建按日期范围分区的表。

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    date DATE
)
PARTITION BY RANGE (YEAR(date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022)
);
CREATE TABLE table2 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    date DATE
)
PARTITION BY RANGE (YEAR(date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022)
);

这样在UNION查询时，如果有日期相关的过滤条件，数据库可以只查询相关分区的数据，提高查询性能。例如：

SELECT * FROM table1 WHERE date >= '2021 - 01 - 01'
UNION
SELECT * FROM table2 WHERE date >= '2021 - 01 - 01';

数据库可以直接定位到2021及之后分区的数据，而不需要全表扫描。

星途面试题库

面试题：MySQL中如何针对UNION查询进行性能优化

知识考点

面试题答案