【MySQL各版本临时表使用情况】

前言：

    测试环境我们一个表数据100多万，做了一个group by 报错了（SQL 错误 [1114] [HY000]: The table '/mysql/3306/tmp/#sql50a7_7d851_0' is full），MySQL版本是8.0.25，从这个问题就引发了我对mysql的临时表的一些思考，按照以往的一些思路，j就是要扩容临时表空间，但是想了下这个mysql8.0和以前的版本还是略有不同的。下面做了一些简单的实验测试下几个主流版本的临时表空间的情况。

测试：

    docker run -d --name mysql5.6 \

-h mysql5.6 -p 33651:3306 --net=mysql-network --ip 172.72.5.50 \
-v /lhrmysqltest3/mysql5.6/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.6


关于用户临时表，通过create temporary table参数创建的
===》5.6
1、存放在参数tmpdir下。
mysql> create temporary table test(id int,name char(100));

Query OK, 0 rows affected (0.04 sec)


mysql>
mysql> create temporary table test1(id int,name char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | test | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.01 sec)

在5.6中，磁盘临时表位于tmpdir下，临时表引擎为myisam，文件名类似#sql4d2b_8_0.ibd，其中#sql是固定的前缀，4d2b是进程号的十六进制表示，8是MySQL线程号的十六进制表示(show processlist中的id)，0是每个连接从0开始的递增值，ibd是innodb的磁盘临时表(通过参数default_tmp_storage_engine控制)。在5.6中，磁盘临时表创建好后，对应的frm以及引擎文件就在tmpdir下创建完毕，可以通过文件系统ls命令查看到。在连接关闭后，相应文件自动删除。因此，我们如果在5.6的tmpdir里面看到很多类似格式文件名，可以通过文件名来判断是哪个进程，哪个连接使用的临时表，这个技巧在排查tmpdir目录占用过多空间的问题时，尤其适用。用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。临时表的undolog存在undo表空间中，与普通表的undo放在一起。有了undo回滚段，用户创建的这种临时表也能支持回滚了。


root@mysql5:/tmp# ls -lrht
total 204K
-rw-rw---- 1 root root 0 Mar 14 08:08 tmp.1XQJAZafX1
-rw-rw---- 1 root root 0 Mar 14 08:10 tmp.E2UiXWbxwU
-rw-rw---- 1 mysql mysql 0 Mar 14 08:10 tmp.gUyhMX8Mu3
-rw-rw---- 1 mysql mysql 8.4K Mar 14 08:15 #sql1_1_0.frm
-rw-rw---- 1 mysql mysql 96K Mar 14 08:15 #sql1_1_0.ibd
root@mysql5:/tmp# ls -lrht
total 408K
-rw-rw---- 1 root root 0 Mar 14 08:08 tmp.1XQJAZafX1
-rw-rw---- 1 root root 0 Mar 14 08:10 tmp.E2UiXWbxwU
-rw-rw---- 1 mysql mysql 0 Mar 14 08:10 tmp.gUyhMX8Mu3
-rw-rw---- 1 mysql mysql 8.4K Mar 14 08:15 #sql1_1_0.frm
-rw-rw---- 1 mysql mysql 96K Mar 14 08:15 #sql1_1_0.ibd
-rw-rw---- 1 mysql mysql 8.4K Mar 14 08:16 #sql1_1_1.frm
-rw-rw---- 1 mysql mysql 96K Mar 14 08:16 #sql1_1_1.ibd
root@mysql5:/tmp#

mysql>
mysql> select count(*),col2 from big_table where id<20000000 group by col2;


root@mysql5:/tmp# ls -lrht
total 60M
-rw-rw---- 1 root root 0 Mar 14 08:08 tmp.1XQJAZafX1
-rw-rw---- 1 root root 0 Mar 14 08:10 tmp.E2UiXWbxwU
-rw-rw---- 1 mysql mysql 0 Mar 14 08:10 tmp.gUyhMX8Mu3
-rw-rw---- 1 root root 0 Mar 15 03:08 tmp.O0v2n3pZOW
-rw-rw---- 1 mysql mysql 0 Mar 15 03:08 tmp.zcgzzcjmj4
-rw-rw---- 1 mysql mysql 21M Mar 15 03:25 #sql_1_0.MYD
-rw-rw---- 1 mysql mysql 26M Mar 15 03:25 #sql_1_0.MYI



===》5.7
1、结构文件存放到tmpdir目录下，数据文件挪到了innodb_temp_data_file_path目录下，这个参数建议设置最大值。

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like '%tmp%';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | |
| internal_tmp_disk_storage_engine | InnoDB |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+----------------------------------+----------+
7 rows in set (0.01 sec)

mysql> create temporary table test1(id int,name char(100));
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table test2(id int,name char(100));
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 2 | root | localhost | test | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+-----------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-----------+--------+-------+----------------------+---------------+
| 47 | #sql1_2_1 | 5 | 26 | FALSE | FALSE |
| 46 | #sql1_2_0 | 5 | 26 | FALSE | FALSE |
+----------+-----------+--------+-------+----------------------+---------------+
2 rows in set (0.01 sec)

