在MySQL中使用STRAIGHT_JOIN的教程
问题
通过「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」,综合来看,性能依然得到了提升。
解释
对第一条sql而言,为什么mysql优化器选择了一个耗时的执行方案?对第二条sql而言,为什么把连接方式改成straight_join之后就提升了性能?
这一切还得从mysql对多表连接的处理方式说起,首先要确定以谁为驱动表,也就是说以哪个表为基准,在处理此类问题时,mysql优化器采用了简单粗暴的解决方法:哪个表的结果集小,就以哪个表为驱动表,通常这都是最佳选择。
说明:在explain结果中,第一行出现的表就是驱动表。
继续post连接post_tag的例子,mysql优化器有如下两个选择,分别是:
- 以post为驱动表,通过status_created索引过滤,结果集119340行
- 以post_tag为驱动表,通过tag_id索引过滤,结果集71220行
显而易见,post_tag过滤的结果集更小,所以mysql优化器选择它作为驱动表,可悲催的是我们还需要以post表中的created字段来排序,也就是说排序字段不在驱动表里,于是乎不可避免的出现了「using filesort」,从而导致慢查询。
知道了来龙去脉,优化起来就容易了。头等大事是务必保证排序字段在驱动表中,所以必须以post是驱动表,于是乎「straight_join」就成了答案,它强制了连接顺序。
…
不过我总觉得「straight_join」这种非标准的语法属于奇技淫巧的范畴,能不用尽量不用,毕竟多数情况下,mysql优化器都能做出正确的选择。
推荐阅读
-
mysql中索引使用不当速度比没加索引还慢的测试_MySQL
-
在WordPress中实现评论头像的自定义默认和延迟加载,wordpress头像_PHP教程
-
php文章中图片处理的使用_PHP教程
-
thinkphp3.0 模板中函数的使用_PHP教程
-
浅谈PHP第三弹---使用二分查找法查找数组中的元素位置_PHP教程
-
WordPress中缩略图的使用以及相关技巧,wordpress缩略图_PHP教程
-
解析在PHP中使用mysqli扩展库对mysql的操作_PHP教程
-
mysql-怎样在带有数据库的网站中设置用户权限?
-
smarty模板引擎中内建函数if、elseif和else的使用方法,smartyelseif_PHP教程
-
session在PHP大型web应用中的使用