MySQL about GROUP by optimizations

Yesterday I wrote a blog post about MySQL's Loose index scan (http://www.cnblogs.com/wingsless/p/5037625.html), Later I found that the main purpose of the optimization method mentioned last time was to achieve loose index scan, and on the execution plan level, the extra message should be "Using index for Group-by". In this way, it is possible for MySQL to process distinct and group by using the same optimization method, that is, to go through the index, loose index scan. So today I studied the official documentation and found that it did.

In fact, for group by, the most common way to do this is to perform a full table scan, storing all group by rows in a single temporary table, and then grouping and identifying or aggregating operations. This problem is too complex, time is long, the space consumption is not small. At this point, MySQL can use the index to optimize group by.

Here you can talk about what is called Loose Index scan, which, according to the official definition, only needs to scan a small portion of the index, not all the data that satisfies the where condition, so this method is called Loose index scan.

Below is the case where you can use the loose index scan:

1 Single Table Query

2 Group by has only the leftmost prefix column, no other columns

3 only supports Max and Min aggregation, and the column to be aggregated must be the index of the column in group by.

4 indexes that are not referenced by the group by must be constants (I don't quite understand that)

5 prefix index is not supported.

Suppose T1 (C1, C2, C3, C4) table has an index that includes C1, C2, C3 columns, and these queries are all available for loose index scan:

SELECTC1, C2 fromT1GROUP  byC1, C2;SELECT DISTINCTC1, C2 fromT1;SELECTC1,MIN(C2) fromT1GROUP  byC1;SELECTC1, C2 fromT1WHEREC1<ConstGROUP  byC1, C2;SELECT MAX(C3),MIN(C3), C1, C2 fromT1WHEREC2>ConstGROUP  byC1, C2;SELECTC2 fromT1WHEREC1<ConstGROUP  byC1, C2;SELECTC1, C2 fromT1WHEREC3=ConstGROUP  byC1, C2;

These are the official documents copied, the correctness is not fully verified.

None of the following SQL has the option to use the loose index scan:

--because the aggregate function is not max or minSELECTC1,SUM(C2) fromT1GROUP  byC1;--because it does not conform to the leftmost prefix principleSELECTC1, C2 fromT1GROUP  byC2, C3;--The query involves part of the index, immediately following the column in group by, but there is no constant WHERE c3 = const就好了 equivalent statement, plusSELECTC1, C3 fromT1GROUP  byC1, C2;

Other aggregation functions can also be used with the loose index scan, such as: AVG (DISTINCT), SUM (DISTINCT), and Count (DISTINCT)

The following statements can also be:

SELECT COUNT (DISTINCTSUM(DISTINCT from T1; SELECT COUNT (DISTINCTCOUNT(DISTINCT from T1;

Many statements are extracted from 5.7 of the document, correctness has not been verified, although the official documents are authoritative, but the letter is not as good as the book, and the paper came to the end of shallow, I know this matter must be preach, tomorrow weekend, I test, and then more.

MySQL about GROUP by optimizations