MySQL이 실제로 정렬 시 임시 파일(디스크)을 사용했는지 여부를 확인하는 방법

MySQL이 실제로 정렬 시 임시 파일(디스크)을 사용했는지 여부를 확인하는 방법

---

✅ 1. SHOW STATUS로 확인 (세션 단위)

SHOW SESSION STATUS LIKE 'Sort_merge_passes';

🔹 설명:

• 이 값이 0보다 크면, 메모리 정렬 버퍼를 넘겨서 디스크를 사용했다는 의미입니다.
• Sort_merge_passes는 filesort 도중 몇 번 외부 정렬(merge pass)을 했는가를 나타냅니다.

🔹 사용 방법:

1. 쿼리 실행 전에 값을 저장:
2. SHOW SESSION STATUS LIKE 'Sort_merge_passes';
3. 쿼리 실행
4. 다시 조회:
5. SHOW SESSION STATUS LIKE 'Sort_merge_passes';

차이가 1 이상이면 임시 파일(디스크 정렬) 발생한 것!

---

✅ 2. EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE
SELECT * FROM my_table ORDER BY some_column;

• EXPLAIN ANALYZE는 실행 시간과 함께 실제 임시 테이블 사용 여부, filesort 사용 여부 등을 보여줍니다.
• 결과에 "Using filesort"와 함께 memory/disk 정렬 여부도 간접적으로 파악 가능.

MySQL 8.0 이상에서 지원됨

---

✅ 3. 임시 테이블 사용 확인 (Created_tmp_disk_tables)

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

• 쿼리 실행 전/후로 이 값의 차이를 보면 디스크 기반 임시 테이블이 생성되었는지 확인 가능
• 정렬 외에도 GROUP BY, DISTINCT, UNION, TEXT/BLOB 컬럼 포함된 ORDER BY 등도 해당됨

---

✅ 메모리 설정 관련 (참고)

MySQL은 다음 설정에 따라 메모리 정렬 한계를 정합니다:

변수의미기본값

sort_buffer_size	정렬 버퍼 크기 (각 쿼리마다!)	보통 256KB ~ 2MB
read_rnd_buffer_size	정렬 후 읽을 때 사용하는 버퍼	256KB ~ 4MB

→ 정렬 대상 row 수가 많거나 row 크기가 크면 쉽게 한계를 넘어서 디스크로 spill됩니다.

---

✅ 요약

방법	내용
SHOW STATUS LIKE 'Sort_merge_passes'	정렬 중 외부 병합(디스크)을 몇 번 했는지
SHOW STATUS LIKE 'Created_tmp_disk_tables'	디스크 기반 임시 테이블 사용 여부
EXPLAIN ANALYZE	실행 시점에서 정렬 및 임시 파일 사용 여부 추적 가능 (MySQL 8+)