千万级历史数据跑报表 性能优化方案

各位社区的大佬:
小弟 现在有个千万数据量的会员表,有注册时间及注册渠道。现在有个业务需求需要把这些数据清洗到数据报表(数据表)里面
数据报表格式如下

register_date register_channel count
2020-01-01 1 10
2020-01-01 2 13
2020-01-02 1 12
2020-01-02 2 11

目前我的做法是查询表里最早注册时间循环至当前时间,在循环里面查询日期下 ->groupBy('registered_channel')->selectRaw('registered_channel as register_channel, count(1) as register_num') 渠道 及渠道注册人数;

不知各位还有什么更好的优化方案

《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 8

新建一张表,把数据按格式导入到新表里,每日凌晨更新一次

PS :发帖的时候,请检查几遍在发出来,你看下短短几个字,多少个错别字

3年前 评论
kiti (楼主) 3年前

你用循环就注定你的速度不会快

3年前 评论
kiti (楼主) 3年前

你先分批次清洗数据 然后放入一个队列 一次插入多条数据 看你业务需求

3年前 评论

不需要实时查询的话,将数据分批归档不就好了吗?而且归档过的数据不会变动的

3年前 评论

group by 数据库不会蹦么?给出如下伪代码:

$total = Register::count();
$limit = 1000;
$batch = ceil($total / $limit);
for ($i = 0; $i < $batch; $i ++) {
    $skip = $i * $limit;
    $items = Register::query()->skip($skip)->limit($limit)->get();
    foreach($items as $item) {
        $report = Report::query()->where('register_date', $item->date)->where('register_channel', $item->channel)->first();
        if ($report) {
            $report->count ++;
            $report->save();
        } else {
            Report::create(['register_date' => $item->date, 'register_channel' => $item->channel, 'count' => 1]);
        }
    }
}
3年前 评论
lar_cainiao 3年前
GeorgeKing (作者) 3年前
kiti (楼主) 3年前

创建一个表记录已经清洗数据的id,清洗结果,失败原因, 然后定时去执行脚本去刷(脚本取记录表里不存在id的分块)

3年前 评论

千万级的mysql还行啊,时间建个索引 你可以按照月写个循环,按照insert into select 这种形式到个新表应该不是问题

3年前 评论

说一下我们项目当前的做法吧 (用户登录活跃统计)

要求: 每天凌晨统计昨天一天的数据,统计好放到 daily 中

实现:

  1. 前一天统计完会保存一个时间为 23:59:59 的记录id (例: 1000000)
  2. 凌晨用计划任务的方式调用脚本去统计, sql 大概是
    select * from activity where id > 1000000 
    and created_at between '2020-07-29 00:00:00' and '2020-07-29 23:59:59'
    如需分块,则用 chunk() 或 手动 limit 方式分块查询;如果可以在 sql 中计算,则视情况用 group by
  3. 统计好的数据 insert 到 daily 表中,然后再用 id > 1000000created_at between 查出 max(id) 保存下来,方便用于明天第一步的使用。

注意项: 因为 created_at 是 timestamp 字段,加索引也不怎么管用,而主键索引很好用

3年前 评论
kiti (楼主) 3年前
LuminEe (作者) 3年前
LuminEe (作者) 3年前

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