MySql自动分区

自动分区需要开启MySql中的事件调度器，可以通过如下命令查看是否开启了调度器

show variables like '%scheduler%';

如果没开启的话通过如下指令开启

SET GLOBAL event_scheduler = 1;

 

1、创建一个分区表

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    createTime DATETIME NOT NULL,
    PRIMARY KEY(id, createTime)
) ENGINE=Innodb
PARTITION BY RANGE (YEAR(createTime))
(
    PARTITION p_2010 VALUES LESS THAN (2011),#2011年以前一个分区
    PARTITION p_2011 VALUES LESS THAN (2012) #2011-2012一个分区
)

查看当前表分区信息：

   SELECT PARTITION_NAME,PARTITION_DESCRIPTION,PARTITION_METHOD,TABLE_ROWS,CREATE_TIME
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'sales' ORDER BY partition_description DESC LIMIT 1000;

新建一个存储过程用于给分区表新增分区

DROP PROCEDURE IF EXISTS create_Partition ;
CREATE PROCEDURE create_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50),IN partitionNamePrefix VARCHAR(50))
L_END:BEGIN
     DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT "";
     DECLARE P_NAME VARCHAR(255) DEFAULT "";      
     DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT "";  
     DECLARE i INT DEFAULT 1;
     DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT "";
     DECLARE PARTITION_COUNT INT DEFAULT 1; #一次性增加一个分区数

     SELECT IFNULL(PARTITION_NAME,"") INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS
      WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
     
     IF ISEXIST_PARTITION <=> "" THEN
       SELECT "Partition table not is exist" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;

     SELECT IFNULL(partition_description,"") INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS
      WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;

     IF MAX_PARTITION_DESCRIPTION <=> "" THEN
       SELECT "Partition table is error" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;
     
    SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');
    WHILE i <= PARTITION_COUNT DO
           SET P_DESCRIPTION = MAX_PARTITION_DESCRIPTION+i;
           SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
           SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION ',partitionNamePrefix,P_NAME,' VALUES LESS THAN (',P_DESCRIPTION,'))');
           SELECT @S;
           PREPARE stmt2 FROM @S;
           EXECUTE stmt2;
           DEALLOCATE PREPARE stmt2;
           SET i = i + 1 ;
    END WHILE;          
END L_END;

创建一个事件定时器用于定时创建分区

DELIMITER $$
CREATE EVENT auto_set_partitions
     ON SCHEDULE
     EVERY 5 MINUTE
     COMMENT 'auto set partitions for table '
   DO
      BEGIN
    CALL create_Partition('mysql','test','p');
      END $$

这样每过5分钟就会在sales分区表上新增一个分区