JOIN은 정말 느린가 (역정규화)

Q. 역정규화를 수행하는 이유가 무엇인가요?
A. 테이블이 쪼개져 조인 연산이 쿼리 내 포함되면 성능이 안좋아지기 때문에, 조회 성능을 개선하기 위해서 역정규화를 고려할 수 있습니다.

최근 면접 스터디 중, 역정규화에 대해 이야기를 하다가 나온 문답이다. 질문은 내가, 답변은 스터디원이 해주셨는데 만약 서로 입장이 바뀌었어도 똑같이 대답했을 것 같다. 

Database를 공부하면서, 혹은 개발을 진행하면서 통념처럼 받아들이게 되는 말이 바로 조인은 느리다.이다.

그런데 최근에 들어서는 Database에 대해 더 깊게 공부할 일들이 생기면서 이에 의문이 생겼다. 

조인이 만약 진짜 느린거라면, 왜 그런건데?

보통 이렇게 까지 물어보면 테이블 여러개를 봐야하니까.라고 답변이 돌아오는데 이런 대답은 내가 원하는 대답이 아니였다. 조금 더 기술적이고 명확한 답변을 원했다. 그렇다고 나 스스로에게 이를 물어보면 명백하게 답변을 할 수 있느냐? 아닌 것 같았다. 그래서 나 스스로라도 이에 대한 명확한 대답을 할 수 있어지기 위해 이 포스트를 작성한다.

 

조인은 느리다에 대한 추론

DB가 조인 문을 만났을 때 작동하는 방식으로는 NL 조인, 소트 머지 조인, 해시 조인 등이 존재한다. 세부적인 작동 방식은 선행(Driving), 후행(Driven) 각각 테이블 내 활용할만한 인덱스의 존재 여부에 따라 달라지겠지만, 이들은 모두 선행 테이블에서 필요한 데이터만을 뽑아낸 다음 이를 토대로 후행 테이블에 접근한다는 큰 그림은 동일하다. 

여기서 한가지 추론이 가능해지는데, 단계를 나눠서 진행하기 때문에 필연적으로 논리적 IO가 많아질 것이라는 추론이다. 

다만 한가지 의문이 드는 것은 일대다 관계를 갖는 테이블을 역정규화하게 되면 일 쪽의 데이터가 다 쪽의 테이블에 패딩되는 형태로 테이블 구성이 이루어지는데 이러한 상황에서는 IO 횟수 증가에 대한 부담보다는 IO를 통해 가져오는 데이터의 용량이 더 부담이 되지 않을까 하는 것이다.

이들을 직접 확인하기 위해, 실제 DB에 둘로 나눈 테이블에 대한 조인 쿼리 성능과 이를 역정규화한 조회 쿼리 성능을 비교해보고자 한다.

 

테스트 환경 구성

테스트 환경을 구성하기 위해 아래 두 테이블을 생성해주었다. 그 후, 각각 5만개, 30만개의 더미 데이터를 채워넣어주었다.

-- post 테이블
CREATE TABLE post (
  id INT auto_increment,
  user_id INT not null,
  name VARCHAR(20) not null,
  content VARCHAR(300) not null,
  created_at TIMESTAMP not null,
  PRIMARY KEY (id)
);

-- comment 테이블
CREATE TABLE comment (
  id INT auto_increment,
  post_id INT not null,
  content VARCHAR(100) not null,
  created_at TIMESTAMP not null,
  PRIMARY KEY (id),
  CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCE post(id)
);

그리고 역정규화한 경우의 수를 위해 두 테이블을 INNER 조인한 레코드를 그대로 테이블에 적재하는 쿼리도 생성해주었다.

CREATE TABLE post_comment (
  id INT auto_increment NOT NULL,
  PRIMARY KEY(id)) 
  SELECT 
    p.id as post_id,
    p.user_id as user_id,
    p.name as post_name,
    p.content as post_content,
    p.created_at as post_created_at,
    c.content as comment_content,
    c.created_at as comment_created_at
  FROM post p, comment c WHERE c.post_id = p.id;

 

테스트

두 테이블을 조인해서 데이터를 조회하는 방식과 해당 테이블들을 미리 역정규화 해둔 테이블에서 데이터를 조회하는 방식의 성능을 비교하기 위해 아래의 케이스를 테스트해보겠다.

