为什么STRAIGHT_JOIN如此大幅度地改善了此查询，并且在SELECT关键字之后编写它意味着什么? [英] Why does STRAIGHT_JOIN so drastically improve this query, and what does it mean when it is written after the SELECT keyword?
问题描述
我有以下MySql查询:
I have the following MySql query:
select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1
运行大约需要30秒，这很奇怪，因为如果我注释掉join或where子句，它会花费不到一秒钟的时间: 即
It takes about 30 seconds to run, which was strange, because if I comment out the join or the where clause it takes less than a second: i.e.
select t1.*
from Table1 t1
where t1.FilterID = 1
或
select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
每个过程都花费不到一秒钟.
each take less than a second.
然后有STRAIGHT_JOIN关键字，在这里可以找到它的一个参考: http://dev.mysql.com/doc/refman/5.0/en/join.html
Then there is the STRAIGHT_JOIN keyword, which I can find one reference of, here: http://dev.mysql.com/doc/refman/5.0/en/join.html
STRAIGHT_JOIN与JOIN相似， 除了左表总是 在正确的表格前阅读.这个可以 用于那些(少数)情况 联接优化器将其放在 表格顺序错误.
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
什么? 我可以写:
select t1.*
from Table1 t1
STRAIGHT_JOIN Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1
并且查询将在不到一秒钟的时间内执行.
and the query executes in less than a second.
即使是陌生人，我也可以写:
Even stranger, I can write:
select STRAIGHT_JOIN t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1
并且花费不到一秒钟的时间，而且这种语法甚至看起来都不合法.
and it takes less than a second, and this syntax does not appear to even be legal.
我想第二个例子意味着，每当编写INNER JOIN时都会使用STRAIGHT_JOIN，但是我找不到有关它的任何文档.
I would guess the second example means that a STRAIGHT_JOIN will be used whenever an INNER JOIN is written, but I can’t find any documentation about it.
这是怎么回事，联接优化器"如何导致这种相对较差的性能?我应该一直使用STRAIGHT_JOIN吗?我该如何知道何时使用它?
What is going on here, and how can the "join optimizer" result in such relatively poor performance? Should I always use STRAIGHT_JOIN? How can I tell when to use it or not?
表1和表2都有整数主键； FilterID是另一个表的外键； CommonID列都是第三张表的外键.它们都有索引.数据库引擎是InnoDB.
Table1 and Table2 both have integer primary keys; FilterID is a foreign key to another table; the CommonID columns are both foreign keys to a third table. They both have indexes on them. The database engine is InnoDB.
谢谢
推荐答案
这是怎么回事，联接优化器"如何导致如此相对较差的性能?
What is going on here, and how can the "join optimizer" result in such relatively poor performance?
STRAIGHT_JOIN强制执行表的连接顺序，因此在外部循环中扫描table1，在内部循环中扫描table2.
STRAIGHT_JOIN forces the join order of the tables, so table1 is scanned in the outer loop and table2 in the inner loop.
优化器并不完美(尽管还算不错)，最可能的原因是统计数据过时.
The optimizer is not perfect (though stil quite decent), and the most probable cause is the outdated statistics.
我应该始终使用
STRAIGHT_JOIN
否，仅当优化器错误时.这可能是因为您的数据分布严重偏斜或无法正确计算(例如，对于空间索引或全文索引).
No, only when the optimizer is wrong. This may be if your data distribution is severely skewed or cannot be calculated properly (say, for spatial or fulltext indexes).
我怎么知道何时使用它?
How can I tell when to use it or not?
您应该收集统计信息，为这两种方式构建计划，并了解这些计划的含义.
You should collect the statistics, build the plans for both ways and understand what do these plans mean.
如果看到:
-
自动生成的计划不是最佳方案，无法通过标准方法进行改进，
The automatically generated plan is not optimal and cannot be improved by the standard ways,
STRAIGHT_JOIN版本更好，您可以理解它总是会的，并理解为什么为什么它会总是会的
The STRAIGHT_JOIN version is better, you understand it always will and understand why it always will
，然后使用STRAIGHT_JOIN.
这篇关于为什么STRAIGHT_JOIN如此大幅度地改善了此查询，并且在SELECT关键字之后编写它意味着什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
