跳至主要內容

十万个why：明明两个字段都加了单列索引，为什么写了句 OR 之后，执行计划还是走了全表扫描？

程序员小富大约 7 分钟

大家好，我是小富～

写 SQL 使用 OR 条件是非常常见的场景，为了优化这类查询，会特意为 phone 和 email 两个字段分别创建单列索引。

SELECT * FROM users WHERE phone = '13800000000' OR email = 'xiaofu@qq.com';

但我们使用 EXPLAIN 查看该语句的执行计划，往往会发现 type 字段显示为 ALL，说明 MySQL 最终还是走了全表扫描，没有使用我们创建的索引。

奇怪吧，明明两个字段都有索引，为什么用 OR 之后索引却失效了？

MySQL 优化器的成本计算

理解为什么索引失效前，先理解 MySQL 查询优化器的工作原理。MySQL 是基于成本的优化器，它在生成执行计划时，会估算各种执行路径的成本值，最终选择成本最低的路径。

传统的关系型数据库，MySQL 默认的读取路径一次查询只能使用一个索引。假设我们将查询限制在 phone 索引上，那么优化器会通过 phone 二级索引树快速定位到主键值，再通过主键值回表获取完整的行记录。

但在 OR 条件下，查询的语义是并集，也就是满足 phone = '13800000000' 或者 email = 'xiaofu@qq.com' 的数据都需要被找出来。

如果优化器只选择走 phone 索引，确实能快速找到 phone 匹配的行；但由于满足 email 条件的行可能散落在表的其他位置，为了不漏掉数据，MySQL 在通过 phone 索引查出部分数据后，仍然不得不对整张表进行一次全表扫描，找出满足 email = 'xiaofu@qq.com' 且不与 phone 重合的数据。

这时候优化器会对比两种执行路径的成本：

  • 路径 A（单索引 + 回表 + 全表扫描）：扫描 phone 索引 + 回表获取记录 + 全表扫描查找 email 的记录。

  • 路径 B（直接全表扫描）：从头到尾扫描一次全表，边扫描边过滤满足 phone 或 email 条件的记录。

回表属于随机 I/O，而全表扫描属于顺序 I/O。在 MySQL 的成本计算中，一次随机 I/O 的权重默认是顺序 I/O 的几倍。如果回表的数据行数稍微多一点，路径 A 的估算成本就会远超路径 B。所以优化器会果断放弃索引，直接走全表扫描。

为什么索引合并没生效？

有同学会问：MySQL 不是有索引合并（Index Merge）机制，它能同时用两个索引，最后在内存里把结果合并吗？

OR 条件下，MySQL 确实有个 index_merge_union 索引并集算法，它的处理流程：

  1. 二级索引扫描：通过 phone 索引树扫描出满足条件的主键 ID 集合 S1。由于二级索引的叶子节点本身是按照二级索引键值排序的，相同的二级索引键值，其叶子节点存储的主键 ID 默认是递增有序的。

  2. 二级索引扫描：通过 email 索引树扫描出满足条件的主键 ID 集合 S2，同样的其内部主键 ID 也是有序的。

  3. 并集去重：内存中将 S1 和 S2 进行去重合并，得到最终的主键集合。因为 S1 和 S2 天生有序，MySQL 可以使用高效的双指针归并算法以 O(N) 的时间复杂度快速完成合并。

  4. 有序回表：利用合并后的主键集合进行回表查询。这时主键 ID 已经是去重且有序的，回表操作可以从随机 I/O优化为顺序 I/O，极大地提高了读取效率。

都有这个机制，为什么实际开发很难看到它生效？这主要受限于几个硬性约束和成本考量：

算法对主键有序性的要求

index_merge_union 算法之所以高效，核心在于并集去重操作能在 O(N) 时间复杂度内完成。这要求 S1 和 S2 两个集合在扫描出来时必须是天生有序的。

