在mysql中哪个内部连接sql最有效和最好? [英] In mysql which inner join sql is most effective and best?

问题描述

在mysql中，哪个内部连接sql最有效和最好?
1.

In mysql which inner join sql is most effective and best? 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
1.

select t01.uname, t02.deptname
from   user t01,  department t02
where  t01.deptid = t02.deptid
and    t01.uid    = '001'

2.

select t01.uname, t02.deptname
from   user t01,  department t02
where  t01.uid    = '001' 
and    t01.deptid = t02.deptid

3.

select t01.uname, t02.deptname
from   user t01 inner join department t02 on t01.deptid = t02.deptid
                                         and t01.uid = '001'

4.

select t01.uname, t02.deptname
from   user t01 inner join department t02 on t01.deptid = t02.deptid
where  t01.uid = '001'

我的mysql是5.1

My mysql is 5.1

推荐答案

所有这些在功能上都是等效的.完全使用INNER联接时，即使WHERE子句和JOIN条件之间的分隔也不会改变结果(这对于OUTER联接可能很重要).此外，所有这些都应制定出完全相同的查询计划(实际上是零性能差异).您添加项目的顺序无关紧要.查询引擎可以自由地进行优化，因为它认为最适合查询的功能规范.即使您确定有关订单的特定行为，也不应指望它.该规范允许明天的补丁程序改变该领域今天的行为.请记住:SQL的要点是基于集合的，并且是声明性的:您告诉数据库您要执行的操作，而不是操作方式你要它做.

All of those are functionally equivalent. Even the separation between WHERE clause and JOIN condition will not change the results when working entirely with INNER joins (it can matter with OUTER joins). Additionally, all of those should work out into the exact same query plan (effectively zero performance difference). The order in which you include items does not matter. The query engine is free to optimize as it sees best fit within the functional specification of the query. Even when you identify specific behavior with regards to order, you shouldn't count on it. The specification allows for tomorrow's patch to change today's behavior in this area. Remember: the whole point of SQL is to be set-based and declarative: you tell the database what you want it to do, not how you want it to do it.

现在，正确性和性能已成问题，我们只能解决样式问题:程序员的工作效率以及代码的可读性/可维护性.在这方面，该列表中的选项#4到目前为止是最佳选择，其次是#3，这是最佳选择，尤其是当您开始研究更复杂的查询时.只是不再使用A,B语法；自1992年版SQL标准以来，它已经过时了.始终写出完整的INNER JOIN(或LEFT JOIN/RIGHT JOIN/CROSS JOIN等).

Now that correctness and performance are out of the way, we're down to matters of style: things like programmer productivity and readability/maintainability of the code. In that regard, option #4 in that list is by far the best choice, with #3 the next best, especially as you start to get into more complicated queries. Just don't use the A,B syntax anymore; it's been obsolete since the 1992 version of the SQL standard. Always write out the full INNER JOIN (or LEFT JOIN/RIGHT JOIN/CROSS JOIN etc).

所有这些都说明了，尽管顺序对性能没有影响(或至少不应该如此)，但当我编写SQL以在决定顺序的方法中使用约定时，我确实发现这很有用.这有助于我在以后进行调试和故障排除时识别错误或错误的假设.我尝试遵循的一般指南是，行为就像顺序确实重要，然后记住这一点，尝试使数据库满足查询所需的工作内存尽可能长地保持尽可能长的时间:首先从较小的表开始，然后再加入较大的表；在考虑表大小时，请考虑WHERE子句中与索引匹配的条件；当您有选择时，更喜欢内部连接优先于外部连接；列出连接条件以优先使用索引(尤其是主键/聚集键)，然后列出连接的其他条件.

All that said, while order does (or, at least, should) not matter to performance, I do find it helpful when I'm writing SQL to use a convention in my approach that does dictate the order. This helps me identify errors or false assumptions later when debugging and troubleshooting. This general guide that I try to follow is to behave as if the order does matter, and then with that in mind try to keep the working set of memory needed by the database to fulfill the query as small as possible for as long as possible: start with smaller tables first and then join to the larger; when considering table size, take into account conditions in the WHERE clause that match up with an index; prefer the inner joins before outer when you have the choice; list join conditions to favor indexes (especially primary/clustered keys) first, and other conditions on the join second.

这篇关于在mysql中哪个内部连接sql最有效和最好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！