Shulou(Shulou.com)05/31 Report--

This article mainly explains "the use of temporary files in MYSQL5.7". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the usage of temporary files in MYSQL5.7".

Use temporary files

First, use tmpdir

1. The file sort file name of the execution plan begins with MY.

Lsof | grep delete

For example, / tmp/MYdRH1GW (deleted)

2. The binary log cache file name for big things begins with ML

Lsof | grep delete

For example, / tmp/MLq9INFu (deleted)

3. Compressed tempory table

CREATE TEMPORARY TABLE tmp_table1 (id int) ROW_FORMAT=COMPRESSED

Ls / tmp/

Such as:

# sql6b82_6_7.frm

# sql6b82_6_7.ibd

4. Online DDL involves sorting, such as add key

Alter table testsort add key (id)

Lsof | grep delete

Such as:

/ tmp/ibCxlYQg (deleted)

/ tmp/ib51nvZ1 (deleted)

Set innodb_tmpdir to put such files in the specified directory

Second, use innodb_temp_data_file_path

1. The execution plan use temporay table 5.7 will be the internal table of innodb.

2. Uncompressed tempory table

CREATE TEMPORARY TABLE tmp_table1 (id int)

You can use select * from INNODB_TEMP_TABLE_INFO; query

These are also not shown as innodb tables.

Third, use innodb data

It's online ddl.

1. The name of ALGORITHM copy is # sql-

Alter table testsort ALGORITHM=copy, add im int

# sql-6b82_6.frm

# sql-6b82_6.ibd

2. The name of ALGORITHM inplace is # sql-ib

Alter table test add key id int

such as

# sql-6b82_6.frm

# sql-ib59-867962583.ibd

But when it comes to sorting, such as add key, use tmpdir or innodb_tmpdir to see above.

Thank you for your reading, the above is the content of "the use of temporary files in MYSQL5.7", after the study of this article, I believe you have a deeper understanding of the use of temporary files in MYSQL5.7, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.