MySQL이 statement처리 중에 internal temporary table(내부 임시 테이블)을 작성하는 경우에 대하여 알아보자.
Internal Temporary Table이란
- Statement처리 중 최적화 또는 결과를 저장해두기 위해 내부적으로 작성되는 테이블이다. 따라서 유저가 직접 컨트롤 할 수 있는 테이블이 아니다.
- 내부 템프 테이블을 사용하는 쿼리를 실행하면 자동으로 생성되고, 종료 시 자동으로 삭제된다.
- 처음은 메모리 위에 작성되고, 처리해야 할 양이 많아지고 설정 해놓은 메모리 사용량을 초과하면 자동으로 디스크 상의 테이블로 변환된다.
Internal Temporary Table을 작성하는 조건
- UNION을 사용하는 경우
- ORDER BY구문과 다른 컬럼으로 GROUP BY구문을 사용하는 경우
- 복수의 테이블을 갱신하는 경우
- GROUP_CONCAT() 또는 COUNT(DISTINCT)를 사용하는 경우
- ORDER BY구문과 조합한 DISTINCT를 사용하는 경우
- 인라인뷰를 사용하는 경우
- 서브쿼리 또는 세미조인의 materialization용으로 작성된 테이블이 있는 경우
- 동일한 테이블 간의 INSERT ... SELECT
- 윈도우 함수를 사용하는 경우
- CTE(common table expression)를 사용하는 경우
확인 방법
실행계획(EXPLAIN)을 보면 내부 임시 테이블을 작성 했는 지 확인할 수 있다.
실행계획(EXPLAIN)을 보면 내부 임시 테이블을 작성 했는 지 확인할 수 있다.
- Extra열에 Using temporary가 표시되는 경우
- select_type열에 DRIVED가 표시되는 경우
- select_type열에 SUBQUERY 또는 MATERIALIZE가 표시되는 경우
여러가지 패턴이 있으므로 EXPLAIN으로 정확한 정보를 얻는 건 어렵지만, 일반적으로 위와 같이 생각해두면 좋다.
UNION을 사용한 쿼리
mysql> EXPLAIN SELECT id FROM t0 UNION SELECT id FROM t1; +-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+ | 1 | PRIMARY | t0 | NULL | index | NULL | id | 5 | NULL | 491096 | 100.00 | Using index | | 2 | UNION | t1 | NULL | index | NULL | id | 5 | NULL | 491096 | 100.00 | Using index | | NULL| UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
파생테이블
mysql> EXPLAIN SELECT t0.id FROM t0 JOIN (SELECT distinct id FROM t1) t1 USING(id); +----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 80597 | 100.00 | NULL | | 1 | PRIMARY | t0 | NULL | ref | id | id | 8 | t1.id | 15 | 100.00 | NULL | | 2 | DERIVED | t1 | NULL | index | id | id | 8 | NULL | 80597 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
세미조인의 materialization
mysql> EXPLAIN t0.id FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+ | 1 | SIMPLE | t0 | NULL | ALL | id | NULL | NULL | NULL | 491096 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8 | t0.id | 1 | 100.00 | NULL | | 2 | MATERIALIZED | t1 | NULL | index | id | id | 8 | NULL | 80597 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+
Storage engine에 대하여
내부 임시 테이블에 사용되는 스토레지 엔진은 MySQL버전에 따라 다르다.
MySQL 5.6 이하
- In-Memory temporary table: MEMORY storage engine
- Disk temporary table: MyISAM storage engine
In-Memory tmp table의 허용치는 tmp_table_size, max_heap_table_size 파라미터 중 작은 값이고, 초과 시 디스크를 사용한다.
MySQL 5.7
- In-Memory temporary table: MEMORY storage engine
- Disk temporary table: MyISAM 또는 InnoDB storage engine
디스크 임시 테이블의 Default storage engine이 InnoDB로 변경되었다.
체감상 InnoDB가 퍼포먼스가 좋았으나, 사용하는 데 주의해야할 점이 있다
체감상 InnoDB가 퍼포먼스가 좋았으나, 사용하는 데 주의해야할 점이 있다
- InnoDB 제한에 의한 쿼리 에러 발생
- InnoDB temporary table용 테이블 스페이스 사이즈 증가
1.은 InnoDB page size가 16KB인 경우 행 사이즈 맥시멈이 약 8000Byte으로, 초과 시 Row size too large 또는
Too many columns 에러가 발생하는 경우가 있다
Too many columns 에러가 발생하는 경우가 있다
2.은 디스크 임시 테이블을 사용한 쿼리가 종료된 후에도 ibtmp1에 해당 데이터가 남아있어 디스크 부족현상이 일어날 수 있으며, mysqld를 재기동 하지 않는 이상 줄어들지 않는다.
mysql>SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend |
+------------------------------+이에 대한 회선책은 autoextend가 아닌 max_file_size를 설정하는 방법과 디스크 임시 테이블을 MyISAM으로 변경하는 방법이 있다(온라인으로 변경 가능)
MySQL 8.0
8.0 부터는 새로운 TempTable storage engine이 등장했고 Disk temporary table MyISAM이 폐지되었다.
internal_tmp_mem_storage_engine과 temptable_use_mmap파라미터에 따라서 사용되는 스토레지 엔진이 다르다
1. internal_tmp_mem_storage_engine=TempTable, temptable_use_mmap=ON인 경우
In-Memory temporary table: TempTable storage engine
Disk temporary table: TempTable storage engine
2. internal_tmp_mem_storage_engine=TempTable, temptable_use_mmap=OFF인 경우
In-Memory temporary table: TempTable storage engine
Disk temporary table: InnoDB storage engine
3. internal_tmp_mem_storage_engine=Memory
In-Memory temporary table: MEMORY storage engine
Disk temporary table: InnoDB storage engine
위 같이 세 종류가 있으며 Default는 1이다.
8.0 또한 InnoDB 디스크 임시 테이블을 채용하고 있지만, 8.0.13부터는 ibtmp1가 비대화되는 현상이 수정되었다
# Refer
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html
댓글 없음:
댓글 쓰기