MySQL in exists

目标：分析MySQL in exists的内部机制

分析：

详解：

第一部分 in exists内部机制

直接先看一个对比

小表，里面有10条数据
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

大表，里面有2598750条数据
CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(20) NOT NULL,
  `title` varchar(20) NOT NULL,
  `goods_num` int NOT NULL,
  `money` decimal(7,4) NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2598751 DEFAULT CHARSET=utf8mb3;

in 外部大表，子查询小表

in 外部大表，子查询小表
explain select count(1) from orders o where o.user_id in (select u.id from users u);
show warnings;

MySQL解析
/* select#1 */ select count(1) AS `count(1)` from `testdb6`.`users` `u` join `testdb6`.`orders` `o` where (`testdb6`.`o`.`user_id` = `testdb6`.`u`.`id`)

exists 外部大表，子查询小表

exists 外部大表，子查询小表
explain select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id);
show warnings;

MySQL解析
/* select#1 */ select count(1) AS `count(1)` from `testdb6`.`users` `u` join `testdb6`.`orders` `o` where (`testdb6`.`o`.`user_id` = `testdb6`.`u`.`id`)

in 外部小表，子查询大表

in 外部小表，子查询大表
explain select count(1) from users u where u.id in (select o.user_id from orders o);
show warnings;

MySQL解析
/* select#1 */ select count(1) AS `count(1)` from `testdb6`.`users` `u` semi join (`testdb6`.`orders` `o`) where (`testdb6`.`o`.`user_id` = `testdb6`.`u`.`id`)

exists 外部小表，子查询大表

exists 外部小表，子查询大表
explain select count(1) from users u where exists (select 1 from orders o where o.user_id = u.id);
show warnings;

MySQL解析
/* select#1 */ select count(1) AS `count(1)` from `testdb6`.`users` `u` semi join (`testdb6`.`orders` `o`) where (`testdb6`.`o`.`user_id` = `testdb6`.`u`.`id`)

关于semi join的说明，请查看这篇文章，www.linuxidc.com/Linux/2015-… 写的非常好。

第二部分 in exists误区

• in 是先执行子查询，适合于外表大而内表小的情况。
• exists 是以外层表为驱动表，先被访问，适合于外表小而内表大的情况。 这两句话熟悉吗，经常在MySQL优化的文章中看到，从上边的对比我们看到，其实in跟exists的执行优化是一样的。我测试使用的是MySQL8.0.25，如果MySQL的版本比较低的话，可以自己测试一下，MySQL随着版本迭代也做了很多优化。所以没有万能的方法，很多都需要自己测试。

第三部分 参考

• 建表，写数据： juejin.cn/post/684490…
• semi join详解：www.linuxidc.com/Linux/2015-…
• MySQL In不能用内连接改写： www.linuxidc.com/Linux/2015-…