SQL Server索引优化咨询：用户访问报表查询的索引设计建议

现有表结构 #

Company (
    Id,
    Name
)

User (
    Id,
    Name,
    CompanyId
)

UserAccess (
    Id,
    UserId,
    Date,
    Type /* 'Login' or 'Logout' */
)

目标查询语句 #

DECLARE @CompanyId int = 1;

SELECT
    u.Name,
    ua.*
FROM
    UserAccess ua
INNER JOIN 
    User u ON ua.UserId = u.Id
WHERE
    u.CompanyId = @CompanyId

两种方案的利弊分析与优化建议 #

方案1：仅给UserAccess建以UserId为键的非聚集索引

这个索引能帮数据库快速通过UserId定位对应的访问记录，但查询仍需关联User表过滤CompanyId。如果User表的CompanyId字段没有索引，数据库得全表扫描User表找目标公司的用户，数据量大时效率会明显下降。

方案2：给UserAccess冗余CompanyId并建复合索引

这种做法确实能跳过User表关联，直接从UserAccess过滤目标公司的记录，但要承担数据冗余的维护成本：一旦用户的CompanyId变更，你必须同步更新该用户在UserAccess表中的所有关联记录，写操作开销会大幅增加。如果用户的所属公司经常变动，这个方案反而会拖垮整体性能。

更优的折中方案

1. 先给User表建覆盖索引，覆盖CompanyId过滤及查询所需字段：

CREATE NONCLUSTERED INDEX IX_User_CompanyId ON User(CompanyId) INCLUDE (Id, Name);

这个索引能让数据库快速定位目标公司的所有用户，且直接获取Id和Name，无需回表查询User的主键索引。

2. 给UserAccess建覆盖索引，基于UserId并包含查询所需的所有字段：

CREATE NONCLUSTERED INDEX IX_UserAccess_UserId ON UserAccess(UserId) INCLUDE (Id, Date, Type);

查询时，数据库会先通过User表的索引拿到目标公司的用户列表，再通过UserAccess的索引快速定位这些用户的所有访问记录，全程无需回表，性能最优。

特殊场景下的冗余方案

只有当用户的CompanyId几乎从不变更，且报表查询频率极高、对性能要求极致时，才考虑将CompanyId冗余到UserAccess表，建(CompanyId)或(CompanyId, Date)的覆盖索引（若报表常按时间排序）。但必须提前评估好数据同步的成本，比如通过触发器或业务代码维护冗余字段的一致性。

内容的提问来源于stack exchange，提问作者Alberto Zambrano Green