原文章地址：https://www.ujcms.com/knowledge/714.html
SQL界一直以来都流传这样一种说法，不要用in，要用exists代替in，in的性能很低。甚至在程序中使用了in还会被同行嫌弃，认为在任何时候exists的性能都比in高。
小数据量这几个用起来肯定没有太多区别，而要造几百万的数据进行测试，毕竟稍显麻烦。既然所有的大神都这么说，那么就这么信吧。
现在主流的观点认为，外表比内表（子查询表）结果集大，用in效率比exists高；内表（子查询表）比外表结果集大，用exists效率比in高。但实际如何，还是看最后的测试结果吧。
一、静态数据
in和洪水猛兽一般，以至于静态数据都不太敢用in，要拆成or的写法。比如 where id_ in (1,2,3) 改写成 where id_ = 1 or id_ = 2 or id_ = 3。这就很奇怪了，in和or的写法虽然不同，但逻辑是一样的，怎么in就更慢呢。
后来有些贤者已经证明了，in 改成 or效果是一样的。只不过有些数据库的in有数量限制，比如oracle的in的数量最多为1000个。
二、准备测试数据
in在可读性、方便性上有着极大的优势，不管换成exists还是join的方式，都比不上in。心里一直想用in的渴望终于驱使自己亲自测试，性能到底有多大差距。
这里使用 MySQL-5.7.32 进行测试。在数据库中产生大量数据，必须用到存储过程，并且还需要一些批量处理数据的技巧。否则几百万的数据会让你处于无尽的等待中。
以下表结构来源于真实的开源java cms项目：UJCMS。
每篇文章都属于某个栏目。即可通过角色和栏目的关联，控制文章的权限；也可通过组织和栏目的关联，控制文章权限。角色、栏目和组织、栏目都是多对多关联关系。
三、文章表
CREATETABLE`ujcms_article`(`id_`int(11)NOTNULL,`site_id_`int(11)NULLDEFAULTNULL,`channel_id_`int(11)NULLDEFAULTNULL,`org_id_`int(11)NULLDEFAULTNULL,`name_`varchar(255)NULLDEFAULTNULL,`status_`smallint(6)NULLDEFAULT0,PRIMARYKEY(`id_`)USINGBTREE,INDEX`idx_article_channel`(`channel_id_`)USINGBTREE,INDEX`idx_article_org`(`org_id_`)USINGBTREE,INDEX`idx_article_site`(`site_id_`)USINGBTREE)ENGINE=InnoDB;
3.1 200万条文章数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_article`()BEGINDECLARE i INTDEFAULT0;DECLARE j INTDEFAULT1;-- 1000条批量插入性能最好，非常重要。DECLARE batch INTDEFAULT1000;SET@sql_insert="INSERT INTO ujcms_article (id_, site_id_, channel_id_, org_id_, name_, status_) VALUES";SET@sql_values="";SET autocommit =0;WHILE i <2000DOWHILE j <= batch DOSET@id_val= i*batch+j;SET@channel_id= FLOOR((@id_val-1)/200)+1;SET@org_id= FLOOR((@id_val-1)/2224)+1;SET@sql_values= CONCAT(@sql_values,"(",@id_val,",",((@id_val-1)%100)+1,",",@channel_id,",",@org_id,",'文章",@id_val,"',",((@id_val-1)%5)+1,")");IF j < batch THENSET@sql_values= CONCAT(@sql_values,",");ENDIF;SET j = j +1;ENDWHILE;SET@sql_whole= CONCAT(@sql_insert,@sql_values);PREPARE stmt FROM@sql_whole;EXECUTE stmt;DEALLOCATEPREPARE stmt;-- 100个事务提交性能最好，不过效果非常细微，使用自动提交事务也基本没区别，100万数据差2-5秒，聊胜于无。IF i >0AND(i+1)%100=0THENCOMMIT;ENDIF;SET@sql_values="";SET j =1;SET i = i +1;ENDWHILE;SET autocommit =1;END
3.2 栏目表
CREATETABLE`ujcms_channel`(`id_`int(11)NOTNULL,`name_`varchar(255)NULLDEFAULTNULL,PRIMARYKEY(`id_`)USINGBTREE)ENGINE=InnoDB;
3.2.1 1万条栏目数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_channel`()BEGINDECLARE i INTDEFAULT1;DECLARE max INTDEFAULT10000;SET@sql_insert="INSERT INTO ujcms_channel VALUES ";SET@sql_values="";WHILE i <= max DOSET@sql_values= CONCAT(@sql_values,"(", i,",","'栏目", i,"')");IF i < max THENSET@sql_values= CONCAT(@sql_values,",");ENDIF;SET i = i +1;ENDWHILE;SET@sql_whole= CONCAT(@sql_insert,@sql_values);PREPARE stmt FROM@sql_whole;EXECUTE stmt;DEALLOCATEPREPARE stmt;SET@sql_values="";END
3.3 角色表
CREATETABLE`ujcms_role`(`id_`int(11)NOTNULL,`name_`varchar(255)NULLDEFAULTNULL,PRIMARYKEY(`id_`)USINGBTREE)ENGINE=InnoDB;
3.3.1 9条角色数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_role`()BEGINDECLARE i INTDEFAULT1;WHILE i <=9DOINSERTINTO ujcms_role VALUES(i, CONCAT('角色', i));SET i = i +1;ENDWHILE;END
3.4 角色栏目关联表
CREATETABLE`ujcms_role_channel`(`role_id_`int(11)NOTNULL,`channel_id_`int(11)NOTNULL,PRIMARYKEY(`role_id_`,`channel_id_`)USINGBTREE,INDEX`idx_rolechannel_channel`(`channel_id_`)USINGBTREE)ENGINE=InnoDB;
3.4.1 1.8万条角色栏目关联数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_role_channel`()BEGINDECLARE i INTDEFAULT1;DECLARE j INTDEFAULT1;DECLARE role_max INTDEFAULT9;SET@sql_insert="INSERT INTO ujcms_role_channel VALUES ";SET@sql_values="";WHILE i <= role_max DOSET j =(i-1)*1000+1;WHILE j <= i*1000+1000DOSET@sql_values= CONCAT(@sql_values,"(", i,",", j,")");IF j < i*1000+1000THENSET@sql_values= CONCAT(@sql_values,",");ENDIF;SET j = j +1;ENDWHILE;SET@sql_whole= CONCAT(@sql_insert,@sql_values);PREPARE stmt FROM@sql_whole;EXECUTE stmt;DEALLOCATEPREPARE stmt;SET@sql_values="";SET i = i +1;ENDWHILE;END
3.5 组织表
CREATETABLE`ujcms_org`(`id_`int(11)NOTNULL,`name_`varchar(255)NULLDEFAULTNULL,PRIMARYKEY(`id_`)USINGBTREE)ENGINE=InnoDB;
3.5.1 900条组织数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_org`()BEGINDECLARE i INTDEFAULT1;DECLARE max INTDEFAULT900;SET@sql_insert="INSERT INTO ujcms_org VALUES ";SET@sql_values="";WHILE i <= max DOSET@sql_values= CONCAT(@sql_values,"(", i,",","'组织", i,"')");IF i < max THENSET@sql_values= CONCAT(@sql_values,",");ENDIF;SET i = i +1;ENDWHILE;SET@sql_whole= CONCAT(@sql_insert,@sql_values);PREPARE stmt FROM@sql_whole;EXECUTE stmt;DEALLOCATEPREPARE stmt;SET@sql_values="";END
3.6 组织栏目关联表
CREATETABLE`ujcms_org_channel`(`org_id_`int(11)NOTNULL,`channel_id_`int(11)NOTNULL,PRIMARYKEY(`org_id_`,`channel_id_`)USINGBTREE,INDEX`idx_orgchannel_channel`(`channel_id_`)USINGBTREE)ENGINE=InnoDB;
3.6.1 90.9万条组织栏目关联数据
CREATEDEFINER=`ujcms`@`%`PROCEDURE`insert_org_channel`()BEGINDECLARE i INTDEFAULT1;DECLARE j INTDEFAULT1;DECLARE left_max INTDEFAULT900;SET@sql_insert="INSERT INTO ujcms_org_channel VALUES ";SET@sql_values="";WHILE i <= left_max DOSET j =(i-1)*10+1;WHILE j <= i*10+1000DOSET@sql_values= CONCAT(@sql_values,"(", i,",", j,")");IF j < i*10+1000THENSET@sql_values= CONCAT(@sql_values,",");ENDIF;SET j = j +1;ENDWHILE;SET@sql_whole= CONCAT(@sql_insert,@sql_values);PREPARE stmt FROM@sql_whole;EXECUTE stmt;DEALLOCATEPREPARE stmt;SET@sql_values="";SET i = i +1;ENDWHILE;END
四、join查询
oin可谓是最平常的解决方案了，虽然这里存在多对多关系，join后会导致数据重复，但用distinct去重就好了。
以下SQL实现，按用户所拥有的角色，查询有权限的文章列表。
SELECTcount(distinct t.id_)FROM
ujcms_article t
JOIN ujcms_role_channel rc ON t.channel_id_ = rc.channel_id_
WHERE
rc.role_id_ in(4,6,5,1,9)
执行时间0.721秒。好像还不错，可以接受。
如果功能再复杂一些，支持通过角色和组织两个地方给权限，SQL如下：
SELECTcount(distinct t.id_)FROM
ujcms_article t
JOIN ujcms_role_channel rc ON t.channel_id_ = rc.channel_id_
JOIN ujcms_org_channel oc ON t.channel_id_ = oc.channel_id_
WHERE
rc.role_id_ in(4,6,5,1,9)or oc.org_id_ =233
执行时间57.626秒。是的，你没有看错，将近一分钟。这就是我们不敢把功能做的太复杂的原因。
五、exists查询
既然join性能如此不堪，试试传说中的exists神器，会不会有奇效呢？
SELECTcount(t.id_)FROM
ujcms_article t
WHEREEXISTS(SELECT*FROM ujcms_role_channel rc
WHERE t.channel_id_ = rc.channel_id_
AND rc.role_id_ in(4,6,5,1,9))
执行时间5.934秒。一个关联表就没法用了，这是神器还是神坑？
SELECTcount(t.id_)FROM
ujcms_article t
WHEREEXISTS(SELECT*FROM ujcms_role_channel rc
WHERE t.channel_id_ = rc.channel_id_
AND rc.role_id_ in(4,6,5,1,9))orEXISTS(SELECT*FROM ujcms_org_channel oc
WHERE t.channel_id_ = oc.channel_id_ and oc.org_id_ =233)
执行时间7.296秒。果然效果比join的57.626秒好太多了，只可惜7秒的时间还是太慢了。
六、in查询
exists神器都如此不堪，那就来好好看看臭名昭著的in是怎么出洋相的吧。
SELECTcount(t.id_)FROM
ujcms_article t
WHERE
t.channel_id_ IN(SELECT rc.channel_id_
FROM ujcms_role_channel rc
WHERE rc.role_id_ in(4,6,5,1,9))
执行时间0.275秒。这可比join的0.721秒都快的多呀。
但也就比join快了几百毫秒，无关痒痛，还有两个关联表的呢，看in是怎么死的。
SELECTcount(t.id_)FROM
ujcms_article t
WHERE
t.channel_id_ IN(SELECT rc.channel_id_
FROM ujcms_role_channel rc
WHERE rc.role_id_ in(4,6,5,1,9))or
t.channel_id_ IN(SELECT oc.channel_id_
FROM ujcms_org_channel oc
WHERE oc.org_id_ =233)
执行时间0.422秒。What?是数据大意了没有闪吗？你让join的57.626秒和exists的7.296秒情何以堪？
七、exists的荣光
前面说过，exists在外表数据集小，内表（子查询）数据集大的情况下，性能比in好。那么我们就来复现一下这个场景吧。
ujcms_channel 表只有1万条数据，作为外表；ujcms_article表有200万数据，作为内表。
7.1 in查询:
selectcount(t.id_)from ujcms_channel t where t.id_ in(select a.channel_id_ from ujcms_article a where a.site_id_ in(1,3,4,5))and t.name_ like'栏目1%'
执行时间1.325秒。
7.2 exists查询
selectcount(t.id_)from ujcms_channel t whereexists(select*from ujcms_article a where t.id_ = a.channel_id_ and a.site_id_ in(1,3,4,5))and t.name_ like'栏目1%'
执行时间0.540秒。
7.3 join查询
selectcount(distinct t.id_)from ujcms_channel t
join ujcms_article a on t.id_ = a.channel_id_
where a.site_id_ in(1,3,4,5)and t.name_ like'栏目1%'
执行时间0.181秒。
可以看到in确实比exists更耗时，但join性能更好。
八、结论
在多对多关联时，由于会导致数据大量膨胀，用join要慎重，特别是多个多对多关联，要慎重。
exists目前没有看到太多高光的表现。原因是外表中的每一条数据都要执行一次嵌套的子查询，当外表数据量大的时候，exists的性能不可能好到哪里去。
in的子查询只需要执行一次，主要是怕这个子查询的结果集非常大，占用内存。但一般in里的子查询结果集大到离谱的情形非常少，所以in绝不是性能低下洪水猛兽，多对多关联时，大胆的，甚至尽可能的用in。
只有当外表数据量很小，而内表的数据量有十分巨大时，exists才可能有一些优势。不过通过实际测试也没发现有多大的优势。由于外表数据量非常小，而内表数据量却十分巨大的情形非常少，没有做特别严谨的测试。因此exists的使用场景非常少，实在要用，要先做好测试。
版权归原作者 钱多多_qdd 所有， 如有侵权，请联系我们删除。