Mysql Optimization GROUP by scenario _mysql

The most common way to execute a GROUP BY clause is to scan the entire table and then create a new temporary table in which all rows of each group in the table should be contiguous, finally using the temporary table to locate the group and apply the aggregate function (if there is a clustered function). In some cases, MySQL can get results by accessing the index without creating a temporary table. The EXPLAIN output of such a query displays the value of the extra column as the Using index for group-by.

One. Loose Index Scan

1. Meet the conditions

The query is for a table.
A GROUP by uses the leftmost prefix of the index.
You can use only 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 idx (C1,C2,C3):

SELECT C1, c2 from T1 GROUP by C1, C2;

SELECT DISTINCT C1, c2 from T1;

SELECT C1, MIN (C2) from T1 GROUP by C1;

SELECT C1, c2 from T1 WHERE C1 < const GROUP by C1, C2;

SELECT MAX (C3), MIN (C3), C1, c2 from T1 where C2 > Const GROUP by c1, C2;

SELECT C2 from T1 WHERE C1 < const GROUP by C1, C2;

SELECT C1, c2 from T1 WHERE c3 = Const GROUP by c1, C2;

Examples of conditions that do not meet:

1. In addition to Min () or Max (), there are other cumulative functions, such as:

SELECT C1, SUM (C2) from the T1 GROUP by C1;

2. The domain in the GROUP by clause does not refer to the beginning of the index, for example:

SELECT c1,c2 from T1 GROUP by C2, C3;

3. The query references a part of the keyword that follows the group by section and does not have an equation 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, execution group by can still not create temporary tables. If there is a scope condition in the WHERE clause, the method reads only the keywords that satisfy those conditions.

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

1. There is a vulnerability in GROUP by, but it is already covered by the condition C2 = ' a '.

SELECT c1,c2,c3 from t1 WHERE c2 = ' A ' GROUP by C1,C3;

2. GROUP by is not the leftmost prefix, but there is a condition that provides constants for that element:

SELECT c1,c2,c3 from t1 WHERE c1 = ' A ' GROUP by C2,C3;