MySQL Internal Temporary Table (Internal Temporary Table)

When some SQL commands are executed in the MySQL database, it may need to create some internal temporary tables to perform complex sorting or grouping queries. MySQL temporary tables can be divided into in-memory and on-disk. If possible, MySQL always uses the in-memory temporary table first, and when the temporary table becomes too large, it may be converted into a on-disk temporary table.

The following conditions may cause the SQL command to create a temporary table:

* Different order by and group by conditions are used, or they contain non-first table fields in the JOIN query;
* Both DISTINCT and order by are used;
* If the SQL command uses the SQL _SMALL_RESULT option, a temporary in-memory table will be created when necessary, unless
The query also contains elements that need to be stored on-disk;

In-memory temporary tables may be blocked under some conditions, resulting in MySQL switching to on-disk temporary tables:

* The data table contains BLOB or TEXT fields;
* Columns with more than 512 bytes in any condition of group by or DISTINCT;
* If UNION or union all is used, and the SELECT column contains any column with more than 512 bytes;
* If the temporary in-memory table becomes too large, the value exceeds the tmp_table_size or max_heap_table_size value;

MySQL uses the Created_tmp_tables and Created_tmp_disk_tables variables to record the number of temporary tables used;

Optimization of MySQL internal temporary table (internal temporary table:

(1) first, consider how to avoid creating temporary tables using SQL commands;

For a database with a very busy query connection, frequent use of queries that require creating temporary tables is already a performance bottleneck. As a DBA, You need to review the structure of your data table and the associations between tables, and reconsider the primary key and index, reorganize the data structure to reduce the need for different order by and group by conditions in the application.
As a programmer, you may need to re-analyze your application requirements and try to simplify the complexity of SQL commands. For example, try to associate multiple levels of queries at a time, split the table into multiple queries with fewer Association levels, or use a View table.

(2) second, try to ensure that the temporary table is created in the memory rather than in the disk;

If you cannot avoid creating temporary tables, try to ensure that these temporary tables can be created in the memory. Avoid using BLOB and TEXT fields in the structure design and query commands, or use the SUBSTRRING (colum, length) function to convert them to the string type; use the SQL _SMALL_RESULT option to notify the database to use the in-memory temporary table; Use View to simplify query; Use the RAM disk memory disk to store the temporary table of the MySQL database (ensure that the BLOB and TEXT fields are not used ).

How to Avoid On-Disk Temporary Tables

Because Memory storage engine does not support BLOB and TEXT, it contains BLOB and TEXT fields. When it needs to use an implicit temporary table (implicit temporary table, you have to use the on-disk MyISAM temporary table, even if its query results may only have a few simple rows of data.

This leads to a serious performance bottleneck. Even if you can configure to store a temporary MySQL table to a RAM disk, you still need to use many expensive operating systems to call functions. In practice, It is also found that temporary tables of some SQL statements cannot even be used by RAM disk at all (in this case, the SQL query command fails because the temporary table cannot be created ).

The best solution is to avoid using the BLOB and TEXT types unless you really need them.
If you can't avoid them, you may be able to use the order by substrring (colum, length)
Trick to convert the values to character strings. wihich will permit in-memory temporary
Tables.

Just be sure that you are using a short engough substring that the temporary table
Doesn't grow larger than max_heap_table_size or tmp_table_size, or MySQL will convert
The table to an on-disk MyISAM table.

If the Extra column of EXPLAIN contains "Using temporary ",
The query uses an implicit temporary table.