当MySQL Query Optimizer无法找到可以利用的合适索引时,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作
例如
EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id > 1 and group_id < 10 GROUP BY user_id \G
********** 1. row *********
id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using where; Using index; Using temporary; Using filesort
执行计划说明MySQL通过索引找到了所需的数据,然后创建了临时表,又进行了排序操作,才得到所需的GROUP BY结果
示例中 group_id并不是一个常量条件,而是一个范围,而且GROUP BY 字段为user_id。所以MySQL无法根据索引的顺序来帮助GROUP BY的实现,只能先通过索引范围扫描得到需要的数据,将数据存入临时表,然后再进行排序和分组操作来完成GROUP BY
针对这种情况的优化,必须要有足够的sort_buffer_size供排序时使用,而且尽量不要进行大结果集的GROUP BY操作,因为如果超出系统设置的临时表大小就会出现将临时表数据复制(copy)到磁盘上面再进行操作的情况,这时的排序分组操作性能将成数量级的下降