sql优化-in与exists
在mysql数据库中,in是最常用的,但有些情况会用到exists,谨以此日记记录现在的理解。
理论知识:
1. exists()中返回的只有true和false,不返回结果集,无论select输出什么值;
2. in的结果集长度是受限制的,大数据量的时候无法使用,且会降低效率。
结论:
1. 当你只需要判断后面的查询结果是否存在时使用exists();
2. 当你需要使用里面的结果集的时候必须用in()。
优化原则:
网上教程很多,这里只强调一点,勤用explain,多实践才是王道!
案例:
有学生表student,父亲表father。
CREATE TABLE `student` (
`id` char(36) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`fatherId` char(36) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `father` (
`id` char(36) NOT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `father` VALUES ('17f4befc-0889-11e7-b17a-0cc47a71a8bd', '张三他爹');
INSERT INTO `father` VALUES ('18035c7d-0889-11e7-b17a-0cc47a71a8bd', '李四他爹');
INSERT INTO `father` VALUES ('180d00a6-0889-11e7-b17a-0cc47a71a8bd', '王五他爹');
INSERT INTO `father` VALUES ('fb043f00-0888-11e7-b17a-0cc47a71a8bd', '陈二狗他爹');
INSERT INTO `student` VALUES ('0801f8bb-0889-11e7-b17a-0cc47a71a8bd', '陈二狗', 'fb043f00-0888-11e7-b17a-0cc47a71a8bd');
INSERT INTO `student` VALUES ('42512449-0889-11e7-b17a-0cc47a71a8bd', '张三', '17f4befc-0889-11e7-b17a-0cc47a71a8bd');
INSERT INTO `student` VALUES ('4262a35a-0889-11e7-b17a-0cc47a71a8bd', '李四', '18035c7d-0889-11e7-b17a-0cc47a71a8bd');
INSERT INTO `student` VALUES ('4269cfb6-0889-11e7-b17a-0cc47a71a8bd', '王五', '180d00a6-0889-11e7-b17a-0cc47a71a8bd');
若要查询张三他爹的姓名,已知张三的姓名,sql两种方式:
select name from father where exists (select id from student where student.fatherId=father.id and student.name='张三');
select name from father where id in (select fatherId from student where name='张三');
注意点:exists返回的是一个true或false,若exists()括号内的表与外表无关联,则要么返回外表的全部结果集,要么返回空,在sql优化时要注意这点,不然无法返回正确的结果集。
select name from father where exists (select id from student where student.name='张三');
以上这句sql,只会返回所有父亲的姓名,切记!