1.Simple Nested-Loops Join算法
1.1概念
从一张表(外部表)中每次读取一条记录,然后将记录与内部表中的记录进行比较。
对于外部表R,内部表S,并且不存在索引
扫描成本O(Rn*Sn),Rn和Sn表示对应表的记录数
1.2 算法
算法如下:
For each row r in R do //从表R取出一条记录r
For each row s in S do // 从表S取出一条记录s
If r and s satisfy the join condition // 判断他们是否满足条件
Then output the tuple <r, s> // 满足条件则输出记录
1.3 关于索引情况
但是当内部表对所联接的列含有索引时,Simple Nested-Loops Join算法可以利用索引的特性来进行快速匹配,此时的算法调整为如下:
For each row r in R do //从表R取出一条记录r
lookup r in S index // 根据索引 从表S中寻找满足联接条件的记录是否存在
If find s == r //如果找到
Then output the tuple <r, s> //则输出记录
对于联接的列含有索引的情况,外部表的每条记录不再需要扫描整张内部表,只需要扫描内部表上的索引即可得到联接的判断结果。
在INNER JOIN中,两张联接表的顺序是可以变换的,根据前面描述的Simple Nested-Loops Join算法,优化器在一般情况下总是选择将联接列含有索引的表作为内表。如果两张表R和S在联接列上都有索引,并且索引的高度相同,那么优化器会选择记录数少的表作为外部表,这是因为内部表的扫描次数总是索引的高度,与记录的数量无关。
结论
添加索引时需要往数据多的表加索引
具体示例:
mysql-联接查询-增加索引
1.4 复杂情况
1.4.1存在where条件,而且是多张表
SELECT * FROM T1
INNER JOIN T2 ON P1 (T1, T2 )
INNER JOIN T3 ON P2 (T2,T3)
WHERE P (T1, T2, T3)
执行时的程序可能是
FOR each row t1 in T1{
FOR each row t2 in T2 such that P1(t1,t2) {//对于记录t1和t2 满足联接条件P1
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3){ //如果满足where 条件
t:=t1||t2||t3; //赋值么?
OUTPUT t; //输出结果
}
}
}
}
1.4.2 但是可能存在表的执行顺序优化
FOR each row t3 in T3 {
FOR each row t2 in T2 such that P1(t2,t3) {
FOR each row t1 in T1such that P2(t1,t2) {
IF P(t1,t2,t3){ /
t:=t1||t2||t3; //
OUTPUT t; //输出结果
}
}
}
}
1.4.3 push-down-conditions优化
内部的条件可能会放到外部表中去判断,减少外部表循环次数,内部表循环次数也会大大减少,整体循环次数就大大降低了
P(t1,t2,t3)等价于C1(t1)&C2(t2)&C3(t3)
所以优化为
FOR each row t1 in T1 such that C1(t1){
FOR each row t2 in T2 such that P1(t1,t2) and C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) and C3(t3){
IF P(t1,t2,t3){ //如果满足where 条件
t:=t1||t2||t3; //
OUTPUT t; //输出结果
}
}
}
}
注意:outer join是不能优化执行顺序,还需要进行一个是否outer join的判断
2.Block Nested-Loops Join算法
2.1 简介
如果联接表没有索引时,Simple Nested-Loops Join算法扫描内部表很多次,执行效率会非常差。而Block Nested-Loops Join算法就是针对没有索引的联接情况设计的,其使用Join Buffer(联接缓存)来减少内部循环取表的次数。
2.2 概念
例如,当缓存块大小为10时
Block Nested-Loops Join算法先把对Outer Loop表(外部表)每次读取的10行
记录(准确地说是10行需要进行联接的列)放入Join Buffer中,然后在Inner Loop表(内部表)中直接匹配这10行数据。因此,对Inner Loop表的扫描减少了1/10。对于没有索引的表来说,Block Nested-Loops Join算法可以极大地提高联接的速度。
具体示例:
mysql-联接查询-增加索引
EXPLAIN select * from dept_emp as d inner join salaries as s on s.to_date=d.to_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 110 | 100.00 | NULL |
| 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 2814 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
在 Extra 这一列中指出了使用到了join buffer。当在使用联接查询,并且没有加上索引的情况下,mysql会自动帮我们使用join buffer优化查询次数
ps:可以通过系统变量设置Join Buffer的大小