SQL - Using Temporary, Using Filesort 정리 (+ 임시 테이블, 파일 정렬)

Using Temporary, Using Filesort

Explain 문으로 실행 계획을 확인하면, 늘 눈에 거슬리는 것이 Using Temporary, Using Filesort이다.

 

이것들이 왜 발생하고 어떠한 영향이 있는 지 알아보자.

임시 테이블(Using Temporary)

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑 할 때는 내부적인 임시 테이블을 사용한다.

 

MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다.

 

원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.

 

다음은 임시 테이블이 필요한 경우이다.

• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• DISTINCT와 ORDER BY가 동시에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION이나 UNION DISTICT가 사용된 쿼리
• UNION ALL이 사용된 쿼리
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

해당 6가지 경우 중 1~3 번째 경우에는 Extra 칼럼에 "Using Temporary"가 표시된다. 반면 4~6 번째 경우에는 Extra 칼럼에 "Using Temporary"가 표시되지 않지만 임시 테이블이 사용된다.

 

주의사항

내부 임시 테이블이 크기가 커서 디스크에 생성되면 성능 이슈가 발생한다.

 

내부 임시 테이블이 디스크에 생성되었는지 여부를 파악하려면 아래의 명령어로 가능하다.

SHOW SESSION STATUS LIKE 'Create_tmp%';

해당 쿼리의 결과로 생성된 임시 테이블의 상태를 파악할 수 있다.

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
+--------------------------+------------+

 

만약 처리해야하는 레코드 건수가 100만건이라고 가정하자. 해당 건의 데이터 디스크에 저장된다면 큰 부하가 발생하게 된다.

따라서 각별히 주의를 기울여야 한다.

 

드라이빙 테이블 정렬 (Using Filesort)

MySQL의 정렬 처리는 크게 2가지로 나뉜다.

• Index를 이용한 정렬
• File Sort ("Using filesort")

 

인덱스를 사용할 수 있는 경우는 아래의 경우이다.

1. ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속해야 한다.
2. ORDER BY 절의 순서가 인덱스와 일치해야 한다.
3. WHERE 절에 첫 번째 읽는 테이블에 대한 조건이 있다면, ORDER BY절도 같은 인덱스를 사용할 수 있어야 한다.

인덱스를 사용하는 정렬은 완벽한 조건을 요구하는 만큼 처리가 빠르다. 인덱스의 값이 정렬되어 있기 때문에 해당 인덱스를 읽기만 하면 된다.

 

MySQL은 인덱스를 통해 정렬을 할 수 없다면 FileSort를 사용한다. 위에서 본 임시 테이블을 사용한 정렬도 FileSort에 해당한다.

 

File Sort는 레코드가 많아질수록 쿼리의 응답 속도가 현저히 떨어지게 된다.

 

File Sort를 사용하는 경우도 2가지로 나눌 수 있다.

• 드라이빙 테이블만 정렬 ("Using filesort")
• 임시 테이블을 이용한 정렬 ("Using temporary, Using filesort")

드라이빙 테이블만 정렬된다는 방식은 조회하는 테이블 중 먼저 액세스되는 테이블만 SortBuffer에서 정렬해서 나머지 테이블들과 조합하는 방식이다.

이 경우 SortBuffer로 테이블을 하나 옮겨서 정렬하는 작업이 필요하지만 임시 테이블을 이용하는 방법보다는 낫다.

 

드라이빙 테이블만 정렬하는 방법을 사용할 수 없다면 임시 테이블을 사용해야 한다. 임시 테이블을 이용한 정렬은 2개 이상의 테이블을 조인해서 그 결과를 전부 임시 테이블에 넣어서 정렬하는 방식이다.

임시테이블을 이용한 정렬은 모든 테이블의 결과를 임시 테이블에 넣고 정렬을 수행하므로 가장 느리다. 특히 레코드가 많아지면 임시 테이블이 디스크에 저장되면서, 대규모 서비스에서 치명적인 I/O 부하가 발생한다.

 

이 경우 Application Layer나 Presentation Lyaer에서 정렬을 해서 뷰에 노출하는 것을 고려할 수 있다.

 

---

Reference

• https://weicomes.tistory.com/155
• https://leedo1982.github.io/wiki/Mysql_6_3_mysql_main_process/