萬盛學電腦網

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

SQL Server 分區表完整實作指南:從概念到建立、檔案群組規劃

什麼時候需要分區表?

當資料表符合以下條件時,應考慮分區:

  1. 資料量大:查詢明顯變慢(建議百萬列以上)
  2. 資料有明確分段特性:如按年/月分隔,熱資料(近期)頻繁讀寫,冷資料(歷史)僅查詢
  3. 操作集中於部分資料:不需掃描全表即可滿足大多數業務

分區表核心概念

  • 邏輯上:仍是單一資料表,應用程式無感知
  • 物理上:資料依分區函數分散至不同檔案群組/磁碟
  • 優勢:查詢裁剪、維護靈活(切換分區、歸檔)、I/O 平行化

建立分區表四步驟

步驟 1:建立檔案群組與檔案(建議)

SSMS → 資料庫屬性 → 檔案群組 → 新增 Sale2009、Sale2010、Sale2011、Sale2012、Sale2013

再於「檔案」頁籤新增對應 .ndf 檔案,指定路徑(最佳置於不同磁碟)。

步驟 2:建立分區函數

CREATE PARTITION FUNCTION partfunSale (DATETIME)
AS RANGE RIGHT FOR VALUES ('20100101', '20110101', '20120101', '20130101');
  • RANGE RIGHT:界值屬於右側分區(>= 界值)
  • 四個界值產生五個分區:
    1. < 2010-01-01
    2. 2010-01-01 ~ 2010-12-31
    3. 2011-01-01 ~ 2011-12-31
    4. 2012-01-01 ~ 2012-12-31
    5. >= 2013-01-01

步驟 3:建立分區配置

CREATE PARTITION SCHEME partschSale
AS PARTITION partfunSale
TO (Sale2009, Sale2010, Sale2011, Sale2012, Sale2013);

將分區函數的各分區對應至檔案群組。

步驟 4:建立分區表

CREATE TABLE Sale (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(16) NOT NULL,
    [SaleTime] DATETIME NOT NULL
) ON partschSale(SaleTime);

關鍵限制:分區欄位(SaleTime)必須包含於叢集索引鍵中。若表有叢集索引,分區欄位需為其前導欄;若無叢集索引,建表時指定 ON partschSale(SaleTime) 即可。

驗證分區表

SSMS → 資料表屬性 → 儲存體,可見「分區」資訊與分區號對應檔案群組。

總結

分區表將大表物理拆分、邏輯合一,透過「分區函數定義切點、分區配置對應儲存、建表指定分區欄位」三步走完成。配合檔案群組分散磁碟,能顯著提升大資料量環境的查詢與維護效能。

copyright © 萬盛學電腦網 all rights reserved