mysql（九）MySQL如何执行关联查询

MySQL中“关联”一次所包含的意义比一般意义上理解的要更广泛。总的来说，MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。

所以理解MySQL如何执行关联查询至关重要。我们先来看一个UNION查询的例子。对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表时也是一次关联。

当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL现在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。

按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现——正如其名“嵌套循环关联”。请看下面的例子中的简单查询：

mysql>select tbl1.col1,tbl2.col2

          ->from tbl1 inner join tbl2 using(col3)

           ->where tbl1.col1 in(5,6);

假设MysQL按照查询中的表顺序进行关联操作，我们则可以用下面的伪代码表示MySQL将如何完成这个查询：

List tbl1 = tbl中col1为5，6的行；
        for(tbrow : tb1){
            List tb2 = （tbl2中col3=tbrow中col3的行）
            for(tb2row : tb2){
                输出【tbl1.col1,tbl2.col2】
            }
        }
           

上面的查询对于单表和多变关联查询都适用，如果是一个单表查询，那么只需完成上面外层的基本操作。对于外连接上面的执行过程仍然适用。例如，我们将上面查询修改如下：

mysql>select tbl1.col1,tbl2.col2

         ->from tbl1 left join tbl2 using(col3)

          ->where tbl1.col1 in(5,6)

对应的伪代码如下，我们用黑体标示出不同的部分：

List tbl1 = tbl中col1为5，6的行；
        for(tb1row : tb1){
            List tb2 = （tbl2中col3=tb1row中col3的行）
            for(tb2row : tb2){
                if(tbl1row.col3== tbl12row.col3) {
                    输出[tbl1.col1, tbl2.col2]
                }else{
                    输出【tbl1.col1,null】
                }
            }
        }
           

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中,然后将这个临时表当作一个普通表对待（正如其名“派生表”）。MySQL在执行UNION查询时也使用类似的临时表，在遇到又外连接的时候MySQL将其改为等价的左外连接。简而言之MysQL会将所有的查询类型都转换成类似的执行计划。

执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行explain extended后，在执行show warnings，就可以看到重构出的查询。

任何多表查询都可以用一棵树表示，mysql总是从一个表开始一直嵌套循环，回溯完成所有表关联。所以，MySQL的执行计划总是如下图所示：，这是一棵左侧深度优先的树。

关联查询中，任何的子查询都可以被改编成左连接或者内连接，但他们的查询效率因为查询执行器的关系可能会有所不同，例如下面这句： mysql>select * from sakila.film          ->where film_id in(select film_id from sakila.film_actor where actor_id = 1); 我们可能以为它会先执行in语句里面的句子，再做外部关联，实际上，查询执行器，因为film_id要关联外部表，所以我们可以通过（explain extended来看这个查询被改成了什么样子）： select * from sakila.film where exists(selct * from sakila.film_actor where actor_id =1 and film_actor.film_id = film.film_id);

通过explain我们可以看到，子查询是一个相关子查询 ---------------------------------------------------------------------------------------------------------------------------- id       select_type                                  table             type                 possible_keys 1         primary                                         film               ALl                     null 2    DEPENDENT  SUBQUERY      film_actor       eq_ref          primary,idx_fk_film_id

根据explain我们可以看到，MySQL先选择对file表进行全表扫描，然后根据返回的film_id逐个执行子查询。这个时候我们可以通过重写这个查询： mysql>select film.* from sakila.film          ->inner join sakila.film_actor using(film_id)           ->where actor_id = 1;

但也并不是所有的左连接或者内链接查询都比子查询要快，我们这时候就要记住一条准则：通过explain查看执行具体细节，通过explain extended查看查询执行器改写后的语句,执行show warnings会将上句查询的sql优化后的语句查询出来。

上面的查询对于单表和多变关联查询都适用，如果是一个单表查询，那么只需完成上面外层的基本操作。对于外连接上面的执行过程仍然适用。