Depth analysis of Mysql 5.7 temporary table space _mysql

Temporary tables

Temporary tables, as the name suggests, are temporary, used to destroy the table. Data can be saved either on a temporary file system or on a fixed disk file system.

The temporary table has several of the following:

1, global temporary table

This temporary table takes effect after the database instance has been started and is invalidated after the database instance has been destroyed. In MySQL, this temporary table corresponds to the memory table, the memory engine.

2. Session-Level Temp table

This temporary table takes effect after the user's login system succeeds and expires when the user exits. The temporary table in MySQL refers to a create temporary table table created with such a keyword.

3. Transaction-Level temporary tables

This temporary table takes effect at the start of a transaction, and fails after the transaction commits or rolls back. There is no such temporary table in MySQL, which must be implemented indirectly using the session-level temporary table.

4, the Search level temporary table

This temporary table is generated between execution of the SQL statement and is invalidated after execution. In MySQL this temporary table is not very fixed, following the MySQL default storage engine to change. For example, the default storage engine is MyISAM, the engine of the temporary table is MyISAM, and the form of file generation and the form of data operation and MyISAM, only the data stored in memory; If the default engine is InnoDB, then the engine of the temp table is InnoDB, At this point all of its information is stored in the shared table space Ibdata.

MySQL 5.7 Temp Table space

MySQL 5.7 Optimizes temporary tablespace space for the InnoDB storage engine. Prior to MySQL 5.7, the InnoDB engine's temporary tables were kept in Ibdata, while Ibdata's greedy disk footprint caused temporary table creation and deletion to have a significant performance impact on other normal tables. In MySQL5.7, the following two important aspects of optimization are done for temporary tables:

1, MySQL 5.7 to the temporary table data and rollback information (limited to the uncompressed table) from the shared table space inside out, forming their own separate table space, parameters innodb_temp_data_file_path .

2, in the MySQL 5.7 in the temporary table of the relevant search information stored in the System Information table: information_schema.innodb_temp_table_info and MySQL 5.7 before the version to see the temporary table system information is not a good way.

One thing to note is that although the InnoDB temporary table has its own tablespace, it is not yet possible to define the save path for the temporary tablespace file itself, but to inherit the Innodb_data_home_dir. At this point if you want to take other disk, such as memory disk to act as a temporary table space to save the address, you can only use the old way to do the soft chain. As a small example:

The OS I'm using is ubuntu12.x, and I want to use the Tmpfs file system as a temporary table space,

root@ytt-master-virtualbox:/usr/local/mysql/data# ln-s/run/shm//usr/local/mysql/data/tmp_space2

Root@ytt-master-virtualbox:/usr/local/mysql/data#ls-l | grep ' Shm '
lrwxrwxrwx1 root root  9 Nov 10:28tmp_space2->/run/shm/

Then innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend add the following line to the [mysqld] in my.cnf and restart the MySQL service:

Mysql>select @ @innodb_temp_data_file_path \g
***************************1. Row ***************************
@ @innodb_temp_data_file_path: tmp_space2/ibtmp2:200m:autoextend
1 Rowin Set (0.00 sec)

First write a stored procedure that creates a batch of temporary tables:

delimiter$$ use

' t_girl ' $$

dropprocedure IF EXISTS ' sp_create_temporary_table ' $$

createdefiner= ' root ' @ ' localhost ' PROCEDURE ' sp_create_temporary_table ' (in
  f_cnt int UNSIGNED)
BEGIN
   DECLARE i int UNSIGNED DEFAULT 1;

   While I <= f_cnt
   do
    SET @stmt = CONCAT (' Create temporarytable tmp ', I, ' (id int, Tmp_desc varchar (60)); 
    PREPARE s1 from @stmt;
    EXECUTE S1;
    SET i = i + 1;
   End While;
   DROP PREPARE S1;
  end$$

DELIMITER;

Now to create 10 temporary tables:

Mysql>call sp_create_temporary_table (a);
Queryok, 0 rows affected (0.07 sec)

In the past, we only knew that 10 temporary tables were created, but only by remembering or manually recording the names of the temporary tables.

You can now retrieve the relevant data directly from the data dictionary.

 mysql> select * FROMINFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO; +----------+------- -------+--------+-------+----------------------+---------------+
| table_id | NAME | N_cols | space| Per_table_tablespace |
is_compressed |  +----------+--------------+--------+-------+----------------------+---------------+
| 56 |  #sql1705_2_9 | 5 | 36 | FALSE |  FALSE | | 55 |  #sql1705_2_8 | 5 | 36 | FALSE |  FALSE | | 54 |  #sql1705_2_7 | 5 | 36 | FALSE |  FALSE | | 53 |  #sql1705_2_6 | 5 | 36 | FALSE |  FALSE | | 52 |  #sql1705_2_5 | 5 | 36 | FALSE |  FALSE | | 51 |  #sql1705_2_4 | 5 | 36 | FALSE |  FALSE | | 50 |  #sql1705_2_3 | 5 | 36 | FALSE |  FALSE | | 49 |  #sql1705_2_2 | 5 | 36 | FALSE |  FALSE | | 48 |  #sql1705_2_1 | 5 | 36 | FALSE |  FALSE | | 47 |  #sql1705_2_0 | 5 | 36 | FALSE |
FALSE | +----------+--------------+--------+-------+----------------------+---------------+ 10rows in Set (0.00 sec) 

/div>

Summarize

Functional I wrote here, you can find time to test if you are interested in performance. I hope the content of this article for everyone to learn or use mysql5.7 can bring some help, if you have questions you can message exchange.