mysql 主键id 时间戳 mysql in 主键

转载

mob64ca1416b5a8 2023-09-16 17:06:40

文章标签 mysql 主键id 时间戳 mysql java 数据子查询 文章分类 MySQL 数据库

结论

小表做外表的情况下
对于主键来说， not exists 比 not in 快。
对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。
大表做外表的情况下
对于主键来说， not in 比 not exists 快。
对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

用法讲解

为了方便，我们创建两张表 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, '典韦', '魏国');

mysql 主键id 时间戳 mysql in 主键_mysql 主键id 时间戳

这里假设用 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);

得到的结果都是一样的。

mysql 主键id 时间戳 mysql in 主键_数据_02

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

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

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

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

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

mysql 主键id 时间戳 mysql in 主键_mysql_03

另外需要明白的是， exists 返回的结果是一个 boolean 值 true 或者 false ，而不是某个结果集。因为它不关心返回的具体数据是什么，只是外层查询需要拿这个布尔值做判断。

区别是，用 exists 时，若子查询查到了数据，则返回真。用 not exists 时，若子查询没有查到数据，则返回真。

由于 exists 子查询不关心具体返回的数据是什么。因此，以上的语句完全可以修改为如下，

-- 子查询中 name 可以修改为其他任意的字段，如此处改为 1 。
select * from t1 where not exists (select 1 from t2 where t1.name=t2.name);

从执行效率来说，1 > column > * 。因此推荐用 select 1。（准确的说应该是常量值）

in, exists 执行流程

1、对于 in 查询来说，会先执行子查询，如上边的 t2 表，然后把查询得到的结果和外表 t1 做笛卡尔积，再通过条件进行筛选（这里的条件就是指 name 是否相等），把每个符合条件的数据都加入到结果集中。

sql 如下，

select * from t1 where name in (select name from t2);

伪代码如下：

for(x in A){
    for(y in B){
     if(condition is true) {result.add();}
    }
}

这里的 condition 其实就是对比两张表中的 name 是否相同。

2、对于 exists 来说，是先查询遍历外表 t1 ，然后每次遍历时，再检查在内表是否符合匹配条件，即检查是否存在 name 相等的数据。

sql 如下，

select * from t1 where name exists (select 1 from t2);

伪代码如下：

for(x in A){
  if(exists condition is true){result.add();}
}

对应于此例，就是从 id 为 1001 开始遍历 t1 表，然后遍历时检查 t2 中是否有相等的 name 。

如 id=1001时，张三存在于 t2 表中，则返回 true，把 t1 中张三的这条记录加入到结果集，继续下次循环。id=1002 时，李四不在 t2 表中，则返回 false，不做任何操作，继续下次循环。直到遍历完整个 t1 表。

是否走索引？

针对网上说的 in 和 exists 不走索引，那么究竟是否如此呢？

我们在 MySQL 5.7.18 中验证一下。（注意版本号哦）

单表查询

首先，验证单表的最简单的情况。我们就以 t1 表为例，id为主键， name 为普通索引。

分别执行以下语句，

explain select * from t1 where id in (1001,1002,1003,1004);
explain select * from t1 where id in (1001,1002,1003,1004,1005);
explain select * from t1 where name in ('张三','李四');
explain select * from t1 where name in ('张三','李四','王五');

mysql 主键id 时间戳 mysql in 主键_数据_04