mysql分区 alter_mysql分区表设计（一）

数据库中有多张表要做分区表(按日期分区)，但是这些表用于分区的日期字段记录形式并不统一，有如下三种类型：PARTITION par_20151225 VALUES LESS THAN (736323) ENGINE = InnoDB

PARTITION par_20151010 VALUES LESS THAN (1444492800) ENGINE = InnoDB

PARTITION par_20151006 VALUES LESS THAN (1452480) ENGINE = InnoDB

首先创建一个元数据信息表，用于记录要分区的表以及分区类型，如下：mysql> select * from op_tb_partition;

+-------------------------------+----------+

| tb_name                       | timetype |

+-------------------------------+----------+

| NL_APP_ACTION_ERROR_TRACE     | unixtime |

| NL_APP_ACTION_SQL_TRACE       | unixtime |

| NL_APP_ACTION_TRACE           | unixtime |

| NL_MOB_APP_CRASH_DATA         | usertime |

| NL_MOB_APP_INTERACTION_TRACE  | unixtime |

| NL_NET_MOB_PAGE_DATA          | unixtime |

| NL_NET_MOB_PING_DATA          | unixtime |

| NL_NET_PAGE_DATA              | unixtime |

| NL_NET_PING_DATA              | unixtime |

| NL_MOB_APP_ERROR_TRACE        | datetime |

| NL_MOB_APP_SOCKET_ERROR_TRACE | datetime |

+-------------------------------+----------+

11 rows in set (0.00 sec)

此外，还需创建一张日志表，用于记录存储过程执行的详细信息，如下：mysql> desc change_partition_log;

+------------+---------------+------+-----+-------------------+-----------------------------+

| Field      | Type          | Null | Key | Default           | Extra                       |

+------------+---------------+------+-----+-------------------+-----------------------------+

| tm_base    | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| op_type    | varchar(20)   | YES  |     | NULL              |                             |

| change_sql | varchar(2000) | YES  |     | NULL              |                             |

+------------+---------------+------+-----+-------------------+-----------------------------+

3 rows in set (0.00 sec)

为了是维护工作变的简单一些，希望能用一个存储过程来实现所有类型表的分区维护，因此编写了如下的存储过程：DELIMITER $$

DROP PROCEDURE IF EXISTS lens_mobapp_trace.add_partition_original;

CREATE PROCEDURE `add_partition_original`()

BEGIN

DECLARE table_name varchar(255) DEFAULT "";

DECLARE tmp_sql varchar(255) DEFAULT "";

DECLARE max_day_will int DEFAULT 0;

DECLARE max_day_now int DEFAULT 0;

DECLARE difference int DEFAULT 0;

DECLARE i int DEFAULT 1;

DECLARE tmp_day_value int DEFAULT 0;

DECLARE tmp_day int DEFAULT 1;

DECLARE alter_sql varchar(255) DEFAULT "";

DECLARE par_name varchar(255) DEFAULT "";

DECLARE done int DEFAULT - 1;

DECLARE myCursor CURSOR FOR

SELECT

tb_name

FROM op_tb_partition;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @tmp_sql = CONCAT("select to_days(date_add(now(),interval 6 day)) into @max_day_will");

PREPARE stmt FROM @tmp_sql;

EXECUTE stmt;

OPEN myCursor;

myLoop:

LOOP

FETCH myCursor INTO table_name;

IF done = 1 THEN

LEAVE myLoop;

END IF;

set @tmp_sql = concat("SELECT timetype FROM op_tb_partition WHERE tb_name = '", table_name, "' into @timetype");

prepare stmt from @tmp_sql;

execute stmt;

case

when @timetype="datetime" then

set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");

prepare stmt from @tmp_sql;

execute stmt;

while i <= @difference do

set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select to_days(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");

prepare stmt from @tmp_sql;

execute stmt;

set @par_name = concat("par_",@tmp_day);

SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");

insert into change_partition_log values( now(), "add_trace", @alter_sql);

prepare stmt from @alter_sql;

execute stmt;

set i = i+1;

end while;

set i = 1;

when @timetype="usertime" then

set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");

prepare stmt from @tmp_sql;

execute stmt;

while i <= @difference do

set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select nl_to_timestamp(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");

prepare stmt from @tmp_sql;

execute stmt;

set @par_name = concat("par_",@tmp_day);

SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");

insert into change_partition_log values( now(),"add_trace" , @alter_sql);

prepare stmt from @alter_sql;

execute stmt;

set i = i+1;

end while;

set i = 1;

when @timetype="unixtime" then

set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");

prepare stmt from @tmp_sql;

execute stmt;

while i <= @difference do

set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");

prepare stmt from @tmp_sql;

execute stmt;

set @tmp_sql = concat("select unix_timestamp(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");

prepare stmt from @tmp_sql;

execute stmt;

set @par_name = concat("par_",@tmp_day);

SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");

insert into change_partition_log values( now(),"add_trace" , @alter_sql);

prepare stmt from @alter_sql;

execute stmt;

set i = i+1;

end while;

set i = 1;

end case;

END LOOP myLoop;

CLOSE myCursor;

END$$

DELIMITER ;

由于业务需求，这些表的数据只需要保留3个月，再次编写删除分区的存储过程，如下：DELIMITER $$

DROP PROCEDURE IF EXISTS lens_mobapp_trace.del_partition_original; CREATE PROCEDURE `del_partition_original`()

BEGIN

declare table_name varchar(255) default 0;

declare tmp_sql varchar(255) default "";

declare max_par_now varchar(255) default "";

declare par_del_num varchar(255) default "";

declare par_del varchar(255) default "";

declare alter_sql varchar(255) default "";

declare i int default 0;

declare done int default -1;

declare myCursor cursor for select tb_name from op_tb_partition;

declare continue handler for not found set done = 1;

open myCursor;

myLoop:LOOP

fetch myCursor into table_name;

if done = 1 then

leave myLoop;

end if;

set @tmp_sql = concat("select count(*) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_par_now");

prepare stmt from @tmp_sql;

execute stmt;

set @par_del_num = @max_par_now-90-7;

while i 

set @tmp_sql = concat("select partition_name from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA ='lens_mobapp_trace' and table_name='",table_name,"' order by partition_name limit 1 into @par_del");

prepare stmt from @tmp_sql;

execute stmt;

set @alter_sql = concat("alter table ",table_name," drop partition ",@par_del);

prepare stmt from @alter_sql;

execute stmt;

insert into change_partition_log values(now(),"drop_trace", concat(i,"--",@par_del,"--",@alter_sql));

set i = i+1;

end while;

set i = 0;

end loop myLoop;

close myCursor;

END$$

DELIMITER ;

最后编写定期执行上述存储过程的调度event，如下：DELIMITER ;;

CREATE EVENT lens_mobapp_trace.`manager_partition_original` ON SCHEDULE EVERY 1 DAY STARTS '2013-12-24 00:30:00' ON COMPLETION PRESERVE ENABLE DO BEGIN

call add_partition_original;

call del_partition_original;

END ;;

DELIMITER ;