複合主鍵 - 索引兩個欄位？

我在使用者和角色之間有一個多對多的連接表。

CREATE TABLE AppUserRole 
(
   UserId INT NOT NULL,
   RoleId INT NOT NULL, 

   CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId), 

   CONSTRAINT FK_AppUserRole_User FOREIGN KEY (UserId) 
       REFERENCES AppUser(UserId), 
   CONSTRAINT FK_AppUserRole_Role FOREIGN KEY (RoleId) 
       REFERENCES AppRole(RoleId)
);

CREATE INDEX IX_AppUserRole_RoleId ON AppUserRole(RoleId)
CREATE INDEX IX_AppUserRole_UserId ON User(UserId)

UserId既然複合鍵是按 ( , )的順序聚集的RoleId，那麼第二個索引UserId真的有必要嗎？

當您查詢特定UserId的所有行時，它將使用聚集索引。只有當您查詢特定的所有使用者時，RoleId才會使用索引IX_AppUserRole_RoleId。

索引是否IX_AppUserRole_UserId必要？

查詢按列順序使用索引（首先是 UserId，然後是 RoleId）。如果沒有 RoleId 上的索引，它將掃描聚集索引。除非存在帶有 userId 的 where 子句，否則引擎不知道如何在不掃描所有 userId 的情況下進入索引。

因為 roleId 是一個 FK，所以最佳實踐建議在其上創建一個索引。如果您（經常）查詢它，您肯定需要它。

僅對 UserId 的索引是沒有用的。聚集索引已經這樣做了，因為 UserId 是入口點。使用它不需要額外查找 PK 即可獲取數據或關聯的 roleId。只是聚集索引搜尋。

引用自：https://dba.stackexchange.com/questions/108924