在 SQL Server 开发中，多字段  OR  模糊查询（如  WHERE 字段1 LIKE '%关键词%' OR 字段2 LIKE '%关键词%' ）是高频需求，但此类查询常因“无法有效使用索引”导致全表扫描，尤其在百万级数据量下，查询速度会显著下降。本文将从性能瓶颈根源出发，提供 5 种可落地的优化方案，结合实例说明每种方案的适用场景与实现细节。

一、先搞懂：多字段 OR 模糊查询为何慢？

多字段  OR  模糊查询性能差的核心原因，在于 SQL Server 执行计划的局限性，具体表现为两点：

前缀模糊匹配（%关键词）无法使用索引：SQL Server 的 B 树索引仅支持“前缀匹配”（如  LIKE '关键词%' ），若模糊查询以  %  开头（如  %关键词  或  %关键词% ），索引会失效，触发全表扫描；

OR 逻辑导致索引选择困难：即使多个字段单独建立了索引， OR  连接的多字段查询也难以让 SQL Server 同时使用多个索引（需通过“索引合并”优化，但触发条件苛刻），最终仍可能走全表扫描。

示例慢查询（假设  Users  表有 100 万数据， UserName  和  Email  为普通字段，无特殊优化）：

-- 需求：查询用户名或邮箱包含“zhang”的用户

SELECT UserID, UserName, Email

FROM Users

WHERE UserName LIKE '%zhang%' OR Email LIKE '%zhang%';

执行计划会显示“聚集索引扫描”（全表扫描），百万级数据下查询耗时可能超过 5 秒。

二、5 种优化方案：从索引到SQL改写，按需选择

针对多字段  OR  模糊查询，需根据“数据量、查询频率、业务场景”选择优化方案，以下按“优化效果优先级”排序，逐步讲解实现方式。

方案 1：创建“多字段联合非聚集索引”（仅适用于前缀模糊匹配）

若业务允许将模糊查询改为 前缀匹配（如  LIKE 'zhang%' ，即“关键词开头”），创建“多字段联合非聚集索引”是最优选择——索引可直接被使用，避免全表扫描。

适用场景

- 模糊查询为“前缀匹配”（非  %关键词  或  %关键词% ）；

- 查询字段固定（如仅需匹配  UserName  和  Email ）。

实现步骤

创建联合非聚集索引：针对查询的多个字段，创建包含“查询字段+返回字段”的联合索引（覆盖索引，避免“书签查找”额外开销）；

-- 索引包含：查询字段（UserName, Email）+ 返回字段（UserID）

CREATE NONCLUSTERED INDEX IX_Users_UserName_Email

ON Users (UserName, Email)

INCLUDE (UserID); -- INCLUDE 包含查询需要返回但无需索引排序的字段

优化后的查询（前缀匹配）：

SELECT UserID, UserName, Email

FROM Users

WHERE UserName LIKE 'zhang%' OR Email LIKE 'zhang%';

效果验证

执行计划会显示“索引查找”（而非全表扫描），百万级数据下查询耗时可从 5 秒降至 100ms 以内。

注意

若必须使用“中间/后缀模糊匹配”（如  %zhang%  或  %zhang ），此方案无效——B 树索引不支持此类匹配的索引查找。

方案 2：用 UNION ALL 替换 OR（适用所有模糊匹配类型）

 OR  逻辑会让 SQL Server 难以优化，而  UNION ALL  可将“多字段查询”拆分为“多个单字段查询”，每个单字段查询可独立使用各自的索引，最终合并结果（无去重开销，比  UNION  更快）。

适用场景

- 多字段模糊匹配（支持  %关键词%   %关键词  等任意类型）；

- 每个查询字段已单独建立索引。

实现步骤

为每个查询字段创建独立非聚集索引：

-- 为 UserName 创建索引（包含返回字段）

CREATE NONCLUSTERED INDEX IX_Users_UserName

ON Users (UserName)

INCLUDE (UserID, Email);

-- 为 Email 创建索引（包含返回字段）

CREATE NONCLUSTERED INDEX IX_Users_Email

ON Users (Email)

INCLUDE (UserID, UserName);

用 UNION ALL 改写查询：拆分  OR  为两个独立查询，再合并结果；

-- 拆分后：先查 UserName 匹配，再查 Email 匹配，合并结果

SELECT UserID, UserName, Email

FROM Users

WHERE UserName LIKE '%zhang%'

UNION ALL -- 用 UNION ALL 而非 UNION，避免去重的性能损耗（若有重复数据需用 UNION）

SELECT UserID, UserName, Email

