MySQL 임시 테이블에 대한 심층적인 이해-MySQL 튜토리얼-php.cn

개요

MySQL에는 외부 임시 테이블과 내부 임시 테이블을 포함하여 두 가지 주요 임시 테이블 유형이 있습니다. 외부 임시 테이블은 create temporary table.... 문을 통해 생성된 임시 테이블입니다. 임시 테이블은 이 세션에서만 유효하며, 세션 연결이 끊어진 후 임시 테이블 데이터는 자동으로 지워집니다. 내부 임시 테이블에는 크게 두 가지 유형이 있는데, 하나는 information_schema에 있는 임시 테이블이고, 다른 하나는 실행 계획에 "임시 사용"이 포함된 경우 임시 테이블이 생성됩니다. 내부 임시 테이블과 외부 임시 테이블의 한 가지 차이점은 내부 임시 테이블의 테이블 구조 정의 파일을 볼 수 없다는 것입니다. 외부 임시 테이블의 테이블 정의 파일 frm은 일반적으로 #sql{process id}_{thread id}_serial number로 구성되므로 서로 다른 세션에서 동일한 이름의 임시 테이블을 생성할 수 있습니다.

임시 테이블

임시 테이블과 일반 테이블의 주요 차이점은 인스턴스, 세션 또는 명령문이 종료된 후 데이터가 자동으로 정리되는지 여부입니다. 예를 들어 내부 임시 테이블에서 쿼리 중에 중간 결과 집합을 저장하려는 경우 사용자 테이블 구조 및 데이터에 영향을 주지 않고 쿼리가 완료된 후 임시 테이블이 자동으로 재활용됩니다. 또한, 서로 다른 세션의 임시 테이블은 동일한 이름을 가질 수 있습니다. 여러 세션에서 쿼리를 실행할 때 임시 테이블을 사용하려는 경우 이름이 중복될 염려가 없습니다. 5.7에서 임시 테이블스페이스가 도입된 이후에는 모든 임시 테이블이 임시 테이블스페이스(비압축)에 저장되고 임시 테이블스페이스의 데이터를 재사용할 수 있습니다. 임시 테이블은 Innodb 엔진뿐만 아니라 myisam 엔진, 메모리 엔진 등도 지원합니다. 따라서 임시 테이블에서는 엔터티(idb 파일)를 볼 수 없으나 반드시 메모리 테이블일 필요는 없고 임시 테이블스페이스에 저장될 수도 있다.

임시 테이블 VS 메모리 테이블

임시 테이블은 innodb 엔진 테이블 또는 메모리 엔진 테이블일 수 있습니다. 여기서 소위 메모리 테이블은 테이블 생성 명령문 create table...engine=memory를 통해 모든 데이터가 메모리에 있습니다. 동일한 내부 메모리 엔진의 경우. 테이블에서 frm 파일을 볼 수 없으며 디스크의 information_schema 디렉터리도 볼 수 없습니다. MySQL 내에서 information_schema의 임시 테이블에는 innodb 엔진의 임시 테이블과 메모리 엔진의 임시 테이블이라는 두 가지 유형이 포함됩니다. 예를 들어, TABLES 테이블은 메모리 임시 테이블에 속하고 열과 프로세스 목록은 innodb 엔진 임시 테이블에 속합니다. 메모리 테이블의 모든 데이터는 메모리에 있습니다. 메모리의 데이터 구조는 배열(힙 테이블)입니다. 모든 데이터 작업은 메모리에서 완료됩니다. 데이터 볼륨이 작은 경우 속도가 상대적으로 빠릅니다(물리적 IO 작업 없음). 참여하고 있습니다). 하지만 메모리는 결국 제한된 리소스이므로 데이터 양이 상대적으로 많은 경우에는 메모리 테이블을 사용하는 것이 적합하지 않으며 대신 이 임시 테이블은 B+를 사용합니다. 트리 스토리지 구조(innodb 엔진) Innodb 버퍼풀 리소스는 공유되며 임시 테이블의 데이터는 버퍼풀의 핫 데이터에 특정 영향을 미칠 수 있습니다. 또한 작업에 물리적 IO가 포함될 수 있습니다. 메모리 엔진 테이블은 실제로 Btree 인덱스 및 Hash 인덱스를 포함한 인덱스를 생성할 수 있으므로 쿼리 속도가 매우 빠릅니다. 주요 단점은 제한된 메모리 리소스입니다.

