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 thesort_bufferTo determineName, City, ageThree fields.
  • From the indexcityFind the first row that meets the criteria and get the primary keyid.
  • Retrieves the entire row from the primary key indexname,city,ageThree fields, putsort buffer.
  • In the indexcityProceed to the next record.
  • rightsort_bufferThe data in thenameDo quicksort.
  • Take before1000Return 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. rowidThe 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 thesort_bufferTo determinename, idTwo fields.
  • From the indexcityGet the first record that meets the requirement, and get the primary keyid.
  • Fetches the entire row from the primary key indexname,idTwo fields, depositsort_buffer.
  • From the indexcityTake the next record.
  • rightsort_bufferAccording to thenameSorting.
  • Take before1000Line, according to theidIs returned to the original tablecity,nameandageThree 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 used
  • using index condition: Index push down, conditional filtering
  • using where:servreUpon receipt of the data, thewhereConditional filtering
  • using filesort: Sort using temporary files
  • using temporary: A temporary table is used