10.4.4 Internal Temporary Table Use in MySQL

10.4.4 Internal Temporary Table Use in MySQL

어떤 경우에는 서버가 구문을 처리하는 동안 내부 임시 테이블을 생성합니다. 사용자는 이러한 일이 언제 발생하는지에 대해 직접적으로 제어할 수 없습니다.

서버는 다음과 같은 조건에서 임시 테이블을 생성합니다:

• 뒤에서 설명하는 몇 가지 예외를 제외하고, UNION 구문을 평가할 때.

• TEMPTABLE 알고리즘, UNION 또는 집계를 사용하는 뷰를 평가할 때.

• 파생 테이블을 평가할 때 (참조: Section 15.2.15.8, “Derived Tables”).

• 공통 테이블 식을 평가할 때 (참조: Section 15.2.20, “WITH (Common Table Expressions)”).

• 서브쿼리 또는 세미조인 머티리얼라이제이션을 위해 생성되는 테이블의 경우 (참조: Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”).

• ORDER BY 절과 다른 GROUP BY 절을 모두 포함하거나, ORDER BY 또는 GROUP BY가 조인 큐의 첫 번째 테이블이 아닌 다른 테이블의 컬럼을 포함하는 구문을 평가할 때.

• ORDER BY와 결합된 DISTINCT를 평가할 때 임시 테이블이 필요할 수 있습니다.

• SQL_SMALL_RESULT 수정자를 사용하는 쿼리의 경우, 이후 설명되는 디스크 기반 저장소가 필요한 요소도 쿼리에 포함되어 있지 않다면, MySQL은 메모리 내 임시 테이블을 사용합니다.

• 동일한 테이블에서 select와 insert를 모두 수행하는 INSERT ... SELECT 구문을 평가하기 위해, MySQL은 SELECT에서 반환된 행을 보관하는 내부 임시 테이블을 생성한 다음, 해당 행을 대상 테이블에 insert합니다. 참조: Section 15.2.7.1, “INSERT ... SELECT Statement”.

• 다중 테이블 UPDATE 구문을 평가할 때.

• GROUP_CONCAT() 또는 COUNT(DISTINCT) 식을 평가할 때.

• 윈도 함수 를 평가할 때 (참조: Section 14.20, “Window Functions”) 필요에 따라 임시 테이블을 사용합니다.

어떤 구문에 임시 테이블이 필요한지 판단하기 위해서는 EXPLAIN을 사용하고 Extra 컬럼에 Using temporary가 표시되는지 확인합니다 (참조: Section 10.8.1, “Optimizing Queries with EXPLAIN”). 파생 또는 머티리얼라이즈된 임시 테이블의 경우, EXPLAIN에 반드시 Using temporary가 표시되는 것은 아닙니다. 윈도 함수를 사용하는 구문의 경우, FORMAT=JSON을 사용하는 EXPLAIN은 윈도잉 단계에 대한 정보를 항상 제공합니다. 윈도 함수가 임시 테이블을 사용하는 경우, 각 단계마다 그 사실이 표시됩니다.

일부 쿼리 조건으로 인해 메모리 내 임시 테이블을 사용할 수 없게 되면, 서버는 대신 디스크 기반 테이블을 사용합니다:

• 테이블에 BLOB 또는 TEXT 컬럼이 존재하는 경우. 메모리 내 내부 임시 테이블의 기본 스토리지 엔진인 TempTable 스토리지 엔진은 바이너리 대용량 객체 타입을 지원합니다. 참조: Internal Temporary Table Storage Engine.

• SELECT 목록에 최대 길이가 512보다 큰 문자열 컬럼(바이너리 문자열의 경우 바이트, 비바이너리 문자열의 경우 문자)이 존재하고, UNION 또는 UNION ALL이 사용되는 경우.

• SHOW COLUMNS 및 DESCRIBE 구문은 일부 컬럼에 대해 타입으로 BLOB을 사용하므로, 결과에 사용되는 임시 테이블은 디스크 기반 테이블입니다.

