首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式碼之家 >> 數據庫知識 >  >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 性能優化:以計算欄位實現函數索引、以雜湊值模擬 Hash 索引

     編輯:關於SqlServer

痛點背景

  • SQL Server **不支援函數索引** (Function-based Index):WHERE LOWER(col) = 'abc' 導致索引失效。
  • **寬欄位/多欄位連接** 導致索引過大、維護成本高、查詢效率低。

本文介紹兩種「以空間換時間」的變通技巧:持久化計算欄位 + 索引 與 雜湊值替代鍵。

技巧一:計算欄位實現「函數索引」效果

步驟

  1. 建表時定義 **PERSISTED 計算欄位**
  2. 在計算欄位上建立索引
  3. 查詢時直接針對計算欄位條件

範例

CREATE TABLE TestFunctionIndex (
    id INT IDENTITY(1,1),
    val VARCHAR(50),
    subval AS LOWER(SUBSTRING(val, 10, 4)) PERSISTED  -- 持久化計算欄位
)
GO
CREATE INDEX idx_subval ON TestFunctionIndex(subval)
GO
-- 插入 10 萬筆測試資料
INSERT INTO TestFunctionIndex(val) VALUES (NEWID())
GO 100000

驗證

  • 直接對原欄位用函數:WHERE LOWER(SUBSTRING(val,10,4)) = 'abcd' → **Index Scan**
  • 對計算欄位查詢:WHERE subval = 'abcd' → **Index Seek** ✅

驚喜發現

若計算欄位有索引,**原欄位使用相同運算式查詢時,優化器可能自動匹配計算欄位索引**!

WHERE LOWER(SUBSTRING(val,10,4)) = 'abcd'  -- 也能用到 idx_subval 🎉

技巧二:雜湊值替代長字串/多欄位鍵 (模擬 Hash Index)

適用場景

  • 查詢/連接條件為 **長字串** (如公司全名 NVARCHAR(200))
  • **多欄位組合** 連接 (如 a.key=b.key AND a.type=b.type AND a.status=b.status ...)

實作:單一長欄位

CREATE TABLE testHashColumn (
    id INT IDENTITY(1,1),
    QueryName NVARCHAR(100),
    HashName AS CAST(HASHBYTES('MD2', QueryName) AS UNIQUEIDENTIFIER) PERSISTED
)
GO
CREATE INDEX idx_HashName ON testHashColumn(HashName)
GO
  • HASHBYTES('MD2', ...) 產生 16 bytes 雜湊
  • CAST(... AS UNIQUEIDENTIFIER) 轉為 16 bytes GUID,索引極窄
  • 查詢時:WHERE HashName = CAST(HASHBYTES('MD2', @input) AS UNIQUEIDENTIFIER)

關鍵:確保唯一性

  • CHECKSUM **極易碰撞** (不同字串產生相同值),**不可用於唯一鍵替代**
  • HASHBYTES (MD5/SHA1/SHA2) + UNIQUEIDENTIFIER 碰撞機率極低,適合替代

實作:多欄位組合鍵

-- 於兩張表同樣定義
HashKey AS CAST(HASHBYTES('SHA1', 
    CONCAT(key, '|', type, '|', status, '|', creationTime)) AS UNIQUEIDENTIFIER) PERSISTED

連接改為:ON a.HashKey = b.HashKey,單一窄索引替代寬複合索引。

效能與權衡

指標原始寬索引雜湊替代索引
索引寬度寬 (數百 bytes)固定 16 bytes
空間佔用大小 (約 1/10~1/20)
查詢邏輯直觀需計算雜湊值
碰撞風險無極低 (SHA1+GUID)
維護成本高 (頁分裂、統計更新慢)低

總結

  • **計算欄位 + 索引** = 變相函數索引,解決 `WHERE func(col) = ...` 索引失效問題。
  • **雜湊值替代鍵** = 變相 Hash 索引,解決長字串/多欄位連接索引過大問題。
  • 核心原則:**以空間換時間**、**確保替代鍵唯一性**、**持久化計算欄位 (PERSISTED)** 避免重複運算。
  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式碼之家 All Rights Reserved