在 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 模糊查询的优化核心，是“避免全表扫描”和“减少无效数据处理”。实际开发中，无需追求“最优方案”，而是根据“查询格式、数据量、读写频率”选择适配方案：
- 前缀匹配优先用 联合索引；
- 任意模糊匹配优先用 全文索引；
- 读多写少场景可用 数据冗余；
- 大数据量结果必加 分页查询。
通过本文方案，可将百万级数据的多字段模糊查询速度从秒级优化至毫秒级，满足业务高性能需求。