How MySQL optimizes group by

　　

The most general way to execute a GROUP BY clause is to scan the entire table first and then create a new temporary table in which all rows for each group should be contiguous and finally use the temporary table to locate the group

and apply the aggregation function (if there is an aggregation function). In some cases, MySQL can get results by accessing the index without creating a temporary table. The EXPLAIN output of this type of query shows Extra

The value of the column is Using index for group-by.

One. Loose Index Scanning

1. Meet the conditions

• The query is for a table.

• GROUP by uses the leftmost prefix of the index.

• You can only use the min () and Max () aggregate functions, and they all point to the same column.

2. Example

Table T1 (C1,C2,C3,C4) has an index of IDX (C1,C2,C3):

Const Const CONSTconst GROUP by C1, C2; 

Examples of conditions not satisfied:

1. In addition to Min () or Max (), there are other cumulative functions, for example:     SELECT C1, SUM (C2) from T1 GROUP by C1;
2. The fields in the GROUP by clause do not reference the beginning of the index, for example:

     SELECT c1,c2 from T1 GROUP by C2, C3;
3. The query references part of the keyword following the GROUP by section, and there is no equality equal to the constant, for example:     SELECT c1,c3 from T1 GROUP by C1, C2;

Two. Compact Index Scan

If the loose index scan condition is not met, performing group by can still not create a temporary table. If there is a scope condition in the WHERE clause, the method reads only the keywords that meet these conditions.

Otherwise, an index scan is performed. The method reads the scope defined by the WHERE clause.

1. There is a vulnerability in GROUP by, but it has been overridden by the condition C2 = ' a ' .      = ' A ' GROUP by C1,C3;
2. Group by does not satisfy the leftmost prefix, but there is a condition that provides a constant     for the element:= ' A ' group by C2,C3;