JOIN이 느린건 내 골반이 멈추지 않는 탓일까? ㅜ.ㅜ

ㅜ.ㅜ

JOIN, 꼭 필요한데... 왜 이렇게 느릴까?

서비스를 개발하면서 JOIN은 필수이다. 여러 테이블에 저장된 정보들을 이용해 의미 있는 데이터를 만들어내는 것은 JOIN 없이 불가능하다. 하지만 아무리 로직을 잘 짜도 JOIN이 들어가는 순간 쿼리가 느려지는 경우가 있다. 그리고 대부분은 비효율적인 JOIN 이 범인인 경우가 많다.

 

근데 항상 같은 방식으로 쿼리를 작성해도 어떤건 느리고 어떤건 빠르고 어떤건 효율적이고 어떤건 비효율적이고... 뭔가 문제일까?

 

단순한 INNER JOIN 이라도 작성한 쿼리를 데이터베이스가 항상 똑같은 방식으로 실행시키는 것은 아니다. DB 내부에는 옵티마이저(Optimizer)라는 똑똑한 친구가 있어서 주어진 쿼리를 가장 효율적으로 실행할 수 있는 최적의 방법을 찾아낸다.

 

오늘은 이 옵티마이저가 어떻게 최적의 조인 방법을 결정하는지, 그 실행 계획을 우리가 어떻게 볼 수 있는지 알아보자.

 

옵티마이저

옵티마이저는 우리가 작성한 SQL 쿼리를 분석하여 데이터를 가장 빠르고 효율적으로 가져올 수 있는 실행 계획을 수립하는 데이터베이스 엔진의 핵심 구성 요소이다.

옵티마이저는 항상 아래와 같은 질문들에 대한 최적의 답을 찾는다.

• 어떤 테이블을 먼저 읽을까? (조인 순서)
• 테이블을 읽을 때 인덱스를 사용할까, 아니면 처음부터 끝까지 다 읽을까? (접근 방식)
• 두 테이블을 합칠 때 어떤 알고리즘을 사용할까? (조인 알고리즘)

 

옵티마이저의 3가지 조인 알고리즘

 

옵티마이저가 테이블을 조인할 때 주로 사용하는 알고리즘은 대표적으로 세 가지이다.

1. 중첩 루프 조인 (Nested Loop Join, NLJ)
   한 테이블에서 행을 하나씩 읽으면서 다른 테이블에서 조건에 맞는 행을 찾는 가장 기본적이고 직관적인 방식. 작은 테이블이나 인덱스가 잘 되어 있을 때 유리
2. 정렬 병합 조인 (Sort Merge Join, SMJ)
   두 테이블을 조인할 컬럼 기준으로 각각 정렬한 뒤 정렬된 결과를 마치 지퍼처럼 합치면서 조인하는 방식. 대용량 테이블이나 등가(=) 조인이 아닐 때 고려된다.
3. 해시 조인 (Hash Join)
   한 테이블(보통 작은 쪽)로 메모리에 해시 테이블을 만들고 다른 테이블을 읽으면서 해시 테이블을 탐색하여 빠르게 짝을 찾는 방식. 등가(=) 조인이고 대용량 테이블일 때 유리

옵티마이저는 테이블의 크기, 인덱스 유무, 조인 조건, 사용 가능한 메모리 등 다양한 통계 정보를 바탕으로 이 세 가지 알고리즘 중 가장 비용이 적게 들 것으로 예상되는 방식을 선택한다.

 

그렇다면 옵티마이저가 어떤 결정을 내렸는지 우리는 어떻게 알 수 있을까? 바로 EXPLAIN 명령어이다. SQL 쿼리 앞에 이 키워드를 붙이면 DB는 실제로 쿼리를 실행하는 대신 어떤 계획으로 실행할 지 계획을 상세하게 보여준다.

이를 통해 옵티마이저는 어떤 상황에서 어떤 방식의 알고리즘을 선택하는지, 단순한 쿼리문에서도 왜 속도 차이가 발생했는지 알아보자.

---

테스트 환경은 다음과 같다.

• 유저 테이블과 주문 테이블이 있다.
• 유저 테이블에는 이름과 이메일

users

• 주문 테이블에는 유저id, 주문 날짜, 주문한 상품 이름 (테스트를 위해 약 6천개의 주문을 생성했다.)

orders

 

이제 users 테이블과 orders 테이블을 user_id로 조인한다고 해보자.

SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id

 

유저 id와 주문 테이블의 유저 id 는 1:N 관계 (유저 1명 / 여러 주문) 이다.

