MySQL creates a large number of disk temp tables

Find out that the temporary table created in MySQL is basically a disk staging table, which you can troubleshoot. Finally, a description of the temporal table mechanism.

By looking at the MySQL status variable, you can see that the temporary table created is basically a disk temp table.

View temporary table configuration, default configuration 16M, configuration and no problem.

Create two tables and do a query test

CREATE TABLE t1 (F1 int auto_increment primary key, F2 char (), F3 datetime Default Now ()), insert into T1 (F2) VALUES (' Zhao ' ), (' Money '), (' Sun '), (' Lee '); CREATE table t2 (F1 int auto_increment primary KEY, F2 int, F3 char (), F4 datetime default Now ()); ins ert into T2 (F2, F3) VALUES (' 3 ', ' Goku '), (' 1 ', ' Four '), (' 4 ', ' White ');

Refresh status, query, find disk table created

The query of two small tables completely satisfies the condition of creating the memory temporary table, which appears to be a configuration problem. Check the configuration file for configuration big_tables.

View the official documentation for Big_tables:

If set to 1, all temporary tables are stored on disk instead of in memory. This is a bit slow, but for a SELECT operation that requires a large temporary table, the table tbl_name is full error is not reported. The default value for a new connection is 0 (use a temporary table in memory). Typically, you do not need to set this variable because the memory table is automatically converted to a disk-based table as needed.

Modify the system variable and test again, this time the temporary table created is a temporary memory table.

MySQL Internal temp table (internal temporary table) mechanism

In some cases, MySQL creates an internal temporary table when executing a statement. The user cannot directly control when it occurs.

The server creates temporary tables in the following situations:

Evaluate the Uinon statement;

Evaluate some views, such as a view using the temptable algorithm, UNION or aggregation;

Evaluation of derived tables (sub-queries in the FROM clause);

Create tables for subqueries or semi-join implementations;

Evaluates statements that contain an ORDER BY clause and a different GROUP BY clause, or an order by or group by contains a statement from a column other than the first table in the queue;

The use of distinct with order by may require a temporary table;

For queries that use the Sql_small_result modifier, MySQL uses temporary tables in memory, unless the query also contains elements that require disk storage;

Evaluate multiple table UPDATE statements;

Evaluates an group_concat () or COUNT (DISTINCT) expression.

Want to know if a statement has used a temporary table, see through EXPLAIN that the Extra column has no description using temporary. EXPLAIN does not necessarily use temporal tables for temporary tables or derived temporary tables.

When the server creates an internal temporary table (whether it is a memory table or a disk table), the value of the Created_tmp_tables state variable is increased. If the server creates a temporary disk table (either directly or from a memory table), the value of the Created_tmp_disk_tables state variable is increased. By show global status like ' create% '; Statement view.

Some query conditions prevent the use of temporary tables in memory, in which case the server uses the table on disk:

A blob or text column exists in the table;

If you use union or union all, then any string column in the SELECT list that has a maximum length greater than 512 (bytes of the binary string, characters that are not binary strings)

The SHOW COLUMNS and DESCRIBE statements use BLOBs as the type of some columns, so the staging table for the results is a table on disk.

MySQL creates a large number of disk temp tables