日志分表分页

因为公司数据日志打点十分频繁, 并且涵盖有多个条件筛选的情况, 并且采用了数据分表的出来数据写入.

更加主要问题是每日单表数据递增速度在最低 50G 以上, 并且如果用户量越高递增的数据流越高, 还有更大的问题就是数据格式不稳定.

内部上报数据是 JSON 格式数据, 高版本 MySQL 支持的 JSON 数据结构选用这种类型方便对后续数据过滤匹配筛选

而日志上报数据后台需要可以通过 年月日时分秒 这种条件精确筛选入库时间, 后续还要支持其他复杂条件精确检索.

这里以简单打点日志表 step_log 做说明, 后台进程会采用分表入库 step_log_20241201,step_log_20241202,.... 入库, 那么后续需要检索分页数据就需要费时处理下, 可以假设表结构字段如下:

player_id: 玩家ID
sid: 服务器ID
time: 入库时间戳
action: 行为id
val: 上报数据 JSON, 以 {} 数据格式为主

需要说明这里的 val 数据内部 JSON 内部数据会按照频繁度, 从里面升级为表字段; 比如表内部带有 { "channel":1001 } 字段是运营迫切需要的, 那么后续会将 JSON 内字段 晋升 为表字段, 追加上索引之后能够更快加速表数据检索的速度.

之后数据分页也需要利用 UNION ALL 来做表合并, 因为日志表一般数据结构是固定, 所以可以直接将所有表连接查询:

# 假设用户需要查询 step_log_20241201 ~ step_log_20241207 的表数据 
# 首选需要判断表是否存在, 去掉那些不存在的表
SHOW TABLES LIKE 'step_log_20241201'

# 之后通过检索出存在合表数据总量
SELECT COUNT(1) AS `total`
FROM step_log_20241201
UNION ALL
SELECT COUNT(1) AS `total`
FROM step_log_20241202;
# 其他表追加 UNION ALL ...., 最后返回总数列表的数量直接合计加上即可得出数据总量

# 只有追加上面所有分表总数大于0才需要后续的具体数据查询
# 这里会获取所有的分表总数, 之后就能合表分页处理
SELECT *
FROM step_log_20241201
UNION ALL
SELECT *
FROM step_log_20241202
ORDER BY id DESC # 合表之后排序处理
LIMIT 10,10 # 分页
;

# 如果采用条件的时候就能对字段进行索引加速, 包括查询总数时候加上
SELECT COUNT(1) AS `total`
FROM step_log_20241201
UNION ALL
SELECT COUNT(1) AS `total`
FROM step_log_20241202
WHERE player_id = 10001;
# 分表是能触发各自索引加速


# 后续联表查询直接加上即可
SELECT *
FROM step_log_20241201
UNION ALL
SELECT *
FROM step_log_20241202
# 追加条件查询
WHERE player_id = 10001;
# 合表之后排序处理
ORDER BY id
DESC LIMIT 10,10 # 分页
;

因为日志表大部分都是固定不变字段和结构, 所以这种联表方式正好契合跨分表查询的情况; 但是分页实际上就是查询数据之后 skip 丢弃掉多余数据, 本身如果表数据的内容过大的情况查询时候占用内存依旧很可怕, 像 单天单表50G 的情况跨天数查询依旧无解, 只能通过硬件条件来覆盖掉损失.

这种方法虽然能够完美满足需求, 但是依旧没办法很好处理大数据量筛选处理问题, 只能说算是个不那么好的妥协方案.

MeteorCat / 日志分表分页

日志分表分页