WHERE LOWER(col) = 'abc' 導致索引失效。本文介紹兩種「以空間換時間」的變通技巧:持久化計算欄位 + 索引 與 雜湊值替代鍵。
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 🎉
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) |
| 維護成本 | 高 (頁分裂、統計更新慢) | 低 |