MySQL oreder by one-way sorting and two-way sorting

The sorting of the innodb storage engine is divided into two categories, index sorting and file sorting. Index sorting efficiency is definitely better than file sorting, so please use index sorting as much as possible.

How to check which sorting method is used in sql statement
The answer lies in the explain keyword, check the execution plan of the sql statement, we can see it from the mysql official website

  • If the Extra column of EXPLAIN output does not contain Using filesort, the index is used and a filesort is not performed.
  • If the Extra column of EXPLAIN output contains Using filesort, the index is not used and a filesort is performed. It
    means that if the'Extra' column does not contain'Using filesort', then the index sort is used, otherwise it is the file sort

I use index sorting

This kind of file sorting is not used


and file sorting.

File sorting


Of course, the following is what I want to say in this blog.

Two implementations of file sorting
In mysql, sorting files before using version 4.1 Dual sorting, use one-way sort after 4.1
dual sort
need to scan twice disk, read the sort field and the corresponding line number to start with the disk buffer, the buffer in after sorting, and then read from the disk needs to query a field in
single sort
sort fields and fields to be queried read together into buffer sorting, returned directly to the client

Mentioned above buffer, sort buffer pool, we can view the default size through

show variables like "%sort_buffer%"

I have also seen some blog posts on the Internet about how to deal with when sort_buffer_size is not enough. Some people say that single-channel sorting will still be used, and some people say that double-record sorting will be used again when the data exceeds sort_buffer_size.
First of all, no matter which method is used, it may happen that the sort_buffer_size is exceeded. At this time, a tmp file will be created to merge the files and cause multiple IOs. When the size of the sort_buffer_size is exceeded, the two-way sorting must be better, but this is just a guess. Let's verify below.

Verify which sorting method is used
1. Turn on optimizer tracking
SET OPTIMIZER_TRACE="enabled=on"
2. Execute sql
select * from tab_no_index order by name //No index

Insert picture description here

After executing the sql, view the tracking information

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;

When using file sorting , the json field of the TRACE field will have the field ``filesort_summary.

Insert picture description here


Wait for the introduction of the meaning of this value. We are sorting a large table and also view the tracking information.

Insert picture description here


Hey, it is different from the above

Sort_mode field introduction
The sort_mode value provides information about the contents of the tuple in the sort buffer. There are three values

  1. <sort_key, rowid>: two-way sort
  2. <sort_key, additional_fields>: single-channel sort
  3. <sort_key, packed_additional_fields>: single-way sorting, this kind of query field is packed and compressed compared to the above

This is the original words of the official website.
The sort_mode value provides information about the contents of tuples in the sort buffer:

<sort_key, rowid>: This indicates that sort buffer tuples are pairs that contain the sort key value and row ID of the original table row. Tuples are sorted by sort key value and the row ID is used to read the row from the table.

<sort_key, additional_fields>: This indicates that sort buffer tuples contain the sort key value and columns referenced by the query. Tuples are sorted by sort key value and column values ​​are read directly from the tuple.

<sort_key, packed_additional_fields>: Like the previous variant, but the additional columns are packed tightly together instead of using a fixed-length encoding.

in conclusion
When the query data exceeds sort_buffer_size, single-channel sorting is used