[MySQL] 정렬과 그룹핑 처리 - filesort, 임시 테이블, 그리고 그 내부

ORDER BY, GROUP BY, DISTINCT는 쿼리 결과를 정렬하고 중복을 제거하는 연산입니다. 이 세 연산은 인덱스를 활용할 수 있으면 추가 비용 없이 처리되지만, 그렇지 않으면 MySQL은 filesort와 임시 테이블이라는 두 가지 수단을 동원합니다. 이번 포스트에서는 각 연산이 내부적으로 어떤 경로로 처리되는지, filesort 알고리즘은 구체적으로 어떻게 동작하는지, 그리고 임시 테이블은 언제 어떤 엔진으로 생성되는지를 살펴보겠습니다.

 

---

1. ORDER BY 처리

MySQL의 ORDER BY 처리 경로는 세 가지입니다. 옵티마이저는 쿼리 구조와 인덱스 상태에 따라 이 중 하나를 선택합니다.

 

1.1 인덱스 정렬

정렬 기준 컬럼에 적합한 인덱스가 있다면, 인덱스를 순서대로 읽는 것만으로 정렬된 결과를 얻을 수 있습니다. 별도의 정렬 작업이 없으므로 성능상 가장 유리합니다. EXPLAIN의 Extra 컬럼에 Using filesort가 없으면 이 경로입니다.

인덱스 정렬이 불가능한 대표적인 경우는 다음과 같습니다.

• ORDER BY 기준 컬럼에 인덱스가 없는 경우
• WHERE 절의 레인지 스캔에 사용된 인덱스와 ORDER BY 기준 컬럼이 다른 경우
• 복합 인덱스의 컬럼 순서와 ORDER BY 순서가 일치하지 않는 경우
• 일부 컬럼은 ASC, 일부는 DESC인 혼합 정렬 (MySQL 8.0의 내림차순 인덱스로 해소 가능)

이 경우 옵티마이저는 아래 두 경로 중 하나를 선택합니다.

 

1.2 드라이빙 테이블만 정렬 후 조인

조인이 포함된 쿼리에서 ORDER BY 기준 컬럼이 드라이빙 테이블(driving table)에 속하는 경우, MySQL은 드라이빙 테이블을 먼저 filesort로 정렬한 뒤 드리븐 테이블과 조인합니다.

-- employees가 드라이빙 테이블이고 ORDER BY 컬럼이 employees에 속하는 경우
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY e.hire_date;

조인 결과 전체를 임시 테이블에 담지 않아도 되므로, 다음에 설명하는 임시 테이블 정렬보다 효율적입니다. EXPLAIN에서 드라이빙 테이블에 해당하는 행의 Extra 컬럼에 Using filesort가 표시됩니다.

 

1.3 임시 테이블 정렬

ORDER BY 기준 컬럼이 드리븐 테이블에 속하거나 조인 이후에야 확정되는 값을 기준으로 정렬해야 하는 경우, MySQL은 조인 결과 전체를 임시 테이블에 담은 뒤 filesort를 수행합니다. 세 경로 중 비용이 가장 높습니다.

-- ORDER BY 컬럼이 드리븐 테이블(departments)에 있는 경우
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY d.dept_name;

EXPLAIN의 Extra 컬럼에 Using temporary; Using filesort가 함께 표시되면 이 경로입니다.

 

1.4 filesort의 내부 알고리즘

1.2와 1.3에서 공통으로 사용되는 filesort는 Single-pass와 Two-pass 두 가지 알고리즘 중 하나로 동작합니다.

 

Two-pass 알고리즘 (구버전 방식)

Two-pass는 데이터를 두 번 읽어 정렬을 완료합니다.

• 1차 패스: 정렬 기준 컬럼 값과 행 포인터(row pointer)만 읽어 sort buffer에 적재합니다.
• 정렬: sort buffer 내에서 정렬합니다. sort buffer가 부족하면 정렬된 청크를 디스크 임시 파일에 기록하고, 모든 청크가 준비되면 병합합니다.
• 2차 패스: 정렬된 순서에 따라 행 포인터로 실제 데이터를 다시 읽어 반환합니다.

sort buffer 사용량이 적은 것이 장점이지만, 데이터를 두 번 읽어야 하고 2차 패스에서 랜덤 I/O가 발생하므로 결과 행이 많을수록 성능이 떨어집니다.

 

Single-pass 알고리즘 (현재 기본 방식)

Single-pass는 데이터를 한 번만 읽습니다.

• 1차 패스: 정렬 기준 컬럼 값과 함께 SELECT에 필요한 모든 컬럼을 sort buffer에 적재합니다.
• 정렬: sort buffer 내에서 정렬합니다. sort buffer가 부족하면 정렬된 청크를 디스크 임시 파일에 기록하고 병합합니다.
• 정렬 완료 후 sort buffer의 데이터를 그대로 클라이언트에 반환합니다.

I/O 횟수가 줄어드는 대신, SELECT 대상 컬럼 전체를 sort buffer에 담아야 하므로 메모리 사용량이 늘어납니다. MySQL은 기본적으로 Single-pass를 사용하며, 정렬 대상 레코드의 크기가 max_length_for_sort_data를 초과할 때만 Two-pass로 전환합니다.

