当前位置:首页 > SQL Server > 正文内容

使用哈希算法优化数据库设计的案例分享

管理员3年前 (2018-07-04)SQL Server618

之前经手过一个搜索引擎的项目,其中有一个功能模块是记录所有用户的访问记录,用于统计分析某个网站点的页面访问分布情况,原数据库设计人员把访问的 url 都记录到数据库,并在该列建立了唯一索引,每次向表中写数据之前,先进行查询,如果没有重复的则执行一个插入操作,整个过程使用的merge操作。

系统在运行3个月以后,数据大量积累,已达千万级,插入一条新的数据,大概延时3-5秒左右,造成系统极大的卡顿,经过问题排查,发现数一个简单的查询,也要跑好长时间,拷贝数据到测试环境后,分析其数据情况,发现url 的值最长为 900+ ,平均长度为700+,该列又是一个字符类型,并且不同的行的内容,大部的内容是重复的,字符串的查询效率在这里表现的很差, 即便用的是唯一索引。

问题解决思路及改善的方法如下:

使用hash算法,将长度为700+的内容压缩到32位,可以极大的提高查询速度,并且将这个32位的值也存放于数据库中,即冗余一个列,在这新增的这一列上建立一个索引(注意,因为考虑哈希碰撞问题,不能建成唯一索引),并删除原列上的索引,再做查询操作时,同时比较哈希值和原值。

下面模拟一个场景,还原当时的状态,步骤如下:

1、原来的数据库设计

create table test(
    id int, 
    url varchar(1024) unique
)
go
insert into test(id, url) values
(100,'http://www.msgde.net/mssql/mssql_hashbytes.html'),
(200,'http://www.msgde.net/oracle/oracle_create_condition_index.html')
go
declare @url varchar(500)
set @url = 'http://www.msgde.net/mssql/mssql_hashbytes.html'
select id, url from test
where url = @url;
go
drop table test
go

2、调整后的数据库设计

create table test(
    id int, 
    url varchar(1024), 
    url_hash as hashbytes('MD5',url) persisted
)
create index ix_test_hashurl on test(url_hash)
go
insert into test(id, url) values
(100,'http://www.msgde.net/mssql/mssql_hashbytes.html'),
(200,'http://www.msgde.net/oracle/oracle_create_condition_index.html')
go
declare @url varchar(500)
set @url = 'http://www.msgde.net/mssql/mssql_hashbytes.html'
select id, url_hash from test
where url_hash = hashbytes('MD5',@url)
and url = @url
go
drop table test
go

注:

1、这是一个 SQL Server 的数据库,在Oracle和MySQL 中也有相应的函数实现这个功能,或者开发人员使用自己比较熟悉的语言来生成这个值。

2、使用哈希后数据,只能使用“等于”操作,不能用于like 。

打赏 支付宝打赏 微信打赏

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/mssql/mssql_hashbytes.html

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。