SQL Server带LIKE的DISTINCT查询有索引仍过慢，求优化方案

首先看你的查询，存在不少冗余条件，比如dr[.,][ !^]ab%和dr[., !^]ab%逻辑完全重复，还有_ ab%里的空格大概率是输入失误，先把查询精简，减少不必要的条件判断：

SELECT DISTINCT Doctor 
FROM YourTable 
WHERE Doctor LIKE 'ab%'
   OR Doctor LIKE 'dr[.,][ !^]ab%'
   OR Doctor LIKE 'dr[., !^]_ab%'
   OR Doctor LIKE 'mr[.,][ !^]ab%'
   OR Doctor LIKE 'mr[., !^]_ab%'
   OR Doctor LIKE 'ms[.,][ !^]ab%'
   OR Doctor LIKE 'ms[., !^]_ab%'

但核心问题是：除了ab%是前缀匹配，其他条件都是头衔（dr/mr/ms）+符号/空格+ab%的模式，这种带中间可变字符的LIKE无法利用普通非聚集索引的前缀匹配能力，导致数据库只能做全索引扫描，大数据集下自然慢。下面是几种针对性的优化方案：

1. 预处理生成清洗后的姓名列（最推荐） #

新增一个持久化计算列，把Doctor字段里的前缀头衔（dr/mr/ms及后续的点、空格）去掉，只保留核心姓名部分，然后给这个列建索引：

-- 添加持久化计算列，根据实际数据调整清洗规则
ALTER TABLE YourTable ADD CleanedDoctor AS 
    CASE 
        WHEN LOWER(LEFT(Doctor, 2)) IN ('dr','mr','ms') THEN
            LTRIM(
                STUFF(Doctor, 1, 
                    -- 处理带点（Dr.）或空格（Dr ）的情况
                    CASE WHEN SUBSTRING(Doctor, 3, 1) IN ('.', ' ') THEN 3 ELSE 2 END, 
                    ''
                )
            )
        ELSE Doctor
    END PERSISTED;

-- 给计算列建非聚集索引，INCLUDE(Doctor)避免回表
CREATE NONCLUSTERED INDEX IX_YourTable_CleanedDoctor ON YourTable(CleanedDoctor) INCLUDE(Doctor);

之后查询直接匹配清洗后的列，完全利用索引前缀匹配：

SELECT DISTINCT Doctor 
FROM YourTable 
WHERE CleanedDoctor LIKE 'ab%';

这样数据库可以通过索引快速定位所有符合条件的行，DISTINCT也能通过索引高效去重，性能会有质的提升。

2. 使用全文索引 #

SQL Server的全文索引对字符串模糊匹配的性能远优于LIKE，尤其适合大数据集。给Doctor列创建全文索引后，用CONTAINS做前缀查询：

-- 先启用数据库全文搜索（如果没开）
EXEC sp_fulltext_database 'enable';

-- 创建全文目录
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

-- 给Doctor列创建全文索引，替换PK_YourTable为你的表主键索引名
CREATE FULLTEXT INDEX ON YourTable(Doctor) KEY INDEX PK_YourTable;

-- 查询语句
SELECT DISTINCT Doctor 
FROM YourTable 
WHERE CONTAINS(Doctor, '"ab*"');

全文索引会自动拆分字符串中的单词，比如"Dr. Abraham"会被识别为包含"abraham"，所以搜索"ab*"会命中这条记录，刚好满足你的需求，查询速度会比LIKE快很多。

3. 缓存查询结果 #

自动补全的查询前缀（比如'ab'）重复请求率很高，把查询结果缓存起来可以彻底解决性能问题：

• 应用层可以用Redis、Memcached等缓存工具，把每个前缀对应的Doctor列表缓存起来，设置较短的过期时间（比如5分钟），兼顾实时性和性能。
• 也可以依赖SQL Server的查询缓存，但要确保查询语句完全一致，且数据更新后缓存会自动失效。

4. 改用索引视图 #

如果不想修改原表结构，可以创建索引视图提前维护好可用于自动补全的前缀数据：

-- 创建绑定架构的视图
CREATE VIEW vw_DoctorAutocomplete
WITH SCHEMABINDING
AS
SELECT DISTINCT 
    Doctor, 
    -- 取清洗后姓名的前10位作为前缀（可根据需求调整长度）
    LEFT(
        CASE 
            WHEN LOWER(LEFT(Doctor, 2)) IN ('dr','mr','ms') THEN
                LTRIM(STUFF(Doctor, 1, CASE WHEN SUBSTRING(Doctor,3,1) IN ('.',' ') THEN 3 ELSE 2 END, ''))
            ELSE Doctor
        END, 10
    ) AS SearchPrefix
FROM dbo.YourTable;

-- 创建聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_vw_DoctorAutocomplete_SearchPrefix ON vw_DoctorAutocomplete(SearchPrefix, Doctor);

查询时直接匹配前缀：

SELECT Doctor 
FROM vw_DoctorAutocomplete 
WHERE SearchPrefix LIKE 'ab%';

索引视图会在原表数据更新时自动同步，查询时直接利用聚集索引快速定位数据。

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