Temporary table
As the name suggests, a temporary table is a temporary, destroyed table. Data can be saved on a temporary file system or on a fixed disk file system.
There are several temporary tables:
1. Global temporary table
This temporary table takes effect after the database instance is started and expires after the database instance is destroyed. In MySQL, this temporary table corresponds to a memory table, that is, the memory engine.
2. Session level temporary table
This temporary table takes effect after the user logs into the system successfully and becomes invalid when the user logs out. The temporary table in MySQL refers tocreate temporary table Such keywords create tables.
3. Transaction level temporary table
This temporary table takes effect at the beginning of the transaction and expires after the transaction is committed or rolled back. There is no such temporary table in MySQL, and it must be implemented indirectly using session-level temporary tables.
4. Search for temporary tables of level
This temporary table is generated between SQL statement execution and expires after execution. In MySQL, this temporary table is not very fixed, and changes according to the MySQL default storage engine. For example, the default storage engine is MyISAM, and the temporary table engine is MyISAM, and the file generation form and data operation form are the same as MyISAM, except that the data is stored in memory; if the default engine is INNODB, then the temporary table engine is INNODB, and all its information is stored in the shared table space ibdata.
MySQL 5.7 temporary tablespace
MySQL 5.7 optimizes the temporary tablespace of the InnoDB storage engine. Before MySQL 5.7, the temporary tables of the INNODB engine were stored in ibdata, and the greedy disk occupation of ibdata caused the creation and deletion of temporary tables to have a very large performance impact on other normal tables. In MySQL 5.7, the following two important aspects of optimization are made for temporary tables:
1. MySQL 5.7 strips the data of temporary tables and rollback information (limited to uncompressed tables) from the shared table space to form its own separate table space, with the parameters asinnodb_temp_data_file_path。
2. Save the search information of the temporary table in the system information table in MySQL 5.7:information_schema.innodb_temp_table_info. There is no good way to view the system information of temporary tables in versions before MySQL 5.7.
One thing to note is:Although INNODB temporary tables have their own tablespaces, they cannot define the saving path of temporary tablespace files themselves, and can only inherit innodb_data_home_dir. At this time, if you want to use other disks, such as memory disks, as the storage address of the temporary table space, you can only use the old method to make a soft link. To give a small example:
I am using the OS now, and I 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_space2 root@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 to the following line [mysqld] in it, after restarting 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 for batch creation 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 create 10 temporary tables:
mysql>call sp_create_temporary_table(10); QueryOK, 0 rows affected (0.07 sec)
If we had only known that we had created 10 temporary tables, but we could only record the names of the temporary tables by memory or manually.
You can now directly retrieve relevant data 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)
Summarize
That’s all for functionality. If you are interested in performance, you can find time to test it. I hope that the content of this article will be of some help to everyone to learn or use mysql5.7. If you have any questions, you can leave a message to communicate.