MySQL千万表归档的项目实践

目录

• 概述
• 背景
• 归档方案
• 主从延迟出现原因分析
• 主从延迟解决思路分析
• 结语

概述

随着项目数据量的急剧增长，为了优化性能提升产品体验感，我们决定对数据进行归档处理。归档策略为实时数据仅保留6个月，超过期限的数据将被归档至历史表中，在此过程中，我们遇到了数据库主从延迟的问题，下面将进行分析去解决

背景

硬件：阿里云mysql主从服务

项目：深度使用主从库，非操作类处理大多都查从库

涉及表：主要以订单及账务业务为中心的表

数据量：最大表当前数据量接近五千余万

注：以下归档将不分析归档后业务影响，需结合自身业务去考量相关的善后工作（例：业务该如何查询历史数据，报表该如何跑历史数据等）

归档方案

在归档实践中，我们是以分布式调度存储过程进行以减少网络开销，android但为避免业务敏感以及方案通用性，下面将以业务程序代码讲解归档步骤

# 演示表
DROP TABLE IF EXISTS `biz_order`;
CREATE TABLE `biz_order`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `order_no` varchar(20) NOT NULL COMMENT '订单编号',
  `order_status` tinyint NOT NULL COMMENT '订单状态(1:成功 2:失败 3:支付中)',
  ..... 省略N个字段
  `gmt_crea编程客栈te` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
# 这里非常重要，历史表必需与原表结构保持一致，即使可能原表上会有N个索引影响迁移的性能
CREATE TABLE biz_order_history LIKE biz_order;

# 归档日志记录表
DROP TABLE IF EXISTS `biz_archive_log`;
CREATE TABLE `biz_archive_log`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `src_table` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '归档的表名',
  `sql_text` varchar(1024) CHARACTEphpR SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '执行的脚本',
  `paras` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '实际执行参数',
  `flag` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否执行成功:成功,失败',
  `result_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '执行结果描述',
  `row_num` int NULL DEFAULT NULL COMMENT '影响行数',
  `exec_times` int NULL DEFAULT NULL COMMENT '执行耗时ms',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `flag`(`flag` ASC) USING BTREE,
  INDEX `create_time`(`create_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '归档日志表' ROW_FORMAT = Dynamic;

准备工作都完成后下面正式开始归档流程，下面将以 biz_order表 为例

• 确定biz_order表归档条件，即：哪些数据需要归档，哪些数据能归档？

  以order表为例，那么只能归档状态为: 1:成功 2:失败 的订单(实践时这里需要结合业务考量清楚)

• 当确定归档条件后，我们下一步需要做的事就是需要确定这些条件有没有索引

# 因为我们后续将会以此条件去删除原表数据，继而写入目标表(若无索引在千万级表中想删除部分数据那可真难哦)
ALTER TABLE `biz_order` ADD INDEX `idx_create_status`(`gmt_create`, `order_status`);

现在我们知道了归档方向，也做好了归档的前期准备，那么该如何进行归档呢？可以一次性直接将6个月前数据归档吗？

显然这是不行的，因为大批量删除可能会锁表，会影响正常的实时业务，这是我们不能接受的现象

这里给到一种归档策略：分段归档

顾名思义，我们可以将历史要归档的数据拆分成一个个小段，然后对这些小段进行归档操作，少量多次的原理

例：minute按分钟循环归档 tenMinutes按十分钟循环归档 hour按小时循环归档 day按天循环归档 month按月循环归档

归档实践

# 拿按小时归档举例
LocalDate startDate = '2023-07-01'; # 举例直观说明，非实际Api
LocalDate endDate = '2023-07-31';
for (LocalDate tmpDate = startDate; tmpDate.isBefore(endDatjavascripte;
    tmpDate = tmpDate.plusHours(1)) {
    1. 开启事务
    2. insert into biz_order_history select * from payment where create_time >= '2023-07-01 00:00' and create_time < '2023-07-01 01:00' and order_status in (1, 2);
    3. delete from biz_order where create_time >= '2023-07-01 00:00' and create_time < '2023-07-01 01:00' and order_status in (1, 2);
    # 以上任何一个步骤异常都将进行回滚并记录 biz_archive_log 日志
    # 若插入 biz_archive_log 日志也失败则忽略，不影响业务
    4. insert into biz_archive_log
    5. 提交事务
}
# 具体使用什么归档方式取决于具体要归档的表，需慎重评估：粒度太大会执行较慢，形成慢SQL大事务影响业务。粒度太小则也会造成归档时间过长，从服务器延迟高(埋个伏笔，下面会提到)

到目前为止，我们的归档也已经做完了，可以开始测试，继而上线了

因这个归档方案我们之前在其他项目也用过，就没搭相关环境进行深度性能测试，也正是因为这里让我们在线上踩了大坑

2024年12月的一天凌晨，此归档版本正式上线，根据既定好的验证流程，一步步执行，在这过程中并没有什么异常，性能也符合我们预期，随即我们开始跑其他历史表，但天有不测风云，问题来了

主从延迟自9点多的100多秒然后到10点左右达到了巅峰的800多秒，业务也随着主从延迟的增加而炸锅，因上面提及的项目深度使用从库，这时导致很多业务已经无法正常使用，随即我们陆续停掉了归档任务…

主从延迟出现原因分析

# 首先分析异步主从为什么会有延迟(这里使用的是异步复制)
a. 主库执行完一个事务，写入binlog，我们把这个时刻记为T1
b. 主库同步数据给从库，从库接收完这个binlog的时刻，记录为T2
c. 从库执行完这个事务，这个时刻记录为T3
所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即T3-T1

# 主从延迟出现原因
a. 机器性能差
b. 从库的压力大
c. 大事务
d. 网络延迟
e. 从库数量多，复制压力大
f. 低版本只支持单线程复制，高版本是支持多线程复制

主从延迟解决思路分析

因我们使用的是阿里云MySQL8.0主从服务,有且只有一个从库

所以a/d/e/f原因可以排除掉，那么还剩下b/c两个原因

当时提工单联系阿里云售后，给出的答复也大致是我们同步的操作是否是大事务，以及同步数据量过大导致负载较高，继而出现主从延迟高的问题

顺着这个思路，dba同事提出2个方案

1. 将从服务器配置为代理地址，归档时在代理服务器将从库切换到主库，从而使得整个系统都运行在主库之上，此时归档虽然会导致主从延迟过高，但不影响业务(此方案同样可在大表加索引等场景使用)

缺点：主库在此时压力会剧增

2. 开始重新改造存储过程，在每个批次(hour)归档完成后，强制sleep1秒来降低从服务器的复制压力

这里我们批次hour实际上事务并不算大，若hour级别数据量大可以考虑实现minuthttp://www.devze.come、tenMinutes来进一步的降低事务的粒度

因第一个方案是需要重新配置改造，故我们选择第二个方案先进行尝试

emm 如同柳暗花明又一村，这时归档数据的延迟已经降到1s以下，这时几乎可以忽略不计，对业务不会造成影响

注：从库版本为8.0.28，slave_parallel_workers配置为8

结语

暂停策略是一个临时解决方案，但它在一定程度上缓解了主从延迟问题，保证了归档操作的顺利进行。我们将继续监控数据库性能，并寻求更长期的解决方案，以确保数据的一致性和归档操作的效率。

到此这篇关于MySQL千万表归档的项目实践的文章就介绍到这了,更多相关MySQL千万表归档内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！