MySQL中OR、IN与UNION ALL查询效率对比分析

结果或目标：明确比较对象与核心变量

我们需要明确比较的是 MySQL 中对单表查询性能的三种常见写法：OR、IN 以及 UNION ALL。核心的评判变量为：
- 执行耗时：查询返回结果所花费的时间。
- 执行计划：通过 EXPLAIN 查看表的访问类型（Type）、扫描行数（Rows）以及使用的索引（Key）。
- 是否存在索引：查询条件所在的字段是否包含有效的索引（主键或非主键索引）。

准备条件：建立实验环境与数据集

为了能够清晰地观察到性能差异，我们构建了一个包含约一千万行数据的测试表。简单的 InnoDB 引擎表结构如下：

DROP TABLE IF EXISTS BT;
CREATE TABLE BT (
  ID INT(10) NOT NULL,
  VName VARCHAR(20) DEFAULT '' NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=INNODB;

注意
插入大量数据时，建议暂时设置 autocommit = 0 并定期 COMMIT，以加快批量插入速度。此外，为了模拟真实的“非索引列”情况，我们在非主键字段的值中加入了额外的后缀（如 'TT'），防止因存储引擎的特殊处理而导致不准确的索引命中结果。

第1步：生成百万级测试数据

可以使用以下存储过程进行快速填充。此处以 10,000,000 条为例：

DROP PROCEDURE IF EXISTS test_proc;
CREATE PROCEDURE test_proc()
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  WHILE i < 10000000 DO
    INSERT INTO BT (ID, VNAME) VALUES(i, CONCAT('M', i, 'TT'));
    SET i = i + 1;
    IF i % 2000 = 0 THEN
      COMMIT;
    END IF;
  END WHILE;
END;

插入完成后，表中包含一千万条记录。其中 `ID` 是主键（有唯一索引），`VName` 是没有额外普通索引的普通字段。

真实操作顺序：分场景进行性能测试

第2步：在【有索引】的主键字段上使用三种语法

对 `ID` 字段查询特定的三个不连续的值（例如 98，85220，9888589）进行测试。为了便于观察精确到毫秒级的差异，这里采用了更精细的计时工具（如 EMS SQL Manager 等客户端）而非仅靠 MySQL 命令行返回值。

A：使用 OR 连接

SELECT * FROM bt WHERE id = 98 OR id = 85220 OR id = 9888589;

B：使用 IN 列表

SELECT * FROM bt WHERE id IN (98, 85220, 9888589);

C：使用 UNION ALL 拼接

SELECT * FROM bt WHERE id = 98
UNION ALL
SELECT * FROM bt WHERE id = 85220
UNION ALL
SELECT * FROM bt WHERE id = 9888589;

执行时间与执行计划分析：

• 这三种方式的结果集一致，且查询耗时都在同一数量级（客户端显示均不到 100ms，相差几毫秒以内）。
• 我们重点观察 `EXPLAIN` 执行计划中的关键字段：
  

• 对于 `OR` 和 `IN`：Type 为 range（范围扫描），表示利用了主键索引。
• 对于 `UNION ALL`：其内部每个子查询的 Type 均为 ref（引用扫描），同样利用了主键索引。

虽然从理论执行计划看，ref（精准匹配单个常量）优于 range（匹配一个范围），但在查找固定几个点的主键时，MySQL 优化器已经将这种差异降到了最低。因此在索引列上，它们的表现可以说是不相上下。

第3步：在【无索引】的普通字段上使用三种语法

接下来对没有建普通索引的 `VName` 字段进行同样的查询（例如值为 'M98TT'，'M85220TT'，'M9888589TT'）：

A：使用 OR 连接

SELECT * FROM bt WHERE vname = 'M98TT' OR vname = 'M85220TT' OR vname = 'M9888589TT';

B：使用 IN 列表

SELECT * FROM bt WHERE vname IN ('M98TT', 'M85220TT', 'M9888589TT');

C：使用 UNION ALL 拼接

SELECT * FROM bt WHERE vname = 'M98TT'
UNION ALL
SELECT * FROM bt WHERE vname = 'M85220TT'
UNION ALL
SELECT * FROM bt WHERE vname = 'M9888589TT';

关键测试结果：
在这种纯全表扫描（Type: ALL）的情况下，UNION ALL 的执行时间变成了 OR 和 IN 的接近三倍！

原因分析：
- OR 和 IN：MySQL 只需要**遍历整张表一次**（rows 字段显示的扫描总行数等于表的物理行数之和）。它在一遍 IO 读取中比对三个值，命中就输出。
- UNION ALL：将其拆解为三次完全独立的查询，这意味着 MySQL 需要对全表进行**三次完整的全表扫描**。扫描的物理 IO 量和 CPU 比对工作量自然翻了倍。

完成判断：基于实际情况的选择策略

经过上述两组对比测试，我们可以得出符合 MySQL 优化器运行逻辑的最终建议：

1. 针对索引列的常数条件查询
   不要迷信 UNION ALL。在仅有几个常量的条件下，使用 OR 或 IN 即可，不仅速度快（因为都走了索引），而且代码可读性最高。
2. 针对非索引列的查询（必须全表扫描时）
   坚决使用 OR 或 IN，禁用 UNION ALL！** 否则你将白白承受 N 倍（N为查询条件组数）的全表扫描开销，这对大表来说是毁灭性的性能打击。
3. 针对“既有索引列又有非索引列”的复杂混合查询
   比如 WHERE VName = 'xxx' OR ID = 999。
   推荐使用 OR 或 IN。虽然在极端复杂计算导致优化器放弃部分索引时可能需要用到 UNION 来强制拆分，但在绝大多数简单的多字段并列查询中，合并成一个查询让优化器统筹决定（即使其中一个条件是全表扫描，MySQL 也能较高效地跑完一次 I/O 顺便查了另一个字段）。

简要总结

《MySQL中使用OR、IN与UNION ALL在查询命令下的效率对比》的核心结论是：UNION ALL 并非万能钥匙，其性能优势仅在特定索引命中场景下才略微显现，而当遭遇无条件限制导致需要全表扫描时，它会比 OR/IN 慢得多。在实际业务编码中，请结合查询字段的索引情况来灵活切换写法。

以上就是MySQL中OR、IN与UNION ALL在查询命令下的效率对比的详细内容，更多关于SQL优化、MySQL查询性能的资料请关注本站其它相关文章！