MySQL 임시 테이블, 디스크로 새는 것 막기

GROUP BY, DISTINCT, 일부 ORDER BY나 서브쿼리를 처리할 때 MySQL은 중간 결과를 담을 임시 테이블을 만듭니다. 이게 메모리 안에서 끝나면 빠르지만, 크기가 한계를 넘으면 디스크 임시 테이블로 전환되면서 느려집니다. 느린 집계 쿼리의 숨은 원인인 경우가 많습니다.

메모리 임시 vs 디스크 임시

디스크로 넘어가는 두 가지 큰 이유는 (1) 결과가 tmp_table_size/max_heap_table_size 한계를 초과하거나, (2) TEXT나 BLOB 같은 큰 컬럼이 결과에 포함되어 메모리 엔진을 못 쓰는 경우입니다.

진단: 디스크로 새는지 확인

먼저 디스크 임시 테이블이 실제로 생기는지 봅니다.

SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';

Created_tmp_disk_tables가 Created_tmp_tables 대비 비율이 높으면(예: 25% 이상) 디스크로 새고 있다는 뜻입니다. 그다음 어떤 쿼리가 범인인지 실행계획에서 확인합니다.

EXPLAIN SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY COUNT(*);

Extra 컬럼에 Using temporary가 보이면 그 쿼리가 임시 테이블을 만든다는 신호입니다. Using temporary; Using filesort가 함께 나오면 정렬까지 디스크에서 일어날 수 있습니다.

디스크 임시 테이블 줄이는 법

1. 결과에서 큰 컬럼을 빼기 — SELECT * 대신 필요한 컬럼만. TEXT/BLOB이 섞이면 메모리 엔진을 못 쓰니 집계 결과에서 제외합니다.
2. 인덱스로 GROUP BY 커버 — GROUP BY 컬럼에 인덱스가 있으면 임시 테이블 없이 정렬된 순서로 읽어 Using temporary가 사라집니다.
3. 불필요한 DISTINCT·중복 제거 — 조인으로 행이 불어나 DISTINCT를 붙인 경우, 조인 조건을 고치면 임시 테이블 자체가 사라집니다.
4. tmp_table_size·max_heap_table_size 상향 — 둘 중 작은 값이 한계입니다. 정상 쿼리가 근소하게 넘칠 때만 올리고, 무한정 키우지는 않습니다(연결마다 메모리 소모).

SET SESSION tmp_table_size = 67108864;       -- 64MB
SET SESSION max_heap_table_size = 67108864;

설정값을 올리기 전에 쿼리 자체를 줄이는 것이 먼저입니다. 메모리만 키우면 동시 접속이 많을 때 전체 메모리가 부족해집니다.

요점 정리

• 임시 테이블이 디스크로 넘어가면 I/O 때문에 느려진다.
• Created_tmp_disk_tables 비율과 EXPLAIN의 Using temporary로 진단.
• 큰 컬럼 제외 → 인덱스 커버 → 불필요한 DISTINCT 제거 순으로 쿼리부터 고친다.
• 설정값 상향은 마지막 수단.

Using temporary가 사라지도록 쿼리를 고쳐보는 실습은 데이터베이스 트랙에서 회원가입 없이 무료로 할 수 있습니다.