MySQL DBA运维之自动化分区管理

MySQL · ruyi · 于 2年前发布 · 3226 次阅读

现在,分区在互联网MySQL应用中非常广泛,尤其是对于日志表,报表相关的分析表,或者写入量很大但是查询很少的大表,最好建立分区表,这样既可以提高一些查询请求,又便于管理和维护。这里我不讲分区的类型和区别等基础知识,这些大家看官方文档就可以了。这次我向大家分享的是分区表的管理维护相关知识,该维护方式是通过一个存储过程实现的,可以按照设定规则提前创建好分区表,删除历史分区数据。通常情况下,我们在分区时一般基于范围分区的情况比较多,通常有按天、按周、按月几种情况:

首先我们以按天分区为例,创建一个按天的分区表并实现自动分区管理。分区建立几个就可以了,其他的分区后面会自动创建:

CREATE TABLE `rpt_dzd_track_src_keyword_2013` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `COUNT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `MERCHANT_ID` decimal(18,0) NOT NULL,
  `BOUNCE_UV` decimal(12,0) DEFAULT NULL,
  `MC_SITE` decimal(8,0) NOT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`,`COUNT_DATE`),
  KEY `index_rdtsk2013_c_d` (`COUNT_DATE`) USING BTREE,
  KEY `index_rdtsk2013_m_i` (`MERCHANT_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (UNIX_TIMESTAMP(`COUNT_DATE`))
(PARTITION p20130101 VALUES LESS THAN(UNIX_TIMESTAMP('2013-01-02 00:00:00')) ENGINE = InnoDB,
 PARTITION p20130102 VALUES LESS THAN(UNIX_TIMESTAMP('2013-01-03 00:00:00')) ENGINE = InnoDB,
 PARTITION p20130103 VALUES LESS THAN(UNIX_TIMESTAMP('2013-01-04 00:00:00')) ENGINE = InnoDB,
 PARTITION p20130104 VALUES LESS THAN(UNIX_TIMESTAMP('2013-01-05 00:00:00')) ENGINE = InnoDB
);

其次创建自动化分区的存储过程:

mysql> delimiter $$
mysql> CREATE DEFINER = `root`@`%` PROCEDURE `prc_mantiance_partition`(IN tablename VARCHAR(50),IN dbname VARCHAR(50),IN add_day INT ,IN expire_day INT,IN add_cycle INT)
 
BEGIN
DECLARE max_partition DATETIME DEFAULT NULL;
DECLARE v_sql VARCHAR(500);
         SET @tmp_table = tablename;
         SET @tmp_db = dbname;
         ## add partiontion
                   SET v_sql= CONCAT('SELECT MAX(DATE_FORMAT(FROM_UNIXTIME(PARTITION_DESCRIPTION),',"'",'%Y-%m-%d 00:00:00',"'",')) into @max_partition FROM information_schema.PARTITIONS WHERE TABLE_NAME= ? AND TABLE_SCHEMA=?');
                   SET @v_sql=v_sql;
                   PREPARE stmt FROM @v_sql; 
                   EXECUTE stmt USING @tmp_table,@tmp_db;                          
                   DEALLOCATE PREPARE stmt;
                   SET max_partition=@max_partition;
        
                   WHILE DATEDIFF( max_partition, NOW() ) < add_day  DO     
                           
                      IF add_cycle < 7 THEN
                                           
                            SET max_partition=DATE_ADD(max_partition, INTERVAL add_cycle DAY);
                            SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 DAY),'%Y%m%d'),' VALUES LESS THAN ( UNIX_TIMESTAMP( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');     
                      ELSEIF MOD(add_cycle,7) = 0 THEN
                     
                         SET max_partition=DATE_ADD(max_partition, INTERVAL add_cycle/7 WEEK);
                            SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 DAY),'%Y%m%d'),' VALUES LESS THAN ( UNIX_TIMESTAMP( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');     
                      ELSE
                    
                           SET max_partition=DATE_ADD(max_partition, INTERVAL 1 MONTH);         
                            SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 MONTH),'%Y%m'),' VALUES LESS THAN ( UNIX_TIMESTAMP( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');
                      END IF;
                     
                            SET @v_sql=v_sql; 
                            PREPARE stmt FROM @v_sql; 
                            EXECUTE stmt ;     
                            DEALLOCATE PREPARE stmt;
                           
                   END WHILE;
                  
         ##  drop the oldest partition
             IF expire_day <> 0
             THEN
                            SET v_sql= CONCAT('SELECT MIN(DATE_FORMAT(FROM_UNIXTIME(PARTITION_DESCRIPTION),',"'",'%Y-%m-%d',"'",')),PARTITION_NAME INTO @min_partition,@min_partition_name FROM information_schema.PARTITIONS WHERE TABLE_NAME= ? AND TABLE_SCHEMA= ?');                    
                           
                            SET @v_sql=v_sql;
                            PREPARE stmt FROM @v_sql; 
                            EXECUTE stmt USING @tmp_table,@tmp_db;                          
                            DEALLOCATE PREPARE stmt;         
        
                      WHILE DATEDIFF( NOW(), @min_partition ) > expire_day DO                
                           
                               SET v_sql= CONCAT('alter table ',@tmp_db,'.',@tmp_table,' DROP PARTITION ',@min_partition_name);
                                               
                               SET @v_sql=v_sql; 
                               PREPARE stmt FROM @v_sql; 
                               EXECUTE stmt;     
                               DEALLOCATE PREPARE stmt;
                       
                               SELECT MIN(DATE_FORMAT(FROM_UNIXTIME(PARTITION_DESCRIPTION),'%Y-%m-%d')),PARTITION_NAME INTO @min_partition,@min_partition_name
                               FROM information_schema.PARTITIONS
                               WHERE TABLE_NAME=@tmp_table AND TABLE_SCHEMA=@tmp_db;
        
                      END WHILE; 
            END IF;
    END;
$$
mysql> delimiter ;

自动分区使用示例,存储过程5个参数分别表示:(数据表名,数据库名,提前创建N天的分区表、历史数据保留天数、分区周期:1表示按天分区 7表示按周分区 30表示按月分区 这里的周期要和前面所建分区表周期一致
手动执行下面存储过程命令,就可以帮你自动创建好后面的分区啦。表示按天分区,自动提前创建10天的分区,分区数据保留1年

call `prc_mantiance_partition`('rpt_dzd_track_src_keyword_2013','dzd_rpt',10 ,365,1);

将存储过程部署成计划任务实现自动分区

[mysql@db-3-29 sh]$ crontab -l
1 0 * * * sh /data/mysql/sh/auto_management_table_partitions.sh > /tmp/crontab.log  2>&1
[mysql@db-3-29 sh]$ cat /data/mysql/sh/auto_management_table_partitions.sh
#!/bin/sh
/data/mysql/bin/mysql -u root -p123456 --socket=/data/mysql/data/mysql.sock dzd_rpt -e "call prc_mantiance_partition('rpt_dzd_track_src_keyword_2013','dzd_rpt',10 ,365,1);"

如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

本帖已经被管理员设置为: 精华帖 !
共收到 4 条回复 MySQL 分区 分区管理
ruyi#12年前 0 个赞

适用于to_days函数分区表的自动分区

CREATE TABLE `T_PRODUCT_STAT_HIS` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `SITE_ID` int(11) NOT NULL,
  `PRODUCT_CODE` varchar(200) DEFAULT NULL,
  `PRODUCT_NAME` varchar(1000) NOT NULL,
  `PRODUCT_URL` varchar(1000) NOT NULL,
  `GENERATE_TIME` datetime NOT NULL,
  PRIMARY KEY (`ID`,`GENERATE_TIME`,`SITE_ID`),
  KEY `IDX_T_PRODUCT_STAT_HIS__SITE_ID` (`SITE_ID`),
  KEY `IDX_T_PRODUCT_STAT_HIS_GENERATE_TIME` (`GENERATE_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=1920541130 DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(GENERATE_TIME))
