当前位置: 首页 > 未分类>阅读正文

mysql group by优化

2022.7.14 朱丰华 67 次 留下评论 1179字

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+ 树存储,存储效率不如数组来得高。所以,既然你告诉我数据量很大,那从磁盘空间考虑,还是直接用数组来存吧。

本篇完,还有疑问?留下评论吧

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注