mysql子查询为什么不走索引_mysql为什么用了子查询后，主查询没走主键索引

这是一个非常非常old的问题.

演示一下:

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

(`test1`.`yanse`.`name`,(select 1 from `test1`.`yanse` whe

re ((`test1`.`yanse`.`name`) = `test1`.`yanse`.`name`))) |

+-------+------+----------------------------------------------------------------

--------------------------------------------------------------------------------

----------------------------------------------------------------+

1 row in set (0.00 sec)

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