(转)MySQL 优化的奇技淫巧之 STRAIGHT_JOIN - (sunznx) 振翅飞翔
28 August 2019
原文: MySQL 优化的奇技淫巧之 STRAIGHT_JOIN

来自 Mysql Join 语法解析与性能分析

straight_join 完全等同于 inner join 只不过,join 语法是根据 “哪个表的结果集小,就以哪个表为驱动表” 来决定谁先载入的,而 straight_join 会强制选择其左边的表先载入。

最近没怎么搞 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 优化器都能做出正确的选择。