何时在MySQL上使用STRAIGHT_JOIN [英] When to use STRAIGHT_JOIN with MySQL
问题描述
我刚刚使用了一个相当复杂的查询，它需要8秒钟才能运行. EXPLAIN显示了一个奇怪的表顺序，即使使用FORCE INDEX提示也没有全部使用我的索引.我遇到了STRAIGHT_JOIN连接关键字，并开始用它替换一些我的INNER JOIN关键字.我注意到速度有了很大提高.最终，我为此查询用STRAIGHT_JOIN替换了我所有的INNER JOIN关键字，现在它的运行时间为.01秒.
我的问题是何时使用STRAIGHT_JOIN，何时使用INNER JOIN?如果您编写良好的查询，是否有任何理由不使用STRAIGHT_JOIN?
我不建议在没有充分理由的情况下使用STRAIGHT_JOIN.我自己的经验是，MySQL查询优化器选择不良查询计划的频率比我想要的要多，但又不足以使您通常绕开它，如果您始终使用STRAIGHT_JOIN，这就是您要做的.
我的建议是将所有查询保留为常规JOIN.如果发现一个查询正在使用次优查询计划，建议您先尝试重写或重新组织查询，以查看优化程序是否会选择更好的查询计划.另外，至少对于innodb，不仅要确保索引统计信息已过期( 解决方案
I wouldn't recommend using STRAIGHT_JOIN without a good reason. My own experience is that the MySQL query optimizer chooses a poor query plan more often than I'd like, but not often enough that you should just bypass it in general, which is what you would be doing if you always used STRAIGHT_JOIN.
My recommendation is to leave all queries as regular JOINs. If you discover that one query is using a sub-optimal query plan, I would suggest first trying to rewrite or re-structure the query a bit to see if the optimizer will then pick a better query plan. Also, for innodb at least, make sure it's not just that your index statistics are out-of-date (ANALYZE TABLE). That can cause the optimizer to choose a poor query plan. Optimizer hints should generally be your last resort.
Another reason not to use query hints is that your data distribution may change over time, or your index selectivity may change, etc. as your table grows. Your query hints that are optimal now, may become sub-optimal over time. But the optimizer will be unable to adapt the query plan because of your now outdated hints. You stay more flexible if you allow the optimizer to make the decisions.
这篇关于何时在MySQL上使用STRAIGHT_JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
