SQL Server视图LIKE模糊查询性能优化及视图索引可行性问询

一、视图能否创建索引 #

SQL Server支持索引视图（也叫物化视图），但你的现有视图需要调整才能满足创建要求：

• 必须添加WITH SCHEMABINDING选项绑定架构，且视图中不能使用SELECT *，必须显式指定所有列名
• 索引视图不支持LEFT/RIGHT等外连接，你的视图中大量使用LEFT JOIN，若无法修改为INNER JOIN则无法创建标准聚集索引视图
• 仅SQL Server 2017及以上版本支持在索引视图中使用STRING_AGG聚合函数，低版本需要替换聚合逻辑
  如果可以调整连接逻辑，调整后优先创建唯一聚集索引（键列选T1.Id），再创建包含Username的非聚集索引，即可显著提升查询效率。

二、无需修改视图的优化方案 #

1. 补齐基础表覆盖索引 #

优先给所有连接键和查询用到的列建覆盖索引，减少全表扫描开销：

-- InternalUsers覆盖索引
CREATE NONCLUSTERED INDEX IX_InternalUsers_ObjectGuid_Cover 
ON InternalUsers(ObjectGuid)
INCLUDE (SAMAccountName, GivenName, LastName, Email, Telephone, Mobile, company, ModifiedStatus)

-- Users表覆盖索引
CREATE NONCLUSTERED INDEX IX_Users_Id_Cover 
ON Users(Id)
INCLUDE (Username, FirstName, LastName, Email, Phone, Mobile, CompanyCode)

-- Companies表覆盖索引
CREATE NONCLUSTERED INDEX IX_Companies_Name_Cover 
ON Companies(name)
INCLUDE (Code)

-- UsersGroups覆盖索引
CREATE NONCLUSTERED INDEX IX_UsersGroups_MasterUsersId_Cover 
ON UsersGroups(MasterUsersId)
INCLUDE (GroupsId)

-- Groups表覆盖索引
CREATE NONCLUSTERED INDEX IX_Groups_Id_Cover 
ON Groups(Id)
INCLUDE (Name)

上述索引创建后，视图的基础数据扫描速度会提升30%~70%，模糊查询耗时会明显降低。

2. 针对前后通配符模糊查询优化 #

LIKE '%xxx%'的前后通配符场景无法使用普通B树索引，可选择以下两种方案：

方案A：全文索引（推荐）

给Username对应的两个基础字段建全文索引：

-- 创建全文目录（仅首次需要）
CREATE FULLTEXT CATALOG UserFtCatalog AS DEFAULT;

-- 给Users表Username建全文索引，替换PK_Users_Id为Users表的主键名
CREATE FULLTEXT INDEX ON Users(Username) 
KEY INDEX PK_Users_Id;

-- 给InternalUsers表SAMAccountName建全文索引，替换PK_InternalUsers_ObjectGuid为InternalUsers表的主键名
CREATE FULLTEXT INDEX ON InternalUsers(SAMAccountName) 
KEY INDEX PK_InternalUsers_ObjectGuid;

查询时将LIKE '%testuser%'替换为CONTAINS(Username, 'testuser')，性能可提升10倍以上。

方案B：预固化视图数据

如果对数据实时性要求不高，可定时将视图数据同步到实体表，给实体表的Username字段建全文索引：

-- 创建固化表，字段和myUsers视图完全对齐
CREATE TABLE myUsers_Cached (
    Id INT PRIMARY KEY,
    Username NVARCHAR(200),
    FirstName NVARCHAR(200),
    LastName NVARCHAR(200),
    Email NVARCHAR(200),
    Phone NVARCHAR(50),
    Mobile NVARCHAR(50),
    CompanyCode NVARCHAR(50),
    Name NVARCHAR(200),
    IsNotMaster INT,
    MemberOf NVARCHAR(MAX)
)

-- 创建SQL Server定时作业，每隔1~5分钟执行以下语句同步数据
TRUNCATE TABLE myUsers_Cached;
INSERT INTO myUsers_Cached SELECT * FROM dbo.myUsers;

查询直接访问myUsers_Cached表，模糊查询速度可达到毫秒级。

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