萬盛學電腦網

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

SQL Server 自動化分區管理方案:固定分區循環復用與切換機制

設計目標

使用固定編號分區 (01~05) 循環儲存遞增資料 (自增 ID 或日期分區)。最舊分區滿時,自動切出清空,釋放空間供新資料復用。適用條件:資料遞增、可接受歷史資料移出、單日增量 ≤ 分區容量閾值。

核心架構圖解

整體概念圖:資料流經分區函數分配至分區,後台作業循環復用分區

  • 分區對齊:索引建立時指定相同分區配置,資料與索引同步移動。
  • PRIMARY 檔案群組:作為切換分區的暫存區,預先配置 ≥ 單分區大小空間。

自動化切換邏輯流程

自動化設計邏輯流程圖

  1. 讀取分區管理表 PartitionManage 判斷是否達預警值。
  2. 在 PRIMARY 建立臨時表 (同分區配置)。
  3. ALTER TABLE ... SWITCH PARTITION n TO TempTable PARTITION n 瞬間切換 (中繼資料操作,極快)。
  4. 清空臨時表資料 (或保留為歷史表)。
  5. 修改分區函數 MERGE RANGE 移除舊邊界,SPLIT RANGE 加入新邊界 (順序:先改方案再改函數)。
  6. 更新管理表狀態與時間。

分區管理表設計

PartitionManage 表結構

欄位用途
Change_Value預警 ID 值,達此值觸發切換
Part_Value分區函數邊界值
FileGroup_String分區檔案群組名稱 (可腳本自動生成)
IsDone狀態 (0=待處理, 1=已完成)
UpdateTime最後切換時間

靈活應對:修改預警值提早切換、調整分區間隔、跳級檔案群組、批次標記歷史分區 IsDone=1。

生產環境分區分佈實測

ID 分區記錄分佈:各分區筆數持平,第20分區因未及時切換明顯偏多

ClassId 分類分區:記錄數不均,需前期規劃邊界值平衡分佈

關鍵 T-SQL 片段

-- 切換分區到臨時表 (中繼資料操作,秒級)
ALTER TABLE [dbo].[BigTable] SWITCH PARTITION @PartitionNum TO [dbo].[Temp_BigTable] PARTITION @PartitionNum;

-- 修改分區方案 (指定下一個分區用哪個檔案群組)
ALTER PARTITION SCHEME [PS_Name] NEXT USED [FG_New];

-- 合併舊邊界 / 分割新邊界
ALTER PARTITION FUNCTION [PF_Name]() MERGE RANGE (@OldBoundary);
ALTER PARTITION FUNCTION [PF_Name]() SPLIT RANGE (@NewBoundary);

排程與監控

  • SQL Server Agent 作業每日/每小時執行檢查預存程序。
  • 監控 sys.partitions 記錄數與 PartitionManage 狀態。
  • 告警:分區切換失敗、檔案群組空間不足、遞增值逼近 BIGINT 上限。
copyright © 萬盛學電腦網 all rights reserved