SQL Server利用HashKey計算列解決寬欄位查詢的效能問題

主人翁

       本文主人翁：MSSQL菜鳥和MSSQL老鳥。

問題提出

       某年某月某日，某MSSQL菜鳥滿臉愁容的跑到老鳥跟前，心灰意懶的對老鳥說“我最近遇到一個問題，很大的問題，對，非常大的問題”。老鳥不急不慢的推了推2000度超級近視眼鏡框，慢吞吞的說：“說來聽聽”。

       “我有一個100萬資料量的表，有一個寬度為7500欄位，不幸的是現在我需要根據這個欄位的值來查詢表資料，而且最為可恨的是MSSQL Server不允許我在這個欄位上建立Index，所以，我的查詢語句爆慢，應用程式直接超時，腫麼辦呀，腫麼辦？”。

問題分析

       老鳥一聽，捋了捋一身上老毛，頭頭是道的分析說：“查詢慢，是正常的，快起來才不正常呢。你想想啊，欄位寬度為7500，顯然這個欄位不能建立索引了，因為MSSQL限制建立索引的條件是鍵值寬度不超過900byte，100萬的資料量沒有索引的查詢跑起來IO立馬上起來了，效能瓶頸是理所應當的。”

       “那要怎麼解決啊？”，菜鳥已經心急如焚了。

       老鳥接著問：“你知道Hash Join的原理嗎？Hash Join就是將兩個表的連線欄位先算出Hash值，然後再利用Hash值來做連線操作的，對吧？”

       “我知道Hash Join的原理啊，和解決這個問題有什麼關係？”，菜鳥已經迫不及待了。

       “我們完全可以借用這個思想嘛，我們可以先建立一個計算列，這個計算列儲存著寬欄位的Hash值，然後在這個Hash值上面建立索引。在查詢的時候，我們直接使用Hash來檢索滿足條件的記錄，換句話講，只要Hash值滿足條件，能夠匹配上，對應的寬欄位也就滿足條件了嘛。”，老鳥像教育孩子似的教育著菜鳥。

       “喔~~？哦~？”，菜鳥還是似懂非懂。老鳥看出了菜鳥的心思，於是得意洋洋的說：“來來來，讓我們一起來看看Demo吧”。

解決問題

       於是老鳥洋洋灑灑的寫了一段測試Demo:

       建立測試表

use tempdb
go

--Create Test table
if OBJECT_ID(`dbo.test_for_hashkey`,`U`) is not null
    drop table dbo.test_for_hashkey
GO
create table dbo.test_for_hashkey
(
    id int identity(1,1) primary key
    ,SearchKeyword varchar(7500) null
);
/*
We can`t create index on the column SearchKeyword since the maximum key length has 900 bytes limitation.

create index ix_DBA_SearchKeyword
ON dbo.test_for_hashkey(SearchKeyword);
GO
*/

       初始化100萬條資料

--1 million records data init
SET NOCOUNT ON
declare
    @loop int
    ,@do int
    ,@SearchKeyword varchar(7500)
;

select
    @loop = 1000000
    ,@do = 0
;

while @do < @loop
begin
    set
        @SearchKeyword = REPLICATE(newid(),220)
    ;
    insert into dbo.test_for_hashkey
    select @SearchKeyword
    ;
    set @do = @do + 1
end
go

       菜鳥的查詢方法效能

--performance testing at the very first time for the regular query
declare
    @SearchKeyword varchar(7500)
;
select TOP 1
    @SearchKeyword = SearchKeyword
FROM dbo.test_for_hashkey WITH(NOLOCK)
where id = 59987;

SET STATISTICS TIME ON
SET STATISTICS IO ON
select *
FROM dbo.test_for_hashkey WITH(NOLOCK) 
where SearchKeyword = @SearchKeyword
;

/* cold cache
Table `test_for_hashkey`. Scan count 5, logical reads 1003732, physical reads 6792, read-ahead reads 987055, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2870 ms,  elapsed time = 6213 ms.
*/

       從註釋部分的效能指標來看，菜鳥的查詢方法效能的確如老鳥所說，IO消耗非常嚴重，邏輯讀達到了100萬，物理讀達到了6792；時間CPU 2870毫秒和時間消耗6213毫秒還不算太嚴重（因為我的測試環境是SSD的儲存介質）。
老鳥的優化方案：先新增計算列，記得為計算列使用PERSISTED關鍵字，然後在計算列上建立索引。

--and now, it`s time for us to do something for booting the query
ALTER TABLE dbo.test_for_hashkey
ADD SearchKeyword_hashkey AS checksum(SearchKeyword) PERSISTED
;
GO
CREATE INDEX IX_SearchKeyword_hashkey ON dbo.test_for_hashkey(SearchKeyword_hashkey);
GO

       檢驗老鳥優化方案

--test again to observe the performance metrics
declare
    @SearchKeyword varchar(7500)
    , @SearchKeyword_hashkey int
    ;
select TOP 1
    @SearchKeyword_hashkey = CHECKSUM(SearchKeyword)
    , @SearchKeyword = SearchKeyword
FROM dbo.test_for_hashkey WITH(NOLOCK)
where id = 59987;

select *
FROM dbo.test_for_hashkey WITH(NOLOCK) 
where SearchKeyword_hashkey = @SearchKeyword_hashkey
--to avoid hash key collisions, we`d better add this condition statement
and SearchKeyword = @SearchKeyword
;
/*
Table `test_for_hashkey`. Scan count 1, logical reads 7, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

*/

       從註釋部分的效能指標來看，老鳥的優化方案的確棒棒的，邏輯讀降低到7，物理讀降低都1；CPU和執行時間消耗均為0毫秒，也就是秒殺，效能取得了質的飛躍。

       同時，從老鳥優化方案的執行計劃來看，的確走到了這個有效的索引上來:

注意事項

       看完優化效果後，菜鳥已經激動得不能自已：“牛X，老鳥就是老鳥，請收下我的膝蓋吧，今生今世為你做牛做馬”。

       老鳥摸了摸菜鳥腦袋，語重心長的說：“千萬不要高興得太早，這個方法雖然效果很棒，但是有兩個需要注意的點”。

       一、為了防止Hash碰撞，我們最好在WHERE語句中加上防止Hash碰撞的程式碼

--to avoid hash key collisions, we`d better add this condition statement
and SearchKeyword = @SearchKeyword

       二、這個方法只適合於字串全部匹配的情況，對應字串部分模糊和全部模糊匹配並不適合。