MySQL Optimization-using filesort

In MySQL, there are actually two filesort implementation algorithms. One is to first retrieve the corresponding sorting fields based on the corresponding conditions and the row pointer information that can be directly located in the row data, then sort in sort buffer. The other is to retrieve all fields that satisfy the condition row at a time and sort them in sort buffer.

Only the first sorting algorithm was available before mysql. The second algorithm was an improved algorithm starting with MySQL. The main purpose was to reduce the I/O operations required to access table data twice in the first algorithm, it is converted to one time twice, but it also consumes more sort buffer space. Of course, All Versions later than MySQL4.1 also support the first algorithm, mySQL mainly compares the size of the system parameter max_length_for_sort_data and the sum of the Field Types retrieved by the Query statement to determine which sort algorithm to use. If max_length_for_sort_data is larger, the second optimized algorithm is used. Otherwise, the first algorithm is used. Therefore, if you want the order by operation to be as efficient as possible, pay attention to the setting of the max_length_for_sort_data parameter.

MySQL optimization Case Analysis

MySQL optimization: configurable WAIT_FOR_READ

MySQL Optimization in CentOS