In-depth parsing of temporary tablespace in MySQL 5.7 and in-depth parsing of mysql5.7

In-depth parsing of temporary tablespace in MySQL 5.7 and in-depth parsing of mysql5.7

Temporary table

A temporary table, as its name implies, is a temporary table that has been destroyed. Data can be stored in both a temporary file system and a fixed disk file system.

Temporary tables include the following types:

1. Global temporary table

This temporary table takes effect after the database instance is started and becomes invalid after the database instance is destroyed. In MySQL, this temporary table corresponds to the memory table, that is, the memory engine.

2. Session-level temporary table

This temporary table takes effect after the user successfully logs on to the system and becomes invalid when the user exits. The temporary table in MySQL referscreate temporary table This keyword is used to create a table.

3. Transaction-level temporary table

This temporary table takes effect at the beginning of the transaction and becomes invalid after the transaction is committed or rolled back. There is no such temporary table in MySQL, which must be indirectly implemented using session-level temporary tables.

4. Retrieving temporary tables

This temporary table is generated between SQL statement execution and becomes invalid after execution. In MySQL, this temporary table is not very fixed and changes with the default storage engine of MySQL. For example, the default storage engine is MyISAM, and the engine of the temporary table is MyISAM. The file generation form and data operation form are the same as that of MyISAM, but the data is stored in the memory. If the default engine is INNODB, the engine of the temporary table is INNODB, and all its information is stored in the shared tablespace ibdata.

Temporary tablespace in MySQL 5.7

MySQL 5.7 optimizes the temporary tablespace of the InnoDB Storage engine. Before MySQL 5.7, temporary tables of the INNODB engine were saved in ibdata, the greedy disk usage of ibdata results in the creation and deletion of temporary tables, which has a great impact on the performance of other normal tables. In MySQL5.7, two important aspects of temporary tables are optimized as follows:

1. MySQL 5.7 splits the data and rollback information of the temporary table (only for uncompressed tables) from the shared table space to form its own independent table space. The parameter isinnodb_temp_data_file_path.

2. in MySQL 5.7, save the retrieval information of the temporary table in the system information table:information_schema.innodb_temp_table_infoMySQL 5.7 and earlier versions do not have a good way to view the system information of temporary tables.

Note that:Although the temporary INNODB table has its own tablespace, you cannot define the path to save the temporary tablespace file. You can only inherit innodb_data_home_dir. If you want to use another disk, such as a memory disk, as the storage address of the temporary tablespace, you can only use the old method for soft chain. For example:

I am using Ubuntu12.X and want to use the tmpfs file system as a temporary tablespace,

root@ytt-master-VirtualBox:/usr/local/mysql/data# ln -s/run/shm/ /usr/local/mysql/data/tmp_space2root@ytt-master-VirtualBox:/usr/local/mysql/data#ls -l | grep 'shm'lrwxrwxrwx1 root root  9 Nov 13 10:28tmp_space2 -> /run/shm/

Theninnodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextendAdd the following line to [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:autoextend1 rowin set (0.00 sec)

First, write a stored procedure for creating temporary tables in batches:

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 create 10 temporary tables:

mysql>call sp_create_temporary_table(10);QueryOK, 0 rows affected (0.07 sec)

In the past, we only knew that we had created 10 temporary tables, but only remembered or manually recorded the name of the temporary table.

You can now retrieve 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)

Summary

I will write about the functionality here. If you are interested in performance, you can test it later. I hope this article will help you learn or use mysql5.7. If you have any questions, please leave a message.