SQL中IS NOT NULL与日期比较的性能差异及优化通用性疑问

原始查询（执行较慢）：

SELECT COUNT(DISTINCT (`patient_id`)) AS `num`
FROM `visits` AS `Visit`
         INNER JOIN `patients` AS `patient` ON `Visit`.`patient_id` = `patient`.`id`
WHERE `Visit`.`doctor_id` = 21293
  AND `Visit`.`office_id` = 14712
  AND `Visit`.`started_at` IS NOT NULL
LIMIT 1;

将Visit.started_at IS NOT NULL替换为Visit.started_at > '2000-01-01'后查询速度提升，疑问：该优化是否所有场景适用、必然提升性能？

核心结论 #

这个优化手段并非所有场景都适用，也不能保证必然提升性能，其效果取决于数据分布、索引类型、数据库优化器逻辑，且必须以业务逻辑一致性为前提。

具体影响因素 #

• 数据分布决定过滤效率
  如果表中started_at的非NULL值大多晚于'2000-01-01'，那么> '2000-01-01'会比IS NOT NULL过滤掉更多无效数据，减少后续需要处理的行数，自然提升速度。但如果存在大量早于'2000-01-01'的有效非NULL记录，或者NULL值极少，两种条件的过滤范围差异不大，性能提升就不明显；极端情况下，如果大部分符合doctor_id和office_id条件的记录started_at都早于'2000-01-01'，替换后优化器可能放弃索引扫描（认为过滤后行数太少不值得用索引），反而导致全表扫描，性能下降。

• 索引类型影响扫描逻辑
  对于常见的B-tree索引，NULL值通常会被集中放在索引的一端。IS NOT NULL需要扫描索引中除NULL段之外的所有数据，而> '2000-01-01'是从索引的某个中间节点开始范围扫描，扫描的索引范围更小，效率更高。但如果是哈希索引（比如MySQL的MEMORY引擎），两种条件都会触发哈希查找，性能差异可以忽略。

• 数据库优化器的执行计划选择
  不同数据库的优化器会根据表的统计信息（比如NULL值占比、started_at的数值分布）估算两种条件的过滤行数，进而选择最优执行计划。如果统计信息过时或不准确，优化器可能做出错误判断，比如认为> '2000-01-01'过滤后的行数太少，反而选择全表扫描，导致性能下降。

• 业务逻辑的一致性是前提
  这个替换的核心前提是：所有业务上有效的started_at非NULL记录都晚于'2000-01-01'。如果存在早于该日期的有效记录，替换后会导致查询结果少统计部分数据，完全违背业务需求——性能优化不能以牺牲数据正确性为代价。

优化建议 #

1. 验证业务逻辑一致性：先确认started_at IS NOT NULL和started_at > '2000-01-01'的查询结果完全一致，再考虑使用该优化。
2. 分析数据分布：通过SELECT COUNT(*) FROM visits WHERE started_at IS NULL;、SELECT MIN(started_at) FROM visits;等语句，了解NULL值数量和started_at的最小值，判断替换后的过滤范围是否合理。
3. 对比执行计划：用EXPLAIN命令查看两种查询的执行计划，重点关注type（索引类型）、rows（预估扫描行数）、Extra（是否使用索引、是否需要排序等），明确性能差异的根源。
4. 创建复合索引：如果该查询是高频查询，建议创建复合索引(doctor_id, office_id, started_at)，让优化器可以直接通过索引过滤出符合doctor_id、office_id、started_at条件的行，避免回表和额外的去重操作，从根源提升查询性能。

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