SSMS中大量NOT LIKE加通配符的SQL查询优化方案咨询

核心问题分析 #

原查询中用%ins%的宽泛匹配会命中无关内容（如"Insane Cookies"），但添加48个前缀通配符的NOT LIKE会导致查询无法利用索引，大幅降低性能。以下是几种优化方案：

1. 精准匹配目标模式，避免过度宽泛的LIKE #

通过匹配独立单词边界来排除ins作为其他单词子串的情况，无需大量NOT LIKE：

• 使用PATINDEX匹配ins作为单词开头、或被空格分隔的情况，同时保留insurance的匹配：

SELECT
    a.Customer,
    b.Amount,
    c.Description
FROM
    table1 as a
    INNER JOIN table2 as b ON a.CustKey = b.AmtKey
    INNER JOIN table3 as c ON a.CustKey = c.DescKey
WHERE
    (
        -- 匹配开头是ins的单词
        PATINDEX('ins%', c.Description) > 0
        -- 匹配被空格/分隔符包围的ins
        OR PATINDEX('%[[:space:]]ins%', c.Description) > 0
        -- 匹配完整的insurance
        OR c.Description LIKE '%insurance%'
    )
    AND
    (
        b.Description NOT LIKE '%domains%' 
        AND b.Description NOT LIKE '%vitamins%' 
        AND b.Description NOT LIKE '%Gainsville%' 
        AND b.Description NOT LIKE '%circle k%'
        -- 无需再排除insane，因为上面的匹配规则已避开此类情况
    )

这种方式直接缩小了c.Description的匹配范围，自然减少了需要排除的无关项。

2. 启用全文索引（推荐大表场景） #

SQL Server的全文索引专门针对文本搜索优化，能高效处理单词边界匹配，完全替代宽泛的LIKE和大量NOT LIKE：

1. 先给table3的Description字段创建全文索引（需数据库已启用全文功能）；
2. 使用CONTAINS函数精准查找目标词汇：

SELECT
    a.Customer,
    b.Amount,
    c.Description
FROM
    table1 as a
    INNER JOIN table2 as b ON a.CustKey = b.AmtKey
    INNER JOIN table3 as c ON a.CustKey = c.DescKey
WHERE
    -- 匹配独立的ins或insurance单词
    CONTAINS(c.Description, ' "ins" OR "insurance" ')
    AND
    (
        b.Description NOT LIKE '%domains%' 
        AND b.Description NOT LIKE '%vitamins%' 
        AND b.Description NOT LIKE '%Gainsville%' 
        AND b.Description NOT LIKE '%circle k%'
    )

全文索引会自动忽略insane这类包含ins但并非独立单词的内容，且查询速度远快于LIKE。

3. 用临时表/表变量管理排除列表（仍需保留排除项时） #

如果必须保留对b.Description的排除规则，将所有排除关键词存入临时表，用LEFT JOIN替代多个NOT LIKE：

-- 创建临时表存储排除关键词
CREATE TABLE #ExcludeKeywords (Keyword NVARCHAR(50))
INSERT INTO #ExcludeKeywords 
VALUES ('domains'), ('vitamins'), ('Gainsville'), ('circle k')
-- 加入剩余44个关键词

SELECT
    a.Customer,
    b.Amount,
    c.Description
FROM
    table1 as a
    INNER JOIN table2 as b ON a.CustKey = b.AmtKey
    INNER JOIN table3 as c ON a.CustKey = c.DescKey
    -- 左连接排除表，匹配到的就是需要过滤的记录
    LEFT JOIN #ExcludeKeywords ek ON b.Description LIKE '%' + ek.Keyword + '%'
WHERE
    (
        PATINDEX('ins%', c.Description) > 0
        OR PATINDEX('%[[:space:]]ins%', c.Description) > 0
        OR c.Description LIKE '%insurance%'
    )
    -- 只保留未匹配到排除关键词的记录
    AND ek.Keyword IS NULL

DROP TABLE #ExcludeKeywords

这种方式将多个NOT LIKE合并为一次连接，逻辑更易维护，查询计划也更高效。

4. 过滤条件前置，减少连接数据量 #

先对table2和table3做过滤，再与table1连接，减少参与连接的行数：

WITH FilteredTable3 AS (
    SELECT DescKey, Description
    FROM table3
    WHERE
        PATINDEX('ins%', Description) > 0
        OR PATINDEX('%[[:space:]]ins%', Description) > 0
        OR Description LIKE '%insurance%'
),
FilteredTable2 AS (
    SELECT AmtKey, Amount
    FROM table2
    WHERE
        Description NOT LIKE '%domains%' 
        AND Description NOT LIKE '%vitamins%' 
        AND Description NOT LIKE '%Gainsville%' 
        AND Description NOT LIKE '%circle k%'
)
SELECT
    a.Customer,
    ft2.Amount,
    ft3.Description
FROM
    table1 as a
    INNER JOIN FilteredTable2 ft2 ON a.CustKey = ft2.AmtKey
    INNER JOIN FilteredTable3 ft3 ON a.CustKey = ft3.DescKey

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