复合主键搭配单/多非聚集索引的选型及租户用户表登录索引疑问

先理清楚当前的基础结构：你的User表复合主键应该是PRIMARY KEY CLUSTERED (TenantId, UserId)吧？这个设计没问题——把TenantId放在聚集索引首位，保证同租户的用户数据物理聚集，对租户内的批量操作、权限查询非常友好。

现在核心问题是登录场景不需要输入TenantId，直接用Email（或其他登录字段）查询，这时候聚集索引派不上用场（因为没法利用前缀TenantId过滤），所以得针对性设计非聚集索引来优化登录性能。

核心方案：针对登录字段创建覆盖式非聚集索引 #

根据登录逻辑的不同，分两种情况：

1. 全局唯一的登录字段（推荐）

如果你的系统要求Email（或用户名）是全局唯一的（毕竟登录门户非租户专属，相同邮箱对应多个租户会导致登录歧义），直接创建唯一非聚集覆盖索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_User_Email_Unique 
ON dbo.[User] (Email)
INCLUDE (TenantId, UserId, PasswordHash, [LoginStatus, LastLoginTime -- 其他登录验证/后续流程需要的字段])

• 为什么这么设计？
  • 用Email作为索引键：登录查询的条件是WHERE Email = @InputEmail，这个索引能直接快速定位到唯一行，避免全表扫描。
  • INCLUDE字段：把登录时需要读取的所有字段（比如验证密码用的PasswordHash，生成令牌需要的UserId、TenantId）都包含进来，这样查询时不需要回表查聚集索引，直接从非聚集索引就能拿到所有数据，也就是「覆盖查询」，性能拉满。
  • 唯一约束：既保证了数据完整性（不会出现重复邮箱），又让索引的查找效率更高。

2. 允许跨租户重复的登录字段

如果业务上允许不同租户有相同的Email（比如两个租户下都有john@doe.com），那不能加唯一约束，但索引结构类似：

CREATE NONCLUSTERED INDEX IX_User_Email 
ON dbo.[User] (Email)
INCLUDE (TenantId, UserId, PasswordHash, [TenantName -- 用于让用户选择租户的字段])

这种情况下，登录时会返回多条记录，你需要在登录界面增加租户选择的逻辑，但这种场景其实不太常见——毕竟用户登录时大概率不想选租户，所以还是推荐全局唯一的登录字段设计。

补充说明 #

• 不要把TenantId放到非聚集索引的键里：登录时你不知道租户ID，加进去反而会增大索引体积，降低查询效率，放在INCLUDE里就足够了。
• 多登录方式的处理：如果支持用户名、手机号等其他登录方式，同理为每个登录字段创建对应的覆盖索引即可。
• 索引维护：用户表属于写入较频繁的表（注册、修改邮箱等），定期检查索引碎片，必要时重建或重组索引，保证查询性能。

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