建立複合索引的順序

前晚許志學顧問分享了一個須注意的 primary key 結合 clustered index 的案例。例如多對多關係時，鍵值順序如何？

CREATE TABLE A(A1 INT PRIMARY KEY)
CREATE TABLE B(B1 INT PRIMARY KEY)

CREATE TABLE C(A1 INT,B1 INT PRIMARY KEY(A1,B1))
還是
CREATE TABLE C(A1 INT,B1 INT PRIMARY KEY(B1,A1))

ALTER TABLE C ADD CONSTRAINT FK_A FOREIGN KEY(A1) REFERENCES A(A1)
ALTER TABLE C ADD CONSTRAINT FK_B FOREIGN KEY(B1) REFERENCES B(B1)

例如：A 代表訂單，B 代表消費原因，一張訂單可能有多種消費原因，一種消費原因可能發生在多張訂單上，則 A1 是訂單編號，B1 是消費原因編號

我贊同他的觀點並加上自己的想法：

• 若考慮大量資料新增，則哪個資料表常新增，則依它的新增順序讓該欄位排前面，避免 page split
• 若以查詢而言，則越多記錄的，相較起來值越唯一的放在前面，大量重複的資料不適合當索引
• C table 不一定要採用複合鍵當主鍵，仍可以另建 identity 欄位當主鍵

若設計一層層展開的子 table，每更深一層的子 table 都加上父 table 的 PK 再加一欄子 table 有意義的欄位；當作子 table 的 PK。則原理相同，通常建複合主鍵也是叢集索引時，依然是該子 table 的流水號放第一個欄位。但，我依然會嘗試另建子 table 欄位內的 identity 流水號當作 PK 與叢集索引。避免重集索引太大，且意義多，容易需要變動。