Preface最近有个业务需求，给我一条数据A，它存在于数据库B，多的部分比A数出来。由于数据比较杂乱，我这里简化了模型。然后你会发现，我去，这不就是不在，不存在。那么问题来了，in、notin、exists、notexists有什么区别，效率如何？在网上听说in和exists是不会去索引的，请问真的是这样吗？带着疑问，我们研究下去。注意：说这个问题，不说明MySQL版本的都是耍流氓。我在这里使用5.7.18。使用说明为了方便起见，我们创建两个表t1和t2。并分别添加一些数据。(id为主键，name为普通索引)--t1DROPTABLEIFEXISTS`t1`;CREATETABLE`t1`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`address`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_t1_name`(`name`(191))USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1009DEFAULTCHARSET=utf8mb4;INSERTINTO`t1`VALUES('1001','张三','北京'),('1002','李四','天津'),('1003','王舞','北京'),('1004','赵刘','河北'),('1005','杰克','河南'),('1006','汤姆','河南'),('1007','贝尔','上海'),('1008','孙琦','北京');-t2droptableifexists`t2`;createtable`t2`（`iD`Int（11）notnullauto_increment，`name`varchar（255）targinututf8mb4collat??ef8mb4collat??eutf8mb4_general_genullld_genulldfaultnull_genulldnull_genullldnull_null_equbly_exprientneequardserallatebrate'gratetrigenteltratetrigentratetrigntratetranctry（tranctrate）`(`name`(191))USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1014CHARACTERSET=utf8mb4COLLATE=utf8mb4_general_ciROW_FORMAT=动态；INSERTINTO`t2`VALUES(1001,'张三','北京');INSERTINTO`t2`VALUES(1004,'赵六','河北');INSERTINTO`t2`VALUES(1005,'杰克','河南'）;插入`t2`VALUES(1007,'贝尔','上海');INSERTINTO`t2`VALUES(1008,'孙琦','北京');INSERTINTO`t2`VALUES(1009,'曹操','魏国');INSERTINTO`t2`VALUES(1010,'刘备','蜀国');INSERTINTO`t2`VALUES(1011,'孙权','吴国');INSERTINTO`t2`VALUES(1012,'诸葛亮'，'蜀');INSERTINTO`t2`VALUES(1013,'典韦','韦');那么，对于现在的问题，很简单，使用notin或者notexists就可以比较t1表中t2表中多余的数据，挑出来（当然t2中多余的不算）。这里假设使用name来匹配数据。select*fromt1wherenamenotin(selectnamefromt2);或select*fromt1wherenotexists(selectnamefromt2wheret1.name=t2.name);结果是一样的。但是，需要注意的是，notin和notexists还是有区别的。使用notin时，需要保证子查询的匹配字段不为空。例如，这个表中的名称t2需要有非空限制。否则，notin返回的整个结果集将为空。比如我在t2表中添加了一条name为空的数据。INSERTINTO`t2`VALUES(1014,NULL,'魏国');那么这个时候notinresult会返回空。另一件需要理解的事情是exists返回的结果是一个布尔值true或false，而不是结果集。因为它并不关心返回的具体数据是什么，而是外层查询需要用这个布尔值来判断。不同的是，使用exists时，如果子查询找到数据，则返回true。使用notexists时，如果子查询没有找到数据，则返回true。因为exists子查询并不关心具体返回的数据是什么。因此，上述语句可以修改如下，子查询中的--name可以修改为任意其他字段，比如这里的1。选择*fromt1wherenotexists(select1fromt2wheret1.name=t2.name);在执行效率方面，1>column>*。因此，推荐选择1。（准确的说应该是一个常量值）in,exists执行过程1.对于in查询，会先执行子查询，比如上面的t2表，然后是查询得到的结果和外表t1会做笛卡尔积，然后通过条件进行过滤（这里的条件是指名字是否相等），每条满足条件的数据都加入到结果集中。sql如下，select*fromt1wherenamein(selectnamefromt2);伪代码如下：for(xinA){for(yinB){if(conditionistrue){result.add();}}}这里的条件其实就是比较两个表中的名字是否相同。2.对于exists，先查询遍历外表t1，然后每次遍历检查内表是否满足匹配条件，即检查是否有同名数据。sql如下，select*fromt1wherenameexists(select1fromt2);伪代码如下：for(xinA){if(existsconditionistrue){result.add();}}对应这个例子，就是从id为1001开始遍历t1表，然后在遍历的时候检查是否有是t2中的同名。比如当id=1001时，t2表中存在张三，则返回true，将t1中张三的这条记录添加到结果集中，继续下一次循环。当id=1002时，李四不在t2表中，则返回false，不做任何操作，继续下一个循环。直到遍历整个t1表。你想使用索引吗？关于in和exists不使用索引，是这样吗？我们在MySQL5.7.18中验证一下。（注意版本号）单表查询首先验证最简单的单表情况。我们以t1表为例，id是主键，name是普通索引。分别执行以下语句，explainselect*fromt1whereidin(1001,1002,1003,1004);explainselect*fromt1whereidin(1001,1002,1003,1004,1005);explainselect*fromt1wherenamein('张三','李四');explainselect*fromt1wherenamein('张三','李四','王舞');为什么要分别查看不同id的个数？看截图你会惊奇地发现，当id有四个值的时候，依然使用主键索引。当id为五个值时，不使用索引。这很有趣。再看name的情况，当value太多的时候，索引就用不上了。所以，我猜它与匹配字段的长度有关。按照汉字是三个字节来算，编程喜欢用2的n次方的尿，这里大概是以16个字节为分界点。但是，我在我的服务器（版本号5.7.22）上使用相同的数据进行查询，当我找到四个id值时，我停止了索引。因此，这里估计的阈值为12字节。不管怎样，这表明MySQL中in查询的字节长度应该是有限制的。（没有官方说法，仅供参考）多表涉及到子查询。本例中查询两张表时主要检查in和exists是否有索引。1.分别执行以下语句，主键索引(id)和普通索引(name)，是否使用in和不in下的索引。explainselect*fromt1whereidin(selectidfromt2);--1explainselect*fromt1wherenamein(selectnamefromt2);--2explainselect*fromt1whereidnotin(selectidfromt2);--3explainselect*fromt1wherenamenotin(selectnamefromt2);.12、t1不使用索引，t2不使用索引。（本例中测到如果改名字为唯一索引，t1也会使用索引）23、t1不使用索引，t2使用索引。34、t1不使用索引，t2不使用索引。4我的天，结果看起来乱七八糟，看来能不能上索引，完全看心情。但是我们发现只有第一种情况，即匹配到主键索引字段，使用in时，两个表都被索引了。这是规定吗？有待考察，我们往下看。2、接下来测试exists和notexists下的主键索引和普通索引。sql如下，解释select*fromt1whereexists(select1fromt2wheret1.id=t2.id);explainselect*fromt1whereexists(select1fromt2wheret1.name=t2.name);explainselect*fromt1wherenotexists(select1fromt2wheret1.id=t2.id);t2.名称);这个结果很有规律。看看大家是否发现t1表无论如何都不会使用索引，t2表有索引就会使用索引。为什么会这样？其实上一节提到了exists的执行过程，已经说明了问题。它是由outertable驱动的，无论如何都要遍历，所以会扫描整张表。内表通过索引可以快速判断当前记录是否匹配。效率如何？我们来测试一下网上说比in高的exists的执行效率，在t1和t2分别插入100W和200W条数据。在这里，我使用自定义函数循环插入。语句参考如下，（我没有把表名提取到变量中，因为没找到办法，尴尬）--传入id起始值和插入数据的大小，函数返回的结果是最终插入项的数量，通常应该等于数据的大小。--id自增，循环往t1表中添加数据。这里为了方便，id和name取同一个变量，地址是北京。delimiter//dropfunctionifexistsinsert_datas1//createfunctioninsert_datas1(in_startint(11),in_lenint(11))returnsint(11)begindeclarecur_lenint(11)default0;declarecur_idint(11);setcur_id=in_start;whilecur_len