只有在等值查询，如 phone = '138...' 取出的主键 ID 才是按照主键大小递增排序的。

一旦出现范围查询，如 phone LIKE '138%' 或 phone > '138'，由于二级索引键值不同，即使索引字段有序，但对应的主键 ID 在索引页中也是无序交错的。

这样 MySQL 无法直接利用双指针进行并集，必须引入 Sort-Union 算法，即先在内存中对主键 ID 进行排序，然后再做并集。但在内存排序对 CPU 和内存开销很大，优化器在估算成本后，通常会放弃索引合并，直接选择全表扫描。

优化器成本估算的临界值

即便 OR 两边都是等值查询，字段都有索引，优化器依然会很细致。

回表比例达到一定阈值（一般取决于表的数据量、页大小及系统负载），回表的随机 I/O 成本会呈指数级上升。优化器计算出索引合并的成本比一次性的全表扫描还要高，必然会选择全表扫描。

硬性失效

OR 的底层逻辑是必须获取满足任意一方的所有数据：

隐式类型转换：如果 phone 在表中是 VARCHAR 类型，但在 SQL 中写成了数字 WHERE phone = 13800000000，MySQL 会隐式地将字段值转换为浮点数再做比较，导致 phone 索引失效。

既然 phone 无法走索引，MySQL 就必须通过全表扫描来找出满足 phone 条件的行，整个查询因此退化为全表扫描。

包含未建索引的列：如果 SQL 包含没有索引的字段 WHERE phone = '138...' OR age = 18，因为 age 没有索引，数据库无论如何都要进行全表扫描以过滤 age = 18 的数据，所以 phone 索引同样会被放弃。

大厂的 SQL 优化方案

为了规避 OR 的索引失效和优化器成本估算不准，实际开发可以用以下两种更稳妥的优化方案。

用 UNION 或 UNION ALL 代替 OR（首选）

这是我最推荐、执行计划最稳定的改写方式，我们可以将 OR 查询拆分为两个独立的子查询，然后使用 UNION 进行连接：

SELECT * FROM users WHERE phone = '13800000000'
UNION ALL
SELECT * FROM users WHERE email = 'xiaofu@qq.com';

为什么这种方案更优？

  1. 没有单索引限制：拆分后两个子查询是完全独立的，第一个子查询可以稳定、高效地使用 phone 索引，第二个子查询可以稳定使用 email 索引。

  2. 避免优化器估算失准：单索引查询的成本估算非常精准，MySQL 不用去纠结复杂的 Index Merge 成本。

  3. 优先使用 UNION ALL 提升性能：UNION 会在内存中创建一个临时表，对结果集进行去重排序，带来额外的 CPU 和内存开销。如果业务上可以容忍重复数据，或者逻辑上两个条件的结果集本身就是互斥的，例如 phone 和 email 匹配到的不可能是同一行，强烈建议使用 UNION ALL。UNION ALL 只做结果集拼接，不进行任何去重和排序性能高。

覆盖索引

如果你的业务场景不需要 SELECT *，只需要获取索引列本身

SELECT id, phone, email FROM users WHERE phone = '13800000000' OR email = 'xiaofu@qq.com';

由于查询的字段（id、phone、email）已经全部包含在二级索引中，MySQL扫描索引后不需要进行回表。 没有了随机 I/O 成本，索引扫描和内存合并的开销就变得极廉价，MySQL 优化器 100% 会触发 index_merge_union 避免全表扫描。

说在最后

SQL 优化的核心，就是让 SQL 的执行路径更简单，增加 SQL 查询的确定性。包含 OR 条件的组合查询，经常因为回表成本的权衡，导致优化器选择保守的全表扫描。

多索引 OR 查询建议使用 UNION ALL 进行改写，将复杂的、充满不确定性的多条件 OR 查询，拆解为确定性更高、执行路径更清晰的单索引查询，可以保证 SQL 查询的稳定性。

上次编辑于: