问题场景 #

你当前的需求是通过扫描词典，根据词条匹配命中情况计算得分，快速判断句子是否属于某词典对应的语言子集。原始实现采用笛卡尔积关联句子表和词典表，再计算得分筛选，但随着词典规模和句子数量增长，执行时间急剧上升：

原始表结构与查询代码

CREATE TABLE [dbo].[Sentences]( 
    [sentenceId] [int] IDENTITY(1,1) NOT NULL, 
    [sentence] [nvarchar](1000) NULL 
) ON [PRIMARY] 
GO 

CREATE TABLE [dbo].[Dictionary]( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [term] [varchar](20) NOT NULL, 
    [meaning] [varchar](1000) NOT NULL 
) ON [PRIMARY] 
GO 

-- 填充数据
GO 

WITH Test(id, score) AS ( 
    SELECT 
        sentenceId, 
        100 * SUM(SIGN(CHARINDEX(' ' + term + ' ', LOWER(sentence)))) / MAX(LEN(sentence) + 1) 
    FROM dbo.Sentences, dbo.Dictionary 
    GROUP BY sentenceId 
) 
SELECT COUNT(*) 
FROM dbo.Sentences JOIN Test ON id = sentenceId 
WHERE score > 1 
GO

当前性能表现

可以看到，当词典词条数从175涨到3500时，10000条句子的执行时间从13s飙升到233s，完全呈线性增长趋势。

优化方案 #

针对你提到的“得分超过1时无需扫描完整词典”的需求，结合SQL特性，这里提供几个实用的优化思路：

1. 用全文索引替代CHARINDEX，从根本提升匹配效率

CHARINDEX是逐字符扫描字符串，性能极低，尤其是大字符串和大词典场景。SQL Server的全文搜索专门针对文本匹配优化，能大幅降低匹配耗时。

步骤1：创建全文索引

-- 创建全文目录（如果没有的话）
CREATE FULLTEXT CATALOG ft_LanguageDetection AS DEFAULT;
GO

-- 给Sentences表的sentence字段创建全文索引（替换为你的主键名）
CREATE FULLTEXT INDEX ON dbo.Sentences(sentence) 
KEY INDEX PK_Sentences_sentenceId;
GO

-- 给Dictionary表的term字段创建全文索引（替换为你的主键名）
CREATE FULLTEXT INDEX ON dbo.Dictionary(term) 
KEY INDEX PK_Dictionary_id;
GO

步骤2：修改查询用全文搜索匹配

WITH SentenceMatches AS (
    SELECT 
        s.sentenceId,
        COUNT(DISTINCT d.id) AS matchCount,
        LEN(s.sentence) + 1 AS sentenceLength
    FROM dbo.Sentences s
    -- 用CONTAINS做精确词条匹配，确保是完整单词（加双引号）
    JOIN dbo.Dictionary d ON CONTAINS(s.sentence, '"' + d.term + '"')
    GROUP BY s.sentenceId, LEN(s.sentence) + 1
)
SELECT COUNT(*)
FROM SentenceMatches
WHERE 100.0 * matchCount / sentenceLength > 1

这个方案能把匹配效率提升一个数量级，尤其是大词典场景，全文搜索的优势会非常明显。

2. 预计算句子长度字段，减少重复计算

你的查询里每次都要计算LEN(sentence)+1，可以把这个值持久化到表中，避免每次查询都重复计算：

ALTER TABLE dbo.Sentences 
ADD SentenceLengthPlus1 AS LEN(sentence) + 1 PERSISTED;
GO

之后查询直接用SentenceLengthPlus1字段，能节省不少CPU开销，尤其是大表场景。

3. 实现“得分达标即停止匹配”的逻辑

SQL是集合式语言，原生没有“提前终止”的语法，但可以用递归CTE模拟这个逻辑：对每个句子，逐个匹配词典词条，累计得分，一旦得分超过1就停止继续匹配。

