上亿用户登录数据计算留存

Clive
  • 44

现状是将用户的登录数据记录到一个mysql单表如下:

CREATE TABLE `user_retention_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `login_date` date NOT NULL,
  `register_date` date NOT NULL,
  `account` varchar(30) NOT NULL DEFAULT '',
  `login_ip` varchar(20) NOT NULL,
  `source` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx` (`source`,`login_date`,`register_date`,`account`) USING BTREE,
  KEY `idx_account_logindate` (`account`,`login_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

通过类似下列sql查询在注册日期register_date为2020-09-01的用户在登录日期login_date为2020-09-15的人数。

select count(distinct account) as remain_user_count from user_retention_stats where login_date='2020-09-15' and register_date='2020-09-01';
该表随着日期增长与用户量堆积,会变得越来越大,可能上亿数据,此时实时查询留存数据会变得效率很低。
是否有替代单表mysql的方案,可以供海量登录数据准实时计算留存使用?
回复
阅读 1k
1 个回答

用户的登录数据记录正常都是只插入不删改,可以实时同步到clickhouse进行实时计算,上亿条数据查询你那条SQL正常都只是毫秒级,可以去了解下,我最近也是在将公司的一些聚合查询挪到clickhouse上去执行。
如果不打算引入clickhouse,那就只能做分表了,正常可以按登录日期每月分表。

宣传栏