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

打赏 支付宝打赏 微信打赏