SUBPARTITION BY HASH (SITE_ID)
SUBPARTITIONS 4
(
 PARTITION p20150111 VALUES LESS THAN (to_days('2015-01-12')) ENGINE = InnoDB,
 PARTITION p20150118 VALUES LESS THAN (to_days('2015-01-19')) ENGINE = InnoDB,
 PARTITION p20150125 VALUES LESS THAN (to_days('2015-01-26')) ENGINE = InnoDB
);
//自动分区,适用于to_days函数
DROP PROCEDURE IF EXISTS `prc_mantiance_partition_days`;
CREATE DEFINER = `root`@`127.0.0.1` PROCEDURE `prc_mantiance_partition_days`(IN tablename VARCHAR(50),IN dbname VARCHAR(50),IN add_day INT ,IN expire_day INT,IN add_cycle INT)
BEGIN
DECLARE max_partition DATETIME DEFAULT NULL;
DECLARE v_sql VARCHAR(500);
 SET @tmp_table = tablename;
 SET @tmp_db = dbname;
 ## add partiontion
  SET v_sql= CONCAT('SELECT MAX(DATE_FORMAT(from_days(PARTITION_DESCRIPTION),',"'",'%Y-%m-%d 00:00:00',"'",')) into @max_partition FROM information_schema.PARTITIONS WHERE TABLE_NAME= ? AND TABLE_SCHEMA=?');
  SET @v_sql=v_sql;
  PREPARE stmt FROM @v_sql;  
  EXECUTE stmt USING @tmp_table,@tmp_db;  
  DEALLOCATE PREPARE stmt;
  SET max_partition=@max_partition;
 
  WHILE DATEDIFF( max_partition, NOW() ) < add_day  DO
   
     IF add_cycle < 7 THEN
       
   SET max_partition=DATE_ADD(max_partition, INTERVAL add_cycle DAY);
   SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 DAY),'%Y%m%d'),' VALUES LESS THAN ( to_days( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');
