元哥的筆記

每個月都會要求自己寫一些筆記或是文章之類

2026-04-16

用GPT 來學習架構場景-SQL SERVER 冷熱分離

用GPT 來學習架構場景

用 SQL Server 做「冷熱分離（Hot/Cold Data Separation）」其實是很實務、而且在高流量系統中很常見的設計。你可以把它理解為：

👉 熱門資料（Hot）放快、常用的地方
👉 冷資料（Cold）放便宜、慢一點但容量大的地方

我直接用「工程實務角度」幫你拆解 👇

一、為什麼要做冷熱分離？

常見場景：

1️⃣ 訂單系統

  • 最近 3 個月 → 熱資料（常查）
  • 1 年前 → 冷資料（幾乎不查）

2️⃣ Log / Audit

  • 最近 logs → 熱
  • 歷史 logs → 冷（甚至封存）

3️⃣ 金融 / 回測系統（你之前提過）

  • 最近行情 → 熱
  • 歷史 10 年 → 冷

二、SQL Server 常見 4 種做法（重點）

✅ 方案 1：分區表（Partition Table）👉 最推薦

這是最標準、最乾淨的方式

核心概念

OrderDate → 分區鍵

例如：

  • 2026 → Partition A（Hot）
  • 2025 → Partition B（Warm）
  • 2024↓ → Partition C（Cold）

👉 透過：

  • Partition Function
  • Partition Scheme

優點

  • 查詢自動只掃「熱資料」（Partition Pruning）
  • 可做「滑動窗口（Sliding Window）」
  • 可快速切換（Switch）

典型語法

CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES 
('2024-01-01', '2025-01-01', '2026-01-01');

✅ 方案 2：分表（Hot Table / Cold Table）👉 最簡單

結構

Orders_Current   (熱資料)
Orders_History   (冷資料)

查詢方式

SELECT * FROM Orders_Current
UNION ALL
SELECT * FROM Orders_History

或用 View 包裝：

CREATE VIEW Orders_All AS
SELECT * FROM Orders_Current
UNION ALL
SELECT * FROM Orders_History

優點

  • 好理解
  • 好控制
  • 適合中小型系統

缺點

  • 查詢要自己控
  • 索引要維護兩份

✅ 方案 3：檔案群組（Filegroup）👉 偏進階

核心概念

  • Hot → SSD Filegroup
  • Cold → HDD / 便宜儲存

PRIMARY (Hot)
FG_Archive (Cold)

搭配 Partition：

👉 最強組合：Partition + Filegroup

✅ 方案 4：資料庫分離（Database Split）👉 大型系統

架構

DB_OLTP       → 熱資料
DB_Archive    → 冷資料

查詢

SELECT * FROM DB_Archive.dbo.Orders

或：

  • Linked Server
  • ETL

三、實戰設計（推薦你用這個）

如果你現在做 ASP.NET Core API 👇

👉 我建議你：

⭐ 「分區 + 滑動窗口」設計

設計策略

  • 保留最近 3~6 個月在 Hot Partition
  • 舊資料自動切到 Cold Partition

搭配 Job

用 SQL Agent：

ALTER PARTITION FUNCTION ... SPLIT RANGE
ALTER PARTITION FUNCTION ... MERGE RANGE

👉 每月跑一次

四、冷熱分離 + API 設計（重點）

你系統要這樣設計：

API 層

GET /orders?date=2026 → 查 Hot
GET /orders?date=2023 → 查 Cold

或：

👉 完全透明（推薦）

  • DB 自動分區
  • API 不用知道

五、什麼時候該做？

✔ 建議做

  • 資料 > 1000萬筆
  • 查詢變慢
  • IO 壓力高

❌ 不用做

  • 小系統（< 100萬）
  • 查詢不頻繁

六、進階優化（你這種工程師會用到）

1️⃣ Columnstore Index（冷資料超強）

CREATE CLUSTERED COLUMNSTORE INDEX …

👉 適合：

  • 報表
  • 回測（你有提過）

2️⃣ 壓縮（Compression）

DATA_COMPRESSION = PAGE

👉 冷資料省空間

3️⃣ 歷史資料封存

  • 匯出 CSV
  • 或 Data Lake

4 種正確做法（依成熟度排序）

 方案 1：Partition SWITCH（最強、企業級）

👉 幾乎是「瞬間搬移」= Metadata 操作

核心概念

Orders (主表 - 分區)
Orders_Archive (冷資料表)

