3조 SQL JOIN
$ sudo apt update
$ sudo apt install mysql-server$ mysql -v조인 대상 테이블의 인덱스가 있는 경우 사용
- Loop의 기준이 되는 드라이빙/드리븐 테이블 선정
- 드라이빙 테이블 기준으로 반복해서 읽으며 인덱스를 이용한 조인 수행
- 조인해야 할 데이터가 많지 않은 경우에 사용
- 기준이 될 드라이빙 테이블을 선정하고 이 테이블로부터 where 절에 정의된 검색 조건을 만족하는 데이터들을 필터링한 후, 이 값을 가지고 조인 대상 테이블을 반복적으로 검색하면서 조인 조건을 만족하는 결과값을 얻어냄
- 인덱스가 없으면 이 조인을 이용하지 않는다.
- 두 테이블 모두 인덱스를 가지고 있다면 레코드 수가 적은 것이 드라이빙 테이블로 선택된다.
- 둘 중 하나에만 인덱스가 있는 경우 인덱스가 없는 테이블이 드라이빙 테이블로 선택된다. 인덱스가 있는 테이블을 드라이빙 테이블로 선정하는 경우, 드리븐 테이블을 읽으면서 계속해서 Full Scan이 발생할 수 있다.
Driving table → Driven table 이 있어서 중첩For문으로 Join을 하는 방법 → Optimizer 에 의해 driving과 driven 테이블이 선택됨
Index가 없으면 (어느 컬럼에?) Nested Join을 안탄다.
MySQL은 Sort Merge Join을 지원하지 않는다.
- 조인 대상 테이블에 인덱스가 없는 경우 사용
- 조회의 범위가 많을 때 주로 사용하는 조인 방법론
- 양쪽 테이블을 각각 access 하여 결과를 정렬하고 정렬한 결과를 차례로 Scan 해 나가면서 연결고리의 조건으로 merge를 하는 방식
- 주로 조인 조건 컬럼에 인덱스가 없거나, 출력해야 할 결과 값이 많을 때 사용
- 조회 범위가 좁을 때 유리한 Nested Loop Join과 장단점이 서로 반대
- 해싱 기법을 이용하여 Join을 실행하는 기법
- 비용 기반 옵티마이저를 사용할때 선택이 가능, join 조건중 적어도 하나 이상의 equi-join이 존재하는 경우에 채택됨
mysql> explain select *
-> from t1
-> join t2 on t1.c1 = t2.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.02 sec)
- 동등 조인이 아닌 모든 추가 조건의 조인이 실행된 후 필터로 Hash join이 실행됩니다.
mysql> explain format=tree
-> select *
-> from t1
-> join t2
-> on (t1.c1 = t2.c1 and t1.c2 < t2.c2)
-> join t3 on (t2.c1 = t3.c1)
-> ;
+----------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------+
| -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1)
-> Table scan on t3 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 < t2.c2) (cost=0.7 rows=1)
-> Inner hash join (t2.c1 = t1.c1) (cost=0.7 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
세 개의 테이블을 JOIN하여 데이터를 조회하는 기능으로, 세 테이블 간의 관계를 모두 고려하여 데이터를 조회합니다.
사용 예시
다음은 고객 테이블 (customers), 주문 테이블 (orders), 제품 테이블 (products)을 사용하여 3중 조인을 하는 예시입니다.
- 고객 테이블: 고객 ID (customer_id), 고객 이름 (customer_name)
- 주문 테이블: 주문 ID (order_id), 고객 ID (customer_id), 제품 ID (product_id), 주문 수량 (quantity)
- 제품 테이블: 제품 ID (product_id), 제품 이름 (product_name), 제품 가격 (price)
내부 조인 (INNER JOIN)를 사용하여 각 고객의 모든 주문과 주문한 제품 정보를 조회하는 쿼리:
SELECT c.customer_name, o.order_id, o.quantity, p.product_name, p.price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;
왼쪽 외부 조인 (LEFT OUTER JOIN)을 사용하여 모든 고객과 각 고객의 주문 정보 (있으면)를 조회하는 쿼리:
SELECT c.customer_name, o.order_id, o.quantity, p.product_name, p.price
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
LEFT OUTER JOIN products p ON o.product_id = p.product_id;
Join 순서?
위 예시에서 고객 join 주문 이 먼저 합쳐지고 그 결과가 제품과 join 될까요? 그것은 옵티마이저에 의해 결정됩니다.
예를 들어, 4개의 테이블(A,B,C,D)을 조인한다고 하면 옵티마이저는 다음과 같이 순차적으로 조인을 수행합니다.
- A와 B를 조인
- 1번의 결과 집합을 C와 조인
- 2번의 결과를 D와 조인
이때 테이블의 조인 순서는 내부적으로 옵티마이저에 의해 결정되므로 위의 조인 순서는 그저 예시라고 생각해주세요.
- 쿼리 실행시 임시 테이블 생성 필요 여부 파악
- MySQL 옵티마이저는 쿼리 실행을 위해 임시 테이블이 필요하다고 판단합니다.
- group by, order by, distinct, union or union all, sub query, having , join 등 다양한 상황에서 임시 테이블이 필요할 수 있습니다.
- 메모리 임시 테이블 생성 시도
- 먼저 메모리에 임시 테이블을 생성하려고 시도합니다.
- Memory (8.0 이전) → TempTable (8.0 이후)
- 메모리 한도 초과 시 디스크 기반 임시 테이블 생성
- 만약 메모리 임시 테이블 크기가 tmp_table_size 또는 max_heap_table_size 를 초과하면 MySQL 은 디스크 기반 임시 테이블로 전환합니다.
- MyISAM (8.0 이전) → InnoDB (8.0 이후)