mysql 分组查询原理_详解MySQL分组查询Group By实现原理

1sky@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

9sky@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

然后再看如下 Query 的执行计划：

1sky@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-by

32