beautifulsoups.dev
Published on

MySQL 8 filesort·tmp table 폭증 튜닝 실전

Authors

서버가 멀쩡히 돌아가다가 어느 날부터 EXPLAIN에 Using filesort, Using temporary가 자주 보이고, P95/P99가 튀며 CPU/IO가 치솟는 상황은 MySQL 8 운영에서 흔한 장애 패턴입니다. 특히 트래픽이 늘거나 배포로 쿼리가 바뀌었을 때뿐 아니라, 통계/카디널리티 변화나 히스토그램/플랜 변경, 정렬·그룹핑 임계값 초과로 디스크 tmp table로 전환되면서도 발생합니다.

이 글은 “filesort/tmp table이 왜 늘었는지”를 관측 → 재현 → 원인 분류 → 쿼리/인덱스/설정 튜닝 순서로 정리합니다. (MySQL 8 기준)

1) filesort·tmp table이 의미하는 것

Using filesort

  • 인덱스 순서를 그대로 이용해 정렬(Order By)을 끝내지 못하고, 별도의 정렬 단계가 들어갔다는 뜻입니다.
  • 이름은 filesort지만, 항상 디스크를 쓰는 것은 아닙니다. 메모리에서 정렬하다가 임계값을 넘으면 디스크를 사용합니다.

Using temporary

  • GROUP BY, DISTINCT, UNION, 윈도우 함수, 정렬+그룹핑 조합 등에서 중간 결과를 임시 테이블에 담아 처리한다는 뜻입니다.
  • 임시 테이블이 메모리(TempTable 엔진)에서 처리되다가, 크기가 커지면 디스크(InnoDB temp table) 로 내려가며 비용이 폭증합니다.

핵심은 두 문구가 “나쁘다”가 아니라, 빈도가 폭증하거나 디스크 tmp로 자주 떨어질 때가 문제라는 점입니다.

2) 폭증 시 가장 먼저 보는 관측 지표(5분 컷)

2.1 Performance Schema로 상위 쿼리 찾기

MySQL 8에서는 sys 스키마가 실전에서 가장 빠릅니다.

-- 최근 정렬/임시테이블 관련 부하가 큰 쿼리
SELECT *
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

-- 임시 테이블 생성이 많은 쿼리
SELECT *
FROM sys.statements_with_temp_tables
ORDER BY total_latency DESC
LIMIT 20;

-- 정렬이 많은 쿼리
SELECT *
FROM sys.statements_with_sorting
ORDER BY total_latency DESC
LIMIT 20;

여기서 상위 1~3개 쿼리가 전체를 망치는 경우가 대부분입니다.

2.2 전역 상태 변수로 “디스크 tmp” 여부 확인

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort%';
  • Created_tmp_disk_tables가 같이 증가하면, 임시 테이블이 디스크로 내려가고 있는 겁니다.
  • Sort_merge_passes 증가도 중요합니다. 정렬이 메모리에 못 올라가 merge pass가 늘면 지연이 커집니다.

2.3 로그/모니터링 비용도 같이 점검

정렬/임시테이블 폭증은 쿼리 로그(슬로우 로그, general log) 볼륨도 함께 늘 수 있습니다. 운영 환경에선 로그 수집 비용도 장애의 2차 피해가 되므로, 로그 폭증 시엔 비용 관점도 같이 확인하세요: CloudWatch Logs 비용 폭증 원인과 절감 10가지

3) 재현: EXPLAIN ANALYZE로 “어디서” 터지는지 보기

MySQL 8의 EXPLAIN ANALYZE는 정렬/임시테이블의 비용을 체감할 수 있게 해줍니다.

EXPLAIN ANALYZE
SELECT user_id, COUNT(*) AS c
FROM orders
WHERE created_at >= '2026-02-01'
GROUP BY user_id
ORDER BY c DESC
LIMIT 50;

여기서 확인할 포인트:

  • Sort 노드가 어디에 있는지
  • Temporary table/Materialize가 생기는지
  • 실제 rows, loops가 예상과 얼마나 다른지(통계 문제 가능)

4) 원인 분류: 폭증 패턴 6가지

4.1 ORDER BY가 인덱스를 못 타는 경우

다음 중 하나면 filesort가 발생하기 쉽습니다.

  • 정렬 컬럼이 인덱스에 없거나, 인덱스 순서가 맞지 않음
  • WHERE 조건과 ORDER BY가 서로 다른 인덱스를 요구
  • ORDER BY에 표현식/함수 사용 (ORDER BY DATE(created_at) 등)
  • 정렬 방향 혼합, 컬럼 조합 불일치

해결: “필터 + 정렬”을 함께 만족하는 복합 인덱스

-- 예: 최신 주문 50건
SELECT *
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 50;

-- 좋은 인덱스
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

MySQL 8은 DESC 인덱스를 지원합니다. (ASC/DESC 혼합도 가능하지만, 조건에 따라 여전히 filesort가 생길 수 있습니다.)