임시 테이블 사용 시나리오

앞서 언급했듯이 실행 계획에 "임시 테이블 사용"이 포함된 경우 임시 테이블이 사용됩니다.

테스트 테이블 구조는 다음과 같습니다.

mysql> show create table t1_normal\G
*************************** 1. row ***************************
       Table: t1_normal
Create Table: CREATE TABLE `t1_normal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

로그인 후 복사

시나리오 1: Union

mysql> explain select * from t1_normal union select * from t1_normal; 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

로그인 후 복사

Union 연산의 의미는 두 하위 쿼리 결과의 Union을 취하고 임시 데이터를 생성하여 한 행만 유지하는 것입니다. 기본 키가 있는 테이블을 사용하면 "중복 제거" 문제를 해결하기 위해 최종 결과 집합을 임시 테이블에 저장하므로 실행 계획의 Extra 항목에서 "임시 사용"을 확인할 수 있습니다. Union과 관련된 작업은 Union All이며, 이는 두 하위 쿼리의 결과도 병합하지만 중복 문제를 해결하지는 않습니다. 따라서 Union All의 경우 "중복 제거"의 의미가 없으므로 임시 테이블이 필요하지 않습니다.

mysql> explain select * from t1_normal  union  all select * from t1_normal;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | PRIMARY     | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
|  2 | UNION       | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

로그인 후 복사

시나리오 2: group by

mysql> explain select c1,count(*) as count from t1_normal group by c1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

로그인 후 복사

group by는 지정된 열을 기준으로 그룹화하는 것을 의미하며 기본적으로 지정된 열을 기준으로 정렬됩니다. 위 SQL 문의 의미는 t1_normal의 데이터를 c1 컬럼의 값으로 그룹화하고 c1의 각 컬럼 값에 대한 레코드 수를 계산하는 것입니다. 실행 계획에서 "Using temporary;Using filesort"를 참조하세요. 그룹 기준의 경우 먼저 각 값의 발생 횟수를 계산해야 합니다. 이를 위해서는 해당 값이 존재하지 않는 경우 임시 테이블을 사용해야 합니다. , 레코드가 있으면 삽입하고 개수가 누적되므로 "임시 사용"이 표시되고 그룹 기준은 정렬을 의미하므로 c1 열에 따라 레코드를 정렬해야 하므로 "파일 정렬 사용"이 표시됩니다.

1) 파일 정렬 제거

실제로 그룹별은 "정렬 의미" 제거도 표시할 수 있습니다.

mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

로그인 후 복사

명령문에 "order by null"을 추가한 후 실행 계획에 "Using filesort"가 더 이상 나타나지 않는 것을 볼 수 있습니다.

2) 임시 테이블을 제거하세요

mysql> explain select SQL_BIG_RESULT c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

로그인 후 복사

可以看到执行计划中已经没有了“Using temporary”，所以group by并非一定依赖临时表，临时表在group by中的作用主要是“去重”。所以，实际上有另外一种方式，不使用临时表，直接利用sort_buffer排序(sort_buffer不够时，进行文件排序，具体而言是每一个有序数组作为一个单独文件，然后进行外排归并)，然后再扫描得到聚合后的结果集。

3).SQL_BIG_RESULT

同时我们语句中用到了“SQL_BIG_RESULT”这个hint，正是因为这个hint导致了我们没有使用临时表，先说说SQL_BIG_RESULT和SQL_SMALL_RESULT的含义。

SQL_SMALL_RESULT：显示指定用内存表(memory引擎)

SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)

两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量。下面是从MySQL手册中摘录的说明。

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively.

For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements.

For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting.

This should not normally be needed.

回到问题本身，这里MySQL优化器根据hint知道需要使用磁盘临时表，而最终直接选择了数组存储+文件排序这种更轻量的方式。

如何避免使用临时表

通常的SQL优化方式是让group by 的列建立索引，那么执行group by时，直接按索引扫描该列，并统计即可，也就不需要temporary和filesort了。

mysql> alter table t1_normal add index idx_c1(c1);
Query OK, 0 rows affected (1 min 23.82 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1_normal | NULL       | index | idx_c1        | idx_c1 | 5       | NULL | 523848 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

로그인 후 복사