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

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

管理员2年前 (2018-07-04)SQL Server429

之前经手过一个搜索引擎的项目，其中有一个功能模块是记录所有用户的访问记录，用于统计分析某个网站点的页面访问分布情况，原数据库设计人员把访问的 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 。

打赏

支付宝打赏

微信打赏

返回列表

上一篇：SQL Server 触发器使用try catch

没有最新的文章了...

SQL Server 中的条件索引实现部分数据唯一的约束

有同事提到这样的一个需求，有一个数据字典表，存储着要引用的数据项目，要求所有的正在使用的数据项，不能重名，而标记删除的则不要求，问是否可以在数据库加上这个限制。SQL Server 自身已提供这样的功能，在创建索引时，可以使用 where 子句，对部分数据进行索引，而不是所有的行，示例如下：1、先创...

利用Windows 的计划任务完成 SQL Server 数据库实现异地备份

SQL Server使用 Backup database 时,只能使用本地的磁盘,或把其他的机器共享止录映射为本机的一个虚拟盘符，再进行备份;下面提供另一个方法,思路是在备份放在本地,再通过FTP的形式,传到远程机器上,当然了,这需要远程的机器开通FTP服务。@echo off...

卖水果的net

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

相关文章

SQL Server 中的条件索引实现部分数据唯一的约束

利用Windows 的计划任务完成 SQL Server 数据库实现异地备份

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

卖水果的net

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

相关文章

SQL Server 中的条件索引实现部分数据唯一的约束

利用Windows 的计划任务完成 SQL Server 数据库实现异地备份

发表评论取消回复

@2020 www.msgde.net 冀ICP备20003249号冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号