테마
정렬·그룹·임시 테이블
EXPLAIN1 결과에 보이는 Using filesort·Using temporary의 정체를 다룬다. 정렬·그룹핑을 인덱스 순서대로 처리할 수 있으면 둘 다 사라진다. 그렇지 않으면 정렬 버퍼(sort buffer)와 임시 테이블(temporary table)이 대신 일한다.
ORDER BY 처리
인덱스는 값이 정렬된 상태로 저장된다. 그래서 인덱스 순서와 ORDER BY가 일치하면 인덱스를 순서대로 읽기만 해도 정렬이 끝난다. 일치하지 않으면 MySQL이 읽은 결과를 따로 정렬하는데, 이것이 파일소트(filesort)다. 이름과 달리 파일을 뜻하는 게 아니라 정렬 작업 자체를 가리킨다. 먼저 메모리의 정렬 버퍼에서 정렬하고, 버퍼가 넘치면 디스크를 쓴다.
sql
-- (created_at) 인덱스 있음
SELECT * FROM t ORDER BY created_at LIMIT 10; -- 인덱스 순서 읽기, filesort 없음
SELECT * FROM t ORDER BY updated_at LIMIT 10; -- 인덱스 없음 → filesort파일소트 자체는 나쁜 게 아니다. 건수가 적으면 메모리에서 끝나고, LIMIT이 있으면 전체를 정렬하지 않고 상위 N개만 힙(heap)2에 유지하는 방식으로 싸게 처리된다. 문제는 대량의 행을 정렬하다 버퍼가 넘쳐 디스크로 유출(spill)되는 경우다.
GROUP BY·DISTINCT
GROUP BY는 같은 값끼리 묶는 그룹핑과, 묶음마다 합계·개수 등을 내는 집계를 한다. 인덱스 순서가 맞으면 같은 값이 연달아 나오므로 읽으면서 바로 그룹을 끝내는 스트리밍 집계를 한다. 맞지 않으면 임시 테이블에 모아 처리한다.- MySQL 8.0부터 GROUP BY는 결과 순서를 보장하지 않는다. 정렬이 필요하면
ORDER BY를 명시한다. GROUP BY에 없는 칼럼을 SELECT 목록에 쓸 수 있는지는 SQL 쪽 규칙이다(SELECT 작성과 처리 순서 2의ONLY_FULL_GROUP_BY). DISTINCT는 중복을 제거한다. 인덱스를 읽으며 서로 다른 값만 골라내는 유니크 스캔이 되면 싸고, 아니면 임시 테이블에 모아(해시나 정렬로) 중복을 거른다.
ORDER BY·GROUP BY·DISTINCT 모두 인덱스 순서를 활용하느냐, 따로 모아서 처리하느냐가 갈림길이다.
내부 임시 테이블
내부 임시 테이블(internal temporary table)은 따로 모아서 처리할 때 서버가 쿼리 도중 스스로 만드는 작업용 테이블이다.
- 메모리에 만들 때 기본 엔진은 TempTable3이다. 임시 테이블 하나가
tmp_table_size(메모리 임시 테이블 하나의 크기 한도)를 넘으면 디스크의 InnoDB 내부 임시 테이블로 전환된다. 모든 TempTable이 함께 쓰는 자원은temptable_max_ram(메모리 한도)·temptable_max_mmap(메모리가 모자랄 때 디스크 파일을 메모리처럼 연결해 쓰는 메모리 맵 파일의 한도)의 영향도 받는다. - TempTable은 TEXT·BLOB(Binary Large Object, 대용량 바이너리 객체) 타입도 메모리에서 다룰 수 있다. 메모리 임시 테이블의 엔진으로 MEMORY 엔진4을 선택한 경우에는 이 타입을 지원하지 않아 디스크 전환 조건이 달라진다.
- 임시 테이블을 쓰면
EXPLAIN에Using temporary가 표시된다. 모으는 건수가 크면 여기서 병목이 난다.
처방은 다음 순서로 한다. 먼저 실제로 처리하는 행 수와 정렬량을 확인한다. 다음으로 인덱스 순서를 활용할 수 있는지 검토한다. 그다음 SELECT하는 칼럼과 중간 결과를 줄인다. 메모리 한도 조정은 마지막이다. Using temporary가 보인다는 것만으로 무조건 튜닝하지 않는다.
면접 포인트
- "파일소트가 뭐예요?" → 인덱스 순서로 못 읽을 때의 정렬 단계. 메모리→디스크 유출 구조.
- "Using temporary가 나왔어요" → 모아서 처리 중. 인덱스 활용·SELECT 축소·메모리 한도 조정 순서로.
- "GROUP BY가 느려요" → 인덱스 순서 집계 가능 여부 → 임시 테이블 유출 여부.
참고