Orderby_MySQL in mysql

I. orderby principle 1. use the order of indexes to obtain ordered data. when the orderBY condition of the query statement and the Index key used in the query execution plan (or the previous Index keys) completely consistent, and the index access method is range, ref or index... i. principle of order

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 mode is range, when using ref or index, MySQL can use the index order to directly obtain sorted data. In this way, order BY is basically the optimal sorting method, because MySQL does not need to perform actual sorting operations. Note that there are also many restrictions on using index sorting.

When sorting the join operation, if order by only references the column of the first table, MySQL performs the filesort operation on the table and then performs the connection processing, EXPLAIN outputs "Using filesort"; otherwise, MySQL must generate a temporary table for the query result set and perform the filesort operation after the connection is complete. at this time, EXPLAIN outputs "Using temporary; using filesort ".

Note: MySQL can only use one index during query. Therefore, if the WHERE condition already occupies an index, the index will not be used in sorting.

mysql> show create table test \G*************************** 1. row ***************************       Table: testCreate Table: CREATE TABLE `test` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  `a` int(11) unsigned NOT NULL,  `b` int(11) unsigned NOT NULL,  `c` int(11) unsigned NOT NULL,  `data` varchar(20) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `u` (`addtime`,`a`,`b`),  KEY `a` (`a`),  KEY `b` (`b`),  KEY `c` (`c`)) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

Execute query 1

Mysql> explain select * from test where addtime = '2017-10-13 15:38:32 'order by a, B;

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ------------- +

| 1 | SIMPLE | test | ref | u | 8 | const | 4 | Using where |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ------------- +

Execute query 2

Mysql> explain select * from test where addtime = '2017-10-13 15:38:32 'order by a, B, c;

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ----------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ----------------------------- +

| 1 | SIMPLE | test | ref | u | 8 | const | 4 | Using where; Using filesort |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ----------------------------- +

Use of filesort

A. The where statement and order by statement use different indexes.

B. check for too many rows without overwriting indexes

C. Both ASC and DESC are used for index columns.

D. The index column in the where statement or order by statement uses an expression, including a function expression.

E. The combination of the where statement and the order by statement satisfies the leftmost prefix, but the where statement uses the conditional query

F. when left join is used, table fields on the right are sorted.

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.

There are two Implementation Algorithms for filesort in MySQL:

1) dual-path sorting: First, retrieve the corresponding sorting fields and the row pointer information that can directly locate the row data according to the corresponding conditions, then sort in sort buffer (but this will be essential hit the table in random order and is not very fast ).

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.

3. use the Using temporary table for filesort

If the order by clause only references the first table in the join operation, MySQL first sorts the first table and then joins the table. in expain, Using Filesort appears.

Otherwise, MySQL first saves the results to the Temporary Table, and then sorts the data in the temporary Table. in this case, the display of the Extra in expain Using Temporary Using Filesort

4. Join sorting

There are three methods in mysql

1) Use index-based access method that produces ordered output-"null

2) Use filesort () on 1st non-constant table-"Using filesort" in the first row

3) Put join result into a temporary table and use filesort () on it-"Using temporary; Using filesort" in the first row

5. For more information about filesort, see What does Using filesort mean in MySQL?

From original

The truth is, filesort is badly named. anytime a sort can't be saved Med from an index, it's a filesort. it has nothing to do with files. filesort shoshould be called "sort. "It is quicksort at heart.

If the sort is bigger than the sort buffer, it is Med a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. there is a lot more to it than this. I refer you to Sergey Petrunia's article on How MySQL executes ORDER. you can also read about it in our book, but if you read Sergey's article you won't need.

There is a misunderstanding here, which is easy to understand. what does filesort in the output information of the Explain command mean? In fact, it is easy to tell you that MySQL needs to perform actual sorting operations instead of obtaining sorted data through indexes.

1) filesort (in fact, sorting) may not necessarily generate a temporary table

2) there is no direct connection between filesort and writing data from temporary tables to the disk.

II. 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 return 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.