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

Question

现状是将用户的登录数据记录到一个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的方案，可以供海量登录数据准实时计算留存使用？

Answer 1

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