MYSQL GROUP by optimization

GROUP by Optimization

The regular Match Group by (Group) Action clause is a sweep table and creates a temporary table containing contiguous grouped rows , using temporary tables to get group data, using appregate functions (aggregate function) (if any ). In some cases, MYSQL may be better off by using index access to avoid creating temporary tables .

The most important prerequisite for avoiding temporal tables with indexes access is that all column attributes in the GROUP BY clause must come to the same index (Union ), and the index is stored in its keyword order (BTree Index,hash index is not sequential). Whether to use index access instead of creation of temporary table also relies on the index part property referenced by the query statement , the specified conditional part (where) , and the aggregate function in the SELECT clause .

Loose Index Scan (loose index scanning)

The most efficient way to handle a group by method is to retrieve the grouped column property by using index directly . At this point, MySQL takes advantage of the Index keyword's ordered property (BTREE), which ensures that the group information is found in an index without regard to all the keywords in the index (whether the WHERE clause condition is satisfied). The access method considers only a subset of the keywords in index , so it becomes the loose index scan. when there is no WHERE clause in the query statement, the loose index scan reads

The key you need is smaller than reading all keys. If the WHERE clause contains a scope condition, loose index scan looks for the first key in each group that satisfies the range condition and reads the minimum number of keys again , which needs to meet the following points:

1:the query overwrites only one table, 2:group by childColumn Properties in a sentenceMeetthe leftmost prefix principleAndno other column properties in non-index。 （The instinct keyword is also universally applicable), e.g. table T1 has an index (C1,C2,C3), if the query contains group by C1,C2, then loose index SACN is appropriate, but group by C2,C3 is not appropriate (leftmost prefix), GROUP by C       1,C2,C4 is also inappropriate (C4 is not a column property in the index). Column properties in the 3:select clausecan onlyContainsmin (), max () aggregate function, and theyall refer to a column property in group by。 4: In the query statementIndex keyword（exceptThose index keywords for the GROUP BY clause)must be a constant(meaning that theymust be referenced by means of = Constra），unless it is min (), Max (); All column keywords in 5:index, column values must be fully indexed,instead of a prefix index, e.g.C1 varchar, index (C1 (10)), which is not used by loose index scan。 When a query statement uses loose index scan, the explain outputUsing index for group-byInformation

Assuming there is an index idx (C1,C2,C3) on table T1 (C1,C2,C3,C4), the loose Index scan method is used in several cases:

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;

The query does not use loose index scan:

The index column keyword for the except group by column appearing in 1:select can only appear with = or min (), Max ();

SELECT SUM  from GROUP  by C1;

2: Leftmost prefix

SELECT  from GROUP  by C2, C3;

3: with 1 explanations

SELECT  from GROUP  by C1, C2;

A loose index scan can also be used in other forms of aggregation functions: AVG (DISTINCT), SUM (DISTINCT) (single column), COUNT (DISTINCT) (Multi-column) support. Conditions:

1: no GROUP BY or DISTINCT clause in query

2: The limitations mentioned above still apply

Apply:

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

Not applicable:

SELECT DISTINCT COUNT (DISTINCT from T1; SELECT COUNT (DISTINCTfromGROUP by C1;

Tight index Scan (compact index scanning)

The compact index scan was not a full index scan except for the interval index scan.

When a loose index scan is not available, you can still avoid creating temporary tables. If there is a range condition in the WHERE clause , only the key that satisfies the condition is read.

Otherwise, perform a full index scan. Because the algorithm reads all keys that meet the range criteria, or if there are no conditions to scan the entire index , we are called Compact index scans .

With compact index scanning, the grouping operation is performed only after all keys have been found .

The algorithm uses the equation to compare all the queries referenced by the columns in effect, only the equality constants can fill in the gap of the query key, it is possible to form an index prefix, using index prefixes for index lookups.

This allows MySQL to avoid additional sorting operations that can be obtained directly from the index. Assuming index (C1,C2,C3) is in table tables (C1,C2,C3,C4), the following query does not support a loose index scan, but supports compact index scanning

1: Although there is a void but has been filled by where c2= ' a '

SELECT  from WHERE = ' a ' GROUP  by C1, C3;

2: Although group by is not the first match to the index, it provides a comparison of constants in the where

SELECT  from WHERE = ' a ' GROUP  by C2, C3;

MYSQL GROUP by optimization