Ch09-2 기본 데이터 처리

한 줄 정의

MySQL의 기본 데이터 처리는 풀 스캔 / 정렬 / 그룹핑 / 임시 테이블 네 가지 메커니즘으로 구성되며, 실행 계획의 Extra 칼럼은 결국 이 네 가지의 조합을 보여줍니다.

쉽게 말하면

쿼리 처리는 음식점 주방 과 같습니다.

풀 스캔 : 냉장고 전체를 뒤져서 재료를 찾기
ORDER BY 처리 : 재료를 가나다순으로 정렬하기 (인덱스 정렬은 미리 정렬된 서랍을 쓰는 것)
GROUP BY 처리 : 재료를 종류별로 분류해 모으기
DISTINCT 처리 : 중복된 재료 솎아내기
임시 테이블 : 작업 도중 결과를 임시로 올려둘 도마 (메모리가 좁으면 바닥에 내려놓음 = 디스크)

이 모든 작업은 가능하면 인덱스로 미리 처리 하고, 안 되면 메모리에서, 그것도 안 되면 디스크에서 합니다.

왜 중요한가?

EXPLAIN 결과의 Extra 칼럼에 등장하는 핵심 항목들이 모두 여기서 나옵니다.

Using filesort → ORDER BY를 인덱스로 처리 못 함
Using temporary → 내부 임시 테이블 사용
Using index for group-by → GROUP BY 최적화 (Loose Index Scan) 적용
Using where → 스토리지 엔진이 가져온 행을 MySQL 엔진이 추가 필터링

이 항목들의 의미와 원인을 모르면 EXPLAIN은 그저 영어 단어 나열일 뿐입니다.

핵심 내용

풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔이 선택되는 조건

옵티마이저는 다음 경우 풀 테이블 스캔을 선택합니다.

테이블이 작아서 인덱스 탐색보다 통째로 읽는 게 빠를 때
WHERE/ON 절에 인덱스를 활용할 조건이 없을 때
인덱스 조건이 있어도 읽어야 할 비율이 20~25%를 초과 할 때
force_index 같은 강제가 없을 때

Read Ahead로 빨라지는 풀 스캔

InnoDB는 풀 스캔 시 읽기 선행(Read Ahead) 을 적용합니다.

flowchart LR
    READ["페이지 N개 연속 읽음"] --> DETECT["순차 읽기 패턴 감지"]
    DETECT --> AHEAD["다음 익스텐트(64페이지)<br/>백그라운드로 미리 읽음"]
    AHEAD --> CACHE["버퍼 풀에 적재"]

이 덕분에 풀 스캔이 생각보다 빠를 수 있습니다. 옵티마이저가 풀 스캔을 선택하는 데에는 이 메커니즘이 반영됩니다.

풀 인덱스 스캔

Extra: Using index (커버링)와 헷갈리면 안 됩니다.

풀 테이블 스캔 : 데이터 파일 전체를 읽음
풀 인덱스 스캔 : 인덱스 트리의 리프 페이지 전체를 읽음 (데이터보다 작아 더 빠름)
커버링 인덱스 스캔 : 필요한 칼럼이 모두 인덱스에 있어 데이터 접근 없음

병렬 처리 — MySQL 8.0의 제한적 지원

MySQL은 전통적으로 세션당 단일 스레드 로 쿼리를 실행했습니다. 8.0부터 일부 제한적인 병렬 처리가 도입됐습니다.

작업	병렬 처리
일반 SELECT	불가 (단일 스레드)
`SELECT COUNT(*) FROM tab` (PK 풀 스캔)	가능 (`innodb_parallel_read_threads`)
인덱스 빌드 (`ALTER TABLE ... ADD INDEX`)	가능 (`innodb_ddl_threads`)

진짜 OLAP 워크로드라면 MySQL보다 ClickHouse 같은 컬럼형 DB를 고려하는 게 정답입니다. MySQL의 병렬 처리는 보조적 수준입니다.

ORDER BY 처리

ORDER BY를 처리하는 두 가지 방법이 있습니다.

flowchart TD
    OB["ORDER BY 만남"] --> Q{인덱스로 정렬<br/>가능?}
    Q -->|Yes| IDX["인덱스 정렬 활용<br/>(추가 비용 0)"]
    Q -->|No| FS["Filesort 수행<br/>메모리에서 디스크로 확장"]

    style IDX fill:#dfd
    style FS fill:#fdd

인덱스를 이용한 정렬

WHERE 조건과 ORDER BY가 같은 인덱스의 칼럼 순서를 따를 때 가능합니다.

-- 인덱스: (dept_no, hire_date)
SELECT * FROM employees WHERE dept_no = 'd001' ORDER BY hire_date;
-- → 인덱스 정렬, Extra: 비어있음

Filesort

인덱스로 못 풀면 별도의 정렬 작업 을 수행합니다. 이것이 Using filesort입니다.

flowchart LR
    DATA["대상 행 수집"] --> MEM["sort_buffer<br/>(메모리)"]
    MEM --> FIT{버퍼에<br/>다 들어감?}
    FIT -->|Yes| INMEM["In-memory sort<br/>(QuickSort)"]
    FIT -->|No| DISK["디스크 임시 파일에<br/>분할 정렬 후 머지"]

Filesort의 두 가지 모드

모드	동작	효율
`<sort_key, rowid>`	정렬키 + 주소만 정렬 후 다시 데이터 접근	메모리 절약, I/O 많음
`<sort_key, additional_fields>`	필요한 모든 칼럼을 정렬 시 함께 들고감	메모리 더 쓰지만 빠름

MySQL 8.0은 후자(additional_fields)를 더 선호하며, max_length_for_sort_data로 임계값을 조정합니다.

Filesort 회피 전략

ORDER BY 칼럼을 포함하는 인덱스 설계
복합 인덱스에서 ORDER BY 칼럼을 WHERE 등가 조건 칼럼 뒤 에 배치
정말 안 되면 sort_buffer_size를 늘려 디스크 사용 회피

GROUP BY 처리

GROUP BY는 세 가지 방식 중 하나로 처리됩니다.

flowchart TD
    GB["GROUP BY"] --> M1["방식 1: 인덱스 스캔 (Tight Scan)"]
    GB --> M2["방식 2: Loose Index Scan"]
    GB --> M3["방식 3: 임시 테이블 사용"]

    M1 --> M1D["인덱스 순서대로 읽으며 그룹핑<br/>Extra: 비어있음"]
    M2 --> M2D["인덱스의 일부만 띄엄띄엄 읽음<br/>Extra: Using index for group-by"]
    M3 --> M3D["임시 테이블에 그룹핑 후 집계<br/>Extra: Using temporary"]

Tight Index Scan

GROUP BY 칼럼이 인덱스 정렬 순서와 일치할 때 사용됩니다. 모든 행을 읽지만 정렬은 공짜입니다.

Loose Index Scan — 가장 효율적

MIN(), MAX() 같은 집계와 결합될 때, 각 그룹의 첫 (혹은 마지막) 키만 읽고 넘어갑니다.

SELECT dept_no, MIN(emp_no) FROM dept_emp GROUP BY dept_no;
-- 각 dept_no 그룹의 첫 emp_no만 읽음 → Loose Scan

임시 테이블 사용

위 두 방법으로 못 풀면 메모리 임시 테이블 에 그룹핑한 결과를 누적합니다. Extra: Using temporary; Using filesort 가 함께 보이면 가장 비싼 케이스입니다.

DISTINCT 처리

DISTINCT는 본질적으로 GROUP BY와 같은 방식으로 처리됩니다.

형태	처리
`SELECT DISTINCT col FROM tab`	인덱스가 있으면 Loose Scan
`SELECT DISTINCT col1, col2 FROM tab`	임시 테이블 사용 가능성 큼
`SELECT col1, COUNT(DISTINCT col2) FROM tab GROUP BY col1`	그룹별로 임시 테이블 → 매우 비쌀 수 있음

흔한 실수 — DISTINCT는 함수가 아닙니다

-- 잘못된 이해: 'COUNT 안의 컬럼만 unique'
SELECT DISTINCT col1, col2 FROM tab;
-- 실제: (col1, col2) 조합 전체가 unique

DISTINCT는 결과 행 전체 에 적용됩니다. 함수처럼 보여도 함수가 아닙니다.

내부 임시 테이블 (Internal Temporary Table)

MySQL은 다음 작업에서 내부 임시 테이블 을 자동 생성합니다.

