(MySQL) JOIN 실행계획 - Driving table과 rows로 보는 JOIN 성능

1. 문제 상황

 

JOIN 쿼리 실행계획을 확인하던 중 한 가지 이상한 점을 발견했다. 주문 조회 쿼리였기 때문에 orders가 먼저 접근될 것으로 예상했지만, 실행계획에서는 order_items가 먼저 나타났다. 처음에는 JOIN 순서가 달라도 결과는 동일하므로 큰 차이가 없다고 생각했다.

하지만 Nested Loop Join 구조를 이해하면서, 어떤 테이블이 먼저 읽히는지는 JOIN 성능에 큰 영향을 줄 수 있음을 알게 되었다.

 

---

 

2. 테스트 환경

 

본 글에서 사용한 테이블 구조는 다음과 같다.

CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  merchant_id BIGINT NOT NULL,
  order_status VARCHAR(20) NOT NULL,
  ordered_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  qty INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

 

orders는 PK(id)만 존재하며, order_items 역시 PK(id)만 존재하는 상태다. 즉, JOIN에 사용되는 order_items.order_id에는 별도의 인덱스가 없는 상태다.

이는 JOIN lookup 비용 차이를 확인하기 위한 의도적인 설정이다.

 

데이터 규모는 다음과 같다.

SELECT COUNT(*) FROM orders;       -- 100,000
SELECT COUNT(*) FROM order_items;  -- 500,000

 

---

 

3. 문제의 쿼리

SELECT
  o.id,
  o.merchant_id,
  o.order_status,
  o.ordered_at,
  i.product_id,
  i.qty
FROM order_items i
JOIN orders o ON o.id = i.order_id
WHERE o.merchant_id = 7
  AND o.order_status = 'PAID'
ORDER BY o.ordered_at DESC
LIMIT 50;

 

이 쿼리의 의도는 단순하다. 특정 merchant의 최근 주문 50건과 해당 주문의 item 정보를 조회하는 것이다.

직관적으로 생각한다면

1. orders 테이블에서 조건 필터링
2. orders 테이블에서 최근 50건 추출
3. 해당 orders.order_id 기준으로 order_items 조회

이러한 실행 흐름이 기대될 것이다.

하지만 실행계획은 내가 처음에 생각한 것과 다른 결과를 보여줬다.

 

---

4. 실행계획 확인

 

여기서 가장 먼저 확인해야 할 것은 테이블 순서다.

실행계획에서는 order_items가 먼저 나타나고 있다.

---

5. Nested Loop Join

 

MySQL의 일반적인 JOIN 방식은 Nested Loop Join이다. Nested Loop Join은 단순하게 보면 다음과 같은 구조다.

1. 먼저 읽히는 테이블을 순차적으로 탐색하고
2. 각 row마다 다음 테이블에서 매칭되는 데이터를 찾아 JOIN을 수행한다.

즉, 첫 번째 테이블을 한 건씩 읽어가면서, 각 row마다 이후 테이블 lookup이 반복되는 형태로 JOIN이 수행된다.

 

간단히 생각하면 중첩 for문과 같은 원리라고 볼 수 있다. 따라서 첫 번째 테이블의 row 수만큼 내부 테이블 lookup이 반복되며, 이때 lookup 비용이 크다면 전체 JOIN 비용 역시 급격히 증가할 수 있다.

 

---

6. Driving table

 

Nested Loop Join을 처음 접하면 JOIN 비용을 단순히 두 테이블 크기의 곱(N×M)으로 생각하기 쉽다. 즉, 어떤 테이블을 먼저 읽든 결과적으로 조인은 동일하게 수행되므로 JOIN 순서는 큰 의미가 없다고 느낄 수 있다.

 

하지만 실제 JOIN 수행 과정에서는 단순히 두 테이블의 row 수를 곱하는 방식으로 비용이 결정되지 않는다.

 

Nested Loop Join에서는 먼저 읽히는 테이블의 각 row마다 이후 테이블에서 조건에 맞는 데이터를 찾아야 하며, 이때 해당 데이터를 얼마나 쉽게 찾을 수 있는지가 JOIN 비용에 큰 영향을 준다.

 

예를 들어 내부 테이블에 인덱스가 존재한다면 빠르게 매칭 데이터를 찾을 수 있지만, 인덱스가 없다면 매번 테이블을 탐색해야 할 수도 있다.

 