---

TEST 1-1 : 조인 컬럼에 인덱스가 없을 때

 

인덱스 생성 없이 그냥 이상태로 바로 EXPLAIN 쿼리를 실행해보면 다음과 같은 결과가 나온다.

해시 조인

이미지의 두 번째 행(테이블 o)을 보면 Extra 컬럼에 Using where; Using join buffer (hash join) 이라고 써있는걸 볼 수 있다.

 

왜 해시 조인이 선택되었을까?

1. users 테이블 (u): type: ALL 로 테이블 풀 스캔을 하며 모든 유저를 탐색하므로 rows가 3이 된다.
2. orders 테이블 (o): type: ALL 로 전체 상품 즉, rows는 6010이 된다.

옵티마이저는 양쪽 테이블 모두 전체 스캔이 필요한 상황에서 더 작은 users 테이블(3행)을 기준으로 메모리에 해시 테이블(Join Buffer)을 만들고 더 큰 orders 테이블(6010행)을 스캔하며 해시 탐색을 하는 해시 조인이 중첩 루프 조인(3행 * 6010행 스캔)보다 훨씬 비용이 적다고 판단한 것이다.

TEST 1-2 : 조인 컬럼에 인덱스가 있을 때

여기에 인덱스를 추가해보자.

CREATE INDEX idx_user_id ON orders (user_id);

 

orders의 user_id 컬럼에 인덱스를 생성해줬다 이렇게 되면 이 전에는 orders 테이블의 모든 행을 하나씩 확인했다면 이제는 user_id 인덱스를 통해 바로 해당 사용자의 행을 찾게된다.

 

다시 EXPLAIN을 실행시키며 아래와 같은 결과를 얻을 수 있다.

NLJ

 

users 테이블은 이전과 동일하다. 하지만 orders 테이블에는 변화가 있다.

• type: ref: 더 이상 풀 스캔을 하지 않는다. ref는 인덱스를 사용하여 조인 조건(u.id = o.user_id)에 맞는 데이터를 효율적으로 찾아온다는 의미이다.
• possible_keys: idx_user_id (사용 가능한 인덱스 후보)
• key: idx_user_id (실제로 옵티마이저가 선택한 인덱스!)
• ref: testdb.u.id (인덱스를 탐색할 때 users 테이블의 id 값을 사용한다는 의미)
• Extra: 이전과 달리 null

왜 바뀌었을까? (인덱스 기반 NLJ의 효율성)

인덱스 생성 전 옵티마이저는 users 테이블 전체 스캔 + orders 테이블 전체 스캔이 필요하다고 판단했다. 해시 조인이 비용이 적다고 계산한 것이다.

 

인덱스 생성 후 옵티마이저는 users 테이블은 어차피 다 읽어야 하지만 각 사용자에 대해 orders 테이블을 찾을 때 새로 만든 idx_user_id 인덱스를 사용할 수 있게 되었다.

 

이 부분에서 인덱스를 사용한 조회(type: ref)는 테이블 전체 스캔(type: ALL)보다 훨씬 비용이 저렴하다. 따라서 옵티마이저는 users 테이블을 읽고 → 각 user.id마다 orders 테이블의 인덱스를 탐색하는 방식 즉, 인덱스를 활용한 중첩 루프 조인(Index Nested Loop Join)이 해시 조인보다 더 효율적이라고 판단하여 실행 계획을 변경한 것이다.

 

결과적으로는 모든 사용자의 모든 주문 정보를 가져오는 것은 같지만 인덱스가 없을 때는 orders 테이블 전체를 3번 스캔해야 했던 반면 인덱스를 사용하면 각 사용자에 해당하는 주문만 효율적으로 찾아오므로 전체 작업량이 훨씬 줄어든다

---

그렇다면 처음부터 테이블 풀 스캔이 아닌 상황에서는 어떨지 궁금해졌다. 

같은 환경에서 WHERE 절을 사용해 조건을 부여하고 풀 스캔이 아닌 상황으로 테스트해보자.

TEST 2-1 : 조인 컬럼에 인덱스가 없을 때

SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

 

 

아까와 같은 쿼리문에서 WHERE 문으로 Alice의 주문만 보도록 했다.

 

인덱스가 없는 상황에서 EXPLAIN을 사용해보면 아래와 같은 결과가 나온다.

NLJ

인덱스가 없는 상황인데 해시 조인이 아닌 NLJ가 사용되었다.

 

아까랑 다르다!