작업	임시 테이블 발생 조건
ORDER BY	인덱스로 정렬 불가 + Filesort 외 추가 가공 필요
GROUP BY	인덱스로 그룹핑 불가
DISTINCT	여러 칼럼 조합
UNION	중복 제거 위해 항상 임시 테이블 (UNION ALL은 예외)
파생 테이블	서브쿼리 결과
CTE (WITH)	8.0부터 지원

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

flowchart LR
    NEED["임시 테이블 필요"] --> SIZE{"예상 크기 ≤<br/>tmp_table_size?"}
    SIZE -->|Yes| MEM["MEMORY/TempTable 엔진"]
    SIZE -->|No| DISK["InnoDB 임시 테이블"]

    MEM --> GROW{사용 중<br/>임계 초과?}
    GROW -->|Yes| MIGRATE["디스크로 이전"]
    GROW -->|No| KEEP["메모리 유지"]

MySQL 8.0의 변화 — TempTable 엔진

MySQL 8.0의 기본 임시 테이블 엔진은 TempTable 입니다.

VARCHAR, BLOB, TEXT 타입을 메모리에서 효율적으로 처리 (이전 MEMORY 엔진은 BLOB/TEXT 불가)
메모리 한도(temptable_max_ram) 초과 시 mmap 파일 로 오프로딩 (디스크보다 빠름)
그래도 한계 초과 시 InnoDB 디스크 임시 테이블 로 전환

Extra 항목으로 본 임시 테이블 위치

Extra	의미
`Using temporary`	내부 임시 테이블 사용 (메모리/디스크는 표시 안 됨)
`Using temporary; Using filesort`	임시 테이블 만들고 그 위에서 정렬 (가장 비쌈)

performance_schema.events_statements_history의 created_tmp_disk_tables 카운터로 디스크 임시 테이블 사용 여부 를 확인할 수 있습니다.

정리

Extra 칼럼 핵심 항목 사전

Extra	의미	좋음/나쁨
(비어있음)	인덱스로 모든 처리 완료	매우 좋음
`Using index`	커버링 인덱스	매우 좋음
`Using where`	스토리지 엔진이 준 결과를 추가 필터	보통
`Using index condition`	ICP 적용	좋음
`Using filesort`	별도 정렬 수행	주의
`Using temporary`	임시 테이블 생성	주의
`Using temporary; Using filesort`	임시 테이블 만들고 정렬	매우 나쁨
`Using index for group-by`	Loose Scan	좋음

정렬·그룹핑 회피 우선순위

단계	전략
1순위	인덱스로 처리 (정렬·그룹핑 모두 공짜)
2순위	메모리 안에서 처리 (`sort_buffer_size`, `tmp_table_size` 충분히)
3순위	디스크 임시 테이블/Filesort (체념하고 받아들임)
회피 불가	쿼리 자체를 분리하거나 결과 캐싱

내 생각

Using temporary; Using filesort 조합은 즉시 튜닝 대상 입니다. 이 둘이 같이 나오면 임시 테이블에 데이터를 다 모은 다음 다시 정렬 하는 가장 비싼 패턴입니다. GROUP BY ... ORDER BY 다른 칼럼 같은 형태에서 자주 발생합니다.
“Filesort라고 다 나쁜 건 아닙니다.” 메모리 안에서 끝나면 매우 빠릅니다. 진짜 문제는 디스크로 떨어지는 Filesort 입니다. sort_buffer_size와 created_tmp_disk_tables 카운터를 같이 봐야 진짜 병목인지 판단할 수 있습니다.
TempTable 엔진의 등장은 8.0의 숨은 큰 개선 입니다. 이전엔 BLOB/TEXT가 들어가면 무조건 디스크 임시 테이블이라 느렸지만, 이제 mmap으로 빠르게 처리됩니다. 그래서 8.0 마이그레이션 후 “이 쿼리가 이유 없이 빨라졌다” 같은 사례가 생깁니다.
풀 스캔이 항상 나쁘다는 건 오해입니다. 테이블이 작거나, 결과 비율이 높거나, 순차 I/O로 충분히 빠르면 옵티마이저는 풀 스캔을 골라야 합니다. EXPLAIN에서 type: ALL이 보였다고 무조건 인덱스를 추가하지 말고, 데이터 양과 비율을 먼저 봐야 합니다.

출처

Real MySQL 8.0 (1권), 9.2 기본 데이터 처리

Dev Wiki

탐색기