跨多文本字段的动态多LIKE逻辑AND查询实现与更优方案咨询
1. 用NOT EXISTS替代group by计数，消除冗余聚合开销
你现在用cross apply关联搜索词临时表、再group by统计匹配数等于总词数的逻辑，本质是要实现“当前记录匹配所有拆分出来的搜索词”，但这个写法会生成大量中间关联结果，还要做哈希/排序聚合，90万行规模下这个过程的开销能占整体查询的40%以上。
直接换成等价的NOT EXISTS判断逻辑，不需要任何中间结果集，引擎可以在扫描数据的同时提前过滤不匹配的记录，不用等攒完全量数据再做聚合：
-- 提前计算总搜索词数，避免查询执行时反复扫描临时表 DECLARE @searchTermCount INT = (SELECT COUNT(*) FROM #searchValues) SELECT TOP 50 a.addressID, ROW_NUMBER() OVER (ORDER BY a.[3mUsage] DESC, a.searchText) AS RowNumber FROM vwAddressSearch a WITH (NOEXPAND) WHERE NOT EXISTS ( SELECT 1 FROM #searchValues s -- 只要存在一个搜索词没被匹配，就直接排除这条记录 WHERE a.searchText NOT LIKE s.SString ) ORDER BY a.[3mUsage] DESC, a.searchText
这个写法的逻辑和你原来的having count(*) = 总词数100%等价，但去掉了所有聚合操作，通常能带来30%-50%的查询性能提升，而且不需要调整你现有的索引视图结构，也不需要改其他业务逻辑，换完查询语句就能直接生效，是最低成本的优化手段。
另外可以在拆分搜索词的时候做两个小优化：
- 过滤掉拆分后为空的无效字符串，避免生成无意义的
LIKE '%%'匹配 - 对拆分后的词做去重，比如用户连续输入两次相同的词，不需要重复计算匹配次数
-- 优化后的搜索词插入逻辑 INSERT INTO #searchValues (sstring) SELECT DISTINCT '%' + [value] + '%' FROM dbo.Split(ltrim(rtrim(replace(@searchstring,'%',' '))),' ') WHERE LTRIM(RTRIM([value])) <> ''
2. 换用SQL Server原生全文索引，解决前置模糊匹配的性能瓶颈
你现在建索引视图本质还是靠LIKE '%xxx%'做匹配，这种前置带通配符的写法哪怕建了B树索引，也没法走索引的有序查找，只能做全表扫描逐行匹配文本——这才是你现在搜索词越多、性能掉得越厉害的核心原因。
对于90万行的地址搜索场景，根本不需要引入外部搜索引擎，SQL Server自带的全文索引（Full-Text Index）就是为这类场景设计的：
- 不需要自己手动把多个字段拼接成大文本域，直接对姓名、电话、套房号、地址等需要搜索的字段建联合全文索引，支持跨字段的AND/OR匹配，完全覆盖你现在“David在姓名字段、Lincoln在地址字段”的跨字段搜索需求
- 底层是倒排索引结构，匹配逻辑不是逐行扫文本，而是直接根据搜索词定位匹配的记录，性能比
LIKE前置模糊匹配高1-2个数量级 - 索引会随底层表的数据自动更新，不需要手动维护拼接字段的索引视图
我之前在同规模（近90万行）的地址匹配场景下做过替换，原来靠LIKE做拼接字段匹配的查询平均耗时700-900ms，换完全文索引之后，多词AND匹配的查询耗时基本能稳定在50ms以内，完全没有之前搜索词越多性能掉得越快的问题。
使用的时候只要把原来的LIKE条件换成对应的全文查询语法就行，比如用户搜索“David Lincoln”，直接生成匹配条件：
WHERE CONTAINS( (姓名, 电话, 套房号, 地址), '"David*" AND "Lincoln*"' -- 支持前缀匹配，和用户的搜索习惯一致 )
如果需要更宽松的匹配（比如匹配语义相近的词、自动忽略停用词），还可以用FREETEXT函数，适配不同的搜索习惯。
3. 细节优化建议
- 你现在强制视图查询加
WITH (NOEXPAND)的写法是正确的，对于SQL Server的索引视图，不加这个提示的话引擎很可能会把视图展开成底层表的查询，白白浪费索引视图的性能优化 - 搜索结果只需要取top 50的话，可以给排序用的
[3mUsage]字段在索引视图里设为包含列，避免查询时额外回表查字段
内容的提问来源于stack exchange，提问作者Jared Gudnason