👉 把某個分區「切」到另一張表

 

條件（很重要）

兩張表必須：

  • 結構完全一樣
  • Index 完全一樣
  • Partition 對齊

實作流程

1️⃣ 建 Archive Table

CREATE TABLE Orders_Archive (...)

2️⃣ 切換分區

ALTER TABLE Orders
SWITCH PARTITION 1
TO Orders_Archive;

優點

  • 🚀 超快（幾乎 instant）
  • 🚀 不吃 IO
  • 🚀 不會鎖整張表

缺點

  • 設計複雜
  • 一開始要規劃好 Partition

 方案 2：分批搬移（Batch Migration）👉 最常用

👉 適合你現在「已經上線」的系統

核心策略

👉 一次搬「小量」，避免鎖表

範例

WHILE 1=1
BEGIN
    INSERT INTO Orders_History
    SELECT TOP (1000) *
    FROM Orders
    WHERE OrderDate < '2024-01-01'

    DELETE TOP (1000)
    FROM Orders
    WHERE OrderDate < '2024-01-01'

    IF @@ROWCOUNT = 0 BREAK

    WAITFOR DELAY '00:00:01' -- 降低壓力
END

關鍵優化

👉 一定要加：

✔ 索引（不然會全表掃描）

CREATE INDEX IX_OrderDate ON Orders(OrderDate)

✔ 小批次（1000~5000）

避免：

  • 鎖太久
  • log 太大

✔ 排程（SQL Agent）

  • 半夜跑
  • 每天搬一點

✅ 方案 3：INSERT SELECT + DELETE（進階版）

👉 用 Transaction 控制

BEGIN TRAN

INSERT INTO Orders_History
SELECT *
FROM Orders
WHERE OrderDate < '2024-01-01'

DELETE FROM Orders
WHERE OrderDate < '2024-01-01'

COMMIT

⚠️ 問題：

  • 會鎖表（大資料量）
  • 不適合 production

👉 👉 只適合「小資料量」

✅ 方案 4：ETL（企業級）

👉 用工具：

  • SSIS
  • Azure Data Factory

架構

OLTP (Hot)
   ↓
ETL Job
   ↓
Data Warehouse / Archive (Cold)

優點

  • 可做清洗
  • 可壓縮
  • 可轉格式（Columnstore）

三、推薦你用哪個？

根據你目前背景（.NET + API + 投資系統）👇

如果你「現在系統已經在跑」

👉 用：

✔ Batch Migration（方案2）

🟡 如果你「準備重構」

👉 用：

✔ Partition + SWITCH（方案1）

四、實戰最佳流程（給你直接用）

Step 1：建立冷資料表

CREATE TABLE Orders_History (
    Id INT,
    OrderDate DATE,
    Amount DECIMAL(10,2)
)

Step 2：建立索引

CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate)

Step 3：排程搬移

👉 每天跑：

EXEC MoveOldOrders

Step 4：Stored Procedure

CREATE PROCEDURE MoveOldOrders
AS
BEGIN
    SET NOCOUNT ON;

    WHILE 1=1
    BEGIN
        INSERT INTO Orders_History
        SELECT TOP (1000) *
        FROM Orders
        WHERE OrderDate < DATEADD(MONTH, -6, GETDATE())

        DELETE TOP (1000)
        FROM Orders
        WHERE OrderDate < DATEADD(MONTH, -6, GETDATE())

        IF @@ROWCOUNT = 0 BREAK

        WAITFOR DELAY '00:00:00.5'
    END
END

五、你一定要注意的坑（很重要）

❗ 1. FK（外鍵）

  • 會卡 DELETE
    👉 建議：
  • 先移子表
  • 或關 FK

❗ 2. Transaction Log

👉 一定要：

  • Full → 會爆 log
  • 建議：
    • SIMPLE mode（搬資料期間）

❗ 3. 重複資料

👉 建議加：

 

WHERE NOT EXISTS (...)

 

❗ 4. API 同時寫入

👉 要避免：

  • 搬資料時剛好寫入

👉 解法：

  • 用時間切（例如：只搬 6 個月前）

六、幫你總結一句話

👉 冷熱分離的資料轉移 =「小批次 + 可中斷 + 不鎖表」

七、幫你一句話總結

👉 冷熱分離的本質：讓「常用資料變快」，讓「不常用資料變便宜」

 

元哥的筆記

標籤雲

系列文章

本頁段落