order by
MySQL allocates each thread a block of memory for sorting, called sort_buffer.
1. Sort by all fields
Suppose we have a query statement:
select city, name, age from t where city='hangzhou' order by name limit 1000;
Copy the code
There is a normal index on city.
The execution process of order BY is as follows:
- Initialize the
sort_buffer
To determineName, City, age
Three fields. - From the index
city
Find the first row that meets the criteria and get the primary keyid
. - Retrieves the entire row from the primary key index
name
,city
,age
Three fields, putsort buffer
. - In the index
city
Proceed to the next record. - right
sort_buffer
The data in thename
Do quicksort. - Take before
1000
Return line.
sort_buffer_size
Sort operations, either in memory or using external sort.
Depending on the memory required for sorting, and the parameter sort_buffer_size.
You can determine whether temporary files are used using OPTIMIZER_TRACE and number_of_tmp_files.
External sort usually uses merge sort.
2. rowid
The sorting
If a single row is too large, MySQL uses ROwid sorting.
Max_length_for_sort_data, used to control the length of sort data. If the length of a single line exceeds this value, MySQL considers the single line to be too large and changes its algorithm.
Same query:
select city, name, age from t where city='hangzhou' order by name limit 1000;
Copy the code
The ROWID process is:
- Initialize the
sort_buffer
To determinename, id
Two fields. - From the index
city
Get the first record that meets the requirement, and get the primary keyid
. - Fetches the entire row from the primary key index
name
,id
Two fields, depositsort_buffer
. - From the index
city
Take the next record. - right
sort_buffer
According to thename
Sorting. - Take before
1000
Line, according to theid
Is returned to the original tablecity
,name
andage
Three fields are returned.
3. Compare
If the memory is large enough, full-field sort is preferred because ROWID sort will result in multiple disk reads back to the table.
MySQL design idea: if there is enough memory, use more memory, minimize disk access.
4. Extra
using index
: An overwrite index is usedusing index condition
: Index push down, conditional filteringusing where
:servre
Upon receipt of the data, thewhere
Conditional filteringusing filesort
: Sort using temporary filesusing temporary
: A temporary table is used