How long does MySQL AS create a temporary table?

You're right :

The client creates a new session, this session is only a 1-to-1 relationship between the server and the client, the client may set up a temporary table on the server to meet the needs of the client to process some transactions, when the client exits the session, the temporary table will be automatically dropped, and no data information occupies the database space.

This is the first advantage: space saving.

Clients create temporary tables that serve only specific transactions, and this table is private and does not need to be shared with others. So there's a second advantage: privacy.

The temporary tables created by the client have independent operation and read/write performance, so they are faster and more efficient, which has the third advantage: high efficiency.

There are many possibilities for similar situations depending on the client's needs.

1. The local temporary table (at the beginning) is only valid for the current connection, and will be automatically deleted when the current connection is disconnected.

2. The global temporary table (at the beginning) is also valid for other connections, and will be automatically deleted when the current connection and other connections that have accessed it are disconnected.

3. Regardless of whether it is a local temporary table or a global temporary table, as long as the connection has access permissions, you can use drop table tmp (or drop table tmp) to explicitly delete temporary tables.

We're still using the environment from Lab 05, omitting the process of preparing the data.

We still use two sessions, one session run, which is used to run the main SQL; Another session ps, which is used to make observations of the performance schema:

Reset the statistics in the performance schema, the table size limit of the temporary table depends on the smaller of the parameters tmp table size and max heap table size, we use setting max heap table size as an example in our experiment.

We set the session-level temporal table size to 2M (less than the space used by the temporal table in the last experiment) and execute the sql:using the temporal table

View memory allocation records:

We will find that the memory allocation is slightly greater than 2m, and we guess that the temporary table will consume a little more than the configuration and can be ignored.

You can see that the statement uses a temporary table that needs to be dropped once.

So how much disk does this temporary table use?

Redo the experiment, skip it.

Then look at the statistical values for the performance schema:

Several phenomena can be seen:

1.The temporary tablespace is written to .

2.This data is written slowly and gradually after the statement is written.

You can see that the thread on which the data is written is a page clean thread, which is a dirty operation, so that you can understand why the data is being written slowly.

You can also see that the size of each IO operation is 16K, which is the operation of swiping the data page.

Conclusion: As we can see, 1MySQL basically adheres to the Max Heap table size setting, and when the memory is insufficient, the table is directly transferred to disk for storage.

2.Due to the different engines (the in-memory table engine is HEAP, and the in-disk table engine follows the configuration of the Internal TMP Disk Storage Engine), the amount of data written to disk in this experiment is different from the amount of data used in memory in experiment 05.

3.If a temporary table uses disk and the table engine is configured as innodb, even if the temporary table is used in a short-term SQL statement and is released after use, dirty pages will be brushed to the disk after release, consuming part of the I/O.

and the relationship between the text data type and the temporary table.

When discussing the topic of how to get a temporary table on disk or a temporary table in memory, I think it is necessary to talk about two data types, blob and text. Both of these data types are used to store large volumes of data. The former is saved in binary form, while the latter is saved in character form.

These two data types are fundamentally different from the others. In a MySQL database, these two data types are treated as objects with entities. The storage engine will also use a special one.

MySQL requires the creation of implicit temporary tables to resolve certain types of queries. Often, the sorting phase of a query needs to rely on temporary tables. For example, when you use group by, order by, or distinct.

Such a query is executed in two stages: first by collecting the data and putting them into a temporary table, and then by performing sorting on the temporary table.

For some union statements, views that cannot be merged, derived tables are used for subqueries, multi-table updates, and some other cases, temporary tables are also required. If the temporary table is small, it can be created in memory, otherwise it will be created on disk. MySQL creates a table in memory that is converted to on-disk storage if it gets too big.

The maximum value of an in-memory temporary table is defined by the tmp table size or max heap table size value, whichever is less. The default size in MySQL is 16MB. You can increase the value if you run a query with a large amount of data, or if you haven't been queried optimized.

When setting thresholds, consider the amount of RAM available and the number of concurrent connections during peak periods. You can't increment variables indefinitely, because at some point you'll need to get MySQL to use temporary tables on disk.

Note: If the table in question has a text or blob column, a temporary table will be created on disk even if the size is less than the configured threshold.

The data and structure of temporary tables are stored in memory.

It's easy to create a temporary table by adding the temporary keyword to the normal create table statement

create temporary table tmp_table (name varchar(10) not null,value integer not null)

Temporary tables are not the same as memory tables. It's easy to get confused.

The table structure and data of a temporary table are stored in memory. When you use it, you can directly use the table structure of the memory table to store it on disk, and only the data is stored in memory.

To create a temporary table, you add a temporarycreate temporary table (Field 1 Constraints, Field 2 Constraints, nbsp; .

Use joins instead of sub-queries to pick the most applicable field attributes.

Use union instead of manually created temporary tables, use transactions, use foreign keys, and use index-optimized query statements.

Try to avoid using it as much as possible, and it will be optimized.

You can put some frequently accessed data into a temporary table, so that the access will be faster.

Because the data is in server memory.

In addition, every time you query, the database needs to generate some temporary data in the temporary table.

Operations on a temporary table do not affect the original table.

Temporary tables are visible only for the current connection, and when the connection is closed, MySQL automatically deletes the table and frees up all space. If you use a PHP script to create a MySQL temporary table, the temporary table will be automatically destroyed whenever the PHP script is executed.

Delete the MySQL temporary tableBy default, when you disconnect from the database, the temporary table is automatically destroyed. Of course, you can also use the drop table command in the current mysql session to manually delete temporary tables.

Here's an example of manually deleting a temporary table:

from Sloth Academy - A one-stop data knowledge platform.

Local Temporary Tables will automatically disappear when the current session ends, and will only be valid after the current callback, the global temporary table will disappear only after all the replies are over, and the global temporary table you created can be accessed by others.

Let's take a look at the temporary table of what kind of database. Generally speaking, the temporary tables of mssql and mysql do not need to be deleted, and will be cleared as long as the server is restarted, which is equivalent to an in-memory table, which is only stored in memory.

There are two types of temporary tables.

One is the ordinary temporary table.

create table table name.

Field Type Other.

to create. This kind of table is established even if others are invisible.

A section of the connection table is cleared.

There is also a type called a global temporal table when it is created.

create table table name.

Field Type Other.

This kind of table can be created to create a domain (a local connection line) that can be accessed, and this kind of table is a bit different from the previous one.

He must be logged out of all the accessing people, no one will connect, the access will be disconnected, the temporary table can be left alone, unless necessary, otherwise you can leave him alone, and when you disconnect, it will naturally be cleared.

Generally, the data will be automatically deleted when it is disconnected, or you can manually delete the table name.