MySQL order by|group by sort optimization

ORDER BY keyword optimization 1 try to sort by using the index method, avoiding sorting using the Filesort method.  2. MySQL supports two sorting methods, Filesort and Index,index, which refer to the MySQL scan index itself to complete the sorting. Filesort is inefficient in ordering. 

order by satisfies both cases and uses index to sort 1 the. Order BY statement uses the leftmost column of the index 2. Use the WHERE clause and the ORDER BY clause condition to satisfy the leftmost top of the index.

If you are not on the index column, Filesort has two algorithms: MySQL will start by double-sorting and single-sort Mysql4.1 before using two-way sorting, literally two scans of the disk, finally getting the data, reading the row pointers and the order by column, sorting them. Then scan the sorted list and re-read the corresponding data from the list by the values in the list. Single sort: Reads the columns to be queried from the disk, sorts them in buffer according to the order by column, and then scans the sorted list for output, which is more efficient and avoids the second reading of the data. And it turns the random io into sequential io, but he uses more space because he keeps every row in memory. Single query problem: In Sort_buffer, when using a single query, because all the fields are taken out, so it is possible to take out the total size of the data out of sort_buffer capacity, resulting in only sort_buffer capacity to go to the size of the data, to sort, After the discharge of the sort_buffer capacity size, resulting in multiple I/O.

Optimization strategy: Increase the setting of the Sort_buffer_size parameter to increase the setting of the Max_length_forsort_data parameter

Group BY is essentially a sorted first, followed by the best left prefix of the index. When an index column cannot be used to increase the setting of the Sort_buffer_size parameter and the settings of the Max_length_forsort_data parameter where the where is higher than the having, the condition that can be written in the where limit is not to have a qualification.

MySQL order by|group by sort optimization