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

小编典典

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

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

此测试的精确蒙皮环境：

*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

)

俱乐部会员动态数量

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

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

2020-05-17