PieCloudDB中查询购买全部产品的客户ID及SQL性能优化求助

问题描述 #

PieCloudDB数据库中有两张表：

• customer表：记录客户ID（customer_id）及其购买的产品ID（product_key）
• product表：记录所有产品的主键（product_key）

需要找出购买了全部产品的客户ID，示例数据及预期输出如下：

Customer表：

Product表：

预期输出：

原查询的性能问题 #

你尝试的查询使用了COUNT(DISTINCT product_key)，在数据量较大时，这个操作需要对分组后的每个客户的产品ID去重并计数，计算成本很高，导致查询耗时过长。

优化方案 #

1. 添加索引（优先操作）

首先给customer表创建联合索引，加速分组和统计操作：

CREATE INDEX idx_customer_product ON customer (customer_id, product_key);

如果product表的product_key还不是主键或没有索引，也建议创建：

CREATE INDEX idx_product_key ON product (product_key);

2. 优化后的查询语句

方案一：简化原查询结构

去掉子查询的JOIN，直接在HAVING子句中对比计数，逻辑更简洁，性能也会有所提升：

SELECT customer_id
FROM customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM product);

方案二：使用双重NOT EXISTS（适合大表场景）

通过嵌套的NOT EXISTS检查客户是否存在未购买的产品，利用索引可以快速定位数据，避免昂贵的去重计数：

SELECT DISTINCT c.customer_id
FROM customer c
WHERE NOT EXISTS (
    SELECT 1
    FROM product p
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer c2
        WHERE c2.customer_id = c.customer_id
          AND c2.product_key = p.product_key
    )
);

方案三：利用JOIN和分组计数

通过将customer表与product表关联，统计每个客户匹配到的产品数量，再与总产品数对比：

SELECT c.customer_id
FROM customer c
INNER JOIN product p ON c.product_key = p.product_key
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(*) FROM product);

这些方案中，方案二在数据量极大且索引完善的情况下通常表现最优，因为它避免了全局的去重计数操作，而是通过索引快速验证每个客户的产品覆盖情况。

内容的提问来源于stack exchange，提问作者heihei Li