MySQL优化二之子查询

SQL样例： select * from table_a where id in ( select id from table_b where name = '' ) 

1. MySQL 5.6以下的子查询都会全表扫描table_a

然后每条记录去和子查询select id from table_b where name = '' 来join

2.但是理想的情况是 ：

先查询出子查询里面的内容再和外面的查询进行in操作

测试：

5.5版本

子查询：

explain SELECT * FROM `mytest`.`table_rm001` as t1 where id in ( 

    select id from `mytest`.`table_rm001` as t2 where t2.`bigint` = 499999    

)

select_type

type

•   unique_subquery

  This type replaces  ref for some  IN subqueries of the following form:

             value IN (SELECT            primary_key FROM            single_table WHERE            some_expr)
  

  unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

上面的子查询t2里面查询的是ID，ID又是主键，是聚簇索引，所以才走primary 。

其实一般我们想的是t1能够走主键索引,这样效率比较高。

下面我们换个字段来看看

子查询二：

 explain select * from `test_xiaogu`.`table_rm001` as t1, 

(select id from `test_xiaogu`.`table_rm001` as t2 where t2.`bigint` = 499999 ) as t3

  where t1.id = t3.id

修改成join

explain select * from `mytest`.`table_rm001` as t1, 

(select id from `mytest`.`table_rm001` as t2 where t2.`bigint` = 499999 ) as t3

    where t1.id = t3.id

table 

<derived N>: The row refers to the derived table result for the row with an  id value of  N. A derived table may result, for example, from a subquery in the  FROM clause

5.6 版本

select version()

 子查询：

explain SELECT * FROM `test_xiaogu`.`table_rm001` as t1 where id in ( 

    select id from `test_xiaogu`.`table_rm001` as t2 where t2.`bigint` = 499999    

)

 5.6 改成join

   explain select * from `test_xiaogu`.`table_rm001` as t1, 

(select id from `test_xiaogu`.`table_rm001` as t2 where t2.`bigint` = 499999 ) as t3

    where t1.id = t3.id