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
: 应该是不一样结果的吧？