彻底理清mysql的join|8月更文挑战

328 阅读2分钟

这是我参与8月更文挑战的第5天,活动详情查看:8月更文挑战

前言

如果你只会用join,不懂join,那你可能是得过且过,对sql并没有太大的感触。今天我们就来彻底理解理解join是如何关联多张表的。

select * from table1 t1
left join table2 t2 on t1.id = t2.tid;

如果是你,你会如何设计算法,获取合法的结果集呢?

你可能会取出t1中第一个值,在t2中进行匹配。循环取值比对,直至t1中的取完。如果说t1表有1万条记录,t2表有1万条记录,那么最终次数达到 1 亿次。显然是不行的

for (data1 in t1) {
  for (data2 in t2) {
    if(data1通过on匹配data2) {
      结果
    }
  }
}

显然你的思路是正确的的,那么如何优化这种简单的算法呢?让我们看看mysql是如何设计join算法的,其中你能够感悟到索引的重要性,以及为什么没有使用到索引。

一、索引嵌套循环连接

当t2表的tid建立索引时,就会匹配使用该算法。
思路:取出t1表中第一个值,不与t2表中的记录进行匹配,而是在t2的索引页快速查找。这样就减少与t2表比对的次数

image.png

二、缓存块嵌套循环连接

当不能使用索引的时候,就会匹配使用该算法。
思路:一次取出t1表中多个值,将值与t2表记录进行匹配。减少t1的扫表次数

扫表:从内存读取数据的过程,消耗性能。

image.png

show variables like 'optimizer_switc%'; # 查看Block Nested-Loop Join是否开启,默认开启
show variables like 'join_buffer_size%'; # 查看join缓冲区大小

总结

索引嵌套循环连接(Index Nested-Loop Join):针对循环匹配字数进行优化。
缓存块嵌套循环连接(Block Nested-Loop Join):针对IO次数优化。

优化思路:

  • 小表驱动大表:减少外层循环(外层在这里指的是 t1 表)
  • 为匹配的条件增加索引:较少内层循环(内层在这里指的是t2表)
  • 增大join_buffer_size大小:缓存更多的数据,减少内层的扫表次数。
  • 减少不必要的字段查询:字段越少,join_buffer存储的数据更多。

此时,我想我们不仅仅只明白笛卡尔积,我们还真正明白join是如何工作的。