不要再问我 in，exists 走不走索引了

微信搜『烟雨星空』，获取最新好文。

前言

最近，有一个业务需求，给我一份数据 A ，把它在数据库 B 中存在，而又比 A 多出的部分算出来。由于数据比较杂乱，我这里简化模型。

然后就会发现，我去，这不就是 not in ，not exists 嘛。

那么问题来了，in, not in , exists , not exists 它们有什么区别，效率如何？

曾经从网上听说，in 和 exists 不会走索引，那么事实真的是这样吗？

带着疑问，我们研究下去。

注意： 在说这个问题时，不说明 MySQL 版本的都是耍流氓，我这里用的是 5.7.18 。

用法讲解

为了方便，我们创建两张表 t1 和 t2 。并分别加入一些数据。（id为主键，name为普通索引）

-- t1
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t1_name` (`name`(191)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1009 DEFAULT CHARSET=utf8mb4;

INSERT INTO `t1` VALUES ('1001', '张三', '北京'), ('1002', '李四', '天津'), ('1003', '王五', '北京'), ('1004', '赵六', '河北'), ('1005', '杰克', '河南'), ('1006', '汤姆', '河南'), ('1007', '贝尔', '上海'), ('1008', '孙琪', '北京');

-- t2
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_t2_name`(`name`(191)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1014 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t2` VALUES (1001, '张三', '北京');
INSERT INTO `t2` VALUES (1004, '赵六', '河北');
INSERT INTO `t2` VALUES (1005, '杰克', '河南');
INSERT INTO `t2` VALUES (1007, '贝尔', '上海');
INSERT INTO `t2` VALUES (1008, '孙琪', '北京');
INSERT INTO `t2` VALUES (1009, '曹操', '魏国');
INSERT INTO `t2` VALUES (1010, '刘备', '蜀国');
INSERT INTO `t2` VALUES (1011, '孙权', '吴国');
INSERT INTO `t2` VALUES (1012, '诸葛亮', '蜀国');
INSERT INTO `t2` VALUES (1013, '典韦', '魏国');

那么，对于当前的问题，就很简单了，用 not in 或者 not exists 都可以把 t1 表中比 t2 表多出的那部分数据给挑出来。（当然，t2 比 t1 多出来的那部分不算）

这里假设用 name 来匹配数据。

select * from t1 where name not in (select name from t2);
或者用
select * from t1 where not exists (select name from t2 where t1.name=t2.name);

得到的结果都是一样的。

但是，需要注意的是，not in 和 not exists 还是有不同点的。

在使用 not in 的时候，需要保证子查询的匹配字段是非空的。如，此表 t2 中的 name 需要有非空限制。如若不然，就会导致 not in 返回的整个结果集为空。

例如，我在 t2 表中加入一条 name 为空的数据。

INSERT INTO `t2` VALUES (1014, NULL, '魏国');

则此时，not in 结果就会返回空。