Talking about filesort from the MySQL temporary table

The internal temporary table type is related to the generation time:Http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed byMEMORYStorage engine, or stored on disk and processed byMyISAMStorage engine. the server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it.

Sometimes the database server generates internal temporary tables when executing some queries. These temporary tables may be generated in the memory and processed by the memory engine, it may also be generated on the disk and processed by the MyISAM engine. If the size of the temporary table in the memory exceeds the limit, the server will save the temporary table as a temporary disk table. You cannot directly control these internal temporary tables and manage the database engines of these temporary tables.

Temporary tables can be created under conditions such as these:

When an internal temporary table is generated:

• If there isORDER BYClause and a differentGROUP BYClause, or ifORDER BYOrGROUP BYContains columns from tables other than the first table in the join queue, a temporary table is created.

• Use the order by clause and a different group by clause (after my experiment, it should be a group by non-index column, and a temporary table will be generated ), or if the column 'ORDER BY' or 'group by' is not from the first table of the join statement sequence, a temporary table is generated, group by any column will generate a temporary table)

• DISTINCTCombinedORDER BYMay require a temporary table.

• Temporary tables may be required when distinct and order by are used together. (In this experiment, a temporary table is generated when distinct (non-index column) is used)

• If you useSQL_SMALL_RESULTOption, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

• If SQL _small_result is used, MySQL uses a memory temporary table. Definition: SQL _big_resultOrSQL_SMALL_RESULTCan be usedGROUP BYOrDISTINCTTo tell the optimizer that the result set has rule rows or is small, respectively.SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key onGROUP BYElements.SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. This shocould not normally be needed.

 

To determine whether a query requires a temporary table, useEXPLAINAnd checkExtraColumn to see whether it saysUsing temporary. See section 8.8.1, "Optimizing queriesEXPLAIN".

You can use the explain statement to check whether a temporary table is used in the extra field.

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

In some cases, the server directly uses the disk temporary table

• Presence ofBLOBOrTEXTColumn in the table

• When blob or text exists in the table (this is because the memory engine does not support these two data types. Here I will add, not long as the query contains blob and text columns, a temporary disk table is generated. In high-performance MySQL, we should say this: "because the memory storage engine doesn't support the blob and text types, queries that use blob or text ColumnsAnd need an implicit temporary tableWill have to use on-disk MyISAM temporry tables, even for only a few rows. "That is to say, if our query contains blob and text columns and a temporary table is required, the temporary table will be forcibly converted to a temporary disk table. Therefore, this book keeps reminding us that, if you want to sort blob and text, use substring (column, length) to cut these columns into strings, so you can use the in-memory temporary table)

• Presence of any column inGROUP BYOrDISTINCTClause larger than 512 bytes

• The size of the Group by or distinct clause exceeds 512 bytes.

• Presence of any column larger than 512 bytes inSELECTList, ifUNIONOrUNION ALLIs used

• If Union or union all is used and the Select column contains more than 512 bytes

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. the maximum size for In-memory temporary tables is the minimum oftmp_table_sizeAndmax_heap_table_sizeValues. This differs fromMEMORYTables explicitly createdCREATE TABLE: For such tables,max_heap_table_sizeSystem variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

If the built-in memory temporary table becomes too large after it is created, MySQL automatically converts it to a disk temporary table. The size of the temporary memory table depends on the values of tmp_table_size and max_heap_table_size. The size of the memory temporary table generated by using create table depends on max_heap_table_size to determine whether to convert it to a disk temporary table.

When the server creates an internal temporary table (either in memory or on disk), it incrementsCreated_tmp_tablesStatus variable. If the server creates the table on disk (either initially or by converting an in-memory table) It incrementsCreated_tmp_disk_tablesStatus variable.

When the server generates a temporary memory table, the created_tmp_tables status variable value increases. When the server creates a temporary disk table, the created_tmp_disk_tables status variable value increases. (These variables can be viewed using the show status command)

TIPS: The size of internal temporaray table is limited by the minimum values of tmp_table_size and max_heap_table_size. The size of user-created temporary table is limited to max_heap_table_size, but not tmp_table_size. The original document is as follows. Note the bold Section.

 tmp_table_size

Command-line format	--tmp_table_size=#
Option-File Format	tmp_table_size
Option sets variable	Yes,tmp_table_size
Variable name	tmp_table_size
Variable Scope	Global, session
Dynamic variable	Yes
	Permitted values
	Type	numeric
	Default	system dependent
	Range	1024 .. 4294967295

