查看: 11126|回复: 45

一则使用HASH技术进行优化的案例

[复制链接]
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2009-7-27 15:07 | 显示全部楼层 |阅读模式
表结构:
create table test_key(pk varchar(32),         key1 varchar(20),        key2 varchar(20),
        key3 varchar(20),         key4 varchar(20),         key5 varchar(20),         key6 varchar(20),
        key7 varchar(20),         key8 varchar(20),         key9 varchar(20),         key10 varchar(20),
        key11 varchar(20),        key12 varchar(20),        key13 varchar(20),        key14 varchar(20),
        key15 varchar(20),        key16 varchar(20),        key17 varchar(20),        key18 varchar(20),
        key19 varchar(20),        key20 varchar(20))
               
业务需求:
检查指定key1~key20值的记录是否已经存在,如存在返回其pk,否则插入新值

sql如:
Select pk from test_key        
  where key2='key_value' and key1 = 'key_value'
        and key3 = 'key_value'        and key4 = 'key_value'        and key5 = 'key_value'        and key6 = 'key_value'
        and key7 = 'key_value'        and key8 = 'key_value'        and key9 = 'key_value'        and key10 = 'key_value'
        and key11 = 'key_value'        and key12 = 'key_value'        and key13 = 'key_value'        and key14 = 'key_value'
        and key15 = 'key_value'        and key16 = 'key_value'        and key17 = 'key_value'        and key18 = 'key_value'
        and key19 = 'key_value'        and key20 = 'key_value'       
很可能对于任意key来说其选择性都不是很强,但是20个key的组合却是唯一的
如此结构非常不利于索引优化

针对这一应用可以考虑将key1~key20合并成一个字段key_conn,并建立索引
如增加字段Key_conn varchar(400)
如此一来表变大了近一倍

于是想到使用hash减少列大小
例如增加Key_conn varchar(16)
并将源数据通过md5转换为16位后再插入

测试如下:

构建test_key数据:
declare
        i int :=1;
    begin
        for i in 1..200000 loop
insert into test_key  (pk,key1        ,
Key2        ,key3        ,key4        ,key5        ,
key6        ,key7        ,key8        ,key9        ,key10        ,key11        ,key12        ,key13        ,key14        ,
key15        ,key16        ,key17        ,key18        ,key19        ,key20        )
values(SYS_GUID(),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20),
dbms_random.string('a',20),dbms_random.string('a',20));
  end loop;
    commit;
    end;
   
构建合并字段的表:

create table test_merge(pk varchar(32),
        key1 varchar(20),        key2 varchar(20),
        key3 varchar(20),        key4 varchar(20),
        key5 varchar(20),        key6 varchar(20),
        key7 varchar(20),        key8 varchar(20),
        key9 varchar(20),        key10 varchar(20),
        key11 varchar(20),        key12 varchar(20),
        key13 varchar(20),        key14 varchar(20),
        key15 varchar(20),        key16 varchar(20),
        key17 varchar(20),        key18 varchar(20),
        key19 varchar(20),        key20 varchar(20),
        Key_conn varchar(16)
        )

构建合并函数:
CREATE OR REPLACE FUNCTION merge_to_md5(
inputstr IN VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(16);
BEGIN
retval := (DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => inputstr)) ;
RETURN retval;
END;


将源数据转换为合并数据:
Insert into test_merge
select pk,key1        ,Key2        ,key3        ,
key4        ,key5        ,key6        ,key7        ,
key8        ,key9        ,key10        ,key11        ,
key12        ,key13        ,key14        ,key15        ,
key16        ,key17        ,key18        ,key19        ,key20        ,
merge_to_md5(key1||key2||key3||key4||key5||key6||key7||key8||key9||key10||key11||key12||key13||key14||key15||key16||key17||key18||key19||key20
) from test_key

更新统计信息:
SQL> exec sys.dbms_stats.gather_table_stats(null,tabname => 'test_key',estimate_percent => 100);

PL/SQL procedure successfully completed

SQL> exec sys.dbms_stats.gather_table_stats(null,tabname => 'test_merge',estimate_percent => 100);

