SQL Server 排序取數據的性能陷阱與優化

常見問題場景

-- 問題：大表排序後取前 N，無索引支持
SELECT TOP 100 * FROM Orders ORDER BY CreatedAt DESC;
-- 執行計劃：Table Scan + Sort (內存/TempDB 溢寫)

核心優化原則

1. 索引覆蓋排序 (最有效)

-- 查詢模式: ORDER BY CreatedAt DESC
-- 最優索引: INDEX(CreatedAt DESC) INCLUDE (所需列)
-- 實現: 掃描索引葉級即可有序輸出，避免 Sort 算子

2. 避免 SELECT * 強制回表

-- 差: SELECT * → Key Lookup (每行回表)
-- 優: SELECT Id, CreatedAt, Amount → 覆蓋索引
-- 極致: 僅需排序列 + 主鍵 → 最窄索引

3. 分頁查詢優化 (OFFSET FETCH / Keyset)

-- 深分頁慢: OFFSET 100000 ROWS FETCH NEXT 20
-- 優化: Keyset 分頁 (記住上一頁最後排序值)
WHERE CreatedAt < @lastDate OR (CreatedAt = @lastDate AND Id < @lastId)
ORDER BY CreatedAt DESC OFFSET 0 ROWS FETCH NEXT 20;

TempDB 壓力排查

-- 監控排序溢寫
SELECT 
    session_id,
    request_id,
    query_plan_hash,
    used_memory_kb,
    max_used_memory_kb
FROM sys.dm_exec_query_memory_grants
WHERE grant_time > DATEADD(minute, -10, GETDATE())
  AND used_memory_kb > 10000;  -- 超過 10MB 排序內存

統計信息與參數嗅探

• 大表排序前執行 UPDATE STATISTICS 確保基數準確
• 參數化查詢可能導致計劃不穩，考慮 OPTION (RECOMPILE) 或強制索引提示

實戰案例：訂單列表優化

-- 原查詢: 500萬行，TOP 20 耗時 8s
SELECT TOP 20 o.Id, o.UserId, o.Amount, o.Status, o.CreatedAt
FROM Orders o
WHERE o.Status = 1
ORDER BY o.CreatedAt DESC;

-- 優化索引
CREATE INDEX IX_Orders_Status_CreatedAt 
ON Orders (Status, CreatedAt DESC) 
INCLUDE (Id, UserId, Amount);

-- 結果: 索引 Seek + 有序輸出，耗時 < 10ms

避坑清單

1. ❌ ORDER BY RAND() / NEWID() 全表掃描排序
2. ❌ 字符串排序未指定 Collation 導致隱式轉換
3. ❌ 多列排序順序與索引不匹配 ORDER BY a ASC, b DESC 索引 (a, b)
4. ❌ 大偏移量 OFFSET 不配合 Keyset 改造