1. 드라이빙 - 드리븐 테이블에 대한 인덱스 스캔 VS 역정규화 테이블에 대한 인덱스 스캔
2. 드라이빙 - 드리븐 테이블에 대한 (테이블 풀스캔 - 인덱스 스캔) VS 역정규화 테이블에 대한 테이블 풀스캔
3. 드라이빙 - 드리븐 테이블에 대한 테이블 풀스캔 VS 역정규화 테이블에 대한 테이블 풀스캔

각 테스트 케이스에 대해서는 범위 탐색 조건을 줄이거나 늘려서 타겟 데이터가 다수인 경우와 소수인 경우를 나눠서 비교하려고 한다. 이 때 다수의 기준은 전체 데이터의 약 30%이고, 소수의 기준은 전체 데이터의 약 5%로 상정하겠다.

기준이 되는 지점을 미리 확인하기 위해 가장 적절해보이는 칼럼으로 post_created_at을 선정했고 이를 기준으로 post_comment 테이블에서 상위 5%, 30% 지점을 확인하니 각각 2023-12-24 14:14:34 과 2022-10-18 16:44:59 으로 나타났다. 해당 값들을 이제 아래 쿼리의 일시에 넣어보면서 성능을 비교하려 한다.

-- 조인 쿼리
SELECT * FROM post p, comment c WHERE c.post_id = p.id AND WHERE p.created_at > '{일시}';

-- 역정규화 테이블 쿼리
SELECT * FROM post_comment WHERE post_created_at > '{일시}';

1. 드라이빙 - 드리븐 테이블에 대한 인덱스 스캔 VS 역정규화 테이블에 대한 인덱스 스캔

테이블 조회 시 조건식으로 활용되는 칼럼에 모두 적절한 인덱스가 포함된 경우이다. 

이를 위해 post.created_at 칼럼과 comment.post_id 칼럼, 그리고 post_comment.post_created_at 칼럼에 각각 인덱스를 생성해주었다.

CREATE INDEX idx_created_at ON post(created_at);

CREATE INDEX idx_post_id ON comment(post_id);

CREATE INDEX idx_post_created_at ON post_comment(post_created_at);

다수 데이터

앞에서 언급한 두 쿼리를 전체 데이터의 30%만 뽑아내는 시점 값을 통해 실행한 실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 쿼리 결과

결과를 요약하자면 조인 쿼리의 비용이 역정규화 테이블 쿼리보다 2배 정도 높게 결과가 나오고 있다.

이를 해석해보면, 조인 쿼리의 경우엔 post가 드라이빙 테이블로 활용되고 있고, 이 때 조건식에 의해 5만개의 테이블 레코드 중 1.5만개의 테이블 레코드가 타겟으로 산정된 것으로 보인다. 이후 NL 조인에 의해 1.5만번의 인덱스 스캔이 발생했고, 해당 지점에서 대부분의 부하가 몰려있다.

역정규화 테이블 쿼리의 경우엔, 조건식을 가지고 곧바로 테이블 풀스캔이 작동했다. 이 또한 상당히 부하가 있는 작업이지만, 인덱스 스캔이 1.5만번 발생한 위 쿼리보다는 효율적인 방식이 되었다.

또한 한가지 특이점이라면, 조건식 인덱스 활용이 가능하도록 인덱스를 생성해두었지만 결과 집합의 데이터가 워낙 많다보니 싱글 블록 IO 기반 랜덤 엑세스보다는 멀티 블록 IO 기반의 시퀀셜 엑세스를 사용하도록 옵티마이저가 판단한 것이다.

소수 데이터

앞에서 언급한 두 쿼리를 전체 데이터의 5%만 뽑아내는 시점 값을 통해 실행한 실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 쿼리 결과

결과를 요약하자면 조인 쿼리의 결과가 미세하게나마 더 비용이 적게 나오고 있다. 

이번에는 결과 집합이 적은 규모이기 때문에 옵티마이저가 생성되어 있는 인덱스를 모두 활용했고, NL 조인은 인덱스 스캔을 단 249번만 수행하여 성능이 좋게나왔다.

역정규화 테이블 조회 쿼리 또한 인덱스를 정상적으로 활용하여 성능이 잘 나왔지만 미세하게나마 비용은 더 높은데, 사실 차이가 크지 않아서 사실상 성능이 동일하다고 봐도 될 것 같다. 하지만 조인은 항상 느리다 라는 서술이 틀릴 때도 있다는 것을 보여주는 사례라고 볼 수 있을 것 같다.

