关于mysql group by 执行原理和超大数据量查询优化介绍

group by是在日常开发中很常用的一种查询聚合方式，不管是统计或者分组都有很重要的使用场景。这里做一点简单的介绍，希望会有用到。
在大数据量的情况下出现慢查询的问题。那优化慢sql前，肯定是要懂sql的查询逻辑，所以我先介绍下group by 语句的执行逻辑。

• group by 执行逻辑

拿下面这张表举例，这是一张记录文件夹id和用户id关联关系的表。其中dir_id代表文件夹id，uid代表用户id，还有个唯一索引是uniq_dir_id。

create table t_dir_user
(
id bigint unsigned auto_increment
primary key,
dir_id bigint default 0 not null,
uid bigint default 0 not null,
constraint uniq_dir_id
unique (dir_id, uid)
)

表一共有2000多万的数据（可以去看一下关于B+树的数据存储相关的资料）。下面开始介绍使用group by 语句时sql执行的原理。

• 没有用到索引的情况
  先说下结论，group by后面的列如果不能使用上索引，那么则会产生临时表且很可能产生文件排序的情况。
  group by 语句有分 使用到索引和没有使用到索引的情况，先看看没有使用到索引的情况。假如我想查询在一些文件夹范围内，用户关注的文件夹数量。那我可以写出下面这样的sql。

explain select count(1), uid  
from t_dir_user  
where dir_id in (1803620,4368250,2890924,2033475,3038030)  
group by uid;

使用explain分析时，会发现这个查询是使用到索引的，且Extra 那一栏会出现下面的信息。

Using index condition; Using temporary; Using filesort

上述信息代表了查询是使用到了索引来做where条件查询，并且使用到了临时表和文件排序
注意 ：临时表和文件排序这两个操作都是性能不佳的操作，写sql时应尽量避免
现在来对这种情况做更加具体的分析，在上述例子中，mysql相当于建立了一张临时表，具体是内存的临时表还是磁盘的临时表要看临时表数据量大小，内存放不下会放到磁盘上。

临时表一列存放需要分组的值，上述案例中就是 uid，一列存放统计出来的count值，mysql会一遍扫描uniq_dir_id索引，一边向这个临时表中写入数据或更新count值，当索引扫描完成后，再将填满数据的临时表做下排序然后返回给客户端。注意这个排序的行为，如果需要排序的数据量很大则会产生文件排序，否则则是内存排序

• 使用到索引的情况
  再来看看group by 后跟的列能使用到索引的情况。
  先说下结论，使用到索引的时候，mysql会使用内置的聚合函数来进行操作，而不是创建临时表。并且节省了排序这一步，这种方式会更高效。

explain select count(1), dir_id  
from t_dir_user  
where dir_id in (1803620,4368250,2890924,2033475,3038030)  
group by dir_id;

此时explain分析后你会发现，虽然使用的是相同的索引，但是Extra这一栏的信息已经变了，Extra信息如下：

Using index condition; Using aggregate; Using index

Using aggregate 这条sql会使用mysql内置的聚合函数进行分组聚合的操作。

我们来具体分析下，因为group by此次是按dir_id文件夹id进行分组的，而dir_id刚好可以用上dir_id和uid建立的联合索引uniq_dir_id，并且索引是有序的，这样mysql在扫描索引的时候，就是一个文件夹id的索引数据扫描完成后，再次去扫描下一个文件夹id的索引数据，扫描的同时会对该文件夹id的count值进行累加。 这样一个文件夹的索引数据扫描完成后刚好就能知道这个文件夹id关联的uid的count值，并将这个值发送给客户端。

所以，整个过程其实是一边扫描索引对特定文件夹id的count值进行累加，一边将累加后的结果返回给客户端的过程。
注意，mysql返回给客户端的结果并不是全部查询出来后才返回给客户端，而是可以边查边返回的。
整个过程是没有用上临时表的。这样的查询会更加高效。

• 使用索引的情况下如何优化千万级count group by查询

在了解完group by语句的执行逻辑后，对大数据了的sql进行了分析，发现sql的group by列是属于已经使用了索引的情况下其实查询也会比较慢。那为啥还会慢呢？
其实也很简单，因为即使是使用了索引，group by的过程还是会有扫描索引和进行累加的过程，由于扫描的数据量太大了，最终导致了sql整体耗时还是很慢，超过了1s的阈值。
既然如此，那就换优化思路，这也是对大数据量的聚合统计的一种常用手段。 （1）业务大部分时候都是读多写少的，可以建立一张新表专门用于记录对应的文件夹管理的用户数，每次关联关系发生变化时，同时再更新下这张统计表的数量即可。而业务在查询数量时，则直接查统计表中的数据。 这种优化非常适合大数据量的统计。

（2）还可以使用elasticsearch 这类型数据库存数据，在这个案例里，相当于就把t_dir_user整张表的数据同步到elasticsearch中，并且做mysql到elasticsearch集群数据的实时同步机制，这样以后在查询对应文件夹的关联人数时，可以直接在elasticsearch进行查询。elasticsearch会对每个字段建立倒排索引。由于倒排索引中会存储该索引的记录条数，在这个案例中就是dir_id对应的记录条数，所以在用elasticsearch进行dir_id的分组count查询时是相当快的。
其实，你可以发现由于elasticsearch的倒排索引内直接记录了数量信息，这个和由mysql建立新的统计表记录数量，原理其实是一致的，就是将高频的读count查询改由低频的更新操作。
（3）其实如果真的出现很大数据的情况，自己觉得单点的MySQL其实并不是很适合存储，可以选择分库分表的设计，将数据分别按照某些策略进行存储，在查询的时候，也会大大的降低读的速度
（4）自己觉得最好的一种方式，结合业务需求，如果数据是存在长期性的增长，例如用户记录，log记录，操作行为等。除了上述的elasticsearch，也可以采用一下其他现在主流的数据库。例如我们的一些流量记录，都是千万级别以上，采用的就starrocks，同时有一些用户行为记录，采用的clickhouse,一些文本相关的记录采用elasticsearch。基于架构上的数据库资源的更改，我觉得才可以真正的去解决很大并发读写和大数据量查询的问题