第一篇博客，记载马上要做的MYSQL分区工作

最新推荐文章于 2024-09-20 21:26:51 发布

ls959826728

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量279

点赞数

分类专栏： MYSQL 文章标签： mysql 分区

本文链接：https://blog.csdn.net/ls959826728/article/details/50802051

版权

MYSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

公司的几张亿级和千万级的大表日插入50W左右，为了方便查询，以时间字段作为分区规则，每天定一个分区，查询确实上升了好几个档次，但是分区总是要消耗完的，而且发现分区在查询每个月的最后一天和第一天的交界时，会带上第一个分区，而第一个分区是保存了去年9月以前的数据，是最大的分区，所以每月第一天公司的数据库有点堵塞，因为无法过滤第一个分区，现考虑以下两种方案：

1.更改分区规则，以mod（to_days(·time·)，62），取余数，分成63个有效分区，采用range分区，第一个分区less than （0）来做为空分区，解决上面的无法过滤第一个分区的情况，同时可以循环利用分区。在实验过程中发现，可能是因为分区规则采用了两个函数的原因，explain partitions 发现以前的sql无法过滤分区。亟待解决。

2.还是采用以前的分区规则，to_days()，每天一个分区，先做到2017年，365个分区，然后编写存储过程，实现每日清理第一个分区然后在最后的分区后面添加一个新分区，然后用事件控制每日跑一次存储

以上两种方法都要附加数据迁移的，因为热点数据只有3个月，可以新建一个数据库，将3个月以前的日志文件存放进这个数据库，将迁移的过程也加进上面那个存储，每日执行一遍，将90天以前历史数据迁移进专门的数据库。这样表的大小就控制住了。存储如下：

DELIMITER $$

USE `AudioVideo`$$

DROP PROCEDURE IF EXISTS `data_relay_CoinLog`$$

CREATE DEFINER=`root`@`%` PROCEDURE `data_relay_CoinLog`()
out_proc:BEGIN

     DECLARE P_NAME VARCHAR(255) DEFAULT 0;
     DECLARE P_DESCRIPTION INT(11) DEFAULT 0;
     DECLARE i INT DEFAULT 0;
     DECLARE min_partition_number VARCHAR(255) DEFAULT 0;
     DECLARE max_partition_description INT(11) DEFAULT 0;
     DECLARE t_error INT DEFAULT 0;
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=-1;

WHILE i<=6 DO -- 转移一个星期的分区数据
     SELECT PARTITION_NAME INTO min_partition_number FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'AudioVideo' AND TABLE_NAME = 'T_CoinLog_test' LIMIT 1,1 ;   -- 获取非首分区最小分区名

     SELECT MAX(PARTITION_DESCRIPTION) INTO max_partition_description FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'AudioVideo' AND TABLE_NAME = 'T_CoinLog_test' ;   -- 获取最大分区名


       SET @S1=CONCAT('insert into `AudioVideo_old`.`T_CoinLog_test` select * from `AudioVideo`.`T_CoinLog_test` PARTITION (',min_partition_number,')');
       PREPARE stmt1 FROM @S1;
       EXECUTE stmt1;

       SET @S2= CONCAT('ALTER TABLE T_CoinLog_test DROP PARTITION ',min_partition_number);
       PREPARE stmt2 FROM @S2;
       EXECUTE stmt2;

           SET P_DESCRIPTION = max_partition_description+1;
           SET P_NAME = REPLACE(FROM_DAYS(P_DESCRIPTION), '-', '');
           SET @S3=CONCAT('ALTER TABLE T_CoinLog_test ADD PARTITION (PARTITION P',P_NAME,' VALUES LESS THAN (',P_DESCRIPTION,'))');

           PREPARE stmt3 FROM @S3;
           EXECUTE stmt3;

      SET i=i+1;

END WHILE;

      IF t_error=-1 THEN
         ROLLBACK;
         SELECT -1 AS FlagCode , '数据转移出错，回滚' AS ErrorDescribe;

      ELSE
         COMMIT;
         SELECT 1 AS FlagCode , '数据转移成功' AS ErrorDescribe;

      END IF;

END$$

DELIMITER ;