UDN-企业互联网技术人气社区

板块导航

浏览  : 1482
回复  : 0

[讨论交流] 运行超过了5分钟?那你还没有发挥出数据库的性能

[复制链接]
呵呵燕的头像 楼主
发表于 2016-11-8 12:39:41 | 显示全部楼层 |阅读模式
  1946年的2月14日,阳光温柔地洒在宾夕法尼亚大学,燕子新筑的小屋里,人类历史上第一台电子计算机轻轻地敲开了生命之壳,张开了明亮的眼睛,地球村的村民们用玫瑰和香槟欢迎她的到来,她有一个美好的名字:“爱你爱歌”,反正不是爱我。

  在“爱你爱歌”的家里,有很多精巧的房子,还有一个宽敞的院子,房子里堆满了各种书籍,院子里花开四季。同学们都喜欢到“爱你爱歌”家里去玩,或寻找《绿野仙踪》,或手捧《海底总动员》。

  因为“爱你爱歌”家的房子里没有装电灯,也没有点亮蜡烛,所以大家想看书,都必须把书拿到院子里来看,同学们习惯了把房子称作硬盘,把院子称作内存,把书称作文件,把取书和看书的过程称作程序。

  有一本书(test.txt)的内容是这样的:

  123|100|秀兰邓波儿

  456|200|格列佛

  789|300

  一开始的时候,同学们可能是这样取书和看书的:

f.png


  可是这样取书看书,同学们都觉得太麻烦了,终于有一天,在山的那边,海的那边有一群小伙伴,MicroSoft、Oracle 和 IBM,他们活泼又聪明,他们想着用一种简单高效的方式从房子里把书(文件/表)取出来,拿到院子(内存)里看,他们是这样做的:

  select id,score,name from test.txt

  123|100|秀兰邓波儿

  456|200|格列佛

  789|300

  他们也会这么做:

  select id,score,name from test.txt where name=’秀兰邓波儿’

  123|100|秀兰邓波儿

  他们还可以这样做:

  select id,score,name from test.txt order by id desc

  789|300

  456|200|格列佛

  123|100|秀兰邓波儿

  是不是简单了很多很多?MicroSoft、Oracle和IBM把他们的新发明取了一个他们都喜欢的昵称:数据库。

  数据库为我们提供了便利,方便我们对数据进行检索、分类、汇总、排序,同时拥有优良的性能,而这一切也需要我们合理使用数据库。

  有一张库存表如下,其中customer+productno+change_date是唯一的,表示某个customer拥有的某样产品在某一天的剩余库存数量,比如customer=1的客户在20070105那一天在仓库里保存的产品123的数量是1000,在20070108那天又增加了1000,一共是2000,而在20070106和20070107那两天,库存数量没有变化,都是1000。

  因此库存表不会保存相关记录。此外,库存表中唯一的productno不超过100个,也就是产品不超过100类。

e.png


  客户把产品保存在仓库里,仓库的主人是要收点费用的,他说每天他都要收取费用,而每天的费用=当天库存数量*当天费率。不同的产品费率不同,同一个产品每天的费率也都不一样,他把不同产品每天的费率,保存在如下费率表里:

d.png


  仓库的主人希望能计算出10年前到现在,每一个customer+productno,每天的费用之和,有一位同学是按照如下方式一天一天计算然后汇总的: sum=1000*0.00001 + 1000*0.00002 + 1000*0.00003 + 2000*0.00004

c.png


  假设这个客户把他的产品在仓库里保存了10年,那么为了算出这个客户的这个产品10年的费用之和,按照这位同学的方式,需要进行大约3650次计算。这样的计算方法从业务角度来说是正确的,但库存表有5000万行记录,其中唯一的customer+productno大约1000万个,所以最多要进行1000万*3650=365亿次计算,当然并非所有产品都保存了10年,但是在“爱你爱歌”家里,完成全部计算依然耗时1星期。

  仓库的主人是个脾气很好的人,只是偶尔抱怨这让他等到花儿也有点谢了,他说他希望在杜鹃花映红了天边的晚霞的时候,就已经知道他的收成有多少。我们从“唯一的customer+productno 有1000万行”这句话能发现什么?

  这说明每个customer+productno的库存数量在10年间平均变化5次,显然大部分时间库存数量是不变的,于是我们想计算方式可以改成sum=1000*(0.00001 + 0.00002 + 0.00003) + 2000*0.00004的吧。或许有同学会说,这样计算出来的值可能会和原来的方法得到的结果不同,嗯,确实是的,但是我们问了仓库的主人,他说我们这样计算他也是认可的,也是对的。

  假设我们要计算从20070105到20161006之间的每个customer+productno的费用总和,我们是这样做的:先从库存表算出每个customer+productno的数量不变的日期区间,由于平均每个customer+productno在10年间变化5次数量,因此如下数据集大约6000万行。

