使用IS NULL OR与ISNULL搭配LIKE的性能影响及百万级数据扩展疑问

嘿，针对你遇到的这个参数化查询里处理LIKE '%'时要包含NULL的问题，以及百万级大表的性能扩展担忧，我来分享下实际落地里的经验和分析：

你要实现的逻辑其实是：当查询参数是'%'（也就是全匹配）时，要返回所有非NULL的匹配值+NULL值——但常规的LIKE '%'只会命中非NULL的任意值，NULL是不满足LIKE条件的，所以得做特殊处理。

你提到小数据量下几种方案的执行计划、耗时都差不多，但到百万级量级，就得从索引利用、执行逻辑的效率来抠细节了：

方案1：用OR结合IS NULL #

WHERE (YourColumn LIKE @SearchParam OR YourColumn IS NULL)

• 小数据量下看起来没问题，但到百万级表要注意：如果YourColumn上没有索引，必然走全表扫描；如果有索引，当@SearchParam是'%'时，优化器能识别出LIKE '%'等价于YourColumn IS NOT NULL，整个条件就变成YourColumn IS NOT NULL OR YourColumn IS NULL——说白了就是要返回全表数据，这时候不管有没有索引，都会扫整个表或整个索引，这是不可避免的，毕竟你要拿所有数据。但如果参数不是'%'，这个写法能正常用索引seek，还算高效。

方案2：用ISNULL/COALESCE转换NULL #

WHERE ISNULL(YourColumn, '') LIKE @SearchParam
-- 或者
WHERE COALESCE(YourColumn, '') LIKE @SearchParam

• 正如Grant Fritchey演示的那样，ISNULL和COALESCE在这个场景下的性能差异极小，不用太纠结选哪个。但要注意：这种写法用函数包装了YourColumn，会直接导致列上的常规索引失效，百万级表下只能走全表扫描，代价很高。
• 优化思路：如果经常需要这种匹配逻辑，可以给表加个计算列+对应索引：

  ALTER TABLE YourTable ADD YourColumn_NullAsEmpty AS ISNULL(YourColumn, '')
  CREATE NONCLUSTERED INDEX IX_YourTable_YourColumn_NullAsEmpty ON YourTable(YourColumn_NullAsEmpty)
  

  之后查询时直接用这个计算列匹配，就能用到索引seek，性能会大幅提升。

方案3：动态SQL构建条件 #

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM YourTable WHERE 1=1'
IF @SearchParam <> '%'
BEGIN
    SET @SQL += ' AND YourColumn LIKE @SearchParam'
END
ELSE
BEGIN
    SET @SQL += ' AND (YourColumn IS NOT NULL OR YourColumn IS NULL)'
END
EXEC sp_executesql @SQL, N'@SearchParam NVARCHAR(100)', @SearchParam

• 这个方案的优势是“按需生成最优查询”：当参数不是'%'时，生成的纯LIKE条件能正常用索引seek；当是'%'时，等价于全表查询，优化器会选最有效的方式返回数据。而且你用sp_executesql带参数的写法，也规避了SQL注入风险，很安全。

1. 优先搞对索引策略：如果大部分场景下参数不是'%'，给YourColumn建常规非聚集索引是最优解；如果经常要处理全匹配+NULL的场景，计算列索引会比全表扫描高效太多。
2. 别随便用函数包装列：除非你给计算列建了索引，否则ISNULL(YourColumn, '')这种写法会废掉索引，百万级数据下扫描的IO代价会高到离谱。
3. 用真实量级数据测试：小数据量的执行计划参考价值有限，建议用工具生成百万级测试数据，打开SET STATISTICS IO, TIME ON看实际的CPU、IO消耗，这样才能准确判断性能。
4. 别纠结ISNULL和COALESCE的小差异：Grant的测试结论也说了，两者的性能差远不如索引策略带来的影响大，把精力放在优化查询逻辑和索引上才是关键。

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