MySQL 또는 MaraiDB 엔진이 스토리지 엔진으로부터 받은 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시테이블을 사용하게 된다.
일반 적으로 MariaDB 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지만 디스크로 옮겨진다.
임시테이블이 필요한 쿼리
1. ORDER BY 와 GROUP BY 에 명시된 컬럼이 다른 쿼리
2. ORDER BY 나 GROUP BY 에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 경우
3. DISTINCT 와 ORDER BY 가 동시에 쿼리에 존재하는 경우 또는 DISTINCT 가 인덱스로 처리되지 못하는 경우
4. UNION 이나 UNION DISTINCT 가 사용된 쿼리(select_type 컬럼이 UNION RESULT인 경우)
5. UNION ALL 이 사용된 쿼리(select_type 컬럼이 UNION RESULT 인 경우)
6. 쿼리의 실행 계획에서 select_type 이 DERIVED 인 쿼리1번부터 4번까지는 유니크 인덱스를 가진 내부 임시테이블이 만들어진다. 5번과 6번은 인덱스가 없는 내부 임시 테이블이 만들어진다. 일반적으로 유니크 인덱스가 있는 내부 임시테이블이 처리능력이 더 좋다.
임시테이블 관련 상태 변수
explain Extra 에 Using temporary 가 한번 표시됐다고 해서 임시 테이블을 하나만 사용했다는 것을 의미 하지 않는다. 임시 테이블이 메모리나 디스크에 생성됐는지 확인하려면 MariaDB 서버의 상태변수 (SHOW SESSION STATUS LIKE 'Created_tmp%';) 확인하면된다.
인데스를 가지는 내부 임시 테이블
MySQL 5.5, MariaDB 5.2 버전까지는 내부 임시 테이블은 항상 인덱스가 없이 생성됐다.
그래서 내부 임시 테이블(DERIVED)이 드라이빙이 아니라 드리븐 테이블로 사용되거나 검색용으로 사용되는 경우에는 성능이 저하됐다. 이후 버전부터는 자동으로 인덱스를 추가한 상태로 임시 테이블을 생성한다.
위의 explain 에서 key 필드에 출력된 auto_key0은 dept_name 에 인덱스를 생성했다는 것을 의미 한다.
내부 임시 테이블(Internal Temporary Table)의 주의사항
SELECT * FROM employees GROUP BY last_name ORDER BY first_name;위 쿼리는 GROUP BY 와 ORDER BY 칼럼이 다르다. 그리고 last_name 에 인덱스가 없다고 하면 임시 테이블과 정렬작업까지 수정해야 하는 쿼리 형태이다.
내부 작업 과정은 다음과 같다.
1. employees 테이블의 모든 컬럼을 포함한 임시테이블 생성(MEMORY 테이블)
2. employees 테이블로부터 첫 번째 레코드를 innoDB 스토리지 엔진으로부터 가져와서
3. 임시 테이블에 같은 last_name 이 있는지 확인
4. 같은 last_name 없으면 임시 테이블에 insert
5. 같은 last_name 있으면 임시 테이블에 update 또는 무시
6. 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블을 Aria 스토리지 엔진을 이용해 서 디스크로 이동
7. employess 테이블에서 더 읽을 레코드가 없을 때까지 2~5번 과정 반복
8. 최종 내부 임시 테이블에 저장된 결과에 대해 정렬작업을 수행
9. 클라이언트에 결과 반환여기서 중요한점은 가능한 인덱스를 사용해 처리하고, 임시 테이블을 생성하지 않도록 처리해야한다. 또한, 임시 테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성되는 경우도 주의 해야한다. SELECT 절의 컬럼은 최소화하면서 BLOB나 TEXT컬럼은 배제, 데이터 타입 선정도 가능한 적게해주는것이 좋다.
TIP
쿼리중에서 FROM 절 사용되는 서브 쿼리는 무조건 임시 테이블을 생성하므로 주의해야한다. 또한, UNION, UNION ALL 이 사용된 쿼리도 항상 임시테이블을 생성하여 결과를 병합한다.
인덱스를 사용하지 못하는 정렬 작업은 임시 버퍼 공간을 사용하는데, 정렬할 레코드가 많아지면 결국 디스크 자원을 사용한다. 정렬에 사용되는 버퍼도 결국 임시 테이블과 같다.
