MySQL order by performance optimization example

MySQL order by performance optimization example

This article mainly introduces the MySQL order by performance optimization method example. This article explains the principle of order by in MySQL and the three methods to optimize order by. For more information, see

Preface

In the course of work, various business requirements require order by sorting when accessing the database. Sometimes unnecessary or unreasonable sorting operations may cause the database system to crash. How can we sort order? This article introduces order by in terms of principle and optimization.

I. Principle of order by in MySQL

1. Use the order of indexes to obtain ordered data

When the order BY condition of the query statement is exactly the same as the Index key used in the query execution plan (or the previous Index keys), and the Index access method is rang, when using ref or index, MySQL can use the index order to directly obtain sorted data. The order BY method is basically the Optimal Sorting method, because MySQL does not need to perform the actual sorting operation. Note that index sorting has many restrictions. This is explained later.

2. obtain results by sorting memory/Disk Files

Because there is no available ordered index to obtain ordered data, MySQL needs to sort the obtained data in the sorting area of the sort_buffer_size system variable through the corresponding sorting algorithm, this sorting area is exclusive to each Thread, So multiple sort buffer memory areas may exist in MySQL at the same time.

In MySQL, there are two filesort implementation algorithms:

1) Dual-path sorting: first, obtain the corresponding sorting fields and the row pointer information that can directly locate the row data based on the corresponding conditions, and then sort the data in sort buffer.

2) single-path sorting: all fields that meet the condition rows are retrieved at a time and sorted 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. The typical optimization mode is space-for-time. 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.

2. Optimize order

How can we optimize sorting operations that cannot be avoided? Obviously, the first using index sorting method is preferred. If the first method cannot be satisfied, MySQL should try its best to use the second single-path algorithm for sorting. This can reduce a large number of random IO operations and greatly improve the efficiency of sorting.

1. Set the max_length_for_sort_data parameter.

In MySQL, it is determined whether to use the old-fashioned or simplified Sorting Algorithm by using 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 there is sufficient memory for MySQL to store non-sorted fields that need to be returned, you can increase the value of this parameter so that MySQL can choose to use the sort algorithm of the latest version.

2 remove unnecessary returned Fields

When the memory is not abundant, you cannot force MySQL to use the sort algorithm by adding the preceding parameters. Otherwise, MySQL may have to divide the data into multiple segments, then sort the data, which may be worth the candle. In this case, you need to remove unnecessary return fields to adapt the length of the returned results to the constraints of the max_length_for_sort_data parameter.

3. Increase the value of sort_buffer_size.

If this value is too small, and you return too many results at a time, you may sort the results multiple times, and then sort the results at last, this will take longer. Increasing sort_buffer_size is not to allow MySQL to select the sort algorithm, but to minimize the number of data segments to be sorted in the sorting process, because segmentation will cause MySQL to have to use temporary tables for exchange sorting.

But the value is not larger, the better:

1 Sort_Buffer_Size is a connection-level parameter. When this buffer is required for each connection for the first time, the configured memory is allocated at a time.

2 Sort_Buffer_Size is not as large as possible. Because it is a connection-level parameter, excessive settings + high concurrency may exhaust system memory resources.

3 It is said that when Sort_Buffer_Size exceeds 2 MB, mmap () instead of malloc () will be used for memory allocation, resulting in reduced efficiency.