SQL Server移除WHERE子句NOT语句优化慢查询并保证结果一致

你之前的改写错误核心是误用了德摩根定律：原查询是NOT (A OR B OR C OR D)，等价于NOT A AND NOT B AND NOT C AND NOT D，而非你用的OR连接。同时每个子条件内部的AND取反后要替换为OR，外层的OR取反后要替换为AND。

正确等价改写步骤 #

先拆解原查询中被NOT包裹的四个子条件，逐个取反后用AND拼接：

1. 原第一个子条件：ispending = 'F' AND isviewable = '0'
   取反结果：ispending != 'F' OR isviewable != '0'
2. 原第二个子条件：ispending = 'T' AND iscomparable = '0'
   取反结果：ispending != 'T' OR iscomparable != '0'
3. 原第三个子条件：ispending = 'T' AND iscomparable IS NULL AND isviewable = '0'
   取反结果：ispending != 'T' OR iscomparable IS NOT NULL OR isviewable != '0'
4. 原第四个子条件：ispending IS NULL AND iscomparable = '0'
   取反结果：ispending IS NOT NULL OR iscomparable != '0'

最终等价SQL语句 #

SELECT ispending, isviewable, iscomparable, ID
FROM tableA
WHERE
    name = 'Karen' 
    AND (ispending != 'F' OR isviewable != '0')
    AND (ispending != 'T' OR iscomparable != '0')
    AND (ispending != 'T' OR iscomparable IS NOT NULL OR isviewable != '0')
    AND (ispending IS NOT NULL OR iscomparable != '0')

额外性能优化建议 #

如果要提升查询速度，可以创建覆盖过滤条件和返回字段的复合索引：

CREATE INDEX idx_tableA_name_pending_viewable_comparable ON tableA (name, ispending, isviewable, iscomparable);

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