我的实验数据包括两张表:t_author表 和 t_poetry表。
对应表的数据量:
t_author表,13355条记录;
t_poetry表,289917条记录。
对应的表结构如下:
CREATE TABLE t_poetry (
id bigint(20) NOT NULL AUTO_INCREMENT,
poetry_id bigint(20) NOT NULL COMMENT '诗词id',
poetry_name varchar(200) NOT NULL COMMENT '诗词名称',
author_id bigint(20) NOT NULL COMMENT '作者id' PRIMARY KEY (id),
UNIQUE KEY pid_idx (poetry_id) USING BTREE,
KEY aid_idx (author_id) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4
CREATE TABLE t_author (
id int(15) NOT NULL AUTO_INCREMENT,
author_id bigint(20) NOT NULL, author_name varchar(32) NOT NULL,
dynasty varchar(16) NOT NULL,
poetry_num int(8) NOT NULL DEFAULT '0'
PRIMARY KEY (id),
UNIQUE KEY authorid_idx (author_id) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4
执行计划分析 IN 执行过程
sql示例:select from tabA where tabA.x in (select x from tabB where y>0 );
其执行计划:
(1)执行tabB表的子查询,得到结果集B,可以使用到tabB表的索引y;
(2)执行tabA表的查询,查询条件是tabA.x在结果集B里面,可以使用到tabA表的索引x。
Exists执行过程
sql示例:select from tabA where exists (select * from tabB where y>0);
其执行计划:
(1)先将tabA表所有记录取到。
(2)逐行针对tabA表的记录,去关联tabB表,判断tabB表的子查询是否有返回数据,5.5之后的版本使用Block Nested Loop(Block 嵌套循环)。
(3)如果子查询有返回数据,则将tabA当前记录返回到结果集。
tabA相当于取全表数据遍历,tabB可以使用到索引。
实验过程
实验针对相同结果集的IN和Exists 的SQL语句进行分析。
包含IN的SQL语句:
select from t_author ta where author_id in
(select author_id from t_poetry tp where tp.poetry_id>3650 );
包含Exists的SQL语句:
select from t_author ta where exists
(select * from t_poetry tp where tp.poetry_id>3650 and tp.author_id=ta.author_id);
第一次实验
数据情况
t_author表,13355条记录;t_poetry表,子查询筛选结果集 where poetry_id>293650 ,121条记录; 执行结果
使用exists耗时0.94S, 使用in耗时0.03S,IN 效率高于Exists。 原因分析
对t_poetry表的子查询结果集很小,且两者在t_poetry表都能使用索引,对t_poetry子查询的消耗基本一致。两者区别在于,使用 in 时,t_author表能使用索引:
仅对不同数据集情况下的上述exists语句分析时发现,数据集越大,消耗的时间反而变小,觉得很奇怪。
具体查询条件为:
where tp.poetry_id>3650,耗时0.13S
where tp.poetry_id>293650,耗时0.46S
可能原因:条件值大,查询越靠后,需要遍历的记录越多,造成最终消耗越多的时间。这个解释有待进一步验证后再补充。