참고 max_length_for_sort_data는 단일 레코드에서 SELECT 대상 컬럼 크기의 합산 기준값입니다. MySQL 8.0.20 이후 기본값은 4096바이트입니다.

 

1.5 sort buffer와 임시 파일

sort_buffer_size는 세션 단위로 할당되는 sort buffer의 크기입니다. filesort가 필요한 정렬 작업마다 이 크기의 메모리가 독립적으로 할당됩니다.

정렬 대상 데이터가 sort buffer를 초과하면 다음 방식으로 처리합니다.

1. 현재 sort buffer에 적재된 데이터를 정렬한 뒤 디스크 임시 파일에 기록합니다.
2. 다음 데이터 청크를 sort buffer에 적재해 같은 방식으로 처리합니다.
3. 모든 청크가 준비되면 병합 정렬(merge sort) 로 최종 결과를 만듭니다.

sort_buffer_size를 지나치게 크게 설정하면 동시 연결이 많을 때 메모리 압박이 생길 수 있습니다. 일반적으로 256KB~2MB 범위에서 워크로드에 맞게 조정하는 것이 권장됩니다.

 

---

2. GROUP BY 처리

2.1 인덱스를 활용하는 GROUP BY

GROUP BY 컬럼이 인덱스와 일치하는 경우, 별도의 정렬이나 임시 테이블 없이 인덱스를 읽는 것만으로 집계할 수 있습니다. 인덱스를 활용하는 방식에는 두 가지가 있습니다.

 

타이트 인덱스 스캔 (Tight Index Scan)

GROUP BY 컬럼 전체가 인덱스의 선두부터 순서대로 일치하는 경우, 인덱스를 순서대로 읽으면서 그룹 경계를 감지해 집계합니다. WHERE 조건이 있으면 레인지 스캔으로, 없으면 풀 인덱스 스캔으로 동작합니다.

 

루스 인덱스 스캔 (Loose Index Scan)

인덱스를 그룹 단위로 건너뛰며 읽는 방식입니다. (dept_id, salary) 복합 인덱스에서 GROUP BY dept_id를 수행할 때, 각 dept_id 그룹의 첫 번째(또는 마지막) 레코드만 읽으면 MIN(salary), MAX(salary) 집계를 완료할 수 있습니다. 조건과 동작 방식은 B-Tree 인덱스 포스트에서 자세히 다루었으므로 여기서는 생략합니다.

 

2.2 임시 테이블을 사용하는 GROUP BY

인덱스를 활용할 수 없는 경우, MySQL은 임시 테이블을 생성해 GROUP BY를 처리합니다.

1. 테이블을 순차적으로 읽으며 각 행을 처리합니다. (WHERE 조건이 있으면 해당 인덱스로 필터링 후 읽습니다.)
2. GROUP BY 컬럼 값을 키로, 집계 함수의 중간 결과를 값으로 임시 테이블에 누적합니다.
3. 모든 행 처리가 끝나면 임시 테이블의 결과를 반환합니다.

EXPLAIN의 Extra 컬럼에서 Using temporary가 확인됩니다.

 

한 가지 주의할 점은 MySQL 8.0부터 GROUP BY가 묵시적 정렬을 수행하지 않는다는 것입니다. 이전 버전에서는 GROUP BY 결과가 자동으로 그룹 키 기준으로 정렬되었지만, 8.0부터는 이를 보장하지 않습니다. 정렬이 필요하다면 반드시 ORDER BY를 명시해야 합니다.

 

---

3. DISTINCT 처리

3.1 인덱스를 활용하는 DISTINCT

DISTINCT 대상 컬럼에 인덱스가 있으면 루스 인덱스 스캔과 유사한 방식으로 처리됩니다. 인덱스는 이미 정렬되어 있으므로, 같은 값이 연속으로 나타나는 구간에서 첫 번째 레코드만 읽으면 중복을 제거할 수 있습니다.

 

3.2 단순 DISTINCT — GROUP BY로 변환

집계 함수 없이 DISTINCT만 사용하는 경우, 옵티마이저는 내부적으로 이를 GROUP BY로 변환해 처리합니다.

-- 아래 두 쿼리는 동일하게 처리됩니다
SELECT DISTINCT dept_id FROM employees;
SELECT dept_id FROM employees GROUP BY dept_id;

인덱스를 활용할 수 있으면 인덱스 스캔으로, 없으면 임시 테이블 경로로 처리됩니다.

 

3.3 집계 함수 내 DISTINCT

COUNT(DISTINCT col), SUM(DISTINCT col)처럼 집계 함수 안에 DISTINCT가 포함된 경우는 단순 SELECT DISTINCT와 처리 방식이 다릅니다. 옵티마이저는 GROUP BY로 변환하지 않고, 임시 테이블을 생성해 중복을 제거한 뒤 집계합니다.

-- 임시 테이블로 처리됩니다
SELECT COUNT(DISTINCT dept_id) FROM employees;

