何时在 MySQL 中使用 STRAIGHT_JOIN

我刚刚处理了一个相当复杂的查询，运行需要 8 秒.EXPLAIN 显示了一个奇怪的表顺序，即使有 FORCE INDEX 提示，我的索引也没有全部使用.我遇到了 STRAIGHT_JOIN join 关键字并开始用它替换我的一些 INNER JOIN 关键字.我注意到速度有了显着的提高.最终，我只是用 STRAIGHT_JOIN 替换了我所有的 INNER JOIN 关键字，现在它在 0.01 秒内运行.

我的问题是什么时候使用 STRAIGHT_JOIN，什么时候使用 INNER JOIN?如果您正在编写好的查询，是否有任何理由不使用 STRAIGHT_JOIN?

解决方案

如果没有充分的理由，我不建议使用 STRAIGHT_JOIN.我自己的经验是，MySQL 查询优化器选择一个糟糕的查询计划的频率比我希望的要高，但频率不够，一般情况下你应该绕过它，如果你总是使用 STRAIGHT_JOIN，你就会这样做.

我的建议是将所有查询保留为常规 JOIN.如果您发现一个查询使用了次优查询计划，我建议您首先尝试重写或重新构建查询，看看优化器是否会选择更好的查询计划.此外，至少对于 innodb，请确保不仅仅是您的索引统计信息已过时(分析表).这会导致优化器选择一个糟糕的查询计划.优化器提示通常应该是您最后的手段.

不使用查询提示的另一个原因是你的数据分布可能会随着时间的推移而改变，或者你的索引选择性可能会随着你的表的增长而改变等等.您现在最佳的查询提示可能会随着时间的推移变得次优.但是由于您现在已经过时的提示，优化器将无法调整查询计划.如果您让优化器做出决定，您就会更加灵活.

I just had a fairly complex query I was working with and it was taking 8 seconds to run. EXPLAIN was showing a weird table order and my indexes were not all being used even with the FORCE INDEX hint. I came across the STRAIGHT_JOIN join keyword and started replacing some of my INNER JOIN keywords with it. I noticed considerable speed improvement. Eventually I just replaced all my INNER JOIN keywords with STRAIGHT_JOIN for this query and it now runs in .01 seconds.

My question is when do you use STRAIGHT_JOIN and when do you use INNER JOIN? Is there any reason to not use STRAIGHT_JOIN if you are writing good queries?

解决方案

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.