leetcode数据库算法题系列八 ：183. 从不订购的客户（MySQL版）

阅读之前，请牛刀小试：https://leetcode-cn.com/problems/customers-who-never-order/

本系列教程，基于19个案例来探索SQL编程的艺术。 SQL 是一门结构化查询语言，更是一门面向集合的语言。

---

一 、题目：从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

例如给定上述表格，你的查询应返回：

Customers
Henry
Max

应用场景

重表中查找不存在另外一张表的记录是十分多见的，没有迟到的学生。 常常需要统计这一特殊群体。

---

二、准备：创建表插入数据

CREATE TABLE Customers (
	id INT PRIMARY KEY,
	name VARCHAR(200)
);

CREATE TABLE Orders (
	id INT PRIMARY KEY,
	customerid INT
);


INSERT INTO Customers (
	id,name
)VALUES (1,'Joe'),(2,'Henry'),(3,'Sam'),(4,'Max');

INSERT INTO Orders (id,customerid)VALUES (1,3),(2,1);

---

三、解法：条条大路

3.1 解法一：使用子查询和 NOT IN 子句

使用关键字IN； 这种解法非常贴近我们思考的思维，也是否容易理解，但使用NOT IN 会存在陷阱。

SELECT name Customers 
FROM Customers 
WHERE id NOT  IN (SELECT CustomerId FROM Orders);

小实验： 探讨IN 关键字存在坑(就是存在NULL的情况)

如果 NOT IN 子查询中用到的表里被选择的列中存在 NULL ，则 SQL 语句整体的查询结果永远是空。

关于这个问题的需要关注SQL中的三值运算问题。 可以阅读这篇文章: https://blog.csdn.net/qq_31156277/article/details/84333561

---

3.2 解法二 ：使用子查询和 not exists

使用关键字exists 。

SELECT c.Name AS Customers
FROM Customers c
WHERE NOT EXISTS (
    SELECT o.id
    FROM Orders o
    WHERE o.CustomerId = c.id
);

使用关键字exists可以很好的利用子查询中与外部连接的字段上的索引。

---

3.3 解法三： 使用关键字 join

这种做法是十分不错的，值的推荐

SELECT c.Name AS Customers 
FROM Customers c
LEFT JOIN Orders o ON (c.Id = o.CustomerId)
WHERE o.CustomerId IS NULL;

---

三种写法性能比较

环境： MySQL5.7 、win10、16G 、机械硬盘(SSD烧坏了)

素材准备： 使用序号表numserial 批量插入数据 。 序号表如何创建： https://blog.csdn.net/qq_31156277/article/details/89328849

INSERT INTO customers(id,name)
select num,concat('name',n.num) 
from numserial n
where n.num > 4 and n.num <= 1000000;

INSERT INTO orders (id,customerid)
select n.num, ceiling(rand() * 1000000)
from numserial n 
where n.num > 2 and n.num <= 1000000;

在这两个表中已经存在了100w的数据。

---

附上一张执行计划相关参数的图：

未创建索引前：

第二种和第三种真的是太慢了，中途吃了个饭，但是还是没有执行完毕！强行停止创建索引了。

执行了这么久还是没有执行完毕。 原因在于会产生大量的随机I/O， 数据量太大，完全的笛卡尔积，双重循环操作，且执行完成后需要大量的merge；在加上磁盘性能读取也是比较慢的。

中断后创建索引，在orders表的customerid 字段上

-- 创建索引
CREATE  INDEX  idx_customerid ON orders (customerid);
-- 删除索引
DROP INDEX idx_customerid ON Orders;
-- 查看索引
SHOW INDEX FROM Orders

---

创建索引后：

---

SQL	无索引(大约值)	创建索引后(大约值)
NOT IN	3.4S	2.7S
NOT EXISTS	1h+(慢的太真实了)	4.5S
LEFT JOIN	1h+	2.3S

在有索引的情况下 left join是性能最好的，其次 not in 也是不错的，最后是not exists 。合理创建索引很有必要。能让连接时rows的数量直接变为1。

---

注意： 上面的结果不能说明 not in 比 not exists 更好; 任何的优化都需要根据具体的监控来进行，且不同的写法肯定有其最好和最坏的场景，所以不能一概而论。； 同时，创建好的索引对SQL性能的提高是不言而喻的。

---

参考

• 三值逻辑和NULL https://blog.csdn.net/qq_31156277/article/details/84333561