设为首页收藏本站
查看: 91|回复: 0

[PHP] 在MySQL中使用STRAIGHT_JOIN的教程_MySQL

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    问题
      通过「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优化器有如下两个选择,分别是:
    [ol]
  • 以post为驱动表,通过status_created索引过滤,结果集119340行
  • 以post_tag为驱动表,通过tag_id索引过滤,结果集71220行 [/ol]  显而易见,post_tag过滤的结果集更小,所以MySQL优化器选择它作为驱动表,可悲催的是我们还需要以post表中的created字段来排序,也就是说排序字段不在驱动表里,于是乎不可避免的出现了「Using filesort」,从而导致慢查询。
      知道了来龙去脉,优化起来就容易了。头等大事是务必保证排序字段在驱动表中,所以必须以post是驱动表,于是乎「STRAIGHT_JOIN」就成了答案,它强制了连接顺序。
      …
      不过我总觉得「STRAIGHT_JOIN」这种非标准的语法属于奇技淫巧的范畴,能不用尽量不用,毕竟多数情况下,MySQL优化器都能做出正确的选择。
  • 回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表