負責 SQL Server 專案或初接觸者,常面臨效能調校難題。本文彙整實戰經驗,提供十大設計指導原則 (多數亦適用其他 DBMS)。
- 熟知你的工具
別輕視 T-SQL 指令與工具。花一個週末瀏覽所有 T-SQL 命令,建立心智索引:未來設計查詢時能想起「有個命令能做這事」,再查 MSDN 確認語法。
- 拒絕游標
游標是效能殺手:佔用記憶體、不可思議的鎖定行為、速度如蝸牛,並使 DBA 的優化失效。每次 FETCH 等同一次 SELECT,10,000 列游標 = 10,000 次 SELECT。改用集合式 SELECT/UPDATE/DELETE。
實例:10萬列游標存儲程序 40 分鐘 → 改寫集合式 10 秒。
- 正規化資料表
不正規化通常源於「效能迷思」或「懶惰」。DBMS 專為正規化設計,反正規化常導致更慢。堅持正規化設計 (至少 3NF)。
- 避免 SELECT *
明確列出所需欄位:
① 減少記憶體/網路頻寬
② 更安全 (不暴露敏感欄位)
③ 給查詢優化器機會利用覆蓋索引
- 瞭解資料操作模式
索引加速 SELECT 但拖慢 INSERT/DELETE (維護成本)。關鍵在於:你對這張表主要做什麼操作?含 WHERE 子查詢的 UPDATE/DELETE 也屬讀操作。
- 別給低選擇性欄位建索引 (如「性別」)
索引是將表依某標準分割。性別只有男/女,百萬列表僅分兩堆,毫無幫助且維護昂貴。索引欄位順序應依「相異值數量多寡」排序:姓名 > 縣市 > 性別。
- 善用交易
耗時查詢務必包在交易中,系統異常時可回滾,保護資料一致性。
- 小心死鎖
所有預存程序遵循固定鎖定順序 (如先鎖表 A 再鎖表 B)。若有程式反序鎖定,極易死鎖且難發現。
- 別一次開啟大量資料集
不要把 10 萬列塞進 ComboBox。使用者不會翻那麼多。改善 UI:分頁、搜尋、僅顯示前 100~200 筆。
- 避免伺服器端游標
用戶端游標減少伺服器/網路開銷與鎖定時間。
- 使用參數化查詢
單引號逸出問題 (如 'A'B) 不要用雙單引號替代,那是治標不治本,還會破壞查詢計畫快取。參數化查詢徹底解決注入、逸出、快取重用問題。
- 開發期使用大量真實資料測試
測試庫資料量小掩蓋效能問題。上線前必須在接近生產量級的資料下驗證。