4.2 GROUP BY/DISTINCT가 임시 테이블을 만드는 경우

  • GROUP BY 컬럼이 인덱스 선두(prefix)로 정렬되어 있지 않으면 임시 테이블이 만들어지기 쉽습니다.
  • DISTINCT는 내부적으로 중복 제거를 위해 임시 구조가 필요해지는 경우가 많습니다.

해결 1: GROUP BY 순서에 맞는 인덱스

SELECT user_id, status, COUNT(*)
FROM orders
WHERE created_at >= ?
GROUP BY user_id, status;

CREATE INDEX idx_orders_group
ON orders(user_id, status, created_at);

주의: WHERE의 범위 조건(created_at >= ?)이 인덱스 뒤쪽에 있으면, GROUP BY 최적화가 제한될 수 있습니다. 이럴 땐 쿼리 목적(필터 우선 vs 그룹핑 우선)에 따라 인덱스 설계를 다시 잡아야 합니다.

해결 2: 불필요한 DISTINCT 제거

-- 나쁜 예: 이미 user_id가 PK/UNIQUE로 보장되는데 DISTINCT 사용
SELECT DISTINCT user_id FROM users WHERE ...;

-- 좋은 예
SELECT user_id FROM users WHERE ...;

4.3 JOIN이 “폭발”해서 정렬/그룹핑 입력이 커지는 경우

정렬 자체가 문제라기보다, 조인 결과가 예상보다 커져서 정렬 비용이 폭증하는 케이스입니다.

  • 조인 조건 누락
  • 카디널리티 추정 실패로 조인 순서가 뒤집힘
  • 세미조인/서브쿼리 변환 실패

해결: 조인 키 인덱스 + 조인 순서 점검

EXPLAIN ANALYZE
SELECT o.id, o.created_at, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 100;

-- 필요한 인덱스 예
CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);

WHERE로 줄인 뒤 정렬해야 합니다. status로 많이 줄어드는데 인덱스가 없다면, 대량 스캔 후 정렬로 이어질 수 있습니다.

4.4 윈도우 함수/CTE로 materialize가 늘어나는 경우

MySQL 8에서 윈도우 함수, CTE는 편하지만 중간 결과를 크게 만들 수 있습니다.

  • CTE는 경우에 따라 materialize(실체화)되어 임시 테이블을 만들 수 있습니다.

해결: CTE를 인라인으로 바꾸거나, 중간 결과 크기 축소

WITH recent AS (
  SELECT id, user_id, created_at
  FROM orders
  WHERE created_at >= NOW() - INTERVAL 7 DAY
)
SELECT user_id, COUNT(*)
FROM recent
GROUP BY user_id;

-- 대안: 조건을 더 강하게, 필요한 컬럼만, 가능하면 인라인 서브쿼리 검토

4.5 tmp table이 메모리에서 디스크로 떨어지는 경우(가장 체감 큼)

MySQL 8의 임시 테이블은 기본적으로 TempTable(메모리) → 임계값 초과 시 디스크(InnoDB)로 전환될 수 있습니다.

대표 변수:

  • tmp_table_size
  • max_heap_table_size
  • temptable_max_ram

메모리 임시 테이블의 최대 크기는 보통 min(tmp_table_size, max_heap_table_size)로 제한되는 것으로 이해하면 운영에 도움이 됩니다(세부 동작은 엔진/유형에 따라 다름).

해결: “무작정 키우기”가 아니라 동시성 고려

다음은 점검용입니다.

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'temptable_max_ram';
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

튜닝 원칙:

  • 임시 테이블이 큰 쿼리가 동시에 많이 실행되면, tmp 관련 메모리를 크게 올리는 것은 OOM/스왑의 지름길입니다.
  • 먼저 쿼리로 중간 결과 크기를 줄이고(필터 선적용, 필요한 컬럼만), 그 다음에 메모리 상향을 검토하세요.

4.6 통계/히스토그램/플랜 변경으로 갑자기 느려지는 경우

“코드 변경 없는데 갑자기”라면 통계 변화가 의심됩니다.

  • 데이터 분포가 바뀌었는데 통계가 낡음
  • 히스토그램 유무에 따라 플랜이 바뀜
  • 인덱스 카디널리티 추정 실패로 잘못된 조인 순서/정렬 전략 선택

해결: ANALYZE TABLE + 히스토그램(선택)

ANALYZE TABLE orders;

-- 특정 컬럼 분포가 치우쳐 추정이 자주 틀리면 히스토그램 검토
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country WITH 64 BUCKETS;

히스토그램은 만능이 아닙니다. 자주 변하는 컬럼에 남발하면 오히려 관리 비용이 늘 수 있으니, “플랜이 자주 틀리는 컬럼”에 한정하는 게 좋습니다.

5) 실전 튜닝 레시피: 자주 나오는 쿼리 패턴별 처방

