Detailed MySQL group query group by implementation principle

Because group by actually does the same sort operation, group by is mostly just a grouping operation after sorting. Of course, if other aggregate functions are used when grouping, then some aggregation function calculations are also required. As a result, the index can also be used as an order by in the implementation of Group by.

In MySQL, the implementation of group by is also available in several (three) ways, with two ways to use existing index information to complete group by, and the other to be used in scenarios where the index is completely unusable. Below we do an analysis of these three implementations.

1. Using Loose (Loose) index scans to implement GROUP by

What is a loose index scan implementation Group by? In fact, when MySQL fully utilizes the index scan to implement group by, it does not need to scan all index keys that meet the criteria to complete the operation results.

Let's use an example to describe the loose index scan implementation GROUP by, and before the example we need to first adjust the index of the Group_message table and add the Gmt_create field to the index of the group_id and user_id fields:

1 sky@localhost:example 08:49:45> CREATE INDEX IDX_GID_UID_GC
2
3-> on Group_message (group_id,user_id,gmt_create);
4
5 Query OK, rows affected (0.03 sec)
6
7 records:96 duplicates:0 warnings:0
8
9 sky@localhost:example 09:07:30> DROP Index Idx_group_message_gid_uid
10
One-> on Group_message;
12
Query OK, rows affected (0.02 sec)
14
records:96 duplicates:0 warnings:0
Then look at the following Query's execution plan:

1 sky@localhost:example 09:26:15> EXPLAIN
2
3-> SELECT User_id,max (gmt_create)
4
5-> from Group_message
6
7-> WHERE group_id < 10
8
9-> GROUP by group_id,user_id\g
10
11 *************************** 1. Row ***************************
12
Id:1
14
Select_type:simple
16
Table:group_message
18
Type:range
20
possible_keys:idx_gid_uid_gc
22
key:idx_gid_uid_gc
24
Key_len:8
26
Ref:null
28
Rows:4
30
Extra:using where; Using Index for Group-by
32
1 row in Set (0.00 sec)

We see information in the Extra of the execution plan showing "Using index for Group-by", which in fact tells us that MySQL Query Optimizer the group by operation we need by using a loose index scan.

The following picture depicts the approximate implementation of the scanning process: