JAVA程序，MYSQL日志表按月分区

java程序， AOP（Aspect Oriented Programming）环绕通知，记录接口调用日志。

package springboot.aop;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import springboot.aop.dao.RequestLogMapper;
import springboot.aop.entity.RequestLog;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;

/**
 * @author cruder
 * @version 1.0
 * @date 2023-05-12 10:07:54 
 */
@Aspect
@Component
@Slf4j
public class CallLogAspect {

    @Resource
    private RequestLogMapper requestLogMapper;

    /**
     * 切controller中的所有方法
     */
    @Pointcut("execution(* springboot..controller..*(..))")
    public void point() {
    }

    @Around("point()")
    public Object doBefore(ProceedingJoinPoint joinPoint) throws Throwable {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        log.info("---------------------------------------------------------------------------");
        String IP = request.getRemoteAddr();
        log.info("【请求源头】 IP={},URL={},Method={}",IP , request.getRequestURL().toString(), request.getMethod());
        String requestParam ;
        RequestLog requestLog = new RequestLog();
        requestLog.setRequestIp(IP);
        requestLog.setUrl(request.getRequestURL().toString());
        requestLog.setMethod(request.getMethod());
        if ("GET".equals(request.getMethod())) {
            requestParam = request.getQueryString();
            if(requestParam!=null && requestParam.length()<1000){
                log.info("【请求参数】 {}", requestParam);
                requestLog.setRequestParam(requestParam);
            }
        } else {
            requestParam = Arrays.toString(joinPoint.getArgs());
            if(requestParam.length()<1000){
                log.info("【请求参数】 {}", requestParam);
                requestLog.setRequestParam(requestParam);
            }
        }
        Object re = joinPoint.proceed();
        String returnParam = JSON.toJSONString(re);
        if(returnParam.length()<1000){
            log.info("【请求返回】 {}", returnParam);
            requestLog.setReturnParam(returnParam);
        }
        requestLog.setUserId("cruder");
        requestLogMapper.insert(requestLog);
        log.info("---------------------------------------------------------------------------");


        return re;
    }

}

mysql 建立分区表

SHOW VARIABLES LIKE '%partition%';
SELECT @@version;
SELECT VERSION();
SHOW VARIABLES LIKE '%version%';
SHOW VARIABLES
    WHERE variable_name LIKE 'version%';


create table request_log
(
    request_ip    VARCHAR(16)                             null comment '请求ip',
    user_id       VARCHAR(32)                             null comment '用户编码',
    url           VARCHAR(256)                            null comment '请求url',
    method        VARCHAR(16)                             null comment '请求方法',
    request_param VARCHAR(1000)                           null comment '请求参数',
    return_param  VARCHAR(1000)                           null comment '返回参数',
    create_time   TIMESTAMP default CURRENT_TIMESTAMP
)
    comment '接口请求日志表'
partition by range (UNIX_TIMESTAMP(create_time))(
    PARTITION p0 values less than (UNIX_TIMESTAMP('20230501')),
    PARTITION p1 values less than (UNIX_TIMESTAMP('20230601')),
    PARTITION p2 values less than (UNIX_TIMESTAMP('20230701')),
    PARTITION p999 values less than MAXVALUE
    );

-- 为分区表添加n个分区：
ALTER TABLE request_log ADD PARTITION (PARTITION p3 values less than (UNIX_TIMESTAMP('20230801')));

-- 删除一个分区已经分区内的所有数据
ALTER TABLE request_log DROP PARTITION p999;

-- 删除分区，但保留分区内的数据
ALTER TABLE request_log TRUNCATE PARTITION p999;

-- 重建分区，即先删除分区中的所有记录，然后重新插入。可用于整理分区碎片。
ALTER TABLE request_log REBUILD PARTITION p0, p1;

-- 优化分区，整理分区碎片
ALTER TABLE request_log OPTIMIZE PARTITION p0, p1;

-- 修补分区
ALTER TABLE request_log REPAIR PARTITION p0,p1;

-- 检查分区
ALTER TABLE request_log CHECK PARTITION p1;

-- 查看某个schema下某个表的分区信息
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 'request_log';

-- 查看某个schema下某个表的分区信息
ALTER TABLE request_log ANALYZE PARTITION p1;

select month(now());
select unix_timestamp(now());
select day(now());
select to_days(now());
select to_days('20230512');
select year(now());
select dayofmonth(now());
select dayofyear(now());

select * from request_log

explain select * from request_log where create_time > '2023-05-01 00:00:00' and create_time < '2023-05-31 23:59:59'
order by create_time asc;