MySQL Group By optimization

MySQL Group By optimization

I wrote a blog post on MySQL's loose index scan (). Later I found that the main purpose of the optimization method mentioned last time was to implement loose index scan, at the execution plan level, the Extra information should be "Using index for group-". In this case, MySQL may use the same optimization method as group by in processing distinct, that is, index-based loose index scan. So today I have studied the official documentation and found that this is indeed the case.

In fact, the most common implementation method for group by is to perform a full table scan and store all group by rows in a temporary table in sequence, then perform group identification or aggregation. This problem is too complicated. It takes a long time and the space consumption is not small. In this case, MySQL can use indexes to optimize group.

Here we can talk about what is loose index scan. According to the official definition, this method only needs to scan a small portion of the data in the index, rather than all the data that meets the where condition, therefore, this method is called loose index scan.

Which of the following conditions can I use loose index scan:

1. Single Table query

In 2 Group by, only the leftmost prefix Column exists, and no other columns exist.

3 only supports max and min aggregation, and the column to be aggregated must be the index of the column in group.

4. The other part of the index that is not referenced by group by must be a constant)

5. prefix indexes are not supported.

Assume that the table t1 (c1, c2, c3, c4) has an index that includes the c1, c2, and c3 columns. All of the following queries can be performed by loose index scan:

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;

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

None of the following SQL statements can use loose index scan:

-- Because the aggregate function is not max or min

SELECT c1, SUM (c2) FROM t1 group by c1;

-- Because it does not conform to the leftmost prefix Principle

SELECT c1, c2 FROM t1 group by c2, c3;

-- The query involves a part of the index, followed by the columns in group by, but there is no constant equivalent statement. Just add WHERE c3 = const.

SELECT c1, c3 FROM t1 group by c1, c2;

Other Aggregate functions can also use loose index scan, such as AVG (DISTINCT), SUM (DISTINCT), and COUNT (DISTINCT)

The following statements can also be used:

Select count (DISTINCT c1), SUM (DISTINCT c1) FROM t1;

Select count (DISTINCT c1, c2), COUNT (DISTINCT c2, c1) FROM t1;

Many statements are excerpted from 5.7 documents, and the correctness is not verified. Although the official documents are authoritative, it is better to believe in books than to have no books, and to get a glimpse of them on paper, I know that this is a must. I will test it one by one tomorrow weekend and try again later.

This article permanently updates the link address: