MySQL 表连接优化

2021-10-08 05:20:48

概述

表连接是业务场景中经常会出现的一种SQL类型，一般的，复杂的业务会存在大量的表连接查询。就性能而言，多表的连接的性能会很低。做好表连接优化也是提升业务性能的重要方面

表连接语句

认识表连接之前，先认识下表连接的类型。一般的，表三种常见的表连接方式:

  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

inner join
只显示两表相互匹配的行

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a inner join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读，汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏，电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数：[3]，耗时：6 ms.

left join
可以看到，左表中有的行而右表中的没有，右表的字段会用null值显示出来

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读，汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏，电影 |
| 5 | 库洛洛 | 2 | 四川成都 | | |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数：[4]，耗时：3 ms.

right join
右表中有的左表中没有行，会显示右表中的行，左表中会null值显示

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a right join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读，汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏，电影 |
| | | | | 宇航员 | 悠悠球，篮球 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数：[4]，耗时：4 ms.

MySQL表连接算法

Nested-Loop Join算法

mysql的基本表连接算法是Nested-Loop Join，也就是我们常说的嵌套循环算法。一个简单的嵌套循环连接（NLJ）算法一次一个循环地从第一个表中读取行，将每一行传递给一个嵌套循环，该循环处理连接中的下一个表 。

对于一个t1,t2,t3，官方文档上给出一个伪代码如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

这里是假设有三张表t1,t2,t3.分别使用类型为range，ref，all来连接，从伪代码中可以看出
t3表会被全表扫，然后依次去读取t1和t2上 的数据，t3一般就被称为驱动表，MySQL 的优化器自称是可以智能选择结果集最小的表作为驱动表，根据算法来看，结果集较小的驱动表确实可以使循环次数减少，达到优化的目的。

Block Nested-Loop join算法

除了嵌套循环之外，官方文档上还介绍了 一种块循环算法，将外层循环的数据存在join buffer中，内层循环中的表会和buffer中的数据进行对比，从而减少循环次数
官方文档给出一个表达式，来表述循环次数的关系

(S * C)/join_buffer_size + 1

以上面的t1,t2,t3三表连接为例，S表示t1，t2组合在缓存中的大小，C是这些组合在buffer中的数量，整个式子就是t3被扫描的次数

可以看出，join_buffer_size越大，扫描的次数越小，但是这个优化有上限，当join_buffer_size大到能够缓存所有之前的行组合，那么就是性能最好的时候，再增大这个值，也就没有优化效果了。

join 优化总结

从基础算法可以看出，一般情况下，这种嵌套循环的算法消耗非常高，尤其是时间上的消耗，我们在创建涉及到表连接的SQL时，一定要选择最优性能的SQL

一般的，表连接的SQL性能优化可从这几个方面入手

  • 索引优化
    在有索引的情况下，MySQL会尝试使用Index Nested-Loop Join算法，相对于简单的嵌套循环，性能会好一些。这就需要在必要的字段上建上索引

那对于左连接和右连接，建索引的列有不同，对于left join，左边的表会全部返回，右边则只返回匹配到的行，所以一般的，左连接的连接条件字段中，右表的字段最好需要一个索引，用来过滤不需要的行。相对的，对于right join 而言，表连接字段的左表字段的索引就显得十分重要了

那有时除了匹配字段之外，表连接会伴随着一些条件语句，如：

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid where a.sex = 1;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读，汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏，电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+

对于这些有条件字段的语句，毫无疑问，需要索引的支持，过滤掉无需要的行数，来减少循环的次数。从而使SQL执行的更加迅速

  • 驱动表优化
    当进行多表连接查询时，驱动表的定义为：

1）指定了联接条件时，满足查询条件的记录行数少的表为驱动表
2）未指定联接条件时，行数少的表为驱动表

注意：如果搞不清楚该让谁做驱动表，谁join谁，可以让MySQL运行时自行判断

对于left和right join mysql的驱动表选择一般都是需要返回全部行的那个表，对于inner join和full join，一般情况下，mysql自己的优化器，智能选择较少的表作为驱动表，这样循环的效率会高很多。

此外，根据驱动表的字段进行排序，也会使表连接性能提升

select * from t1 a left join t2 b on a.id = b.id left join t3 c a.id = c.id ORDER BY a.id DESC ;

如果你实在难以确定驱动表的选择，可以去掉join,如下，mysql会选择最少的表作为驱动表，这会对你的选择给出建议

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a,user_info b where a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读，汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏，电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+

  • join_buffer_size

    在join的操作需求，MySQL在完成某些join需求的时候（all row join/all index /scan join）为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作，当join buffer 太小，MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中，如此往复，这势必会造成被驱动表需要被多次读取，成倍增加IO访问，降低效率（执行计划中如果现实using join buffer）

    也可以从上面的循环次数的配置可以看出，当join_buffer_size的值增大时，是可以减少循环的次数，相当于进行了一些优化

(S * C)/join_buffer_size + 1

MySQL5.7版本这个参数的默认值256K ，若是感觉过小，可以适当增加。最大值不能超过3GB

上一篇：PostgreSQL数据备份策略


下一篇：MySQL主从复制学习