Go MSSQL 分頁實作比較與建議

在 Go 語言開發後端並對接 MSSQL 時，分頁（Pagination）是一個非常經典的效能優化課題。對於 Cursor-based（指標式） 與 Offset-based（偏移量式，即你提到的下兩次 SQL） 的選擇，取決於你的業務場景（如：後台管理系統 vs. 無限捲動的 App）。

以下是詳細的比較、實作建議與常見坑點。

---

一、 分頁方案深度對比

1. Offset-based Paging (兩次 SQL 請求)

這是最常見的作法：先 SELECT COUNT(*) 算總數，再用 OFFSET / FETCH 取資料。

• 實作方式 (MSSQL 2012+)：SQL-- 第 1 次：拿總筆數 SELECT COUNT(*) FROM Orders WHERE Status = 1; -- 第 2 次：拿分頁資料 SELECT * FROM Orders WHERE Status = 1 ORDER BY OrderID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
• 優點：
  • 支援跳頁： 使用者可以直接點擊「第 5 頁」。
  • 前端友善： 容易計算總頁數、顯示剩餘頁數。
• 缺點：
  • 效能問題： 當 OFFSET 很大時（例如萬級跳頁），資料庫必須掃描並丟棄前面的資料，效能劇降。
  • 資料飄移： 在分頁過程中若有新資料插入，使用者可能會看到重複的資料。

2. Cursor-based Paging (Seek Method)

不使用偏移量，而是根據上一頁最後一筆資料的唯一標識（如 ID 或時間戳）來找下一頁。

• 實作方式：SQL-- 假設上一頁最後一個 ID 是 500 SELECT TOP 10 * FROM Orders WHERE Status = 1 AND OrderID > 500 ORDER BY OrderID ASC;
• 優點：
  • 高效能： 無論翻到多深，效能幾乎一致，因為它利用索引直接定位。
  • 穩定性： 不會因為資料插入/刪除導致看到重複或遺漏資料。
• 缺點：
  • 無法跳頁： 只能「下一頁」或「上一頁」，適合「載入更多」或無限捲動。
  • 排序限制： 排序欄位必須具有唯一性（或搭配主鍵排序）。

---

二、 比較總覽表

特性	Offset-based (2 SQLs)	Cursor-based (Seek)
隨機跳頁	支援	不支援
大數據效能	差 (隨著頁數增加變慢)	優 (效能穩定)
實作難度	簡單	中等 (需記錄最後一個 ID)
適用場景	管理後台、報表、小規模資料	App 瀑布流、大型日誌、百萬級資料
MSSQL 特效	OFFSET...FETCH 語法	依賴索引 B-Tree 搜尋

---

三、 其他實作解決辦法與第三方套件

在 Go 的生態系中，你不需要從零開始寫 SQL，可以利用以下套件處理：

1. GORM (最流行的 ORM)

GORM 內建了分頁的封裝，但通常是基於 Offset 的。

• Offset 實作： db.Limit(10).Offset(20).Find(&users)
• 問題： 它不會自動幫你做 COUNT(*)，你通常需要自己寫一個 Wrapper 函數跑兩次查詢。

2. Ent (Facebook 出品的強型別 ORM)

Ent 對於分頁的 API 設計非常優雅，支援較複雜的查詢組合。

• 優點： 程式碼生成技術讓你的分頁查詢在編譯期就受檢查。

3. SQLX

如果你喜歡原生 SQL，sqlx 是首選。它沒有自動分頁功能，但能輕鬆將結果 Map 到 Struct。

---

四、 實作中遇到的常見問題與坑

1. MSSQL 的 ORDER BY 限制

在 MSSQL 中，使用 OFFSET / FETCH 必須搭配 ORDER BY。如果你沒寫 ORDER BY，SQL 會報錯。這在 Go 寫動態查詢時常被忽略。

2. COUNT(*) 的效能殺手

當資料量達到千萬級，SELECT COUNT(*) 會變得非常慢。

• 解決： 如果不要求精確總數，可以從 MSSQL 的系統表（sys.partitions）讀取近似值，或者快取總數（Cache）。

3. Deep Paging (深分頁) 攻擊

惡意使用者或爬蟲可能會請求 OFFSET 1000000，這會瞬間拖垮資料庫 IO。

• 解決： 強制限制最大跳頁數，或者超過一定頁數後強制切換為 Cursor 模式。

4. N+1 問題

在 Go 實作分頁時，如果拿到分頁後又在 Loop 裡查詢關聯資料，會導致效能崩潰。

• 解決： 使用 JOIN 或 Preload (Eager Loading)。

---

五、 最終建議

1. 一般管理後台： 使用 Offset-based。雖然它有兩次 SQL，但在資料量小於 10 萬筆時，使用者體驗最佳（因為能跳頁）。你可以寫一個 Go Pagination Helper 函式來統一回傳 {total, page, data}。
2. 高流量 / 大數據 App： 務必使用 Cursor-based。
3. 效能折衷方案：
   • 先查出 ID 清單（Covering Index），再用 ID 去 IN 查資料。
   • SELECT COUNT(*) 加上快取（例如 Redis 存 5 分鐘），不需要每次翻頁都重新算總數。

