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

关于网友提出的“mysql为什么用了子查询后，主查询没走主键索引
”问题疑问，本网通过在网上对“mysql为什么用了子查询后，主查询没走主键索引
”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：

问题：mysql为什么用了子查询后，主查询没走主键索引
描述:

---

解决方案1:

你不用子查询的时候，也没有走主键索引啊。。。呵呵

解决方案2:

这是一个非常非常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
(`test1`.`yanse`.`name`,(select 1 from `test1`.`yanse` whe
re ((`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

以上介绍了“mysql为什么用了子查询后，主查询没走主键索引”的问题解答，希望对有需要的网友有所帮助。
本文网址链接：http://www.codes51.com/itwd/1073504.html