Example of MySQL order by performance optimization method

This article mainly introduced the MySQL order by performance Optimization Method Example, this article explained in the MySQL the order by principle and the optimized order by three kinds of methods, needs the friend to be possible to refer to under

Objective

During the work process, various business requirements require an order by sort when accessing the database. Sometimes unnecessary or unreasonable sort operations are likely to cause the database system to crash. How do I handle the order by sort? This article introduces the ordering by from the principle and the optimization level.

The principle of an order by in MySQL

1 obtaining ordered data with the order of index

When the order by condition of the query statement and the index key (or the previous key) used in the query's execution plan are exactly the same, and the index access is rang,ref or index, MySQL can take advantage of the indexed sequence to get the sorted data directly. This way of ordering by is basically the optimal sort of way, because MySQL does not need to do the actual sort operation. It should be noted that there are many limitations to using index sorting. This is explained in the following article.

2 using Memory/disk file sort to get results

Because there is no available ordered index to obtain ordered data, MySQL needs to use the appropriate sorting algorithm, the data obtained in the SORT_BUFFER_SIZE system variables set the size of the sorting area to sort, this sorting area is exclusive to each thread, so that may be at the same time in There may be more than one sort buffer memory area in MySQL.

In MySQL, there are two algorithms for implementing Filesort:

1) Two-way sorting: First, according to the appropriate conditions to remove the corresponding sort of fields and can directly locate row data of the row pointer information, and then sorted in the sort buffer.

2 One-way sorting: All fields that meet the criteria row are taken out and sorted in the sort buffer.

Before the MySQL4.1 version, only the first sorting algorithm, the second algorithm is the MySQL4.1 start of the improved algorithm, the main purpose is to reduce the first time the algorithm requires two access to the table data IO operation, two times into one, but the corresponding will also consume more sort buffer space. The typical optimization method is to change time in space. Of course, all the later versions of MySQL4.1 also support the first algorithm, and MySQL determines which sort algorithm to use by comparing the size of the system parameter Max_length_for_sort_data and the sum of the field type taken out by the query statement. If the max_length_for_sort_data is larger, the second optimized algorithm is used, and the first algorithm is used instead. Therefore, if you want the order by operation to be as efficient as possible, you need to pay attention to the setting of the Max_length_for_sort_data parameter.

Two optimizations by

How do you optimize the sort operation when you can't avoid it? It is clear that the first choice of the method of using index is preferred, and that, in the first way, it is possible for MySQL to choose a second single algorithm for sorting. This reduces the number of random IO operations and greatly improves the efficiency of the sorting process.

1 increase the setting of max_length_for_sort_data parameters

In MySQL, deciding whether to use an old-fashioned sort algorithm or an improved version of the sorting algorithm is determined by the parameter Max_length_for_ sort_data. When the maximum length of all returned fields is less than the value of this parameter, MySQL will choose the improved sorting algorithm, on the contrary, the old algorithm. So, if you have enough memory to keep MySQL in the sorted fields that need to be returned, you can increase the value of this parameter to let MySQL choose to use the improved version of the sorting algorithm.

2 get rid of unnecessary return fields

When the memory is not very abundant, can not simply forcibly increase the above parameters to force MySQL to use the improved version of the sorting algorithm, it may cause MySQL to have to divide the data into a number of segments, and then sorted, which may outweigh the gains. At this point, you need to remove the unnecessary return fields so that the length of the returned results fits the limit of the Max_length_for_sort_data parameter.

3 Increase sort_buffer_size parameter setting

If this value is too small, plus you return a lot of the number of bars, then it is very likely to be sorted many times, and then the final sequence of the results of the series together, so that will be more slowly, increase sort_buffer_size is not to allow MySQL to choose an improved version of the sorting algorithm, Rather, it is to allow MySQL to minimize the sort of data that needs to be sorted during the sort process, because fragmentation can cause MySQL to have to use temporary tables for Exchange sorting.

But this value is not the bigger the better:

The 1 sort_buffer_size is a connection-level parameter that allocates the set of memory for the first time each connection needs to use this Buffer.

2 Sort_buffer_size is not the larger the better, because it is connection-level parameters, too large settings + high concurrency may deplete the system memory resources.

3 It is said that when the sort_buffer_size exceeds 2M, it will use Mmap () instead of malloc () for memory allocation, resulting in lower efficiency.