爱站程序员基地注：当前测试mysql版本：mysql5.7，编码utf8mb4，ENGINE：InnoDB
准备表和数据的脚本：
[code]-- 创建A表DROP TABLE IF EXISTS `A`;CREATE TABLE A (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 创建B表DROP TABLE IF EXISTS `B`;create table B like A;-- 往A表插入1万条记录drop procedure if exists insert_A;delimiter ;;create procedure insert_A()begindeclare i int;set i=1;while(i<=10000)doinsert into A(a,b) values(i,i);set i=i+1;end while;end;;delimiter ;call insert_A();-- 往B表插入100行记录drop procedure if exists insert_B;delimiter ;;create procedure insert_B()begindeclare i int;set i=1;while(i<=100)doinsert into B(a,b) values(i,i);set i=i+1;end while;end;;delimiter ;call insert_B();
in和exsits优化，in和exists如何选择？
一. 主表关联字段和子表关联字段都有索引时
[code]-- sql1：explain select * from A where A.id in (select B.id from B); -- 查询数据用时：0.245s-- sql2：explain select * from A where exists (select 1 from B where B.id=A.id); -- 查询数据用时：0.353s-- sql3：explain select * from B where B.id in (select A.id from A); -- 查询数据用时：0.133s-- sql4：explain select * from B where exists (select 1 from A where A.id=B.id); -- 查询数据用时：0.170s
A有1万条数据，B有100条数据
分析结果：
1.sql1和sql2查询的主表是数据量较大的A表，从explain可以看到，sql1用的in查询，select_type都是SIMPLE简单查询，而且都有走索引。再看sql2用exists查询，走了B表的子查询，而且A表没走索引全表扫描。综合来看sql1性能更高。
2.sql3和sql4查询的主表是数据量100条的小表B，sql3先查询 B 表，两个简单查询；sql4 先子查询A表，然后查询B表。综合性能差不多，sql3稍微领先一点点。
3.对比sql1和sql3，两个都是in查询，mysql会自动选择小表B先查询。
4.对比sql2和sql4，两个都是exists查询，sql总是会先执行子查询。
结论：
1.使用 in 查询时 mysql 会自动选择最佳方案，无论小表是主表还是子表，都是优先查询小表数据，以小表驱动大表。
2.使用 exists 查询时，会优先执行子查询，也就是可以控制子查询先执行。当小表作为子查询时效率不及in查询，当大表作为子查询时效率接近in查询，但是无论怎样效率都比不上in查询。主表子表关联字段都有索引时优先使用 in 查询。
二. 主表关联字段建索引，子表关联字段没建索引时
[code]-- 主表关联字段建索引，子表关联字段没建索引时explain select * from A where A.id in (select B.b from B); -- 0.01sexplain select * from A where exists (select 1 from B where B.b=A.id); -- 0.41sexplain select * from B where B.id in (select A.b from A); -- 0.01sexplain select * from B where exists (select 1 from A where A.b=B.id); -- 0.01s
分析结果：
1.对比sql1和sql2，都是用大表A做主表，小表B做子表。sql1先走B表子查询，把查询结果放到临时表，然后临时表匹配A表索引，一共扫描大概200行。sql2两个都全表扫描，估计扫描9980*100行（可能mysql有优化没有这么多行），效率比sql1低。我的测试结果sql1 0.01秒，sql2 0.41s。
2.对比sql3和sql4，都是小表B做主表，大表A做子表。sql3先全表扫描A表9980行，这个是 MATERIALIZED 物化子查询，返回的结果就是A全表记录，然后全表扫描B表，最后通过B表的索引与子查询临时表匹配。sql4两次全表扫描，但是因为 dependent subquery 子查询受匹配条件影响，经匹配后子查询数据更少，性能比sql3稍微好一点点。
3.对比sql1和sql3，都是in查询，经过mysql优化器后，会优先选择关联字段没有索引的表作为驱动表。
4.对比sql2和sql4，都是exists查询，优先进行子查询。sql2的子表数据较少，查询效率低。sql4子表比主表数据大，效率高。
结论：
1.使用in查询时 mysql 优先查询关联字段未建索引的表，以无索引驱动有索引。
2.使用exists查询，在主表数据比子表数据小时略有效率提升。
三. 主表关联字段没建索引，子表关联字段建索引时
[code]-- 主表关联字段没建索引，子表关联字段建索引时explain select * from A where A.b in (select B.id from B); -- 0.04sexplain select * from A where exists (select 1 from B where B.id=A.b); -- 0.07sexplain select * from B where B.b in (select A.id from A); -- 0sexplain select * from B where exists (select 1 from A where A.id=B.b); -- 0s
1.对比sql1和sql2，sql1的先查询A表，两个简单查询，效率更高。sql2先子查询B表，然后全表扫描A表，效率低些。
2.对比sql3和sql4，explain几乎一样，查询耗时也几乎差不多。但是sql4有子查询
3.对比sql1和sql3，都是in查询，都是优先查询无索引表。
四. 关联字段都不建索引时
[code]-- 主表关联字段没建索引，子表关联字段建索引时explain select * from A where A.b in (select B.b from B); -- 0.06sexplain select * from A where exists (select 1 from B where B.b=A.b); -- 0.36sexplain select * from B where B.b in (select A.b from A); -- 0.01sexplain select * from B where exists (select 1 from A where A.b=B.b); -- 0.00s
1.sql1的子表数据小，小表先查询，小表驱动大表，效率比sql2高。
2.sql4的效率比sql3高，主表数据较小时使用 exists 查询。
3.sql1和sql3都是in查询，都是先查询子表。
总结：
查询规律：
1.使用 exists 查询的时候总是先执行子查询。
2.使用 in 查询的时候，关联字段都建索引的时候优先查询小表（小表驱动大表），只有一个字段建索引的时候优先查询未建索引表（无索引驱动有索引）。关联字段都未建索引时优先子查询。
优化规则：
1.无索引驱动有索引。
2.小表驱动大表。
优化建议：
1.关联字段有索引时（包括两个关联字段的1个或2个索引）优先使用in查询。
mysql会自动优化好 in 查询，关联字段都有索引时让小表先查询（小表驱动大表），只有一个字段有索引时优先让无索引表先查询（无索引驱动有索引）
2.关联字段都没索引时：
1).当主表 > 子表 用 in 。
2).当主表 < 子表 用 exists。
只有最后一种情况使用 exists 查询效率高点，这有点颠覆认知，大多教程都是说 主表< 子表 时使用exists查询效率高，我经过多番测试，在有索引时即使 主表 < 子表 exists查询效率也没有in高，最好的情况是性能接近。我自己造10万、100万数据在 mysql6、mysql7 测试过都是如此，觉得有疑惑的朋友可以自己测试，也希望把测试结果通知我，如果有不对的还需要改正。
