SQL连接与嵌套查询

Question

这两个SQL语句返回相同的结果，但第一个语句比第二个语句慢得多：

SELECT leading.email, kstatus.name, contacts.status 
FROM clients 
JOIN clients_leading ON clients.id_client = clients_leading.id_client 
JOIN leading ON clients_leading.id_leading = leading.id_leading 
JOIN contacts ON contacts.id_k_p = clients_leading.id_group 
JOIN kstatus on contacts.status = kstatus.id_kstatus 
WHERE (clients.email = 'some_email' OR clients.email1 = 'some_email') 
ORDER BY contacts.date DESC;



SELECT leading.email, kstatus.name, contacts.status
FROM (
     SELECT * 
     FROM clients 
     WHERE (clients.email = 'some_email' OR clients.email1 = 'some_email')
     ) 
AS clients  
JOIN clients_leading ON clients.id_client = clients_leading.id_client 
JOIN leading ON clients_leading.id_leading = leading.id_leading  
JOIN contacts ON contacts.id_k_p = clients_leading.id_group  
JOIN kstatus on contacts.status = kstatus.id_kstatus 
ORDER BY contacts.date DESC;

但我想知道为什么会这样？看起来，在第一个语句中，连接是先完成的，然后应用WHERE子句，而在第二个语句中，情况正好相反。但它在所有数据库引擎上的行为方式都一样吗(我在MySQL上测试过它)？

我期望DB引擎可以优化查询，比如fors，one和firs，应用WHERE子句，然后进行连接。

Answer 1

这可能有很多不同的原因(键控等)，但您可以查看explain mysql命令来查看语句是如何执行的。如果你可以运行它，如果它仍然是一个神秘的东西，那就把它发布出来。

Answer 2

您始终可以使用嵌套查询替换join ...它总是更快，但也很混乱。