在MySQL中使用STRAIGHT_JOIN的教程

问题

   通过「show full processlist」语句很容易就能查到问题sql，如下：

select post.*
from post
inner join post_tag on post.id = post_tag.post_id
where post.status = 1 and post_tag.tag_id = 123
order by post.created desc
limit 100

   说明：因为post和tag是多对多的关系，所以存在一个关联表post_tag。

   试着用explain查询一下sql执行计划(篇幅所限，结果有删减)：

+----------+---------+-------+-----------------------------+
| table  | key   | rows | extra            |
+----------+---------+-------+-----------------------------+
| post_tag | tag_id | 71220 | using where; using filesort |
| post   | primary |   1 | using where         |
+----------+---------+-------+-----------------------------+

   下面给出优化后的sql，唯一的变化就是把连接方式改成了「straight_join」：

select post.*
from post
straight_join post_tag on post.id = post_tag.post_id
where post.status = 1 and post_tag.tag_id = 123
order by post.created desc
limit 100

   试着用explain查询一下sql执行计划(篇幅所限，结果有删减)：

+----------+----------------+--------+-------------+
| table  | key      | rows  | extra    |
+----------+----------------+--------+-------------+
| post   | status_created | 119340 | using where |
| post_tag | post_id    |   1 | using where |
+----------+----------------+--------+-------------+

   对比优化前后两次explain的结果来看，优化后的sql虽然「rows」更大了，但是没有了「using filesort」，综合来看，性能依然得到了提升。
解释

   对第一条sql而言，为什么mysql优化器选择了一个耗时的执行方案？对第二条sql而言，为什么把连接方式改成straight_join之后就提升了性能？

   这一切还得从mysql对多表连接的处理方式说起，首先要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，mysql优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，通常这都是最佳选择。

   说明：在explain结果中，第一行出现的表就是驱动表。

   继续post连接post_tag的例子，mysql优化器有如下两个选择，分别是：

1.     以post为驱动表，通过status_created索引过滤，结果集119340行
2.     以post_tag为驱动表，通过tag_id索引过滤，结果集71220行

       显而易见，post_tag过滤的结果集更小，所以mysql优化器选择它作为驱动表，可悲催的是我们还需要以post表中的created字段来排序，也就是说排序字段不在驱动表里，于是乎不可避免的出现了「using filesort」，从而导致慢查询。

       知道了来龙去脉，优化起来就容易了。头等大事是务必保证排序字段在驱动表中，所以必须以post是驱动表，于是乎「straight_join」就成了答案，它强制了连接顺序。

       …

       不过我总觉得「straight_join」这种非标准的语法属于奇技淫巧的范畴，能不用尽量不用，毕竟多数情况下，mysql优化器都能做出正确的选择。