Basic implementation principle of group by in MySQL

DocumentPreviously, I wrote several articles about some basic implementation principles of common MySQL operations, such as MySQL order by and MySQL Join. This time I wrote another article about the basic implementation principles of MySQL GROUP.

　　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.

• Three pages in total:
• Previous Page
• 1
• 2
• 3
• Next Page