文章目录

  • 一、慢查询优化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版本上

mysql中group by优化 mysql group by 性能_mysql中group by优化

在8版本上

mysql中group by优化 mysql group by 性能_索引_02

  • 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
  • 结果
  • mysql中group by优化 mysql group by 性能_数据库_03

  • 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;

mysql中group by优化 mysql group by 性能_数据库_04

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

mysql中group by优化 mysql group by 性能_mysql中group by优化_05

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

在5.7的版本演示:

mysql中group by优化 mysql group by 性能_mysql_06

在8.0的版本演示:

mysql中group by优化 mysql group by 性能_索引_07

  • 情景四:
// 去掉where条件
explain select name from t2  group by idc,name,id\G;

mysql中group by优化 mysql group by 性能_字段_08