黑豆熊——BlogJava

面对挑战,我告诉自己:面对是勇气,积极面对是态度,坚持积极面对是毅力!

常用链接

统计

积分与排名

最新评论

千万级sql优化

这几天在开发一个模块的时候,遇到一个棘手的问题:海量数据的查询效率问题,在下面的sql语句中,sf02表的数据在七千万左右,sf01表的数据在三千万左右,并且两个表有关联:

select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamount
  from sf02 a
 where a.ksdm = '11019204'
  and exists
 (select policyno
  from sf01 b
  where b.businessnature = '531'
  union all select policyno from sf01 b where b.businessnature = '532'
  and a.policyno = b.policyno)
  and (a.kindcode = 'R21' or a.kindcode = 'R29')
  and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
  and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')
 group by a.tcol2, a.departcode, a.sendtime
 
由于数据库是分公司的,创建临时表不是太好使,这个问题一直没有太好的解决办法,不知道各位有没有好的思路,希望各位留下宝贵的意见,由于这个帖子放在提问区一直没有人浏览,不得已放在首页,还请blogJava对该帖放行,非常感谢。

posted on 2011-09-06 12:18 黑豆熊 阅读(2904) 评论(22)  编辑  收藏

评论

# re: 千万级sql优化 2011-09-06 15:47 @joe

看看执行计划啥情况?  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:03 黑色幽灵

上面的sql是在oracle上面模拟的(没有那么大的数据量),实际上应该是放在informix数据库上执行的,但是在informix,我还不会使用执行计划之类的,哪位仁兄能告知一下。

另外附加一个问题啊:
七千万条数据,执行查询,响应时间在多少,用户是可以接受的?  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:19 @joe

七千万条其实都应该从业务上来处理了,如果你给用户返回一个几万页得数据,用户也不会去都翻页看了。首先从业务上只返回有用的数据。
返回时间,要看什么用户了,如果是专业用户如果时间有等待,最好界面上提示正在处理数据,一般我觉得一个非主要业务的查询超过15秒估计都不行了。
如果是面向一般用户,返回超过10秒估计都没人看了。

大数据量处理一般用在统计分析上,不会去实时查看详细数据。做分区处理吧,缩小查询范围,适当使用位图索引。informix我也不会,呵呵,但是还是建议业务上考虑一下。  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:21 @joe

建两个表模拟一下,看看计划。  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:32 黑色幽灵

实际上这条SQL查询到的数据,不会直接展示给用户,而是与其他数据库取到的数据做比较处理以后,存放到一个临时表中,用户看到的数据都是从这个临时表中取到的,现在执行查询,要80秒左右,不知道这个时间是否是一个正常的时间。  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:39 @joe

80秒非常正常了。  回复  更多评论   

# re: 千万级sql优化 2011-09-06 16:45 黑色幽灵

非常感谢你的回复,呵呵  回复  更多评论   

# re: 千万级sql优化 2011-09-06 17:20 信息

这明显可以做成一个batch job嘛  回复  更多评论   

# re: 千万级sql优化 2011-09-06 17:55 黑色幽灵

8楼这位仁兄,能否介绍的详细一些?  回复  更多评论   

# re: 千万级sql优化[未登录] 2011-09-06 20:13 YY

select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamount
from sf02 a
where
exists
(
select 1 from sf01 b
where (b.businessnature = '531') or (b.businessnature = '532'
and a.policyno = b.policyno))
and (a.kindcode = 'R21' or a.kindcode = 'R29')
and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')
and a.ksdm = '11019204'
group by a.tcol2, a.departcode, a.sendtime  回复  更多评论   

# re: 千万级sql优化[未登录] 2011-09-06 22:15 大鹏

最好给出表结构,能看出表之间的关系,还有索引之类的,能更好的做优化。  回复  更多评论   

# re: 千万级sql优化 2011-09-07 10:56 黑色幽灵

10楼的这位仁兄,好像是把内层的嵌套查询由union all改成or了,但是,我感觉应该是union all的效率更高一些把,完全是个人意见,欢迎讨论。  回复  更多评论   

# re: 千万级sql优化[未登录] 2011-09-07 15:00 roger

用到了sum、exists、union all、group by,还有多个select语句,即使80秒能够返回数据的话,是否会影响其他查询的访问呢?是否会lock table呢?问题太多了

个人建议:
  1,对sql进行拆分
  2,为了性能,创建索引表
  3,即使sql优化了,关系型数据库的数据达到了7千万也会存在查询缓慢的问题,请考虑其他方式进行数据的存储,比如Mongodb
  4,使用搜索(lucene、sphinx)来进行查询获取结果

总之,数据量达到这种程度了,应该考虑从架构的角度解决问题,sql上可优化的实在有限  回复  更多评论   

# re: 千万级sql优化 2011-09-08 19:09 jacklondon chen

union all 的前后都用 from sf01 b, 能不能用两个不同的别名 b1, b2 ? 看不出你后面的 a.policyno = b.policyno 里用的 b 是前后哪一个。

and (a.kindcode = 'R21' or a.kindcode = 'R29') 可以改成:
and (a.kindcode in ( 'R21' , 'R29') )

在 sf02 表上建索引:
索引1, ksdm
索引2, ksdm + insert_time
  回复  更多评论   

# re: 千万级sql优化 2011-09-08 19:11 jacklondon chen

要看 where 条件中,哪个字段分散性好,在它上面建立索引。  回复  更多评论   

# re: 千万级sql优化 2011-09-09 08:24 tbw

恩 不错  回复  更多评论   

# re: 千万级sql优化[未登录] 2011-09-09 10:39 evemalloc

我也遇到这样的问题,希望楼主有什么好的解决方案拿出来分享  回复  更多评论   

# re: 千万级sql优化 2011-09-09 15:32 杨明

这个问题不用从优化Sql的角度去考虑,你的查询条件,查询了十年的数据,这种问题显然是统计分析的问题,需要从数据仓库的角度去考虑。建立一个汇总表。每天增量维护汇总表。如果一天只有一条数据你想还会慢吗?  回复  更多评论   

# re: 千万级sql优化[未登录] 2011-09-15 11:36 小米

@roger
oralce 中 查询是不会锁表的  回复  更多评论   

# re: 千万级sql优化 2011-09-18 17:10 DB Compare Tool

相同的语句在不同的DB上执行情况通常是不一样的,你informix的在oracle上试验,恐怕没什么效果吧  回复  更多评论   

# re: 千万级sql优化 2011-09-21 18:31 吕彦

如果原文sql没贴错的话,看简化为:
select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamount
from sf02 a
where a.ksdm = '11019204'
and a.kindcode in ( 'R21', 'R29')
and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')
group by a.tcol2, a.departcode, a.sendtime
  回复  更多评论   

# re: 千万级sql优化 2011-09-23 11:02 wuzhongxing

and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')

是否可以直接传值,不用函数。或者先计算处理,然后赋值。查询里面带上函数会影响效率,除非做函数索引。   回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航: