MySQL optimization-GROUP BY

Reprint: https://my.oschina.net/heguangdong/blog/38567

In Web applications, the promotion of SQL is simple and avoids complexity. So in our company's application can not see Jon, subqueries and other statements exist, so the indirect group by and the use of the index occupy the majority, in fact, a lot of skills, others are summed up, careful analysis, carefully study the experience of others is the right path. And not impetuous, by the experience of the doctrine.

The most general way to satisfy the GROUP BY clause is to scan the entire table and create a new temporary table, all rows of each group in the table should be contiguous, and then use the temporary table to locate the group and apply the cumulative function, if any. (in popular language, a temporary table is created.) Then use the MySQL internal algorithm. Figure out the result)

In some cases, MySQL can do a better job of accessing the index without creating a temporary table. (In fact, it is because of temporary table things, we should optimize.) ）

The general group by optimization is divided into 2 optimization methods:

1 . Loose index Scanning

2, . Compact Index Scan

What is a loose index scan:

is actually :

with this access method, MySQL An attribute that uses some keywords to sort the index type. This property allows you to use a lookup group in the index without having to consider all the keywords in the index that satisfy all the WHERE conditions. Since the access method only considers a small subset of the keywords in the index, it is called a loosely indexed list. (The official language is incisive)

For example:

explain Select Teamid from Competeinfo where Teamid >10 GROUP by Teamid

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Competeinfo	Range	Teamid	Teamid	4	Null	26	Using where; Using Index for Group-by

The explain here represents viewing the index application. Let me briefly introduce you.

The id->select identifier. This is the query sequence number for SELECT.

Select_type, select Type, simple represents a simpler query. (also including union,primary, etc., specifically to check the book)

Table represents the tables that are referenced.

The type-> join type. It can be said that the index application status. Here, range retrieves only the rows for a given range, using an index to select rows. Most of us will use symbols such as > < and so on for indexing. (also includes other join types, all representing the entire table being scanned.) As for the other keywords. Also check the manual it)

Possible_keys->mysql can use which index to find rows in the table (this is not really applied to)

The index to which key->mysql is actually applied.

Key_len-> Index Length

Ref uses which column or constant to select rows from the table together with the key.

rows-"How many lines are affected?

extra-"Some index application status information. Here the using index for group-by represents a loose index.

If here appears useing tempoary useingfilesort. this is more serious. This proves that your index is not used. WORKAROUND: Change the index.

Anyway Here select Teamid from Competeinfo where Teamid >10 GROUP by Teamid. is to find the group by Teamid. Complete GROUP BY. This is also a way.

But let's be careful. query fields must and later GROUP by consistent .

The second optimization method is also the most commonly used. Compact Index Scan.

What is compact index scanning : an index scan or a range index scan, depending on the query criteria.

Actually, it is the application of the Federated Index.

Explain select Teamid from Competeinfo where Teamid >10 and Competeid > 100020 GROUP by Competeid

This table was established. Competeid, Teamid's federated Index.

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Competeinfo	Range	Competeid,teamid	Competeid	4	Null	22	Using where; Using Index

Everyone has seen it. Here we will find. There may be 2 application indexes. The actual application to the index named Competeid. Returns application information is also using where; Using index. Applied to the index.

Next I say: which cases are not applied.

1. do GROUP by for different index keys

SELECT * FROM A1 GROUP by Key1, Key2;

2. do group by on the non-contiguous index key section

SELECT * from T1 WHERE key2=constant Group by Key_part2;

Non-contiguous index: The index above Competeid. He was formed by Competeid, Teamid, to establish a joint index.

Explain select Teamid from Competeinfo where Teamid >10 and Competeid > 100020 GROUP by Teamid

In that case.

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Competeinfo	Range	Competeid,teamid	Competeid	4	Null	22	Using where; Using index; Using temporary; Using Filesort

Everyone saw it. Extra's information. In this case, group by does not use the index. Impact efficiency.

So be sure to notice. GROUP by order. Problem.

1. index keys for searching records and doing GROUP by is not the same one:

Actually, it means. The Where condition and the group by field are inside an index.

This is not an example.

I believe that through this article you also understand the group by index application!

In fact, we try more, more analysis can be.

If you have any questions, please point out that I will amend it.

MySQL optimization-GROUP BY