mysql group by优化

mysql优先使用松散索引（Loose），然后是紧凑索引，最后尝试用临时表。

索引中用于group的字段，没必要包含多列索引的全部字段。

例如：有一个索引idx(c1,c2,c3)，那么group by c1、group by c1,c2这样c1或c1、c2都只是索引idx的一部分。

要注意的是，索引中用于group的字段必须符合索引的“最左前缀”原则。

group by c1,c3是不会使用松散的索引扫描的。  要利用到松散索引扫描实现 GROUP BY，需要至少满足以下几个条件：　　

◆GROUP BY 条件字段需要符合最左前缀索引　　

◆在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数　　

◆如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在　　

◆如果查询中有where条件，则条件必须为索引，不能包含非索引的字段。

紧凑索引（Tright）分组

紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别：主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成 GROUP BY 操作，得到相应结果。
  

在 MySQL 中，MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作，当 GROUP BY 条件字段不是连续或者索引前缀部分的时候，MySQL Query Optimizer 无法使用松散索引扫描之后，才会尝试通过紧凑索引扫描来实现。

使用临时表实现分组

前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的，当 MySQL Query Optimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作。  当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，就不得不选择通过将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。

分组优化

要保证分组字段应该存在索引。

group by后，会默认存在一个排序，如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

在排序过程中，生成的临时数据占用内存

参数 tmp_table_size 就是控制这个内存临时表大小的，默认是 16M。

如果执行中存放的数据超过内存临时表的上限，这个时候就会把内存临时表转为磁盘临时表。

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。