SQL Server 分頁查詢優化：ROW_NUMBER() 深分頁 CPU 高的改寫方案

原查詢痛點

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY a.product_id DESC) AS rn, ...
    FROM ...  -- 多表關聯、NOT IN 子查詢
) t WHERE rn > 4170 AND rn <= 4200

rn > 4170 導致引擎必須排序前 4170 行再丟棄，深分頁 CPU 線性增長。

方案一：Keyset Pagination (Seek Method) —— 推薦

-- 首頁
SELECT TOP 30 * FROM ... ORDER BY product_id DESC;

-- 後續頁：記住上一頁最後一條的 product_id
SELECT TOP 30 * FROM ...
WHERE product_id < @lastSeenId  -- 關鍵：利用索引直接定位
ORDER BY product_id DESC;

• 無 ROW_NUMBER、無排序全表
• 利用聚簇索引/覆蓋索引 INDEX (product_id DESC) INCLUDE (...) 實現 Index Seek
• 不支援跳頁（只能上一頁/下一頁），但體驗更流暢

方案二：覆蓋索引 + ROW_NUMBER (若必須跳頁)

CREATE NONCLUSTERED INDEX IX_Product_Paging
ON Product_Info (Product_ID DESC)
INCLUDE (Product_Name, Product_BuyNum, Class_ID, SubShop_ID, IsHidden, IsDelete, Is_Audit, Is_AllShow);

使 ROW_NUMBER() OVER (ORDER BY Product_ID DESC) 只掃描索引葉級，不回表，CPU 大幅下降。

方案三：避免 NOT IN / 使用 NOT EXISTS

-- 原
AND a.Product_ID NOT IN (SELECT product_id FROM SubShop_Product WHERE ...)

-- 優化
AND NOT EXISTS (
    SELECT 1 FROM SubShop_Product s
    WHERE s.Product_ID = a.Product_ID
      AND s.IsHidden = 1 AND s.SubShop_ID = 1057
)

NOT EXISTS 可利用索引，NOT IN 遇 NULL 會全表掃描。

綜合建議

1. 業務允許 → 改用 Keyset Pagination（無跳頁）
2. 必須跳頁 → 建立覆蓋索引 + 保留 ROW_NUMBER
3. 重寫子查詢 → NOT EXISTS / LEFT JOIN ... WHERE IS NULL
4. 統計信息更新 → UPDATE STATISTICS 確保優化器選擇正確計劃