SQL最佳实践：避免使用COUNT=0

如果你遇到类似下面的 SQL 查询：

SELECT *
FROM customer c
WHERE 0 = (SELECT COUNT(*)FROM orders oWHERE o.customer_id = c.customer_id);

意味着有人没有遵循 SQL 最佳实践。该语句的作用是查找没有下过订单的客户，其中子查询使用了 COUNT 函数统计客户的订单数量，如果结果等于零，代表没有订单。

从逻辑上来讲，以上查询没有问题；但是它可能存在性能问题。原因在于 COUNT 函数需要统计订单数量，如果某个客户有大量订单，数据库需要计算出一个总数；即使客户的订单数量不多，仍然需要进行统计。

以下是 MySQL 中的执行计划：

EXPLAIN ANALYZE 
SELECT *
FROM customer c
WHERE 0 = (SELECT COUNT(*)FROM orders oWHERE o.customer_id = c.customer_id);-> Filter: (0 = (select #2))  (cost=111563.21 rows=1040858) (actual time=8.305..7043.840 rows=231117 loops=1)-> Table scan on c  (cost=111563.21 rows=1040858) (actual time=6.968..1104.438 rows=1048577 loops=1)-> Select #2 (subquery in condition; dependent)-> Aggregate: count(0)  (cost=1.30 rows=2) (actual time=0.005..0.005 rows=1 loops=1048577)-> Index lookup on o using customer_id (customer_id=c.customer_id)  (cost=1.15 rows=2) (actual time=0.004..0.005 rows=2 loops=1048577)

其中Aggregate表示子查询中执行了聚合操作，查询的执行时间大概是 7 秒。

实际上这个 COUNT 函数结果对于我们并不重要，我们只需要知道客户是否存在订单。这种情况下，最简单的方法就是使用 EXISTS 运算符：

SELECT *
FROM customer c
WHERE NOT EXISTS (SELECT 1FROM orders oWHERE o.customer_id = c.customer_id);

如果客户存在任何订单，子查询就会获得结果，NOT EXISTS 运算符就会排除对应的客户，从而避免统计全部订单数据。新的执行计划如下：

EXPLAIN ANALYZE 
SELECT *
FROM customer c
WHERE NOT EXISTS (SELECT 1FROM orders oWHERE o.customer_id = c.customer_id);-> Nested loop antijoin  (cost=217780392498.61 rows=2177801765412) (actual time=2273.676..3746.089 rows=231117 loops=1)-> Table scan on c  (cost=111871.61 rows=1040858) (actual time=2.191..804.896 rows=1048577 loops=1)-> Single-row index lookup on <subquery2> using <auto_distinct_key> (customer_id=c.customer_id)  (actual time=0.000..0.000 rows=1 loops=1048577)-> Materialize with deduplication  (cost=423621.04..423621.04 rows=2092314) (actual time=2797.545..2797.545 rows=1021689 loops=1)-> Filter: (o.customer_id is not null)  (cost=214389.64 rows=2092314) (actual time=0.811..1372.007 rows=2097157 loops=1)-> Index scan on o using customer_id  (cost=214389.64 rows=2092314) (actual time=0.809..1164.613 rows=2097157 loops=1)

新的查询计划使用了 antijoin 连接和物化（Materialize），执行时间不到 4 秒。

无论用户订单数量多还是少，NOT EXISTS 的性能都不会差于 COUNT 函数，而且绝大部分情况下它的性能会更好。