서버는 특정 조건을 만족하는 UNION 구문에 대해 임시 테이블을 사용하지 않습니다. 대신, 임시 테이블 생성을 위해 필요한 것 중 결과 컬럼 타입캐스팅을 수행하는 데 필요한 데이터 구조만 유지합니다. 테이블은 완전하게 인스턴스화되지 않고, 그 어떤 행도 그 테이블에 쓰이거나 읽히지 않습니다. 행은 직접 클라이언트로 전송됩니다. 그 결과, 메모리와 디스크 요구 사항이 줄어들고, 마지막 쿼리 블록이 실행될 때까지 서버가 기다릴 필요가 없기 때문에 첫 번째 행이 클라이언트로 전송되기까지의 지연도 줄어듭니다. EXPLAIN 및 옵티마이저 트레이스 출력은 이 실행 전략을 반영합니다: UNION RESULT 쿼리 블록은 표시되지 않습니다. 이 블록은 임시 테이블에서 읽어들이는 부분에 해당하기 때문입니다.

다음 조건은 임시 테이블 없이 UNION을 평가할 수 있는 자격을 부여합니다:

• 유니온이 UNION 또는 UNION DISTINCT가 아니라 UNION ALL인 경우.

• 전역 ORDER BY 절이 없는 경우.

• 유니온이 {INSERT | REPLACE} ... SELECT ... 구문의 최상위 레벨 쿼리 블록이 아닌 경우.

Internal Temporary Table Storage Engine

내부 임시 테이블은 메모리에 보관되며 TempTable 또는 MEMORY 스토리지 엔진에 의해 처리되거나, InnoDB 스토리지 엔진에 의해 디스크에 저장될 수 있습니다.

Storage Engine for In-Memory Internal Temporary Tables

internal_tmp_mem_storage_engine 변수는 메모리 내 내부 임시 테이블에 사용되는 스토리지 엔진을 정의합니다. 허용되는 값은 TempTable(기본값)과 MEMORY입니다.

참고

internal_tmp_mem_storage_engine 에 대한 세션 설정을 구성하려면, SESSION_VARIABLES_ADMIN 또는 SYSTEM_VARIABLES_ADMIN 권한이 필요합니다.

TempTable 스토리지 엔진은 VARCHAR 및 VARBINARY 컬럼과 그 밖의 바이너리 대용량 객체 타입 컬럼에 대해 효율적인 저장을 제공합니다.

다음 변수들은 TempTable 스토리지 엔진의 한계와 동작을 제어합니다:

• tmp_table_size: TempTable 스토리지 엔진을 사용하여 생성되는 개별 메모리 내 내부 임시 테이블이 가질 수 있는 최대 크기를 정의합니다. tmp_table_size로 결정되는 한계에 도달하면, MySQL은 메모리 내 내부 임시 테이블을 InnoDB 디스크 기반 내부 임시 테이블로 자동 변환합니다. 기본 값은 16777216바이트(16 MiB)입니다.

tmp_table_size 한계는 개별 쿼리가 과도한 양의 전역 TempTable 리소스를 소비하는 것을 방지하기 위한 것으로, 이러한 리소스를 필요로 하는 동시 쿼리의 성능에 영향을 줄 수 있습니다. 전역 TempTable 리소스는 temptable_max_ram 및 temptable_max_mmap에 의해 제어됩니다.

tmp_table_size가 temptable_max_ram보다 작은 경우, 메모리 내 임시 테이블이 tmp_table_size보다 많은 용량을 사용하는 것은 불가능합니다. tmp_table_size가 temptable_max_ram과 temptable_max_mmap의 합보다 큰 경우, 메모리 내 임시 테이블은 temptable_max_ram과 temptable_max_mmap 한계의 합보다 많은 용량을 사용할 수 없습니다.

• temptable_max_ram: TempTable 스토리지 엔진이 메모리 매핑 파일에서 공간을 할당하기 시작하거나, 설정에 따라 MySQL이 InnoDB 디스크 기반 내부 임시 테이블을 사용하기 시작하기 전에 사용할 수 있는 RAM의 최대량을 정의합니다. 명시적으로 설정되지 않은 경우, temptable_max_ram의 값은 서버에서 사용 가능한 전체 메모리의 3%이며, 최소 1GB, 최대 4GB입니다.

참고

