[Real MySQL 8.0] MySQL에서 정렬 처리 방식과 드라이빙 테이블 선택 전략

연유뿌린빙수 2025. 12. 30. 17:05

DB 설계 시 테이블 간의 관계를 정의할 때, 흔히 부모-자식 테이블 구조에 집중하게 된다. 하지만 쿼리 성능 최적화 관점에서 보면, 정렬 컬럼이 어느 테이블에 위치하는지가 드라이빙 테이블 선택의 핵심 기준이 된다.

MySQL의 세 가지 정렬 처리 방식

MySQL은 ORDER BY 절을 처리할 때 다음 세 가지 방식 중 하나를 선택한다. 성능은 위에서 아래로 갈수록 저하된다.

인덱스 기반 정렬: 별도의 정렬 작업 없이 인덱스 순서대로 데이터를 읽는다.
드라이빙 테이블만 정렬 (Using filesort): 조인 전에 드라이빙 테이블만 먼저 정렬한다.
조인 후 전체 정렬 (Using temporary; Using filesort): 조인 결과 전체를 임시 테이블에 저장한 뒤 정렬한다.

만약 실행 계획을 출력했는데 Using temporary가 보인다면 3번 방식이 적용된 것이며, 이는 대부분의 경우 피해야 할 최악의 시나리오다.

예제를 통해 알아보기

다음은 Real MySQL 8.0에서 수록된 예시들이다.

다음 상황 들을 가정해보자.

tb_test1: 100건
tb_test2: 1000건(tb_test1 1건당 tb_test2 10건이 매칭)
두 테이블의 조인 결과는 전체 1000건

즉, tb_test1이 tb_test2의 부모 테이블인 상황이다.

실행하려는 쿼리는 다음과 같다.

SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1 = t2.col1
ORDER BY t1.col2
LIMIT 10;

우선은 드라이빙 테이블이 무엇인지에 따라서 어떤 성능 차이를 보이는지 확인해보자.

1) tb_test1이 드라이빙 테이블일 때

정렬 방법	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	tb_test1: 1건, tb_test2: 10건	1번	0건
조인의 드라이빙 테이블만 정렬	tb_test1: 100건, tb_test2: 10건	1번	100건(tb_test1 테이블의 레코드 건수 만큼 정렬 필요)
임시 테이블 사용 후 정렬	tb_test1: 100건, tb_test2: 1000건	100번(tb_test1 테이블의 레코드 건수만큼 조인 발생)	1000건(조인된 결과 레코드 건수를 전부 정렬해야함)

2) tb_test2이 드라이빙 테이블일 때

정렬 방법	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	tb_test2: 10건, tb_test1: 10건	10번	0건
조인의 드라이빙 테이블만 정렬	tb_test2: 1000건, tb_test1: 10건	10번	1000건(tb_test2 테이블의 레코드 건수 만큼 정렬 필요)
임시 테이블 사용 후 정렬	tb_test2: 1000건, tb_test1: 100건	1000번(tb_test2 테이블의 레코드 건수만큼 조인 발생)	1000건(조인된 결과 레코드 건수를 전부 정렬해야함)

여기서 주목해야하는 것은 정렬 대상이 어느 테이블에 있냐이다.

정렬 컬럼 col2가 tb_test1에 있으므로, tb_test1을 드라이빙 테이블로 선택해야 정렬 비용을 최소화할 수 있다.

즉, 정리하자면 다음과 같다.

MySQL에서의 정렬 처리 : 드라이빙 테이블의 선택에서 부모/자식이 중요한 게 아니라 “정렬 컬럼 위치”가 핵심이다

보다 더 구체적인 사례들로 비교를 해보자.

고객 테이블(10000명을 대상으로하기)

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    grade VARCHAR(20),        -- 'BRONZE', 'SILVER', 'GOLD', 'VIP'
    created_at DATETIME,
    INDEX idx_grade (grade)
);

주문 테이블(고객 당 50건 정도의 주문이 있다고 가정)

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATETIME,
    total_amount DECIMAL(12,2),
    status VARCHAR(20),
    INDEX idx_customer (customer_id),
    INDEX idx_order_date (order_date)
);

이러한 테이블이 주어졌을 때 조회하려는 것이 회원 등급이 VIP 고객들의 주문 내역을 “최근 주문 순으로 조회” 인 경우에 대하여 확인해보자.

그러면 쿼리문을 다음과 같이 작성한다.

