第一步: 创建分区的 表
DROP TABLE IF EXISTS `t_jy0`;
CREATE TABLE `t_jy0` (
`id` bigint(50) NOT NULL AUTO_INCREMENT,
`creatortime` datetime(6) NOT NULL COMMENT '速度设定',
`P2101002001` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '挤压数量',
`P2101002002` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '主缸位移',
`P2101002003` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '主缸压力',
`P2101002004` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '成品长度',
`P2101002005` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '运行信号',
`P2101002006` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`f_sn`, `f_creatortime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 367470 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic
PARTITION BY RANGE COLUMNS(f_creatortime) (PARTITION d_plc620201101 VALUES LESS THAN ('2019-12-01 08:00:00'))
第二步:定时事件添加分区过程
CREATE DEFINER=`root`@`%` PROCEDURE `createPartition`(tableName varchar(50))
begin
-- 计算下下个月1日的日期表达式(每个月1日做下下个月的分区)
set @pName = concat(year(adddate(now(),interval 2 month)),'-',right(concat('0',month(adddate(now(),interval 2 month))),2),'-01 01:00:00');
set @pMonth = concat(year(adddate(now(),interval 2 month)),right(concat('0',month(adddate(now(),interval 2 month))),2));
set @sqlStr = concat("alter table ",tableName," add partition ( partition ",tableName,"_",@pMonth," values less than('",@pName,"'));");
PREPARE create_stmt from @sqlStr;
EXECUTE create_stmt;
-- select @sqlStr;
end
优化清理
show table status like 'table_name';
查询结果中:
Index_length 代表索引的数量
Data_free 代表碎片数量
然后执行下面命令进行优化整理:
mysql > optimize table table_name
查看数据库连接:
show processlist;