MySQL sorting usage

In MySQL sorting, Sort_merge_passes is added, and MySQL stores the sorting results in the memory in a temporary file. After the records are found, they are sorted again.

MySQL sorting usage:

 
 

  
  
mysql> show global status like 'sort%';  
  
  
+-------------------+------------+  
  
  
| Variable_name | Value |  
  
  
+-------------------+------------+  
  
  
| Sort_merge_passes | 29 |  
  
  
| Sort_range | 37432840 |  
  
  
| Sort_rows | 9178691532 |  
  
  
| Sort_scan | 1860569 |  
  
  
+-------------------+------------+ 
 
 

Sort_merge_passes consists of two steps. MySQL first tries to sort data in the memory. The memory size is determined by the system variable Sort_buffer_size. If the memory size is insufficient, all the records will be read to the memory, mySQL stores the sorting results in the memory to a temporary file. After MySQL finds all the records, it sorts the records in the temporary file.

Sort_merge_passes will be added when sorting again. In fact, MySQL uses another temporary file to store the result of re-sorting. Therefore, we usually see that the Sort_merge_passes increase by twice the number of created temporary files.

Because temporary files are used, the speed may be slow. Increasing Sort_buffer_size will reduce the number of times Sort_merge_passes and temporary files are created. However, blindly increasing Sort_buffer_size does not necessarily increase the speed.

In addition, adding the value of read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) also has some advantages for sorting operations.

 

How to enable MySQL slow Query

Mysql multi-Table query implementation

How to modify MySQL character set in Liunx

Take you to understand mysql Variables

How does one forget the MySQL root User Password?