-- 先给词典生成连续序号（如果id不连续的话）
WITH NumberedDictionary AS (
    SELECT 
        term,
        ROW_NUMBER() OVER (ORDER BY id) AS seq
    FROM dbo.Dictionary
),
RecursiveMatch AS (
    -- 初始：每个句子匹配第一个词典词条
    SELECT 
        s.sentenceId,
        s.SentenceLengthPlus1,
        nd.seq,
        SIGN(CHARINDEX(' ' + nd.term + ' ', LOWER(s.sentence))) AS totalMatches,
        100.0 * SIGN(CHARINDEX(' ' + nd.term + ' ', LOWER(s.sentence))) / s.SentenceLengthPlus1 AS currentScore
    FROM dbo.Sentences s
    CROSS JOIN (SELECT TOP 1 term, seq FROM NumberedDictionary) nd
    UNION ALL
    -- 递归：如果当前得分未超过1，继续匹配下一个词条
    SELECT 
        rm.sentenceId,
        rm.SentenceLengthPlus1,
        nd.seq,
        rm.totalMatches + SIGN(CHARINDEX(' ' + nd.term + ' ', LOWER(s.sentence))),
        100.0 * (rm.totalMatches + SIGN(CHARINDEX(' ' + nd.term + ' ', LOWER(s.sentence)))) / rm.SentenceLengthPlus1
    FROM RecursiveMatch rm
    JOIN NumberedDictionary nd ON nd.seq = rm.seq + 1
    WHERE rm.currentScore <= 1
),
FinalResults AS (
    -- 提取每个句子第一个达标（得分>1）的记录
    SELECT 
        sentenceId,
        currentScore,
        ROW_NUMBER() OVER (PARTITION BY sentenceId ORDER BY seq) AS rn
    FROM RecursiveMatch
    WHERE currentScore > 1
    UNION ALL
    -- 处理所有词条匹配后仍未达标的句子
    SELECT 
        s.sentenceId,
        100.0 * SUM(SIGN(CHARINDEX(' ' + nd.term + ' ', LOWER(s.sentence)))) / s.SentenceLengthPlus1 AS currentScore,
        1 AS rn
    FROM dbo.Sentences s
    LEFT JOIN RecursiveMatch rm ON s.sentenceId = rm.sentenceId
    CROSS JOIN NumberedDictionary nd
    WHERE rm.sentenceId IS NULL
    GROUP BY s.sentenceId, s.SentenceLengthPlus1
)
SELECT COUNT(DISTINCT sentenceId)
FROM FinalResults
WHERE currentScore > 1

这个方案能减少不必要的匹配，尤其是那些很快就能达到得分阈值的句子，不用再扫描剩余的词典词条。

4. 分批次处理大表

如果句子数量特别大（比如百万级），可以分批次处理，避免一次性全表扫描带来的资源压力：

DECLARE @BatchSize INT = 1000;
DECLARE @LastSentenceId INT = 0;
DECLARE @TotalMatches INT = 0;

WHILE EXISTS (SELECT 1 FROM dbo.Sentences WHERE sentenceId > @LastSentenceId)
BEGIN
    WITH BatchMatches AS (
        SELECT 
            s.sentenceId,
            COUNT(DISTINCT d.id) AS matchCount
        FROM dbo.Sentences s
        JOIN dbo.Dictionary d ON CONTAINS(s.sentence, '"' + d.term + '"')
        WHERE s.sentenceId > @LastSentenceId 
          AND s.sentenceId <= @LastSentenceId + @BatchSize
        GROUP BY s.sentenceId
    )
    SELECT @TotalMatches = @TotalMatches + COUNT(*)
    FROM BatchMatches
    WHERE 100.0 * matchCount / s.SentenceLengthPlus1 > 1;

    SET @LastSentenceId = @LastSentenceId + @BatchSize;
END

SELECT @TotalMatches AS TotalMatchingSentences;

优先级建议 #

1. 优先部署全文索引优化，这是提升性能最显著的方案；
2. 配合预计算字段减少重复计算；
3. 如果词典规模极大，且大部分句子能快速达标，再考虑递归终止匹配的方案；
4. 超大规模表用分批次处理避免资源耗尽。

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