mysql为什么用了子查询后，主查询没走主键索引-灵析社区
霹雳青
万码007这是一个非常非常old的问题.mysql 的in子查询 永远被 转为exists 查询, 具体见手册http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html
第一条limitation.演示一下:mysql> explain extended select id from yanse where name in (select name from yan
se);
...
mysql> show warnings;
+-------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
| Note | 1003 | select `test1`.`yanse`.`id` AS `id` from `test1`.`yanse` where
<in_optimizer>(`test1`.`yanse`.`name`,<exists>(select 1 from `test1`.`yanse` whe
re (<cache>(`test1`.`yanse`.`name`) = `test1`.`yanse`.`name`))) |
+-------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.00 sec)
in 和exists的不同:https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074
Select * from T1 where x in ( select y from T2 ) is typically processed as: select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y; The subquery is evaluated, distinct'ed, indexed (or hashed or sorted)
and then joined to the original table -- typically. As opposed to select * from t1 where exists ( select null from t2 where y = x ) That is processed more like:
> for x in ( select * from t1 ) loop
> if ( exists ( select null from t2 where y = x.x )
> then
> OUTPUT THE RECORD
> end if end loop