没有提示指令的直接联接中的表顺序是否会影响性能? [英] Does the order of tables in straight joins, with no hint directives, affect performance?

问题描述

所有基于SQL的RDBMS'(版本长达10年):

All SQL-based RDBMS' (versions up to 10 years old):

在直接联接查询中(无提示指令)表的顺序是否对优化性能和内存管理有所不同?我听说最后一个联接应该是最大的表.数据库的查询优化器如何处理这种情况?

Does the order of tables in a straight join query (with no hint directives) make a difference for optimum performance and memory management? I heard that the last join should be the largest table. How does your DB's query optimizer handle this scenario?

推荐答案

只是在主题上添加更多...是和否，取决于.那是我的答案.它取决于许多因素，包括正在使用的RDBMS(MySQL，MSSQL Server，Oracle，DB2 ...)，联接的类型，表的大小(即行数)，索引等等.

Just to add more on the subject... YES and NO, depends. That is my answer. It depends on many factors, wich RDBMS you are using (MySQL, MSSQL Server, Oracle, DB2...), type of join, size of tables (aka number of rows), indexes and more.

先前的答案是是"和否"，并呼吁在查询中使用提示.但是您的问题是:

The previous answers says yes and no and appeal to using hints on the queries. But your question is:

join语句中的表顺序是否有所不同...

Does the order of tables in a join statement make a difference...

我认为，这在强制查询优化器使用您喜欢的顺序时会省略查询提示的使用.

In my opinion, this leaves out the use of query hints as you are forcing the query optimizer to use the order prefered by you.

因此，请重复使用我的第一个评论中发布的答案/a>对于您的问题，@ Mark Brackett正确指出，重新排列您的联接(不包含查询提示)不会影响性能，因为查询优化器仍将对当前查询使用最有效的执行计划.也许它不是最有效的，所以您可以使用提示并强制使用想要的联接顺序，从而修改查询的执行计划.

So, reusing the answer posted on my first comment to your question, @Mark Brackett correctly points out that rearranging your joins (without query hints) does not affect the performance as the query optimizer will still use the most efficient execution plan for the present query. Maybe it will be not the most efficient so you can use hints and force to use the order you want on the joins and therefore modify the execution plan of the query.

有关以下主题的更多讨论:在JOIN子句中订购很重要吗?优化联接方法

More discussion on the subject on the following links: Does order matter in a JOIN clause? Optimize join methods

这篇关于没有提示指令的直接联接中的表顺序是否会影响性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！