Re: [SQL ] JOIN TABLE時WHERE的用法會影響效能嗎？

看板Database (資料庫)作者JeremyJoung (J.J.)時間8年前 (2016/08/11 16:02)推噓2(2推 0噓 1→)

留言3則, 3人參與討論串5/5 (看更多)

JOIN 不是不可以用子查詢而是多半會造成過度記憶體載入在MySQL中 A JOIN B 的原理是讀取A 然後"一行一行"匹配B 也就是 B是"需要"多少才讀取多少不會整個載入但是如果是 A JOIN (B)的話則會強迫先把B完全載進記憶體TEMP 然後才開始做逐行JOIN 因此子查詢動作會造成過度的記憶體負擔但在MSSQL中就完全不是這麼一回事喔 MSSQL 是真的會先把兩邊的表都讀出來後(並賦予臨時表INDEX) 才在記憶體中做JOIN 這是兩邊SQL的行為差異 MSSQL的臨時表都會盡可能的賦予INDEX(依據來源TABLE INDEX) 所以MSSQL 非常擅長做複雜的JOIN運算因為每一個TABLE都可以使用INDEX加速就算是臨時表中真的沒有原生INDEX 他也會先強迫製作一個HASH INDEX後才拿去匯總但是就很耗時但是這並不表示MySQL中子查詢就是萬惡的如果今天的條件是 # A JOIN B 並且使用B的統計運算 SELECT ID, COUNT(B.*) FROM A JOIN B ON ID GROUP BY A.ID 那麼B使用子查詢GROUP 且B的INDEX寫的非常理想的話 A JOIN (SELECT ID, COUNT(*) FROM B GROUP BY ID) B2 ON ID 就會有"絕對性"的加速因為原本 A JOIN B 要將整個表SCAN出來後並且用FILE SORT才能完成的動作在簡單查詢中 B可以直接實現COUNT/SUM/MIN/MAX BY INDEX 立即產生解答而另一方面當只要A,B已經被JOIN起來之後就再也無法使用任何INDEX 就只能交由TEMP SORT慢慢排列所以根據條件來使用SUB-QUERY GROUP BY 會有飛快的效果另外如果是 A JOIN B JOIN C GROUP BY A 這種更複雜的條件的話的話這種[先壓縮再組合]的效果會更明顯大約是加快10~100倍以上因為3個表串連以後記憶體體績通常會膨脹數千到數十萬倍所以先壓縮(GROUP BY)再組合(JOIN) 可以有效的避免記憶體的肥大化 - 另外原始的SQL寫的不是很理想完全無法使用任何的INDEX 首先命令可以這樣改寫 SELECT * FROM A LEFT JOIN B ON A.key=B.key AND B.name like '%k%' WHERE A.key like '%k%' 這樣就夠了其餘都是多餘的對B的過濾可以在JOIN ON 同時執行效果和WHERE是沒兩樣的甚至你高興的話也可以在ON過濾A 但是結果不會有意義因為是LEFT JOIN 另外 B SUB-QUERY中呼叫 WHERE A.name like '%k%' A.name 應該是打錯字吧?? 要用B.name才對吧? 如果在B中呼叫A.name 也是會過的喔因為B的視野中有A 但是會造成無效查詢記憶體無限膨脹然後外部的 (A.key like '%k%' OR B.key like '%k%') 是沒有意義的因為你已經用JOIN 把 A.key=B.key 所以兩者必然相同使用OR 只會使INDEX無效化而已雖然在使用 LIKE '%k%' 時本來就已經無效化了 ※ 引述《JYHuang (夏天到了，冷不起來了說)》之銘言： : 今天在寫MySQL時，發現條件比較寬時會出現撈資料撈到SERVER沒回應 : 便有點好奇WHERE先後順序和配對會不會影響效能？ : Table A和B大概都是有幾千比的資料 : 兩著的關聯是由一個可能為空白(不是null)的值 : 在下了指令 : SELECT * FROM A : LEFT JOIN (SELECT * FROM B WHERE A.name like '%k%' ORDER BY x) B : ON A.key=B.key : WHERE (A.key like '%k%' OR B.key like '%k%') : 然後就執行到沒回應了， : 猜想用括號括起來是不是會先JOIN 再做條件 : 要是如果改下 : WHERE A.key like '%k%' OR B.key like '%k%' : 會不會先把A做飾選後再去JOIN飾選後的B？ : 另外 : WHERE (A.key like '%k%' OR B.key like '%k%') AND (A.id = n OR B.id) : 跟 : WHERE A.key like '%k%' OR B.key like '%k%' AND A.id = n OR B.id : 應該是不一樣結果的吧？ -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 118.163.72.102 ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1470902555.A.0D6.html ※ 編輯: JeremyJoung (118.163.72.102), 08/11/2016 16:07:27