5.1 Top-N(ORDER BY + LIMIT) 최적화

Top-N은 인덱스 정렬을 타면 압도적으로 빨라집니다.

-- 최신 글 20개
SELECT id, title, created_at
FROM posts
WHERE tenant_id = ?
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_posts_tenant_created
ON posts(tenant_id, created_at DESC);

가능하면 커버링 인덱스(select 컬럼까지 포함)도 고려하세요.

CREATE INDEX idx_posts_tenant_created_cover
ON posts(tenant_id, created_at DESC, id, title);

5.2 페이징에서 filesort 폭증(OFFSET 지옥)

LIMIT offset, size는 offset이 커질수록 정렬/스캔 비용이 커집니다.

-- 나쁜 예
SELECT *
FROM orders
WHERE user_id=?
ORDER BY created_at DESC
LIMIT 100000, 50;

해결: Keyset Pagination

-- 마지막으로 본 created_at, id를 커서로 사용
SELECT id, created_at, total
FROM orders
WHERE user_id=?
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 50;

CREATE INDEX idx_orders_user_created_id
ON orders(user_id, created_at DESC, id DESC);

5.3 GROUP BY + ORDER BY(집계 후 정렬) 최적화

GROUP BY 결과를 ORDER BY count desc로 정렬하는 쿼리는 대개 임시 테이블+정렬이 필요합니다. 이 경우는 인덱스만으로 완전히 없애기 어렵고, 전략을 바꿔야 합니다.

대안:

  • 사전 집계 테이블(summary table)
  • 실시간이 필요하면 스트리밍/배치로 집계 갱신
-- 일 단위 사용자 주문수 집계 테이블
CREATE TABLE user_order_daily (
  ymd DATE NOT NULL,
  user_id BIGINT NOT NULL,
  cnt INT NOT NULL,
  PRIMARY KEY (ymd, user_id),
  KEY idx_cnt (ymd, cnt)
);

-- 조회는 집계 테이블에서
SELECT user_id, cnt
FROM user_order_daily
WHERE ymd = '2026-02-22'
ORDER BY cnt DESC
LIMIT 50;

6) 운영에서의 안전한 변경 순서(실수 방지)

  1. 가해 쿼리 식별: sys.statements_with_sorting, ...temp_tables로 Top N 확정
  2. EXPLAIN ANALYZE로 병목 확인: Sort/Materialize/rows 폭증 지점 확인
  3. 쿼리 수정: 불필요한 컬럼 제거, 조건 선적용, 페이징 방식 변경
  4. 인덱스 추가/수정: 복합 인덱스(필터+정렬), 커버링 인덱스
  5. 통계 갱신: ANALYZE TABLE, 필요 시 히스토그램 제한 적용
  6. 파라미터 조정은 마지막: tmp/sort 메모리 상향은 동시성 고려 후 점진 적용

배포/변경 작업을 쿠버네티스 환경에서 한다면, 장애 시 파드 재시작/프로브/OOM 같은 2차 증상으로 보일 수 있습니다. DB 병목이 애플리케이션을 밀어 올려 OOMKilled로 이어지기도 하니, 앱 레벨 증상도 함께 보면 진단이 빨라집니다: K8s CrashLoopBackOff - OOMKilled·Probe 5분 진단

7) 체크리스트(요약)

  • Created_tmp_disk_tables가 증가하는가? → 디스크 tmp 전환이 핵심 문제일 가능성
  • Sort_merge_passes가 증가하는가? → 정렬 메모리 부족/정렬 입력 과다
  • Top 쿼리 1~3개가 전체를 망치는가? → sys 스키마로 즉시 확인
  • ORDER BY/LIMIT은 인덱스로 해결 가능한가? → (필터, 정렬) 복합 인덱스
  • OFFSET 페이징인가? → Keyset으로 변경
  • GROUP BY 후 정렬인가? → 사전 집계 테이블 검토
  • 코드 변경이 없었는가? → 통계/히스토그램/플랜 변화를 의심하고 ANALYZE

8) 마무리

MySQL 8에서 Using filesort, Using temporary는 흔하지만, “폭증”은 거의 항상 (1) 정렬/그룹핑 입력이 커졌거나 (2) 인덱스가 정렬을 못 도와주거나 (3) 임시 테이블이 디스크로 내려간 결과입니다. 관측(sys/PS)으로 상위 쿼리를 고르고, EXPLAIN ANALYZE로 병목 노드를 확인한 뒤, 쿼리 구조와 인덱스를 먼저 손보면 대부분의 케이스는 설정을 크게 건드리지 않고도 안정화됩니다.

다음 단계로는 실제 운영 쿼리 1~2개를 기준으로 EXPLAIN ANALYZE 결과를 놓고 “어떤 인덱스가 정렬을 대체할 수 있는지”, “중간 결과를 어디서 줄일지”를 구체적으로 설계하는 것이 가장 효과적입니다.

Discuss on Twitter • View on GitHub