The maximum size of internal in-memory temporary tables .(The actual limit is determined as the minimumtmp_table_sizeAndmax_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-diskMyISAMTable. Increase the valuetmp_table_size(Andmax_heap_table_sizeIf necessary) If you do have advancedGROUP BYQueries and you have lots of memory.This variable does not apply to user-createdMEMORYTables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values ofCreated_tmp_disk_tablesAndCreated_tmp_tablesVariables.

See also section 8.4.3.3, "how MySQL uses internal temporary tables ".

 max_heap_table_size

Command-line format	--max_heap_table_size=#
Option-File Format	max_heap_table_size
Option sets variable	Yes,max_heap_table_size
Variable name	max_heap_table_size
Variable Scope	Global, session
Dynamic variable	Yes
	Permitted values
	Platform bit size	32
	Type	numeric
	Default	16777216
	Range	16384 .. 4294967295
	Permitted values
	Platform bit size	64
	Type	numeric
	Default	16777216
	Range	16384 .. 1844674407370954752

This variable sets the maximum sizeWhich user-createdMEMORYTables are permitted to grow. The value of the variable is used to calculateMEMORYTableMAX_ROWSValues. setting this variable has no effect on any existingMEMORYTable, unless the table is re-created with a statement suchCREATE TABLEOr alteredALTER TABLEOrTRUNCATE TABLE. A server restart also sets the maximum size of existingMEMORYTables to the globalmax_heap_table_sizeValue.

This variable is also used in conjunctiontmp_table_sizeTo limit the size of internal in-memory tables. seesection 8.4.3.3, "how MySQL uses internal temporary tables ".

max_heap_table_sizeIs not replicated. See section 16.4.1.21, "replication andMEMORYTables ", andsection 16.4.1.34," replication and variables ", for more information.

--------------------------------------------------------------------

 

Filesort. What is filesort? Translate a blog from Baron Schwartz. Who is this guy? He is the first author of High Performance MySQL.

If you were interviewing to work at percona, and I asked you "what doesUsing filesortMean in explain, "what wocould you say?

I have asked this question in a bunch of interviews so far, with smart people, and not one person has gotten it right. so I consider it to be a bad interview question, and I'm going to put the answer here. if anyone gets it wrong from now on, I know they don't read this blog!

This section is in the bid.

The usual answer is something like "rows are being placed into a temporary table which is too big to fit in memory, so it gets sorted on disk. "Unfortunately, this is not the same thing. first of all, this isUsing Temporary. Secondly, temporary tables may go to disk if they are too big, but explain doesn't show that. (If I interview you, I might ask you what "too big" means, or I might ask youOtherReason temporary tables go to disk !)

The average person's answer is: "When the row data is too large, and the memory cannot accommodate the temporary tables generated by the data, they will be placed in the disk for sorting ." Unfortunately, this answer is wrong. First, this is called using temporary (see the extra field in expain or DESC). Second, when the temporary table is too large, it will indeed leave the disk, but the explain won't show this. (Bala ...)

The truth is, filesort is badly named. anytime a sort can't be saved med from an index, it's a filesort. it has nothing to do with files. filesort shoshould be called "sort. "It is quicksort at heart.

The fact is that filesort is too difficult to get. Filesort is called filesort if an index cannot be used for sorting. He has no relationship with file. Filesort should be called sort. (The author adds: it means that if the existing index cannot be used for sorting, the database server needs to perform additional data sorting, which will actually increase the performance overhead .)

If the sort is bigger than the sort buffer, It is med a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. there is a lot more to it than this. I refer you to Sergey petrunia's article on how MySQL executes order. you can also read about it in our book, but if you read Sergey's article you won't need.

If the sort data is larger than the sort buffer, the sorting will be divided into multiple parts, a small part is sorted each time, and all parts are merged and output (you just want to merge and sort them ). There are still many things to say here. We recommend that you read XXXXX or our books (High Performance MySQL, you have to say it is indeed a good book ). Bala...

OK. Now we know that God code is filesort. In fact, it does not have a half-cent relationship with temporary tables and files. You can try to use order by for an unindexed column. The using filesort will appear in extra.

... Amount... I put the temporary table and filesort together for Mao?