2. 드라이빙 - 드리븐 테이블에 대한 (테이블 풀스캔 - 인덱스 스캔) VS 역정규화 테이블에 대한 테이블 풀스캔

테이블 조회 시 조건식에 포함되는 칼럼들에 대한 인덱스는 존재하지 않고, 다만 원활한 조인을 위한 조인 칼럼에 인덱스만 존재하는 상황이다. 

이를 위해 아래처럼 인덱스를 생성해주었다.

CREATE INDEX idx_post_id ON comment(post_id);

다수 데이터

앞서 언급한 두 쿼리의 실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 쿼리 결과

결과를 요약하자면 조인 쿼리가 역정규화 테이블 조회 쿼리보다 약 30% 더 비싼 것으로 나타난다. 

사실 해당 결과는 이미 위에서 완전히 같은 실행 계획을 분석했기 때문에 이에 대한 상세한 언급은 건너뛰겠다.

소수 데이터

위에서 언급한 두 쿼리에 대한 실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 쿼리 결과

결과를 보면 여전히 다수 데이터와 마찬가지로 여전히 아래 쿼리가 비용이 더 낮게 나오는 것을 볼 수 있다.

하지만 여기에는 맹점이 존재한다. 바로 실행 계획 상에서 옵티마이저가 예측한 rows 값이다. 두 쿼리 모두 30만건의 데이터 중 1500개의 데이터만을 뽑아내는 쿼리임에도 불구하고 옵티마이저는 내부적으로 해당 쿼리들이 10만개 수준의 데이터를 뽑아내는 대규모 데이터 출력 쿼리로 인식한 것이다. 

사실 조건식으로 사용한 칼럼의 인덱스 유무로 인해 같은 실행 계획임에도 코스트가 상이하게 나오는 현상이 테스트 도중 발견되어서(위 글의 1 -> 다수데이터, 2 -> 다수데이터 참고) 왜 그런지 계속 의문이였는데, 여기까지 오고나니 특정 조건으로 필터링될 데이터의 양에 대한 예측치를 산정할 때 인덱스가 존재할 경우 더 정밀한 예측을 수행하는 것으로 생각된다. (이에 대한 확인은 다른 포스팅에서 수행하겠음)

따라서 이는 옵티마이저가 완전히 잘못된 최적화를 하고 있는 것으로 보여지고, 이는 실제 실행 시간을 보면 확연히 나타난다. 

• 조인 쿼리: 72ms
• 역정규화 쿼리: 179ms

따라서 해당 경우의 수는 조인 쿼리가 성능이 더 좋다고 해석하는게 맞다고 보인다. 또한 이러한 관점에서 봤을 때, 같은 테스트 환경에서 다수 데이터 상황일 때도 둘의 성능은 조인 쿼리가 더 나쁘다기보다는 거의 차이가 없다고 해석하는게 맞는 것으로 보인다. (실제 실행 시간 단 2ms 차이)

3. 드라이빙 - 드리븐 테이블에 대한 테이블 풀스캔 VS 역정규화 테이블에 대한 테이블 풀스캔

만약 해당 경우의 수처럼 조인 칼럼 인덱스가 존재하지 않는 상황은 사실 OLTP 환경에서는 권장할 수 없는 상황이지만, 그럼에도 불구하고 최악의 경우의 수를 확인하기 위해 테스트를 진행하겠다.

이를 위해 모든 인덱스를 삭제해주었다.

다수 데이터

실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 결과

위 실행 계획에서 위의 Table scan on c.... 부분과 아래의 Table scan on post_comment.... 부분을 비교하면 역정규화 테이블의 데이터 패딩이 실제 실행 계획에 어떻게 영향을 미치는지 확인이 가능해 보인다. 이들만 따로 떼내 조금 더 확인해보겠다.

위: comment 테이블 전체 조회, 아래: post_comment 테이블 전체 조회

해당 실행 계획을 봤을 때 두 테이블의 예상 rows는 거의 비슷하지만 cost는 데이터 패딩이 발생하지 않은 comment 쪽이 더 낮은 것을 볼 수 있다. 이 결과만 놓고 봤을 땐 데이터 패딩이 cost 산정에 영향을 미치고, 이 것이 성능 상 영향을 미칠 수 있음을 의미하는 것으로 보이지만 그렇게 큰 영향을 주지는 않는 것으로 보인다.