FROM Users

WHERE Email LIKE '%zhang%'

AND UserName NOT LIKE '%zhang%'; -- 过滤已在第一个查询中匹配的记录，避免重复

效果验证

执行计划会显示“两个索引扫描”（而非全表扫描），百万级数据下查询耗时可从 5 秒降至 1-2 秒（具体取决于匹配数据量）。

关键优势

- 支持任意类型的模糊匹配，无查询格式限制；

- 每个子查询独立使用索引，比  OR  逻辑的全表扫描效率高 3-5 倍。

方案 3：使用全文索引（最佳模糊匹配方案，支持多字段）

若业务需要频繁进行“多字段任意位置模糊匹配”（ %关键词% ），全文索引是 SQL Server 原生提供的最优解——它专为“文本内容检索”设计，支持多字段联合检索，查询速度比  LIKE  快 10-100 倍。

适用场景

- 多字段任意位置模糊匹配（ %关键词% ），且查询频率高；

- 字段类型为字符串类型（如  VARCHAR 、 NVARCHAR ），内容为文本（非随机字符）。

实现步骤（分 3 步）

步骤 1：启用数据库全文搜索（若未启用）

-- 检查数据库是否启用全文搜索

SELECT DATABASEPROPERTYEX('你的数据库名', 'IsFullTextEnabled');

-- 结果为 1 表示已启用，0 则执行以下语句启用

EXEC sp_fulltext_database 'enable';

步骤 2：为表创建全文目录和全文索引

全文索引需依赖“全文目录”（类似索引的容器），且需指定“全文键”（通常为主键，如  UserID ）：

-- 1. 创建全文目录（若不存在）

CREATE FULLTEXT CATALOG FT_Catalog_Users

WITH ACCENT_SENSITIVITY = OFF; -- 不区分重音（根据需求调整）

-- 2. 为 Users 表创建全文索引（包含 UserName 和 Email 字段）

CREATE FULLTEXT INDEX ON Users (

    UserName LANGUAGE 'Chinese_PRC', -- 字段 1，指定中文语言（优化分词）

    Email LANGUAGE 'English'         -- 字段 2，指定英文语言

)

KEY INDEX PK_Users_UserID -- 全文键（必须是表的唯一索引，通常为主键）

ON FT_Catalog_Users       -- 关联的全文目录

WITH CHANGE_TRACKING AUTO; -- 自动跟踪数据变化（新增/修改/删除时同步更新全文索引）

步骤 3：用全文检索函数改写查询

全文索引不支持  LIKE ，需使用  CONTAINS  或  FREETEXT  函数，其中  CONTAINS  支持精确匹配，更适合业务场景：

-- 需求：查询 UserName 或 Email 包含“zhang”的用户

SELECT UserID, UserName, Email

FROM Users

WHERE CONTAINS((UserName, Email), 'zhang'); -- (字段1,字段2) 指定多字段，'zhang' 为检索关键词

效果验证

执行计划会显示“全文索引查找”，百万级数据下查询耗时可从 5 秒降至 100ms 以内，且支持关键词分词（如检索“张三”可匹配“张三丰”“张三”）。

注意事项

- 全文索引有“同步延迟”（默认自动跟踪，延迟通常在秒级），不适合“实时性要求极高”的场景（如订单实时检索）；

- 若字段内容为短字符（如手机号、ID 号），全文索引优势不明显，建议用其他方案。

方案 4：数据冗余：新增“联合检索字段”（适合读多写少场景）

若查询频率远高于数据更新频率（读多写少，如博客文章检索、商品搜索），可通过“数据冗余”优化：新增一个“联合字段”，存储多个查询字段的拼接内容，仅对该字段创建索引，实现“单字段模糊查询替代多字段 OR”。

适用场景

- 读多写少（如数据每天更新 1 次，查询每秒数十次）；

- 多字段内容可拼接（无敏感信息，且拼接后不影响检索逻辑）。

实现步骤

步骤 1：新增联合检索字段

-- 为 Users 表新增字段：存储 UserName + Email 的拼接内容

ALTER TABLE Users

ADD SearchField NVARCHAR(500) -- 长度需覆盖两个字段的最大长度之和

CONSTRAINT DF_Users_SearchField

DEFAULT ''; -- 默认值为空

步骤 2：初始化/同步联合字段数据

-- 1. 初始化已有数据

UPDATE Users

SET SearchField = UserName + '|' + Email; -- 用“|”分隔字段，避免关键词跨字段匹配（如“zhang”同时在 UserName 和 Email 中）

