mysql自动分区

mysql自动分区.md

手动分区

对于需要定时分区的表，必须先进行一次手动分区。
对于新建的表,在建表语句后添加：

partition by range(partition_key)( #对于基于日期分期 推荐使用 to_days(pdate) to_days函数会将日期类型转化为int值提高效率
  partition p20190301 values less than (to_days('2019-03-02')), # 分区名字 一般习惯以p开头后面接日期
  partition p20190302 values less than (to_days('2019-03-03'),
);

对于已经存在的表创建分区：

Alter table mydb.my_table partition by range(to_days(pDate))(
  partition p20190301 values less than (to_days('2019-03-02')),
  partition p20190302 values less than (to_days('2019-03-03')),
);

对于对于已经存在的表增加分区：

Alter table mydb.my_table add partition(
  partition p20190301 values less than (to_days('2019-03-02')),
  partition p20190302 values less than (to_days('2019-03-03')),
);

删除分区：alter table my_table drop partiotion p20190302;

• 分区字段必须是联合主键以及联合唯一索引的一部分。
• 一旦创建了分区则最少会保留一个分区，如果需要取消分区需要drop整张表。

存储过程

#如果需要类似dblink的功能 需要在/etc/my.cnf 添加federated引擎 以开启远程映射功能。
DELIMITER //                            #改变分隔符为 // 实际运行中这里不能有注释，否则莫名的无法创建该存储过程
  use `mydb`//                          #使用的数据     
  drop procedure if exists autoPart//   #如果已存在该名字的存储过程则删除

  CREATE PROCEDURE autoPart(IN pDate varchar(16),IN nextDate date,IN pExpireDate varchar(16),IN expireDate date,IN tableName varchar(32),IN backTableName varchar(32)) #创建存储过程， IN代表是入参仅仅值传递，还有OUT,INOUT。
    BEGIN
      declare partCount int unsigned;  #申明一个无符号变量
      select count(*) into partCount from information_schema.partitions where table_schema='mydb' and table_name=tableName; #这是mysql的系统表用于查询已经创建的存储过程。
	  select partCount;	 

	  set @s1=concat('alter table ',tableName,' add partition(
        partition ',pDate,' values less than (to_days(''',nextDate,''')))');    #使用@仅仅是一种习惯,concat()函数用来拼接字符串,两个单引号代表对单引号的转义

      select @s1;              #输出s1
      prepare stmt from @s1;   #必须使用这种类似template的方法写，因为入参作为sql语句的参数会无法识别！
      execute stmt;            #执行sql语句
      deallocate prepare stmt; #释放stmt

      set @s1=concat('insert into ',backTableName,' select * from ',tableName,' where pDate=''',expireDate,'''');
	  select @s1;
	  prepare stmt from @s1;
      execute stmt;
      deallocate prepare stmt;

      set @s1=concat('alter table ',TableName,' drop partition ',pExpireDate);
      select @s1;
      prepare stmt from @s1;
      execute stmt;
      deallocate prepare stmt;

      END //
DELIMITER ;   #使分隔符变回分号

查看当前存在的存储过程：show procedure status where db='mydb' #设定了默认数据库可以省略where字句，注意单引号
查看指定存储过程：show create procedure autopart
删除指定存储过程：drop procedure autopart
调用指定存储过程：call autoPart('p20190327','2019-03-28','p20190311','20190311','my_table','my_table_bak')

定时事件

开启事件计划：set global event_scheduler=ON
设定事件：

delimiter //
Use `citdb`//
drop event if exists  partition_event//
Create event partition_event
  on schedule
    every 1 day starts '2019-03-08 00:00:00'
      do
        begin
          Set @pDate = concat('p',date_format(curdate(),'%Y%m%d')); #varchar(10) 新增分区名
          Set @nextDate = date_add(curdate(),interval 1 day); # date 下一日0点
          Set @pExpireDate =concat('p',date_format(date_sub(curdate(),interval 1 day),'%Y%m%d')); #varchar(10) 需要删除的分区名
          Set @expireDate = date_sub(curdate(),interval 1 day); #date 过期日期pDate
          call autoPart(@pDate,@nextDate,@pExpireDate,@expireDate,'cp_txn_info','cp_txn_info_bak');
        end //
delimiter ;  

查看存在的定时事件：show events
删除指定的定时事件：drop event partition_event