mysql 聚合查询 索引_mysql预聚合结果查询与索引设计

这里需要注意，mysql中存储的数据不光具有明细数据，还具有经过etl聚合完成之后的数据，也就是说查询某个维度的聚合结果的时候，不需要实时的group by，可以直接查询预聚合完成的结果。因为维度很多，所以需要使用联合索引

联合索引最需要关注的是索引的字段顺序

如果是实时聚合，那么最粗的维度需要最左

针对已经预聚合完成的结果，最细粒度的结果应该最左

明细数据的样例：表中的列从左到右粒度一次降低，存在维度由粗到细的层级关系bu_idcat1_idcat2_idcat3_idspu_code11231001

11251002

21231001

明细+预聚合数据的样例bu_idcat1_idcat2_idcat3_idspu_codecount112310011

112510021

212310011

1123-11

1125-11

2123-11

112-1-12

212-1-11

11-1-1-12

21-1-1-11

1-1-1-1-12

2-1-1-1-11

可以发现，聚合之后的数据对比聚合之前的数据，明显多了很多冗余，但是查询速度会快很多，针对两个表的聚合查询的sql如下：明细表：select brand_name, sum(count) from table group by brand_name

明细+预聚合表：select brand_name, count from table where cat1_id=-1 and cat2_id=-1 and cat3_id=-1 and spu_code = -1

第一种查询，因为需要使用group by，所以brand_name需要是索引中最左的，否则速度很慢：bu_id,cat1_id,cat2_id,cat3_id,spu_code

第二种查询，看似需要group by，实际只需要查询预聚合的结果，因为查询预聚合结果需要将被聚合字段的值设置为-1，所以即使不查询最细维度的明细，最细维度也需要添加where匹配，所以对于这种预聚合的查询来说，最细的维度作为最左索引反而是更合适的：spu_code,cat3_id,cat2_id,cat1_id,bu_id

预聚合的效果

让实时的大量明细聚合转变成预聚合结果中的少量记录查询，所以速度快很多。

到ES的联想

es对于聚合排序取top的结果，存在准确性的问题，比如说如果es中保存的是明细，查询bu_id字段的分组集合结果的时候：比如说对bu_id聚合的结果取top5

每个分片的查询，都是对bu_id的聚合结果取top5

对于每个分片来说，可能都有bu_id=1这条记录，但是在分片1中bu_id=1排在第一位，但是在分片2中bu_id=1却排在倒数第一位，所以分片2返回结果给协调节点的时候，不会包含bu_id=1这个结果

但是最终汇总到协调节点的时候，bu_id=1的最终结果排名第一，返回给了客户端，但是这个聚合结果是不准确的，因为缺少了分片2中的那少部分结果

针对上面这种场景，如果采用预聚合的结果，实际上就是查询明细，也就是说对于bu_id=1这个值的聚合结果就是一条记录，只会存在一个分片中，最终到汇总节点的时候也就是bu_id=1的完整的聚合结果，所以直接用es存储也成为了可能