-- 2. 新增触发器，确保数据更新时同步 SearchField

CREATE TRIGGER TR_Users_UpdateSearchField

ON Users

AFTER INSERT, UPDATE

AS

BEGIN

    SET NOCOUNT ON;

    UPDATE u

    SET u.SearchField = i.UserName + '|' + i.Email

    FROM Users u

    INNER JOIN inserted i ON u.UserID = i.UserID;

END;

步骤 3：为联合字段创建索引并改写查询

-- 为 SearchField 创建非聚集索引（若用前缀匹配，索引有效；若用%关键词%，仍需全索引扫描，但仅扫描一个字段）

CREATE NONCLUSTERED INDEX IX_Users_SearchField

ON Users (SearchField)

INCLUDE (UserID, UserName, Email);

-- 优化后的查询：单字段模糊匹配替代多字段 OR

SELECT UserID, UserName, Email

FROM Users

WHERE SearchField LIKE '%zhang%';

效果验证

即使使用  %zhang% ，查询也仅需扫描“SearchField”一个字段的索引（而非全表），百万级数据下查询耗时可从 5 秒降至 1 秒左右。

关键优势

- 实现简单，无需学习全文索引等复杂特性；

- 读操作效率高，适合高频查询场景。

方案 5：分页查询+延迟加载（缓解大数据量返回压力）

若查询结果需返回大量数据（如匹配结果有 10 万条），即使优化了查询本身，“数据传输和渲染”仍会耗时。此时可通过“分页查询”减少单次返回数据量，配合“延迟加载”提升前端体验。

适用场景

- 查询结果数据量大（如超过 1000 条）；

- 前端支持分页展示（如表格分页、滚动加载）。

实现步骤（结合方案 2/3/4 使用）

以“方案 3 全文索引”为例，添加分页逻辑：

-- 分页查询：每页返回 20 条，查询第 2 页（第 21-40 条）

SELECT UserID, UserName, Email

FROM (

    -- 子查询添加行号，按 UserID 排序（确保分页顺序稳定）

    SELECT

        UserID, UserName, Email,

        ROW_NUMBER() OVER (ORDER BY UserID) AS RowNum

    FROM Users

    WHERE CONTAINS((UserName, Email), 'zhang')

) AS T

WHERE T.RowNum BETWEEN 21 AND 40; -- 分页条件

效果验证

单次查询仅返回 20 条数据，网络传输时间从秒级降至毫秒级，前端渲染速度显著提升。

三、优化方案选型指南：按场景匹配最优解

业务场景 推荐方案 优势 劣势

前缀模糊匹配（zhang%） 方案 1（联合索引） 索引利用率最高，速度最快 不支持中间/后缀模糊匹配

任意模糊匹配+查询频率高 方案 3（全文索引） 速度最快，支持多字段分词检索 有同步延迟，配置稍复杂

任意模糊匹配+读多写少 方案 4（联合检索字段） 实现简单，无同步延迟 增加数据冗余，写操作有额外开销

任意模糊匹配+无索引权限 方案 2（UNION ALL） 无需新增索引，仅改写 SQL 性能比全文索引差，需单独索引

结果数据量大+分页展示 方案 5（分页查询） 缓解前端压力，提升体验 需配合其他方案使用，不能单独优化

四、避坑指南：这些错误做法会让优化失效

不要在模糊查询中使用函数处理字段：如  WHERE LOWER(UserName) LIKE '%zhang%' ，会导致索引失效，需提前将字段内容转为小写存储（或在索引中包含小写字段）；

不要过度依赖“索引合并”：SQL Server 对  OR  逻辑的索引合并支持有限，即使多字段有索引，也可能优先选择全表扫描，不如  UNION ALL  稳定；

全文索引不要滥用短字符字段：如手机号（ 138xxxx1234 ），全文索引的分词逻辑对短字符无效，反而不如  LIKE  或普通索引；

数据冗余字段要同步更新：若忘记创建触发器或定时任务，会导致“联合检索字段”与原字段数据不一致，查询结果出错。

五、总结

SQL Server 多字段  OR  模糊查询的优化核心，是“避免全表扫描”和“减少无效数据处理”。实际开发中，无需追求“最优方案”，而是根据“查询格式、数据量、读写频率”选择适配方案：

- 前缀匹配优先用 联合索引；

- 任意模糊匹配优先用 全文索引；

- 读多写少场景可用 数据冗余；

- 大数据量结果必加 分页查询。

通过本文方案，可将百万级数据的多字段模糊查询速度从秒级优化至毫秒级，满足业务高性能需求。

阅读原文：原文链接