temptable_max_ram은 TempTable 스토리지 엔진을 사용하는 각 스레드에 대해 할당되는 스레드 로컬 메모리 블록을 계산에 포함하지 않습니다. 스레드 로컬 메모리 블록의 크기는 해당 스레드의 첫 번째 메모리 할당 요청 크기에 따라 달라집니다. 요청이 1MB보다 작은 경우(대부분의 경우 여기에 해당), 스레드 로컬 메모리 블록 크기는 1MB입니다. 요청이 1MB보다 큰 경우, 스레드 로컬 메모리 블록은 초기 메모리 요청과 거의 동일한 크기입니다. 스레드 로컬 메모리 블록은 스레드 종료 시까지 스레드 로컬 저장소에 유지됩니다.

• temptable_max_mmap: TempTable 스토리지 엔진이 InnoDB 디스크 기반 내부 임시 테이블을 사용하기 시작하기 전에 메모리 매핑 파일에서 할당할 수 있는 메모리의 최대량을 설정합니다. 기본 값은 0(비활성화)입니다. 이 한계는 임시 디렉터리( tmpdir)에서 메모리 매핑 파일이 너무 많은 공간을 사용하는 위험을 줄이기 위한 것입니다. temptable_max_mmap = 0은 메모리 매핑 파일에서의 할당을 비활성화하며, 사실상 그 사용을 비활성화합니다.

TempTable 스토리지 엔진에 의한 메모리 매핑 파일 사용은 다음 규칙에 의해 관리됩니다:

• 임시 파일은 tmpdir 변수로 정의된 디렉터리에 생성됩니다.

• 임시 파일은 생성 및 open된 직후 삭제되므로, tmpdir 디렉터리에서 계속 보이지 않습니다. 임시 파일이 open되어 있는 동안에는 운영체제가 임시 파일이 차지하는 공간을 유지합니다. 임시 파일이 TempTable 스토리지 엔진에 의해 close되거나 mysqld 프로세스가 종료되면 해당 공간이 회수됩니다.

• 데이터는 RAM과 임시 파일 간, RAM 내에서, 또는 임시 파일 간에 이동되지 않습니다.

• temptable_max_ram에 의해 정의된 한계 내에서 공간이 사용 가능해지면 새로운 데이터는 RAM에 저장됩니다. 그렇지 않으면, 새로운 데이터는 임시 파일에 저장됩니다.

• 테이블 데이터의 일부가 임시 파일에 기록된 이후 RAM에서 사용 가능한 공간이 생긴 경우, 나머지 테이블 데이터가 RAM에 저장될 수 있습니다.

메모리 내 임시 테이블에 MEMORY 스토리지 엔진을 사용하는 경우 (internal_tmp_mem_storage_engine=MEMORY), 메모리 내 임시 테이블이 너무 커지면 MySQL은 이를 자동으로 디스크 기반 테이블로 변환합니다. 메모리 내 임시 테이블의 최대 크기는 tmp_table_size와 max_heap_table_size 값 중 더 작은 값으로 정의됩니다. 이는 CREATE TABLE로 명시적으로 생성된 MEMORY 테이블과는 다릅니다. 그러한 테이블의 경우 테이블이 얼마나 크게 성장할 수 있는지는 max_heap_table_size 변수만이 결정하며, 디스크 기반 형식으로의 변환은 없습니다.

Storage Engine for On-Disk Internal Temporary Tables

MySQL 9.5는 디스크 기반 내부 임시 테이블에 대해 InnoDB 스토리지 엔진만 사용합니다. (MYISAM 스토리지 엔진은 더 이상 이 목적에 사용되지 않습니다.)

InnoDB 디스크 기반 내부 임시 테이블은 기본적으로 데이터 디렉터리에 존재하는 세션 임시 테이블스페이스에 생성됩니다. 더 자세한 내용은 Section 17.6.3.5, “Temporary Tablespaces”를 참조하십시오.

Internal Temporary Table Storage Format

