您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 表分區完整指南：提升大數據查詢效能的關鍵技術

編輯：關於SqlServer

什麼是表分區？

表分區是 SQL Server 2005 引入的一項企業級功能，它將大型資料表或索引在邏輯上劃分為多個較小、更易管理的單元，稱為分區。每個分區可以獨立存儲在不同的文件組上，甚至分佈在不同的物理磁碟上，從而實現資料的水平切分。對於應用程式而言，分區表在邏輯上仍是一張完整的表，查詢和維護操作無需修改代碼即可透明受益。

核心優勢

查詢效能提升：透過分區剪枝，查詢優化器僅掃描相關分區，大幅減少 I/O 開銷。
維護靈活性：支援分區級別的匯入、匯出、重建索引、壓縮等操作，避免全表鎖定。
分級存儲：熱門資料置於高速 SSD，歷史冷資料移至廉價 HDD，優化存儲成本。
滾動窗口管理：輕鬆實現歷史資料歸檔與清理，僅需切換分區開關。

實作三步驟

1. 建立分區函數

CREATE PARTITION FUNCTION pf_OrderDate (DATETIME)
AS RANGE RIGHT FOR VALUES (
    '2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01'
);

定義分區邊界值，採用 RANGE RIGHT 表示邊界值屬於右側分區。

2. 建立分區配置

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg_Q1, fg_Q2, fg_Q3, fg_Q4, fg_History);

將各分區映射到預先建立的文件組，建議每個文件組位於獨立磁碟。

3. 建立分區表

CREATE TABLE Sales.Orders (
    OrderID     BIGINT IDENTITY,
    OrderDate   DATETIME NOT NULL,
    CustomerID  INT,
    Amount      MONEY,
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON ps_OrderDate (OrderDate);

注意：分區欄位必須包含在主鍵中，且為分區函數的輸入參數類型。

進階技巧

分區切換實現秒級歸檔

-- 建立暫存表，結構與目標分區完全一致
CREATE TABLE Sales.Orders_Staging (...) ON fg_Q1;

-- 載入新資料至暫存表
INSERT INTO Sales.Orders_Staging ...;

-- 秒級切換：元數據操作，無資料移動
ALTER TABLE Sales.Orders_Staging SWITCH TO Sales.Orders PARTITION 1;

分區對齊索引

所有非叢集索引建議採用相同分區配置（對齊索引），確保分區切換操作不受阻礙：

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Sales.Orders (CustomerID) ON ps_OrderDate (OrderDate);

監控與維護

定期檢視分區統計資訊：

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.partition_number,
    fg.name AS FileGroup,
    p.rows AS RowCount,
    au.total_pages * 8 / 1024.0 AS SizeMB
FROM sys.partitions p
JOIN sys.allocation_units au ON p.hobt_id = au.container_id
JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
WHERE p.object_id = OBJECT_ID('Sales.Orders');