programing

Has-many-through 관계에서 SQL 결과를 필터링하는 방법

javaba 2022. 12. 25. 09:47
반응형

Has-many-through 관계에서 SQL 결과를 필터링하는 방법

이 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.idstudent.stud_id ★★★★★★★★★★★★★★★★★」club.idclub.club_id
  • 나는 이 스레드에서 질의 작성자의 이름을 따서 질의에 이름을 붙였다.
  • 모든 를 몇 번 한 후 를 .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는 대부분의 쿼리에 필요하지 않습니다.
한 인덱스를 합니다.SQL.
마지막 지수는 Postgre의 여러 열 인덱스의 알려진 단점을 보완하는 것입니다.SQL:

다중 색상 B-트리 인덱스는 인덱스 열의 하위 집합과 관련된 쿼리 조건과 함께 사용할 수 있지만 선행(맨 왼쪽) 열에 제약 조건이 있을 때 인덱스가 가장 효율적입니다.

결과.

★★★★★★★★★★★★★★★★★★★★★로부터의 합계 실행 시간:EXPLAIN ANALYZE.

1) Martin 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) Erwin 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) ypercube 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) 와일드플래서 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에 적합한 성능.매우 이국적인 질의 계획이야

10) 와일드플래서 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);

CTE 2).놀랍게도 동일한 데이터를 사용하여 약간 다른 쿼리 계획이 발생할 수 있습니다.student여기서, 서브쿼리가 인덱스를 사용했습니다.

11) ypercube 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) erwin 3: 2.377 밀리초

ypercube's 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.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
   );

동호회원 동수

즉, 필터의 수가 다릅니다.이 질문은 정확히 두 개의 클럽 회원권을 요구했다.그러나 많은 사용 사례는 다양한 숫자에 대비해야 합니다.참조:

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 테이블에 3개 이상의 클럽이 있는 경우:

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

고양이 가죽을 벗기는 방법은 여러 가지가 있습니다.
두 개를 더하면 완성도가 높아지겠죠

1) 그룹화 후 참여

인 을 전제로 .(student_id, club_id)에 있어서 유일하다student_clubMartin 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) 존재

그리고 물론, 고전적인 것이 있다.EXISTSIN.(에서는 이 가 'MySQL'의 경우 'MySQL'의 'MySQL'의 경우 'MySQL'의 경우 'MySQL'보다 훨씬 .(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);

또 다른 CTE.깔끔해 보이지만 일반 서브쿼리의 그룹바이와 같은 플랜을 생성할 수 있습니다.

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)
    ;

테스트하고 싶은 분들을 위해 generate test data의 복사본은 다음과 같습니다.

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)
    ;

이 (클래식)버전은 아직 추가되지 않았기 때문에:

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  
              )
      )

조금 다른 방법으로 한 번 더 시도합니다.'확장 설명' 기사에서 영감을 얻어: EAV 테이블에는 GROUP BY 와 NOT EXISTES 의 복수의 어트리뷰트가 있습니다.

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 스캔에서는 2개의 서브쿼리가 필요 없기 때문에 이 처리는 상당히 양호한 것으로 보입니다.

재귀 쿼리를 오용하는 데는 항상 이유가 있습니다.

(BTW: mysql에 재귀 쿼리가 없는 것 같습니다.)

쿼리 2)와 10의 다른 쿼리 계획

실생활 DB에서 테스트를 해봤기 때문에 이름이 캣스킨 리스트와 다릅니다.백업 복사본이므로 모든 테스트 실행 중에 변경된 내용이 없습니다(카탈로그에 대한 사소한 변경 사항 제외).

질문 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 차트의 미스터 션).student_club 매트릭스에만 가입할 수 있는 배리언트일 수 있습니다.따라서 가장 긴 쿼리에는 계산할 열이 2개밖에 없습니다. 즉, 쿼리를 씬으로 만드는 것이 아이디어입니다.

언급URL : https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation

반응형
저작자표시

'programing' 카테고리의 다른 글

서비스 중지/시작 시 MariaDB 메시지  (0) 2022.12.25
MySql 날짜/시간 값이 잘못되었습니다.  (0) 2022.12.25
지정된 위치에 문자열 삽입  (0) 2022.12.25
1 대 n의 관련 테이블에서 여러 특정 매칭이 있는 테이블에서 Select를 수행하려면 더 빠른 솔루션이 필요합니다.  (0) 2022.12.05
time.struct_time 개체를 datetime 개체로 변환하려면 어떻게 해야 합니까?  (0) 2022.12.05

'programing'의 다른글

  • 현재글Has-many-through 관계에서 SQL 결과를 필터링하는 방법

관련글

댓글

티스토리툴바