mysql表的分区

data_longtime表数据量太大，进行分区

因为分区所用的字段是毫秒级别的bigint类型，所以这里是把分区的依据通过DATE_FORMAT转成秒级再*1000,会有些许的误差

-- 初始化表的分区结构，新建一个当天的分区，所有的数据都会属于当前分区
 ALTER TABLE `data_longtime` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id`,`uploadTime` );
 
 DELIMITER $$
 DROP PROCEDURE IF EXISTS `create_partition_today`$$
 CREATE PROCEDURE `create_partition_today`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
   BEGIN

     DECLARE PARTITIONNAME VARCHAR(16);
		 DECLARE DAY_ENDTIME BIGINT;

     SET PARTITIONNAME = DATE_FORMAT(NOW(), 'p%Y%m%d');
		 SET DAY_ENDTIME = UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d'),' 23:59:59')) * 1000;

     SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                         ' PARTITION BY RANGE (uploadTime)
     (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAY_ENDTIME, '))');
     PREPARE STMT FROM @SQL;
		 EXECUTE STMT;
     DEALLOCATE PREPARE STMT;
   END$$
 DELIMITER ;
 
 CALL create_partition_today('datacenter','data_longtime');


-- 打开事件变量：SET GLOBAL event_scheduler = 'ON';
-- 重启后会被重置,如果需要永久修改,在my.cnf（mysql配置文件）中[mysqld]部分中添加下面内容，重启MYSQL
-- event_scheduler=ON
-- 定时器要执行的任务，创建明天的分区
SET GLOBAL event_scheduler = 'ON'; #打开定时器
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
  BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;

    DECLARE PARTITIONNAME VARCHAR(16);
		DECLARE DAY_ENDTIME BIGINT;

    SET PARTITIONNAME = DATE_FORMAT((NOW() + INTERVAL 1 DAY), 'p%Y%m%d');
		SET DAY_ENDTIME = UNIX_TIMESTAMP(CONCAT(DATE_FORMAT((NOW() + INTERVAL 1 DAY),'%Y-%c-%d'),' 23:59:59')) * 1000;

    SELECT COUNT(*)
    INTO ROWS_CNT
    FROM information_schema.`PARTITIONS`
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0
    THEN
      SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                        ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAY_ENDTIME, '))');
      PREPARE STMT FROM @SQL;
      EXECUTE STMT;
      DEALLOCATE PREPARE STMT;
    ELSE
      SELECT CONCAT("partition `", PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME,
                    "` already exists") AS result;
    END IF;
  END$$
DELIMITER ;

-- 定时器
DELIMITER ||  
DROP EVENT IF EXISTS `create_partition_every_day`||
CREATE EVENT create_partition_every_day  
          ON SCHEDULE  
          EVERY 1 DAY STARTS NOW()
          DO  
      BEGIN  
          CALL create_partition_by_day('datacenter','data_longtime');

 END ||  
DELIMITER ;

查询表的分区

select partition_name,subpartition_name,table_rows
from information_schema.partitions 
where table_name = 'data_longtime'

查询定时器

select * from  mysql.event