黑豆熊——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')

是否可以直接傳值，不用函數。或者先計算處理，然后賦值。查詢里面帶上函數會影響效率，除非做函數索引。   回復  更多評論