Taking MySQL 8.0 as an example, we know that the temporary table spaces of 8.0 are divided into session temporary table spaces and global temporary table spaces by looking at the official documents of 8.0. When InnoDB is configured as the storage engine for temporary tables inside the disk, the session temporary table space stores temporary tables created by users and internal temporary tables created by the optimizer. When a session is disconnected, its temporary tablespace will be truncated and released back into the pool. That is to say, in 8.0, there is a special session temporary tablespace, which can reclaim disk space when the session is killed; The original ibtmp 1 is the current global temporary table space, which stores the rollback segment that changes the temporary table created by the user. In 5.7, ibtmp 1 stores temporary tables created by users and temporary tables inside the disk.

In other words, the usage of ibtmp 1 has changed in 8.0 and 5.7. The data of version 5.7 temporary table is stored in ibtmp 1, and the data of version 8.0 temporary table is stored in session temporary table space. If the temporary table changes, the changed undo data will be stored in ibtmp 1.

Experimental verification: Save the previous query result as a temporary table, and the corresponding session number is 45. By looking at the corresponding dictionary table, we can see that the table space temp_8.ibt is used in session number 45. By saving the query as a temporary table, the session temporary table space can be used, as shown below:

Next, in kill session 45, it is found that the temp_8.ibt space has been released, and it has become the initial size and the state is inactive, which proves that the temporary tablespace can be released in mysql8.0 through kill session.

Summary: In mysql5.7, when the session is killed, the temporary table will be released, but the space will not be released back to the operating system just by marking it in the ibtmp file. If you want to free up space, you need to restart the database; In mysql8.0, temporary tablespaces can be released by terminating the session.