黑豆熊——BlogJava

# 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')

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