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