다시 위 실행 계획으로 돌아가서, 이번엔 조인 쿼리 쪽의 실행 계획이 이전과는 달라진 것을 알 수 있다. 이전에는 post 테이블을 드라이빙 테이블로 뒀는데, 이번에는 comment 테이블을 드라이빙 테이블로 뒀다. 이렇게 바뀐 이유를 유추해보자면, 아무래도 조인 시 어떻게든 인덱스를 활용하려했기 때문으로 보인다. 만약 post가 그대로 드라이빙 테이블인채로 NL 조인을 실행한다면, post 테이블에서 테이블 풀스캔을 통해 찾아낸 레코드 한건 한건에 대해 각각 테이블 풀스캔이 발생했을 것이다. 조건식으로 post 테이블에서 절반정도 필터링 되는 것으로 확인했으니 테이블 풀스캔이 2.5만건 발생하게 되는 것이다.

실제로 조인 순서를 고정했을 때 어떻게 실행 계획이 발생하는지 확인하기 위해 힌트를 주고 실행 계획을 확인했다.

post -> comment 조인 순서 고정 조인 쿼리 결과

결과를 보면 알 수 있듯이 옵티마이저는 post를 드라이빙 테이블로 둘 시 너무 큰 비효율이 발생하는 것을 우려하여 Hash 조인을 선택하는 것을 볼 수 있다. 

다만 어찌되었던 해당 경우는 하나의 테이블을 조회하는 것보다 나쁜 성능을 보인다는 것을 알 수 있다. 

소수 데이터

실행 계획은 아래와 같다.

조인 쿼리 결과

역정규화 테이블 조회 결과

이 경우엔 created_at 칼럼 기반의 인덱스를 활용하지 않기 때문에 목표 데이터를 찾기 위해서는 테이블 풀스캔의 방법 밖에 없다. 따라서 데이터의 양이 적어진다해도 실행 계획 상 비용이나 실제 실행 시간에 큰 차이가 존재하지 않는다.

이 경우 만약 조인 쿼리가 Hash 조인으로 실행된다면 어떤 성능을 발휘할까?

해시 조인 결과

이 경우엔 되려 전체 결과 집합을 출력하는데에는 역정규화 테이블을 풀스캔하는 것보다 빠르다. 다만 Hash 조인은 조인 시 사전 작업이 존재하여 첫번째 레코드를 반환할 때까지 상당히 오래걸리는 것을 볼 수 있다. 이는 부분범위 처리를 통한 성능 개선이 중요한 OLTP 환경에서는 선호되지 않는 방식이다.

결론

테스트를 통해서 도출할 수 있는 결론은 다음과 같다.

• 첫째, 잘 최적화된 조인 쿼리는 성능이 비슷하거나 오히려 근소하게 더 빠를 수 있다. 이 경우가 테스트 전 상정했던 디스크에서 읽어오는 총 데이터 양이 조인 쿼리 쪽이 더 적은 것으로 인해 발생하는 경우의 수라고 생각된다.(물론 영향은 그렇게 크지는 않은 것으로 보인다) 물론 이 경우엔, 가져오는 데이터의 양이 전체 테이블 크기에 비해 작아야한다.
• 둘째, 조인 시 드리븐 테이블에 조인 칼럼으로 인덱스가 존재하지 않을 경우, 상당한 비효율이 발생한다. 해당 경우엔 옵티마이저가 Hash 조인을 선택할 가능성도 있는데 이 또한 전체 데이터 집합을 반환하는데엔 더 좋은 성능을 보일지 몰라도 부분 범위 처리를 통한 성능 개선이 중요한 OLTP 환경에서는 선호되지 않는 방식으로 볼 수 있다.
• (추가) 셋째, 옵티마이저는 테이블 통계값을 관리하고 이를 이용하여 계산한 비용을 기반으로 실행 계획을 세운다. 이 때 특정 칼럼의 인덱스 유무에 따라 통계값의 정확도가 흐려질 수 있고 이에 따라 실제로는 더 비효율적인 실행 계획을 선택할 가능성이 존재한다.

즉, 정규화로 인한 조인 쿼리의 속도가 항상 더 느리다고는 할 수 없어 보인다. 다만 성능을 좋게하려면 드리븐 테이블에 조인 칼럼 인덱스가 거의 필수적으로 필요하고, 이 경우 DML 성능 저하나 추가적인 저장 공간이 필요하므로 정규화 시 추가적인 비용이 드는 것은 맞는 말일 것이다.