性能优化（执行计划 / 慢查询 / 常见陷阱） · SQL 改写优化

OR 查询索引失效怎么办？UNION 改写策略？

What to do when an OR query doesn't use an index? The UNION rewrite strategy?

答案语言

考察要点

这道题面试官想看你是否深入理解 MySQL 的查询优化器如何处理 OR 条件，特别是在涉及多个不同索引列的场景。考察点包括：

索引失效场景：你是否知道 OR 在何种情况下会导致索引失效或性能下降。
Index Merge 优化：你是否了解 MySQL 5.0 之后引入的 Index Merge 机制，它是优化器自动应对 OR 场景的策略。
SQL 改写能力：你是否能主动、正确地将 OR 查询改写为性能更稳定、更可控的 UNION 或 UNION ALL 查询，并清楚两者的区别和适用场景。

核心概念

OR 查询的核心挑战在于，当 OR 连接的条件涉及到两个或多个独立的索引时，优化器难以选择一个“最优”索引。

假设我们有如下用户表和索引：

sql

 1CREATE TABLE users (
 2  id INT PRIMARY KEY AUTO_INCREMENT,
 3  name VARCHAR(100),
 4  age INT,
 5  city VARCHAR(100),
 6  register_time DATETIME,
 7  KEY idx_age (age),
 8  KEY idx_city (city)
 9);

我们来看这个查询：SELECT * FROM users WHERE age = 30 OR city = '北京';

1. 朴素 `OR` 查询与全表扫描

在早期 MySQL 版本（5.0 之前）或优化器认为代价更低时，它会放弃使用 idx_age 和 idx_city 两个独立索引，转而执行全表扫描（Full Table Scan）。因为它无法同时利用两个 B+ 树索引，扫描其中一个索引后，另一个条件仍然需要逐行判断，代价可能比直接扫全表还高。

2. Index Merge Union 优化

从 MySQL 5.0 开始，引入了 Index Merge 优化。对于上述 OR 查询，优化器可能采用 Index Merge Union 策略。

工作原理：

分别扫描：通过 idx_age 索引找到所有 age = 30 的主键 ID 集合（如：{1, 5, 10}）。
再次扫描：通过 idx_city 索引找到所有 city = '北京' 的主键 ID 集合（如：{5, 20, 30}）。
合并去重：将两个主键 ID 集合进行合并与去重，得到最终的主键 ID 集合 {1, 5, 10, 20, 30}。
回表查询：根据合并后的主键 ID，逐个回表（访问主键索引）获取完整的行数据。

执行计划（EXPLAIN）中的体现： type 字段会显示为 index_merge，Extra 字段会包含 Using union(idx_age, idx_city); Using where。

Index Merge 的问题：

合并成本：当两个集合都很大时，在内存中对主键 ID 进行合并和去重的成本不容忽视。
大量随机 I/O：如果合并后的主键 ID 集合非常大且不连续，回表查询会产生大量的随机 I/O，性能可能急剧下降，甚至不如全表扫描（顺序 I/O）。
优化器误判：优化器是基于成本估算的，它可能错误地选择了 Index Merge，而实际上全表扫描或 UNION 改写性能更佳。

3. `UNION` / `UNION ALL` 改写

为了避免 Index Merge 的不确定性，我们可以手动将 OR 查询改写为两个独立的查询，然后用 UNION 或 UNION ALL 连接起来。

UNION ALL：直接合并两个查询的结果集，不去重。
UNION：合并两个查询的结果集，并进行去重操作。去重会引入额外的排序或哈希操作，有性能开销。

改写示例：

sql

 1-- 使用 UNION ALL (性能更高，但可能产生重复行)
 2(SELECT id, name, age, city FROM users WHERE age = 30)
 3UNION ALL
 4(SELECT id, name, age, city FROM users WHERE city = '北京' AND age <> 30); -- 第二个查询增加条件避免重复
 5
 6-- 或者，如果业务允许重复或确定无重复
 7(SELECT id, name, age, city FROM users WHERE age = 30)
 8UNION ALL
 9(SELECT id, name, age, city FROM users WHERE city = '北京');
10
11-- 使用 UNION (结果与原 OR 查询一致，但有去重开销)
12(SELECT id, name, age, city FROM users WHERE age = 30)
13UNION
14(SELECT id, name, age, city FROM users WHERE city = '北京');

这种改写将一个复杂的查询拆分为两个简单的查询。每个子查询都可以明确、高效地使用各自的索引（第一个用 idx_age，第二个用 idx_city），性能表现更稳定、可预测。

多种解法

问题场景：查询 age = 30 或 city = '北京' 的用户。

原始查询

sql

 1SELECT id, name, age, city FROM users WHERE age = 30 OR city = '北京';

执行分析：MySQL 8.0 优化器大概率会选择 Index Merge Union。EXPLAIN 结果中 type 为 index_merge。在 age 和 city 两个条件返回的结果集都很大时，合并与回表的代价会很高。
适用性：表数据量小，或 OR 的两个条件选择性都极高（返回的行数很少）时，Index Merge 效率尚可。

解法 1：`UNION ALL` 改写（推荐，性能优先）

适用版本：所有 SQL 版本
SQL 实现：

sql

 1(SELECT id, name, age, city FROM users WHERE age = 30)
 2UNION ALL
 3(SELECT id, name, age, city FROM users WHERE city = '北京' AND age <> 30);

复杂度：
- 第一个查询： $O(\log N + K_1)$ ，其中 $K_1$ 是 age=30 的行数。
- 第二个查询： $O(\log N + K_2)$ ，其中 $K_2$ 是 city='北京' 的行数。
- 总体：两个索引扫描 + 结果集合并，无去重开销。性能非常稳定。
索引依赖：idx_age 和 idx_city。两个子查询分别、高效地利用了这两个索引。

