单表数据行数达到2000w级别,如何做查询优化,是否有更好的数据存储推荐?

此表为用户活跃表,按月份进行了分表,单表数据行数大概在 2000w 左右,需要根据用户标识进行去重然后统计。

下面是一张 1000w 左右的表的查询结果,SQL 语句如下:

select count(distinct open_id) from user_behavior_logs_202108 where log_date BETWEEN 20210805 and 20210825;

查询时间:26.09s

Explain 结果如下:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_behavior_logs_202108 (NULL) ALL idx_open_id,idx_log_date (NULL) (NULL) (NULL) 9739837 11.11 Using where

1000w 就查询这么慢了。
尝试通过group by 替换 distinct,然后 count(*),但是没啥效果,依旧那么慢。
听说 pgsql 性能会好一些,也尝试了 pgsql2000w 的查询效率,耗时也在30s+了。
想请问有没有哪位大神碰到过类似的场景,是如何进行查询优化的,或是有更好的数据存储推荐。

期待大家一起讨论😜

Promise
讨论数量: 9

数据统计上BI,或者用es,都不想的话可以增加辅表,存储统计数据,不用实时动态统计

2年前 评论

加个 index_log_date_open_id 联合索引试试

2年前 评论

@风吹过有夏天的味道 会走索引,但执行时间反而更久了 :joy:

2年前 评论

@jonyle es 有像 mysqlunion 操作吗,将子表进行拼接,然后再进行去重统计

2年前 评论

跑个定时任务 每日的数据归总一下弄个中间表

2年前 评论

@风吹过有夏天的味道 因为需要对用户进行去重,所以这里做不了中间表 难受😫

2年前 评论

@Promisehp 将每天要跑的 数据 整合好之后凌晨跑 跑上去 可以解决问题吗

2年前 评论

现在又回过头看到了这篇。有个想法,数据多用索引慢主要是index_log_date这个字段区分度不高,可以在加个字段一天就一个值,然后用此字段加索引做区分

1年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!