SELECT c.name, c.email, o.order_date, o.total_amount, o.status
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.grade = 'VIP'
ORDER BY o.order_date DESC
LIMIT 100;

드라이빙 테이블이 무엇인지에 따라 실행계획을 확인해보자

실행계획1) customers을 드라이빙 테이블로 선택

EXPLAIN SELECT c.name, c.email, o.order_date, o.total_amount, o.status
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.grade = 'VIP'
ORDER BY o.order_date DESC
LIMIT 100;

그러면 실행계획이 다음과 같이 출력된다.

+----+----------+------+---------------+------+---------------------------------+
| id | table    | type | key           | rows | Extra                           |
+----+----------+------+---------------+------+---------------------------------+
|  1 | c        | ref  | idx_grade     | 500  | Using temporary; Using filesort |
|  1 | o        | ref  | idx_customer  | 50   | NULL                            |
+----+----------+------+---------------+------+---------------------------------+

using temporary 출력으로 보아 좋지 않은 성능으로 정렬됨을 확인할 수 있다.
어떤 과정으로 실행되는지 확인해보자.

만약 customers가 드라이빙 테이블이라면, customers에서 grade 컬럼을 바탕으로 VIP 고객을 필터링한다.(WHERE c.grade ='VIP')
→ VIP 고객 마다의 orders를 조인
→ 조인 결과를 임시 테이블 에 저장(using temporary)
→ 그럼 이 임시테이블을 order_date로 정렬해서 조인 테이블을 전체 정렬 하는 것으로 책에서 나온 최악의 경우임
→ 그리고 이 중에서 100건만 반환

실행계획2) orders(자식 테이블)을 드라이빙 테이블로 선택

SELECT /*+ JOIN_ORDER(o, c) */ 
    c.name, c.email, o.order_date, o.total_amount, o.status
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.grade = 'VIP'
ORDER BY o.order_date DESC
LIMIT 100;

order_date를 기준으로 정렬을 하고 싶고, order_date가 orders에 있기 때문에 orders를 드라이빙 테이블로 두고 적절한 인덱스를 추가하면 좋은 성능을 보일 수 있다.

필요한 인덱스를 새로 추가한다.

ALTER TABLE orders ADD INDEX idx_order_date_customer (order_date DESC, customer_id);

이러면 드라이빙 테이블에서 인덱스를 사용하고 끝내버리면 된다.

SELECT /*+ JOIN_ORDER(o, c) */ 
    c.name, c.email, o.order_date, o.total_amount, o.status
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.grade = 'VIP'
ORDER BY o.order_date DESC
LIMIT 100;

과정을 확인해보자.

orders를 idx_order_date_customer 인덱스로 스캔 (이미 order_date DESC 순으로 정렬됨)
→ 각 주문 마다 customers를 조인하고 그 customers에서 grade를 보면서 VIP를 체크
→ limit 100이 있으니까 조건 만족 행이 100건이 채워지면 즉시 종료해버림(스트리밍)

인덱스가 이미 정렬된 상태이므로 별도의 정렬 작업이 필요없다!
또한 LIMIT 조건 덕분에 전체 데이터를 스캔하지 않고 조기 종료가 가능하다.
이런 것이 스트리밍 방식 이라고 한다. 오직 인덱스를 통해서만 가능하며, 인덱스를 사용하지 않은 경우에는 버퍼링 방식 이라고 한다.

인덱스의 중요성

위에서 언급했듯이 스트리밍 방식을 적용하려면 ORDER BY 대상 컬럼에 인덱스가 반드시 존재해야 한다. 인덱스가 없으면 MySQL은 전체 결과를 버퍼에 모아 정렬할 수밖에 없고, 이 경우 LIMIT 조건이 있더라도 조기 종료 최적화가 불가능하다.

그러면 현실에선 뭐를 생각해야하나?

위에서 ADD INDEX를 했듯이, ORDER_BY 조건에 해당하는 컬럼에 대한 인덱스를 생성하는 것이 정렬 지연 문제 해결의 핵심이다. 이를 인덱스 튜닝이라고 한다.

특히 커서 기반 페이지네이션의 경우, 핵심 장점이 "필요한 행만 읽고 즉시 반환한다"는 점인데, 버퍼링 방식이 적용되면 이 장점이 완전히 사라진다. 따라서 페이지네이션 쿼리에서는 다음 두 가지를 반드시 고려해야 한다.

정렬 컬럼이 위치한 테이블을 드라이빙 테이블로 선택한다.
해당 컬럼에 적절한 인덱스를 추가한다.