반응형
SQL 결과를 has-many-through 관계로 필터링하는 방법
나는 테이블을 가지고 가정 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
나는 궁금했어. 그리고 우리 모두 알다시피 호기심은 고양이를 죽이는 것으로 유명합니다.
그렇다면 고양이의 피부를 가장 빨리 살리는 방법은 무엇일까요?
이 테스트를위한 정확한 고양이 가죽 환경 :
- 적절한 RAM 및 설정을 갖춘 Debian Squeeze의 PostgreSQL 9.0 .
- 학생 6.000 명, 클럽 회원 24.000 명 (실제 데이터와 유사한 데이터베이스에서 복사 한 데이터)
- 질문에서 명명 스키마에서 약간의 전환 :
student.idisstudent.stud_idandclub.idisclub.club_idhere. - 이 스레드에서 작성자의 이름을 따서 쿼리 이름을 지정했습니다. 인덱스는 2 개입니다.
- 캐시를 채우기 위해 모든 쿼리를 몇 번 실행 한 다음 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.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) 마틴 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) 데릭 : 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) wildplasser 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.986 ms
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) 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) 어윈 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) 어윈 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
)
동적 클럽 회원 수
즉, 다양한 필터 수입니다. 이 질문은 정확히 두 개의 클럽 회원을 요구했습니다 . 그러나 많은 사용 사례는 다양한 수에 대비해야합니다.
이 관련 이후 답변에 대한 자세한 설명은 다음과 같습니다.
SELECT s.*
FROM student s
INNER JOIN student_club sc_soccer ON s.id = sc_soccer.student_id
INNER JOIN student_club sc_baseball ON s.id = sc_baseball.student_id
WHERE
sc_baseball.club_id = 50 AND
sc_soccer.club_id = 30
select *
from student
where id in (select student_id from student_club where club_id = 30)
and id in (select student_id from student_club where club_id = 50)
student_id를 원하면 다음을 수행하십시오.
Select student_id
from student_club
where club_id in ( 30, 50 )
group by student_id
having count( student_id ) = 2
학생의 이름도 필요하면 :
Select student_id, name
from student s
where exists( select *
from student_club sc
where s.student_id = sc.student_id
and club_id in ( 30, 50 )
group by sc.student_id
having count( sc.student_id ) = 2 )
club_selection 테이블에 두 개 이상의 클럽이있는 경우 :
Select student_id, name
from student s
where exists( select *
from student_club sc
where s.student_id = sc.student_id
and exists( select *
from club_selection cs
where sc.club_id = cs.club_id )
group by sc.student_id
having count( sc.student_id ) = ( select count( * )
from club_selection ) )
SELECT *
FROM student
WHERE id IN (SELECT student_id
FROM student_club
WHERE club_id = 30
INTERSECT
SELECT student_id
FROM student_club
WHERE club_id = 50)
또는 더 쉽게 더 일반적인 솔루션으로 확장하는 n클럽 것을 피합니다 INTERSECT(MySQL을 사용할 수 없습니다) 및 IN(로 이것의 성능이 MySQL을 빨아 )
SELECT s.id,
s.name
FROM student s
join student_club sc
ON s.id = sc.student_id
WHERE sc.club_id IN ( 30, 50 )
GROUP BY s.id,
s.name
HAVING COUNT(DISTINCT sc.club_id) = 2
다른 CTE. 깔끔해 보이지만 일반 하위 쿼리의 groupby와 동일한 계획을 생성 할 수 있습니다.
WITH two AS (
SELECT student_id FROM tmp.student_club
WHERE club_id IN (30,50)
GROUP BY student_id
HAVING COUNT(*) > 1
)
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
;
테스트하려는 사람들을 위해 내 생성 테스트 데이터 사본 :
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;
CREATE TABLE tmp.student
( id INTEGER NOT NULL PRIMARY KEY
, sname VARCHAR
);
CREATE TABLE tmp.club
( id INTEGER NOT NULL PRIMARY KEY
, cname VARCHAR
);
CREATE TABLE tmp.student_club
( student_id INTEGER NOT NULL REFERENCES tmp.student(id)
, club_id INTEGER NOT NULL REFERENCES tmp.club(id)
);
INSERT INTO tmp.student(id)
SELECT generate_series(1,1000)
;
INSERT INTO tmp.club(id)
SELECT generate_series(1,100)
;
INSERT INTO tmp.student_club(student_id,club_id)
SELECT st.id , cl.id
FROM tmp.student st, tmp.club cl
;
DELETE FROM tmp.student_club
WHERE random() < 0.8
;
UPDATE tmp.student SET sname = 'Student#' || id::text ;
UPDATE tmp.club SET cname = 'Soccer' WHERE id = 30;
UPDATE tmp.club SET cname = 'Baseball' WHERE id = 50;
ALTER TABLE tmp.student_club
ADD PRIMARY KEY (student_id,club_id)
;
따라서 고양이 피부를 만드는 방법은 여러 가지가 있습니다 . 더 완벽하게 만들기 위해 두 개 를 더
추가하겠습니다 .
1) 먼저 GROUP, JOIN 나중에
온전한 데이터 모델 가정 (student_id, club_id)입니다 고유 의를 student_club. Martin Smith의 두 번째 버전은 다소 비슷하지만 먼저 그룹에 합류합니다. 더 빠를 것입니다.
SELECT s.id, s.name
FROM student s
JOIN (
SELECT student_id
FROM student_club
WHERE club_id IN (30, 50)
GROUP BY 1
HAVING COUNT(*) > 1
) sc USING (student_id);
2) 존재
물론 고전적인 EXISTS. Derek의 IN. 간단하고 빠릅니다. (MySQL에서는를 사용하는 변형보다 훨씬 빠릅니다 IN) :
SELECT s.id, s.name
FROM student s
WHERE EXISTS (SELECT 1 FROM student_club
WHERE student_id = s.student_id AND club_id = 30)
AND EXISTS (SELECT 1 FROM student_club
WHERE student_id = s.student_id AND club_id = 50);
아무도이 (클래식) 버전을 추가하지 않았기 때문에 :
SELECT s.*
FROM student AS s
WHERE NOT EXISTS
( SELECT *
FROM club AS c
WHERE c.id IN (30, 50)
AND NOT EXISTS
( SELECT *
FROM student_club AS sc
WHERE sc.student_id = s.id
AND sc.club_id = c.id
)
)
또는 유사 :
SELECT s.*
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.student_id = s.id
AND sc.club_id = c.club_id
)
)
약간 다른 접근 방식으로 한 번 더 시도하십시오. Explain Extended : EAV 테이블의 여러 속성 : GROUP BY 대 NOT EXISTS 의 기사에서 영감을 얻었습니다 .
SELECT s.*
FROM student_club AS sc
JOIN student AS s
ON s.student_id = sc.student_id
WHERE sc.club_id = 50 --- one option here
AND NOT EXISTS
( SELECT *
FROM
( SELECT 30 AS club_id --- all the rest in here
--- as in previous query
) AS c
WHERE NOT EXISTS
( SELECT *
FROM student_club AS scc
WHERE scc.student_id = sc.id
AND scc.club_id = c.club_id
)
)
또 다른 접근 방식 :
SELECT s.stud_id
FROM student s
EXCEPT
SELECT stud_id
FROM
( SELECT s.stud_id, c.club_id
FROM student s
CROSS JOIN (VALUES (30),(50)) c (club_id)
EXCEPT
SELECT stud_id, club_id
FROM student_club
WHERE club_id IN (30, 50) -- optional. Not needed but may affect performance
) x ;
WITH RECURSIVE two AS
( SELECT 1::integer AS level
, student_id
FROM tmp.student_club sc0
WHERE sc0.club_id = 30
UNION
SELECT 1+two.level AS level
, sc1.student_id
FROM tmp.student_club sc1
JOIN two ON (two.student_id = sc1.student_id)
WHERE sc1.club_id = 50
AND two.level=1
)
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
WHERE two.level> 1
;
CTE 스캔은 두 개의 개별 하위 쿼리가 필요하지 않기 때문에 이것은 합리적으로 잘 수행되는 것 같습니다.
재귀 쿼리를 오용하는 이유는 항상 있습니다!
(BTW : mysql에는 재귀 쿼리가없는 것 같습니다)
쿼리 2) 및 10)의 다른 쿼리 계획
실제 DB에서 테스트했기 때문에 이름이 catskin 목록과 다릅니다. 백업 복사본이므로 모든 테스트 실행 중에 아무것도 변경되지 않았습니다 (카탈로그에 대한 사소한 변경 제외).
쿼리 2)
SELECT a.*
FROM ef.adr a
JOIN (
SELECT adr_id
FROM ef.adratt
WHERE att_id IN (10,14)
GROUP BY adr_id
HAVING COUNT(*) > 1) t using (adr_id);
Merge Join (cost=630.10..1248.78 rows=627 width=295) (actual time=13.025..34.726 rows=67 loops=1)
Merge Cond: (a.adr_id = adratt.adr_id)
-> Index Scan using adr_pkey on adr a (cost=0.00..523.39 rows=5767 width=295) (actual time=0.023..11.308 rows=5356 loops=1)
-> Sort (cost=630.10..636.37 rows=627 width=4) (actual time=12.891..13.004 rows=67 loops=1)
Sort Key: adratt.adr_id
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=12.386..12.710 rows=67 loops=1)
Filter: (count(*) > 1)
-> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.245..5.958 rows=2811 loops=1)
Recheck Cond: (att_id = ANY ('{10,14}'::integer[]))
-> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.217..0.217 rows=2811 loops=1)
Index Cond: (att_id = ANY ('{10,14}'::integer[]))
Total runtime: 34.928 ms
쿼리 10)
WITH two AS (
SELECT adr_id
FROM ef.adratt
WHERE att_id IN (10,14)
GROUP BY adr_id
HAVING COUNT(*) > 1
)
SELECT a.*
FROM ef.adr a
JOIN two using (adr_id);
Hash Join (cost=1161.52..1261.84 rows=627 width=295) (actual time=36.188..37.269 rows=67 loops=1)
Hash Cond: (two.adr_id = a.adr_id)
CTE two
-> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=13.059..13.447 rows=67 loops=1)
Filter: (count(*) > 1)
-> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.252..6.252 rows=2811 loops=1)
Recheck Cond: (att_id = ANY ('{10,14}'::integer[]))
-> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.226..0.226 rows=2811 loops=1)
Index Cond: (att_id = ANY ('{10,14}'::integer[]))
-> CTE Scan on two (cost=0.00..50.16 rows=627 width=4) (actual time=13.065..13.677 rows=67 loops=1)
-> Hash (cost=384.68..384.68 rows=5767 width=295) (actual time=23.097..23.097 rows=5767 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1153kB
-> Seq Scan on adr a (cost=0.00..384.68 rows=5767 width=295) (actual time=0.005..10.955 rows=5767 loops=1)
Total runtime: 37.482 ms
@ erwin-brandstetter 제발, 이것을 벤치마킹하십시오 :
SELECT s.stud_id, s.name
FROM student s, student_club x, student_club y
WHERE x.club_id = 30
AND s.stud_id = x.stud_id
AND y.club_id = 50
AND s.stud_id = y.stud_id;
@sean의 번호 6)처럼 깨끗합니다.
-- EXPLAIN ANALYZE
WITH two AS (
SELECT c0.student_id
FROM tmp.student_club c0
, tmp.student_club c1
WHERE c0.student_id = c1.student_id
AND c0.club_id = 30
AND c1.club_id = 50
)
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
;
쿼리 계획 :
Hash Join (cost=1904.76..1919.09 rows=337 width=15) (actual time=6.937..8.771 rows=324 loops=1)
Hash Cond: (two.student_id = st.id)
CTE two
-> Hash Join (cost=849.97..1645.76 rows=337 width=4) (actual time=4.932..6.488 rows=324 loops=1)
Hash Cond: (c1.student_id = c0.student_id)
-> Bitmap Heap Scan on student_club c1 (cost=32.76..796.94 rows=1614 width=4) (actual time=0.667..1.835 rows=1646 loops=1)
Recheck Cond: (club_id = 50)
-> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.36 rows=1614 width=0) (actual time=0.473..0.473 rows=1646 loops=1)
Index Cond: (club_id = 50)
-> Hash (cost=797.00..797.00 rows=1617 width=4) (actual time=4.203..4.203 rows=1620 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 57kB
-> Bitmap Heap Scan on student_club c0 (cost=32.79..797.00 rows=1617 width=4) (actual time=0.663..3.596 rows=1620 loops=1)
Recheck Cond: (club_id = 30)
-> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.38 rows=1617 width=0) (actual time=0.469..0.469 rows=1620 loops=1)
Index Cond: (club_id = 30)
-> CTE Scan on two (cost=0.00..6.74 rows=337 width=4) (actual time=4.935..6.591 rows=324 loops=1)
-> Hash (cost=159.00..159.00 rows=8000 width=15) (actual time=1.979..1.979 rows=8000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 374kB
-> Seq Scan on student st (cost=0.00..159.00 rows=8000 width=15) (actual time=0.093..0.759 rows=8000 loops=1)
Total runtime: 8.989 ms
(20 rows)
그래서 여전히 학생에 대한 seq 스캔을 원하는 것 같습니다.
SELECT s.stud_id, s.name
FROM student s,
(
select x.stud_id from
student_club x
JOIN student_club y ON x.stud_id = y.stud_id
WHERE x.club_id = 30
AND y.club_id = 50
) tmp_tbl
where tmp_tbl.stud_id = s.stud_id
;
가장 빠른 변형 사용 (Mr. Brandstetter 차트의 Mr. Sean). 단 하나의 조인으로 변형 될 수 있습니다. student_club 매트릭스에만 살 권리가 있습니다. 따라서 가장 긴 쿼리에는 계산할 열이 두 개뿐입니다. 아이디어는 쿼리를 얇게 만드는 것입니다.
참고 URL : https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation
반응형
'code' 카테고리의 다른 글
| 소스가 git에서 감지되었지만 등록되지 않은 Vcs 루트가 감지되도록 IntelliJ IDEA를 구성하십시오. (0) | 2020.08.30 |
|---|---|
| 내 열 (SQL Server 2008 R2)에 고유 한 제약 조건을 만들려면 어떻게해야합니까? (0) | 2020.08.30 |
| .NET에서 Settings.settings 파일을 사용할 때 구성은 실제로 어디에 저장됩니까? (0) | 2020.08.30 |
| LINQ에서 SELECT UNIQUE를 수행하려면 어떻게해야합니까? (0) | 2020.08.30 |
| NSZombie는 무엇입니까? (0) | 2020.08.29 |