MySQL中使用EXISTS替代IN是一种优化查询的方法

MySQL中使用EXISTS替代IN是一种优化查询的方法

#不能使用in关键字，索引无效 type=ALL

MySQL中使用EXISTS替代IN是一种优化查询的方法，它可以避免在子查询中进行全表查询，从而提高查询效率。
假设有两张表，一张是employees（员工表），一张是departments（部门表）。如果我们想找出所有在departments表中存在的employees，可以使用EXISTS来替代通常的IN子查询。

使用IN的查询可能如下所示：
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

使用EXISTS的查询可以写成：
SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id);

在这个查询中，EXISTS是一个逻辑操作符，它会检查内部查询是否至少返回一行。如果内部查询返回了结果，那么外部查询的WHERE条件就被认为是真的，相应的行就会被返回。这样做比用IN子查询在性能上通常会更好，因为数据库可以利用索引来优化EXISTS子查询，而IN子查询则不行。

link:
mysql分组求最大ID记录行方法
https://www.cnblogs.com/oktokeep/p/14761479.html

CREATE TABLE `test_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`order_no` BIGINT(20) DEFAULT NULL,
`amt` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4


INSERT INTO `test_user`(`id`,`order_no`,`amt`)
VALUES
(1,111,100),
(2,222,200),
(3,333,300),
(4,111,101),
(5,111,102),
(6,222,201),
(7,222,202),
(8,333,301),
(9,333,302);

SELECT * FROM test_user WHERE order_no = '111';

##取最大的记录
SELECT * FROM `test_user` t
WHERE NOT EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id > t.`id`);

##以订单号：111为例，拆解：
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 1
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 4
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 5   //只有这一条符合要求


## NOT EXISTS  可以理解为 没有查询返回记录，看成：0
SELECT * FROM `test_user` t
WHERE 1 > (SELECT COUNT(1) FROM test_user WHERE order_no = t.order_no AND id > t.`id`);

查询返回结果：
5 111 102
7 222 202
9 333 302

##取反的话，不符合查询结果。
SELECT * FROM `test_user` t
WHERE EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id > t.`id`);

##返回2个结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 1
##返回1个结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 4
##没有结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id > 5


##取最小的，不符合查询结果。
SELECT * FROM `test_user` t
WHERE EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id < t.`id`);

##取最小的记录
SELECT * FROM `test_user` t
WHERE NOT EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id < t.`id`);


##以订单号：111为例，拆解：
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 1    //只有这一条符合要求
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 4
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 5   

SELECT * FROM `test_user` t
WHERE 1 > (SELECT COUNT(1) FROM test_user WHERE order_no = t.order_no AND id < t.`id`);

查询返回结果：
1 111 100
2 222 200
3 333 300

##没有结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 1
##返回1个结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 4
##返回2个结果
SELECT 1 FROM test_user WHERE order_no = '111' AND id < 5

扩展：查询按订单号分组的最大ID行的记录

##简单方法：倒序然后再分组，可以查询出来。  不符合要求！
SELECT t.* FROM (
SELECT * FROM `test_user` ORDER BY id DESC
) t GROUP BY order_no


#1  id等于号 = max(id)
SELECT * FROM `test_user` t
WHERE id = (SELECT MAX(id) FROM test_user WHERE order_no = t.order_no);

#2 两个表的内连接 ，订单号关联，id = max(id)
SELECT t.* FROM `test_user` t,
(SELECT order_no,MAX(id) maxId FROM `test_user` GROUP BY order_no) t2
WHERE t.order_no = t2.order_no AND t.id=t2.maxId;

#3 not exists 同该篇
SELECT * FROM `test_user` t
WHERE NOT EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id > t.`id`);

#4 同2，内连接的另外一种写法
SELECT t.* FROM `test_user` t
INNER JOIN (SELECT order_no,MAX(id) maxId FROM `test_user` GROUP BY order_no) t2
ON t.order_no = t2.order_no AND t.id=t2.maxId;

#5 同3 where 1 > 0  ()括号里面查询没有结果，则count(1) = 0
SELECT * FROM `test_user` t
WHERE 1 > (SELECT COUNT(1) FROM test_user WHERE order_no = t.order_no AND id > t.`id`);