沈老师,你好,想请教一个数据库查询日志,前台页面显示的问题。
需求:
按照某些特定检索条件查询日志;
通过前台Web页面查询并显示相关日志信息;
检索需求包含用户,时间段区间,类型等特定字段;
希望做到:
查询速度尽可能快;
支持分页查询;
目前方案:
日志信息存储在Oracle中,根据日期对Oracle做了分区处理,每天生成一个分区表,每个分区表中的数据总量大概在1000W左右。在相关查询字段例如用户,类型上建立索引,来满足不同维度的查询需求。
潜在问题:
跨分区的查询,求记录总数(计算分页时的查询),耗时要3-4分钟,请问有什么优化方法么?
==问题描述完==
这个需求还是非常变态的,通常日志会进行过滤/结构化/汇总,放入数据仓库,建立业务宽表,宽表上的查询,一般不会具体查一行一行的记录。
如果要支持检索,并一行一行在Web后台进行展示,至少要解决几个方面的问题:
存储问题;
检索问题;
扩展性问题(数据量扩展,检索字段扩展);
一、存储问题
是否可以用关系型数据库存储日志?
如果日志格式固定,检索条件固定,是可以的。
例如:
2019-08-11 23:19:20 uid=123 action=pay type=wechat money=12
可以转化为表:
t_log(date, time, uid, action, type, money)
然后在相关字段上建立索引,以满足后台查询与展示的需求。
数据量太大,怎么解决?
按照题目描述,日数据量大概在1000W级别,1年的数据量大概在36Y级别。
如果用Oracle存储,1000W为一个分区表:一年需要365个分区,跨分区的查询性能较低,不太合适。
改为1个月一个分区:单分区3Y记录,大部分分区无写操作(插入,修改,删除),只有索引上的读操作,读写性能基本能抗住。一年12个分区,性能比365个分区好很多。
虽然本例的日志可以结构化(将日志转化表),由于数据量太大,其实关系型数据库不太适用,可以用适合更大数据量的ES或者Hive来存储。
二、检索问题
日志格式固定,检索条件固定,如果用关系型数据库或者Hive存储,可以在相关字段上建立索引,来满足查询需求。
如果用ES来存储,其内部用倒排表实现,天然支持检索。
三、扩展性问题
1. 数据量扩展
不管用Oracle,ES还是Hive来存储,它们的区别只是单实例/单集群存储容量不一样,如果数据量无限扩展,本质上的解决方案还是“水平切分”。
需要注意的是,尽量不要使用自带的“分区表”来扩展,而在业务层自己拆分。
画外音:《互联网公司为啥都不用分区表?》。
2. 检索字段扩展
如果日志不是标准化的,检索字段也不是固定的,那就麻烦了,那就变成了也“搜索引擎”的问题。
此时使用ES是更为合适的,不过结合无限的数据量,最终可能需要自己实现存储于检索引擎(类似于百度,存储容量无限,检索字段不固定)。
总结:
结合本例,日志量大,模式固定,建议:
最建议,使用Hive存储,使用索引的方式实现日志后台检索需求;
如果扩展性要求稍高,可以使用ES实现存储与检索,使用水平扩展来存储更大的数据量;
【本文为51CTO专栏作者“58沈剑”原创稿件,转载请联系原作者】
戳这里,看该作者更多好文
【编辑推荐】
数据库允许空值(null),往往是悲剧的开始(1分钟系列)
通过自研数据库画像工具支持“去O”评估
数据库软件架构,到底要设计些什么?
内存KV缓存/数据库,可以选择它? | 1分钟系列
亿级流量高并发下,缓存与数据库不一致,咋办?