优化含多列nvarchar类型LIKE操作的SQL查询

问题背景 #

执行涉及多列nvarchar类型的LIKE子串搜索查询时遇到性能瓶颈。需求是在三个关联表（关联路径：C→B→A）的多个Extra列中搜索子串，只要任一表匹配，就返回对应的Table A记录。其中Table A仅有3个可搜索列，Table B和C各有8个Extra列。

表结构 #

Table A
- Id
- ExtraOne
...
- ExtraThree

Table B
- Id
- ExtraOne
...
- ExtraEight
- A_Id (外键关联Table A)

Table C
- Id
- ExtraOne
...
- ExtraEight
- B_Id (外键关联Table B)

当前使用的查询语句 #

SELECT [t1].[id]
FROM
(
    SELECT DISTINCT [t0].[id]
    FROM
    (
        SELECT [b].[id]
        FROM [Table A] AS [b]
        WHERE
        (
            (
                ([b].[id] LIKE '%searchText%')
                OR ([b].[extraone] LIKE '%searchText%')
            )
            OR ([b].[extratwo] LIKE '%searchText%')
        )
        UNION
        SELECT [b0].[id]
        FROM [Table A] AS [b0]
        INNER JOIN [Table B] AS [c] ON [b0].[id] = [c].[A_Id]
        WHERE
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        [c].[id] LIKE '%searchText%'
                                    )
                                    OR ([c].[name] LIKE '%searchText%')
                                )
                                OR ([c].[extraone] LIKE '%searchText%')
                            )
                            OR ([c].[extratwo] LIKE '%searchText%')
                        )
                        OR ([c].[extrathree] LIKE '%searchText%')
                    )
                    OR ([c].[extrafour] LIKE '%searchText%')
                )
                OR ([c].[extrafive] LIKE '%searchText%')
            )
            OR ([c].[extrasix] LIKE '%searchText%')
        )
        UNION
        SELECT [b1].[id]
        FROM [Table A] AS [b1]
        INNER JOIN [Table B] AS [c0] ON [b1].[id] = [c0].[A_id]
        INNER JOIN [Table C] AS [d] ON [c0].[id] = [d].[B_id]
        WHERE
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        [d].[id] LIKE '%searchText%'
                                    )
                                    OR ([d].[name] LIKE '%searchText%')
                                )
                                OR ([d].[extraone] LIKE '%searchText%')
                            )
                            OR ([d].[extratwo] LIKE '%searchText%')
                        )
                        OR ([d].[extrathree] LIKE '%searchText%')
                    )
                    OR ([d].[extrafour] LIKE '%searchText%')
                )
                OR ([d].[extrafive] LIKE '%searchText%')
            )
            OR ([d].[extrasix] LIKE '%searchText%')
        )
    ) AS [t0]
) AS [t1]
ORDER BY [t1].[id]
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

已做优化及现状 #

已通过UNION替代左连接获得一定性能提升，但仍需进一步优化。另外发现，当FETCH NEXT行数设为100时，查询速度是设为10时的3倍。

需要解决的问题 #

1. 有哪些替代技术或优化手段可提升查询中LIKE操作的性能？
2. 是否应使用全文检索替代LIKE来搜索nvarchar列的子串？
3. 如何为查询条件涉及的列创建合适的索引以提升性能？

回答 #

1. LIKE操作的性能优化手段

• 避免前缀通配符：如果业务允许，优先使用'searchText%'而非'%searchText%'，前缀通配符会导致常规索引失效，无法利用索引加速查询。
• 简化OR条件嵌套：将多层嵌套的OR条件简化为扁平结构（比如a OR b OR c替代((a OR b) OR c)），降低查询优化器的解析复杂度。
• 拆分查询并行处理：把三个UNION分支拆分独立处理，先分别获取各表匹配的Table A主键，再合并去重，减少单查询的关联和过滤负载。
• 临时表存储中间结果：将各表中匹配的主键存入临时表，再通过临时表关联获取Table A记录，避免重复执行关联和过滤逻辑。
• 优化分页逻辑：如果id是自增主键，可先定位匹配记录的ID范围，再基于范围分页，避免全表扫描后再排序分页的高开销。

2. 全文检索是否替代LIKE？

非常推荐用全文检索替代前后通配的LIKE查询，核心原因：

• LIKE '%xxx%'会触发全表扫描，大表场景下性能极差；全文检索基于倒排索引，查询速度能提升数倍甚至数十倍。
• 主流数据库（如SQL Server、MySQL）都支持全文索引，可针对目标nvarchar列创建全文索引，使用CONTAINS或FREETEXT函数实现灵活搜索，支持子串、同义词等需求。
• 需注意调整分词规则，确保符合业务的子串搜索需求（比如部分数据库支持CONTAINS(*, '"*searchText*"')实现任意子串匹配）。

3. 针对查询的索引策略

• 常规索引的局限性：对于LIKE '%xxx%'，常规非聚集索引无法被利用，因为索引按前缀排序，前后通配无法定位索引范围。
• 首选：全文索引
  • 对Table A的Id、ExtraOne、ExtraTwo创建全文索引；
  • 对Table B的Id、Name、ExtraOne~ExtraSix创建全文索引；
  • 对Table C的Id、Name、ExtraOne~ExtraSix创建全文索引；
  • 确保外键列A_Id、B_Id有常规非聚集索引，加速关联查询。
• 无法使用全文检索时的替代方案
  • 列存储索引：适用于大表场景，对多列扫描的性能有明显提升；
  • 计算列+索引：将多个搜索列合并为一个计算列，对计算列创建索引（仅适用于固定多列组合搜索，且合并后列长度不能超限）；
  • 前缀匹配索引：如果业务能接受前缀匹配，对搜索列创建非聚集索引，仅'xxx%'模式可利用索引。

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