萬盛學電腦網

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

百萬級資料查詢優化 30 大黃金法則

面對百萬級甚至億級資料量,查詢效能優化是系統穩定的關鍵。本文彙整 30 條實戰驗證的優化鐵律,涵蓋索引、查詢改寫、架構設計、維護策略。

索引與查詢條件優化

  1. 避免全表掃描:WHERE、ORDER BY 涉及欄位必建索引
  2. 拒絕 IS NULL 判斷:導致索引失效 → 設定預設值 0,改查 num = 0
  3. 避免 !=/<>:引擎放棄索引 → 改寫邏輯或使用聯合索引
  4. 慎用 OR:導致掃描 → 拆分為 UNION ALL 多查詢合併
  5. 慎用 IN/NOT IN:連續值用 BETWEEN,離散值考慮 EXISTS 或臨時表
  6. 前置萬用字元殺手:LIKE '%abc%' 必掃描 → 改用全文檢索或 LIKE 'abc%'
  7. 變數參數陷阱:WHERE col = @var 編譯時未知值 → 強制索引 WITH (INDEX(idx_name)) 或 OPTION (RECOMPILE)
  8. 拒絕欄位運算:WHERE num/2=100 → 改 WHERE num=200
  9. 拒絕欄位函數:SUBSTRING(name,1,3)='abc' → name LIKE 'abc%';DATEDIFF(day, dt, '2024')=0 → dt >= '2024-01-01' AND dt < '2024-01-02'
  10. 複合索引最左原則:查詢必須包含索引第一欄位,順序一致

查詢寫法優化

  1. 拒絕無意義查詢:SELECT ... WHERE 1=0 建空表 → 用 CREATE TABLE
  2. EXISTS 替代 IN:EXISTS (SELECT 1 FROM B WHERE B.id = A.id) 通常優於 IN
  3. 索引非萬能:欄位區分度低(如性別)索引無效,優化器會選擇掃描
  4. 索引適度原則:單表索引 ≤ 6 個,過多拖慢寫入
  5. 避免更新叢集索引鍵:導致資料物理重排,資源極大

資料型別與儲存優化

  1. 優先數值型別:字串比較逐字元,數值一次比較
  2. VARCHAR 取代 CHAR:節省空間,掃描更快
  3. 禁用 SELECT *:明確列出欄位,減少 I/O 與網路傳輸

暫存表與表變數策略

  1. 優先表變數:小資料量驻留記憶體,但無統計資料、僅主鍵索引
  2. 避免頻繁建刪暫存表:消耗系統表資源
  3. 大量資料用 SELECT INTO:最小化記錄,提速;小量用 CREATE TABLE + INSERT
  4. 程序結束顯式清理:TRUNCATE TABLE → DROP TABLE,避免系統表長時間鎖定

游標與集合導向思維

  1. 能用集合操作絕不用游標:FAST_FORWARD 游標僅適合小資料集
  2. 先找 Set-based 解法:通常比逐列處理快數量級
  3. 結果集含總計:用 ROLLUP/GROUPING SETS 替代游標累加

交易與網路優化

  1. SET NOCOUNT ON:程序/觸發器首尾加上,抑制 DONE_IN_PROC 訊息
  2. 縮小交易範圍:減少鎖定時間,提升並發
  3. 避免傳回大量資料:分頁、摘要、非同步載入

優化無終點,唯有持續監控、分析執行計畫、針對瓶頸精準施策。

copyright © 萬盛學電腦網 all rights reserved