In mysql, filesort refers to file sorting, which sorts the obtained data in memory through the corresponding sorting algorithm. There are two types of filesort: 1. Dual-way sorting, which first takes out the corresponding sorting field and the row pointer information that can directly locate the row data according to the corresponding conditions, and then sorts it in the sort buffer; 2. Single-way sorting, which is a one-time sorting Take out all the fields of the rows that meet the condition, and then sort them in the sort buffer.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
There are two sorting implementation methods for ORDER BY in MySQL:
1. Use ordered index to obtain ordered data
2. File sorting (filesort)
When analyzing the query in explain, use the ordered index to obtain the ordered data and display Using index, and file sorting displays Using fileso rt.
Only when all columns in ORDER BY must be included in the same index, and the order of the index is exactly the same as the order in the order by clause, and the sort direction of all columns (ascending order Or descending order) are the same, (when using ASC mode and DESC mode mixedly, no index is used)
The combination of where statement and order by statement satisfies the leftmost prefix
In other cases, use file sorting as follows:
1) where statement and order by statement , using different indexes
2) Too many rows are checked, and no covering index is used
3) In ORDER BY Columns are not included in the same index, that is, different indexes are used
‑ ‑4) Both ASC and DESC are used for index columns
‑ ‑ 5) The index column in the where statement or ORDER BY statement uses expressions, including function expressions
∼ 6) The combination of where statement and ORDER BY statement satisfies the leftmost prefix, but in the where statement What is being searched for is the range.
This filesort does not mean sorting through disk files, but only tells us that a sorting operation has been performed. That is, the execution plan given by MySQL Query Optimizer (viewed through the EXPLAIN command) is called file sorting (filesort)
File sorting is to sort the obtained data in memory through the corresponding sorting algorithm : MySQL needs to sort data in memory, and the memory area used is the sorting area set by the sort_buffer_size system variable. This sorting area is exclusive to each Thread, so there may be multiple sort buffer memory areas in MySQL at the same time.
There are two types of filesort
Dual-way sorting: First, the corresponding sorting field is taken out according to the corresponding conditions and the row data can be directly located. Row pointer information is then sorted in the sort buffer. After sorting, the query field is taken out according to the row pointer, and disk IO is executed twice.
Single-way sorting: All fields that meet the conditional rows are taken out at one time, and then sorted in the sort buffer. Execute disk io once.
MySQL mainly determines which sorting algorithm needs to be used by comparing the size of the system parameter max_length_for_sort_data we set and the sum of the field type sizes taken out by the Query statement. If max_length_for_sort_data is larger, the second optimized algorithm is used, otherwise the first algorithm is used. So if you want the efficiency of the ORDER BY operation to be as high as possible, you must be careful about the setting of the max_length_for_sort_data parameter. A colleague's database once experienced a large number of sorting waits, resulting in high system load and long response time. Finally, it was found that MySQL used the traditional first sorting algorithm. After increasing max_length_for_sort_data After changing the parameter value, the system load was immediately greatly relieved and the response was much faster.
If the order by clause only refers to the first table in the join, MySQL will sort the first table first and then join. That is, the Using Filesort of Extra in expain. Otherwise, MySQL first saves the results to the temporary table (Temporary Table), and then sorts the data in the temporary table. At this time, the Extra in expain displays Using temporary Using Filesort.
【Related recommendations: mysql video tutorial】
The above is the detailed content of What is filesort in mysql. For more information, please follow other related articles on the PHP Chinese website!