Basic implementation principle of group by in MySQL

Basic implementation principle of group by in MySQL

Because group by actually performs sorting operations, and compared with order by, group by mainly only performs grouping operations after sorting. Of course, if other Aggregate functions are used during grouping, Some Aggregate functions are required for calculation. Therefore, in the implementation of group by, indexes can also be used like order.

In MySQL, the implementation of group by also has multiple (three) methods, two of which use the existing index information to complete group, another scenario is that indexes cannot be used completely. The following is an analysis of the three implementation methods.

1. Use Loose index scanning to implement GROUP

What is loose index scan to implement group? In fact, when MySQL uses index scan to implement group by, it does not need to scan all the index keys that meet the conditions to complete the operation.

In the following example, we use a loose index scan to implement group by. Before this example, we need to 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:

Reference content is as follows:

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, 96 rows affected( 0.02 sec)

Records: 96 Duplicates: 0 Warnings: 0

Then let's look at the execution plan of the following Query:

Reference content is as follows:

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_id \ G

* *************************** 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-

1 row in set (0.00 sec)

We can see that "Using index for group-by" is displayed in the Extra information of the Execution Plan. In fact, this is to tell us, mySQL Query Optimizer uses loose index scanning to implement the group by operation we need.