【MySQL优化】JOIN 的使用优化

摘要:本篇文章是对生产环境的一次慢查询进行优化,优化结果将查询时间从10秒优化到不到1s,在优化的过程中对join优化进行总结,虽然使用强制索引不是一个最佳的办法,但也确实能解决一些场景比较特殊的问题。join优化的目标是尽可能减少joinNested-Loop的循环次数,所以需要让小表做驱动表;关联字段尽量走索引;适当将join_buffer_size调大等等。

优化实践

场景再现:生产环境中一个查询评论内容的SQL语句执行时间长达10秒以上,DBA找到该慢查询语句,未优化前的SQL语句内容如下:

EXPLAIN SELECT
    `xx_answer`.`content` AS `answer_content`
FROM
    `xx_comment`
LEFT JOIN `xx_order_info` ON `xx_order_info`.`order_id` = `xx_comment`.`order_id`
LEFT JOIN `xx_goods` ON `xx_goods`.`goods_id` = `xx_comment`.`id_value`
LEFT JOIN `xx_suppliers` ON `xx_suppliers`.`suppliers_id` = `xx_goods`.`suppliers_id`
LEFT JOIN `xx_comment` AS `xx_answer` ON `xx_comment`.`comment_id` = `xx_answer`.`parent_id`
WHERE
    `xx_comment`.`parent_id` = 0
AND `xx_comment`.`first_is` = 1
AND `xx_goods`.`suppliers_id` IN('3' , '4' , '9')
ORDER BY
    `xx_comment`.`comment_id` DESC
LIMIT 10 OFFSET 20

执行EXPLAIN后的结果如下:

image-20201119104523463

会发现最后一个表连接时Extra中存在Using join buffer (Block Nested Loop)(使用连接缓存,块嵌套循环的算法)。

另一个奇怪的现象是在另一个库2相同的表中执行上面同样的SQL语句却非常快,而且库2中表的数据量比库1中表的数据要多。两个表都有相同的字段和索引结构,parent_id字段也都创建了普通索引:

 KEY `parent_id` (`parent_id`)

使用如下语句对parent_id去重进行分析:

select distinct(parent_id) from xx_comment;

分析后发现,库2表中的有79条,而库1表中的有0条,初步推断在库2的表中因parent_id有79条,优化器在左连接时使用了索引,而没有选择使用Using join buffer (Block Nested Loop)。试着在sql 语句优化中尝试使用强制索引FORCE INDEX(parent_id)如下:

EXPLAIN SELECT
    `xx_answer`.`content` AS `answer_content`
FROM
    `xx_comment` FORCE INDEX(parent_id)
LEFT JOIN `xx_order_info` ON `xx_order_info`.`order_id` = `xx_comment`.`order_id`
LEFT JOIN `xx_goods` ON `xx_goods`.`goods_id` = `xx_comment`.`id_value`
LEFT JOIN `xx_suppliers` ON `xx_suppliers`.`suppliers_id` = `xx_goods`.`suppliers_id`
LEFT JOIN `xx_comment` AS `xx_answer` ON `xx_comment`.`comment_id` = `xx_answer`.`parent_id`
WHERE
    `xx_comment`.`parent_id` = 0
AND `xx_comment`.`first_is` = 1
AND `xx_goods`.`suppliers_id` IN('3' , '4' , '9')
ORDER BY
    `xx_comment`.`comment_id` DESC
LIMIT 10 OFFSET 20

再次执行计划后,如下图所示:

image-20201119104719656

经过优化前和优化后的Explain对比可以发现如下现象:

  • xx_comment 表中不在使用全表扫描ALL,而是使用了ref非唯一性索引扫描,这样不会再使用Using temporary临时表排序和Using filesort文件排序,大大提升查询的效率。

  • xx_answer表中Extra中也已不存在Using join buffer (Block Nested Loop),执行语句后发现速度的确非常快。

问题:为何使用强制索引FORCE INDEX(parent_id)后,优化器就不选择Using join buffer (Block Nested Loop)

MySQL官方文档中 8.8.2 EXPLAIN Output Format[3]提到:MySQL使用Nested-Loop Loin算法处理所有的关联查询。使用这种算法,意味着这种执行模式:

  • 从第一个表中读取一行,然后在第二个表、第三个表...中找到匹配的行,以此类推;

  • 处理完所有关联的表后,MySQL将输出选定的列,如果列不在当前关联的索引树中,那么会进行回表查找完整记录;

  • 继续遍历,从表中取出下一行,重复以上步骤。

多表join不管多少个表join,都是用的Nested-Loop Join实现的。如果有第三个join的表,那么会把前两个表的join结果集作为循环基础数据,在执行一次Nested-Loop Join,到第三个表中匹配数据,更多多表同理。

join走索引(Index Nested-Loop Join):索引嵌套循环join,驱动表越小,复杂度越低,越能提高搜索效率。

join不走索引(Block Nested-Loop Join):没有使用索引时,join连接的过程中就会用到join buffer,本次使用到的是Block Nested Loop Join,同样的也遵循驱动表越小,复杂度越低,越能提高搜索效率。

join使用总结:

  • join优化的目标是尽可能减少joinNested-Loop的循环次数,所以需要让小表做驱动表;

  • 关联字段尽量走索引,这样就可以用到Index Nested-Loop Join了;

  • 如果有order by,请使用驱动表的字段作为order by,否则会使用 using temporary

  • 如果不可避免要用到BNL算法,为了减少被驱动表多次扫描导致的对Buffer Pool利用率的影响,那么可以尝试把 join_buffer_size调大;

  • 为了进一步加快BNL算法的执行效率,我们可以给关联条件加上索引,转换为BKA算法;如果加索引成本较高,那么可以通过临时表添加索引来实现;
  • 如果使用的是MySQL 8.0.18,可以尝试使用hash join,如果是较低版本,也可以自己在程序中实现一个hash join

参考文章

MySQL多表关联之Block Nested-Loop Join

SQL运行内幕:从执行原理看调优的本质

相关推荐

微信扫一扫,分享到朋友圈

【MySQL优化】JOIN 的使用优化
返回顶部

显示

忘记密码?

显示

显示

获取验证码

Close