SELECT查询WHERE子句排除NULL行是否有性能优势及优化咨询

性能收益结论 #

查询#2相比查询#1没有任何性能收益，二者执行效率完全一致。
原因非常简单：所有主流关系型数据库（MySQL、PostgreSQL、SQL Server等）的查询优化器，都能识别SampleCol = 'Test'这个等值条件隐含了SampleCol IS NOT NULL的逻辑——因为NULL和任何值做等值比较的结果都是UNKNOWN，不会被计入查询结果集。优化器会自动将两个查询改写为完全相同的执行计划，本质都是全表扫描10万行数据过滤符合条件的行，额外写的IS NOT NULL条件不会带来任何加速，反而属于冗余代码。

这类查询的性能优化建议 #

• 优先为SampleCol列建立索引：当前该列无索引是性能瓶颈的核心，两个查询都只能走全表扫描。如果该列NULL值占比极高，还可以进一步建立过滤/部分索引，只索引非NULL的行，大幅降低索引体积、提升查询效率，示例SQL：

  -- 普通B树索引，适配所有数据库
  CREATE INDEX idx_foo_samplecol ON Foo(SampleCol);
  
  -- 过滤索引（PostgreSQL/SQL Server支持，MySQL 8.0.13+也支持类似的函数索引/条件索引）
  CREATE INDEX idx_foo_samplecol_nonnull ON Foo(SampleCol) WHERE SampleCol IS NOT NULL;
  

• 避免使用select *，按需查询字段：如果只需要返回特定几个字段，可以把这些字段加到索引的尾部，做成覆盖索引，查询时直接从索引取数不需要回表查主键对应的整行数据，性能可提升数倍。
• 业务允许的前提下修改列属性为NOT NULL：如果业务逻辑上SampleCol不存在NULL的合理含义，可以给列设置默认值（比如空字符串）后改为NOT NULL约束，既能避免NULL值带来的各种逻辑坑，也能进一步提升索引的查询效率。
• 不要冗余写不必要的判断条件：除了增加代码维护成本外无任何收益，极端情况下还可能干扰部分小众数据库或旧版本数据库的优化器判断。

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