普通的SQL Server應用程序要求支持一個或幾個長字符串搜索。(本文中,我們稱超過20個字符的字符串為長字符串。)假如前端應用程序希望允許用戶提供兩個字符串;你啟動一個執行這兩個字符串搜索任務的存儲程序,然後應用兩個相關列目錄對搜索進行優化。在小型的表格中,你可能注意不到產生的效果。但是,如果表格包含5 000萬行,它就會影響存儲程序與搜索性能。
應用稱為hash關鍵字(引用單獨一個hash)或hash桶(一個hash關鍵字集合)的字符串目錄的優秀方法可大大節省磁盤空間並提高性能。
何為hash(hash)
hash是應用一個指定字符串算法的整數結果。有各式各樣的hash算法,但最常用的是內置的SQL函數Checksum()。通常,你給這個函數一個字符串,它就返回一個整數(在大型表格中,我們不能保證這個整數的唯一性)。
數據庫設計中的hash表格
假設在我們感興趣的表格中有這些列:
列名 數據類型 名稱 Varchar(50) 組名稱 Varchar(50)
這兩個列的多列目錄每行會耗用50+50個字符,加上上面提到的5 000萬行,這可是個相當大的難題。
基於這兩個列的hash關鍵字相當的小,即每行四個字節。如果我們不將hash關鍵字存儲在這一列的目錄中,它還會更小。相反,我們應該建立一個計算列,該列的公式是這兩個列的hash關鍵字,然後將那個列編入目錄並忽視字符串對的目錄。
用戶(不管是人還是應用程序)查詢感興趣的值;然後我們將參數轉換為hash關鍵字並搜索hash目錄。副本集合要比引擎必須訪問的行集合小得多,以便對查詢值進行精確匹配。然後將hash關鍵字搜索與兩個感興趣的列的比較結合起來,隔離出一個小型的行子集,並對兩個列進行檢驗,找出匹配值。基於整數列的搜索比基於長字符串關鍵字的搜索要快得多,同樣也比復合關鍵字搜索快得多。
應用Checksum函數作hash關鍵字運算
嘗試運行這段樣本代碼,它表明如何獲得指定值或值組合的hash關鍵字:
USE AdventureWorks
SELECT Name, GroupName, Checksum(Name,GroupName)AS HashKey
FROM Adventureworks.HumanResources.Department
ORDER BY HashKey
所得的結果顯示在下表中(為求簡潔,只選用了10個結果)。
名稱 組名稱 hash關鍵字 工具設計 研究與開發 -2142514043 生產 制造 -2110292704 發貨與收貨 存貨管理 -1405505115 購買 存貨管理 -1264922199 文件控制 質量保證 -922796840 信息服務 總執行管理 -904518583 質量保證 質量保證 -846578145 銷售 銷售與營銷 -493399545 生產控制 制造 -216183716 營銷 銷售與營銷 -150901473
在現實環境中,你可以建立一個調用Name_GroupName_hb的計算列。假設前端傳入名稱(Name)與組名稱(GroupName)的目標值,你就可以用下列代碼來處理這一問題:
CREATE PROCEDURE DemoHash
( ?@Name Varchar(50), ?@GroupName Varchar(50)
)
AS
-- USE AdventureWorks
DECLARE @id as int SET @id = Checksum(@Name,@GroupName)
SELECT * FROM Adventureworks.HumanResources.Department
WHERE HashKey = @id
AND Name = @Name
AND GroupName = @GroupName
想象一下,在一個5 000萬行的表格中,返回了100行指定的hash關鍵字。由於這兩個列沒有其它的目錄,查詢優化器就應用hash桶目錄。這樣就可以快速地隔離出100個感興趣的行。然後我們訪問這些行,檢驗名稱(Name)與組名稱(GroupName)列來進行精確匹配。這樣就大大提高了性能,同時節省了大量的磁盤空間。
引例假設搜索目標存在於一個單獨的表格中。假如要從多個表格中選擇目標來進行搜索,也可以應用同樣的技巧。只需建立一個連接表格的表格函數,然後建立一個hash不同表格列的目錄即可。
結論
在相對較小的表格中,建立一個目錄hash桶對於提高性能可能沒有太大的作用,但這樣做可節省磁盤空間。如果你使用大型的表格,本技巧就極為實用。
Arthur Fuller從事數據庫應用程序開發20余年。在Access ADP、微軟SQL 2000、MySQL和.NET方面有豐富的經驗。