如何筛选具有多次通过关系的SQL结果

乜烨霖

2023-03-14

问题内容：

假设我有表student，club以及student_club：

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

我想知道如何找到足球（30）和棒球（50）俱乐部中的所有学生。
虽然此查询不起作用，但这是我到目前为止最接近的东西：

SELECT student.*
FROM   student
INNER  JOIN student_club sc ON student.id = sc.student_id
LEFT   JOIN club c ON c.id = sc.club_id
WHERE  c.id = 30 AND c.id = 50

问题答案：

我很好奇众所周知，好奇心以杀死猫而闻名。

那么，哪一种最快的方法是给猫皮呢？

此测试的精确蒙皮环境：

*Debian Squeeze上的 *PostgreSQL 9.0 ，具有不错的RAM和设置。
6.000名学生，24.000个俱乐部会员资格（数据从类似的数据库复制而来，带有真实生活的数据。）
从问题中的命名模式稍微转移：“ student.id是” student.stud_id和“ club.id在club.club_id这里”。
我在该线程中以查询的作者命名，在其中有两个索引。
我运行了所有查询几次以填充缓存，然后使用EXPLAIN ANALYZE选择了5个最好的查询。
相关指标（应该是最佳的-只要我们不具备要查询哪些俱乐部的知识）：
```
ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
```
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);

club_pkey这里的大多数查询都不需要。
主键在PostgreSQL中自动实现唯一索引。
最后一个索引是为了弥补PostgreSQL
上多列索引的已知缺点：

可以将多列B树索引用于涉及该索引列的任何子集的查询条件，但是当前导（最左边）列受到约束时，该索引效率最高。

EXPLAIN ANALYZE的总运行时间。

1）马丁2：44.594毫秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

2）Erwin 1：33.217毫秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

3）马丁1：31.735毫秒

SELECT s.stud_id, s.name
   FROM   student s
   WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30
   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50);

4）Derek：2.287毫秒

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

5）欧文2：2.181毫秒

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

6）肖恩：2.043毫秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

后三个的表现几乎相同。4）和5）得出相同的查询计划。

后期添加：

花式SQL，但性能跟不上。

7）超级立方体1：148.649毫秒

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c 
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

8）ypercube 2：147.497毫秒

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id  
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

不出所料，这两个的表现几乎相同。查询计划会导致表扫描，而计划者在这里找不到使用索引的方法。

9）Wildplasser 1：49.849毫秒

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

精美的SQL，CTE的性能不错。非常奇特的查询计划。
同样，有趣的是9.1如何处理这个问题。我将很快将此处使用的数据库集群升级到9.1。也许我会重新运行整个shebang …

10）Wildplasser 2：36.986毫秒

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

查询2的CTE变体。出乎意料的是，它可能会导致使用完全相同的数据的查询计划略有不同。我在上找到了顺序扫描student，其中子查询变量使用了索引。

11）超级立方体3：101.482毫秒

另一个后期添加@ypercube。有多少种方法真令人惊讶。

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   )

12）欧文3：2.377毫秒

@ypercube的11）实际上只是这个更简单的变体的令人费解的逆向方法，它也仍然缺少。执行几乎与顶级猫一样快。

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   )

13）欧文4：2.375毫秒

难以置信，但这是另一个全新的变体。我认为有超过两个成员的潜力，但它也仅以两个而跻身顶级猫之列。

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   )

俱乐部会员动态数量

换句话说：数量不同的过滤器。这个问题要求有两个俱乐部会员资格。但是许多用例必须为数量众多做准备。

在此相关的稍后答案中进行详细讨论：

在WHERE子句中多次使用同一列