TIL

9.2(4) 내부 임시 테이블 활용

9.2.6 내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진에서 받아온 레코드를 정렬하거나 그루핑할 때 내부적인 임시 테이블을 사용한다.
  • CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과는 다름
• 일반적으로 처음엔 메모리에 생성됐다가 테이블 크기가 커지면 디스크로 옮겨진다.
• 쿼리 처리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

• MySQL 8.0 이전 버전까지는 스토리지 엔진에 관계없이 임시 테이블이 메모리를 사용할 땐 MEMORY 스토리지 엔진을, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.
  • MEMORY 엔진은 VARBINARY나 VARCHAR 같은 가변 길이 타입을 지원하지 못해 메모리 낭비 문제를 갖고 있었다.
  • 임시 테이블을 디스크에 저장할 때 MyISAM의 경우 트랜잭션을 지원하지 못하는 문제가 있었다.
• MySQL 8.0 이후 버전부터 메모리는 TempTable 스토리지 엔진을, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선되었다.
  • TempTable은 가변 길이 타입을 지원한다.
  • internal_tmp_mem_storage_engine 시스템 변수로 메모리용 임시 테이블을 MEMORY와 TempTable 중 선택할 수 있다. (기본값은 TempTable)
  • temptable_max_ram 시스템 변수로 메모리 공간 크기를 제안할 수 있다. (기본값 1GB)
• 임시 테이블 크기가 1GB보다 커지는 경우 MySQL 서버는 임시 테이블을 디스크로 기록하는데 2가지 방법 중 하나를 선택한다.
  • MMAP 파일로 디스크에 기록
    • temptable_use_mmap 시스템 변수의 기본값이 ON이라서 기본적으로 임시 테이블은 MMAP 파일로 디스크에 기록된다.
    • 이는 MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문이다.
  • InnoDB 테이블로 기록
    • 내부 임시 테이블이 처음부터 디스크 테이블로 생성되는 경우가 있는데 이 때 internal_tmp_disk_storage_engine 시스템 변수에 설정되어 있는 엔진을 사용한다. (기본값 InnoDB)

임시 테이블이 필요한 쿼리

• 다음 패턴의 쿼리는 대표적으로 임시 테이블을 생성하는 케이스다.
  1. ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  2. ORDERY BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  3. DISTINCT나 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  4. UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
  5. 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
• 보통 임시 테이블을 사용하면 실행 계획 Extra 칼럼에 Using temporary가 표시되지만 3, 4, 5번은 표시되지 않는다.
• 1~4 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다.
• 4번은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다.
  • 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 상당히 느리다.

임시 테이블이 디스크에 생성되는 경우

• 다음과 같은 조건에선 메모리 임시 테이블을 사용할 수 없어 디스크 기반 임시 테이블을 사용한다.
  • UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블 크기가(MEMORY 엔진에서) tmp_table_size 또는 max_heap_table_size 변수보다 크거나 (TempTable 엔진에서) temptable_max_ram 변수 값보다 큰 경우

임시 테이블 관련 상태 변수

• 실행 계획에서 Using temporary가 표시되어도 임시 테이블이 메모리에서 처리됐는지 디스크에처 처리 됐는지, 몇 개의 임시 테이블이 사용됐는지도 알 수 없다.

• 임시 테이블이 메모리에 생성됐는지 디스크에 생성됐는지 확인하려면 다음과 같이 확인해볼 수 있다.

    -- // 현재 세션 상태 값 초기화
    FLUSH STATUS;
      
    SELECT first_name, last_name
    FROM employees
    GROUP BY first_name, last_name;
      
    SHOW SESSION STATUS LIKE 'Created_tmp%';
      
    |-------------------------|-------|
    | Variable name           | Value |
    |---------------------------------|
    | Created_tmp_disk_tables | 1     |
    | Created_tmp_tables      | 1     |
    |-------------------------|-------|
  

  • Created_tmp_tables: 쿼리 처리를 위해 만들어진 모든 내부 임시 테이블 개수를 누적하는 상태 값.
  • Created_tmp_disk_tables: 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값