数据实测告诉你:不要人云亦云的瞎说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
如果有小伙伴想重复上述测试,可以找我要数据。
圣心: 这个中文翻译也挺好 https://spring-doc.cadn.net.cn/spring-boot/3.3.2-SNAPSHOT/index.html
qq_47686912: 配置都是对的,搞了半天都不行,重启一下结果就可以了,为什么会有这种事情啊
小姚_: 楼主发一份数据,想测一下,感谢
手可摘月亮: 哈哈 感谢你啊
要身心健康: 但是我有个sql就是用in很慢,用exists快了很多,感觉很玄学