写 SQL 使用OR条件是非常常见的场景为了优化这类查询会特意为phone和email两个字段分别创建单列索引。SELECT * FROM users WHERE phone 13800000000 OR email xiaofuqq.com;但我们使用EXPLAIN查看该语句的执行计划往往会发现type字段显示为ALL说明 MySQL 最终还是走了全表扫描没有使用我们创建的索引。奇怪吧明明两个字段都有索引为什么用OR之后索引却失效了MySQL 优化器的成本计算理解为什么索引失效前先理解 MySQL 查询优化器的工作原理。MySQL 是基于成本的优化器它在生成执行计划时会估算各种执行路径的成本值最终选择成本最低的路径。传统的关系型数据库MySQL 默认的读取路径一次查询只能使用一个索引。假设我们将查询限制在phone索引上那么优化器会通过phone二级索引树快速定位到主键值再通过主键值回表获取完整的行记录。但在OR条件下查询的语义是并集也就是满足phone 13800000000或者email xiaofuqq.com的数据都需要被找出来。如果优化器只选择走phone索引确实能快速找到phone匹配的行但由于满足email条件的行可能散落在表的其他位置为了不漏掉数据MySQL 在通过phone索引查出部分数据后仍然不得不对整张表进行一次全表扫描找出满足email xiaofuqq.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索引并集算法它的处理流程二级索引扫描通过 phone 索引树扫描出满足条件的主键 ID 集合 S1。由于二级索引的叶子节点本身是按照二级索引键值排序的相同的二级索引键值其叶子节点存储的主键 ID 默认是递增有序的。二级索引扫描通过 email 索引树扫描出满足条件的主键 ID 集合 S2同样的其内部主键 ID 也是有序的。并集去重内存中将 S1 和 S2 进行去重合并得到最终的主键集合。因为 S1 和 S2 天生有序MySQL 可以使用高效的双指针归并算法以 O(N) 的时间复杂度快速完成合并。有序回表利用合并后的主键集合进行回表查询。这时主键 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 13800000000MySQL 会隐式地将字段值转换为浮点数再做比较导致 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 xiaofuqq.com;为什么这种方案更优没有单索引限制拆分后两个子查询是完全独立的第一个子查询可以稳定、高效地使用 phone 索引第二个子查询可以稳定使用 email 索引。避免优化器估算失准单索引查询的成本估算非常精准MySQL 不用去纠结复杂的Index Merge成本。优先使用UNION ALL提升性能UNION会在内存中创建一个临时表对结果集进行去重排序带来额外的 CPU 和内存开销。如果业务上可以容忍重复数据或者逻辑上两个条件的结果集本身就是互斥的例如 phone 和 email 匹配到的不可能是同一行强烈建议使用 UNION ALL。UNION ALL只做结果集拼接不进行任何去重和排序性能高。覆盖索引如果你的业务场景不需要SELECT *只需要获取索引列本身SELECT id, phone, email FROM users WHERE phone 13800000000 OR email xiaofuqq.com;由于查询的字段id、phone、email已经全部包含在二级索引中MySQL扫描索引后不需要进行回表。 没有了随机 I/O 成本索引扫描和内存合并的开销就变得极廉价MySQL 优化器 100% 会触发index_merge_union避免全表扫描。说在最后SQL 优化的核心就是让 SQL 的执行路径更简单增加 SQL 查询的确定性。包含OR条件的组合查询经常因为回表成本的权衡导致优化器选择保守的全表扫描。多索引 OR 查询建议使用UNION ALL进行改写将复杂的、充满不确定性的多条件 OR 查询拆解为确定性更高、执行路径更清晰的单索引查询可以保证 SQL 查询的稳定性。 两个字段都建了单列索引，为什么加了 OR，执行计划还是全表扫描？
写 SQL 使用OR条件是非常常见的场景为了优化这类查询会特意为phone和email两个字段分别创建单列索引。SELECT * FROM users WHERE phone 13800000000 OR email xiaofuqq.com;但我们使用EXPLAIN查看该语句的执行计划往往会发现type字段显示为ALL说明 MySQL 最终还是走了全表扫描没有使用我们创建的索引。奇怪吧明明两个字段都有索引为什么用OR之后索引却失效了MySQL 优化器的成本计算理解为什么索引失效前先理解 MySQL 查询优化器的工作原理。MySQL 是基于成本的优化器它在生成执行计划时会估算各种执行路径的成本值最终选择成本最低的路径。传统的关系型数据库MySQL 默认的读取路径一次查询只能使用一个索引。假设我们将查询限制在phone索引上那么优化器会通过phone二级索引树快速定位到主键值再通过主键值回表获取完整的行记录。但在OR条件下查询的语义是并集也就是满足phone 13800000000或者email xiaofuqq.com的数据都需要被找出来。如果优化器只选择走phone索引确实能快速找到phone匹配的行但由于满足email条件的行可能散落在表的其他位置为了不漏掉数据MySQL 在通过phone索引查出部分数据后仍然不得不对整张表进行一次全表扫描找出满足email xiaofuqq.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索引并集算法它的处理流程二级索引扫描通过 phone 索引树扫描出满足条件的主键 ID 集合 S1。由于二级索引的叶子节点本身是按照二级索引键值排序的相同的二级索引键值其叶子节点存储的主键 ID 默认是递增有序的。二级索引扫描通过 email 索引树扫描出满足条件的主键 ID 集合 S2同样的其内部主键 ID 也是有序的。并集去重内存中将 S1 和 S2 进行去重合并得到最终的主键集合。因为 S1 和 S2 天生有序MySQL 可以使用高效的双指针归并算法以 O(N) 的时间复杂度快速完成合并。有序回表利用合并后的主键集合进行回表查询。这时主键 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 13800000000MySQL 会隐式地将字段值转换为浮点数再做比较导致 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 xiaofuqq.com;为什么这种方案更优没有单索引限制拆分后两个子查询是完全独立的第一个子查询可以稳定、高效地使用 phone 索引第二个子查询可以稳定使用 email 索引。避免优化器估算失准单索引查询的成本估算非常精准MySQL 不用去纠结复杂的Index Merge成本。优先使用UNION ALL提升性能UNION会在内存中创建一个临时表对结果集进行去重排序带来额外的 CPU 和内存开销。如果业务上可以容忍重复数据或者逻辑上两个条件的结果集本身就是互斥的例如 phone 和 email 匹配到的不可能是同一行强烈建议使用 UNION ALL。UNION ALL只做结果集拼接不进行任何去重和排序性能高。覆盖索引如果你的业务场景不需要SELECT *只需要获取索引列本身SELECT id, phone, email FROM users WHERE phone 13800000000 OR email xiaofuqq.com;由于查询的字段id、phone、email已经全部包含在二级索引中MySQL扫描索引后不需要进行回表。 没有了随机 I/O 成本索引扫描和内存合并的开销就变得极廉价MySQL 优化器 100% 会触发index_merge_union避免全表扫描。说在最后SQL 优化的核心就是让 SQL 的执行路径更简单增加 SQL 查询的确定性。包含OR条件的组合查询经常因为回表成本的权衡导致优化器选择保守的全表扫描。多索引 OR 查询建议使用UNION ALL进行改写将复杂的、充满不确定性的多条件 OR 查询拆解为确定性更高、执行路径更清晰的单索引查询可以保证 SQL 查询的稳定性。