SQL Server利用HashKey计算列解决宽字段查询的性能问题

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

       二、这个方法只适合于字符串全部匹配的情况,对应字符串部分模糊和全部模糊匹配并不适合。

时间: 2024-04-26 06:27:37

SQL Server利用HashKey计算列解决宽字段查询的性能问题的相关文章

用SQL Server HAVING子句计算列和平均值

在本文中,简单说明这个子句,并提供一些代码实例,这是说明HAVING子句用法的最佳方法. 严格来说,HAVING并不需要一个子表,但没有子表的HAVING并没有实际意义.如果你只需要一个表,那么你可以用WHERE子句达到一切目的.为进行实践,HAVING预先假定至少两个表和一个基于第二个表的合计函数. 下面是一个简单的例子:你想要订单总数超过25000美元的客户清单.你需要适当连接的三个表:Customer.SalesOrderHeader和SalesOrderDetail.然后,你求Detai

SQL Server · 特性分析 · 2012列存储索引技术

摘要 MS SQL Server 2012首次引入了列存储索引(Columnstore Index)来加速数据分析(OLAP)和数据仓库(Data Warehouse)场景的查询,它主要是通过将数据按列压缩存储的方式来减少查询对磁盘IOPS开销和CPU开销,最终达到提升查询效率,降低响应时间的目的.当然,列存储索引也不是一把万能的钥匙,在SQL Server 2012版本中它有诸多非常严苛限制条件. 这篇文章会从以下几个方面来介绍列存储索引: 列存储索引所涉及到的基本概念 列存储索引的结构 列存

SQL Server自动化运维系列——监控磁盘剩余空间及SQL Server错误日志(Power Shell)

原文:SQL Server自动化运维系列--监控磁盘剩余空间及SQL Server错误日志(Power Shell) 需求描述 在我们的生产环境中,大部分情况下需要有自己的运维体制,包括自己健康状态的检测等.如果发生异常,需要提前预警的,通知形式一般为发邮件告知. 在所有的自检流程中最基础的一个就是磁盘剩余空间检测.作为一个高效的DBA不可能每天都要上生产机上查看磁盘剩余或者直到磁盘无剩余空间报错后才采取扩容措施. 当然,作为微软的服务器有着自己的监控软件:SCCM(System Center

SQL Server自动化运维系列——关于邮件通知那点事(.Net开发人员的福利)

原文:SQL Server自动化运维系列--关于邮件通知那点事(.Net开发人员的福利) 需求描述 在我们的生产环境中,大部分情况下需要有自己的运维体制,包括自己健康状态的检测等.如果发生异常,需要提前预警的,通知形式一般为发邮件告知. 邮件作为一种非常便利的预警实现方式,在及时性和易用性方面也有着不可替代的优点. 所以,在本篇中将详细的分析下在SQL Server中的邮件通知功能及使用方式等.  本篇实现 1.通过SQL Server自带的邮件功能实现运维的预警及检测 2.利用数据库邮件组件代

SQL Server自动化运维系列——监控跑批Job运行状态(Power Shell)

原文:SQL Server自动化运维系列--监控跑批Job运行状态(Power Shell) 需求描述 在我们的生产环境中,大部分情况下需要有自己的运维体制,包括自己健康状态的检测等.如果发生异常,需要提前预警的,通知形式一般为发邮件告知. 在上一篇文章中已经分析了SQL SERVER中关于邮件的基础配置,本篇将利用此功能对多台Server的跑批Job进行监控. 本篇实现 1.每天检查服务器中的SQL Server跑批Job的运行状态,如果跑批失败,则发邮件告诉管理员失败的明细 2.解决多台服务

小结SQL Server连接失败错误的解决

server|错误|解决     在使用 SQL Server 的过程中,用户遇到的最多的问题莫过于连接失败了.一般而言,有以下两种连接 SQL Server 的方式:    一是利用 SQL Server 自带的客户端工具,如企业管理器.查询分析器.事务探查器等.     二是利用用户自己开发的客户端程序,如ASP 脚本.VB程序等,客户端程序中又是利用 ODBC 或者 OLE DB 等连接 SQL Server.下面,我们将就这两种连接方式,具体谈谈如何来解决连接失败的问题.     一.客

SQL Server 重置Identity标识列的值(INT爆了)

原文 http://www.cnblogs.com/gaizai/archive/2013/04/23/3038318.html SQL Server 重置Identity标识列的值(INT爆了) 2013-04-23 17:45 by 听风吹雨, 1146 阅读, 16 评论, 收藏, 编辑 一.背景 SQL Server数据库中表A中Id字段的定义是:[Id] [int] IDENTITY(1,1),随着数据的不断增长,Id值已经接近2147483647(int的取值范围为:-2 147 4

SQL Server 2012 自动增长列,值跳跃问题

原文:SQL Server 2012 自动增长列,值跳跃问题   介绍 从 SQL Server 2012 版本开始, 当SQL Server 实例重启之后,表格的自动增长列的值会发生跳跃,而具体的跳跃值的大小是根据增长列的数据类型而定的.如果数据类型是 整型(int),那么跳跃值为 1000:如果数据类型为 长整型(bigint),那么跳跃值为 10000.从我们的项目来看,这种跳跃问题是不能被接受的,尤其是展示在客户端的时候.这个奇怪的问题只在 SQL Server 2012 及更高的版本中

SQL Server 利用锁提示优化Row_number()-程序员需知

原文:SQL Server 利用锁提示优化Row_number()-程序员需知 网站中一些老页面仍采用Row_number类似的开窗函数进行分页处理,此时如果遭遇挖坟帖的情形可能就需要漫长的等待且消耗巨大.这里给大家介绍根据Row_number()特性采用特定锁Hint提升查询速度.   直接上菜   脚本环境可在SQL Server优化技巧之SQL Server中的"MapReduce"找到   如下查询在分页中比较常见 set statistics time on select *