MySQL8.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_colCreate 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_tabCreate 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_ci1 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)