学习篇-mysql-优化10-慢查询优化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;