【数据库优化】大表分表分库备份压缩进行业务表，备份历史业务表

最新推荐文章于 2024-07-13 18:10:19 发布

黑桃K

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量121

点赞数 1

文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_43162050/article/details/139420778

版权

由于大表数据业务需求，确保当前运行数据表数据保持在一定体量，历史少用数据，需要对历史数据进行分库搬迁，并按照时间进行分区

我采用的是将现有数据，保留两年数据，先执行一次大批量的录入，后续运用数据库定时任务，执行操作函数，从当前时间往后推两年时间，两年前的今天的形式将数据录到入新库新表，删除业务库历史存留

首先需要创建一张分区表，存于历史库（分区字段必须是主键），加PARTITION 字段查询则可以根据时间段查询数据，不加则为所有

CREATE TABLE my_table (
    id INT    AUTO_INCREMENT,
    name VARCHAR(255),
    create_time DATE,
PRIMARY  KEY (id,create_time) -- 分区数据必须和主键
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p2027 VALUES LESS THAN (2028),
PARTITION p2028 VALUES LESS THAN (2029),
PARTITION p2029 VALUES LESS THAN (2030),
PARTITION p2030 VALUES LESS THAN (2031),
PARTITION p2031 VALUES LESS THAN (2032),
PARTITION p2032 VALUES LESS THAN (2033),
PARTITION p2033 VALUES LESS THAN (2034),
PARTITION p0 VALUES LESS THAN MAXVALUE
);


select * from my_table PARTITION (p2019);

INSERT INTO my_table (name, create_time) VALUES ('Data 1', '2018-01-01');
INSERT INTO my_table (name, create_time) VALUES ('Data 2', '2019-03-15');
INSERT INTO my_table (name, create_time) VALUES ('Data 3', '2020-07-10');

创建一个mysql函数，（也算是回顾了一下sql函数相关知识，游标循环之类的，尤其要注意库引用，搞错了会很麻烦）确保可以运行，配置到事件定时执行即可

CREATE DEFINER=`root`@`%` PROCEDURE `kujiale_quote_his`()
BEGIN
 DECLARE curr_order_id LONG DEFAULT -1;
 DECLARE done INT DEFAULT FALSE;

	#查询订单两年前的数据
 declare order_ids cursor for select t1.id from biz_order t1 where DATE_FORMAT(t1.create_time,'%Y-%m-%d') = DATE_SUB(DATE(now()),INTERVAL 24 MONTH);
 
 #首次同步21-23年数据
--   declare order_ids cursor for select t1.id from biz_order t1 where DATE_FORMAT(t1.factory_time,'%Y-%m-%d') <'2023-05-05' and DATE_FORMAT(t1.factory_time,'%Y-%m-%d') >'2021-01-01' and order_attribute not in (1,5,10,4) and status!=70;
 
#declare order_ids cursor for select t1.id from biz_order t1 where DATE_FORMAT(t1.factory_time,'%Y-%m-%d') = '2019-03-16';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

insert into his_crm.biz_move_his_log(biz_table,biz_table_his,where_key,where_value,move_count)values('biz_move_his_log','biz_move_his_log','begin','执行过程order_quote_his开始',0);
 
	#循环撤销订单id查询物料报价信息
 open order_ids;  # 打开游标
    # 将游标中的值赋给定义好的变量，实现for循环的要点

	#循环
	read_loop: LOOP
			FETCH order_ids INTO curr_order_id;
			IF done THEN
          LEAVE read_loop;
      END IF; 
			IF curr_order_id > 0 THEN
			
				#防止重复先删除一遍his库的
				delete from his_crm.biz_kujiale_quote_new WHERE order_id=curr_order_id;
			
			
				#根据order_id查询biz_mes_order_quote 插入对应his
				insert into his_crm.biz_kujiale_quote_new(select t.*,(select create_time from crm.biz_order where id=curr_order_id) as order_time from crm.biz_kujiale_quote_new t where t.order_id=curr_order_id);
				
		
				
				insert into biz_move_his_log(biz_table,biz_table_his,where_key,where_value,move_count)values('biz_kujiale_quote_new','biz_kujiale_quote_new','order_id',curr_order_id,(select count(1) from biz_kujiale_quote_new t where t.order_id=curr_order_id));
				#删除原报价明细表数据
				#请勿修改成无条件删除
				delete from crm.biz_kujiale_quote_new WHERE  order_id=curr_order_id;
			END if;
	END LOOP;
	#结束日志
	insert into his_crm.biz_move_his_log(biz_table,biz_table_his,where_key,where_value,move_count)values('biz_move_his_log','biz_move_his_log','end','执行过程order_quote_his结束',0);

 CLOSE order_ids;
END

注意事项：删除大表数据，使用delete 服务器磁盘并不会释放空间需要执行 TRUNCATE TABLE table_name; 优化表，会重新编排索引，压缩空间出来，大表会需要很多时间，另外如果采用读写分离，配置了主从库，一定要确保主从两边函数都具备同步，因为函数好像不会同步，出现错误主从就会脱钩，踩坑了