Analysis of sorting in Mysql in SQL Optimization

In Mysql, the sorting in SQL optimization is partially parsed in mysql. The copied SQL statements normalize the search results. Here there are sorting and grouping, statistics and other related sorting processes. sorting algorithms are of the greatest significance. In different mysql versions, especially after the 5.1 series, sorting algorithms are constantly improved; in the sorting algorithm, the array width, function search range, size, and affected height mentioned in the new and old algorithms are modified, and related threshold values are set, to guide the automatic closure of related parameters; The following is the collection of related sort files in the 5.5 series; [html] # define UT_SORT_FUNCTION_BODY (SORT_FUN, ARR, AUX_ARR, LOW, HIGH, CMP_FUN) \ {\ ulint ut_sort_mid77; \ ulint ut_sort_i77; \ ulint ut_sort_low77; \ ulint ut_sort_high77; \ ut_ad (LOW) <(HIGH); \ ut_ad (ARR); \ ut_ad (AUX_ARR); \ if (LOW) = (HIGH)-1) {\ return; \} else if (LOW) = (HIGH)-2) {\ if (CMP_FUN (ARR) [LOW], (ARR) [(HIGH) -1])> 0) {\ (AUX_ARR) [LOW] = (ARR) [LOW]; \ (ARR) [LOW] = (ARR) [(HIGH) -1]; \ (ARR) [(HIGH)-1] = (AUX_ARR) [LOW]; \}\ return ;\}\\ ut_sort_mid77 = (LOW) + (HIGH)/2; \ SORT_FUN (ARR), (AUX_ARR), (LOW), ut_sort_mid77); \ SORT_FUN (ARR ),( AUX_ARR), ut_sort_mid77, (HIGH); \ ut_sort_low77 = (LOW); \ ut_sort_high77 = ut_sort_mid77; \ for (LOW); ut_sort_i77 <(HIGH ); ut_sort_i77 ++) {\ if (ut_sort_low77> = ut_sort_mid77) {\ (AUX_ARR) [ut_sort_i77] = (ARR) [ut_sort_high77]; \ ut_sort_high77 ++; \} else if (ut_sort_high77> = (HIGH) {\ (AUX_ARR) [ut_sort_i77] = (ARR) [ut_sort_low77]; \ ut_sort_low77 ++; \} else if (CMP_FUN (ARR) [Ut_sort_low77], \ (ARR) [ut_sort_high77])> 0) {\ (AUX_ARR) [ut_sort_i77] = (ARR) [ut_sort_high77]; \ ut_sort_high77 ++; \} else {\ (AUX_ARR) [ut_sort_i77] = (ARR) [ut_sort_low77]; \ ut_sort_low77 ++; \}\\ memcpy (void *) (ARR) + (LOW), (AUX_ARR) + (LOW), \ (HIGH)-(LOW) * sizeof * (ARR); \} \ parameter max_length_for_sort_data; in MySQL, the decision to use the first old-fashioned sorting algorithm or the new improved algorithm is based on the max_length_for_sort_data parameter. When the maximum length of all returned fields is smaller than this parameter value, MySQL selects the improved sorting algorithm. Otherwise, it selects the old algorithm. Therefore, if we have enough memory for MySQL to store non-sorted fields to be returned, we can increase the value of this parameter so that MySQL can choose to use the sort algorithm of the latest version. The sort_buffer_size parameter increases sort_buffer_size not to allow MySQL to select the sort algorithm, but to minimize the number of data segments to be sorted during the sorting process, this will cause MySQL to have to use temporary tables for exchange sorting. In addition, we can avoid related fields. Select the desired fields to be returned.