Join 알고리즘 및 Index 활용 개념

개요

• Join 알고리즘과 Index 활용 개념 정리
• FULL OUTER JOIN과 CROSS JOIN 사용 사례

Join 알고리즘의 이해

데이터베이스에서 Join을 수행할 때, 옵티마이저에 의해 Nested Loop Join과 Hash Join이 주로 사용된다

1. Nested Loop Join

• 동작 원리: 두 테이블 간의 Join에서, 외부 테이블의 각 행을 가져와 내부 테이블을 반복적으로 검색함
• 사용 시점:
  • 작은 데이터셋 또는 Index가 있는 경우
  • Index를 사용하여 내부 테이블의 행을 빠르게 찾을 수 있을 때
• 장점: 단순하고 직관적이며, 작은 테이블에서 효율적
• 단점: 데이터셋이 크거나 Index가 없는 경우 비효율적

2. Hash Join

• 동작 원리: Join 조건에 맞는 테이블의 데이터를 메모리에 해시 테이블로 생성하고, 다른 테이블의 데이터를 이 해시 테이블과 Join함
• 사용 시점:
  • 대규모 데이터셋에서 Index가 없을 때
  • 메모리에 해시 테이블을 생성할 수 있는 충분한 자원이 있을 때
• 장점: 큰 데이터셋을 효율적으로 처리할 수 있음
• 단점: 메모리 의존적이며, 메모리 부족 시 성능 저하 발생 가능

Index 활용

1. 단일 컬럼 Index와 복합 컬럼 Index

• 단일 컬럼 Index: 특정 테이블의 개별 컬럼에 대해 생성되는 Index로, 각 컬럼에 대해 독립적인 B-Tree 구조가 생성됨. 예를 들어, CREATE INDEX idx_column1 ON my_table(column1);와 같이 설정하면, column1에 대한 Index가 B-Tree 구조로 생성되어, 해당 컬럼을 사용하는 쿼리에서 빠르게 검색이 가능해짐
• 복합 컬럼 Index: 여러 컬럼을 결합하여 하나의 Index를 생성하는 경우로, 결합된 컬럼에 대해 하나의 B-Tree 구조가 생성됨. 예를 들어, CREATE INDEX idx_composite ON my_table(column1, column2, column3);와 같이 설정하면, column1, column2, column3이 결합된 Index가 생성됨. 이 Index는 먼저 column1, 그 다음 column2, 마지막으로 column3의 값을 기준으로 정렬된 형태로 유지되며, 선두 컬럼부터 시작하는 조건을 사용할 때 효과적임

2. Index와 Join 알고리즘의 관계

• Nested Loop Join: Index를 잘 활용하여 성능을 높일 수 있음
• Hash Join: Index가 없는 경우 자주 사용되지만, Index를 추가하면 Nested Loop Join으로 변경 가능
• 성능 최적화: Hash Join이 사용되는 쿼리들을 모니터링하고, Index를 추가함으로써 성능 향상 가능

FULL OUTER JOIN과 CROSS JOIN

1. FULL OUTER JOIN

FULL OUTER JOIN은 두 테이블 간의 모든 행을 조인 결과에 포함시키며, 조인 조건을 만족하지 않는 경우에도 행을 유지하고, 해당하는 다른 테이블의 값은 NULL로 표시함

사용 예시

• 데이터 일치성 검토:

  • customers와 orders 테이블이 있다고 가정
  • customers 테이블에는 고객 정보가, orders 테이블에는 주문 정보가 포함됨
  • 모든 고객에 대한 주문 내역을 보고 싶고, 주문 내역이 없는 고객과 고객 정보가 없는 주문을 모두 보고 싶을 때 사용

  SELECT c.customer_id, c.customer_name, o.order_id
  FROM customers c
  FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

  • 이 쿼리는 모든 고객과 모든 주문을 결과에 포함시킴
  • 주문 내역이 없는 고객은 order_id가 NULL로 표시되고, 고객 정보가 없는 주문은 customer_id와 customer_name이 NULL로 표시됨

• 데이터 비교:

  • 두 개의 로그 테이블이나, 두 개의 다른 소스에서 온 데이터를 비교할 때 사용 가능
  • 예를 들어, 같은 구조의 두 테이블이 다른 시스템에서 온 경우, 서로 일치하지 않는 행들을 확인하기 위해 FULL OUTER JOIN을 사용할 수 있음

2. CROSS JOIN

CROSS JOIN은 두 테이블 간의 모든 가능한 행의 조합(카르테시안 곱)을 생성함. 모든 조합을 필요로 하는 시나리오에서 사용됨

사용 예시

• 모든 조합 계산:

  • 예를 들어, 제품과 고객 테이블이 있을 때, 모든 고객이 모든 제품을 구매할 수 있는 가능성을 계산하고자 할 때 사용

  SELECT p.product_name, c.customer_name
  FROM products p
  CROSS JOIN customers c;

  • 이 쿼리는 모든 제품과 모든 고객의 조합을 생성함
  • 예를 들어, 5개의 제품과 10명의 고객이 있다면, 총 50개의 결과 행이 나옴
  • 이 결과는 어떤 제품이 어떤 고객에게 팔릴 수 있는지에 대한 모든 가능성을 나타냄

• 날짜와 항목의 조합:

  • 일정한 기간 동안 각 항목의 상태를 모두 보여주고 싶을 때 사용 가능
  • 예를 들어, 일별 판매량을 보고할 때 모든 날짜와 모든 제품을 결합하여 모든 가능성을 보여줌

  SELECT d.date, p.product_name
  FROM dates d
  CROSS JOIN products p;

  • 이 쿼리는 모든 날짜에 대해 모든 제품이 결합된 결과를 생성함
  • 이후 이 결과에 판매량 데이터를 조인하여 특정 날짜에 특정 제품의 판매량을 표시할 수 있음