两列均可为NULL场景下等值比较如何让MySQL命中索引

你当前使用的(t1.id is null and t2.id is null) or (t1.id = t2.id)判断逻辑，因为OR条件拼接NULL判断的写法，会让MySQL优化器误判全表扫描成本低于索引扫描，导致无法命中已建的id列索引，可按以下优先级选择优化方案：

• 优先使用NULL安全等值运算符<=>替换现有判断逻辑
  该运算符是MySQL原生支持的等值比较语法，天然匹配你的判定规则：两个值相等时返回真，两个值均为NULL时同样返回真，其余不等场景返回假。替换后的判断逻辑非常简洁：

  t1.id <=> t2.id
  

  该写法不会触发OR条件的优化器失效问题，无论是作为WHERE过滤条件还是两表JOIN的关联条件，都可以正常命中id列上的普通B树索引。

• 从表设计层面规避NULL值（长期最优方案）
  如果业务场景允许，直接将两个id列的属性修改为NOT NULL，同时给业务上不存在的id值设置默认值（比如自增ID不可能为负数时设默认值-1），彻底消除NULL值判断场景。此时判断逻辑可以直接简化为t1.id = t2.id，可以100%稳定命中索引，同时也能避免其他场景下NULL值带来的逻辑坑、统计偏差问题。

• 无法修改表结构时，用UNION ALL拆分OR逻辑
  如果暂时不能调整字段非空属性，也可以将原本带OR的单条查询拆分为两个结果集无重叠的子查询，通过UNION ALL合并结果：

  -- 第一部分：匹配两列均为NULL的场景
  SELECT * FROM t1 JOIN t2 ON t1.id IS NULL AND t2.id IS NULL
  UNION ALL
  -- 第二部分：匹配两列非空且相等的场景
  SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
  

  拆分后的两个子查询的过滤/关联条件都没有OR拼接，优化器可以分别对两个条件生成走索引的执行计划，同时因为两个子查询的结果集完全不存在重叠（一个是NULL匹配场景，一个是非NULL等值匹配场景），用UNION ALL不需要做去重操作，性能损耗极低。

注意不要为了兼容NULL判断在索引列上套用函数，比如写成IFNULL(t1.id, -1) = IFNULL(t2.id, -1)，这种写法会因为对索引列做了函数计算导致索引失效，除非额外创建对应的函数索引，否则性能反而比原写法更差。修改完查询逻辑后，可以通过EXPLAIN命令查看执行计划，当执行计划的key列显示你创建的id索引、type列为ref/eq_ref/range时，说明索引已经被正常使用。

内容的提问来源于stack exchange，提问作者dinwal