mysql数据库中IN和EXISTS的误区

       前言&＃xff1a;最近在看 《高性能mysql第三版》 这本书&＃xff0c;读到子查询优化那章&＃xff0c;书中说mysql会将in子查询改写成exists查询&＃xff08;书中基于的mysql版本是5.1.50和5.5&＃xff09;&＃xff0c;于是乎我又上网找了下资料&＃xff0c;发现网上说法几乎都是&＃xff1a;

       in 子查询是把外表和内表hash关联&＃xff0c;先查询内表&＃xff0c;再把内表结果与外表匹配&＃xff0c;对外表使用索引&＃xff08;外表效率高&＃xff0c;可用大表&＃xff09;&＃xff0c;而内表多大都需要查询&＃xff0c;不可避免&＃xff0c;故外表大的使用in&＃xff0c;可加快效率。

       exists 是对外表做loop循环&＃xff0c;每次loop循环再对内表&＃xff08;子查询&＃xff09;进行查询&＃xff0c;那么因为对内表的查询使用的索引&＃xff08;内表效率高&＃xff0c;故可用大表&＃xff09;&＃xff0c;而外表有多大都需要遍历&＃xff0c;不可避免&＃xff08;尽量用小表&＃xff09;&＃xff0c;故内表大的使用exists&＃xff0c;可加快效率&＃xff1b;

       发现竟然和书上说的不一样&＃xff0c;因为按照书上说所&＃xff0c;in 和 exists 应该是一样的&＃xff08;会重写 in 查询为 exists ) &＃xff0c;于是想在本地mysql测试一下&＃xff0c;本地装的是5.7版本&＃xff0c;数据库中有两个表 userinfo 和  syslog 表&＃xff1a;

       这里使用两个表的主键id进行关联&＃xff0c;sql 和分析计划表如下&＃xff1a;

explain select * from syslog where id in(select id from userinfo);show WARNINGS;

       可以看到优先执行userinfo表&＃xff0c;再去和syslog进行比对&＃xff0c;看起来似乎和网上说的一样&＃xff0c;接着我们将顺序颠倒&＃xff1a;

explain select * from userinfo where id in(select id from syslog);show WARNINGS;

       结果竟然和上面执行计划运行顺序是一致的&＃xff1f;&＃xff1f;&＃xff1f;我们打开结果二那一栏查看mysql优化器将sql重写后的结果&＃xff0c;分别如下&＃xff1a;

原SQL&＃xff1a;
select * from syslog where id in(select id from userinfo);
优化后的SQL&＃xff1a;
/* select#1 */
SELECT&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;id&＃96; AS &＃96;id&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;user_name&＃96; AS &＃96;user_name&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;type&＃96; AS &＃96;type&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;operation&＃96; AS &＃96;operation&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;method&＃96; AS &＃96;method&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;params&＃96; AS &＃96;params&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;ip&＃96; AS &＃96;ip&＃96;,&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;operation_time&＃96; AS &＃96;operation_time&＃96;
FROM&＃96;test&＃96;.&＃96;userinfo&＃96;
JOIN &＃96;test&＃96;.&＃96;syslog&＃96;
WHERE(&＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;id&＃96; &＃61; &＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;id&＃96;)

原SQL&＃xff1a;
select * from userinfo where id in(select id from syslog);
优化后的SQL&＃xff1a;
/* select#1 */
SELECT&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;id&＃96; AS &＃96;id&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;user_id&＃96; AS &＃96;user_id&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;user_name&＃96; AS &＃96;user_name&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;age&＃96; AS &＃96;age&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;gender&＃96; AS &＃96;gender&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;address&＃96; AS &＃96;address&＃96;,&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;user_pass&＃96; AS &＃96;user_pass&＃96;
FROM&＃96;test&＃96;.&＃96;syslog&＃96;
JOIN &＃96;test&＃96;.&＃96;userinfo&＃96;
WHERE(&＃96;test&＃96;.&＃96;userinfo&＃96;.&＃96;id&＃96; &＃61; &＃96;test&＃96;.&＃96;syslog&＃96;.&＃96;id&＃96;)

       可以看到 mysql将两个in子查询全都改写成了内连接查询 &＃xff0c;这也就可以解释两个分析计划表为什么总是先查找userinfo表了&＃xff0c;因为在内连接的情况下mysql优化器始终会先访问数据量小的那张表&＃xff0c;这样可以减少不必要的IO。

第一个sql改写后首先运行的是 select id from userinfo 使用了主键覆盖索引&＃xff08;Extra 为 using index&＃xff09;&＃xff0c;将查询的结果和 syslog 进行匹配&＃xff0c;所以syslog表使用了主键索引&＃43;where条件过滤&＃xff08;Extra 为 using Where&＃xff09;。

第二个sql改写后首先运行的的是 select * from userinfo &＃xff0c;所以计划中显示的是全表扫描&＃xff0c;然后将查询后的结果和 syslog 进行匹配&＃xff0c;这里因为查找的是select id from syslog where id &＃61; ? &＃xff0c;所以计划中显示是覆盖索引&＃43;where条件过滤&＃xff08;Extra 为 using where &＃43; using index)。

       我们再来看一下 exists 的分析情况&＃xff1a;

explain select * from syslog where exists(select * from userinfo where userinfo.id &＃61; syslog.id);

explain select * from userinfo where exists(select * from syslog where syslog.id &＃61; userinfo.id);

可以看到exists查询是一个相关子查询&＃xff0c;内部的查询需要依赖外部的查询结果&＃xff0c;所以两个sql的分析计划都是先将外部的表进行全表扫描再和子查询表进行比对&＃xff0c;如果外部的表数据量小的话性能可能不会太差&＃xff0c;数据量大的情况下性能会非常糟糕。

结论&＃xff1a;1、mysql5.5以前会将 in 子查询改写成 exists 查询&＃xff0c;如果外部表数据量大的情况下性能会非常糟糕。

           2、mysql5.7&＃xff08;5.6没有测过&＃xff0c;感兴趣的同学可以测测&＃xff09;对 in 子查询进行了优化&＃xff0c;会将sql改写成 join 连接&＃xff0c;这样优化器就可以始终优先访问数据量小的表格&＃xff0c;减少IO&＃xff0c;性能和直接写连接查询几乎是一样的&＃xff08;这点和网上书上说的是有出入的&＃xff09;。

           3、exists查询会被分解成一个外部查询和相关子查询&＃xff08;DEPENDENT SUBQUERY&＃xff09;&＃xff0c;这样子查询会依赖于外部查询的结果&＃xff0c;所以始终会对外部表进行全表扫描&＃xff0c;外部表数据量大的时候要尤其注意。