MySQL常见的几个误解

以下内容出于“高性能MySQL”中的第6章。

 

一、 MySQL如何执行关联查询：

1. 很多文章中介绍说MySQL执行关联查询时是先执行from中的内容，因此会先将表根据on中的条件进行关联，之后再通过where中的条件对结果集进行过滤。

如：select tb1.col1, tb2.col2 from tb1 inner join tb2 on tb1.col3 = tb2.col3 where tb1.col1 in (5, 6);

按照这种说法是先执行“tb1 inner join tb2 on tb1.col3 = tb2.col3”，之后再执行“tb1.col1 in (5, 6)”。在这种执行顺序下，能过滤更多数据的条件应该放到on中来处理。

 

2. 今天读到MySQL对任何关联查询都执行“嵌套循环关联”操作，即：先在一张表中循环取出单条数据，然后再嵌套循环到下一张表中寻找匹配的行，依次下去知道找到所有表中匹配的行为止。然后根据匹配的行返回查询中需要的各个列，MySQL会尝试在最后一张关联表中找到所有匹配的行，如果无法找到更多的行则会返回到上一层次关联表。

按照这种说法，具体的执行顺序伪码为：

outer_iter = iterator over tb1 where col1 in (5, 6)

outer_row = outer_iter.next

while outer_row

inner_iter = iterator over tb2 where col3 = outer_row.col3

inner_row = inner_iter.next

while inner_row

output [outer_row.col1, inner_row.col2]

inner_row = inner_iter.next

end

outer_row = outer_iter.next

end

按照这种“嵌套循环关联”的执行顺序，MySQL会优先使用where条件来过滤第一张表，之后再以结果集和关联条件过滤下一张表。在这种情况下能过滤更多数据的条件应该放到where条件中。

 

二、 关联子查询：

如下SQL： select * from film where film_id in (select film_id from film_actor where actor_id = 1);

1. 一般认为MySQL会先执行子查询返回所有包含actor_id为1的film_id，也就是说是这样执行的：

result: select film_id from film_actor where actor_id = 1;

select * from film where film_id in (result);

2. 在实际中，MySQL会将相关的外层表压入到子查询中，也就是说会将查询改写成：

select * from film where exists ( select * from film_actor where actor_id = 1 and film_actor.film_id = film.film_id);

这样的结果是，在执行时会先选择对film表进行全表扫描，然后根据返回的file_id逐个执行子查询。

可以考虑将这个查询修改为：select film.* from film inner join film_actor on film_actor.film_id = film.film_id where actor_id = 1;