Real MySQL [6-10] 실행계획 - 임시 테이블(Using temporary)

본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다. 

임시 테이블(Using temporary)

---

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑 할 때는 내부적인 임시 테이블을 사용한다. "내부적"이라는 단어가 포함된 것은 여기서 이야기하는 임시 테이블이 CREATE TEMPORARY TABLE 로 만든 임시 테이블과 다르기 때문이다. MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는  MyISAM 스토리지 엔진을 이용한다.

임시 테이블이 필요한 쿼리 

- ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리

- ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리

- DISTINCT와 ORDER BY가 동시에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리

- UNION이나 UNION DISTICT가 사용된 쿼리

- UNION ALL이 사용된 쿼리 

- 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리 

어떤 쿼리의 실행계획이 임시테이블을 사용하는지는 Extra 칼럼의 "Using temporary"라는 키워드가 표시되는지 확인하면 된다. 하지만 "Using temporary"가 표시되지 않을 때도 임시 테이블을 사용할 수 있는데 위의 예시에 마지막 3개의 패턴이 그런 예이다. 1~4번째 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다. 그리고 5, 6번째 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다. 유니크 인덱스가 있는 내부 임시 테이블은 인덱스가 없는 임시 테이블보다 처리 성능이 느리다. 

임시 테이블 관련 상태 변수

실행 계획상에서 "Using temporary"가 표시되면 임시 테이블을 사용했다는 사실을 알 수 있다. 하지만 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지는 알 수 없으며, 몇 개의 임시테이블이 사용됐는지도 알 수 없다. 임시 테이블이 디스크에 생성되었는지 메모리에 생성됐는지를 파악하려면 MySQL 서버의 상태 변수를 확인해 보면 된다.

mysql> SHOW SESSION STATUS LIKE 'Create_tmp%';

위의 명령으로 임시 테이블의 사용 현황을 확인한다. 그리고 SELECT 쿼리를 실행한 후, 다시 상태 조회 명령을 실행해 보면 된다. 두 상태 변수가 누적하는 값의 의미는 다음과 같다

Created_tmp_tables

쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값. 이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지를 구분하지 않고 모두 누적한다.

Create_tmp_disk_tables

디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 값.

임시 테이블 관련 주의사항

성능상의 이슈가 될만한 부분은 내부 임시 테이블이 MyISAM 테이블로 디스크에 생성되는 경우다.

SELECT * FROM employees GROUP BY last_name ORDER BY first_name;

이 쿼리는 GROUP BY와 ORDER BY 컬럼이 다르고, last_name 칼럼에 인덱스가 없기 때문에 임시 테이블과 정렬 작업까지 수행해야 하는 골칫거리가 되는 쿼리 형태다.

처리해야하는 레코드 건수가 30만건 정도가 된다. 이 실행 계획의 내부적인 작업과정은 다음과 같다

1) Employees 테이블의 모든 칼럼을 포함한 임시 테이블을 생성(MEMERY 테이블)

2) Employees 테이블로부터 첫 번째 레코드를 InnoDB 스토리지 엔진으로부터 가져옴

3) 임시 테이블에 같은 last_name이 있는지 확인

4) 같은 last_name 이 없으면 임시 테이블에  INSERT

5) 같은 last_name 이 있으면 임시 테이블에 UPDATE 또는 무시

6) 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블을 MyISAM 테이블로 디스크로 이동

7) Employees 테이블에서 더 읽을 레코드가 없을 때까지 2~6번 과정이 반복

8) 최종 내부 임시 테이블에 저장된 결과에 대해 정렬 작업 수행

9) 클라이언트에 결과 반환 

여기서 중요한것은 디스크에 임시 테이블이 저장된 경우 30만건을 임시 테이블로 저장하면 적지 않은 부하가 발생하라리는 것이다. 

INNER JOIN과 OUTER JOIN은 성능을 고려해서 선택하기보다 업무 요건에 따라 선택하는 것이 바람직하다. 데이터의 정확한 구조를 모르고 OUTER JOIN을 사용하면 얼마 지나지 않아 잘못된 결과가 표시될 것이다.