SELECT v_sql;
     ELSEIF MOD(add_cycle,7) = 0 THEN
     
      SET max_partition=DATE_ADD(max_partition, INTERVAL add_cycle/7 WEEK);
   SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 DAY),'%Y%m%d'),' VALUES LESS THAN ( to_days( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');
     ELSE
     
          SET max_partition=DATE_ADD(max_partition, INTERVAL 1 MONTH);  
   SET v_sql= CONCAT('ALTER TABLE ',@tmp_db,'.',@tmp_table ,' ADD PARTITION ( PARTITION p',DATE_FORMAT(DATE_SUB(max_partition,INTERVAL 1 MONTH),'%Y%m'),' VALUES LESS THAN ( to_days( ',"'",max_partition,"'",' ) ) ENGINE = INNODB);');
     END IF;
     
   SET @v_sql=v_sql;  
   PREPARE stmt FROM @v_sql;  
   EXECUTE stmt ;      
   DEALLOCATE PREPARE stmt;
   
  END WHILE;
 
 ##  drop the oldest partition
     IF expire_day <> 0
     THEN
   SET v_sql= CONCAT('SELECT MIN(DATE_FORMAT(from_days(PARTITION_DESCRIPTION),',"'",'%Y-%m-%d',"'",')),PARTITION_NAME INTO @min_partition,@min_partition_name FROM information_schema.PARTITIONS WHERE TABLE_NAME= ? AND TABLE_SCHEMA= ?');
   SET @v_sql=v_sql;
   PREPARE stmt FROM @v_sql;  
   EXECUTE stmt USING @tmp_table,@tmp_db;  
   DEALLOCATE PREPARE stmt;
 
     WHILE DATEDIFF( NOW(), @min_partition ) > expire_day DO
       
      SET v_sql= CONCAT('alter table ',@tmp_db,'.',@tmp_table,' DROP PARTITION ',@min_partition_name);
           
      SET @v_sql=v_sql;  
      PREPARE stmt FROM @v_sql;  
      EXECUTE stmt;      
      DEALLOCATE PREPARE stmt;
       
      SELECT MIN(DATE_FORMAT(from_days(PARTITION_DESCRIPTION),'%Y-%m-%d')),PARTITION_NAME INTO @min_partition,@min_partition_name
      FROM information_schema.PARTITIONS
      WHERE TABLE_NAME=@tmp_table AND TABLE_SCHEMA=@tmp_db;
 
     END WHILE;  
            END IF;
    END;

自动增加分区计划任务

[mysql@db-3-5 sh]$ crontab -l 
11 00 * * * (cd /data/mysql/sh ; bash auto_management_table_partitions.sh >>auto_management_table_partitions.log 2>&1)

脚本内容如下

#!/bin/bash
/data/mysql/bin/mysql -vvv -uroot -p123456 pis -e "call prc_mantiance_partition_days('T_PRODUCT_STAT_HIS','pis',62,365,7)"
ruyi#22年前 0 个赞

手动增加分区的代码实例

ALTER TABLE paylog ADD PARTITION
(
PARTITION p201501 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN (UNIX_TIMESTAMP('2015-04-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN (UNIX_TIMESTAMP('2015-05-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN (UNIX_TIMESTAMP('2015-06-01 00:00:00')) ENGINE = InnoDB
);
 
ALTER TABLE  PRODUCT_PRICE_HIS ADD PARTITION
(
PARTITION p20150223  VALUES  LESS THAN ( UNIX_TIMESTAMP('2015-02-24 00:00:00')),
PARTITION p20150302  VALUES  LESS THAN ( UNIX_TIMESTAMP('2015-03-03 00:00:00')),
PARTITION p20150309  VALUES  LESS THAN ( UNIX_TIMESTAMP('2015-03-10 00:00:00')),
PARTITION p20150316  VALUES  LESS THAN ( UNIX_TIMESTAMP('2015-03-17 00:00:00'))
);
 
ALTER TABLE central_logs ADD PARTITION
(
PARTITION p1208 VALUES LESS THAN (to_days('2012-09-01')) ENGINE = MyISAM,
PARTITION p1209 VALUES LESS THAN (to_days('2012-10-01')) ENGINE = MyISAM,
PARTITION p1210 VALUES LESS THAN (to_days('2012-11-01')) ENGINE = MyISAM,
PARTITION p1211 VALUES LESS THAN (to_days('2012-12-01')) ENGINE = MyISAM,
PARTITION p1212 VALUES LESS THAN (to_days('2013-01-01')) ENGINE = MyISAM
);

 ```
ruyi#32年前 0 个赞

手动删除分区实例

mysql> alter table cart_log drop partition p20130626;
ruyi#42年前 0 个赞

先占个位置

回复本帖 (需要登录)