MySQL8.0中消失又回来的磁盘临时表

ywz888

发布于 2022-8-18 15:37

浏览

0收藏

• 1.引子
• 2.发现问题
• 3.破题
• 4.总结

1.引子

首先，我是一个标题党^_^，写这样的标题就是为了吸引你们来看的。标题中说的磁盘临时表消失，并不是真的消失了，而是在MySQL默认配置下，从8.0.15到8.0.27的版本都不会产生磁盘临时表(8.0.15之前的版本没有做个测试，就不妄下结论了)，在8.0.28及之后的版本中，默认配置又放开了磁盘临时表(文章中未特殊说明时都指内部临时表，非用户创建临时表)的使用，这是什么情况？请容我慢慢道来。

2.发现问题

我的生产环境数据库版本为8.0.25，巡检发现Created_tmp_disk_tables计数器的值非常高，与Created_tmp_tables的比例超过了20%，发现此现象的第一感觉就是系统中有大量占用磁盘临时表的慢查询，处理方法一般就是分析具体慢SQL，进行针对性的优化，或者粗暴些就增大max_heap_table_size、tmp_table_size的值，就能把问题解决了。

但是，使用pt-query-digest分析完慢SQL，我就有点蒙了，慢SQL执行的平均时间、最长执行时间都不足1秒，关于临时表的参数max_heap_table_size设置为32M，tmp_table_size设置为默认值16M，初步判断如果使用了磁盘临时表，那么需要的临时表空间至少要大于16M，那么执行时间是不会这么短的，我又不信邪的把TOP慢SQL都挨个执行了一遍，执行都很快，Created_tmp_disk_tables计数器的值并没有增加。

由于生产环境不能随意操作，我在本地搭建了相同版本的数据库，为了复现磁盘临时表的使用，设置参数tmp_table_size为1024，同时也构造了几个大表关联，中间结果集超过2M的查询，然而也没有让Created_tmp_disk_tables计数器的值增加，这就很奇怪了~ ~!

mysql> show create table t_col \G
*************************** 1. row ***************************
       Table: t_col
