文章描述了一个针对Zabbix的history表进行MySQL分区的自动化过程,特别是增加了对数字时间戳类型的兼容性。通过修改存储过程`partition_maintenance`,实现了根据int类型的时间戳字段`clock`进行分区,并展示了不同时间戳类型的分区SQL示例。
背景:MySQL自动化分区现阶段只支持datetime类型,需要添加数字类时间戳类型,用于zabbix的history历史表数据自动化分区。
- CREATE TABLE `history` (
- `itemid` bigint(20) unsigned NOT NULL,
- `clock` int(11) NOT NULL DEFAULT '0',
- `value` double NOT NULL DEFAULT '0',
- `ns` int(11) NOT NULL DEFAULT '0',
- KEY `history_1` (`itemid`,`clock`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- DELIMITER $$
-
- USE `managerdb`$$
-
- DROP PROCEDURE IF EXISTS `partition_maintenance`$$
-
- CREATE DEFINER=`root`@`%` PROCEDURE `partition_maintenance`(p_schema_name VARCHAR(32), p_table_name VARCHAR(32), p_keep_data_days INT, p_create_next_intervals INT,p_par_column VARCHAR(64))
- BEGIN
- /*
- */
- DECLARE l_older_than_partition_date VARCHAR(16);
- DECLARE l_partition_name VARCHAR(16);
- DECLARE l_old_partition_name VARCHAR(16);
- DECLARE l_less_than_timestamp DATETIME;
- DECLARE l_cur_time VARCHAR(64);
- DECLARE l_partition_type VARCHAR(16);
-
- -- 数字时间戳新增
- -- 检查分区字段类型
- SELECT data_type INTO l_partition_type
- FROM information_schema.columns
- WHERE table_schema = p_schema_name
- AND table_name = p_table_name
- AND column_name = p_par_column;
-
-
- #验证是否为分区表单独执行
- CALL partition_verify_new(p_schema_name, p_table_name, p_keep_data_days,p_par_column,p_create_next_intervals); -- 验证TABLE_NAME表是否为分区表。如果不是则转换为分区表
-
- SET l_cur_time = DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL p_keep_data_days DAY) , '%Y-%m-%d 00:00:00');
-
- SET @__interval = 1;
-
- create_loop: LOOP
-
- IF @__interval > p_create_next_intervals+p_keep_data_days THEN -- 如果__interval大于一次创建14个分区,则退出
-
- LEAVE create_loop;
-
- END IF;
-
- SET l_less_than_timestamp = DATE_ADD(l_cur_time, INTERVAL @__interval DAY);
-
- SET l_partition_name = DATE_FORMAT(DATE_ADD(l_cur_time, INTERVAL @__interval-1 DAY), 'p%Y%m%d'); -- 获取当前分区表名称
-
- IF(l_partition_name != l_old_partition_name) THEN
-
- -- 数字时间戳新增
- IF l_partition_type = 'int' THEN
- CALL partition_create(p_schema_name, p_table_name, l_partition_name, UNIX_TIMESTAMP(l_less_than_timestamp)); -- 创建当前分区表
- ELSE
- CALL partition_create(p_schema_name, p_table_name, l_partition_name, TO_DAYS(l_less_than_timestamp)); -- 创建当前分区表
- END IF;
-
- END IF;
-
- SET @__interval=@__interval+1;
-
- SET l_old_partition_name = l_partition_name;
-
- END LOOP;
-
- SET l_older_than_partition_date=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL p_keep_data_days DAY), '%Y%m%d'); -- 201608150000 获取最小分区时间
-
- CALL partition_drop(p_schema_name, p_table_name, l_older_than_partition_date); -- 删除过期分区
-
- UPDATE par_info -- 更新配置表
- SET `update_time` = NOW(),
- `min_partition_name` = CONCAT('p',l_older_than_partition_date),
- `max_partition_name` = l_partition_name
- WHERE `table_schema` =p_schema_name
- AND `table_name`= p_table_name;
- END$$
-
- DELIMITER ;
-
- DELIMITER $$
-
- USE `managerdb`$$
-
- DROP PROCEDURE IF EXISTS `partition_verify_new`$$
-
- CREATE DEFINER=`root`@`%` PROCEDURE `partition_verify_new`(p_schema_name VARCHAR(64), p_table_name VARCHAR(64),p_keep_data_days INT, p_par_column VARCHAR(64),p_create_next_intervals INT)
- BEGIN
- /*
- */
- DECLARE l_partition_name VARCHAR(16);
- DECLARE l_partition_type VARCHAR(16);
- DECLARE l_retrows INT(11);
- DECLARE l_future_timestamp TIMESTAMP;
-
- -- 检查该表是不是分区表
- SELECT COUNT(1)
- INTO l_retrows
- FROM information_schema.partitions
- WHERE table_schema = p_schema_name
- AND table_name = p_table_name
- AND l_partition_name IS NULL;
-
- -- 数字时间戳新增
- -- 检查分区字段类型
- SELECT data_type INTO l_partition_type
- FROM information_schema.columns
- WHERE table_schema = p_schema_name
- AND table_name = p_table_name
- AND column_name = p_par_column;
-
- IF l_partition_type = "int" THEN
- SET @__PARTITION_FUN="UNIX_TIMESTAMP";
- ELSE
- SET @__PARTITION_FUN="TO_DAYS";
- END IF;
-
-
-
- IF l_retrows = 1 THEN
-
- -- 数字时间戳新增
- IF l_partition_type = 'int' THEN
- -- ALTER TABLE zabbix.history partition by range(`clock`);
- SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, CONCAT(" PARTITION BY RANGE(",p_par_column,")("));
- ELSE
- -- alter table zabbix.history partition by range(to_days(clock));
- SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, CONCAT(" PARTITION BY RANGE(TO_DAYS(",p_par_column,"))("));
- END IF;
- SET @__TEMP_DAYS =p_keep_data_days;
-
- verify_loop: LOOP
-
- -- 将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中
- SET l_future_timestamp = TIMESTAMPADD(HOUR, 24, CONCAT(DATE_SUB(CURDATE(),INTERVAL @__TEMP_DAYS DAY), " ", '00:00:00'));
-
- SET l_partition_name = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL @__TEMP_DAYS DAY), 'p%Y%m%d');
-
- -- 数字时间戳新增
- -- ALTER TABLE track add PARTITION (PARTITION p201708160002 VALUES LESS THAN ( TO_DAYS('2017-11-25 00:00:00')));
- SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "PARTITION ", l_partition_name, " VALUES LESS THAN (",@__PARTITION_FUN,"('", l_future_timestamp, "')),");
-
- SET @__TEMP_DAYS=@__TEMP_DAYS-1;
-
- IF @__TEMP_DAYS<-p_create_next_intervals
- THEN
-
- LEAVE verify_loop;
-
- END IF;
-
- END LOOP;
-
- SET @__PARTITION_SQL = CONCAT(SUBSTRING(@__PARTITION_SQL,1,LENGTH(@__PARTITION_SQL)-1),');');
-
- SELECT @__PARTITION_SQL;
- PREPARE STMT FROM @__PARTITION_SQL;
- EXECUTE STMT;
- DEALLOCATE PREPARE STMT;
- END IF;
- END$$
-
- DELIMITER ;
- -- 分区字段类型:int (数字时间戳)
- ALTER TABLE zabbix.history PARTITION BY RANGE(clock)(PARTITION p20230521 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-22 00:00:00')),
- PARTITION p20230522 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-23 00:00:00')),PARTITION p20230523 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-24 00:00:00')),
- PARTITION p20230524 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-25 00:00:00')),PARTITION p20230525 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-26 00:00:00')),
- PARTITION p20230526 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-27 00:00:00')),PARTITION p20230527 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-28 00:00:00')),
- PARTITION p20230528 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-29 00:00:00')),PARTITION p20230529 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-30 00:00:00')),
- PARTITION p20230530 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),PARTITION p20230531 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')),
- PARTITION p20230601 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-02 00:00:00')),PARTITION p20230602 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-03 00:00:00')),
- PARTITION p20230603 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-04 00:00:00')),PARTITION p20230604 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-05 00:00:00')),
- PARTITION p20230605 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-06 00:00:00')),PARTITION p20230606 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-07 00:00:00')),
- PARTITION p20230607 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-08 00:00:00')),PARTITION p20230608 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-09 00:00:00')),
- PARTITION p20230609 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')),PARTITION p20230610 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-11 00:00:00')),
- PARTITION p20230611 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-12 00:00:00')),PARTITION p20230612 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-13 00:00:00')),
- PARTITION p20230613 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-14 00:00:00')),PARTITION p20230614 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-15 00:00:00')),
- PARTITION p20230615 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-16 00:00:00')),PARTITION p20230616 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-17 00:00:00')),
- PARTITION p20230617 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-18 00:00:00')),PARTITION p20230618 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-19 00:00:00')),
- PARTITION p20230619 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')),
- PARTITION p20230620 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-21 00:00:00')));
-
-
-
- -- 分区字段类型:datetime
- ALTER TABLE zabbix.history2 PARTITION BY RANGE(TO_DAYS(clock))(PARTITION p20230530 VALUES LESS THAN (TO_DAYS('2023-05-31 00:00:00')),
- PARTITION p20230531 VALUES LESS THAN (TO_DAYS('2023-06-01 00:00:00')),PARTITION p20230601 VALUES LESS THAN (TO_DAYS('2023-06-02 00:00:00')),
- PARTITION p20230602 VALUES LESS THAN (TO_DAYS('2023-06-03 00:00:00')),PARTITION p20230603 VALUES LESS THAN (TO_DAYS('2023-06-04 00:00:00')),
- PARTITION p20230604 VALUES LESS THAN (TO_DAYS('2023-06-05 00:00:00')),PARTITION p20230605 VALUES LESS THAN (TO_DAYS('2023-06-06 00:00:00')),
- PARTITION p20230606 VALUES LESS THAN (TO_DAYS('2023-06-07 00:00:00')),PARTITION p20230607 VALUES LESS THAN (TO_DAYS('2023-06-08 00:00:00')),
- PARTITION p20230608 VALUES LESS THAN (TO_DAYS('2023-06-09 00:00:00')));
-
-
-
- ****************************************************************************************
- CREATE TABLE `history` (
- `itemid` bigint(20) unsigned NOT NULL,
- `clock` int(11) NOT NULL DEFAULT '0',
- `value` double NOT NULL DEFAULT '0',
- `ns` int(11) NOT NULL DEFAULT '0',
- KEY `history_1` (`itemid`,`clock`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- /*!50100 PARTITION BY RANGE (clock)
- (PARTITION p20230506 VALUES LESS THAN (1683388800) ENGINE = InnoDB,
- PARTITION p20230507 VALUES LESS THAN (1683475200) ENGINE = InnoDB,
- PARTITION p20230508 VALUES LESS THAN (1683561600) ENGINE = InnoDB,
- PARTITION p20230509 VALUES LESS THAN (1683648000) ENGINE = InnoDB,
- PARTITION p20230510 VALUES LESS THAN (1683734400) ENGINE = InnoDB,
- PARTITION p20230511 VALUES LESS THAN (1683820800) ENGINE = InnoDB,
- PARTITION p20230512 VALUES LESS THAN (1683907200) ENGINE = InnoDB,
- PARTITION p20230513 VALUES LESS THAN (1683993600) ENGINE = InnoDB,
- PARTITION p20230514 VALUES LESS THAN (1684080000) ENGINE = InnoDB,
- PARTITION p20230515 VALUES LESS THAN (1684166400) ENGINE = InnoDB,
- PARTITION p20230516 VALUES LESS THAN (1684252800) ENGINE = InnoDB,
- PARTITION p20230517 VALUES LESS THAN (1684339200) ENGINE = InnoDB,
- PARTITION p20230518 VALUES LESS THAN (1684425600) ENGINE = InnoDB,
- PARTITION p20230519 VALUES LESS THAN (1684512000) ENGINE = InnoDB,
- PARTITION p20230520 VALUES LESS THAN (1684598400) ENGINE = InnoDB,
- PARTITION p20230521 VALUES LESS THAN (1684684800) ENGINE = InnoDB,
- PARTITION p20230522 VALUES LESS THAN (1684771200) ENGINE = InnoDB,
- PARTITION p20230523 VALUES LESS THAN (1684857600) ENGINE = InnoDB,
- PARTITION p20230524 VALUES LESS THAN (1684944000) ENGINE = InnoDB,
- PARTITION p20230525 VALUES LESS THAN (1685030400) ENGINE = InnoDB,
- PARTITION p20230526 VALUES LESS THAN (1685116800) ENGINE = InnoDB,
- PARTITION p20230527 VALUES LESS THAN (1685203200) ENGINE = InnoDB,
- PARTITION p20230528 VALUES LESS THAN (1685289600) ENGINE = InnoDB,
- PARTITION p20230529 VALUES LESS THAN (1685376000) ENGINE = InnoDB,
- PARTITION p20230530 VALUES LESS THAN (1685462400) ENGINE = InnoDB,
- PARTITION p20230531 VALUES LESS THAN (1685548800) ENGINE = InnoDB,
- PARTITION p20230601 VALUES LESS THAN (1685635200) ENGINE = InnoDB,
- PARTITION p20230602 VALUES LESS THAN (1685721600) ENGINE = InnoDB,
- PARTITION p20230603 VALUES LESS THAN (1685808000) ENGINE = InnoDB,
- PARTITION p20230604 VALUES LESS THAN (1685894400) ENGINE = InnoDB,
- PARTITION p20230605 VALUES LESS THAN (1685980800) ENGINE = InnoDB,
- PARTITION p20230606 VALUES LESS THAN (1686067200) ENGINE = InnoDB,
- PARTITION p20230607 VALUES LESS THAN (1686153600) ENGINE = InnoDB,
- PARTITION p20230608 VALUES LESS THAN (1686240000) ENGINE = InnoDB,
- PARTITION p20230609 VALUES LESS THAN (1686326400) ENGINE = InnoDB,
- PARTITION p20230610 VALUES LESS THAN (1686412800) ENGINE = InnoDB,
- PARTITION p20230611 VALUES LESS THAN (1686499200) ENGINE = InnoDB,
- PARTITION p20230612 VALUES LESS THAN (1686585600) ENGINE = InnoDB,
- PARTITION p20230613 VALUES LESS THAN (1686672000) ENGINE = InnoDB,
- PARTITION p20230614 VALUES LESS THAN (1686758400) ENGINE = InnoDB,
- PARTITION p20230615 VALUES LESS THAN (1686844800) ENGINE = InnoDB,
- PARTITION p20230616 VALUES LESS THAN (1686931200) ENGINE = InnoDB,
- PARTITION p20230617 VALUES LESS THAN (1687017600) ENGINE = InnoDB,
- PARTITION p20230618 VALUES LESS THAN (1687104000) ENGINE = InnoDB,
- PARTITION p20230619 VALUES LESS THAN (1687190400) ENGINE = InnoDB,
- PARTITION p20230620 VALUES LESS THAN (1687276800) ENGINE = InnoDB) */
-