mysql> show variables like '%temp%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| show_old_temporals | OFF |
+----------------------------+-----------------------+


root@master2:/tmp# ls -lrht
total 12K
-rw-r----- 1 mysql mysql 8.4K Mar 14 08:23 '#sql1_2_0.frm'
root@master2:/tmp# ls -lrht
total 24K
-rw-r----- 1 mysql mysql 8.4K Mar 14 08:23 '#sql1_2_0.frm'
-rw-r----- 1 mysql mysql 8.4K Mar 14 08:23 '#sql1_2_1.frm'
root@master2:/tmp#

root@master2:/var/lib/mysql# ls -lrht
total 188M
-rw-r----- 1 mysql mysql 48M Dec 28 14:26 ib_logfile1
-rw-r----- 1 mysql mysql 56 Dec 28 14:26 auto.cnf
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 ca.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 server-cert.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 client-cert.pem
-rw-r--r-- 1 mysql mysql 452 Dec 28 14:26 public_key.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 private_key.pem
drwxr-x--- 2 mysql mysql 8.0K Dec 28 14:26 performance_schema
drwxr-x--- 2 mysql mysql 4.0K Dec 28 14:26 mysql
drwxr-x--- 2 mysql mysql 8.0K Dec 28 14:26 sys
-rw-r----- 1 mysql mysql 177 Dec 28 14:26 master2-bin.000001
-rw-r----- 1 mysql mysql 3.0M Dec 28 14:26 master2-bin.000002
-rw-r----- 1 mysql mysql 1.4K Dec 28 14:26 ib_buffer_pool
drwxr-x--- 2 mysql mysql 52 Dec 28 15:50 test
-rw-r----- 1 mysql mysql 2.9K Mar 14 08:22 master2-bin.000003
-rw-r----- 1 mysql mysql 84 Mar 14 08:22 master2-bin.index
-rw-r----- 1 mysql mysql 154 Mar 14 08:22 master2-bin.000004
-rw-r----- 1 mysql mysql 48M Mar 14 08:22 ib_logfile0
-rw-r----- 1 mysql mysql 12M Mar 14 08:23 ibtmp1
-rw-r----- 1 mysql mysql 76M Mar 14 08:23 ibdata1

优化器内部产生的临时表，会使ibtmp1变大。连接断开，并不会使空间释放，

mysql> call sp_createNum(10000000);
Query OK, 1611392 rows affected (36.79 sec)

mysql> select count(*),col2 from big_table where id<20000000 group by col2;


root@master2:/var/lib/mysql# ls -lrht && ls -lrht /tmp
total 1020M
-rw-r----- 1 mysql mysql 56 Dec 28 14:26 auto.cnf
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 ca.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 server-cert.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Dec 28 14:26 client-cert.pem
-rw-r--r-- 1 mysql mysql 452 Dec 28 14:26 public_key.pem
-rw------- 1 mysql mysql 1.7K Dec 28 14:26 private_key.pem
drwxr-x--- 2 mysql mysql 8.0K Dec 28 14:26 performance_schema
drwxr-x--- 2 mysql mysql 4.0K Dec 28 14:26 mysql
drwxr-x--- 2 mysql mysql 8.0K Dec 28 14:26 sys
-rw-r----- 1 mysql mysql 177 Dec 28 14:26 master2-bin.000001
-rw-r----- 1 mysql mysql 3.0M Dec 28 14:26 master2-bin.000002
-rw-r----- 1 mysql mysql 1.4K Dec 28 14:26 ib_buffer_pool
-rw-r----- 1 mysql mysql 2.9K Mar 14 08:22 master2-bin.000003
-rw-r----- 1 mysql mysql 84 Mar 14 08:22 master2-bin.index
drwxr-x--- 2 mysql mysql 94 Mar 15 06:39 test
-rw-r----- 1 mysql mysql 48M Mar 15 06:40 ib_logfile1
-rw-r----- 1 mysql mysql 443M Mar 15 06:40 master2-bin.000004
-rw-r----- 1 mysql mysql 76M Mar 15 06:40 ibdata1
-rw-r----- 1 mysql mysql 48M Mar 15 06:40 ib_logfile0
-rw-r----- 1 mysql mysql 332M Mar 15 06:40 ibtmp1
total 0




-====》8.0
关于8.0的测试可以参考下这三篇文章，这三篇文章都是我摘录的。

