The implementation analysis of MySQL ORDER by _mysql

The following examples are used to analyze two kinds of sequencing implementations and their implementation diagrams:
Suppose there are tables A and B two table structures are as follows:
Sky@localhost:example 01:48:21> Show CREATE TABLE AG
1. Row ***************************
Table:a
Create table:create Table ' A ' (
' C1 ' int (one) not NULL default ' 0′,
' C2 ' char (2) Default NULL,
' C3 ' varchar default NULL,
' C4 ' datetime default NULL,
PRIMARY KEY (' C1 ')
) Engine=myisam DEFAULT Charset=utf8

Sky@localhost:example 01:48:32> Show CREATE TABLE BG
1. Row ***************************
Table:b
Create table:create Table ' B ' (
' C1 ' int (one) not NULL default ' 0′,
' C2 ' char (2) Default NULL,
' C3 ' varchar default NULL,
PRIMARY KEY (' C1 '),
KEY ' B_c2_ind ' (' C2 ')
) Engine=myisam DEFAULT Charset=utf8

1, the use of ordered index sorting, in fact, when we query the order by conditions and query execution plan used in the index key (or the previous key) exactly the same, and the index access mode is rang, ref or index, MySQL You can use the index order to get the data that has been sorted directly. This way of ordering by is basically the optimal sort of way, because MySQL does not need to do the actual sort operation.

Let's say we execute the following SQL on table A and B:
Sky@localhost:example 01:44:28> EXPLAIN SELECT a.* from A,b
-> WHERE a.c1 > 2 and A.c2 < 5 and a.c2 = b.c2 ORDER by a.c1g
1. Row ***************************
Id:1
Select_type:simple
Table:a
Type:range
Possible_keys:primary
Key:primary
Key_len:4
Ref:null
Rows:3
Extra:using where
2. Row ***************************
Id:1
Select_type:simple
Table:b
Type:ref
Possible_keys:b_c2_ind
Key:b_c2_ind
Key_len:7
Ref:example. A.c2
Rows:2
Extra:using where; Using Index

As we can see from the execution plan, MySQL actually does not do the actual sort operation, and in fact its entire execution process is as shown in the following illustration:

2, through the corresponding sorting algorithm, the data obtained in the sort of memory, MySQL than need to put the data in the memory of the sort, the use of memory area is that we through the Sort_buffer_size system variables set by the sorting area. This sort area is exclusive to each Thread, so it is possible that multiple sort buffer memory areas may exist in MySQL at the same time.

The second way is called Filesort in the execution plan given by the MySQL Query Optimizer (viewed through the EXPLAIN command). In this way, the main reason is that no ordered index can be used to obtain ordered data, MySQL can only be obtained by sorting the data in memory and then return the data to the client. In MySQL, the implementation of Filesort algorithm is in fact there are two, one is 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 in the sort buffer sorted. The other is to remove all fields that meet the criteria row at once, and then sort 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. Of course, all the later versions of MySQL4.1 also support the first algorithm, MySQL mainly by comparing the size of the system parameters we set Max_length_for_sort_data and Query The sum of the size of the field type taken out by the statement to determine which sort algorithm to use. If the max_length_for_sort_data is larger, the second optimized algorithm is used, and the first algorithm is used instead. So if you want the order by operation to be as efficient as possible, be sure to set the Max_length_for_sort_data parameter. There have been a number of colleagues in the database of the sorting wait, resulting in high system load, and response time has become very long, and finally found that it is because MySQL used the traditional first sorting algorithm, after increasing the Max_length_for_sort_data parameter value, The system load was quickly relieved and the response was much faster.

Let's take a look at MySQL's instance of using the Filesort implementation to sort.

Let's say we change our Query to sort by a.c2, and then look at the situation:
Sky@localhost:example 01:54:23> EXPLAIN SELECT a.* from A,b
-> WHERE a.c1 > 2 and A.c2 < 5 and a.c2 = b.c2 ORDER by a.c2g
1. Row ***************************
Id:1
Select_type:simple
Table:a
Type:range
Possible_keys:primary
Key:primary
Key_len:4
Ref:null
Rows:3
Extra:using where; Using Filesort
2. Row ***************************
Id:1
Select_type:simple
Table:b
Type:ref
Possible_keys:b_c2_ind
Key:b_c2_ind
Key_len:7
Ref:example. A.c2
Rows:2
Extra:using where; Using Index

MySQL takes the qualifying data from Table A, and because the data obtained does not satisfy the order by condition, MySQL performs the filesort operation, and the entire execution process is as shown in the following illustration:

In MySQL, the filesort operation also has a strange limitation, that is, its data source must be from a table, so if our sort data is two (or more) Table through the join, then the MySQL You must sort the data for this temporary table by first creating a temporary table (temporary table), as shown in the following example:

Sky@localhost:example 02:46:15> explain select a.* from A,b
-> where A.c1 > 2 and A.c2 < 5 and a.c2 = b.c2 ORDER by b.c3g
1. Row ***************************
Id:1
Select_type:simple
Table:a
Type:range
Possible_keys:primary
Key:primary
Key_len:4
Ref:null
Rows:3
Extra:using where; Using temporary; Using Filesort
2. Row ***************************
Id:1
Select_type:simple
Table:b
Type:ref
Possible_keys:b_c2_ind
Key:b_c2_ind
Key_len:7
Ref:example. A.c2
Rows:2
Extra:using where

The output of this execution plan is still a little odd, and somehow, MySQL Query Optimizer the "Using temporary" process in the first row of Table A, just to make the output of the execution plan less than one line?

The actual execution process should be as shown in the following illustration: