问题

​ 系统里的操作日志表，主要记录员工发起的各个请求(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
	 */
  	@Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
    	this.insertLogOperate(request);
    }
  
  	@Override
    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表结构一样：

1
create table log_old like log;

写一个存储过程，把一个月前的记录存到log_old中：

1
2
3
4
5
6
7
8
9
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 ;

创建事件，每天执行上面这个过程：

1
2
3
4
create event if not exists event_logBackUp
on schedule every 1 day
on completion preserve
do call proc_logBackUp();

备注：如果数据量太大，第一次执行proc_logBackUp()可能会执行失败，可以根据数据量，把@time改成手动设置某个时间点，分多次执行过程。之后的每天执行时间就不会出什么问题了。