何时使用：当性能是首要考虑因素，并且能通过逻辑（如 AND age <> 30）或业务保证结果集不重复时，这是最佳选择。

解法 2：`UNION` 改写（保证结果一致）

适用版本：所有 SQL 版本
SQL 实现：

sql

 1(SELECT id, name, age, city FROM users WHERE age = 30)
 2UNION
 3(SELECT id, name, age, city FROM users WHERE city = '北京');

复杂度：
- 与 UNION ALL 类似，但增加了去重步骤。
- 去重操作通常需要创建临时表并进行排序或哈希，复杂度约为 $O((K_1+K_2)\log(K_1+K_2))$ 。
索引依赖：idx_age 和 idx_city。

何时使用：当必须确保结果与原始 OR 查询完全一致（即无重复行），且无法或不便在 WHERE 子句中手动去重时使用。需要接受去重带来的额外性能开销。

常见低分回答

“OR 查询一定会导致索引失效，必须改成 UNION。”
- 错误：这个说法太绝对。MySQL 的 Index Merge 优化就是为了处理这种情况。在很多场景下 Index Merge 效率不错，无需改写。正确的回答应该包含对 Index Merge 的讨论。
只提 UNION，不提 UNION ALL。
- 不足：忽略了 UNION 和 UNION ALL 的关键性能差异。UNION 的隐式去重是一个重要的性能陷阱。高分回答必须能辨析二者，并根据场景给出最优建议。
“给 age 和 city 建一个复合索引 (age, city) 就能解决。”
- 错误：对于 WHERE age = 30 OR city = '北京'，复合索引 (age, city) 无法有效工作。它只能用于 age 的查询，或者 age 和 city 同时出现的 AND 查询。对于 city 的查询，由于不满足最左前缀原则，该复合索引无效。
改写 UNION ALL 时，忘记处理重复数据问题。
- 不足：SELECT ... WHERE age=30 UNION ALL SELECT ... WHERE city='北京' 会导致同时满足两个条件的用户（如一个 30 岁的北京用户）出现两次。虽然性能好，但结果集与原 OR 查询不等价。严谨的回答应指出这一点并提供解决方案（如解法 1 中的 AND age <> 30）。

工程实践与优化

看执行计划是第一步：在决定是否改写前，务必使用 EXPLAIN 分析原始 OR 查询的执行计划。如果 type 是 index_merge 且 rows 估算值不大，可能无需优化。如果 type 是 ALL（全表扫描），或者 rows 估算值很大，则应考虑改写。MySQL 8.0+ 的 EXPLAIN ANALYZE 能提供更精确的实际执行信息。
数据分布是关键：UNION 改写是否优于 Index Merge，很大程度上取决于数据的选择性。如果 OR 的两边条件都能过滤掉绝大部分数据（高选择性），那么 UNION 改写后，两个子查询都非常快，性能优势明显。如果任一条件返回大量数据（低选择性），优化器可能会认为 Index Merge 后的大量回表不如直接全表扫描，此时 UNION 改写也可能不是最优解。
覆盖索引：如果查询的字段（id, name, age, city）都被包含在索引中（例如创建 idx_age_name_city(age, name, city)），OR 查询或 UNION 改写都可以从“回表”变为“覆盖索引”，性能会得到巨大提升。但这需要根据业务查询模式综合设计索引。
监控与工具：在生产环境中，通过慢查询日志、Performance Schema 或 pt-query-digest 等工具，可以发现这类存在性能隐患的 OR 查询，并作为优化的依据。

常见追问与延伸题目

追问：如果 OR 的条件是在同一个索引列上，比如 WHERE age = 30 OR age = 40，情况如何？
- 回答要点：这种情况与 WHERE age IN (30, 40) 等价。MySQL 优化器会将其转换成索引的 Range Scan（范围扫描），而不是 Index Merge。它会在 idx_age 索引上定位到 30 和 40 的位置，然后进行扫描。这种查询效率非常高，完全不需要改写成 UNION。这恰好反衬出跨列 OR 的复杂性。
追问：UNION ALL 改写时，你用了 AND age <> 30 来去重，如果 age 列上可能有 NULL 值怎么办？
- 回答要点：这是一个很好的边界情况提问。age <> 30 会将 age 为 NULL 的行也排除掉。如果 city = '北京' 且 age 为 NULL 的行也需要被包含，那么 WHERE 条件需要更严谨。可以改写为 WHERE city = '北京' AND (age <> 30 OR age IS NULL)。这展示了候选人对 NULL 值在比较运算中行为的深刻理解。
追问：除了 UNION 改写，还有没有其他优化思路？
- 回答要点：可以从应用层面或架构层面解决。
  - 应用层拆分：将一个 OR 查询拆分为两个独立的查询请求，在应用代码中合并结果。这给了应用层更大的控制力（例如，可以异步执行两个查询），但增加了代码复杂度和网络开销。
  - 数据冗余/反范式：如果这类查询非常频繁，可以考虑在表中增加一个冗余字段，如 is_key_user。当 age=30 或 city='北京' 时，该字段被标记为 1。这样查询就变成了 WHERE is_key_user = 1，只需在一个单列索引上进行高效查询。这是一种用空间换时间的典型思想。
  - 引入外部搜索引擎：对于更复杂的、多维度、模糊的组合查询，单纯依靠 MySQL 可能已不是最佳方案。可以考虑将数据同步到 Elasticsearch 等搜索引擎中，利用其强大的倒排索引和查询能力来解决。