즉, JOIN 비용은 단순한 N×M 곱셈이 아니라, 데이터를 찾는 비용이 얼마나 반복되는지의 관점에서 이해하는 것이 더 적절하다.

 

이러한 Nested Loop Join에서 먼저 읽히는 테이블을 Driving table이라고 한다.

Driving table이라는 용어는 이후 JOIN 수행 흐름을 ‘주도(drive)’하는 테이블이라는 의미를 가지며, Driving table을 한 건씩 읽어가면서 이후 테이블 lookup이 수행되는 구조로 JOIN이 진행된다.

 

따라서 Driving table의 row 수는 내부 테이블 접근 횟수와 직결되며, 어떤 테이블이 Driving table로 선택되는지는 JOIN 성능에 큰 영향을 줄 수 있다.

 

---

7. JOIN 실행계획에서 테이블 순서의 의미

 

앞서 살펴본 것처럼 Nested Loop Join에서는 먼저 읽히는 Driving table을 기준으로 이후 테이블 lookup이 반복된다. 이때 MySQL 실행계획에 표시되는 테이블 순서는 바로 이러한 JOIN 수행 순서를 그대로 나타낸다.

 

즉, 실행계획에서 먼저 나타나는 테이블이 Driving table이며 이후 테이블은 해당 Driving table row마다 lookup 되는 구조로 JOIN이 수행된다.

 

현재 실행계획에서는 order_items가 먼저 나타나므로 order_items가 Driving table로 선택되었음을 알 수 있다. 이는 약 50만 건의 order_items row 각각에 대해 orders lookup이 수행되는 Nested Loop 구조임을 의미한다.

 

---

8. 왜 order_items가 Driving table이 되었을까?

 

현재 스키마는 다음과 같은 구조로 되어있다.

• orders.id -> PK (항상 인덱스 데이터 존재)
• order_items.order_id -> 인덱스 없음

 

따라서 Nested Loop 구조에 이를 적용하면 다음과 같다.

 

order_items -> orders

• orders.id PK를 lookup
• 인덱스 데이터가 있기 때문에 조회가 매우 빠름

orders -> order_items

• order_items.order_id 에 인덱스가 없음
• 내부 풀스캔 가능성 존재
  
  

옵티마이저는 이러한 lookup 비용 차이를 고려하여 order_items를 Driving table로 선택한 것으로 볼 수 있다.

 

---

9. rows의 의미

 

Driving table을 확인했다면 다음으로 볼 것은 rows 컬럼이다. 실행계획의 rows는 해당 테이블 접근이 몇 번 반복될 것인지에 대한 추정치를 의미한다.

 

현재 실행계획에서는 order_items의 rows가 498,780 으로 나와있는데 약 50만 번의 Nested Loop가 수행될 것으로 예상된다는 의미이다.

 

---

10. Using temporary / Using filesort

 

order_items의 Extra 컬럼도 눈에 띈다. Using temporary; Using filesort 가 나와있는데 이는 order by를 인덱스로 처리하지 못했음을 의미한다.

 

결과적으로 임시 테이블이 생성되고 filesort가 발생하므로 현재 쿼리는

풀스캔 + 임시 테이블 + filesort

 

비용 요소가 동시에 발생하고 있다.

 

---

11. 인덱스 설계

 

쿼리 의도를 다시 보면 다음과 같다.

1. orders 테이블에서 조건 필터링
2. orders 테이블에서 최근 50건 추출
3. 해당 orders.order_id 기준으로 order_items 조회

즉, 먼저 데이터를 줄이고 이후에 join하는 구조가 바람직하다.

 

이를 반영한 인덱스는 다음과 같다.

CREATE INDEX idx_orders_m_status_ordered
ON orders (merchant_id, order_status, ordered_at, id);

CREATE INDEX idx_order_items_order_id
ON order_items (order_id);

 

---

12. 실행계획 재확인

 

동일 쿼리로 다시 EXPLAIN을 수행하면 다음 변화가 나타나는 것을 볼 수 있다.

 

• driving table이 order_items에서 orders로 변경
• driving table의 rows가 498,780 에서 50,002 로 급감
• key에 인덱스 사용
• type이 ALL에서 ref로 개선
• Using temporary; Using filesort 제거
• order by desc 인덱스 처리 (Backward index scan – 인덱스 역방향 읽기)

즉, 옵티마이저가 먼저 필터링 후 JOIN 전략을 선택하게 된다.

 

---

출처 : https://monkeybusiness.tistory.com/944 https://schatz37.tistory.com/2

---