这是我参与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表比对的次数
。
二、缓存块嵌套循环连接
当不能使用索引的时候,就会匹配使用该算法。
思路:一次取出t1表中多个值,将值与t2表记录进行匹配。减少t1的扫表次数
。
扫表:从内存读取数据的过程,消耗性能。
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
是如何工作的。