普通的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方面有丰富的经验。
posted on 2006-05-31 15:22
萌芽的叶子 阅读(396)
评论(0) 编辑 收藏 引用 所属分类:
sql