MySQL using filesort using temporary

using Filesort

The average person's answer is, "when the row data is too large to allow the memory to tolerate temporary tables generated by the data, they are placed on the disk to sort." Unfortunately, the answer is wrong, and the temp table does go to disk when it's too big, but explain doesn't show it.

The truth is, Filesort is badly named. Anytime a sort can ' t is performed from an index, it ' s a filesort. It has no to does with files. Filesort should be called "sort." It's quicksort at heart.

So the fact is, the name of Filesort is too rubbing force. Filesort means that as long as a sort cannot be sorted using an index, it is called Filesort. He doesn't have a half-penny relationship with file. Filesort should be called sort. (the author adds: it means that if you can't sort by the existing index, then you need to sort the database server for additional data, which actually increases the performance overhead.) ）

Also not only the order by will appear Filesort GROUP by does not use the index as it will appear

Check the sort status

Show session status like '%sort% ';

Sort_merge_passes because the sort buffer is not large enough, you have to segment the data that needs to be sorted, and then use the sort merge algorithm to complete the total number of merge times for the entire process, and the general parameter is used to refer to the sort buffer size enough.

Sort range Session/global level (units: times) The total number of sorts completed by range scan.

Sort rows Session/global The total number of rows sorted by the level (units: Row).

Sort scan completes the total number of sorts by scanning the table.

How do you optimize a sort operation when it cannot be avoided? Obviously, you should try to make MySQL choose to use the second single-Path algorithm for sorting. This can reduce the number of random IO operations, greatly improving the efficiency of the sorting work.

1. Set the index appropriately to sort the sort fields using the on index

2. Increase the setting of the Max_length_for_sort_data parameter

3. Remove unnecessary return fields

When the memory is not very abundant, it is not easy to force MySQL to use the improved sorting algorithm by forcing the above parameters, otherwise MySQL will have to divide the data into many segments and then sort, which may outweigh the damage. It is necessary to remove the unnecessary return field, so that the length of the returned result is adjusted to the limit of the Max_length_for_sort_data parameter.

4. Increase the sort_buffer_size parameter setting

The increase in sort_buffer_size is not to allow MySQL to choose the improved sorting algorithm, but to make MySQL minimize the need to sort the data in the sorting process, because the fragmentation will cause MySQL to use temporary tables to exchange sorting.

Using Temporary

The timing of an internal temporal table is as follows:

• Use the ORDER BY clause with a different GROUP BY clause (which, after the author's experiment, should be a group by an index-less column that produces a temporary table), or if the column of the order by or GROUP by is not the first table from the join statement sequence, a temporary table is generated (by the author Experiment, should be using join, GROUP by any column will produce temporary table)

• Temporary tables may be required when DISTINCT and ORDER by are used (the author experimented with DISTINCT (non-indexed columns), resulting in temporary tables)

• 用了 SQL_SMALL_RESULT, mysql就会用内存临时表。定义：SQL_BIG_RESULTOr SQL_SMALL_RESULT can be used GROUP BY with or to tell DISTINCT the optimizer, the result set have many rows or is small, respectively . SQL_BIG_RESULTfor, MySQL

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

• When there is a blob or text in the table (this is because the memory engine does not support these two data types, the author adds, not as long as the query contains a BLOB and text type column will produce a disk temporary table, according to high-performance MySQL, it should be said: "Because The Memory storage engine doesn ' t support the blob and text types, queries this use BLOB or text columns and need an I Mplicit temporary table would have the use of On-disk MyISAM temporry tables, even for only a few rows. " That is, if our query contains a BLOB and text columns, and a temporary table is required, then the temporary table is forced to use the disk temporary table, so this book has been reminding us that if you want to sort the blob and text, you should use SUBSTRING (column, Length) truncates these columns into strings, so you can use the in-memory temp table.
• GROUP by or DISTINCT clause size exceeds Bytes
• Union or union All is used and the joins of SELECT has more than bytes columns

If the built-in memory temporary table becomes too large after it is created, MySQL automatically converts it into a disk temp table. The size of the memory temp table depends on the value of the Tmp_table_size parameter and the max_heap_table_size parameter. The size of the memory temp table generated with the CREATE table depends on the max_heap_table_size to decide whether to convert it to a disk temp table

When the server generates a temporary memory table, the value of the Created_tmp_tables state variable increases, and the value of the Created_tmp_disk_tables state variable increases when the server creates a temporary disk table. (These variables can be viewed by the show status command)

The size of the tips:internal Temporaray table is limited by the minimum values of tmp_table_size and max_heap_table_size, while user-created temporary Table size is limited to max_heap_table_size, and tmp_table_size is irrelevant

MySQL using filesort using temporary