JOIN 4번 문제

JunYeob Lee 님 안녕하세요! 쿼리 최적화까지 생각하시면서 작성하고 계시는군요! 잘 학습하고 계시네요.

말씀하신대로 쿼리를 작성하면 아래 형태를 생각하신거겠지요?

SELECT
  p.type1,
  COUNT(tp.id) AS pokemon_cnt
FROM (
  SELECT id
  FROM basic.trainer
  WHERE achievement_level = 'Master'
) AS master_trainers
INNER JOIN basic.trainer_pokemon AS tp ON master_trainers.id = tp.trainer_id
LEFT JOIN basic.pokemon AS p ON tp.pokemon_id = p.id
WHERE tp.status IN ('Active', 'Training')
GROUP BY p.type1

쿼리 최적화 관점에선 무조건 어떤 방법이 좋다는 것보단, 데이터의 분포 등을 고려해야 합니다. JOIN을 할 때 내부적으로 어떻게 연산이 되는지 알려드리면

INNER JOIN : Broadcast JOIN
LEFT, RIGHT, FULL OUTER JOIN : Hash JOIN
(참고 : 무조건 이 조인이 사용되는 것은 아니고 데이터의 분포 등에 따라 빅쿼리가 최선을 선택)

대표적으로 2개를 많이 언급합니다. 이걸 알기 위해 노드라는 개념을 알아야 하는데, 노드는 컴퓨터라고 생각하면 됩니다. 대규모 데이터를 처리하는 시스템은 내부적으로 여러 컴퓨터를 네트워크로 연결해 작업을 진행합니다.

Broadcast JOIN

주로 INNER JOIN
작은 테이블의 전체 데이터를 모든 노드에 브로드캐스트
큰 테이블은 파티션된 상태로 유지
각 노드는 로컬에서 조인을 수행
작은 테이블이 메모리에 완전히 들어갈 수 있을 때 효과적
테이블 크기의 차이가 존재할 때 (하나가 작고, 하나가 클 때) 효과적

Hash JOIN

양쪽 테이블 모두 해시 테이블로 파티셔닝
파티션끼리 매칭되어 조인이 실행
대규모 데이터 JOIN에 효과적

준엽님이 말씀해주신 방식은 브로드캐스트 JOIN, 제가 사용한 방식은 해시 JOIN을 사용합니다. 데이터의 분포나 상황에 따라서 어떤 것이 더 좋은지가 결정됩니다.

빅쿼리가 내부적으로 데이터를 파악하고, 최선의 결정을 선택합니다. 사용자 입장에선 크게 신경쓸 부분이 적긴 합니다. 그래서 사용자 입장에서 어떤 방법이 제일 좋다는 것보단, 쿼리를 실행해보고 실행 세부 정보를 확인하는 것이 좋습니다.

아래 문서를 보시면 최적화 전략에 나오는데, 참고해보시면 좋을 것 같아요. 제가 제일 자주 사용하는 것은 필요한 데이터만 필터링하고, JOIN할 때는 제일 큰 테이블을 제일 왼쪽에 두고 그 후 테이블을 오른쪽에 배치합니다.

https://cloud.google.com/bigquery/docs/best-practices-performance-compute#optimize_your_join_patterns

준엽님이 말씀하신 방식의 쿼리를 실행할 때(캐싱 적용 제외)

SELECT
  p.type1,
  COUNT(tp.id) AS pokemon_cnt
FROM (
  SELECT
    id
  FROM
    basic.trainer
  WHERE
    achievement_level = 'Master' ) AS master_trainers
INNER JOIN
  basic.trainer_pokemon AS tp
ON
  master_trainers.id = tp.trainer_id
LEFT JOIN
  basic.pokemon AS p
ON
  tp.pokemon_id = p.id
WHERE
  tp.status IN ('Active',
    'Training')
GROUP BY
  p.type1

강의에서 공유드린 쿼리(캐싱 적용 제외)


SELECT
  type1,
  COUNT(tp.id) AS pokemon_cnt
FROM (
  SELECT
    id, 
    trainer_id,
    pokemon_id,
    status
  FROM basic.trainer_pokemon
  WHERE
    status IN ("Active", "Training")
) AS tp
LEFT JOIN basic.pokemon AS p
ON tp.pokemon_id = p.id
LEFT JOIN basic.trainer AS t
ON tp.trainer_id = t.id
# LEFT JOIN을 연속해서 2번 사용할 수 있다(N번)
WHERE
  t.achievement_level = "Master"
GROUP BY
  type1

이렇게 나오고, 여러번 실행해서 경과 시간을 평균으로 비교해보면 됩니다. 한번만 실행하는 것보다 여러번 비교하는게 좋아요(매번 약간씩 달라짐)

지금 데이터는 작은 사이즈라 20밀리초 차이로 보이는데 이게 숫자적으로 성능이 우세하다고 하면 할 수도 있고, 이정도면 큰 차이가 없다라고 볼 수도 있습니다. 판단하는 사람의 관점에 따라 다를 것 같네요. 최적화 관련 글 읽어보시고 브로드캐스트 조인, 해시 조인에 대해 인지해보시면 좋을 것 같아요

또 궁금한 내용이 있다면 말씀해주셔요!

인프런 커뮤니티 질문&답변