问题
系统里的操作日志表,主要记录员工发起的各个请求(CRUD),刚建了没几个月,已经上千万条记录了,导致页面查询的时候,大概需要3-5秒的时间,如果加上条件查询,基本5秒多了。这个时间是完全不能接受的,所以打算重新设计一下。
日志表的元数据:(id,name,url,param,add_time…)
分析
- 现在表每天大概30-50w的数据增长,基本每秒都有十几条的数据插入
- 写多查少,数据库没有做读写分离
- 一般只查看最近的数据
- 基本不会逐个查询,条件查询用的比较多
- 几个在where中用到的字段都没加索引
- sql比较简单,基本没有什么可以优化的
方案
给where条件上的字段加索引
由于name和url查询的时候需要用到like “%xxx%”,这样就算给name和url加上索引,查询的时候也不会用到。add_time字段加索引可以用到,而且where中用到的比较多。但是给数据量这么大的表加索引,耗时惊人。之后越来越多的数据,索引消耗的空间也很可观。
分表
根据hash(name)取模分表
需要预估分多少个表,按当前数据量和数据增长速度来看,几十个表都不够。而且后期增长后添加分表重新分表的工作量太大了。
根据add_time按每几个月增量分表
根据数据增长速度来看,基本需要每周都建个新表,未来表的数量多到难以想象。
归档
因为一般只查看最近的操作日志,可以限制原表储存数据的时间,先把之前的数据存到一张old表中,再每天转存一个月前一天的数据,保持原表只保存最近一个月的数据。
集群
主从复制
读写分离
由于暂时不考虑集群,这两个pass了
解决
重新分析了一下原来的需求,发现对于员工查询类型的请求,并不是很重要,可以不保存。而表里保存的记录基本有90%都是查询类型的请求记录。所以只要过滤改类型的请求,只保存增、删、改的请求记录,就可以使增长量下降90%,这样表的数据压力就没有原来这么大了。再每天将一个月前的数据归档,原表的数据量就大大减少,查询速度也就跟着上来了。
过滤请求
在原来的拦截器中,对request.getRequestURI()进行过滤,原先请求url地址中包含”/get”、”/fetch”、”/list”、”/query”等请求相关的关键字(需要当时设计接口地址的时候有一定的规范),全部直接return。部分代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | public class LogInterceptor extends HandlerInterceptorAdapter { //不需要记录日志的url关键字 private static final String[] NO_LOG_URL_KEYWORD = {"/get", "/fetch", "/list", "/query"}; /** * 记录操作日志 * * @param request */ public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception { this.insertLogOperate(request); } public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception { } private void insertLogOperate(HttpServletRequest request) { //过滤uri String uri = request.getRequestURI(); for (String key : NO_LOG_URL_KEYWORD) { if (uri.contains(key)) { return; } } // 记录操作日志入库 LogOperate log = new LogOperate(); logOperate.setName(authUtil.getCurrentUserName()); logOperate.setParam(JsonUtil.toJsonString(request.getParameterMap())); logOperate.setIp(RequestUtils.getClientIP(request)); logOperate.setUrl(uri); logOperate.setAddTime(new Timestamp(System.currentTimeMillis())); logOperateService.save(logOperate); } } |
修改过程中发现一个问题:如果是POST类型的请求,request.getParameterMap()不能获取到请求的参数,需要用流读取request,而如果在拦截器中读取了,Controller层就读取不到参数了。网上查了一下,需要重写一个方法来解决,这个就不再这里说明了。
数据表处理
新建一张log_old表,和原来的log表结构一样:
create table log_old like log;
|
写一个存储过程,把一个月前的记录存到log_old中:
delimiter $ CREATE PROCEDURE proc_logBackUp() begin set @time = NOW() - INTERVAL 1 MONTH; insert into log_old select * from log where add_time < @time; delete from log where add_time < @time; end $ delimiter ; |
创建事件,每天执行上面这个过程:
create event if not exists event_logBackUp on schedule every 1 day on completion preserve do call proc_logBackUp(); |
备注:如果数据量太大,第一次执行proc_logBackUp()可能会执行失败,可以根据数据量,把@time改成手动设置某个时间点,分多次执行过程。之后的每天执行时间就不会出什么问题了。