Use of the orderby statement in mysql INDEX OPTIMIZATION

In mysql, OrderBy can be used in many usage scenarios. Previously, you can refer to the orderby usage in some manuals. However, this is the first section of this article, next we will introduce the specific usage of this line.

In mysql, Order By can be used in many ways. In the past, some manuals can refer to order by usage, but they all want to be simple. This is the first section of this article, next we will introduce the specific usage of this line.

MySQL Order By key is used to classify the data in the record.

MySQL Order By Keyword Classification

Order by keyword is used to classify the data in the record.

MySQL Order By syntax

The Code is as follows:
SELECT column_name (s) FROM table_name Order by column_name

Note: The SQL statement is a "case-insensitive" Statement (which does not distinguish between uppercase and lowercase letters), that is, "ORDER BY" and "order by" are the same.

The order by keyword is used to sort the data in the record set.

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:
$ 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 order by age "); While ($ row = mysql_fetch _ ($ result )) { Echo $ row ['firstname']; Echo "". $ row ['lastname']; Echo "". $ row ['age']; Echo" "; } Mysql_close ($ con ); ?>

Output of the above Code:

The Code is as follows:
Glenn Quagmire 33 Peter Griffin 35

Sort in ascending or descending order
If you use the order by keyword, the sorting ORDER of the record set is ascending BY default (1 before 9, "a" before "p ).

Use the DESC keyword to set the descending order (9 before 1, "p" before "):

Note:

If we use order by (DESC) when executing the select statement, it first sorts all records BY keywords and then reads the required records in sequence, instead of selecting records first and then sorting them in descending order

Implementation Analysis of MySQL ORDER

The following describes two sorting implementation methods and implementation diagrams through examples:
Assume that two tables, Table A and Table B, have the following structures:
Sky @ localhost: example 01:48:21> show create table AG

The Code is as follows:

* *************************** 1. row *************************** Table: Create Table: create table 'A '( 'C1 'int (11) not null default '0 ′, 'C2 'char (2) default NULL, 'C3' varchar (16) 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 (11) not null default '0 ′, 'C2 'char (2) default NULL, 'C3' varchar (16) default NULL, Primary key ('c1 '), KEY 'B _ c2_ind' ('c2 ') ) ENGINE = MyISAM default charset = utf8

1. Using ordered indexes for sorting means that when the order by condition of our Query is exactly the same as the Index key used in the Query execution plan (or the previous Index keys, when the index access mode is rang, ref, or index, MySQL can directly obtain sorted data by using the index order. The order by method is basically the Optimal Sorting method, because MySQL does not need to perform the actual sorting operation.

Suppose we execute the following SQL statement on Table A and B:

The Code is as follows:

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: 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

We can see from the execution plan that MySQL does not actually sort data, as shown in the following figure:

2. Sort the obtained data in the memory using the corresponding sorting algorithm. MySQL needs to sort the data in the memory, the memory used is the sorting area we set through the sort_buffer_size system variable. This sorting area is exclusive to each Thread, So multiple sort buffer memory areas may exist in MySQL at the same time.

The second method is called filesort in the execution plan provided by MySQL Query Optimizer (viewed by the EXPLAIN command. In this method, the primary reason is that there is no available ordered index to obtain ordered data. MySQL can only sort the obtained data in the memory and then return the data to the client. In MySQL, there are actually two filesort implementation algorithms. One is to first retrieve the corresponding sorting fields based on the corresponding conditions and the row pointer information that can be directly located in the row data, then sort in sort buffer. The other is to retrieve all fields that satisfy the condition row at a time and sort them 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. 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, you must set the max_length_for_sort_data parameter. Some colleagues once experienced a large number of sorting waits, resulting in a high system load and a long response time. Finally, we found out that MySQL uses the traditional first sorting algorithm, after the value of the max_length_for_sort_data parameter is increased, the system load is quickly mitigated and the response is much faster.

Let's take a look at the instances that MySQL needs to use filesort for sorting.

Assume that we change our Query to A. c2 sort, and then look at the situation:

The Code is as follows:

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: 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 extracts Qualified Data from Table A. Because the obtained data does not meet the order by condition, MySQL performs the filesort operation. In MySQL, the filesort operation has a strange restriction, that is, the data source must be from a Table. Therefore, if we sort two or more data records) table obtained through Join, MySQL must first create a Temporary Table and then sort the data of the Temporary Table, as shown in the following example:

The Code is as follows:

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: 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 bit strange. I don't know why, MySQL Query Optimizer displays the "Using temporary" process in the first row's operation on Table, is it just to make the execution plan output less than one line?

MySQL Order By Method

Although order by does not match the ORDER of the index, the index can still be used, as long as the unused index part and all the additional order by fields are included in the WHERE clause.

Use the index MySQL Order
The following queries use indexes to solve order by or group:

The Code is as follows:
SELECT * FROM t1 order by 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 without Indexes
In other cases, MySQL cannot use an index to satisfy order by, even though it uses an index to locate records to match the WHERE clause. These situations are as follows:
* Order by for different index keys:

The Code is as follows:
SELECT * FROM t1 order by key1, key2;

* Order by on the non-consecutive index key:

The Code is as follows:
SELECT * FROM t1 WHERE key2 = constant order by key_part2;

* Both ASC and DESC are used:

The Code is as follows:
SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;

* The index key used for searching records and the order by statement are not the same:

The Code is as follows:
SELECT * FROM t1 WHERE key2 = constant order by key1;

* Many tables are connected together, and the fields in the read records in order by are not all from the first very number table (that is, the connection type of the first table in the EXPLAIN analysis result is not const ).
* Different order by and group by expressions are used.
* Records in Table indexes are not stored in order. For example, the HASH and HEAP tables are like this.

BY executing explain select... order by, you can see whether MySQL uses an index in the query. If the value of the Extra field is Using filesort, MySQL cannot use the index. For details, see "7.2.1 EXPLAIN Syntax (Get Information About a SELECT )". When the results must be sorted, MySQL 4.1 used the following filesort algorithm before:
The Code is as follows:
1. Read records based on the index key or scan data tables. Records that cannot match WHERE clauses will be skipped.
2. Each record in the buffer uses a pair to store two values (index key and record pointer ). The buffer size depends on the value of the system variable sort_buffer_size.
3. When the buffer is slow, run qsort and store the results in temporary files. Save the stored block pointer (if all the 'to' values can be saved in the buffer, you do not need to create a temporary file ).
4. Execute the above operation until all the records are read.
5. Perform a multi-merge operation to save the blocks of up to MERGEBUFF (7) Areas in another temporary file. Repeat this operation until all the blocks in the first file are placed in the second file.
6. Repeat the preceding operations until the remaining number of blocks is less than MERGEBUFF2 (15 ).
7. During the last multi-merge, only the record pointer (the last part of the sort index key) is written to the result file.
8. Read records in sequence by reading the records pointer in the result file. To optimize this operation, MySQL puts the record pointer read into a large block and uses it to read records in sequence and store the records in the buffer. The buffer size depends on 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 two records: one is to estimate the WHERE clause, and the other is to sort. Although the record is successfully read for the first time (for example, a full table scan), the second is random reading (the index key has been sorted, but the record does not exist ). In MySQL 4.1 and later versions, the filesort optimization algorithm is used to record not only the index key value and record location, but also the fields required in the query. This avoids the need to read records twice. The improved filesort algorithm is roughly as follows:
1. As before, read records that match the WHERE clause.
2. Each record records a corresponding; 'tuples 'information, including the index key value, record location, and all fields required in the query.
3. Sort the 'tuples 'based on the index key.
4. Read records in sequence, but read records from the sorted 'tuples 'list instead of from the data table.

The improved filesort algorithm takes longer space than the original 'tuples, they are rarely suitable for sorting buffering (the buffer size is determined by the sort_buffer_size value ). Therefore, more I/O operations may be required, resulting in slower improved algorithms. To avoid slowing down, this optimization method is only used to sort the sum of the size of the additional fields in the 'tuples 'beyond the system variable max_length_for_sort_data (the value of this variable is set too high, which means High Disk Load and low CPU load). To increase the order by speed, you must first check whether MySQL can use indexes instead of additional sorting procedures. If you cannot use an index, follow these policies:
* Increase the value of sort_buffer_size.
* Increase the value of read_rnd_buffer_size.
* Modify tmpdir to point it to a dedicated File System with a lot of free space.

Now, we have introduced mysql usage, index usage, and optimization. For more information, see.