MySQL自动化分区新增数字时间戳类型

文章描述了一个针对Zabbix的history表进行MySQL分区的自动化过程,特别是增加了对数字时间戳类型的兼容性。通过修改存储过程`partition_maintenance`,实现了根据int类型的时间戳字段`clock`进行分区,并展示了不同时间戳类型的分区SQL示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

背景:MySQL自动化分区现阶段只支持datetime类型,需要添加数字类时间戳类型,用于zabbix的history历史表数据自动化分区。

1.zibbix的历史表结构

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

2.修改存储过程部分

partition_maintenance

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 ;

partition_verify_new


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 ;

3.运行结果



-- 分区字段类型: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) */
 

### MySQL 主键的概念 主键(Primary Key)是数据库设计中的核心概念之一,它用于唯一标识表中的每一行记录。主键可以是一个字段或者多个字段组合而成,在定义时需满足两个条件:不为空(NOT NULL),以及值具有唯一性。 在 MySQL 中,可以通过 `PRIMARY KEY` 关键字来声明某个字段作为主键。如果未显式指定主键,则 InnoDB 存储引擎会在后台隐式创建一个隐藏的主键[^1]。 ```sql CREATE TABLE example ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 上述代码展示了如何通过 SQL 创建一张带主键的表格,并设置其为自属性。 --- ### 数据类型的选取考量 对于主键的数据类型选择,常见的争议在于使用整数型还是字符型。整数型的优点包括存储效率高、索引性能好;而 UUID 或其他字符串形式虽然具备全局唯一性的优势,但也存在占用更多磁盘空间和降低写入性能等问题[^4]。 具体到实现层面: - **INT/BIGINT**: 推荐采用 BIGINT 类型以应对未来可能扩展的需求,因为即使当前看来足够大的数值范围也可能随着业务发展变得不足[^3]。 - **UUID**: 虽然提供了跨系统的唯一性保障,但由于其无序性和较大的长度,会对 B+ 树索引造成额外负担。 因此,《高性能MySQL》建议优先考虑基于时间戳或者其他有序算法生成的分布式 ID 方案替代传统 UUID 设计。 --- ### 自主键的工作机制及其优化选项 当设定某一列为自动量(`AUTO_INCREMENT`)时,每当新纪录被加入该列就会获得一个新的最大值加一的结果。然而这种简单的设计曾经带来了一些并发控制上的挑战——即多线程环境下可能出现锁等待现象从而影响吞吐量。 为此版本号大于等于 5.1.22 的 MySQL/InnoDB 实现了一个新的特性叫做 innodb_autoinc_lock_mode 参数用来调节不同场景下的锁定行为模式[^5]: | Mode Value | 描述 | |------------|----------------------------------------------------------------------------------------| | 0 (Traditional) | 行级锁应用于每一条单独的 INSERT INTO ... SELECT FROM 操作 | | 1 (Consecutive) | 只有批量插入操作才会触发表级别的锁 | | 2 (Interleaved) | 完全移除所有针对 auto-increment 值分配过程里的互斥保护措施 | 默认情况下此参数设为 '1' ,意味着大多数常规应用都不会遇到明显的延迟或阻塞情况。 --- ### 性能调优与注意事项 除了合理规划数据模型外还需要关注实际运行过程中可能会碰到的一些特殊情况比如: - 如果频繁删除大量连续编号条目后再继续新增则可能导致间隙填充问题进而引发死锁风险; - 当前支持的最大值一旦达到上限将会抛出错误提示无法再执行进一步的操作除非重新初始化序列起点位置; - 需要特别留意分区表结构下各自子分区内独立计算各自的起始偏移量逻辑差异带来的兼容性隐患等等[^2]. 综上所述, 正确理解和运用这些理论知识可以帮助开发者构建更加健壮高效的数据库应用程序. ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值