메모리 내 내부 임시 테이블이 TempTable 스토리지 엔진에 의해 관리되는 경우, VARCHAR 컬럼, VARBINARY 컬럼 및 기타 바이너리 대용량 객체 타입 컬럼을 포함하는 행은 셀 배열 형태로 메모리에 표현되며, 각 셀에는 NULL 플래그, 데이터 길이 및 데이터 포인터가 포함됩니다. 컬럼 값은 패딩 없이 배열 뒤의 단일 메모리 영역에 연속된 순서로 배치됩니다. 배열의 각 셀은 16바이트의 저장 공간을 사용합니다. TempTable 스토리지 엔진이 메모리 매핑 파일에서 공간을 할당할 때도 동일한 저장 형식이 적용됩니다.

메모리 내 내부 임시 테이블이 MEMORY 스토리지 엔진에 의해 관리되는 경우, 고정 길이 행 형식이 사용됩니다. VARCHAR 및 VARBINARY 컬럼 값은 최대 컬럼 길이까지 패딩되며, 결과적으로 CHAR 및 BINARY 컬럼으로 저장됩니다.

디스크 상의 내부 임시 테이블은 항상 InnoDB에 의해 관리됩니다.

MEMORY 스토리지 엔진을 사용하는 경우, 구문은 처음에 메모리 내 내부 임시 테이블을 생성한 후, 해당 테이블이 너무 커지면 이를 디스크 기반 테이블로 변환할 수 있습니다. 이러한 경우, 변환 과정을 건너뛰고 처음부터 내부 임시 테이블을 디스크에 생성하는 것이 더 나은 성능을 제공할 수 있습니다. big_tables 변수를 사용하여 내부 임시 테이블의 디스크 저장을 강제할 수 있습니다.

Monitoring Internal Temporary Table Creation

내부 임시 테이블이 메모리나 디스크에 생성될 때마다, 서버는 Created_tmp_tables 값을 증가시킵니다. 내부 임시 테이블이 디스크에 생성되면, 서버는 Created_tmp_disk_tables 값을 증가시킵니다. 디스크에 생성된 내부 임시 테이블이 너무 많다면, Internal Temporary Table Storage Engine에서 설명하는 엔진별 한계를 조정하는 것을 고려하십시오.

참고

알려진 제한 사항으로 인해, Created_tmp_disk_tables은 메모리 매핑 파일에서 생성된 디스크 기반 임시 테이블을 계산하지 않습니다. 기본적으로 TempTable 스토리지 엔진 오버플로 메커니즘은 메모리 매핑 파일에 내부 임시 테이블을 생성합니다. 참조: Internal Temporary Table Storage Engine.

memory/temptable/physical_ram 및 memory/temptable/physical_disk Performance Schema 인스트루먼트를 사용하여 메모리와 디스크에서의 TempTable 공간 할당을 모니터링할 수 있습니다. memory/temptable/physical_ram은 할당된 RAM의 양을 보고합니다. memory/temptable/physical_disk는 TempTable 오버플로 메커니즘으로 메모리 매핑 파일을 사용하는 경우 디스크에서 할당된 공간의 양을 보고합니다. physical_disk 인스트루먼트가 0이 아닌 값을 보고하고 TempTable 오버플로 메커니즘으로 메모리 매핑 파일이 사용되는 경우, 어느 시점에서 TempTable 메모리 한계에 도달했음을 의미합니다. 데이터는 memory_summary_global_by_event_name과 같은 Performance Schema 메모리 요약 테이블에서 쿼리할 수 있습니다. 참조: Section 29.12.20.10, “Memory Summary Tables”.

Monitoring Internal Temporary Table Conversion

내부 임시 테이블이 메모리 내에서 디스크 기반으로 변환되면, 서버는 이러한 변화를 추적하기 위해 시스템 상태 변수를 증가시킵니다:

• TempTable_count_hit_max_ram 은 temptable_max_ram 한계에 도달했을 때 증가합니다. 이는 TempTable 스토리지 엔진에 특화되어 있으며, 전역 상태 변수입니다.

• Count_hit_tmp_table_size 는 다음 조건에서 증가합니다:

  • TempTable 스토리지 엔진: tmp_table_size 한계에 도달한 경우.

  • MEMORY 스토리지 엔진: tmp_table_size와 max_heap_table_size 중 더 작은 한계 값에 도달한 경우.

이는 전역 및 세션 상태 변수입니다.