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优化器都能做出正确的选择。

原文发布于微信公众号 - nginx(nginx-study)

原文发表时间:2015-09-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

性能为王:SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3775
来自专栏文渊之博

介绍一种非常好用汇总数据的方式GROUPING SETS

介绍   对于任何人而言,用T-SQL语句来写聚会查询都是工作中重要的一环。我们大家也都很熟悉GROUP BY子句来实现聚合表达式,但是如果打算在一个结果集中包...

38811
来自专栏james大数据架构

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2157
来自专栏程序猿DD

一个不可思议的MySQL慢查分析与解决

前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 mysql > delete from testtable WHERE biz_date <= '2...

3465
来自专栏一个爱吃西瓜的程序员

学习SQL【6】-复杂查询

到目前为止,我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上,学习一些实际应用的方法。 一:视图 1:视图和表 表中存...

3419
来自专栏互联网开发者交流社区

SQL触发器实例(下)

1484
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2038
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

2015
来自专栏沃趣科技

SQL优化案例-自定义函数索引(五)

SQL文本如下,表本身很小,走全表扫描也很快,但因业务重要性,要求尽可能缩短查询时间(为保证客户隐私,已经将注释和文字部分去掉):

1033
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4616

扫码关注云+社区

领取腾讯云代金券