내부 임시 테이블 활용

내부적인 임시 테이블(Internal temprary table)

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다.
• 여기서 이야기하는 임시 테이블은 CREATE TEMORARY TABLE 명령으로 만든 임시 테이블과는 다르다.
  • CREATE TEMORARY TABLE 명령으로 만든 임시 테이블은 사용자가 생성한 임시 테이블.
• 일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성되었다가, 테이블의 크기가 커지면 디스크로 옮겨진다.
  • 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어진다.
• MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능하다.
• 사용자가 생성한 임시 테이블과는 달리 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

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

임시 테이블과 스토리지 엔진

• MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 아래처럼 사용했다.
  • 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용
  • 임시 테이블이 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용
• MySQL 8.0 버전부터는 다음과 같이 개선됐다.
  • 메모리는 TempTable이라는 스토리지 엔진을 사용
  • 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용
• 기존 MEMORY 스토리지 엔진은 VARBINARY나 VARCHAR 같은 가변 길이 타입을 지원하지 못하기 때문에, 임시 테이블이 메모리에 만들어지면 가변 길이 타입의 경우 최대 길이만큼 메모리를 할당해서 사용했다.
  • 메모리 낭비가 심해지는 문제점
• MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못한다는 문제점이 있었다.
• 따라서 가변 길이 타입을 지원하는 TempTable 스토리지 엔진과 트랜잭션 지원 가능한 InnoDB 스토리지 엔진(또는 TempTable 스토리지 엔진의 MMAP 파일 버전)이 사용되도록 개선되었다.

메모리 임시 테이블

• MySQL 8.0 버전부터는 internal_tmp_mem_storage_engine 시스템 변수를 이용해 메모리용 임시 테이블을 선택할 수 있다.
  • TempTable (기본값)
  • MEMORY
• TempTable이 최대한 사용 가능한 메모리 공간의 크기는 temptable_max_ram 시스템 변수로 제어할 수 있다.
  • 1GB (기본값)
• 메모리 임시 테이블을 위해 MEMORY 스토리지 엔진을 사용한다면 다음 두 시스템 변수도 같이 적절한 값으로 제어해야 한다.
  • tmp_table_size
  • max_heap_table_size
  • 두 시스템 변수는 MEMORY 스토리지 엔진을 사용하는 임시 테이블에만 적용된다.
• 임시 테이블의 크기가 1GB보다 커지는 경우 MySQL 서버는 다음 2가지 디스크 저장 방식 중 하나를 선택하여 메모리의 임시 테이블을 디스크로 기록한다.
  • MMAP 파일로 디스크에 기록
  • InnoDB 테이블로 기록
• temptable_use_mmap 시스템 변수로 MMAP 파일로 기록할지 InnoDB 테이블로 전환할지 설정할 수 있다.
  • 기본값은 ON 이다.
  • 메모리의 TempTable을 MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문에 기본값은 ON으로 선택됐다.
  • 즉, ON일 경우 메모리의 TempTable 크기가 1GB를 넘으면 메모리의 TempTable을 MMAP 파일로 전환한다.
  • 이때 디스크에 생성되는 임시 테이블은 tmpdir 시스템 변수에 정의된 디렉터리에 저장된다.

💡 디스크에 저장된 임시 테이블이 저장되는 파일

MySQL 서버는 디스크의 임시 테이블을 생성할 때, 파일 오픈 후 즉시 파일 삭제를 실행하고, 데이터를 저장하기 위해 해당 임시 테이블을 사용한다.

그러면 MySQL 서버가 종료되거나 해당 쿼리가 종료되면 임시 테이블은 즉시 사라진다.

또한 MySQL 서버 내부의 다른 스레드 또는 MySQL 서버 외부의 사용자가 임시 테이블을 위한 파일을 변경 및 삭제하거나 볼 수 없게 한다.

리눅스나 유닉스 계열의 운영체제에서는 임시 테이블이 몇 개나 사용되는지 정도는 확인할 수 있는데, 이때 파일의 상태는 ‘deleted’로 표시될 것이다.

디스크 임시 테이블

• 내부 임시 테이블이 처음부터 디스크 테이블로 생성되는 경우도 있다.
  • internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용된다.
  • 기본값은 innoDB다.

임시 테이블이 필요한 쿼리

• 다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로, 대표적으로 내부 임시 테이블을 생성하는 케이스다.
  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  • ORDER BY와 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블(드라이빙 테이블을 말하는 듯하다)이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리 (select_type 칼럼이 UNION RESULT인 경우)
  • 쿼리의 실행 계획에서 select_type 이 DERIVED인 쿼리
• 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많다.
• 어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 칼럼에 “Using temporary”라는 메시지가 표시되는지 확인하면 된다.
  • “Using temporary”가 표시되지 않을 때도 임시 테이블을 사용할 수 있다. → 위의 패턴 중 마지막 3개 패턴
• 일반적으로 유니크 인덱스가 있는 내부 테이블은 그렇지 않은 쿼리보다 처리 성능이 상당히 느리다.
  • 첫 번째부터 네 번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다.
  • 마지막 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다.

Union과 임시 테이블

• MySQL 8.0 이전 버전까지는 UNION ALL이 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)도 항상 내부 임시 테이블을 사용해서 결과를 모은 후 결과를 반환했다.
• MySQL 8.0 버전부터는 UNION ALL을 사용하는 쿼리는 더는 임시 테이블을 사용하지 않게 개선됐다.
• UNION과 UINON DISTINCT는 여러 결과 집합에서 중복을 제거하는 작업이 필요하기 때문에 임시 테이블을 이용한 중복 제거 작업이 필수적이다.
  • MySQL 서버에서 UNION은 DISTINCT를 생략한 것으로 판단하기 때문에, 결과적으로 UNION DISTINCT와 동일한 기능이다.

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

• 내부 임시 테이블은 기본적으로는 메모리상에 만들어지지만, 다음과 같은 조건을 만족하면 메모리 임시 테이블을 사용할 수 없게 되므로 디스크 기반의 임시 테이블을 사용한다.
  • UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 칼럼에서 512 바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블의 크기가
    • (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나
    • (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우
• MySQL 8.0.13 이전 버전까지는 BLOB이나 TEXT 칼럼을 가진 경우, 임시 테이블을 메모리에 생성하지 못하고 디스크에 생성했다.
• MySQL 8.0.13 버전부터는 BLOB이나 TEXT 칼럼을 가진 임시 테이블에 대해서도 메모리에 임시 테이블을 생성할 수 있게 개선됐다.
  • 메모리 임시 테이블이 MEMORY 스토리지 엔진을 사용하는 경우에는 여전히 디스크 임시 테이블을 사용한다.

임시 테이블 관련 상태 변수

• 실행 계획상에서 (Extra 칼럼에) “Using temprorary”가 표시되면 임시 테이블을 사용했다는 사실을 알 수 있다.
• 하지만 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지, 몇 개의 임시 테이블이 사용됐는지 알 수 없다.
• 임시 테이블이 메모리에 생성됐는지 디스크에 생성됐는지 확인하려면 MySQL 서버의 상태 변수를 확인해 보면 된다.

  -- 현재 세션의 상태 값 초기화
  FLUSH STATUS;
  
  -- // SELECT 쿼리 실행
  
  -- 상태 조회 명령 실행
  SHOW SESSION STATUS LIKE 'Created_tmp%';

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

Reference

참고 서적

📔 Real MySQL 8.0