Re: [請益] 資料庫join後count速度問題

看板Soft_Job作者 (hippo泡)時間9年前 (2016/05/08 22:23), 編輯推噓4(5推 1噓 25→)
留言31則, 11人參與, 最新討論串2/2 (看更多)
※ 引述《stevekevin10 (hippo泡)》之銘言： : 抱歉又來請益個資料庫問題m_ _m : 需要join 500萬 跟 3萬筆的表格 : 根據篩選條件後再根據後者的欄位做count : 但現在下完query後都會卡住 : 請問該如何是好 抱歉我補上query QQ select c.`CHROM`,d.`GeneId`,count(distinct c.`primaryKey`) from `variation` as c join `table 8` as d on c.`CHROM` = d.`CHROM` and c.`POS` > d.`5US` and c.`POS` < d.`3UE` Group by d.`GeneId` 其中variation表格大概快五百萬筆 table 8 大概三萬 然後variation表格有一個pos欄位是一個數字 在table8裡面有兩個欄位 5US 3UE組成一個數字區間 然後兩張表各有一個`CHROM`欄位 但table 8裡面還有一個geneId欄位 主要是想找出variation每一筆各屬於哪一個geneId 並做一個統計 但目前此QUERY跑下去.......SQL SERVER就沒回應卡死了T____T -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.230.218.125 ※ 文章網址: https://www.ptt.cc/bbs/Soft_Job/M.1462717410.A.650.html
→
05/08 23:07, , 1F
噁你直接在join做運算式喔
05/08 23:07, 1F
噓
05/08 23:32, , 2F
加一個c.`CHROM`=xxx，先測看看你的index有沒有用吧!?
05/08 23:32, 2F
→
05/08 23:40, , 3F
我是覺得不會卡死，應該是其他問題比較大
05/08 23:40, 3F
→
05/09 00:25, , 4F
用loop join試看看 你需要學會看execution plan
05/09 00:25, 4F
→
05/09 01:13, , 5F
distinct去掉試試看~
05/09 01:13, 5F
→
05/09 01:14, , 6F
也可以先一個單純的select 再把其他部份逐步加上去
05/09 01:14, 6F
→
05/09 01:15, , 7F
直到發生卡死 就知道是哪個部份造成的了
05/09 01:15, 7F
→
05/09 02:12, , 8F
把join的部份做成view 再用view去做篩選和group
05/09 02:12, 8F
推
05/09 08:20, , 9F
那 index 是哪些欄位? explain SQL 的結果是什麼?
05/09 08:20, 9F
→
05/09 08:20, , 10F
是 1:1 還是 1:n ? 範圍數值很多有沒有分割 table?
05/09 08:20, 10F
→
05/09 09:20, , 11F
CHROM欄位的資料型態是什麼？因為500w x 3w 其實是小事
05/09 09:20, 11F
推
05/09 10:55, , 12F
怎麼卡死法？應該有在跑 只是要等
05/09 10:55, 12F
→
05/09 10:55, , 13F
之前下join查詢等過ㄧ天都有
05/09 10:55, 13F
→
05/09 13:33, , 14F
就這個 query 來說, C表需要 POS + CHROM 的索引
05/09 13:33, 14F
→
05/09 13:34, , 15F
具體要看 CHROM 與 POS 的資料差異性大不大
05/09 13:34, 15F
→
05/09 13:34, , 16F
CHROM 沒什麼差異性的話就建 POS
05/09 13:34, 16F
→
05/09 13:35, , 17F
d 表的話則是需要 5US+3UE + CHROM
05/09 13:35, 17F
→
05/09 13:36, , 18F
假設你索引建對了(建多字段的索引 別分開建)
05/09 13:36, 18F
→
05/09 13:37, , 19F
有可能是你2張表 join 字段類型不一致, 導致索引沒有正確
05/09 13:37, 19F
→
05/09 13:39, , 20F
用了索引不一定會比較快, 具體要看資料怎麼分佈的
05/09 13:39, 20F
→
05/09 13:39, , 21F
查詢的範圍, 所以上一篇有人讓你用 hash join 試試
05/09 13:39, 21F
推
05/09 21:28, , 22F
看來應該不會卡 可是chrom不用加到group by的欄位嗎
05/09 21:28, 22F
推
05/09 21:55, , 23F
不然上面有人提過的 join部分先建成view
05/09 21:55, 23F
→
05/09 23:34, , 24F
正常來說建view只是好看，對效能沒什麼幫助
05/09 23:34, 24F
推
05/10 01:08, , 25F
同意樓上,view試過,對效能沒有用
05/10 01:08, 25F
→
05/10 01:47, , 26F
05/10 01:47, 26F
→
05/10 11:20, , 27F
indexed view 跟一般的view 是不一樣的，增速的地方在於
05/10 11:20, 27F
→
05/10 11:20, , 28F
相對於原表額外的clustered index
05/10 11:20, 28F
→
05/10 11:20, , 29F
並不是view
05/10 11:20, 29F
→
05/12 00:00, , 30F
問題是你們說view，我也只說view沒用-.-
05/12 00:00, 30F
→
05/12 00:03, , 31F
不過原po還是要學著找出慢的原因，這是基本功
05/12 00:03, 31F
‣ 更多 stevekevin10 的文章
文章代碼(AID): #1NBqlYPG (Soft_Job)
‣ 更多 stevekevin10 的文章
文章代碼(AID): #1NBqlYPG (Soft_Job)