在MySQL中使用STRAIGHT_JOIN的教程

2024-07-24 12:45:57

字体：大中小

来源：转载

供稿：网友

问题

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL，如下：

SELECT post.*FROM postINNER JOIN post_tag ON post.id = post_tag.post_idWHERE post.status = 1 AND post_tag.tag_id = 123ORDER BY post.created DESCLIMIT 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 postSTRAIGHT_JOIN post_tag ON post.id = post_tag.post_idWHERE post.status = 1 AND post_tag.tag_id = 123ORDER BY post.created DESCLIMIT 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优化器有如下两个选择，分别是：

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

上一篇：MySQL Index Condition Pushdown(ICP)性能优化方法实例

下一篇：MySQL order by性能优化方法实例