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 RANGECOLUMNS(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) */

二、新表插入数据ｉｎｓｅｒｔ into t_att_dd_pushcard_info_range ｓｅｌｅｃｔ * from t_att_dd_pushcard_infowhere 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=‘2020-07-20 00:00:00‘;

五、检查分区ｓｅｌｅｃｔTABLE_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

ｓｅｌｅｃｔuser_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(ｓｅｌｅｃｔ 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-01 09:44:37‘, ‘%Y-%m-%d‘ ) AND DATE_FORMAT ( t.work_date, ‘%Y-%m-%d‘ ) =‘2020-07-01 09:44:37‘ and r.work_date