SQL Server 2005 表分區實戰：百萬級資料查詢效能幾何級提升

案例背景

專案後期資料突破 1000 萬筆，索引已建、查詢仍超時。分庫因關聯表過多、一對多關係複雜難以實施。發現 SQL Server 2005 表分區功能：邏輯上仍是單一表，物理上將資料依範圍分散至多個檔案群組/磁碟，查詢自動路由至相關分區，大幅降低掃描範圍。

實施步驟

1. 建立多個檔案群組（分散不同磁碟）

ALTER DATABASE [D] ADD FILEGROUP [GF1];

2. 為檔案群組新增實體檔案

ALTER DATABASE [D] ADD FILE (
    NAME = N'GF1',
    FILENAME = N'E:\D\D1\DGF1.ndf',
    SIZE = 5MB,
    FILEGROWTH = 10%
) TO FILEGROUP [GF1];

3. 建立分區函數（定義邊界值）

CREATE PARTITION FUNCTION [D_PARTITIONFUNC] (int)
AS RANGE LEFT FOR VALUES (200000, 400000, 500000);

RANGE LEFT 表示邊界值屬於左側分區。上例產生 4 個分區：ID ≤ 200000、200000 < ID ≤ 400000、400000 < ID ≤ 500000、ID > 500000。

4. 建立分區配置（將分區對應檔案群組）

CREATE PARTITION SCHEME [D_PARTITION_SCHEME]
AS PARTITION [D_PARTITIONFUNC]
TO ([PRIMARY], [GF1], [PRIMARY], [GF1]);

依序對應 4 個分區至檔案群組，實現多磁碟分佈。

5. 移除現有主鍵（分區鍵必須包含於主鍵）

ALTER TABLE dbo.B DROP CONSTRAINT [PK_B];

6. 重建主鍵並指定分區配置

ALTER TABLE dbo.B ADD CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED (ID)
ON [D_PARTITION_SCHEME](ID);

完成後，表 B 依 ID 自動分區儲存。查詢時優化器自動消除無關分區，百萬級甚至百億級資料查詢效能大幅提升。

後續擴充

• 分區合併/拆分：ALTER PARTITION FUNCTION ... SPLIT/MERGE RANGE。
• 查詢分區資料分佈：SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.B')。
• 滑動視窗場景（歷史資料歸檔）：配合 SWITCH 快速移出最舊分區。

注意事項

• 分區欄位建議為整數/日期等有序型別，避免字串分區導致統計資訊不準。
• 所有非叢集索引若需對齊分區，建立時需指定 ON partition_scheme(col)。
• 分區不改變應用程式 SQL，透明度極高。