萬盛學電腦網

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

SQL Server 存儲過程編寫規範與性能優化最佳實踐

適用對象

資料庫開發人員、大數據量專案、關注 SP 性能優化的工程師。

核心原則

規範編寫降低維護成本;優化可帶來數百倍效能差距。開發提交前必須以 SET SHOWPLAN ON 分析查詢計劃並自查優化。

編寫規範與優化要點

A. SQL 使用規範

  1. 避免大事務、慎用 HOLDLOCK,提升並發。
  2. 避免重複訪問大表,先按條件導入臨時表再連接。
  3. 避免游標;超過 1 萬行必須改寫;游標中禁用表連接。
  4. WHERE 條件順序依索引順序、選擇性從高到低排列。
  5. 不在 WHERE 左側對欄位做函數/運算,否則索引失效。
  6. 存在性判斷用 EXISTS 代替 COUNT(1);COUNT(1) 優於 COUNT(*)。
  7. 優先用 >= 而非 > (利用索引求值差異)。
  8. OR 與 UNION 互換評估,UNION ALL 避免去重開銷。
  9. 連接欄位資料類型必須一致,避免隱式轉換導致索引失效。
  10. 參數與欄位類型匹配,防止隱式轉換。
  11. 單次 INSERT/UPDATE 超過 200 頁 (400KB) 會觸發頁鎖升級為表鎖,需分批處理。

B. 索引規範

  1. 大型 OLTP 表索引不超過 6 個,結合應用場景建立。
  2. 優先在查詢條件使用索引欄位,特別是叢集索引;必要時 INDEX(index_name) 強制指定。
  3. 避免大表 Table Scan,評估新增索引成本。
  4. 複合索引必須在條件中使用第一欄位,否則索引不被使用。
  5. 定期重建索引、重新編譯存儲過程 (sp_recompile),消除統計資訊過期導致的計劃劣化。

C. tempdb 使用規範

  1. 減少 DISTINCT、ORDER BY、GROUP BY、HAVING、JOIN、COMPUTE 等加重 tempdb 負擔的操作。
  2. 避免頻繁建刪臨時表,減輕系統表爭用。
  3. 大量資料一次性插入臨時表用 SELECT INTO (最小日誌);小量資料用 CREATE TABLE + INSERT 緩解系統表壓力。
  4. 大臨時表需建索引時,將建表與建索引放入獨立子存儲過程,確保索引統計資訊生效。
  5. SP 結束時顯式清理臨時表:TRUNCATE TABLE → DROP TABLE,避免長時間鎖定系統表。
  6. 謹慎進行大臨時表與大表的連接/修改,會在單語句中多次存取 tempdb 系統表。

D. 算法優化與調優工具

結合 SET STATISTICS IO ON、SET STATISTICS TIME ON、SET SHOWPLAN ON 等命令,多算法比對,選擇資源消耗最低、效率最高的方案。

copyright © 萬盛學電腦網 all rights reserved