Usage index optimization of ORDER BY statement in MySQL

The MySQL order BY keyword is used to classify the data in the record.

MySQL ORDER BY keyword classified by keyword

The ORDER BY keyword is used to classify the data in a record.

MySQL ORDER BY syntax

The code is as follows	Copy Code
SELECT column_name (s) From table_name ORDER BY column_name

NOTE: The SQL statement is a "letter case insensitive" statement (it does not distinguish between uppercase and lowercase letters), that is, "order by" is the same as the "by".

The ORDER BY keyword is used to sort the data in the recordset.

Example
The following example selects all the data stored in the "Persons" table and sorts the results according to the "Age" column:

The code is as follows	Copy Code
<?php $con = mysql_connect ("localhost", "Peter", "abc123"); if (! $con) { Die (' Could not connect: '. Mysql_error ()); } mysql_select_db ("my_db", $con); $result = mysql_query ("SELECT * from Persons"); while ($row = Mysql_fetch_array ($result)) { echo $row [' FirstName ']; echo "". $row [' LastName ']; echo "". $row [' age ']; echo "<br/>"; } Mysql_close ($con); ?>

The output of the above code:

The code is as follows	Copy Code
Glenn Quagmire 33 Peter Griffin 35

Sort in ascending or descending order
If you use the ORDER by keyword, the sort orders for the recordset are ascending by default (1 before 9, "a" before "P").

Use the DESC keyword to set a descending sort (9 before 1, "P" before "a"):

Note:

If we use ORDER by (DESC) when executing a SELECT statement, it first sorts all records by keyword, and then reads the required records sequentially instead of selecting the records and then descending the order

analysis of the implementation of MySQL order by

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

The code is as follows

Copy Code

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:

code is as follows

copy code

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 ***************************< br> id:1 select_type:simple table:a type:range possible_keys:primary key:primary key_len:4< br> 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 Re F: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:

code is as follows

copy code

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 ***************************< br> id:1 select_type:simple table:a type:range possible_keys:primary key:primary key_len:4< br> 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 eligible data out of Table A, because the data obtained does not satisfy the order by condition, so MySQL filesort operation, in MySQL, filesort operation there is a strange limitation, that is, its data source must is derived from a table, so if our sort data is two (or more) table by join, MySQL must first create a temporary table (temporary table) and then sort the data for this temporary table, as shown in the following example:

code is as follows

copy code

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" procedure in the first row of Table A, just to make the output of the execution plan less than one line

MySQL Order by index optimization method

Although an order by is not exactly the same as an index, an index can be used, as long as the unused index portion and all the extra orders by fields are included in the WHERE clause.

Use the indexed MySQL order by
Several of the following queries use the index to resolve the order by or GROUP by part:

The code is as follows	Copy Code
SELECT * from T1-Key_part1,key_part2,...; SELECT * from T1 WHERE key_part1=constant order by Key_part2; SELECT * from T1 WHERE key_part1=constant GROUP by Key_part2; SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 DESC; SELECT * from T1 WHERE key_part1=1 order by Key_part1 DESC, Key_part2 DESC;

MySQL order by with no index
In other cases, MySQL cannot use an index to satisfy an order by, although it uses the index to find records to match the WHERE clause. These situations are as follows:
* The different index keys are made by:

The code is as follows	Copy Code
SELECT * from T1-Key1, Key2;

* In the noncontiguous index key section, do ORDER by:

The code is as follows	Copy Code
SELECT * from T1 WHERE key2=constant order by Key_part2;

* Both ASC and DESC are used:

The code is as follows	Copy Code
SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;

* The index keys used to search for records are not the same as the order by:

The code is as follows	Copy Code
SELECT * from T1 WHERE key2=constant order by Key1;

* There are many tables connected together, and the fields in the read record are not all from the first very many tables (that is, the connection type of the first table in the result of EXPLAIN analysis is not const).
* Different order BY and GROUP by expressions are used.
* Records in the table index are not stored sequentially. For example, the HASH and HEAP tables are like this.

By executing EXPLAIN SELECT ... By, you know whether MySQL uses the index in the query. If the value of the Extra field is a using Filesort, MySQL cannot use the index. For more information, see "7.2.1 EXPLAIN Syntax (get information about a SELECT)". When the result must be sorted, MySQL 4.1 used the following filesort algorithm:
Copy Code as follows:
1. Read the record based on the index key, or scan the datasheet. Records that cannot match the WHERE clause are skipped.
2. Each record in the buffer is stored with a ' pair ' of 2 values (index key and record pointer). The size of the buffer depends on the value of the system variable sort_buffer_size.
3. When the buffer is slow, run qsort (Quick sort) and store the results in a temporary file. Save the stored block pointer (if all of the ' pair ' values are saved in the buffer, you do not need to create a temporary file).
4. Perform the above operation until all the records are read out.
5. Do a multiple merge to save blocks of up to Mergebuff (7) in another temporary file. Repeat this until all the blocks in the first file are placed in the second file.
6. Repeat the above until the remaining number of blocks is less than MERGEBUFF2 (15).
7. In the last multiple merge, only the pointer to the record (the last part of the sort key) is written to the result file.
8. Read the record sequentially by reading the record pointer in the result file. To optimize this operation, MySQL puts the record pointer in a large block and uses it to read the record sequentially, putting the record in the buffer. The size of the buffer is determined by the value of the system variable read_rnd_buffer_size. The code for this step is in the source file ' sql/records.cc '.

One problem with this approximation algorithm is that the database reads 2 records: One time is when the WHERE clause is estimated, and the second is the sort. Although the record was successfully read for the first time (for example, a full table scan was done), the second time was a random read (the index key was sorted, but the record did not). In the MySQL 4.1 and newer versions, the Filesort optimization algorithm is used to include not only the location of key values and records in the records, but also the fields required in the query. This avoids the need to read records 2 times. The improved Filesort algorithm approach is as follows:
1. Read the record that matches the WHERE clause, as before.
2. Relative to each record, a corresponding, ' tuple ' information is recorded, including the index key value, the record location, and all the fields required in the query.
3. Sort the ' tuple ' information according to the index key.
4. Read the records sequentially, but read the records from the list of ' tuples ' that have been sorted instead of reading them again from the datasheet.

Using the improved Filesort algorithm, the ' tuple ' ratio ' pair ' requires longer space and rarely fits in the sort buffer (the size of the buffer is determined by the value of the sort_buffer_size). As a result, this may require more I/O operations, resulting in slower algorithm improvements. To avoid slowing it down, this optimization method is only used to sort the sum of the extra fields in the ' tuple ' by the total over the system variable Max_length_for_sort_data (the idea that the value of this variable is set too high is a high disk load low CPU load). To increase the speed of an order by, you first need to see whether MySQL can use the index instead of the extra sorting process. If you cannot use an index, you can try to follow these strategies:
* Increase the value of the sort_buffer_size.
* Increase the value of the read_rnd_buffer_size.
* Modify Tmpdir to point to a dedicated file system with a lot of space left.

OK, about MySQL usage, indexing usage, optimization Wait a series of all have introduced, we can refer to.