Mysql执行顺序&Join原理(360面试题)

目录

一、Mysql执行顺序

二、Mysql的Join原理

（1）Simple Nested-Loop Join(简单嵌套循环连接)

（2）Index Nested-Loop Join(索引嵌套循环连接)---减少匹配次数

（3）Block Nested-Loop Join(阻塞嵌套循环连接)---减少非驱动表的访问次数

三、Join过程示例解析

---

一、Mysql执行顺序

from--->join,on--->where--->group by,having--->select,distinct--->order by,limit

1、FROM  table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1
2、JOIN table2  所以先是确定表，再确定关联条件
3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2
4、WHERE  对中间表Temp2产生的结果进行过滤  产生中间表Temp3
5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4
6、HAVING  对分组后的记录进行聚合 产生中间表Temp5
7、SELECT  对中间表Temp5进行列筛选，产生中间表 Temp6
8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7
9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8
10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

 

二、Mysql的Join原理

join主要有3种方式：Nested-Loop(嵌套循环)、Hash Join(哈希)、Merge Join(归并)
但Mysql只支持一种join算法：Nested-Loop Join(嵌套循环连接)，Nested-Loop Join(嵌套循环连接)有三种变种：
Simple Nested-Loop Join(简单嵌套循环连接)，Index Nested-Loop Join(索引嵌套循环连接)，Block Nested-Loop Join(阻塞嵌套循环连接)

假设r为驱动表(也叫：外表)，s为匹配表(也叫：非驱动表/内表)。简单来说，驱动表就是主表，left join 中的左表就是驱动表，right join 中的右表是驱动表。

a结构： comments_id        bigInt(20)    P for_comments_if    mediumint(9) product_id         int(11) order_id           int(11) ...

b结构： id            int(11)       p comments_id   bigInt(20) product_id    int(11) key comments_idx (comments_id) ...

图1：简单嵌套循环

图2：索引嵌套循环

图3：阻塞嵌套循环

（1）Simple Nested-Loop Join(简单嵌套循环连接)

说明：从r中分别取出r1、r2、......、rn去匹配s表的左右列，然后再合并数据，对s表进行了r*n次访问，对数据库开销大。

（2）Index Nested-Loop Join(索引嵌套循环连接)---减少匹配次数

说明：这个要求非驱动表上有索引列，可以通过索引来减少匹配次数来加速查询。查询时，驱动表r会根据关联字段的索引进行查找，挡在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表s的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。
索引一般采用B+树的存储结构，可以减少匹配次数，可以提高效率。因为主键必须是唯一的。所以如果被驱动表是用主键去连接，只会出现多对一或者一对一的情况，而不会出现多对多和一对多的情况。

示例1：如下使用的是Index Nested-Loop Join，先对驱动表a的主键筛选，得到一条，然后对非驱动表b的索引进行seek匹配，预计得到一条数据。
SELECT * FROM a gc
JOIN b gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

（3）Block Nested-Loop Join(阻塞嵌套循环连接)---减少非驱动表的访问次数

说明：索引匹配是最高效，但现实中驱动表上不一定有索引列，这时索引列就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。
默认情况下join_buffer_size=256K，在查找的时候MySQL会将所需要的列，包括on列和select的列，先缓存到join buffer当中，然后再去和非驱动表进行匹配。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
与（1）相比，所需要的匹配次数一样，差别就在于驱动表的列数不同，也就是数据量的多少不同。

示例1：使用Block Nested-Loop Join，如果b表数据少，作为驱动表，将b的需要的数据缓存到join buffer中，批量对a表扫描。
SELECT * FROM a gc
JOIN b gcf ON gc.order_id=gcf.product_id

left join示例：
（1）如下用到了索引，所以会采用Index Nested-Loop Join，因为没有筛选条件，会选择一张表作为驱动表去进行join，去关联非驱动表的索引。
BlockSELECT * FROM a gc
LEFT JOIN b gcf ON gc.comments_id=gcf.comments_id
（2）加了where条件后，就会从驱动表筛选出一条来进行对非驱动表的匹配。
SELECT * FROM b gcf
LEFT JOIN a gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056
 

三、Join过程示例解析

left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。

right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

join(inner join): 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

full join:外连接，返回两个表中的行：left join + right join。

cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

关键字: on

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程： 1、中间表 on条件: tab1.size = tab2.size tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA 2 20 20 BBB 2 20 20 CCC 3 30 (null) (null)       2、再对中间表过滤 where 条件： tab2.name=’AAA’ tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA

 

第二条SQL的过程： 1、中间表 on条件: tab1.size = tab2.size and tab2.name=’AAA’ (条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA 2 20 (null) (null) 3 30 (null) (null)

总结：

left join,right join	不管on上的条件是否为真都会返回left或right表中的记录
full join	具有left和right的特性的并集
inner join	条件放在on中和where中，返回的结果集是相同的

 

转自： http://www.cnblogs.com/Jessy/p/3525419.html