sql join算法指的是当我们的sql join关联查询的时候,底层的逻辑是怎么实现的。
一、 simple nested_loop join 简单的嵌套查询算法
simple nested_loop join 算法类似于我们两个for循环
当我们R表和S表join查询(select * from r,s where r.id = s.r_id)的时候,simple nested_loop join算法就是直接拿R表的id去S表的r_id去对比,例如id = 1,分别到S表中的[1,2,3,4] 中去对比。如果相等就返回成功。这种算法的效率比较慢,所以mysql的不采用这种算法。
for(var i in [1,2,3]) {
for(var j in [1,2,3,4]) {
if (i == j) {
return true;
}
return flase;
}
}
二、 index nested_loop join 索引的嵌套查询算法
索引的嵌套查询算法指的是,当S表和R表有一方有索引的时候,查询的算法。
假设我们现在在S表的r_id的字段建立索引
当我们R表和S表join查询(select * from r,s where r.id = s.r_id)的时候, 在R表的id去对比S表的r_id时, 并不是在S表中一条条去对比,而是通过S表中 r_id 的索引来比较。因为索引的IO肯定比较小,效率比较高。例如下图:
驱动表和内表
当r_id有索引时,查询 (select * from r,s where r.id = s.r_id) ,那么现在我们称R表为驱动表,S表为内表。 意思就是R表先全部查询出来,作为驱动然后循环的到内表S表查询。这里就是需要区分驱动表和内表。
那么问题来了:msyql的优化器是怎么选择驱动表和内表的?
一般优化器都是选择小表为驱动表,大表为内表,这是为什么?
现在有A表 100W, B 表10W。
①我们假如A表为驱动表,那么我们需要先扫描A表,假如A表和B表的B+ tree高度都是3,那么IO就是 100w*3. 然后通过索引去查询B表。
②我们假如B表为驱动表,那么我们需要先扫描B表,假如A表和B表的B+ tree高度都是3,那么IO就是 10w*3. 然后通过索引去查询A表。
假设上面的索引查询速度都是很小的,那么30w的IO肯定比300W的IO小,所以肯定会以小表为驱动表更划算的。
三、 block nested-loop join 缓存的嵌套查询算法
block nested-loop join算法,不是像简单的嵌套查询算法一样,每条记录去对比每条记录,而是先把R表的所有记录拿出来后放到join buffer的内存中,以空间换时间,然后通过内存数据一次性的去跟S表的每条记录进行对比。减少了内表表的扫描次数。
我们这里把block nested-loop join算和简单的嵌套查询算法进行比较。
假如A表(1,2,3) B表(1,2,3,4), A表为外表,B表为内表
简单嵌套算法 | 缓冲嵌套算法 | |
外表扫描次数 | 1 | 1 |
内表扫描次数 | 3 | 1 |
比较次数 | 3*4 = 12 | 3*4 = 12 |
解析:外表扫描次数都是1次,这里的扫描是指的取出数据,一次性到B+ tree取出数据,
简单的嵌套算法,内表的扫描次数为什么是3,是因为外表3条记录,每次都需要到B表比较。
缓冲的嵌套算法,内表的扫描次数为什么是1,是因为外表的3条记录放入了join buffer存储后,一次性的跟内表比较。 所以缓冲嵌套算法优势也就是减少了内表的扫描次数。
注意:Join buffer用于连接是ALl, index, range的类型, Join buffer只存储需要进行查询操作的相关列数据,而不是整行的记录。
所以我们应该调大join buffer的值,默认是256KB
四、 batched key access join
这个主要是对block nested-loop join升级,当我们关联的列是二级索引,然后需要回表的话,这里对主键进行了排序,进行了MRR操作。