Mysql Group By,order by,dinstict optimization

1.order by optimization

Implementation method:

1. According to the index field sorting, the data extracted from the index is already well sequenced and returned directly to the client;

2. The index is not used, and the fetched data is returned to the client after a sort operation.

1	EXPLAIN SELECTm.id,m.subject,c.content FROM group_message m,group_message_content c WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BYm.user_id\G;

Optimizer is optimized for query, which accesses the data in the order of the indexes on the m.user_id, so that the obtained data is already sequenced.

This method of using indexes to sort data is the best way to implement result set sequencing in MySQL, using existing indexes to avoid the resource consumption of actual sort calculations.

If the index is not used, MySQL sorts the extracted data according to a certain sorting algorithm, and then returns the sequenced data to the client. There are two main sorting algorithms used in MySQL:

1. Remove the criteria field for sorting and the pointer to the corresponding data row, sort the condition in the sort buffer, and then use the pointer to fetch the request data from the data row and return it to the client;

2. Remove the condition field and all other request data used for sorting, place the fields that are not used for sorting in one piece of memory, then sort the condition fields in sort buffer, and then use the row pointer to match the data in memory to the merged result set, and then return the ordered data to the client. (reduces two access to data, saving IO operations)

3. When the field used for sorting exists in more than one table, or a JOIN operation is required before sorting, MySQL must first put the result set of the join into a temporary table, and then take the data in the temporary table into the sort buffer for sorting.

Optimization method:

1. Increase the setting of the Max_length_for_sort_data parameter. When all the data that needs to be taken out is less than the value of this parameter, MySQL will use the second reordering algorithm, otherwise, the first algorithm, so long as the memory is sufficient to set a large enough value for MySQL to adopt an improved sorting algorithm.

2. Remove unnecessary fields.

3. Increase the value of the Sort_buffer_size parameter. When MySQL sorts the condition fields, if the total length of the ordered field is greater than the value of the parameter, MySQL uses the staging table for the fields that need to be sorted, which also consumes the performance.

2.group by optimization

The implementation of group by IS done in addition to sorting operations, and if some aggregate functions are used, the corresponding aggregation calculations are performed.

Implementation method:

1. Using a loosely (Loose) index scan to implement group by, the so-called Loose index scan, that is, MySQL does not need to scan all the conditions of the index key to complete the group by operation

2. Using compact (tight) index scanning for group BY, compact indexes and loose indexes The main difference is that when the index needs to be scanned, the compact index reads all the index keys that satisfy the criteria, and then uses the group by operation to obtain the corresponding results.

3. When optimizer cannot find a suitable index to use, it chooses to put the read data into a temporary table to complete the group by operation. The group by operation wants to take advantage of the index and must satisfy that the group by field must be stored in the same index, and that the index is an ordered index, and that using different aggregate functions also affects whether the index is used to implement the group by operation.

Optimization method:

1. Use the index as much as possible and loose index to complete the group by operation, which relies on adjusting the index or adjusting query to achieve;

2. When the index is not available, you must provide enough sort_buffer_size for MySQL to complete the sort operation, as described before, or MySQL will sort the fields that need to be sorted in order to affect performance. In addition, do not group by operations on large result sets, because when the amount of data exceeds the system's maximum temporary table size, MySQL will copy data from the temporary table to disk and then manipulate it, and the performance will decrease in order of magnitude.

3.DINSTINCT optimization

Implementation method:

The implementation of distinct is similar to group by, and the implementation process only takes the first record out of each group after group by, so distinct can also be implemented with a loose or compact index, except when distinct cannot be implemented with an index. MySQL also takes the data out and puts it in a temporary table, but does not sort the staging table.

Optimization method:

When using indexes as much as possible, make sure not to perform distinct operations on large result sets, IO operations on disk and IO operations in memory are not at all an order of magnitude.

Mysql Group By,order by,dinstict optimization