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

Formal answer to the question:

Under what circumstances will MySQL create a temporary table (Internal Temporary Table Use in MySQL)?

I'll list three.

1. UNION query

2. Insert into select... from...

3. When the clauses of ORDER BY and GROUP BY are different

4. The data table contains blob/text columns

Wait, there's actually a lot more. Refer to https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html for more information.

2. How do you know that mysql uses a temporary watch?

The problem is simple. EXPLAIN looks in the Extra column of the results of the execution plan, and if it contains Using Temporary, it means that temporary tables will be used. For example, there is a perceptual perception.

Create test table T22: create table T22 as select * from information_schema.tables

Mysql > desc T22 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | TABLE_CATALOG | varchar (512) | NO | | TABLE_SCHEMA | varchar (64) | NO | | TABLE_NAME | varchar (64) | NO | | TABLE_TYPE | varchar (64) | NO | | ENGINE | varchar (64) | YES | | NULL | | VERSION | bigint (21) unsigned | YES | | NULL | | ROW_FORMAT | varchar (10) | YES | | NULL | | | TABLE_ROWS | bigint (21) unsigned | YES | | NULL | AVG_ROW_LENGTH | bigint (21) unsigned | YES | | NULL | | DATA_LENGTH | bigint (21) unsigned | YES | | NULL | | MAX_DATA_LENGTH | bigint (21) unsigned | YES | NULL | | INDEX_LENGTH | bigint (21) unsigned | YES | | NULL | | | DATA_FREE | bigint (21) unsigned | YES | | NULL | AUTO_INCREMENT | bigint (21) unsigned | YES | | NULL | | CREATE_TIME | datetime | YES | | NULL | | UPDATE_TIME | datetime | YES | NULL | | CHECK_TIME | datetime | YES | NULL | | | | TABLE_COLLATION | varchar (32) | YES | | NULL | | CHECKSUM | bigint (21) unsigned | YES | | NULL | | CREATE_OPTIONS | varchar (255) | YES | | NULL | | TABLE_COMMENT | varchar (2048) | NO | +-| -- +-+ 21 rows in set (0.02 sec) mysql > explain-> select table_schema Table_name, create_time from T22 where table_schema like 'test%'-> union-> select table_schema, table_name, create_time from T22 where table_schema like' information%'-> +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | T22 | NULL | ALL | NULL | 12522369 | 11.11 | Using where | | 2 | UNION | T22 | | NULL | ALL | NULL | 12522369 | 11.11 | Using where | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -- +-+ 3 rows in set 1 warning (0.02 sec)

What are the parameters related to the temporary table?

Innodb_temp_data_file_path = ibtmp1:12M:autoextend

Tmp_table_size = 16777216

Max_heap_table_size = 16777216

Default_tmp_storage_engine=InnoDB

Internal_tmp_disk_storage_engine= InnoDB

4. The configuration parameter of mysql temporary table is tmp_table_size. What should I do when there is not enough temporary table space?

If the amount of data that needs to be stored in the temporary table exceeds the upper limit (whichever is the greater in tmp-table-size or max-heap-table-size), you need to generate a disk-based temporary table. That is, in the temporary tablespace specified by innodb_temp_data_file_path.

If you have any questions about this sentence, let me give you an example: repeatedly execute the statement: insert into T22 select * from T22; also check the size changes in the tablespace ibtmp1. Execute the insert statement repeatedly, and the number of inserts in the table grows exponentially.

Take a look at the example:

Fifth, look at the picture and do the last experiment. I don't know if you have the following idea: since the internal temporary table (Internal Temporary Table) is used for sorting and grouping, use temporary table space when the required storage space exceeds the upper limit of tmp-table-size. Temporary tablespaces are disks and are not as fast as memory, so is it possible to increase tmp_table_size to optimize SQL statements that require temporary tables?

Of course, the maximum tmp_table_size is 18446744073709551615, if 256m is recommended.

6. How to monitor the use of temporary tables and temporary tablespaces in mysql?

Mysql > show status like'% tmp%' +-+-+ | Variable_name | Value | +-+-+ | Created_tmp_disk_tables | 1 | Created_tmp_files | 7 | Created_tmp_tables | 18 | + -+

It is recommended that the Created_tmp_disk_tables/Created_tmp_tables should not exceed 25%. If the number of Created_tmp_disk_tables is large, check to see if there are many slow sql and if there are many statements that use temporary tables. Increase the tmp_table_size value.

7. The temporary tablespace files of mysql have exploded, reaching hundreds of gigabytes. What do you think is the reason for this?

As an example of the fourth question, if you keep experimenting over and over again, you will find that ibtmp1 is growing at an amazing rate. There is a project where ibtmp1 has skyrocketed to 300G. As soon as you look at the slow sql log, you can see that there are a lot of slow sql and a lot of statements to sort. So add the maximum limit to ibtmp1. Innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G,mysql will be used repeatedly.

Reference: Lao Ye Teahouse

Https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=207355450&idx=3&sn=3e3a2c0a7497a8cd099ddc5c33a9932d&scene=21#wechat_redirect

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.