SHOOT 프로젝트를 하다가 JOIN에 대해 깨달은 사실이 두 가지 있다.
그래서 오늘은 JOIN이 어떻게 작동하는지 알아보고 효율적으로 사용하는 방법에 대해서 알아볼 것이다. 그리고 최종적으로 내 SHOOT프로젝트에 적용해보려고 한다.
두 개 이상의 테이블 사이의 관련 컬럼을 기준으로 로우를 결합하는 것
그런데 실제로 JOIN을 사용해보니 JOIN, LEFT JOIN을 가장 많이 사용했던 것 같다.
JOIN을 더 자세하게 알아보기 위해서는 데이터베이스에 작동원리를 조금 이해할 필요가 있어보인다. 그 중에서도 어떤식으로 JOIN을 할 지 결정해주는 옵티마이저라는 친구에 대해서 먼저 알아보자.
옵티마이저는 그대로 직역하면 최적화하는 친구. 멋진 놈이다.
옵티마이저는 자기만의 여러가지 규칙으로 실행을 어떻게 할지 판단한다.
JOIN은 다음과 같은 JOIN 기법에 따라 수행하게 되기 때문에 어떤 방법을 사용할지도 옵티마이저의 판단에 따른다.
위 3가지의 JOIN 기법을 가볍게 살펴보고 내 프로젝트에서는 어떻게 돌아가는지 한 번 살펴보도록 하자
RDB에서 공통적으로 사용하는 위 3가지 기법외에도 Hybrid 조인, Star조인 등이 존재하는데 주로 사용하는 postgreSQL에서는 위 3가지 방식만을 사용하는 것 같다.
한 테이블의 row를 기준으로 다른 테이블의 row를 순차적으로 결합하여 조인하는 방식
출처 : https://schatz37.tistory.com/2
이해하기 너무 좋은 것 같아서 다른 티스토리에서 가져왔다. 위 사진처럼 중첩 for문의 방식으로 JOIN을 수행한다.
순서는 다음과 같다.
매우 단순하다. 이를 속도측면으로 접근하는 것도 그리 어렵지 않다. 2번 작업에서 A테이블의 한 row를 기준으로 B테이블의 row들을 탐색하게 된다. 이 때 탐색속도가 빠르면 JOIN의 속도 또한 빨라진다. 또한 테이블에 데이터가 적으면 빨라진다.
-> 반대로 column이 인덱싱되어있지 않고 데이터수가 너무 많으면 다른 조인 기법을 사용한다.
그 외에 더 자세한 내용은 위 티스토리 블로그에 너무 잘 적혀있다.
조인할 두 테이블 중 하나의 테이블을 이용해 해시 테이블을 만들고 조인할 테이블에 대입하여 조인하는 방식
작동 순서는 다음과 같다.
뭐 크게 어려운 내용은 없다. 자료구조 시간에 배웠던 해시테이블이다. 해시테이블을 효율적으로 만드는 방법을 검색하면 이 방법이 효율적으로 돌아가기 위한 조건을 알 수 있다. 그러니 이 방법이 언제 효율적인지나 알아보자
정렬먼저하고 조인하는 방식
작동 순서는 다음과 같다.
마찬가지로 어려운 내용은 아니다. 언제 효율적인지 알아보자
자 그럼 위 조인 알고리즘에 대해서 알아봤으니 실제 프로젝트에서 사용했던 내 테이블들은 어떤 방식으로 조인되고 있는지 알아보고 어떻게 하면 더 효율적으로 쓸 수 있을지 까지 알아보자.
shooot.channel Table
Column | Type | Collation | Nullable | Default | Storage | Stats target |
---|---|---|---|---|---|---|
character varying(320) | not null | extended | ||||
pw | character(64) | not null | extended | |||
name | character varying(20) | not null | extended | |||
sex | smallint | not null | 1 | plain | ||
birth | date | not null | plain | |||
description | character varying(1000) | extended | ||||
profile_img | character(31) | extended | ||||
authority | smallint | not null | 0 | plain | ||
creation_time | timestamp without time zone | not null | CURRENT_TIMESTAMP | plain | ||
login_type | character varying(12) | not null | 'local'::character varying | extended | ||
subscribe_count | integer | not null | 0 | plain |
Indexes :
"email" PRIMARY KEY, btree (email)
가장 조인이 빈번하게 일어나는 채널 테이블이다.
shoot.post Table
Column | Type | Collation | Nullable | Default |
---|---|---|---|---|
post_idx | integer | not null | nextval('shoot.post_post_idx_seq'::regclass) | |
post_title | character varying(32) | not null | ||
post_type | smallint | not null | ||
post_video | character(26) | not null | ||
post_upload_time | timestamp without time zone | not null | CURRENT_TIMESTAMP | |
upload_channel_email | character varying(320) | not null | ||
post_description | character varying(1024) | |||
post_thumbnail | character(30) | |||
post_good_count | integer | not null | 0 | |
delete_time | timestamp without time zone | |||
comment_count | integer | not null | 0 | |
post_view_count | integer | not null | 0 | |
category_idx | integer | not null | '-1'::integer |
프로젝트에서 사용했던 두 테이블을 다양항 상황에서 JOIN을 해보도록 하자
SELECT * FROM shoot.post JOIN shoot.channel ON shoot.post.upload_channel_email = shoot.channel.email
sql은 위의 적힌대로 사용해본다.
외래키 부여
Foreign-key constraints:
"upload_channel_email" FOREIGN KEY (upload_channel_email) REFERENCES shoot.channel(email) ON DELETE CASCADE
채널 테이블의 p key를 게시글 테이블의 upload_channel_email 컬럼의 f key로 등록해주었다. 이제 위의 쿼리를 EXPLAIN함수를 통해 어떻게 진행되었는지 보도록 하자
결과
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=1.04..3.23 rows=10 width=1693)
Hash Cond: ((post.upload_channel_email)::text = (channel.email)::text)
-> Seq Scan on post (cost=0.00..2.10 rows=10 width=153)
-> Hash (cost=1.02..1.02 rows=2 width=1540)
-> Seq Scan on channel (cost=0.00..1.02 rows=2 width=1540)
외래키 해제
ALTER TABLE shoot.post DROP CONSTRAINT upload_channel_email;
위의 sql을 실행하여 외래키 제약조건을 풀어주고 테스트 하였다.
결과
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=1.04..3.23 rows=10 width=1693)
Hash Cond: ((post.upload_channel_email)::text = (channel.email)::text)
-> Seq Scan on post (cost=0.00..2.10 rows=10 width=153)
-> Hash (cost=1.02..1.02 rows=2 width=1540)
-> Seq Scan on channel (cost=0.00..1.02 rows=2 width=1540)
결과는 똑같이 나왔다. 지금 상황에서는 외래키 제약조건이 있고 없고는 차이가 없나보다
외래키 부여 + 인덱스 부여
ALTER TABLE shoot.post ADD CONSTRAINT upload_channel_email FOREIGN KEY (upload_channel_email) REFERENCES shoot.channel (email) ON DELETE CASCADE;
CREATE INDEX btree_test ON shoot.post USING btree (upload_channel_email);
외래키도 다시 부여해주고 인덱싱도 b tree로 시켜줘보자
결과
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=1.04..3.25 rows=11 width=1693)
Hash Cond: ((post.upload_channel_email)::text = (channel.email)::text)
-> Seq Scan on post (cost=0.00..2.11 rows=11 width=153)
-> Hash (cost=1.02..1.02 rows=2 width=1540)
-> Seq Scan on channel (cost=0.00..1.02 rows=2 width=1540)
왜 계속 똑같은 결과가 나올까...
ALTER TABLE shoot.post DROP CONSTRAINT upload_channel_email;
외래키를 다시 해제하고 테스트해보자 QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=1.04..3.25 rows=11 width=1693)
Hash Cond: ((post.upload_channel_email)::text = (channel.email)::text)
-> Seq Scan on post (cost=0.00..2.11 rows=11 width=153)
-> Hash (cost=1.02..1.02 rows=2 width=1540)
-> Seq Scan on channel (cost=0.00..1.02 rows=2 width=1540)
우리 옵티마이저 선생님이 외래키인지 뭔지 인덱싱이 됐는지 안됐는지는 지금 관심이 없어보인다.효율적인 방법 알아보고 싶었는데 옵티마이저 선생님은 지금 내가 생각한 원인들에 크게 관심이 없어보인다.
JOIN이 어떤식으로 작동하는지 알아보고 JOIN을 더 효율적으로 사용하는 방법을 알아보려고 했다. JOIN을 효율적으로 사용하기 위해 옵티마이저가 잘 선택할 수 있도록 그리고 그 선택한 방법이 효율적으로 돌아갈 수 있도록 하는 것이 중요하다고 생각했다.
그래서 F key와 column 인덱스에 집중하여 테스트를 해봤다. 그런데 옵티마이저가 항상 같은 선택을 한다. 그래서 옵티마이저가 같은 선택을 한 이유는 다음과 같은 경우가 있다고 생각한다.
그래서 다음에는 옵티마이저에 대해서 더 깊게 알아보려고 한다. 그리고 오늘 옵티마이저가 똑같은 결과를 도출한 이유에 대해서도 알아보려고 한다.