mysql ： range分区、range和key复合分区、分区裁剪

偏执的太偏执、 2022-05-22 10:49 233阅读 0赞

\--\#\#test01大小为52G，保存大概两天的数据  
\--\#\#每天数据大约25G左右，每天需创建一张备份表，插入新一天的数据，再要在这张表中删除前一天的数据  
\--\#\#现在采取分区裁剪的办法，避免多余的备份与删除  
\--\#\#现在分区分为两种：  
\--\#\#1.简单range分区（一层分区，每个区大约25G）  
\--\#\#2.range和key复合分区（两层分区，理想状态下均匀分布每个分区大概3G，但从实际历史表分区情况看，数据分布不均匀）  
\--\#\#  
  
  
\--\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\--\#\#\#\#\#\#\#\#\#\#\#\#\#range分区\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

--1.停止应用服务器，杀死所有连接进程
    --2.重命名大表
    ALTER TABLE test01 RENAME TO risk_result_terminal_real20180620;
    --3.创建分区表
     CREATE TABLE `test01` (
      `ResultID` VARCHAR(40) NOT NULL COMMENT '机场4字码',
      `NodeId` VARCHAR(40) NOT NULL COMMENT '终端风险因素ID',
      `RiskValue` DOUBLE(5,2) DEFAULT '1.00' COMMENT '终端风险因素值',
      `ComputeTime` DATETIME DEFAULT NULL COMMENT '计算时间',
      `Description` VARCHAR(500) DEFAULT NULL COMMENT '得分备注',
      `Sort` INT(11) DEFAULT NULL,
      `Dimension` VARCHAR(50) DEFAULT NULL COMMENT '评分维度',
      `ItemId` VARCHAR(40) DEFAULT NULL COMMENT '航班ID',
      KEY `r_resultid` (`ResultID`),
      KEY `r_itemId` (`ItemId`),
      KEY `r_risktime` (`ComputeTime`),
      KEY `r_NodeId` (`NodeId`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    /*!50500 PARTITION BY RANGE  COLUMNS(ComputeTime)
    (PARTITION p20180621 VALUES LESS THAN ('2018-06-22') ENGINE = InnoDB,
     PARTITION p20180622 VALUES LESS THAN ('2018-06-23') ENGINE = InnoDB,
     PARTITION p20180623 VALUES LESS THAN ('2018-06-24') ENGINE = InnoDB,
     PARTITION p20180624 VALUES LESS THAN ('2018-06-25') ENGINE = InnoDB,
     PARTITION p20180625 VALUES LESS THAN ('2018-06-26') ENGINE = InnoDB,
     PARTITION p20180626 VALUES LESS THAN ('2018-06-27') ENGINE = InnoDB,
     PARTITION p20180627 VALUES LESS THAN ('2018-06-28') ENGINE = InnoDB) */
    --#修改代码
    --分区增加
    ALTER TABLE test01 ADD PARTITION (PARTITION p20160524 VALUES LESS THAN ('2018-06-25'));
    --分区裁剪
    ALTER TABLE test01 DROP PARTITION p20180621;

\--\#创建存储过程，调用实现分区裁剪；

DELIMITER $$
    USE `mrvc`$$
    DROP PROCEDURE IF EXISTS `create_Partition`$$
    
    CREATE DEFINER=`root`@`%` PROCEDURE `create_Partition`()
    BEGIN
    /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    
    /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT REPLACE(partition_name,'p','') INTO @date_num FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='test01' ORDER BY partition_ordinal_position DESC LIMIT 1;
    SET @Next_date= DATE(DATE_ADD(@date_num+0, INTERVAL 1 DAY))+0;
    SET @Max_date= DATE(DATE_ADD(@date_num+0, INTERVAL 2 DAY))+0;
    
    /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE test01 ADD PARTITION (PARTITION p',@Next_date,' VALUES LESS THAN (''',DATE(@Max_date),'''))');
    
    /* 输出查看增加分区语句
    SELECT @s1;*/
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
    
     /* 取出最小的分区的名称，并删除掉 。
        注意：删除分区会同时删除分区内的数据，慎重 */
    SELECT partition_name INTO @first_num FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='test01' ORDER BY partition_ordinal_position LIMIT 1;
    SET @s=CONCAT('ALTER TABLE test01 DROP PARTITION ',@first_num);
    /* 输出查看增加分区语句
    SELECT @s;*/
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    COMMIT;
    END$$
    
    --#调用方法#
    --方法一
    --###应用程序调用存储过程
     CALL mrvc.`create_Partition`;  
     
    --方法二
    --###mysql定时事件执行存储过程
    DELIMITER $$
    CREATE EVENT Partition_risk_event  
              ON SCHEDULE  
              EVERY 1 day STARTS '2016-05-22 02:00:00'   
              DO  
          BEGIN  
    
              CALL mrvc.`create_Partition`;  
            
    END $$

\--\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\--\#\#\#\#\#\#\#\#\#\#\#\#\#range和key构成复合分区\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

--1.停止应用服务器，杀死所有连接进程
    --2.重命名大表
    ALTER TABLE test01 RENAME TO risk_result_terminal_real20180620;
    --3.创建分区表
    SHOW CREATE TABLE test01;
    CREATE TABLE `test01` (
      `ResultID` VARCHAR(40) NOT NULL COMMENT '机场4字码',
      `NodeId` VARCHAR(40) NOT NULL COMMENT '终端风险因素ID',
      `RiskValue` DOUBLE(5,2) DEFAULT '1.00' COMMENT '终端风险因素值',
      `ComputeTime` DATETIME DEFAULT NULL COMMENT '计算时间',
      `Description` VARCHAR(500) DEFAULT NULL COMMENT '得分备注',
      `Sort` INT(11) DEFAULT NULL,
      `Dimension` VARCHAR(50) DEFAULT NULL COMMENT '评分维度',
      `ItemId` VARCHAR(40) DEFAULT NULL COMMENT '航班ID',
      KEY `r_resultid` (`ResultID`),
      KEY `r_itemId` (`ItemId`),
      KEY `r_risktime` (`ComputeTime`),
      KEY `r_NodeId` (`NodeId`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    /*!50500 PARTITION BY RANGE  COLUMNS(ComputeTime)
    SUBPARTITION BY LINEAR KEY (ItemId)
    (PARTITION p20180621 VALUES LESS THAN ('2018-06-22')
     (SUBPARTITION p201806210 ENGINE = InnoDB,
      SUBPARTITION p201806211 ENGINE = InnoDB,
      SUBPARTITION p201806212 ENGINE = InnoDB,
      SUBPARTITION p201806213 ENGINE = InnoDB,
      SUBPARTITION p201806214 ENGINE = InnoDB,
      SUBPARTITION p201806215 ENGINE = InnoDB,
      SUBPARTITION p201806216 ENGINE = InnoDB,
      SUBPARTITION p201806217 ENGINE = InnoDB,
      SUBPARTITION p201806218 ENGINE = InnoDB,
      SUBPARTITION p201806219 ENGINE = InnoDB),
     PARTITION p20180622 VALUES LESS THAN ('2018-06-23')
     (SUBPARTITION p201806220 ENGINE = InnoDB,
      SUBPARTITION p201806221 ENGINE = InnoDB,
      SUBPARTITION p201806222 ENGINE = InnoDB,
      SUBPARTITION p201806223 ENGINE = InnoDB,
      SUBPARTITION p201806224 ENGINE = InnoDB,
      SUBPARTITION p201806225 ENGINE = InnoDB,
      SUBPARTITION p201806226 ENGINE = InnoDB,
      SUBPARTITION p201806227 ENGINE = InnoDB,
      SUBPARTITION p201806228 ENGINE = InnoDB,
      SUBPARTITION p201806229 ENGINE = InnoDB),
     PARTITION p20180623 VALUES LESS THAN ('2018-06-24')
     (SUBPARTITION p201806230 ENGINE = InnoDB,
      SUBPARTITION p201806231 ENGINE = InnoDB,
      SUBPARTITION p201806232 ENGINE = InnoDB,
      SUBPARTITION p201806233 ENGINE = InnoDB,
      SUBPARTITION p201806234 ENGINE = InnoDB,
      SUBPARTITION p201806235 ENGINE = InnoDB,
      SUBPARTITION p201806236 ENGINE = InnoDB,
      SUBPARTITION p201806237 ENGINE = InnoDB,
      SUBPARTITION p201806238 ENGINE = InnoDB,
      SUBPARTITION p201806239 ENGINE = InnoDB),
     PARTITION p20180624 VALUES LESS THAN ('2018-06-25')
     (SUBPARTITION p201806240 ENGINE = InnoDB,
      SUBPARTITION p201806241 ENGINE = InnoDB,
      SUBPARTITION p201806242 ENGINE = InnoDB,
      SUBPARTITION p201806243 ENGINE = InnoDB,
      SUBPARTITION p201806244 ENGINE = InnoDB,
      SUBPARTITION p201806245 ENGINE = InnoDB,
      SUBPARTITION p201806246 ENGINE = InnoDB,
      SUBPARTITION p201806247 ENGINE = InnoDB,
      SUBPARTITION p201806248 ENGINE = InnoDB,
      SUBPARTITION p201806249 ENGINE = InnoDB),
     PARTITION p20180625 VALUES LESS THAN ('2018-06-26')
     (SUBPARTITION p201806250 ENGINE = InnoDB,
      SUBPARTITION p201806251 ENGINE = InnoDB,
      SUBPARTITION p201806252 ENGINE = InnoDB,
      SUBPARTITION p201806253 ENGINE = InnoDB,
      SUBPARTITION p201806254 ENGINE = InnoDB,
      SUBPARTITION p201806255 ENGINE = InnoDB,
      SUBPARTITION p201806256 ENGINE = InnoDB,
      SUBPARTITION p201806257 ENGINE = InnoDB,
      SUBPARTITION p201806258 ENGINE = InnoDB,
      SUBPARTITION p201806259 ENGINE = InnoDB),
     PARTITION p20180626 VALUES LESS THAN ('2018-06-27')
     (SUBPARTITION p201806260 ENGINE = InnoDB,
      SUBPARTITION p201806261 ENGINE = InnoDB,
      SUBPARTITION p201806262 ENGINE = InnoDB,
      SUBPARTITION p201806263 ENGINE = InnoDB,
      SUBPARTITION p201806264 ENGINE = InnoDB,
      SUBPARTITION p201806265 ENGINE = InnoDB,
      SUBPARTITION p201806266 ENGINE = InnoDB,
      SUBPARTITION p201806267 ENGINE = InnoDB,
      SUBPARTITION p201806268 ENGINE = InnoDB,
      SUBPARTITION p201806269 ENGINE = InnoDB),
     PARTITION p20180627 VALUES LESS THAN ('2018-06-28')
     (SUBPARTITION p201806270 ENGINE = InnoDB,
      SUBPARTITION p201806271 ENGINE = InnoDB,
      SUBPARTITION p201806272 ENGINE = InnoDB,
      SUBPARTITION p201806273 ENGINE = InnoDB,
      SUBPARTITION p201806274 ENGINE = InnoDB,
      SUBPARTITION p201806275 ENGINE = InnoDB,
      SUBPARTITION p201806276 ENGINE = InnoDB,
      SUBPARTITION p201806277 ENGINE = InnoDB,
      SUBPARTITION p201806278 ENGINE = InnoDB,
      SUBPARTITION p201806279 ENGINE = InnoDB)) */
     
    --#修改代码
    --分区增加
    ALTER TABLE test01 ADD PARTITION (
     PARTITION p20180624 VALUES LESS THAN ('2018-06-25')
     (SUBPARTITION p201806240 ENGINE = INNODB,
      SUBPARTITION p201806241 ENGINE = INNODB,
      SUBPARTITION p201806242 ENGINE = INNODB,
      SUBPARTITION p201806243 ENGINE = INNODB,
      SUBPARTITION p201806244 ENGINE = INNODB,
      SUBPARTITION p201806245 ENGINE = INNODB,
      SUBPARTITION p201806246 ENGINE = INNODB,
      SUBPARTITION p201806247 ENGINE = INNODB,
      SUBPARTITION p201806248 ENGINE = INNODB,
      SUBPARTITION p201806249 ENGINE = INNODB));
    --分区裁剪
    ALTER TABLE test01 DROP PARTITION p20180621;

\--\#创建存储过程，调用实现分区裁剪；

DELIMITER $$
    USE `mrvc`$$
    DROP PROCEDURE IF EXISTS `create_Partition`$$
    
    CREATE DEFINER=`root`@`%` PROCEDURE `create_Partition`()
    BEGIN
    /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT REPLACE(partition_name,'p','') INTO @date_num FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='test01' ORDER BY partition_ordinal_position DESC LIMIT 1;
    SET @Next_date= DATE(DATE_ADD(@date_num+0, INTERVAL 1 DAY))+0;
    SET @Max_date= DATE(DATE_ADD(@date_num+0, INTERVAL 2 DAY))+0;
    /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE test01 ADD PARTITION (PARTITION p',@Next_date,' VALUES LESS THAN (''',DATE(@Max_date),''')
     (SUBPARTITION p',@Next_date,'0 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'1 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'2 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'3 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'4 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'5 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'6 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'7 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'8 ENGINE = INNODB,
      SUBPARTITION p',@Next_date,'9 ENGINE = INNODB))');
    /* 输出查看增加分区语句
    SELECT @s1;*/
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
     /* 取出最小的分区的名称，并删除掉 。
        注意：删除分区会同时删除分区内的数据，慎重 */
    SELECT partition_name INTO @first_num FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='test01' ORDER BY partition_ordinal_position LIMIT 1;
    SET @s=CONCAT('ALTER TABLE test01 DROP PARTITION ',@first_num);
    /* 输出查看增加分区语句
    SELECT @s;*/
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    COMMIT;
    END$$
    
    --#调用方法#
    --方法一
    --###应用程序调用存储过程
     CALL mrvc.`create_Partition`;  
     
    --方法二
    --###mysql定时事件执行存储过程
    DELIMITER $$
    CREATE EVENT Partition_risk_event  
              ON SCHEDULE  
              EVERY 1 day STARTS '2016-05-22 02:00:00'   
              DO  
          BEGIN  
    
              CALL mrvc.`create_Partition`;  
            
    END $$

--eg：  
    EXPLAIN
    SELECT * FROM test01
    WHERE ComputeTime = '2018-06-21 15:32:54'
    AND  ItemId = '16136312'