mysql优化join_MySQL优化的奇技淫巧之STRAIGHT_JOIN

我就是我 2023-01-12 07:51 181阅读 0赞

最近没怎么搞SQL优化，碰巧数据库被慢查询搞挂了，于是拿来练练手。

问题

通过「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」，综合来看，性能依然得到了提升。

提醒：注意两次EXPLAIN结果中各个表出现的先后顺序，稍后会解释。

解释

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

这一切还得从MySQL对多表连接的处理方式说起，首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：MySQL优化器如何选择索引和JOIN顺序。

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

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

以post为驱动表，通过status\_created索引过滤，结果集119340行

以post\_tag为驱动表，通过tag\_id索引过滤，结果集71220行

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

知道了来龙去脉，优化起来就容易了，要尽可能的保证排序字段在驱动表中，所以必须以post为驱动表，于是乎必须借助「STRAIGHT\_JOIN」强制连接顺序。

实际上在某些特殊情况里，排序字段可以不在驱动表里，比如驱动表结果集只有一行记录，并且在连接其它表时，索引除了连接字段，还包含了排序字段，此时连接表后，索引中的数据本身自然就是排好序的。

既然聊到这里顺带说点题外话，大家可能会遇到类似下面的问题：原本运行良好的查询语句，过了一段时间后，可能会突然变得很糟糕。一个很大可能的原因就是数据分布情况发生了变化，从而导致MySQL优化器对驱动表的选择发生了变化，进而出现索引失效的情况，所以没事最好多查查，关注一下这些情况。

…

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

发表评论取消回复

表情：

评论列表（有 0 条评论，181人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql优化join_MySQL优化的奇技淫巧之STRAIGHT_JOIN

最近没怎么搞SQL优化，碰巧数据库被慢查询搞挂了，于是拿来练练手。问题通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL，如下： SELE

我就是我/ 2023年01月12日 07:51/ 0 赞/ 182 阅读

相关 Android Studio 奇技淫巧

> 这篇文章篇幅较长，可以使用版权声明下面的目录，找到感兴趣的进行阅读目录 > 3.1 Android Studio使用初探 > > >

红太狼/ 2022年07月14日 03:57/ 0 赞/ 342 阅读

相关 Java奇淫巧技之Lombok

Lombok是一个可以通过简单的注解形式来帮助我们简化消除一些必须有但显得很臃肿的Java代码的工具，通过使用对应的注解，可以在编译源码的时候生成对应的方法。官方地址：[ht

港控/mmm°/ 2022年06月16日 07:10/ 0 赞/ 248 阅读

相关 Java奇淫巧技之Lombok

from: http://blog.csdn.net/ghsau/article/details/52334762 背景我们在开发过程中，通常都会定义大量

爱被打了一巴掌/ 2022年06月10日 07:57/ 0 赞/ 260 阅读

相关 Java奇淫巧技之Lombok

奇淫巧技之Lombok -------------------- 一、Lombok介绍 > Lombok是一个可以通过简单的注解形式来帮助我们简化消除一些必

「爱情、让人受尽委屈。」/ 2022年06月06日 07:55/ 0 赞/ 267 阅读

相关 JS 奇淫巧技

ps: js另外一种高深写法，只不过不适合基础太差的想故意装逼的，因为你要是有意而为之，只能是披着羊皮的狼，玩死自己。要不叫做JsFuck。可以自行JsFuck。什么叫基础

桃扇骨/ 2022年05月24日 02:30/ 0 赞/ 286 阅读

相关 Java奇淫巧技之Lombok

Lombok是一个可以通过简单的注解形式来帮助我们简化消除一些必须有但显得很臃肿的Java代码的工具，通过使用对应的注解，可以在编译源码的时候生成对应的方法。官方地址：[ht

你的名字/ 2022年04月06日 03:14/ 0 赞/ 296 阅读

相关 Java奇淫巧技之Lombok

Lombok是一个可以通过简单的注解形式来帮助我们简化消除一些必须有但显得很臃肿的Java代码的工具，通过使用对应的注解，可以在编译源码的时候生成对应的方法。官方地址：[ht

╰+攻爆jí腚メ/ 2022年04月06日 03:14/ 0 赞/ 269 阅读

相关 Java奇淫巧技之Lombok

背景我们在开发过程中，通常都会定义大量的JavaBean，然后通过IDE去生成其属性的构造器、getter、setter、equals、hashcode、toStr

悠悠/ 2022年03月26日 03:48/ 0 赞/ 327 阅读

相关 Java奇淫巧技之Lombok

Lombok是一个可以通过简单的注解形式来帮助我们简化消除一些必须有但显得很臃肿的Java代码的工具，通过使用对应的注解，可以在编译源码的时候生成对应的方法。官方地址：[htt

一时失言乱红尘/ 2022年02月26日 08:54/ 0 赞/ 298 阅读