Mysql 表分区 及碎片清理

第一步： 创建分区的 表

引用

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;

------------------------------------------------