Mysql分区

1.查询表test_table当前的分区情况

	SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,

TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM information_schema.PARTITIONS

WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='test_table';

 

2.初始化表test_table分区，初始化时设置三个分区

/*初始化分区*/
DELIMITER $$
USE `test_data`$$
DROP PROCEDURE IF EXISTS `init_partition_test_table`$$
CREATE DEFINER=`ims`@`%` PROCEDURE `init_partition_test_table`()
  BEGIN
    DECLARE DAYS_ENDTIME VARCHAR(16);
    DECLARE PARTITIONNAME VARCHAR(16);
		DECLARE DAYS_ENDTIME2 VARCHAR(16);
    DECLARE PARTITIONNAME2 VARCHAR(16);
		DECLARE DAYS_ENDTIME3 VARCHAR(16);
    DECLARE PARTITIONNAME3 VARCHAR(16);
    SET PARTITIONNAME = DATE_FORMAT(curdate(),"p%Y%m%d");
		SET PARTITIONNAME2 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL -1 DAY),"p%Y%m%d");
		SET PARTITIONNAME3 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL -2 DAY),"p%Y%m%d");
    SET DAYS_ENDTIME = DATE_FORMAT(curdate(),"%Y-%m-%d");
		SET DAYS_ENDTIME2 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL -1 DAY),"%Y-%m-%d");
		SET DAYS_ENDTIME3 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL -2 DAY),"%Y-%m-%d");
    SET @SQL = CONCAT('ALTER TABLE `test_table`',
                        ' PARTITION BY RANGE (TO_DAYS(collect_time))
    (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (TO_DAYS(''', DAYS_ENDTIME, ''')),',
		'PARTITION ', PARTITIONNAME2, ' VALUES LESS THAN (TO_DAYS(''', DAYS_ENDTIME2, ''')),',
		'PARTITION ', PARTITIONNAME3, ' VALUES LESS THAN (TO_DAYS(''', DAYS_ENDTIME3, ''')))');
    PREPARE STMT FROM @SQL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  END$$
DELIMITER ;	

CALL init_partition_test_table();

3.创建新分区

/*创建新分区*/ 
DELIMITER $$

USE `test_data`$$

DROP PROCEDURE IF EXISTS `create_partition_test_table`$$

CREATE DEFINER=`ims`@`%` PROCEDURE `create_partition_test_table`()
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_name='test_table' ORDER BY partition_ordinal_position DESC LIMIT 1;
     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE test_table ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
    /* 输出查看增加分区语句*/
    SELECT @s1;
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
/* 取出最小的分区的名称，并删除掉 。
    注意：删除分区会同时删除分区内的数据，慎重 */
   /* select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
    where table_name='test_table' order by partition_ordinal_position limit 1;
    SET @s=concat('ALTER TABLE test_table DROP PARTITION ',@P0_Name);
	  SELECT @s;
		PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1; */
/* 提交 */
    COMMIT ;
 END$$

DELIMITER ;

4.定时每天新增一个分区

/*定时事件 每天创建一个分区*/
DELIMITER ||
DROP EVENT IF EXISTS `create_partition_test_table_event`||

CREATE EVENT create_partition_test_table_event
          ON SCHEDULE
          EVERY 1 day STARTS NOW()
          DO
      BEGIN

          CALL test_data.`create_partition_test_table`;

 END ||
DELIMITER ;

5.定时每年初始化一次分区，避免分区数爆满

/*定时事件 每两年初始化一次分区，避免分区数用满*/
DELIMITER ||
DROP EVENT IF EXISTS `init_partition_test_table_event`||

CREATE EVENT init_partition_test_table_event
          ON SCHEDULE
          EVERY 730 day STARTS NOW()
          DO
      BEGIN

          CALL test_data.`init_partition_test_table`;

 END ||
DELIMITER ;

6.注意：

        1.单表的最大分区数为1024个；

        2.如果分区数不存在，插入数据时会抛异常，可设置默认最大分区MAXVALUE

        3.可通过explain查看执行sql时分区的命中情况