数据实测告诉你：不要人云亦云的瞎说EXISTS 与 in 的区别

背景

实践过程中发现了MySQL一些慢查询，主要出现在in关键字上，查阅相关资料，众多博客都在分析in和 EXISTS 的区别与各自的适用场景，很多都是如下一般，直接给出结论，却没有数据支撑的。

谬论一

 他们的结论言之凿凿的说：

in()适合B表比A表数据小的情况

exists()适合B表比A表数据大的情况
 

谬论二

谬论三

---

我在实测过程中发现，在5.7环境下，无论是大表驱动小表，还是小表驱动大表，in的速度都优于exists，这不由得让我产生了怀疑。

环境准备

安装两个版本的数据库各一个，一个5.7版本，是阿里云的云数据库，在线测试库，一个8.0版本，安装在本地

数据准备

两个版本的数据库同一份数据，

小表是用户表，有id和userID为索引

-- ----------------------------
-- Table structure for t_cmp_user
-- ----------------------------
DROP TABLE IF EXISTS `t_cmp_user`;
CREATE TABLE `t_cmp_user`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `userName` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名称',
  `age` int NULL DEFAULT NULL,
  `gender` int NULL DEFAULT NULL COMMENT '性别1男0女',
  `deptID` int NULL DEFAULT NULL,
  `deptName` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `color` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `userID`) USING BTREE,
  INDEX `companyId`(`companyId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 71617 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

大表是操作日志表，otpID和操作人optorID建了索引

-- ----------------------------
-- Table structure for t_cmp_mission_optlog
-- ----------------------------
DROP TABLE IF EXISTS `t_cmp_mission_optlog`;
CREATE TABLE `t_cmp_mission_optlog`  (
  `otpID` bigint NOT NULL AUTO_INCREMENT COMMENT '操作流水id',
  `missionID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '原子任务id',
  `optorID` int NOT NULL COMMENT '操作人id',
  `optorName` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作人名称',
  `optTm` varchar(21) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作时间',
  `optDesc` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '操作描述',
  `category` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '2' COMMENT '0查阅  1评论  2 操作  3 提醒 默认2',
  `companyId` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`otpID`, `optorID`) USING BTREE,
  INDEX `category`(`category`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1627613 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

小表7万+条数据，大表78万+条数据，大表数据量是小表的十倍+

测试过程

小表 in 大表 

小表 in 大表 EXPLAIN 分析结果，5.7与8.0没有区别

EXPLAIN SELECT
	* 
FROM
	`t_cmp_mission_optlog` AS a 
WHERE
	EXISTS (
	SELECT
		userID 
	FROM
		`t_cmp_user` AS b 
	WHERE
	a.optorID = b.userID)

在5.7版本，小表 in 大表执行需要0.959秒，查询结果集1958条

 在8.0版本，同样是查询结果集1958条，执行只需要0.522秒，

大表 in 小表

EXPLAIN SELECT
	* 
FROM
	`t_cmp_mission_optlog` 
WHERE
	optorID IN ( SELECT userID FROM `t_cmp_user` );

大表 in 小表 EXPLAIN 分析结果，5.7与8.0也几乎没有区别

 在5.7版本，大表 in 小表，查询时间15.22秒，结果集78万+条。

  在8.0版本中，大表 in 小表，结果集同样是78万+条。查询时间只需要6.3秒

大表 EXISTS小表

EXPLAIN SELECT
	* 
FROM
	`t_cmp_mission_optlog` AS a 
WHERE
	EXISTS (
	SELECT
		userID 
	FROM
		`t_cmp_user` AS b 
	WHERE
	a.optorID = b.userID)

可以看到8.0使用了索引，而5.7索引失效

而在执行层面，5.7版本的EXISTS效率低到不可忍受，大表EXISTS小表情况下，十几分钟都没出结果

8.0版本7.8秒就查出了78万+条数据，性能与in相差不大

小表 EXISTS大表

EXPLAIN SELECT
	* 
FROM
	`t_cmp_user` AS a 
WHERE
	EXISTS ( SELECT 1 FROM `t_cmp_mission_optlog` AS b WHERE a.userID = b.optorID );

在EXISTS 方面，可以看到8.0使用了索引，而5.7索引失效

在小表EXISTS大表方面，5.7也是拉胯，数据迟迟查不出来

在小表EXISTS大表方面，8.0更是碾压5.7，2000条数据0.5秒就出来了，与in结果相差无几，考虑到误差几乎就是一模一样。

我们可以看到，8.0 EXISTS 与 in 的sql 分析结果是相同的

最后看一下全部sql分析对比

结论

1、网络上那些没有数据支撑的，就乱评论小表驱动大表，大表驱动小表的言论都是错误的

2、在5.7版本中，无论大表在前还是小表在前，in 的查询效率都要远远高于exists 

3、在8.0版本中，得益于版本的改善，in 的查询效率 与 exists 几乎没有明显差别

4、通过对比发现，5.7版本中 exists 不会走索引，而 in  可以走索引。

5、mysql 8 确实比老的5.7效率要高很多，如果有条件，建议升级8.0

如果有小伙伴想重复上述测试，可以找我要数据。