查询在一张表不在另外一张表的记录及效率探究

发表于 | 分类于 | | 阅读次数

在我做项目的时候遇到一个需求，要将存在于表ta而不存在于表tb中的数据查询出来。

记录使用的方法和探讨效率。

数据准备

创建表ta，并且使用存储过程插入13000条数据，在我的机器上运行时间: 346.719s。如果觉得插入的速度比较慢,可以直接导入我建好的表，百度云地址 http://pan.baidu.com/s/1dFtovg1 ，里面已经有数据了，直接导入sql执行即可，这样比用存储过程要快很多。

DROP TABLE IF EXISTS ta;

CREATE TABLE `ta` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13000 DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS ta_insert;

DELIMITER $$
CREATE PROCEDURE ta_insert()
 MODIFIES SQL DATA
 BEGIN
 SET @i=1;
 SET @max=13000;
 WHILE @i<@max DO
 INSERT INTO `ta` VALUES ();
 SET @i = @i + 1;
END WHILE;
end $$

CALL ta_insert();

创建表tb，并且使用存储过程插入10000条数据，在我的机器上运行时间: 224.102s。

DROP TABLE IF EXISTS tb;

CREATE TABLE `tb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8000 DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS tb_insert;

DELIMITER $$
CREATE PROCEDURE tb_insert()
 MODIFIES SQL DATA
 BEGIN
 SET @i=1;
 SET @max=8000;
 WHILE @i<@max DO
 INSERT INTO `tb` VALUES ();
 SET @i = @i + 1;
END WHILE;
end $$

CALL tb_insert();

子查询

使用NOT IN，ta表中的每一个id值都要去与tb表中的id匹配，匹配到就停止，也就是说，存在于ta表而不存在于tb表的id值需要与所有tb表中的id值进行匹配。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

平均时间为 0.04s。

SELECT ta.id FROM ta WHERE ta.id IN (SELECT id FROM tb)

左连接

使用 LEFT JOIN，ta表左连接tb表，而存在于ta表不存在于tb表中的字段为NULL，于是我们可以通过判断WHERE tb.id IS NULL来找到存在于ta表而不存在于tb表的id值。

平均时间是 0.06s。

SELECT ta.id FROM ta LEFT JOIN tb ON ta.id = tb.id WHERE tb.id IS NULL

效率之谜

版本问题？

按理来说，连接应该比子查询要快，但是在我进行试验的时候发现却不是这样的，子查询居然还比连接要快。

搜索了解到

对于类似NOT IN这样的子查询，也能受益于subquery materialize，将子查询的结果集cache到临时表里，使用hashindex来进行检索；物化的子查询可以看到select_type字段为SUBQUERY，而在MySQL5.5里为DEPENDENT SUBQUERY

可能是版本原因，我用的是mysql5.7，可能做了优化。

于是使用mysql5.5再次测试。

发现子查询和左连接的查询时间都在0.12s附近，还是不能说明连接比子查询高效。进一步猜测，我的数据组织格式是否出现了问题，于是使用上面百度云盘连接中的mm_member表和mm_log表(这是 mysql（4）—— 表连接查询与where后使用子查询的性能分析。 提供的数据) 。

猜测验证

子查询

SELECT mm_member.id FROM mm_member WHERE mm_member.id NOT IN (SELECT DISTINCT mm_log.member_id FROM mm_log)

左连接

SELECT mm_member.id FROM mm_member LEFT JOIN (SELECT DISTINCT mm_log.member_id FROM mm_log ) AS mm
ON mm.member_id = mm_member.id WHERE mm.member_id IS NULL

mysql5.7

子查询为1.1s左右，左连接为1.55s，子查询依然速度较快。

mysql5.5

子查询为48s左右，左连接为1.4s，将近34倍的差距，由此印证上面引用的那部分，mysql5.7确实已经多子查询做了优化，使其达到了逼近左连接的效率。

那为什么我自己所建立的表无法体现版本的这种性能差别？

猜测应该是数据类型的原因，可能int类型的查询效率已经都优化好了。

网传最高效

不太清楚其中的原理，并且在我的测试中性能跟连接差不多。

SELECT id FROM ta WHERE (SELECT COUNT(1) AS num FROM tb WHERE ta.id = tb.id) = 0

而且网上流传的版本((数据库篇) SQL查询~ 存在一个表而不在另一个表中的数据)为

select from B where (select count(1) as num from A where A.ID = B.ID) = 0
应该是
select from A where (select count(1) as num from B where A.ID = B.ID) = 0
大表在前，小表在后。

注意：

  1. 存储过程循环插入比普通方式插入数据慢很多倍。
  2. 索引可以有效提高搜索效率。
  3. 不是所有的子查询都比连接慢的。

参考文档：

  1. MySQL 5.6的优化器改进
  2. mysql（4）—— 表连接查询与where后使用子查询的性能分析。
0%