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
二、这个方法只适合于字符串全部匹配的情况，对应字符串部分模糊和全部模糊匹配并不适合。