Mysql数据分区---日志表数据过大问题处理

生产环境会产生许多操作日志，占用大量内存，因此需要释放空间，仅保留近几个月的 数据。

1. 建表，先默认一个分区

DROP TABLE IF EXISTS `sys_log`;

CREATE TABLE `sys_log` (

`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '编号',

`type` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '1' COMMENT '日志类型',

`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '日志标题',

`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者',

`create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

`remote_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作IP地址',

`user_agent` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户代理',

`request_uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '请求URI',

`method` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作方式',

`params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '操作提交的数据',

`exception` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '异常信息',

`result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,

`use_time` bigint(20) NULL DEFAULT NULL

) ENGINE = InnoDB AUTO_INCREMENT = 66512 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '日志表' ROW_FORMAT = Dynamic

PARTITION BY RANGE (TO_DAYS(create_date))(

PARTITION p20220601 VALUES LESS THAN ( TO_DAYS('20220601'))

);

SET FOREIGN_KEY_CHECKS = 1;

2. 自动创建新分区

DELIMITER $$

DROP PROCEDURE IF EXISTS auto_create_partition$$

CREATE PROCEDURE `auto_create_partition`(IN `table_name` varchar(64))

BEGIN

SET @next_month:=CONCAT(date_format(date_add(now(),interval 2 month),'%Y%m'),'01');

SET @SQL = CONCAT(

'ALTER TABLE `',

table_name,

'`',

' ADD PARTITION (PARTITION p', @next_month, " VALUES LESS THAN (TO_DAYS(",

@next_month ,")) );" );

PREPARE STMT FROM @SQL;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END$$

DELIMITER ;

3. 自动删除N个月前的分区

DELIMITER $$

DROP PROCEDURE IF EXISTS auto_del_partition$$

CREATE PROCEDURE `auto_del_partition`(IN `table_name` varchar(64),IN `reserved_month` int)

BEGIN

SET @del_month:=CONCAT(date_format(date_sub(now(),interval reserved_month month),'%Y%m'),'01');

SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '` DROP PARTITION p', @del_month, ";" );

PREPARE STMT FROM @SQL;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END$$

DELIMITER ;

4. 创建一个Event,每月执行一次

DROP EVENT IF EXISTS `sys_log_auto_partition`;

DELIMITER $$

CREATE EVENT `sys_log_auto_partition`

ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE

ENABLE

DO

BEGIN

call auto_create_partition('sys_log');

call auto_del_partition('sys_log',6);

END$$

DELIMITER ;

5. 验证

-- 查看分区

select partition_name part, partition_expression expr, partition_description descr, table_rows from INFORMATION_SCHEMA.partitions where TABLE_SCHEMA = 'isp_xa_test_db_20' and TABLE_NAME="sys_log";

-- 新增分区

alter table `sys_log` add partition (partition p20220701 values less than (TO_DAYS('20220701')));

alter table `sys_log` add partition (partition p20220801 values less than (TO_DAYS('20220801')));

alter table `sys_log` add partition (partition p20220901 values less than (TO_DAYS('20220901')));

alter table `sys_log` add partition (partition p20221001 values less than (TO_DAYS('20221001')));

alter table `sys_log` add partition (partition p20221101 values less than (TO_DAYS('20221101')));

alter table `sys_log` add partition (partition p20221201 values less than (TO_DAYS('20221201')));

alter table `sys_log` add partition (partition p20230101 values less than (TO_DAYS('20230101')));

alter table `sys_log` add partition (partition p20230201 values less than (TO_DAYS('20230201')));

alter table `sys_log` add partition (partition p20230301 values less than (TO_DAYS('20230301')));

alter table `sys_log` add partition (partition p20230401 values less than (TO_DAYS('20230401')));

-- 删除分区

alter table sys_log drop partition p20230501;

-- 主动调测试

call auto_create_partition('sys_log');

call auto_del_partition('sys_log',7);

-- 可以修改上面Event调用频率为每分钟一次，来测试Event