https://mp.weixin.qq.com/s?__biz=Mzg2MzkxMjg4Ng==&mid=2247484433&idx=1&sn=ee69bec063d9b62e97247e8c72d7988f&chksm=ce7012c8f9079bde03613be223e592f274d4e320b6e0c3f8108ef9db4eac55549e806fedc066&cur_album_id=2830114104928632835&scene=189&key=78b5ebcb496bfcf5b9be0d5eb199508e6da2ece00c1876e2a68db4db4a9072bdb9b94584396b5ea87e55398e541a2a519a74c9d2b463e68f54041538ae5f388788ecc16fad7da9920f8f95f6b4ce5788e40b41dc32088a600b0c0e2e72a9264c5f25838e620b231f672825ef45c8072f8038a36a2dfd4ac7d7cf6a365c09bfce&ascene=7&uin=MTk3NzU5NDkwMg%3D%3D&devicetype=Windows+10+x64&version=6309001c&lang=zh_CN&countrycode=CN&exportkey=n_ChQIAhIQrFi3j8fNU%2BbJzm6tLUQllBLgAQIE97dBBAEAAAAAAGogD2VXk8cAAAAOpnltbLcz9gKNyK89dVj0%2BMy7cJKTzKD5j4m4G6KGE1BqiMWUQsWYa06%2B3S0nYcR1it%2FHl74bObmvXVuMoi6xvsS%2FpDup8F%2BUL2oVe0C9D39HTGhXaIc3NiNggXQAVrze9O1VdBeyJZ6kFwJHDOU3WjIj%2Beb7oxEfF7uNtgx4SWgWEIqoyf6mhe1kZ3yCbktsq9eS3%2F8HbWkGy3uQND%2Bt8R%2FTJDnAj8m1frLoAbV00pAzvVPkc03gGxb7Ffc4hs3pdvqkUnzNC94x&acctmode=0&pass_ticket=DasAmsFZINqXvE8tECjIdJl7gwMbvTdq5n7Rg7QhFkJaEThycJ6dRQ1OjzVMYIMIiqd073PaeixmefRBghjbjg%3D%3D&wx_header=1&fontgear=2

https://www.modb.pro/db/618704

https://baijiahao.baidu.com/s?id=1681217472630396207&wfr=spider&for=pc  ===》描述了5.7和8.0中临时表空间的不同。

docker run --cap-add=sys_nice -d --name mysql8027 \
-h mysql8027 -p 33655:3306 --net=mysql-network --ip 172.72.5.50 \
-v /lhrmysqltest3/mysql8027/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
mysql:8.0.27


Database changed
mysql> show variables like '%tmp%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | |
| internal_tmp_mem_storage_engine | TempTable |
| replica_load_tmpdir | /tmp |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+---------------------------------+-----------+
7 rows in set (0.09 sec)

mysql> show variables like '%temp%';
+-----------------------------+-----------------------+
| Variable_name | Value |
+-----------------------------+-----------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| show_old_temporals | OFF |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 1073741824 |
| temptable_use_mmap | ON |
+-----------------------------+-----------------------+
7 rows in set (0.00 sec)

temptable_use_mmap = ON 时，如果内部临时表超过了 temptable_max_ram 大小，使用内存映射的临时文件用作内部临时表的溢出机制，临时文件放在 tmpdir 目录下
temptable_use_mmap = OFF 时，如果内部临时表超过了temptable_max_ram 大小，使用 InnoDB 磁盘内部临时表用作内部临时表的溢出机制，存放在 innodb 会话临时表空间中，与 MySQL 5.7 的区别是，session 断开后就会释放空间，不需要重启 MySQL
mysql> show variables like '%data%';
+---------------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------------+------------------------+
| binlog_row_metadata | MINIMAL |
| character_set_database | utf8mb4 |
| collation_database | utf8mb4_0900_ai_ci |
| datadir | /var/lib/mysql/ |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_stats_on_metadata | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_use_fdatasync | OFF |
| max_length_for_sort_data | 4096 |
| myisam_data_pointer_size | 6 |
| performance_schema_max_metadata_locks | -1 |
| resultset_metadata | FULL |
| skip_show_database | OFF |
| updatable_views_with_limit | YES |
+---------------------------------------+------------------------+
15 rows in set (0.00 sec)

mysql> create temporary table test1(id int,name char(100));
Query OK, 0 rows affected (0.02 sec)

mysql> create temporary table test2(id int,name char(100));
Query OK, 0 rows affected (0.01 sec)

root@master:/var/lib/mysql# cd '#innodb_temp'
root@master:/var/lib/mysql/#innodb_temp# ls
temp_1.ibt temp_10.ibt temp_2.ibt temp_3.ibt temp_4.ibt temp_5.ibt temp_6.ibt temp_7.ibt temp_8.ibt temp_9.ibt
root@master:/var/lib/mysql/#innodb_temp# ls -lrht
total 800K
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_9.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Jan 11 15:46 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Mar 14 08:55 temp_10.ibt


mysql> select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+------------+--------+------------+
| TABLE_ID | NAME | N_COLS | SPACE |
+----------+------------+--------+------------+
| 1079 | #sql1_1a_6 | 5 | 4243767290 |
| 1078 | #sql1_1a_5 | 5 | 4243767290 |
+----------+------------+--------+------------+