PL/SQL procedure successfully completed

对比数据情况:
SQL> select segment_name,blocks,bytes from user_segments where segment_name in ('TEST_KEY','TEST_MERGE');

SEGMENT_NAME                                                                         BLOCKS      BYTES
-------------------------------------------------------------------------------- ---------- ----------
TEST_KEY                                                                              14336  117440512
TEST_MERGE                                                                            14336  117440512

SQL> select count(*) from test_merge
  2  ;

  COUNT(*)
----------
    200000

重复数据情况:
SQL> select count(*),key_conn from test_merge where key_conn is not null group by key_conn having count(*) >1;

  COUNT(*) KEY_CONN
---------- ----------------

无重复数据


从测试的情况看,Key_conn字段基本无重复,但md5并不能排除其重复的可能性,因此并不能直接Key_conn来替换pk字段,查询时也不能直接用
Select pk from test_merge where key_conn='key_value'
来替换,但使用
Select pk from test_merge
        where  
        key1 = 'key_value' and key2 = 'key_value'        and key3 = 'key_value'        and key4 = 'key_value'
        and key5 = 'key_value'        and key6 = 'key_value'        and key7 = 'key_value'        and key8 = 'key_value'
        and key9 = 'key_value'        and key10 = 'key_value'        and key11 = 'key_value'        and key12 = 'key_value'
        and key13 = 'key_value'        and key14 = 'key_value'        and key15 = 'key_value'        and key16 = 'key_value'
        and key17 = 'key_value'        and key18 = 'key_value'        and key19 = 'key_value'        and key20 = 'key_value'
        and        key_conn= merge_to_md5(key_value_merge)
利用key_conn上的索引定位到几乎唯一行再进行filter即可
论坛徽章:
1
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
发表于 2009-7-27 15:15 | 显示全部楼层
支持

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2009-7-27 15:18 | 显示全部楼层
不错,前几天有个帖子询问如何对一行记录作唯一性确认,这是个快速检测的方法。

使用道具 举报

回复
论坛徽章:
81
青年奥林匹克运动会-马术
日期:2014-09-10 21:37:07奥运会纪念徽章:跳水
日期:2012-09-22 18:27:58奥运会纪念徽章:现代五项
日期:2012-09-07 17:33:44奥运会纪念徽章:铁人三项
日期:2012-06-15 21:27:24版主1段
日期:2012-05-15 15:24:11蜘蛛蛋
日期:2012-05-14 10:50:40灰彻蛋
日期:2012-03-06 19:24:222012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:37
发表于 2009-7-27 15:25 | 显示全部楼层
曾经用类似的方法解决十几亿条数据的任意条件的快速检索

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2009-7-27 15:39 | 显示全部楼层
不错,我曾经也在某个程序里为了避免过大index 而 用了6个colunm 的hash_value作为index

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2009-7-27 15:41 | 显示全部楼层
如果信赖md5的唯一性,或者说可以忽略低概率事件的影响,直接用md5后的结果做pk就更简单了

使用道具 举报

回复
论坛徽章:
68
2012新春纪念徽章
日期:2012-01-04 11:51:22奥运会纪念徽章:举重
日期:2012-08-02 22:17:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:312013年新春福章
日期:2013-02-25 14:51:24慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:312015年新春福章
日期:2015-06-11 12:54:06
发表于 2009-7-27 15:46 | 显示全部楼层
不错~帮顶

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-7-27 15:50 | 显示全部楼层
md5是会重复的,但很多个数才会重复1次

使用道具 举报

回复
论坛徽章:
4
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44生肖徽章2007版:牛
日期:2008-11-10 09:55:38生肖徽章2007版:猪
日期:2009-02-16 13:39:32生肖徽章2007版:猪
日期:2009-03-11 02:06:58
发表于 2009-7-27 16:02 | 显示全部楼层
很好的案例,学习

使用道具 举报

回复
论坛徽章:
2
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442010年世界杯参赛球队:意大利
日期:2010-06-24 12:51:28
发表于 2009-7-27 16:05 | 显示全部楼层
好贴,谢谢分享

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表