이번엔 뭐가 다르길래?

users 테이블은 type : const 이다.

WHERE u.id = 1 조건이 기본키를 사용했기 때문에 단 한 번의 접근으로 정확히 1개의 행을 찾았다는 의미로 효율적으로 진행되었다.

orders 테이블은 type : ALL 이다. 풀 스캔을 사용하므로 비효율이 발생한 부분이다. possible_keys와 key가 모두 null 인데 이는 조인 컬럼인 user_id에 사용할 수 있는 인덱스가 없어 데이블 전체 (6000행 이상)을 풀 스캔 한다는 의미이다.

 

근데 왜 HASH JOIN은 사용되지 않았을까?

 

NLJ는 기본적으로 바깥 테이블을 기준으로 안쪽 테이블을 반복 탐색한다. 인덱스가 없다면 이 탐색 과정이 전체를 모두 탐색하기 때문에 풀 스캔이 된다.

옵티마이저는 비용을 계산한다. 바깥쪽 테이블의 결과가 1행 뿐이기 때문에 옵티마이저는 1행에 대해 orders 테이블 전체를 한 번 스캔하는 비용이 해시 조인을 준비하고 실행하는 비용보다 적다고 판단한 것이다.

Hash Join은 주로 인덱스가 없고 메모리가 충분하며, 동등(=) 조인일 때 사용됩니다. 하지만 옵티마이저는 항상 비용 기반으로 판단하므로 외부 결과가 극단적으로 작으면 Hash Join 준비 비용보다 비효율적인 NLJ가 더 싸다고 판단할 수도 있다.

TEST 2-2 : 조인 컬럼에 인덱스가 있을 때

이 상황에서 아까와 같은 인덱스를 다시 만들고 다시 EXPLAIN을 실행해보자.

NLJ

실행 계획을 보면 orders 테이블에 접근할 때 type: ref로  인덱스를 사용했음이 나타난다. 조인 방식으로는 NLJ가 선택된것은 아까와 같지만 2-2에서 발생한 비효율은 사라진 모습이다.

---

EXPLAIN을 통해 우리는 옵티마이저의 계획을 훔쳐볼 수 있다. 그리고 만약 그 계획이 비효율적이라고 판단된다면 우리는 옵티마이저가 더 좋은 계획을 세우도록 유도할 수 있다.

• 인덱스 추가/수정: 실행 계획을 보고 인덱스가 필요한 곳에 적절히 생성하거나 기존 인덱스를 더 효율적으로 변경
• 쿼리 재작성: 쿼리 구조를 변경하거나 JOIN 순서를 명시하는 등의 방법을 사용
• 통계 정보 업데이트: 옵티마이저는 테이블의 통계 정보를 기반으로 판단하므로 데이터가 크게 변경되었다면 통계 정보를 최신 상태로 업데이트

---

결론

데이터베이스 옵티마이저는 똑똑하고 편리하지만 만능은 아니다. EXPLAIN을 통해 옵티마이저가 어떤 실행 계획을 세웠는지 확인하면서 느린 SQL 쿼리의 성능을 개선하는 첫걸음을 시작해보자.

 

옵티마이저가 왜 그런 결정을 내렸는지 이해하고 인덱스 설계나 쿼리 수정을 통해 더 나은 실행 계획을 유도하는 능력을 키우는 것이 가장 중요한 능력이지 않을까 생각된다.

 

---

 

**MySQL에서 정렬 병합 조인(SMJ)은 사용되지 않나요?

 

마지막으로 궁금했던 점은 "왜 SMJ는 한 번도 등장하지 않았을까?" 였다.

MySQL 옵티마이저도 내부적으로 SMJ 알고리즘을 가지고는 있다. 하지만 실제 JOIN 연산 자체에서는 NLJ(특히 인덱스 활용 시)나 HJ가 대부분의 경우 더 효율적이라고 판단하기 때문에 잘 선택되지 않는다. PostgreSQL, Oracle과 같은 다른 데이터베이스에서는 특정 조건(대용량 데이터 정렬 필요, 비등가 조인 등)에서 SMJ가 더 활발하게 사용되지만 MySQL에서는 개발자가 JOIN 알고리즘으로 SMJ를 직접 선택하거나 강제할 수 있는 방법은 없다고 한다.

옵티마이저가 아주 특수한 경우 내부적으로 사용할 수는 있겠지만 일반적인 JOIN 최적화 과정에서는 NLJ와 HJ를 중심으로 이해하는 것이 현실적이다.