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
: 應該是不一樣結果的吧？