mysql 多对多 过滤_mysql – 如何在一个多对多关系中过滤SQL结果

我好奇。而我们都知道，好奇心有杀猫的声誉。

所以，这是最快的方式皮肤的猫？

这个测试的精确的皮肤环境：

> PostgreSQL 9.0在Debian挤压与体面的内存和设置。

> 6.000名学生，24.000个俱乐部会员资格(从具有现实生活数据的类似数据库复制的数据)。

>稍微偏离命名模式的问题：student.id是student.stud_id，club.id是club.club_id这里。

>我命名查询后，他们的作者在这个线程，有一个索引，其中有两个。

>我跑了所有查询几次填充缓存，然后我选择了最好的5与EXPLAIN ANALYZE。

>相关指数(应该是最佳的 – 只要我们缺乏知识，哪些俱乐部将被查询)：

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 multi-column indexes：

A multicolumn B-tree index can be used with query conditions that

involve any subset of the index’s columns, but the index is most

efficient when there are constraints on the leading (leftmost)

columns.

结果：

来自EXPLAIN ANALYZE的总运行时。

1)Martin 2：44.594ms

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.217ms

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)Martin 1：31.735ms

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.287ms

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)Erwin 2：2.181ms

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)Sean：2.043ms

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)ypercube 1：148.649ms

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.497ms

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)野生型1：49.849ms

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如何处理这一点。我要将这里使用的db集群升级到9.1。也许我会重新运行整个shebang …

10)wildplasser 2：36.986ms

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变体)。令人惊讶的是，它可能导致与完全相同的数据略有不同的查询计划。我发现在学生的顺序扫描，其中子查询变体使用索引。

11)ypercube 3：101.482ms

另一个晚添加@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)erwin 3：2.377ms

@ 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)erwin 4：2.375ms

很难相信，但这里是另一个，真正新的变种。我看到潜在的两个以上的会员，但它也排在只有两个顶级的猫。

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

)