-- 여러 컬럼에 각각 COUNT(DISTINCT)를 적용하면
-- 컬럼마다 별도 임시 테이블이 생성됩니다
SELECT COUNT(DISTINCT dept_id), COUNT(DISTINCT job_id) FROM employees;

dept_id에 인덱스가 있더라도 루스 인덱스 스캔으로 처리되지 않습니다. 루스 인덱스 스캔은 그룹별 최솟값·최댓값처럼 인덱스 내에서 직접 추출 가능한 값을 집계할 때만 적용되는데, COUNT(DISTINCT)는 그룹 내 고유 값 개수를 세야 하므로 반드시 실제 데이터를 읽고 중복을 제거하는 과정이 필요하기 때문입니다. 특히 여러 컬럼에 동시에 COUNT(DISTINCT)를 적용하면 컬럼마다 임시 테이블이 따로 생성되므로 비용이 누적됩니다.

 

3.4 DISTINCT와 ORDER BY 조합

DISTINCT와 ORDER BY를 함께 사용하면 두 가지 제약이 있습니다.

첫째, ORDER BY 대상 컬럼이 SELECT 목록에 없으면 오류가 발생합니다.

-- 오류: dept_name이 SELECT 목록에 없음
SELECT DISTINCT dept_id FROM employees ORDER BY dept_name;

-- 정상: dept_name을 SELECT 목록에 포함
SELECT DISTINCT dept_id, dept_name FROM employees ORDER BY dept_name;

둘째, SELECT 목록에 포함된 경우라도 DISTINCT 처리 후 ORDER BY가 수행되므로 filesort가 추가될 수 있습니다.

 

---

4. 임시 테이블 처리

4.1 임시 테이블이 생성되는 조건

앞서 각 섹션에서 살펴본 것 외에도, MySQL은 다음 상황에서 내부 임시 테이블을 추가로 생성합니다.

• UNION 처리 (UNION ALL은 MySQL 8.0부터 임시 테이블 없이 처리 가능)
• 서브쿼리나 뷰에서 파생된 결과(derived table) 처리
• GROUP BY와 ORDER BY의 기준 컬럼이 서로 다른 경우

어떤 경로로 임시 테이블이 생성되든 EXPLAIN의 Extra 컬럼에 Using temporary가 표시됩니다.

 

4.2 메모리 임시 테이블 vs 디스크 임시 테이블

MySQL 8.0.2 이전에는 메모리 임시 테이블에 MEMORY 스토리지 엔진을 사용했습니다. MySQL 8.0.2부터는 더 효율적인 TempTable 엔진이 기본값으로 도입되었습니다.

 

임시 테이블의 크기가 임계값을 초과하면 디스크 기반으로 전환됩니다. 이 과정을 제어하는 시스템 변수는 다음과 같습니다.

변수	설명	기본값
tmp_table_size	메모리 임시 테이블의 최대 크기 (MEMORY 엔진)	16MB
max_heap_table_size	MEMORY 엔진 테이블의 최대 크기	16MB
temptable_max_ram	TempTable 엔진이 사용하는 메모리 최대값	1GB
temptable_max_mmap	TempTable 엔진의 메모리 맵 파일 최대값	1GB

 

MEMORY 엔진을 사용하는 경우 tmp_table_size와 max_heap_table_size 중 작은 값이 상한이 됩니다. TempTable 엔진은 temptable_max_ram까지 메모리를 사용하고, 초과 시 메모리 맵 파일로 전환하며, 그것도 초과하면 InnoDB 디스크 임시 테이블로 변환합니다.

 

4.3 TempTable 엔진의 특징

기존 MEMORY 엔진은 VARCHAR, BLOB, TEXT 같은 가변 길이 타입을 선언된 최대 길이로 고정 할당해 메모리를 낭비하는 문제가 있었습니다. TempTable 엔진은 실제 데이터 크기에 맞게 저장하므로 메모리 효율이 크게 개선되었습니다. 또한 TempTable은 행 잠금 수준의 동시성을 지원합니다.

 

---

정리

MySQL의 정렬과 그룹핑 처리는 인덱스 활용 여부에 따라 성능이 크게 갈립니다.

처리 방식	인덱스 활용	추가 비용
ORDER BY (인덱스 정렬)	✓	없음
ORDER BY (드라이빙 테이블 filesort)	부분	sort buffer + 정렬 CPU
ORDER BY (임시 테이블 + filesort)	✗	임시 테이블 생성 + 정렬 CPU + I/O
GROUP BY (타이트/루스 인덱스 스캔)	✓	없음 또는 최소
GROUP BY (임시 테이블)	✗	임시 테이블 생성 + 집계
DISTINCT (단순)	조건부	인덱스 있으면 없음, 없으면 임시 테이블
COUNT(DISTINCT)	✗	임시 테이블 생성 (컬럼마다)

 

EXPLAIN의 Extra 컬럼에서 Using filesort나 Using temporary가 보이면 인덱스 설계를 재검토할 여지가 있습니다. 다만 이 두 방식이 항상 나쁜 것은 아닙니다. 데이터 양이 적거나 정렬 빈도가 낮은 경우, 인덱스를 추가하는 비용이 오히려 더 클 수 있습니다.