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:
Copy Code code as follows:
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:
SELECT * from T1-Key1, Key2;
* In the noncontiguous index key section, do ORDER by:
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 keys used to search for records are not the same as the order by:
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 code as follows:
1. Read the records according to the index key, or scan the data table. 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. Make a multiple merge and 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 records 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.
If you use MySQL 4.1 or update, this option allows multiple paths to be in a looping format. Each path is separated by a colon (': ') on Unix, with a semicolon ('; ') on Windows,netware and OS/2. ）。 You can use this feature to distribute the load evenly to several directories. Note: These paths must be directories that are distributed on different physical disks, not different directories on the same physical disk.
