MySQL Order By index Optimization 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:
Copy codeThe 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:
SELECT * FROM t1 order by key1, key2;
* Order by on the non-consecutive index key:
SELECT * FROM t1 WHERE key2 = constant order by key_part2;
* Both ASC and DESC are used:
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:
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:
Copy codeThe 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.
If you use MySQL 4.1 or update, this option allows multiple paths to use the loop format. Each path is separated by a colon (':') on Unix, and a semicolon (';') on Windows, NetWare, and OS/2 (';'). This feature can be used to evenly distribute loads to several directories. Note: These paths must be directories distributed on different physical disks, rather than different directories on the same physical disk.