Detailed description of the implementation principle of MySQL Group query Group By (1)

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:

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 11-> on group_message; 12 13 Query OK, 96 rows affected (0.02 sec) 14 15 Records: 96 Duplicates: 0 Warnings: 0 Then let's look at the execution plan of the following Query: 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 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 8 26 27 ref: NULL 28 29 rows: 4 30 31 Extra: Using where; Using index for group- 32 33 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.

The image below depicts the approximate Implementation of the scanning process:

To use a loose index scan to implement group by, you must meet at least the following conditions:

◆ The group by condition field must be in the first consecutive position in the same index;

◆ When using group by, only the MAX and MIN Aggregate functions can be used;

◆ If a field condition other than the group by condition in the index is referenced, it must exist as a constant;

Why is loose index scanning very efficient?

Because there is no WHERE clause, that is, when the full index scan is required, the number of key values to be read by the loose index scan is as large as the number of groups in the group, that is to say, it is much less than the actual number of key values. When the WHERE clause contains a range limit or an equivalent expression, the loose index scans 1st keywords in each group that meet the range conditions and reads as few keywords as possible again.