Mysql中in和exists的区别 & not in、not exists、left join的相互转换

文章目录

• 1. in 介绍
• • 1.1 in中数据量的限制
  • 1.2 null值不参与in或not in，也就是说in and not in 并不是全量值，排除了null值
  • 1.3 in的执行逻辑
  • 2. exists介绍
  • • 2.1 exists + not exists 是全量数据
    • 2.2 exists的执行逻辑
    • 3. 小表驱动大表的好处
    • 4. in、not in、exists、not exists是否可以走索引（都可以）
    • 5. not in、 not exists、left join语句相互转换（必须在表关联时，否则并不等同）
    • • 5.1 not in
      • 5.2 not exists
      • 5.3 left join + is null

        数据准备

        -- 建表CREATE TABLE `xin_stu_t_bak` (  `id` bigint NOT NULL COMMENT '主键',  `relation_id` bigint DEFAULT NULL COMMENT '外键, 记录教师id',  `student_name` varchar(30) DEFAULT NULL COMMENT '姓名',  `student_age` bigint DEFAULT NULL COMMENT '年龄',  `school` varchar(300) DEFAULT NULL COMMENT '学校',  KEY `relationid` (`relation_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `xin_teach_t_bak` (  `id` bigint NOT NULL COMMENT '主键',  `teacher_name` varchar(30) DEFAULT NULL COMMENT '教师姓名',  `teacher_age` bigint DEFAULT NULL COMMENT '教师年龄',  `school` varchar(300) DEFAULT NULL COMMENT '学校',  KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- 加索引create index id on xin_stu_t_bak(relation_id);create index id on xin_teach_t_bak(id);-- 添数据INSERT INTO lelele.xin_stu_t_bak (id,relation_id,student_name,student_age,school) VALUES (1,NULL,'尤仁义1',11,'徐州中学'), (2,1,'尤仁义2',12,'徐州中学'), (3,NULL,'朱有理1',11,'徐州中学'), (4,2,'朱有理2',12,'徐州中学'), (5,2,'朱有理3',13,'徐州中学'), (6,3,'宋昆明1',11,'徐州中学'), (7,3,'宋昆明2',12,'徐州中学'), (8,14,'宋昆明3',13,'徐州中学');INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES (1,'王翠花1',31,'徐州中学'), (2,'王翠花2',31,'徐州中学'), (3,'王翠花3',33,'徐州中学'), (4,'王翠花4',34,'徐州中学'), (5,'王翠花5',35,'徐州中学'), (1,'王翠花1',31,'徐州中学'), (1,'王翠花1',31,'徐州中学'), (2,'王翠花2',31,'徐州中学'), (6,'王翠花6',31,'徐州中学'), (7,'王翠花7',31,'徐州中学');INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES (8,'王翠花8',33,'徐州中学'), (9,'王翠花9',34,'徐州中学'), (10,'王翠花10',35,'徐州中学'), (11,'王翠花11',31,'徐州中学'), (12,'王翠花12',31,'徐州中学'), (13,'王翠花13',31,'徐州中学');

        1. in 介绍

        1.1 in中数据量的限制

        在oracle中，int中数据集的大小超过1000会报错；

        在mysql中，超过1000不会报错，但也是有数据量限制的，应该是4mb，但不建议数据集超过1000，

        因为in是可以走索引的，但in中数据量过大索引就会失效

        1.2 null值不参与in或not in，也就是说in and not in 并不是全量值，排除了null值

        select * from xin_stu_t_bak a where a.relation_id in ( select id from xin_teach_t_bak b)

        select * from xin_stu_t_bak a where a.relation_id not in ( select id from xin_teach_t_bak b)

        select * from xin_stu_t_bak a

        从此处可以看出，in和not in 加在一起并不是全量的值，排除了null值

        ---

        1.3 in的执行逻辑

        1. 当前的in子查询是B表驱动A表
        2. mysql先将B表的数据一把查出来至于内存中
        3. 遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源）
        4. 假设B有100000条记录，A有10条记录，会交互100000次数据库；再假设B有10条记录，A有100000记录，只会发生10次交互。

        结论: in是先进行子查询，再与外面的数据进行循环遍历，属于子查询的结果集驱动外面的结果集，

        当in子查询的结果集较小时，就形成了小表驱动大表，而两张表的驱动就是一张表的行数据去循环关联另一张表，

        关联次数越少越好，所以小表去查询大表，次数更少，性能更高

        in()适合B表比A表数据小的情况

        2. exists介绍

        2.1 exists + not exists 是全量数据

        select * from xin_stu_t_bak a where exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

        select * from xin_stu_t_bak a where not exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

        exist + not exists 是全量数据，这点与in不同

        2.2 exists的执行逻辑

        1. 当前exists查询是A表驱动B表
        2. 与in不同，exists将A的纪录数查询到内存，因此A表的记录数决定了数据库的交互次数
        3. 假设A有10000条记录，B有10条记录，数据库交互次数为10000；假设A有10条，B有10000条，数据库交互次数为10。

        结论：exists理论上就是boolean值，关联后查询到有值则是true数据留下，关联后查询没有值则是false数据舍弃；

        exists适合B表数据量大，A表数据量小的情况，与in相反

        3. 小表驱动大表的好处

        我们来看下面两个循环：

        for (int i = 0; i for(int j = 0; j}} for(int j = 0; j}}