优秀的编程知识分享平台

网站首页 > 技术文章 正文

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

nanyue 2024-08-08 19:02:38 技术文章 15 ℃

概述

简单分享下最近做的一个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'; 
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';
insert into t_att_dd_pushcard_info_range select * from t_att_dd_pushcard_info
  where work_date>='2020-03-01 00:00:00' and work_date<'2020-05-01 00:00:00'; 
insert into t_att_dd_pushcard_info_range select * from t_att_dd_pushcard_info
  where work_date>='2020-05-01 00:00:00' and work_date<'2020-07-20 00:00:00';


三、表切换

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';


五、检查分区

SELECT
	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 
FROM
	INFORMATION_SCHEMA.PARTITIONS 
WHERE
	TABLE_NAME='t_att_dd_pushcard_info'


六、sql改造

1、原sql

SELECT
	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
	(
	SELECT DISTINCT
		t.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
		t_att_dd_pushcard_info t
		LEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` 
	WHERE
		t.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 ALL
	SELECT DISTINCT
		t.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 
	FROM
		t_att_dd_pushcard_info t
		LEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` 
	WHERE
		t.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 BY
	user_id,
	class_id,
	section_id,
	className,
	isOffDutyFreeCheck,
	work_date,
	normal_or_over



2、改造后sql

SELECT
	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
	(
	SELECT DISTINCT
		t.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')t
		LEFT JOIN t_att_dd_attgroup_sec_info sec ON t.`class_id` = sec.`class_id` 
	) card 
GROUP BY
	user_id,
	class_id,
	section_id,
	className,
	isOffDutyFreeCheck,
	work_date,
	normal_or_over


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


Tags：

猜你喜欢

最近发表
标签列表