Principle of order by in MySQL

Sort Buffer Size (sort_buffer_size)

  • sort_buffer_size determines the memory size that MySQL allocates for sorting (sort_buffer).
  • If the data size to be sorted is less than sort_buffer_size, the sorting is completed in memory.
  • If the data size for sorting is too large for memory, MySQL resorts to using disk temporary files to assist in sorting.
  • External sorting typically uses the merge sort algorithm and may generate multiple temporary files.

Max Length for Sort Data (max_length_for_sort_data)

  • This is a parameter in MySQL specifically controlling the length of row data used for sorting.
  • If the length of a single row exceeds this value, MySQL considers the row too large and uses a different algorithm.
  • In this new algorithm, only the column to be sorted (e.g., name field) and the primary key id are placed in the sort_buffer.

Full Field Sorting vs. Rowid Sorting

  • If MySQL is concerned about insufficient sorting memory, it might use the rowid sorting algorithm, allowing more rows to be sorted at once. However, it would then need to refer back to the original table for data.
  • If MySQL believes memory is sufficient, it prefers full field sorting, placing required fields in sort_buffer. This allows returning query results directly from memory without referring back to the original table.

MySQL’s Design Philosophy: If there’s ample memory, utilize it and minimize disk access.

order by rand()

  • Uses in-memory temporary table.
  • Memory temporary table sorting uses the rowid sorting method.
  • The configuration tmp_table_size limits the size of the in-memory temporary table (default is 16M). If the temporary table size exceeds tmp_table_size, the in-memory table will convert to a disk temporary table.

Priority Queue Sorting Algorithm (Introduced in MySQL 5.6)

Execution flow:

  1. For the 10,000 rows prepared for sorting (R,rowid), first pick the top three rows and construct a heap.
  2. Take the next row (R’,rowid’). If R’ is smaller than R, replace the heap’s max value.
  3. Repeat step 2 until all rows have been compared.

MySQL Conversion Rule: In MySQL, when comparing a string and a number, the string is converted to a number.

Function operations on indexed fields might disrupt the orderliness of index values. As a result, the optimizer may decide to