Shulou(Shulou.com)06/01 Report--

It is found that the temporary tables created in mysql are basically disk temporary tables, which can be checked. Finally, it explains the temporary table mechanism.

By looking at the mysql status variable, you can see that the temporary tables created are basically disk temporary tables.

Check the temporary table configuration, the default configuration is 16m, and there is no problem with the configuration.

Create two tables and do a query test

Create table T1 (F1 int auto_increment primary key, f2 char (50), f3 datetime default now ()); insert into T1 (f2) values ('Zhao'), ('Qian'), ('Sun'), ('Li'); create table T2 (F1 int auto_increment primary key, f2 int, f3 char (50), f4 datetime default now ()); insert into T2 (f2, f3) values ('3Qing,' Wukong'), ('1Qing,' four'), ('4Qing,' white')

Refresh the status, make a query, and find that the disk table is created

The query of the two small tables fully meets the conditions for creating temporary tables in memory, which seems to be a problem of configuration. Check the configuration file for configuration big_tables.

Check the official documentation's description of big_tables:

If set to 1, all temporary tables are stored on disk rather than in memory. This is a bit slow, but for SELECT operations that require large temporary tables, The table tbl_name is full errors are not reported. The default value for new connections is 0 (using temporary tables in memory). Typically, you do not need to set this variable because memory tables are automatically converted to disk-based tables as needed.

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

Internal temporary Table (internal temporary table) Mechanism in mysql

In some cases, mysql creates internal temporary tables when the statement is executed. The user has no direct control over when it occurs.

The server creates temporary tables in the following situations:

● Evaluation UINON statement

● evaluates some views, such as views that use the TEMPTABLE algorithm, UNION, or aggregation

Evaluation of ● derived tables (subqueries in the FROM clause)

● creates tables for subqueries or semi-joins

● evaluates statements that contain ORDER BY clauses and different GROUP BY clauses, or statements that ORDER BY or GROUP BY contain columns from tables other than the first table in the queue

● DISTINCT in conjunction 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 need to be stored on disk

● evaluation multi-table UPDATE statement

● evaluates the GROUP_CONCAT () or COUNT (DISTINCT) expression.

To know if a statement uses temporary tables, check the Extra column through EXPLAIN to see if it says Using temporary. EXPLAIN does not necessarily use temporary tables for temporary or derived temporary tables.

When the server creates an internal temporary table (whether it's a memory table or a disk table), it increments the value of the Created_tmp_tables state variable. If the server creates a disk temporary table (either created directly or converted from the memory table), it increases the value of the Created_tmp_disk_tables state variable. View through the show global status like 'Create%'; statement.

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

There are BLOB or TEXT columns in the ● table

If ● uses UNION or UNION ALL, there are any string columns in the SELECT list with a maximum length greater than 512 (bytes of a binary string, characters that are not binary strings)

The ● SHOW COLUMNS and DESCRIBE statements use BLOB as the type of some columns, so the temporary table for the results is an on-disk table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.