mysql中group by优化 mysql group by 性能

文章目录

• 一、慢查询优化group by详解

一、慢查询优化group by详解

• group by分组优化思路

• group by本质上也同样需要进行排序操作（mysql8优化了，默认不排序了），而且与order by相比，group by主要只是多了排序之后的分组操作。如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by的实现过程中，与order by一样也可以利用到索引。
• 演示

explain select min(name) from t2 group by name \G;

在5.7版本上

在8版本上

• group by的类型

• 三种实现类型

• Loose Index Scan【松散的索引扫描】先走group by 再走where

• 扫描过程

• 先根据group by后面的字段进行分组，分组不需要读取所有索引的key，例如index(key1,key2,key3)，group by key1,key2。此时只要读取索引中的key1,key2。然后再根据where条件进行筛选

• 演示

• 表结构
• sql

explain select actor_id, max(film_id) from film_actor group by actor_id

• 结果

• Tight Index Scan【紧凑的索引扫描】先走where再走group by

• 扫描过程
• 紧凑索引扫描需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成group by操作得到相应结果。区别就是紧凑索引扫描是先执行where操作，再进行分组，松散索引扫描刚好相反。

• Using temporary【临时表实现】（非索引扫描）效率比较低下，需要进行优化

• 扫描过程
• mysql在进行group by操作的时候当mysql query optimizer无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成group by操作

• 影响group by 查询效率

• 和order by 一样，分别在查询字段，where条件，分组字段上做出各种可能的组合，主要就是看有无索引，索引在以上三个关注点上的生效情况。
• 如果where条件中有索引，group by中的索引将失效，如果想使group by的索引生效，where条件要么不走索引，要么和group by走同一个索引
• 演示

• 表结构
• 情景一：

// where条件占用索引
explain select name from t2 where id > 2 and id < 10 group by name\G;

• 情景二：

// 走紧凑索引,idc和group by同在一个索引
explain select name from t2 where idc > 2 and idc < 10 group by idc,name\G;

• 情景三：

// 走紧凑索引,where条件不抢占索引
explain select name from t2 where name='zhangsan' group by idc,name\G;

在5.7的版本演示：

在8.0的版本演示：

• 情景四：

// 去掉where条件
explain select name from t2  group by idc,name,id\G;