MySQL优化的奇技淫巧之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优化器都能做出正确的选择。

MySQL优化的奇技淫巧之STRAIGHT_JOIN》上有18条评论

  1. Pingback引用通告: 第四部 » 博客推荐9:火丁笔记(原老王的技术手册)

  2. 我不太赞同这一点 , 对于老王而言 , 随着时间的累积 , 优化 , 可以转变为纯粹对执行计划的推理 。
    但是对于新手而言 , 我不赞同这么做 , 因为 MYSQL 本生的查询优化器会优化它的连接顺序 . 以及所有的 RIGHT JOIN 都会被改写为等价的 LEFT JOIN 以及 , 在某些情况下它会将外连接转换为内连接以获得表的排序优化 .

    综上所述 , 对于新手而言并不建议使用 STRAIGHT_JOIN 这种强制排序 .
    当然,除了它知道,类似第一个连接表有索引,我需要用索引排序,所以必须强制以我的顺序执行 。但这并不怎么好 .

    总而言之 , 言而总之 , 我觉得 , 新手来说 , 那些工作让优化器去做吧,它做的比你好 .

    类似于这种优化器 HIT 。并不推荐新手使用 . 除了 SQL_CACHE,SQL_NO_CACHE,SQL_BUFFER_RESULT 这种新手可以用用,其他的类似权重调节或者是 SQL_SMALL_RESULT , SQL_BIG_RESULT 这些不推荐新手使用 . 交给优化器去做 .

    • 当然 , 我更建议的是将 JOIN 拆开来 , 在应用程序中自己做连接 .
      我看过老王的一些文章觉得还不错 .
      所以和老王探讨下观点 .
      因为 MYSQL 的连接计划只有一种 , 就是 Nested loop join . 嵌套循环连接 . 我相信听到这个名字就知道它是种啥样的计划了因此我就不写伪代码也不写过程了 . 它不同于 ORACLE 有 HASH JOIN , SORT MERGE JOIN .
      对于这种连接计划 , 我的建议 , 一条语句拆为两条写 , 毕竟它是个嵌套循环 . 数据量大的情况下表现可能并不好 , 虽然 , 在 range 和 all 两个层面它可以使用 using buffer join , 但是终归不如自己手动从 A 表取出数据,然后取 B 表相应的数据,自己执行手工连接 .
      这就相当于执行了哈希连接 . 效率嘛 , 我自己肯定是觉得比 MYSQL 搞了 .
      更何况 , 一个连接会锁住所有涉及到的表 . 而分开写没这个问题 .
      再者来说 , 你 JOIN 的表必须位于同一个库,也就意味着必须位于同一台服务器上 。如果,量较大,那么我可以将表位于两个不同的服务器上,无疑等于做了一次垂直拆分 .

      因此 , 在鄙人看来 , mysql 的 join , 尽可能的拆分之 .

    • 同意你的观点!除非使用者清楚自己在干什么,否则不建议大量使用STRAIGHT_JOIN之类的方法,也正因为如此,我把它划为奇技淫巧的范畴,但不可否认的是,有时候,MySQL优化器比较笨,为了性能,不得不使用些非常规手段。

    • 你好,看过你的评论后,向你请教一个mysql join 的问题,你提到mysql join 可以手动拆分自己连接,请问这个怎么理解,有例子可以参考嚒,谢谢啦

    • 同意楼主观点 STRAIGHT_JOIN 是一个算是比较特例的用法, 不建议不熟悉优化的人去调整.
      但对于下面的拆分JOIN的方案却不能赞同.
      拆分后的查询效率虽然高了一点,
      但相对于数据处理, 不论如何, 都需要完整遍历两个结果集进行合并, 却浪费了更多的内存资源(嘛 PHP的HASH Array而言)和时间,

  3. Pingback引用通告: [慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定 今天百乐 | 今天百乐

  4. Pingback引用通告: 一些关于Python, Linux, Mysql, 开发工具大杂烩 | 云帆技术博客

发表评论

电子邮件地址不会被公开。 必填项已用*标注