b.png


  因为productno最多100个,所以费率表记录数最多是100*3650=36.5万行,是一张很小的表,接下来我们要对费率表以productno分组,以fee_date排正序,对费率做一个滚动求和处理,生成如下新费率表,耗时在1秒以内或者最多2~3秒。

a.png


  现在只要遍历那个含有日期区间的库存表,按如下步骤就能在10分钟内完成全部费用计算。

  1.productno+beg_date关联新费率表的productno+fee_date读取ratesum_oneday_preceding

  2.productno+end_date关联新费率表的productno+fee_date读取ratesum

  3.数量*(ratesum-ratesum_oneday_preceding)算出每一个日期区间的费用

  4.每个customer+productno的各自所有日期区间的费用相加

  仓库的主人走了,渐渐消失在我的眼前,我已经不太记得他的模样,愿他快乐、幸福。而春夏秋冬四种色彩,乘我们不注意的时候,在黑夜与白昼间划过了70个同心的椭圆,带走了“爱你爱歌”70年的青春,也把我们拉进了网络时代。

  我们用IP地址和IP地址区间定位我们的坐标,我们需要知道自己身在何方,是在北美的落基山脉徒步穿行,还是在非洲的恩戈罗恩戈罗为憨厚的大象送上一篮它爱吃的香蕉?

9.png


  这里,IP地址区间不会有重叠,也就是不会出现例如同时有100-200和180-280的情况。我们需要算出IP_ADDRESS的每个IP地址位于IP_RANGE的哪个开始值和结束值区间里。但是像800那样没有位于任何IP地址区间里的IP地址,则不需要查询出来。

  如下是旧的SQL语句,在客户生产环境耗时5分钟

8.png


  新的SQL的算法思路如下:在我的LAPTOP上耗时1.5秒

  STEP 1: IP_ADDRESS和IP_RANGE合并(UNION ALL),生成如下含有4列的一个数据集

  ● IP由IP_ADDRESS的全部IP和IP_RANGE的全部RANGE_BEG构成

  ● DATASET=1表示IP来自IP_RANGE的RANGE_BEG

  ● DATASET=2表示IP来自IP_ADDRESS的IP

  ● 只有DATASET=1的记录,RANGE_BEG和RANGE_END才有值

7.png


  STEP 2:对STEP 1的结果,按照IP和DATASET排正序,这里DATASET的作用是当IP列的IP值有重复的时候,可以确保来自IP_RANGE的RANGE_BEG能排在来自IP_ADDRESS的IP之前。

6.png


  排序后,我们可以发现每个IP地址只可能位于它上面的且离它最近的IP区间内。

  STEP 3: 对于STEP 2的结果集里开始值和结束值为空的记录,那么其开始值和结束值就用它上面离它最近的IP区间的开始值和结束值来填写, 形成如下数据集:

5.png


  STEP 4: 最后读取DATASET=2且IP>=RANGE_BEG AND IP<=RANGE_END的记录就是最终结果。

4.png


  小时候,我用铅笔和橡皮写着童年的心愿:“今天天气很晴朗,蓝蓝的天上白云飘”。如今,我和insert、update、select、delete成了好朋友,很少再用笔写字,我们经常在一起讨论,如何才能花更少的时间完成老师布置的作业。

3.png


  insert说,它花了5分钟才往一张表里插入了800万行记录。对于这个INSERT, 我们一起来分析,其中自定义函数right耗时2分30秒,那么right函数是做什么的呢?先看一下right函数的源码:

2.png


  我们现在知道了,这个函数的功能是用来从一个字符串的右边截取N个字节,可是数据库已经提供了这样的功能了,可以用数据库自己的函数substrb实现,而且性能很不错。所以,先把这个right函数改成substrb,速度提升到了2分30秒,快了1倍。

1.png


  可2分30秒依然还是慢的,那怎么办?我们再看,这里的position_str是一个唯一性标识,类似于学生编号,完全没有必要定义这么长(100多个字节)。我们可以改变设计,10个字节应该够了吧。

  此外,为生成这么长的position_str,进行了7次字符串拼接,字符串拼接是一个很慢的操作,应尽可能避免。我们只要将position_str缩短到10个字节,这是完全可以做到的,那么这个insert只要10秒就可以插入800万行记录。

  我想,如果不改变设计和算法,用C语言,汇编语言实现这个insert的功能,也不会有实质性的性能提升,我想我没有理由去埋怨数据库,埋怨Oracle。我知道,数据库不是万能的,但当我看到一段用数据库语言编写的程序,运行耗时5分钟,我会好奇地问:它是真的真的需要5分钟吗?我想能有更多的时间可以去逛街、旅行、看电影,我想在红叶满山坡的时候随心所欲的游玩而不用在办公室里敲键盘…

原文作者:朱元中 来源:开发者头条

相关帖子

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

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部