萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 資料表分區建立完整步驟教學

分區儲存優勢

  • 大型資料表(百萬列以上)查詢/維護效能提升。
  • 資料依範圍分散不同檔案群組,可置於不同物理磁碟,平衡 I/O。
  • 邏輯上仍為單一表,應用程式無感調用。
  • 支援分區切換、歸檔、備份粒度控制。

建立分區資料庫與檔案群組

CREATE DATABASE PartitionDB
ON PRIMARY
(NAME = 'PartitionDB_Pri', FILENAME = 'D:\Data\PartitionDB_Pri.mdf', SIZE = 100MB, FILEGROWTH = 50MB),
FILEGROUP FG2020
(NAME = 'FG2020', FILENAME = 'E:\Data\FG2020.ndf', SIZE = 100MB, FILEGROWTH = 50MB),
FILEGROUP FG2021
(NAME = 'FG2021', FILENAME = 'F:\Data\FG2021.ndf', SIZE = 100MB, FILEGROWTH = 50MB),
FILEGROUP FG2022
(NAME = 'FG2022', FILENAME = 'G:\Data\FG2022.ndf', SIZE = 100MB, FILEGROWTH = 50MB),
FILEGROUP FG2023
(NAME = 'FG2023', FILENAME = 'H:\Data\FG2023.ndf', SIZE = 100MB, FILEGROWTH = 50MB)
LOG ON
(NAME = 'PartitionDB_Log', FILENAME = 'L:\Log\PartitionDB_Log.ldf', SIZE = 50MB, FILEGROWTH = 20MB);

重點:每個檔案群組對應獨立物理磁碟(D/E/F/G/H),最大化 I/O 平行度。

建立分區函數

-- 以日期為分區鍵,RANGE RIGHT 表示界限值屬於右側分區
CREATE PARTITION FUNCTION PF_Yearly (DATETIME)
AS RANGE RIGHT FOR VALUES ('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01');
  • 產生 5 個分區:<2020、2020~2021、2021~2022、2022~2023、≥2023。
  • RANGE LEFT 則界限值屬左側分區。

建立分區配置

CREATE PARTITION SCHEME PS_Yearly
AS PARTITION PF_Yearly
TO (PRIMARY, FG2020, FG2021, FG2022, FG2023, FG2023);  -- 最後一個分區同放 FG2023 或另建 FG_Future

建立分區資料表

CREATE TABLE dbo.PartitionedLog (
    LogID      BIGINT IDENTITY(1,1) NOT NULL,
    LogTime    DATETIME NOT NULL,          -- 分區欄位
    Message    NVARCHAR(MAX) NOT NULL,
    CONSTRAINT PK_PartitionedLog PRIMARY KEY NONCLUSTERED (LogID)  -- PK 不能在分區欄上除非包含分區欄
)
ON PS_Yearly(LogTime);  -- 指定分區配置與分區欄位

驗證分區分佈

SELECT
    p.partition_number,
    f.name AS filegroup_name,
    p.rows,
    prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
JOIN sys.filegroups f ON ds.data_space_id = f.data_space_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id
WHERE OBJECT_NAME(p.[object_id]) = 'PartitionedLog'
ORDER BY p.partition_number;

分區維護常用操作

  • 分割分區:ALTER PARTITION FUNCTION PF_Yearly() SPLIT RANGE ('2024-01-01');
  • 合併分區:ALTER PARTITION FUNCTION PF_Yearly() MERGE RANGE ('2020-01-01');
  • 切換分區(秒級歸檔):ALTER TABLE PartitionedLog SWITCH PARTITION 1 TO ArchiveLog PARTITION 1;

注意事項

  1. 分區欄位必須包含在所有唯一索引(含 PK)中。
  2. 外鍵參考分區表時,父表也需同樣分區對齊。
  3. SQL Server 2016+ 支援 TRUNCATE TABLE ... WITH (PARTITIONS (1,2))。
  4. 資料壓縮可逐分區設定:DATA_COMPRESSION = PAGE。
copyright © 萬盛學電腦網 all rights reserved