What is the use of mysql temporary table?

MySQL temporary tables are used in many scenarios. For example, temporary tables created by users themselves are used to save temporary data. When executing complex SQL internally, MySQL needs to use temporary tables for grouping, sorting, deduplication and other operations. The following will sort out some concepts, classifications and common problems of MySQL temporary tables.

MySQL temporary table type

1. External temporary table, a temporary table created through the create temporary table syntax, the storage engine can be specified as memory , innodb, myisam, etc., such tables will be automatically cleaned after the session ends. If a temporary table exists at the same time as a non-temporary table, the non-temporary table is not visible. The show tables command does not display temporary table information.
You can view information about external temporary tables through the information_schema.INNODB_TEMP_TABLE_INFO system table. This part is still relatively rarely used.

2. Internal temporary tables are usually used to execute complex SQL, such as group by, order by, distinct, union, etc. If the execution plan contains Using temporary, there will be undo rollback. time, but when there is insufficient space, MySQL will use automatically generated temporary tables internally to assist in completing the work.

MySQL temporary table related parameters

1.max_heap_table_size: The maximum value of the memory table created by the user, also used together with tmp_table_size to limit the internal temporary table in memory the size of.
2.tmp_table_size: The maximum value of the internal temporary table in memory is determined together with the max_heap_table_size parameter, and the minimum value of the two is taken. If the temporary table exceeds this value, it will be moved from memory to disk.
3.innodb_tmpdir: online ALTER TABLE operations that rebuild the table max_tmp_tables

4.default_tmp_storage_engine: The default storage engine for external temporary tables (tables created by create temporary table).

5.innodb_temp_data_file_path: temp file attribute under innodb engine. It is recommended to limit innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine: The internal temporary table storage engine on the disk, the optional value is myisam or innodb. When using an innodb table, in some scenarios, for example, if the temporary table has too many columns, or the row size exceeds the limit, the error "Row size too large or Too many columns" may occur. In this case, the innodb engine of the temporary table should be changed back to myisam. . tmpdir: Temporary table directory. When the size of the temporary table exceeds a certain threshold, it will be transferred from the memory to the disk.
7. The tmpdir variable indicates the directory where the temporary table is located on the disk.

MySQL temporary table related status variables

1.Created_tmp_disk_tables: The number of internal temporary tables created by MySQL on the disk when executing a SQL statement. If this value is large, The possible reason is that the maximum memory value allocated to the temporary table is small, or there are a large number of sorting, grouping, deduplication and other operations in the SQL, and the SQL needs to be optimized.

2.Created_tmp_files: The number of temporary tables created

3.Created_tmp_tables: The number of internal temporary tables created by MySQL when executing SQL statements.

4.Slave_open_temp_tables statement or mix mode can only be seen in use.
The value of slave_open_temp_tables shows how many temporary tables have been created by the current slave through replication. Binlog_format is only valid under statement and mixed.
Note: stop slave is useless. The main library must be manually deleted or the session must exit. Can.
The following is the binlog record information from the database:

MySQL temporary table precautions

1. MySQL temporary tables may cause a reduction in available disk space:
Before MySQL version 5.7, the storage engine of temporary tables defaulted to myisam. The myisam temporary table will automatically delete the temporary table after the SQL execution is completed. surface. However, starting from version 5.7, the default storage engine for temporary tables has been changed to innodb. Although there has been a certain improvement in performance, because the temporary tables of the innodb engine share the table space ibtmp1, multiple sessions create temporary temporary tables at the same time under high concurrency. table, the table space will become very large and cannot be dynamically reduced and cannot be released unless MySQL is restarted.

You can set a maximum value for the temporary table space, such as 10G, as follows:
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
When the temporary table space reaches the maximum When the value is 10G, SQL execution will report an error, affecting the normal execution of the application.
For the problem of excessive temporary table space, there are usually other methods to solve the problem, such as:
Set the storage engine of the temporary table to myisam. Although there may be some performance problems, it will not cause disk space problems.

2.SQL statement:
(1) Add appropriate index
(2) Filter more data in the where condition
(3) Rewrite SQL and optimize execution plan
(4) If you have to use temporary tables, you must reduce concurrency. It is recommended to use SSD hard drive.

3.undo related
1) Use the innodb_rollback_segments configuration option to define the number of rollback segments. The default setting is 128, which is also the maximum value. One rollback segment is always allocated to the system table space, and 32 rollback segments are reserved for temporary table space (ibtmp1). Therefore, to allocate rollback segments to undo the tablespace, set innodb_rollback_segments to a value greater than 33. When configuring a separate undo tablespace, the rollback segment in the system tablespace will be rendered inactive.

That is to say, when the rollback segment exceeds 128, a temporary table is needed for emergency relief.

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2) truncate undo
When innodb_undo_log_truncate is triggered, the undo table space truncation operation creates a temporary undo_space_number_trunc.log file in the server log directory, which is defined by innodb_log_group_home_dir. If a system failure occurs during a truncate operation, the temporary log file allows the startup process to recognize the truncated undo tablespace and continue operations.

4.binlog cache related
Use the binary log cache and the value reaches the value set by binlog_cache_size, and use temporary files to store the number of transactions from changes in transactions. Can be tracked separately through the Binlog_stmt_cache_disk_use status variable.

Summary

1. From the above understanding, the MySQL temporary table cannot be avoided in daily monitoring and optimization.
2. In addition, it can also be used appropriately in business implementation, such as temporarily saving a small amount of information as an intermediate table, etc.
3. When binlog_format is equal to ROW mode during the replication process, the binlog logs related to the temporary table are not recorded (except for the drop command). This part needs attention.
【Related recommendations: mysql video tutorial】