Basic implementation principle of group by in MySQL

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:

The following are the referenced contents:

Sky@localhost:example 08:49:45> CREATE INDEX IDX_GID_UID_GC

-> on Group_message (group_id,user_id,gmt_create);

Query OK, rows affected (0.03 sec)

records:96 duplicates:0 warnings:0

Sky@localhost:example 09:07:30> DROP Index Idx_group_message_gid_uid

-> on Group_message;

Query OK, rows affected (0.02 sec)

records:96 duplicates:0 warnings:0

Then look at the following Query's execution plan:

The following are the referenced contents:

Sky@localhost:example 09:26:15> EXPLAIN

-> SELECT User_id,max (gmt_create)

-> from Group_message

-> WHERE group_id < 10

-> GROUP by GROUP_ID,USER_IDG

1. Row ***************************

Id:1

Select_type:simple

Table:group_message

Type:range

possible_keys:idx_gid_uid_gc

key:idx_gid_uid_gc

Key_len:8

Ref:null

Rows:4

Extra:using where; Using Index for Group-by

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.