MySQL亿级数据表DDL解决方案及实战

2024-02-14 18:20
文章标签 实战 mysql 解决方案 ddl database 数据表 亿级

本文主要是介绍MySQL亿级数据表DDL解决方案及实战，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL亿级数据表DDL解决方案及实战

图片

背景

  随着业务的发展，用户对系统需求变得越来越多，这就要求系统能够快速更新迭代以满足业务需求，通常系统版本发布时，都要先执行数据库的DDL变更，包括创建表、添加字段、添加索引、修改字段属性等。

痛点：在数据量不大的情况下，执行DDL速度较快，对业务基本没啥影响，但是数据量大的情况，而且我们业务做了读写分离，接入了实时数仓，这时DDL变更就是一个的难题，需要综合各方业务全盘考虑，否则会造成主从延迟，业务崩溃等后果。

DDL：MySQL中的DDL语句形式较多，概括有以下几类：CREATE，ALTER，DROP，RENAME，TRUNCATE。

DDL执行方式

算法描述
COPYMySQL早期的变更方式，需要创建修改后的临时表，然后按数据行拷贝原表数据到临时表，做rename重命名来完成创建，在此期间不允许并发DML操作，原表是可读的，不可写，同时需要额外一倍的磁盘空间。
INPLACE直接在原表上进行修改，不需创建临时表拷贝数据及重命名，原表会持有Exclusive Metadata Lock，通常是允许并发DML操作。
INSTANTMySQL 5.8开始支持，只修改数据字典中的元数据，表数据不受影响，执行期间没有Exclusive Metadata Lock，允许并发的DML操作。

MySQL对于DDL执行方式一直在做优化，目的就是为了提高DDL执行效率，减少锁等待，不影响表数据，同时不影响正常的DML操作。

说明

  • 对于MySQL 5.6到5.7版本，可以使用OnLine DDL的方式变更，对于大表来说，执行时间会很长，虽然在Master上DML操作不受影响，但是会导致主从延时。

  • 对于MySQL8.0版本，推出了INSTANT方式，只修改MetaData，不影响表数据，所以执行效率跟表大小几乎没有关系。

题外话：

  1. 长远考虑表数据存储大小，合理设计存储规则（我司规范、数据量、主键、字段类型、索引设计、分区等等）

  2. 适当添加预留字段，防止日后数据量过大，再添加字段带来的风险

    DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`sex` enum(F,M,S),`phone`  varchar(30),`field_1`  varchar(30),`field_2`  varchar(30),`field_3`  varchar(30),PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8-mb4;

  3. 唯一索引设置要慎重（业务在迭代过程中，计划往往赶不上变化，一旦确定唯一，数据增长到一定程度，刚需去除唯一较为麻烦）

倘若已然如此，我们该怎么办？

方案

方案一：增表扩展

通过增加表的方式扩展属性，通过外键join来查询

适用场景：新增字段（一般发展新业务模块可以采用）
如：

-- t_user(uid, c1, c2, c3)
-- t_user_ex(uid, c4, c5, c6)

备注：

  1. 数据量大的情况下，join性能也需要考虑

  2. 非业务隔离的情况，新增字段建表不可取

方案二：扩展表动态维护

通过创建动态扩展表来应对新增字段的场景 

适用场景：新增字段

CREATE TABLE `append` (`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',`uid` int(11) NOT NULL COMMENT '关联ID',`key` varchar(255) NOT NULL DEFAULT '' COMMENT '字段名称',`value` varchar(255) NOT NULL DEFAULT '' COMMENT '字段值',`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`),KEY `idx_key` (`key`(191)),KEY `idx_uid` (`uid`),KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=236062439 DEFAULT CHARSET=utf8mb4 COMMENT='扩展表';

备注：

  1. 数据量大的情况下，join性能也需要考虑

  2. 这种方式最好仅应用于边缘业务字段

方案三：pt-online-schema-change

pt-online-schema-change是Percona-toolkit一员，在线改表工具

原理

        创建一个新表，在新表上执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。
适用场景：新增字段、修改索引

步骤

  1. 创建影子表（即DDL扩展后的表）

  2. 在原表上创建三个触发器，对原表进行的所有insert/delete/update操作，都会对新表（影子表）进行相同的操作

  3. 分批将原表中的历史数据insert到新表，直至数据迁移完成（风险：更新频繁的表可能会导致旧数据覆盖新数据）

  4. 新旧表重命名raname table table to table_old,table_new to table

  5. 删掉触发器，把原表移走或归档（注意移除方式，避免影响线上业务），切换完成。

备注：

  1. 整个过程不需要锁表，可以持续对外提供服务

  2. 变更过程中，最重要的是冲突的处理，一条原则，以触发器的新数据为准，要求被迁移的表必须有主键

  3. 变更过程中，写操作需要建立触发器，所以如果原表已经有很多触发器，方案就不行（互联网高并发业务，一般禁止使用触发器）

  4. 触发器的建立，会影响原表的性能，所以这个操作必须在流量低峰期进行。

方案四：gh-ost

        GitHub基于Golang语言开源的MySQL无触发器在线更改表定义工具（GitHub's Online Schema Transmogrifier简写）
开源地址：https://github.com/github/gh-ost

原理

        gh-ost操作方式与现有的在线模式更改工具类似，以与原始表相似的方式创建影子表，将数据从原始表缓慢且增量地复制到影子表，同时应用正在进行的更改（INSERT，DELETE，UPDATE）到影子表。最后，在业务低峰期，用影子表替换原始表。gh-ost 的不同之处在于不使用触发器而使用二进制日志流捕获表的更改，然后将其异步应用到影子表。

步骤

  1. gh-ost首先连接到主库上，创建影子表

  2. 然后作为一个备库连接到其中一个真正的备库或者主库上(根据具体的参数来定)

  3. 两方面动作，一是从主库上拷贝已有的数据到影子表，二是从备库上拉取增量数据的binlog，并应用回主库

  4. 待数据对齐，进行影子表和原表切换。

备注：

  1. gh-ost具备无触发器、轻量级、可暂停、动态可控、可审计、可测试、高可靠等特性

  2. 对于主从结构 DB 集群，Binlog 日志格式必须是 ROW 模式SET GLOBAL binlog_format = 'ROW';

  3. 修改对象表不能被触发器关联，gh-ost虽不依赖触发器，但依然不支持有触发器关联的表

  4. gh-ost会做很多前置的校验检查，比如 binlog_format，表的主键和唯一键，是否有外键等等

  5. gh-ost有三种模式架构，根据实际场景选择应用

方案五：数据同步平滑迁移

原理

影子表数据同步，待数据对齐将原表和影子表切换身份

步骤

  1. 创建DDL扩展后的影子表table_new

  2. 记录binlog位点

    -- 查询binlog位点
SHOW MASTER STATUS;
-- 查询当前毫秒时间戳
SELECT UNIX_TIMESTAMP()*1000;

  3. 通过离线同步工具（DataX/Logstash）同步WHERE UpdatedAt < 'yyyy-MM-dd HH:mm:ss'的历史数据（若没有更新时间字段也可根据自增ID<MAX(ID)的方式实现）

  4. 历史数据全量同步完成后通过binlog同步工具（canal/otter）基于步骤2的位点进行数据同步（方向:table--->table_new）补齐

  5. 数据补齐后，新旧表重命名raname table table to table_old,table_new to table

  6. 停止步骤4的同步服务

备注：

  1. 整个过程不需要锁表，可以持续对外提供服务

  2. 全量数据同步要控制读取和写入流量，避免读取超时及高频写入导致数据库产生压力影响线上业务服务

  3. 升级环节拆分，风险可控

  4. 该方式对应的技术选型范围广，根据具体场景选择最合适的

同步技术选型

技术选型一：阿里云数据传输工具DTS

优点：

  • 一站式同步工具（同时解决离线和全量同步问题）

  • 速度快，数据对齐低延时

  • 易于操作

缺点：

  • 无法控制读取和写入的流量，会存在大批量读写的可能性，对线上核心业务有风险

  • 源表和目标表结构字段必须一致，局限性太高

  • 无法扩展

技术选型二：开源产品

离线同步
DataX：快、准、狠

  • 表一对一同步支持按自增ID切割，利于自动控制读取数据流量

  • 复杂同步（关联、子查询等）无法自动控制读取流量，只能手动根据条件限制读取范围（如：自增ID或时间）

  • 写入支持控制流量、条数、并发，支持多种写入方式

  • 原生不支持同步进度控制（可通过自行封装的方式实现）

  • 配置同步job脚本相对简单（服务端Json文本及可视化界面操作两种方式）

Logstash：稳（慢）、超强扩展（测试版本：Logstash-7.3.1）

  • 同步频率可控，最快同步频率每分钟一次（因此有效控制读取流量，有张有弛，所以既稳也慢）

  • 支持每次全量同步或按照特定字段（如递增ID、修改时间）增量同步

  • 既支持SQL级别的数据转换，也支持filters进行数据中间转换，扩展性强

  • 支持同步进度记录

canal-手动ETL：快

  • 可以控制查询条件和Limit限制，查多少写多少

  • 在配置完整的canal同步策略后，可以通过adapter-REST接口手动执行

  • 建议小表使用

综合说明：

  1. DataX适合简单规则同步,配置（reader+writer）简单，同步效率高，但若Reader查询条件非索引或者查询语句较慢会导致超时，虽然支持splitPK切割主键，但数据量太大也可能会导致查询超时问题，并且不知道同步进度需要重新来过

  2. Logstash只要时效性不高的场景均能兼容，但配置（input+filters+output）较为复杂，支持同步进度记录，即使出行同步异常也可以根据记录继续同步，另外可以控制查询条数（limit），防止查询超时；

增量同步
canal：基于binlog进行数据同步，支持单向
otter：基于binlog进行数据同步，支持单向、双向
Databus：基于binlog进行数据同步，支持单向、双向（资料少不建议使用）

综合说明：
        对于增量同步canal/otter二选一即可，otter其实也只是基于canal做了二次封装，canal需要进行服务级别的配置，相对灵活，otter有管理界面，易于配置操作，但复杂同步功能需要开发自定义同步插件。

实战示例

需求

需求：order_info表业务迭代需要新增索引字段“order_no”
数据大小：3亿
数据库类型及版本：MySQL5.7
表结构：

-- 原始表
CREATE TABLE order_info (order_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',order_no BIGINT UNSIGNED NOT NULL COMMENT '订单编号',customer_id INT UNSIGNED NOT NULL COMMENT '下单人ID',shipping_user VARCHAR (10) NOT NULL COMMENT '收货人姓名',address VARCHAR (100) NOT NULL COMMENT '地址',payment_method TINYINT NOT NULL COMMENT '支付方式：1现金，2余额，3网银，4支付宝，5微信',order_money DECIMAL (8, 2) NOT NULL COMMENT '订单金额',pay_time DATETIME COMMENT '支付时间',order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',PRIMARY KEY pk_orderid (order_id)
) ENGINE = INNODB COMMENT '订单主表';

方案选择

基于DataX+otter实现

实施流程

前期准备

  1. 创建影子表

    CREATE TABLE order_info_new (order_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',order_no BIGINT UNSIGNED NOT NULL COMMENT '订单编号',customer_id INT UNSIGNED NOT NULL COMMENT '下单人ID',shipping_user VARCHAR (10) NOT NULL COMMENT '收货人姓名',address VARCHAR (100) NOT NULL COMMENT '地址',payment_method TINYINT NOT NULL COMMENT '支付方式：1现金，2余额，3网银，4支付宝，5微信',order_money DECIMAL (8, 2) NOT NULL COMMENT '订单金额',pay_time DATETIME COMMENT '支付时间',order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',PRIMARY KEY pk_orderid (order_id),KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE = INNODB COMMENT '订单主表';

  2. 申请MySQL用户

    • 数据库表读写权限

    • binlog读取权限

记录当前binlog位点

查询SQL：

-- 查询binlog位点
SHOW MASTER STATUS;
-- 查询当前时间及毫秒时间戳
SELECT NOW(), UNIX_TIMESTAMP()*1000;

输出：

{"File":"mysql-bin.002993","Position":480762442
}
{"NOW()":"2020-08-15 12:00:26","UNIX_TIMESTAMP()*1000":1597464026000
}

历史数据全量同步

  1. DataX安装
    流程详见 https://github.com/alibaba/DataX

  2. 配置DataX同步job脚本
    关键点：

    vim order_info_sync.json

    {"job": {"setting": {"speed": {"channel": 10},"errorLimit": {"record": 0,"percentage": 0}},"content": [{"reader": {"name": "mysqlreader","parameter": {"username": "xxxxx","password": "xxxxx","column": ["order_id","order_no","customer_id","shipping_user","address","payment_method","order_money","pay_time","order_status","create_time","update_time"],"where": "update_time < '2020-08-15 12:00:26'","splitPk": "order_id","connection": [{"table": ["order_info"],"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/order"]}]}},"writer": {"name": "mysqlwriter","parameter": {"writeMode": "replace","batchSize": 128,"username": "xxxxx","password": "xxxxx","column": ["order_id","order_no","customer_id","shipping_user","address","payment_method","order_money","pay_time","order_status","create_time","update_time"],"session": ["set session sql_mode='ANSI'"],"preSql": [""],"connection": [{"jdbcUrl": "jdbc:mysql://127.0.0.1:3306/order?characterEncoding=utf8","table": ["order_info_new"]}]}}}]}
}

    1. mysqlreader连接配置非业务依赖的只读实例

    2. mysqlreader查询条件语句配置需考虑查询性能及查询数据大小，能切割主键则尽量使用，避免查询超时和大数据量导致OOM等情况

    3. mysqlwriter链接配置只写的主实例

    4. 读写流量控制："channel": 10,"batchSize": 128（根据实际业务并发情况配置）

    5. mysqlwriter的写入方式writeMode，根据实际情况选择

  3. 启动

    cd bin/
nohup python datax.py ../job/order_info_sync.json &

  4. 查看日志

    cd bin/
tail -f nohup.out

  5. 等待历史数据补全至上述位点

  6. 核对新旧表历史数据是否一致；

增量数据实时同步

说明：
  下述同步案例基于开源产品otter进行增量同步，可自行查看开源文档相关配置进行参考

  1. 安装部署otter

    1. 安装otter-manager

    2. 安装otter-node

  2. 配置 详细配置操作可参考 https://github.com/alibaba/otter/wiki/Adminguide

    1. 登陆otter-manager

    2. 新建数据源配置（主实例的链接地址、数据库名称、用户名、密码）

    3. 新建数据表同步配置（order_info和order_info_new）

    4. 新建canal配置（监听binlog数据库表地址），基于上述位点配置开始位点 

    5. 新建同步通道及映射关系（Channel管理 > Pipeline管理 > 映射关系列表 > 字段映射） 

  3. 开启同步Channel

  4. 等待数据对齐

原表与影子表切换

  1. rename table

    rename table order_info to order_info_old,order_info_new to order_info;

  2. 停止otter同步服务并删除旧表order_info_old（注意删除方式）

备注

  1. 对于表数据性质是早期历史数据不会再更新（如：订单结束后，相关属性不会再变），可以先同步近期历史，然后开启增量实时同步，然后异步的去追平所有的历史数据，这样可以减少增量数据对齐时间，前提是数据本身有一个可靠的生命周期（即到达某种状态，该行数据不再发生变化）

  2. 对于只有插入没有更新、删除的数据，可以直接开启增量实时数据同步，然后异步同步历史全量数据

  3. 建议先开启增量，确保先能够获得位点，然后暂停增量，开启全量同步，全量同步完成，开启增量，组织数据对齐，这样能够完整的保证数据一致性。

  4. 影子表rename方式虽然能够解决数据库系统、业务系统稳定问题，但是需要考虑大数据数据仓库模块的对接，譬如实时数仓ADB不允许源端MySQL表重命名，如果通过COPY方式或者pt-osc、gh-ost等工具都会rename表名，那么就需要从数仓删除该表，重新配置同步(全量 + 增量)，会影响数仓业务

原文出处：吴海飞 阳光出行技术   https://mp.weixin.qq.com/s/0cD4RyCs47MVylCrkWtq2g

 

这篇关于MySQL亿级数据表DDL解决方案及实战的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/709246。

相关文章

MySQL中删除重复数据SQL的三种写法

MySQL中删除重复数据SQL的三种写法

《MySQL中删除重复数据SQL的三种写法》：本文主要介绍MySQL中删除重复数据SQL的三种写法,文中通过代码示例讲解的非常详细,对大家的学习或工作有一定的帮助,需要的朋友可以参考下... 目录方法一：使用 left join + 子查询删除重复数据(推荐)方法二：创建临时表(需分多步执行，逻辑清晰，但会
阅读更多...
Redis连接失败：客户端IP不在白名单中的问题分析与解决方案

Redis连接失败：客户端IP不在白名单中的问题分析与解决方案

《Redis连接失败：客户端IP不在白名单中的问题分析与解决方案》在现代分布式系统中,Redis作为一种高性能的内存数据库,被广泛应用于缓存、消息队列、会话存储等场景,然而,在实际使用过程中,我们可能... 目录一、问题背景二、错误分析1. 错误信息解读2. 根本原因三、解决方案1. 将客户端IP添加到Re
阅读更多...
python 字典d[k]中key不存在的解决方案

python 字典d[k]中key不存在的解决方案

《python字典d[k]中key不存在的解决方案》本文主要介绍了在Python中处理字典键不存在时获取默认值的两种方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录defaultdict：处理找不到的键的一个选择特殊方法__missing__有时候为了方便起见，
阅读更多...
Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
Golang使用minio替代文件系统的实战教程

Golang使用minio替代文件系统的实战教程

《Golang使用minio替代文件系统的实战教程》本文讨论项目开发中直接文件系统的限制或不足,接着介绍Minio对象存储的优势,同时给出Golang的实际示例代码,包括初始化客户端、读取minio对... 目录文件系统 vs Minio文件系统不足：对象存储：miniogolang连接Minio配置Min
阅读更多...
Node.js 中 http 模块的深度剖析与实战应用小结

Node.js 中 http 模块的深度剖析与实战应用小结

《Node.js中http模块的深度剖析与实战应用小结》本文详细介绍了Node.js中的http模块,从创建HTTP服务器、处理请求与响应,到获取请求参数,每个环节都通过代码示例进行解析,旨在帮... 目录Node.js 中 http 模块的深度剖析与实战应用一、引言二、创建 HTTP 服务器：基石搭建（一
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
Linux限制ip访问的解决方案

Linux限制ip访问的解决方案

《Linux限制ip访问的解决方案》为了修复安全扫描中发现的漏洞,我们需要对某些服务设置访问限制,具体来说,就是要确保只有指定的内部IP地址能够访问这些服务,所以本文给大家介绍了Linux限制ip访问... 目录背景：解决方案：使用Firewalld防火墙规则验证方法深度了解防火墙逻辑应用场景与扩展背景：
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2