MySQL优化二之子查询

 

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

 

场景说明：

MySQL 5.6以下的子查询都会全表扫描table_a 然后每条记录去和子查询 table_b  来做join操作

 

理想的情况是 ：

先查询出子查询里面的内容再和外面的查询进行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

DEPENDENT SUBQUERY

First SELECT in subquery, dependent on outer query

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

id	The SELECT identifier

table 

<derivedN>: 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