MySQL SQL Tuning: an in-depth understanding of Order

Indexes without sorting must meet two conditions:
① The sorting field is exactly the same as the INDEX key used in the execution plan (or the previous INDEX keys)
② The table access method is index, ref, or range. [Note: Type in the explain output can be seen]
The fastest sorting is not sorting, which proves another important advantage of the index:
Indexes can reduce the sorting cost, and the resources involved in sorting include CPU and memory, that is
Indexing not only reduces IO overhead, but also accelerates query, but also greatly reduces CPU overhead!

If the index cannot be used to complete the sorting operation, filesort is used, even if the disk files are not required to be fully sorted in the memory.
There are two types of filesort algorithms:
① Two-pass

Principle:
Read the row pointer and sort fields, sort them, and then read the required data based on the sorting results.
Advantages:
The sorting data volume is small and can be completed in the memory.
Disadvantages:
A large number of random IO occurs during the second read, which is too expensive.

② Single-pass

Principle:
Read all the fields involved in the SQL statement at a time, sort the fields according to the sorting fields, and return the sorting result directly.
Advantages:
Only one sequential IO is required, without any random IO, greatly reducing IO overhead
Disadvantages:
The memory can not accommodate so much data, and may be first placed on the disk, but divided into big data, sorting a single small block
After sorting out and putting it back to the disk, after all the individual blocks are arranged, the result set merge is performed, and then the sorting result is returned.

When the sum of the required and sorted columns to be queried is greater than max_length_for_sort_data or the required column is BLOB/TEXT, select two-pass
Single-pass performs better in general. If you want to use it, you can increase max_length_for_sort_data.
Note that when calculating the length of the required and sorted columns, they are all given by the maximum length, such as varchar and UTF-8.
So sometimes we may even see that the temporary storage space consumed by sorting is much more than the original table on the disk.
In addition, in Join, when all order by columns come from the first joined table, Extra has using filesort
For all other Join conditions, Extra will have using temporary and using filesort.
Because MySQL first reads all associated data to the temporary table, and then filesort

If there is no choice but filesort, how can we speed up order?
① Increase sort_buffer_size
② Increase read_rnd_buffer_size. [Note: you do not have to consider this option if it is single-pass]
③ Select only the required columns and select only the appropriate data type for the columns
④ Increase tmpdir

① Http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html
② Http://isky000.com/database/mysql_order_by_implement
High Performance MySQL (3) version: P220-222, P368-369


By Chiba
2013-10-1
Good Luck