Create Table: CREATE TABLE `t_col` (
  `TABLE_CATALOG` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `TABLE_NAME` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `COLUMN_NAME` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `ORDINAL_POSITION` int unsigned NOT NULL,
  `COLUMN_DEFAULT` text CHARACTER SET utf8 COLLATE utf8_bin,
  `IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `DATA_TYPE` longtext CHARACTER SET utf8 COLLATE utf8_bin,
  `CHARACTER_MAXIMUM_LENGTH` bigint DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint DEFAULT NULL,
  `NUMERIC_PRECISION` bigint unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint unsigned DEFAULT NULL,
  `DATETIME_PRECISION` int unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `COLLATION_NAME` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `COLUMN_TYPE` mediumtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `COLUMN_KEY` enum('','PRI','UNI','MUL') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `EXTRA` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `PRIVILEGES` varchar(154) CHARACTER SET utf8 DEFAULT NULL,
  `COLUMN_COMMENT` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `GENERATION_EXPRESSION` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `SRS_ID` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
1 row in set (0.00 sec)

mysql> 
mysql> show create table t_tab \G
*************************** 1. row ***************************
       Table: t_tab
Create Table: CREATE TABLE `t_tab` (
  `TABLE_CATALOG` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `TABLE_NAME` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci DEFAULT NULL,
  `TABLE_TYPE` enum('BASE TABLE','VIEW','SYSTEM VIEW') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ENGINE` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `VERSION` int DEFAULT NULL,
  `ROW_FORMAT` enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `TABLE_ROWS` bigint unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint unsigned DEFAULT NULL,
  `DATA_FREE` bigint unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint unsigned DEFAULT NULL,
  `CREATE_TIME` timestamp NOT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(64) CHARACTER SET utf8,
  `CHECKSUM` bigint DEFAULT NULL,
  `CREATE_OPTIONS` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `TABLE_COMMENT` text CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> select count(1) from t_tab;
+----------+
| count(1) |
+----------+
|      687 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from t_col;
+----------+
| count(1) |
+----------+
|    86688 |
+----------+
1 row in set (0.03 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 258     |
| Created_tmp_files       | 1368776 |
| Created_tmp_tables      | 1199    |
+-------------------------+---------+
3 rows in set (0.01 sec)

mysql> select table_name, count(1) from test.t_col join test.t_tab using(table_name) group by table_name order by 2 desc limit 1;
+------------+----------+
| table_name | count(1) |
+------------+----------+
| tables     |     3072 |
+------------+----------+
1 row in set (0.40 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 258     |
| Created_tmp_files       | 1368786 |
| Created_tmp_tables      | 1201    |
+-------------------------+---------+
3 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.

3.破题

3.1大查询不创建磁盘临时表的定位

想不出原因，就翻一下官方文档说明，看看能否有新发现，通过查找参数tmp_table_size链接到MySQL内部临时表的说明手册中https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html，才了解到在8.0中，新增了参数、存储引擎用于处理临时表

MySQL8.0中消失又回来的磁盘临时表-鸿蒙开发者社区

这里引用一下爱可生大佬胡呈清在【技术分享 | MySQL 内部临时表是怎么存放的】的翻译

• internal_tmp_mem_storage_engine：用来指定在内存中的内部临时表的存储引擎，默认值 TempTable，而非以前默认的 MEMORY

• temptable_max_ram：定义 TempTable 存储引擎开始在磁盘上存储数据之前可以占用的最大内存量，默认值1G，此参数是所有session同时能够使用的最大内存，并不是单个session能够使用的最大内存。

• temptable_use_mmap：定义当 TempTable 存储引擎占用的内存量超过 temptable_max_ram 变量定义的限制时，TempTable 存储引擎是否为内存中的内部临时表分配空间作为内存映射的临时文件。禁用 temptable_use_mmap 时，将使用 InnoDB 磁盘内部临时表代替。默认值ON，8.0.16引入，8.0.26弃用(注意：弃用不等于不生效，参数的配置变动是依然生效的，只是在后续某个版本中会将参数删除)。

• temptable_max_mmap：定义 TempTable 存储引擎在开始将数据存储到磁盘上的 InnoDB 内部临时表之前，被允许从内存映射的临时文件分配的最大内存量（以字节为单位）。设置为0将禁用从内存映射的临时文件分配内存。默认值1G，8.0.23引入。同temptable_max_ram一样，此参数也是全局限制参数，并不是session限制参数

上面也说了，我的生产环境数据库版本为8.0.25，上述参数均使用的默认值。也就能够解释为什么我构造2M临时表空间的查询，都无法触发磁盘临时创建的原因，因为我的2M临时表空间需求，远小于temptable_max_ram+temptable_max_mmap的值。

3.2生产磁盘临时表使用高的定位

上面说到，通过官方文档，能够解释为什么构造的大查询未创建临时表，但是生产环境Created_tmp_disk_tables计数器非常高的原因依然未找到。

在我测试环境不停的操作过程中，发现Created_tmp_disk_tables计数器偶尔有少量增加，但是不管是慢日志，还是show processlist，都无法发现执行时间长的SQL，就算把测试环境的慢SQL手动执行，依然不会使Created_tmp_disk_tables增加，感觉快要发现问题了，但是就差那么一点点无法勘破。

此时我想到了performance_schema.events_statements_history表，它可以记录每个线程最后执行的10条SQL，虽然能够记录的SQL条数很少，但是我测试环境的就我一个人访问，尝试一下从历史SQL中能否发现一些线索。

mysql> select * from performance_schema.events_statements_history where CREATED_TMP_DISK_TABLES > 0 limit 10 \G
*************************** 1. row ***************************
              THREAD_ID: 1151
               EVENT_ID: 82
           END_EVENT_ID: 82
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:94
            TIMER_START: 693632033814279000
              TIMER_END: 693632035332155000
             TIMER_WAIT: 1517876000
              LOCK_TIME: 827000000
               SQL_TEXT: select count(1) from information_schema.INNODB_TABLES
                 DIGEST: 1a5ac5add1f9da986edb5cff05e8c0aaf6f4d90f36618a7197475b1fec4768c6
            DIGEST_TEXT: SELECT COUNT (?) FROM `information_schema` . `INNODB_TABLES`
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 16
CREATED_TMP_DISK_TABLES: 1
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 33643691.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

万幸，它就在那，这个SQL就是我不久前执行过的一些查询，为了确认现象，我又多执行了几次这样的SQL，发现每访问一次，Created_tmp_disk_tables计数器就会增加一次。

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 262     |
| Created_tmp_files       | 1369878 |
| Created_tmp_tables      | 1213    |
+-------------------------+---------+
3 rows in set (0.00 sec)

mysql> select count(1) from information_schema.INNODB_TABLES;
+----------+
| count(1) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 263     |
| Created_tmp_files       | 1369888 |
| Created_tmp_tables      | 1215    |
+-------------------------+---------+
3 rows in set (0.00 sec)

mysql> select count(1) from information_schema.INNODB_TABLES;
+----------+
| count(1) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 264     |
| Created_tmp_files       | 1371134 |
| Created_tmp_tables      | 1217    |
+-------------------------+---------+
3 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

但是我设置的internal_tmp_mem_storage_engine为TempTable，是不应该使用磁盘临时表的，为什么访问这个表又使用上了呢，我对表结构做了查看

mysql> show create table information_schema.INNODB_TABLES \G
*************************** 1. row ***************************
       Table: INNODB_TABLES
Create Table: CREATE TEMPORARY TABLE `INNODB_TABLES` (
  `TABLE_ID` bigint unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(655) NOT NULL DEFAULT '',
  `FLAG` int NOT NULL DEFAULT '0',
  `N_COLS` int NOT NULL DEFAULT '0',
  `SPACE` bigint NOT NULL DEFAULT '0',
  `ROW_FORMAT` varchar(12) DEFAULT NULL,
  `ZIP_PAGE_SIZE` int unsigned NOT NULL DEFAULT '0',
  `SPACE_TYPE` varchar(10) DEFAULT NULL,
  `INSTANT_COLS` int NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> select * from information_schema.tables where table_name='INNODB_TABLES' and TABLE_SCHEMA='information_schema' \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: INNODB_TABLES
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: NULL
        VERSION: 10
     ROW_FORMAT: NULL
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-04-02 14:45:37
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.01 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

从这里，我们可以看到实际上INNODB_TABLES表是一个MEMORY引擎的系统内部临时表，我理解MySQL内部把它的访问策略等同于了internal_tmp_mem_storage_engine=memory，那么内部临时表超过tmp_table_size和max_heap_table_size的较小值，就会转换为磁盘临时表，而我的tmp_table_size设置为了1024，小于访问INNODB_TABLES所需要的内存空间。

The tmp_table_size variable does not apply to user-created MEMORY tables. User-created TempTable tables are not supported.

When using the MEMORY storage engine for internal in-memory temporary tables, the actual size limit is the smaller of tmp_table_size and max_heap_table_size.1.
2.
3.

为了印证所想，我将tmp_table_size值调整为默认值16M，此时再访问PROCESSLIST发现Created_tmp_disk_tables计数器没有增加，基本上确认了上面的猜想。

mysql> select count(1) from information_schema.INNODB_TABLES;
+----------+
| count(1) |
+----------+
|       62 |
+----------+
1 row in set (0.02 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 275     |
| Created_tmp_files       | 1374782 |
| Created_tmp_tables      | 1284    |
+-------------------------+---------+
3 rows in set (0.01 sec)

mysql> set max_heap_table_size=32*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set tmp_table_size=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 275     |
| Created_tmp_files       | 1374824 |
| Created_tmp_tables      | 1285    |
+-------------------------+---------+
3 rows in set (0.00 sec)

mysql> select count(1) from information_schema.INNODB_TABLES;
+----------+
| count(1) |
+----------+
|       62 |
+----------+
1 row in set (0.01 sec)

mysql> show global status like 'created%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 275     |
| Created_tmp_files       | 1374834 |
| Created_tmp_tables      | 1287    |
+-------------------------+---------+
3 rows in set (0.00 sec)

mysql> select @@temptable_max_ram, @@temptable_max_mmap, @@temptable_use_mmap, @@internal_tmp_mem_storage_engine, @@max_heap_table_size, @@tmp_table_size;
+---------------------+----------------------+----------------------+-----------------------------------+-----------------------+------------------+
| @@temptable_max_ram | @@temptable_max_mmap | @@temptable_use_mmap | @@internal_tmp_mem_storage_engine | @@max_heap_table_size | @@tmp_table_size |
+---------------------+----------------------+----------------------+-----------------------------------+-----------------------+------------------+
|          1073741824 |           1073741824 |                    1 | TempTable                         |              33554432 |         16777216 |
+---------------------+----------------------+----------------------+-----------------------------------+-----------------------+------------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.

确认访问INNODB_TABLES会产生磁盘临时表后，又陆续看了其他系统表的表结构，发现除了view，还有两种系统表，engine=InnoDB或者engine=Memory，对比做了实验发现：

• 访问engine=Memory表时，超过min(max_heap_table_size,tmp_table_size)，会产生磁盘临时表，即Created_tmp_disk_tables会增加
• 而访问engine=InnoDB表时，不管访问需求内存多少，都会使Created_tmp_disk_tables会增加，相当于默认使用了InnoDB的磁盘临时表。

与其他同事沟通了解到，在我们的生产环境中，部署了一些定时监控任务访问这些表，进行一些数据收集，因此会定时的产生一批磁盘临时表的访问。而业务SQL使用临时表的存储引擎是TempTable，可以认为目前业务使用的都是内存临时表。

到这里，基本上把开篇生产环境中遇到的问题给弄清楚了。

你以为就完了吗？不，我们接下来开始正题，8.0的磁盘临时表是什么时候消失，什么时候又回来的（主要是实验数据）。

磁盘临时表的变动

上面提到，默认配置参数情况下：

• 从8.0.2开始，MySQL增加了参数internal_tmp_mem_storage_engine、temptable_max_ram，用来控制内部临时表机制。

• 从8.0.16开始，增加参数temptable_use_mmap控制超过temptable_max_ram内存需求时，MySQL如何进行扩展临时表处理

• 从8.0.23开始，增加参数temptable_max_mmap，控制最大内存临时表

• 从8.0.28开始，使用tmp_table_size来控制session级别的最大内存临时表

根据上面的变动，在使用上述参数默认配置的情况下，本人做了多个版本的测试，验证临时表的特性及观察方法。再次申明，文章中的临时表为内部临时表，非用户创建临时表。 MySQL8.0中消失又回来的磁盘临时表-鸿蒙开发者社区

8.0.15

重点说明：internal_tmp_mem_storage_engine=temptable时，如果需要的临时表内存超过了temptable_max_ram设定值，则查询memory_summary_global_by_event_name，可以看到memory/temptable/physical_disk会增加，不超过时memory/temptable/physical_ram增加，总体临时表未发现有大小限制。

此版本默认配置不会产生磁盘临时表

8.0.16、8.0.22

重要说明：和8.0.15相比，增加了参数temptable_use_mmap，如果设置temptable_use_mmap=0，并且internal_tmp_mem_storage_engine=temptable时，最大的临时表限制为temptable_max_ram的参数值，申请超过最大值，会报错ERROR 1114 (HY000): The table '/data/mysql8016/tmp/#sql1b51_4e_6' is full

此版本默认配置不会产生磁盘临时表

8.0.23、8.0.25

重要说明：和8.0.22相比，增加了参数temptable_max_mmap。当参数temptable_use_mmap设置为ON（即默认值）时，最大的临时表限制为temptable_max_ram+temptable_max_mmap的参数值；当参数temptable_use_mmap设置为OFF时，最大的临时表限制为temptable_max_ram的参数值；申请超过最大值，会报错ERROR 1114 (HY000): The table '/data/mysql8016/tmp/#sql1b51_4e_6' is full

此版本默认配置不会产生磁盘临时表

8.0.28

重要说明：和8.0.25相比，通过参数tmp_table_size控制了单个临时表的大小上限，同时通过temptable_max_ram+temptable_max_mmap控制了全局临时表的大小上限，超过后会转换为磁盘临时表，而不会和上面两个版本一样提示空间不足。

此版本默认配置会产生磁盘临时表

8.0.28之前temptable不增加Created_tmp_disk_tables的原因是由于在被临时表使用过程中，是先使用temptable_max_ram空间，再使用temptable_max_mmap空间，如果在使用过程中临时表空间的空间释放，又会变成只使用temptable_max_ram的空间了，估计MySQL的大爷为了避免绕晕，就直接定义这种情况未使用磁盘临时文件，实际上使用了内存映射的临时文件。

而如果使用的是memory引擎，当转化为磁盘临时表后，就算使用过程中空间有释放，也不会再将磁盘临时表转换为内存临时表，因此Created_tmp_disk_tables始终会增加。

max_heap_table_size的作用

关于临时表参数的作用基本上都介绍完了，唯独max_heap_table_size好像没什么存在感，再次翻开了官网，发现它主要是控制用户手动创建的临时表空间大小

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.1.

先设置max_heap_table_size为最小值16384，根据information_schema.INNODB_TABLES表结构，创建一个相同定义的临时表，插入数据，发现超过需要的内存大小，会报错空间不足

mysql> set max_heap_table_size=16384;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE `TEST_INNODB_TABLES` (
    ->   `TABLE_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `NAME` varchar(655) NOT NULL DEFAULT '',
    ->   `FLAG` int NOT NULL DEFAULT '0',
    ->   `N_COLS` int NOT NULL DEFAULT '0',
    ->   `SPACE` bigint NOT NULL DEFAULT '0',
    ->   `ROW_FORMAT` varchar(12) DEFAULT NULL,
    ->   `ZIP_PAGE_SIZE` int unsigned NOT NULL DEFAULT '0',
    ->   `SPACE_TYPE` varchar(10) DEFAULT NULL,
    ->   `INSTANT_COLS` int NOT NULL DEFAULT '0'
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> insert into TEST_INNODB_TABLES select * from information_schema.INNODB_TABLES;
ERROR 1114 (HY000): The table 'test_innodb_tables' is full1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

4.总结

• 参数internal_tmp_mem_storage_engine可以session、global两种级别的值

• 参数temptable_max_ram、temptable_max_mmap、temptable_use_mmap均是global参数，并且temptable_max_ram、temptable_max_mmap影响的是所有session累加的内存限制，并不是单个session的内存限制

• 参数max_heap_table_size、tmp_table_size可以设置session、global两种级别，并且影响范围是单个session的内存上限。

internal_tmp_mem_storage_engine=temptable的引入，带来了部分性能的提升，也带来了一些限制，而且在某些场景下还会引起性能下降，当系统中使用到大量临时表时，可以多进行参数对比测试，以找到最合适系统的配置。

文章描述了发现异常、分析原因的一些过程，以及事后的思考与实验结果，然而临时表的内容远不止这点，由于篇幅关系就不再展开。其中测试场景有限，可能出现误导的情况，各位看官在使用的时候，尽量根据实际业务情况再验证后做出判断。

参考链接：

https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html
https://bugs.mysql.com/bug.php?id=98741
https://forums.percona.com/t/mysql-8-0-the-table-tmp-sql1-f519f-7-is-full/10767
Enjoy GreatSQL :)

文章转载自公众号：GreatSQL社区

分类

其他

标签

GreatSQL

已于2022-8-24 14:36:31修改

51CTO

51CTO博客

51CTO学堂

MySQL8.0中消失又回来的磁盘临时表

订阅鸿蒙技术特刊，精选内容抢先看