mysql 分区表_分享一份生产环境mysql数据库分区表改造方案

最新推荐文章于 2024-01-05 09:00:00 发布

weixin_39527911

最新推荐文章于 2024-01-05 09:00:00 发布

阅读量370

点赞数

文章标签： mysql 分区表

概述

简单分享下最近做的一个mysql数据库分区表改造方案，仅供参考。

思路：(假设在2020.7.21进行表分区改造)

没时间，就不画图说明了

1、创建与原始表一样结构的新表，新分区

2、往新表插入旧表在2020.7.20 00:00:00之前的数据

3、业务空闲时间段进行表切换

4、新表建索引、触发器等

5、数据补录(将原始表中超过2020.7.20 00:00:00的数据补录到新表)

一、创建新表及分区

注意分区键需在主键上，且不能为null

CREATE TABLE `t_att_dd_pushcard_info_range` (  `id` varchar(64) NOT NULL COMMENT 'ID',  `user_id` varchar(64) DEFAULT NULL COMMENT '员工ID',  `work_date` varchar(64) NOT NULL COMMENT '工作日',  `plan_id` varchar(64) DEFAULT NULL COMMENT '排班id',  `approve_id` varchar(64) DEFAULT NULL COMMENT '审批id，结果集中没有的话表示没有审批单',  ....  `patch_flag` varchar(1) DEFAULT NULL COMMENT '是否补卡 Y 是 N 否',  PRIMARY KEY (`id`,work_date),  KEY `idx_hwb1` (`user_id`,`check_type`,`work_date`) USING BTREE,  KEY `idx_work_date` (`work_date`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钉钉打卡详情表'/*!50500 PARTITION BY RANGE  COLUMNS(work_date)(PARTITION p201909 VALUES LESS THAN ('2019-10-01') ENGINE = InnoDB, PARTITION p201910 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB, PARTITION p201911 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB, PARTITION p201912 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB, PARTITION p202001 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB, PARTITION p202002 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB, PARTITION p202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB, PARTITION p202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB, PARTITION p202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB, PARTITION p202006 VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB, PARTITION p202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB, PARTITION p202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB, PARTITION p202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, PARTITION p202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB, PARTITION p202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB, PARTITION p202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB, PARTITION p202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB, PARTITION p202102 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB) */

二、新表插入数据

insert into t_att_dd_pushcard_info_range select * from t_att_dd_pushcard_info  where work_date='2020-01-01 00:00:00' and work_date='2020-03-01 00:00:00' and work_date='2020-05-01 00:00:00' and work_date

三、表切换

alter table t_att_dd_pushcard_info rename to t_att_dd_pushcard_info_arch;alter table t_att_dd_pushcard_info_range rename to t_att_dd_pushcard_info;

四、数据补录

insert into t_att_dd_pushcard_info select * from t_att_dd_pushcard_info_arch  where work_date>='2020-07-20 00:00:00';

五、检查分区

SELECTTABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,DATA_LENGTH / 1024 / 1024 "DATA(MB)",INDEX_LENGTH / 1024 / 1024 "INDEX(MB)",CREATE_TIME,SUBPARTITION_NAME,PARTITION_ORDINAL_POSITION FROMINFORMATION_SCHEMA.PARTITIONS WHERETABLE_NAME='t_att_dd_pushcard_info'

六、sql改造

1、原sql

SELECTuser_id,class_id,className,isOffDutyFreeCheck,work_date,GROUP_CONCAT( userCheckTimeOn SEPARATOR ',' ) AS userCheckTimeOn,GROUP_CONCAT( userCheckTimeOff SEPARATOR ',' ) AS userCheckTimeOff,timeResultOn,timeResultOff,isExceedFix,normal_or_over,section_id,GROUP_CONCAT( patch_flag SEPARATOR ',' ) AS patch_flag FROM(SELECT DISTINCTt.user_id,t.class_id,sec.class_name AS className,sec.is_off_duty_free_check AS isOffDutyFreeCheck,t.work_date,t.user_check_time AS userCheckTimeOn,'' AS userCheckTimeOff,t.check_type,t.time_result AS timeResultOn,'' AS timeResultOff,is_exceed_fix isExceedFix,t.normal_or_over,t.section_id,patch_flag FROMt_att_dd_pushcard_info tLEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` WHEREt.check_type = 'OnDuty'     AND t.`user_id` IS NOT NULL     AND DATE_FORMAT ( t.work_date, '%Y-%m-%d' ) >= DATE_FORMAT ( '2020-07-01 09:44:37', '%Y-%m-%d' ) AND DATE_FORMAT ( t.work_date, '%Y-%m-%d' ) <= DATE_FORMAT ( '2020-07-21 09:44:37', '%Y-%m-%d' ) UNION ALLSELECT DISTINCTt.user_id,t.class_id,sec.class_name AS className,sec.is_off_duty_free_check AS isOffDutyFreeCheck,t.work_date,'' AS userCheckTimeOn,t.user_check_time AS userCheckTimeOff,t.check_type,'' AS timeResultOn,t.time_result AS timeResultOff,is_exceed_fix isExceedFix,t.normal_or_over,t.section_id,patch_flag FROMt_att_dd_pushcard_info tLEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` WHEREt.check_type = 'OffDuty' AND t.`user_id` IS NOT NULL AND DATE_FORMAT ( t.work_date, '%Y-%m-%d' ) >= DATE_FORMAT ( '2020-07-01 09:44:37', '%Y-%m-%d' ) AND DATE_FORMAT ( t.work_date, '%Y-%m-%d' ) <= DATE_FORMAT ( '2020-07-21 09:44:37', '%Y-%m-%d' ) ) card GROUP BYuser_id,class_id,section_id,className,isOffDutyFreeCheck,work_date,normal_or_over

2、改造后sql

SELECTuser_id,class_id,className,isOffDutyFreeCheck,work_date,GROUP_CONCAT( userCheckTimeOn SEPARATOR ',' ) AS userCheckTimeOn,GROUP_CONCAT( userCheckTimeOff SEPARATOR ',' ) AS userCheckTimeOff,timeResultOn,timeResultOff,isExceedFix,normal_or_over,section_id,GROUP_CONCAT( patch_flag SEPARATOR ',' ) AS patch_flag FROM(SELECT DISTINCTt.user_id,t.class_id,sec.class_name AS className,sec.is_off_duty_free_check AS isOffDutyFreeCheck,t.work_date,t.user_check_time AS userCheckTimeOn,'' AS userCheckTimeOff,t.check_type,t.time_result AS timeResultOn,'' AS timeResultOff,is_exceed_fix isExceedFix,t.normal_or_over,t.section_id,patch_flag FROM(select * from t_att_dd_pushcard_info r where r.work_date>='2020-07-01 09:44:37' and r.work_date <='2020-07-21 09:44:37')tLEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` ) card GROUP BYuser_id,class_id,section_id,className,isOffDutyFreeCheck,work_date,normal_or_over

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！