分享---每半月一个分区，分区自动维护

Question

建表语句：drop table if exists terminal_parameter;
CREATE TABLE  `terminal_parameter` (
  `terminal_parameter_id` int(11) NOT NULL AUTO_INCREMENT,
  `serial` int(11) DEFAULT NULL,
  `network_type` char(1) DEFAULT NULL,
  `mcc` int(8) DEFAULT NULL,
  `mnc` int(8) DEFAULT NULL,
  `lac` int(8) DEFAULT NULL,
  `cellid` int(8) DEFAULT NULL,
  `bsic_psc` int(8) DEFAULT NULL,
  `ta_ec_io` int(8) DEFAULT NULL,
  `bcch_rxlev_rscp` int(8) DEFAULT NULL,
  `arfcn_uarfcn` int(8) DEFAULT NULL,
  `rxq` int(8) DEFAULT NULL,
  `c1` int(8) DEFAULT NULL,
  `c2` int(8) DEFAULT NULL,
  `signal_intensity` int(8) DEFAULT NULL,
  `error_rate` int(8) DEFAULT NULL,
  `alarm_type` varchar(16) DEFAULT NULL,
  `txpower` int(8) DEFAULT NULL,
  `small_running_number` int(8) DEFAULT NULL,
  `createtime` datetime NOT NULL,
  `userid` int(8) NOT NULL,
  `terminal_id` int(8) DEFAULT NULL,
  `state` char(1) DEFAULT '0',
  `order_definition_id` int(8) DEFAULT NULL,
  `order_code` varchar(20) DEFAULT NULL,
  `charg_voltage` float(8,2) DEFAULT NULL,
  `battery_voltage` float(8,2) DEFAULT NULL,
  `temprad` float(8,2) DEFAULT NULL,
  `run_state` int(8) DEFAULT NULL,
  `switching_value1` int(8) DEFAULT NULL,
  `switching_value2` int(8) DEFAULT NULL,
  `bcch_freq` int(8) DEFAULT NULL,
  `rxlev` int(8) DEFAULT NULL,
  `rxlev_full` int(8) DEFAULT NULL,
  `rxlev_sub` int(8) DEFAULT NULL,
  `rxqual` int(8) DEFAULT NULL,
  `rxqual_full` int(8) DEFAULT NULL,
  `rxqual_sub` int(8) DEFAULT NULL,
  `idle_ts` int(8) DEFAULT NULL,
  `timing_advance` int(8) DEFAULT NULL,
  `tch_efr_out` int(8) DEFAULT NULL,
  `tch_efr_in` int(8) DEFAULT NULL,
  `dtx` int(8) DEFAULT NULL,
  `major_cycle_frequency` int(8) DEFAULT NULL,
  PRIMARY KEY (`terminal_parameter_id`,`createtime`),
  KEY `idx_createtime` (`createtime`),
  KEY `idx_terminal_id` (`terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p20101115 VALUES LESS THAN (TO_DAYS('2010-11-15')),
PARTITION p20101130 VALUES LESS THAN (TO_DAYS('2010-11-30')),
PARTITION p20101215 VALUES LESS THAN (TO_DAYS('2010-12-15')),
PARTITION p20101231 VALUES LESS THAN (TO_DAYS('2010-12-31')),
PARTITION p20110115 VALUES LESS THAN (TO_DAYS('2011-01-15')),
PARTITION p20110131 VALUES LESS THAN (TO_DAYS('2011-01-31')),
PARTITION p20110215 VALUES LESS THAN (TO_DAYS('2011-02-15')),
PARTITION p20110228 VALUES LESS THAN (TO_DAYS('2011-02-28')),
PARTITION p20110315 VALUES LESS THAN (TO_DAYS('2011-03-15')),
PARTITION p20110331 VALUES LESS THAN (TO_DAYS('2011-03-31')),
PARTITION p20110415 VALUES LESS THAN (TO_DAYS('2011-04-15')),
PARTITION p20110430 VALUES LESS THAN (TO_DAYS('2011-04-30'))
); 
分区维护存储过程：/* 每隔15天执行一次
/* 程序功能：循环使用分区，每半个月一个分区，保留6个月的数据
 时间：2010-11-09 */
 drop procedure if exists Set_Partition;
 create procedure Set_Partition()
 begin
/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    declare exit handler for sqlexception rollback;
    start TRANSACTION;
 
/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    select REPLACE(partition_name,'p','') into @P12_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='mydb_1' and table_name='terminal_parameter' order by partition_ordinal_position DESC limit 1;/* 判断最大分区的时间段，如果是前半个月的，那么根据情况需要加13,14,15,16天
   如果是后半个月的，那么直接加15天。 +0 是为了把日期都格式化成YYYYMMDD这样的格式*/
    IF (DAY(@P12_Name)<=15) THEN
       CASE day(LAST_DAY(@P12_name))
          WHEN 31 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 16 DAY))+0 ;
          WHEN 30 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 15 DAY))+0 ;
          WHEN 29 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 14 DAY))+0 ; 
          WHEN 28 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 13 DAY))+0 ; 
       END CASE;
    ELSE
       set @Max_date= date(DATE_ADD(@P12_Name+0, INTERVAL 15 DAY))+0;
    END IF;/* 修改表，在最大分区的后面增加一个分区，时间范围加半个月  */
    SET @s1=concat('ALTER TABLE terminal_parameter ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',date(@Max_date),''')))');
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;/* 取出最小的分区的名称，并删除掉 。
    注意：删除分区会同时删除分区内的数据，慎重  */
    select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='mydb_1' and table_name='terminal_parameter' order by partition_ordinal_position limit 1;
    SET @s=concat('ALTER TABLE terminal_parameter DROP PARTITION ',@P0_Name);
    PREPARE stmt1 FROM @s;    
    EXECUTE stmt1;            
    DEALLOCATE PREPARE stmt1;  /* 提交         */
    COMMIT ;
 end;
 
也许有人在工作中能用到。
实现这个功能或许有其他更好的办法，欢迎分享。

Answer 1

把技术任务的脚本也贴出来吧。 CREATE EVENT e_Set_Partition
          ON SCHEDULE
          EVERY 15 day STARTS '2011-04-30 23:59:59'  
          DO
          call Set_Partition();

Answer 2

看来你们公司很开放，很与时俱进，不错不错，加油。
对了zuoxingyu你们在平时维护数据库的时候用mk的工具多吗？你们用fio了吗？
问题有点多……我们现在领导的意思是还不敢用，Partition我们还没有都没有使用过，event还有procedure也都没有用现在唯一用新版本的两个特性，一个是plugin的压缩功能，一个就是新版本对auto_inc锁的改进，这两个效果都特别好。压缩直接从712G压缩到224G，现在监控的性能也没有下降（通过测试结果压缩特别适合改动量特别大的应用）；auto_inc锁的改进对于大批量插入效果特别理想。对于散表，我们是手工维护的，在外部脚本中进行建表操作。

Answer 3

不错的方法。不过一般上来说，这种自动化的工作是禁止的。特别是涉及到数据的部分。一但有问题会导致整个数据丢失。 半个月一次，这样的工作更应该由DBA来手工完成。首先，可以一次性创建10年的分区。 其次每年做一次历史分区的归档。

Answer 4

呵呵，新鲜的方法。不过一旦中途mysql或者机器down掉，估计会有恐怖的后果。

Answer 5

ALTER隐士提交，都没法回滚。