萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

資料庫效能調優前必問的 10 個關鍵問題:資料型別、索引、命名陷阱全解析

前言

資料庫調優常被視為「黑魔法」,實則多源於**設計階段的疏忽**。在動手建立索引、改寫 SQL、調參數前,先問自己這 10 個問題,能避開 80% 的效能陷阱。本文以 SQL Server 為主視角,原理通用於 PostgreSQL、MySQL、Oracle 等關聯式資料庫。

問題 1:是否真正需要 Unicode (nchar/nvarchar)?

  • 現狀:預設用 `NVARCHAR(MAX)` 或 `NCHAR`,「以防萬一」。
  • 代價:儲存空間雙倍 (2 bytes/字元)、索引體積增大、記憶體/IO 壓力倍增、CPU 比較成本高。
  • 行動:若系統**確定不需多國語言** (如純內部 ERP、台灣專用),全面改用 `VARCHAR`/`CHAR`。節省 50% 空間,索引深度降低,查詢加速明顯。

問題 2:`smalldatetime` 足夠了嗎?別濫用 `datetime`

型別範圍精度儲存適用場景
`smalldatetime`1900-01-01 ~ 2079-06-06分鐘4 bytes商務單據、Log、不需秒精度的業務時間
`datetime`1753-01-01 ~ 9999-12-313.33 ms8 bytes科學計算、歷史資料、跨世紀系統
`datetime2(n)`0001-01-01 ~ 9999-12-31100 ns6-8 bytes新專案首選 (SQL Server 2008+)
`date` / `time`僅日期/時間天 / 100ns3/3-5 bytes分離存儲更省空間

**原則**:選**最小滿足需求**的型別。

問題 3:`bit` vs `smallint` — 索引陷阱

  • `bit`:1 bit 儲存 (實際 1 byte 對齊)、**無法建立索引** (SQL Server 限制)。
  • `smallint` (或 `tinyint`):2 bytes,**可建立索引**、可統計分布、可參與 Join/Where 搜尋論述。
  • 若欄位常用於 `WHERE status = 1`、`JOIN ON status`,**請用 `tinyint`/`smallint`**。

問題 4:`bit` 欄位預設允許 NULL — 你想要三值邏輯嗎?

  • SQL Server `bit` 預設 `NULL` (三值:0、1、NULL)。
  • 應用層常假設只有 true/false,導致 `WHERE is_active = 1` 漏掉 NULL 行。
  • **建議**:明確宣告 `NOT NULL DEFAULT 0` 或 `1`,消除三值邏輯困擾。

問題 5:NULL 處理策略 — 禁止 NULL,改用空字串/預設值

  • `NULL` ≠ 空字串 ≠ 0。`NULL` 參與比較結果為 `UNKNOWN`,導致 `NOT IN` 子查詢陷阱、`COUNT(col)` 漏算。
  • **策略**:
    • 字串欄位:`NOT NULL DEFAULT ''`
    • 數值欄位:`NOT NULL DEFAULT 0`
    • 日期欄位:`NOT NULL DEFAULT '1900-01-01'` 或業務預設值
    • 真正「未知/不適用」才允許 NULL (如「離職日期」在職員工為 NULL)

問題 6:資料列大小是否超過 8060 bytes (頁面限制)?

  • SQL Server 資料頁 8KB (8060 bytes 可用)。單行超過會導致 **Row Overflow** (大值型別移至 LOB 頁面) 或 **無法建表**。
  • 計算公式:固定長度欄位總和 + 變長欄位偏移陣列 (2 bytes × 變長欄位數) + NULL 位圖 + 行版本資訊 (14 bytes)。
  • **對策**:垂直拆分大表 (常用欄位留主表,大欄位移副表 1:1)、改用 `VARCHAR(MAX)`/`VARBINARY(MAX)` 觸發 Off-row 存儲。

問題 7:善用「標準欄位」模板 — 減少重複定義錯誤

SSMS/工具 → 選項 → 標準欄位:預先定義通用欄位模板 (如 `CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()`, `UpdatedAt`, `CreatedBy`, `IsDeleted`)。新建表直接套用,確保審計欄位一致、命名統一、型別正確。

問題 8:是否需要 `rowversion` (Timestamp) 欄位?

  • `rowversion` (舊稱 `timestamp`):資料庫自動遞增的二進位版本號,**非時間**。
  • 用途:樂觀鎖定 (更新時 `WHERE rowversion = @oldVersion`)、資料同步/複寫偵測變更、ETL 增量抽取。
  • **每表建議加一欄** `RowVer ROWVERSION NOT NULL`,成本極低 (8 bytes),效益極大。

問題 9:叢集索引 (Clustered Index) 選對了嗎?

場景推薦叢集索引鍵理由
單一身分識別主鍵、頻繁點查Identity BIGINT (遞增)插入不分頁分裂、索引窄、非叢集索引鍵值小
多欄位複合鍵、範圍查詢多業務鍵 (如 `TenantId, Date, Id`)資料物理順序符合查詢模式,範圍掃描少 IO
高併發寫入、熱點頁競爭非遞增鍵 (UUID、雜湊) + 分區分散寫入熱點,但犧牲範圍查詢

**誤區**:「每個表都要有叢集索引」— 純 Log/暫存表、極小查閱表可考慮 Heap (無叢集索引),插入更快。

問題 10:預存程序命名用 `sp_` 開頭?效能殺手!

  • `sp_` 開頭預存程序會被 SQL Server 視為**系統程序**,優先在 `master` 資料庫查找,再查使用者資料庫。
  • 導致:額外鎖競爭、編譯計畫快取污染、權限檢查開銷、甚至誤執行系統同名程序。
  • **鐵律**:**絕不使用 `sp_`、`xp_`、`dt_` 等系統前綴**。採用 `usp_` (User Stored Procedure) 或業務前綴 (如 `prc_Sales_GetOrderDetails`)。

進階:SSW SQL Auditor 自動化審計

工具可檢測上述 10 項 + 100+ 規則 (命名慣例、索引碎片、統計資訊過期、缺失主鍵、隱含轉換、標量函數濫用等)。試用 20 次,價格合理,建議納入發布前檢核流程。

結語

資料庫效能 70% 決定於**設計階段** (型別、NULL、索引、命名),30% 在查詢調優。養成「建表前問 10 問」習慣,配合定期 `SQL Auditor` 掃描、執行計畫分析、索引維護計畫,才是治本之道。別等到上線慢查詢爆量才想起來加索引——那往往已經太晚。

copyright © 萬盛學電腦網 all rights reserved