---

從 MSSQL 切換到 MySQL 或 PostgreSQL，對於 Go 後端開發來說，最直接的影響在於 SQL 語法（Dialect）、參數佔位符以及底層驅動的行為。

針對你關心的「分頁實作」，以下是切換後的詳細影響分析：

---

一、 語法差異：分頁關鍵字

這是最明顯的改變。雖然邏輯不變，但 SQL 字串必須重寫。

資料庫	分頁語法 (Offset-based)	特性
MSSQL	OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY	必須搭配 ORDER BY 才能運作。
MySQL	LIMIT 5 OFFSET 10 (或 LIMIT 10, 5)	語法最簡潔，不強制要求 ORDER BY（但實務上強烈建議）。
PostgreSQL	LIMIT 5 OFFSET 10	語法與 MySQL 相同。

Cursor-based 的影響：

三者在 Cursor 模式下的 WHERE ID > ? LIMIT 10 語法幾乎完全一致。

---

二、 Go 程式碼層面的影響

1. SQL 參數佔位符 (Placeholders)

這是 Go 開發者最容易踩坑的地方，不同的驅動對參數的標示方式不同：

• MySQL: 使用問號 ?（例如：WHERE id = ?）
• PostgreSQL: 使用序號 $1, $2（例如：WHERE id = $1）
• MSSQL: 通常使用 @p1 或 ?（取決於驅動）

影響： 如果你寫的是原生 SQL，切換資料庫時，你必須修改所有的查詢字串。若使用 GORM 或 Ent，ORM 會自動幫你處理掉這個差異。

2. 驅動程式 (Drivers)

• MySQL: 常用 github.com/go-sql-driver/mysql。
• PostgreSQL: 推薦使用 github.com/jackc/pgx，效能與對功能（如 JSONB）的支援比舊的 lib/pq 好很多。

---

三、 效能與實作細節的影響

1. PostgreSQL 的 COUNT(*) 痛點

在分頁需要回傳「總筆數」時，PostgreSQL 的 COUNT(*) 通常比 MySQL 和 MSSQL 慢。

• 原因： 由於 PostgreSQL 的多版本並發控制 (MVCC) 機制，它必須掃描資料來確定哪些列對當前事務是「可見的」，無法像某些引擎一樣直接從元數據讀取。
• 解決： 在 PG 中，大數據量的分頁總數通常會改用 reliquary（估算值）或額外維護一個計數表。

2. MySQL 的大 Offset 效能

MySQL 在處理 LIMIT 100000, 10 時，效能損耗非常明顯。雖然這在所有 DB 都有發生，但 MySQL 的優化器在某些情況下處理「延遲關聯」（Late Row Lookups）的手法與 MSSQL 不同，需要手動優化。

3. 交易隔離級別 (Isolation Levels)

• MSSQL: 預設有時會發生讀寫鎖定衝突。
• PostgreSQL: 預設是 Read Committed，在處理高併發分頁時表現非常穩定。

---

四、 第三方套件的處理方案

如果你想讓系統在切換資料庫時更輕鬆，建議引入以下套件：

1. GORM (Object Relational Mapper)
   • 優點： 你只需更改初始化時的 gorm.Open(mysql.Open(dsn))，分頁程式碼 db.Offset(10).Limit(5) 完全不用動。
   • 缺點： 隱藏了底層細節，有時難以優化特定 DB 的特殊索引行為。
2. Masterminds/squirrel (SQL Builder)
   • 如果你不想用 ORM 但想解決佔位符問題，squirrel 可以幫你動態構建 SQL。
   • 它可以根據設定生成 ? 或 $1，讓你換 DB 時不用手改字串。
3. ozzo-dbx / sqlx
   • 輕量級封裝，雖然仍要寫 SQL，但簡化了將分頁結果 Map 到 Go Struct 的過程。

---

五、 總結建議

1. 如果你追求開發速度與靈活性： 切換到 PostgreSQL。它對進階資料型別（JSONB, Array）的支援極佳，且分頁效能穩定。Go 社群對 PG 的支持度（如 pgx 驅動）目前是最高的。
2. 如果你擔心遷移成本：
   • 若使用 ORM (GORM)：切換成本極低，只需調整配置。
   • 若使用 原生 SQL：建議先建立一個 QueryBuilder 層，將 LIMIT/OFFSET 的生成抽象化，否則手改數百個 ? 到 $1 會非常痛苦。
3. 分頁策略調整：
   • 無論換到哪種 DB，若資料量大，Cursor-based 永遠是首選。
   • 若必須用 Offset-based，請務必在切換後針對 COUNT(*) 進行壓力測試，尤其是 PostgreSQL。