高级DBA带你处理Mysql数据库10亿大数据条件下迁移实战

Mysql数据库10亿大数据条件下迁移实战

一、背景描述：

在实际工作中，有些特殊的场景需要进行生产数据迁移，并且生产环境的数据量非常大，比如上10亿数据，并且迁移实施的时间并有所限制，比如9小时内、48小时内，必须恢复生产。
并且在这么庞大的数据量条件下，还要进行数据过滤调整！
此种情况下，传统Java jdbc多线程的技术路线远远不能满足实际场景的需求。这个时候该怎么办？

二、核心技术 MySQL FEDERATED 存储引擎的介绍

技术简介：

FEDERATED存储引擎能让你访问远程的MySQL数据库而不使用replication或cluster技术(类似于Oracle的dblink),使用FEDERATED存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地。

FEDERATED 存储引擎架构

1 本地服务器 FEDERATED 存储引擎的表只存放表的.frm结构文件
2 远程服务器 存放了.frm和数据文件
3 增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。
4 远程数据表的存储引擎为MySQL支持的存储引擎,如MyISAM,InnoDB等

操作步骤：
远程库:
开启 FEDERATED 存储引擎
建立远程访问用户
授予访问对象的权限
本地库：

FEDERATED 引擎使用注意事项

1、FEDERATED 表可能会被复制到其他的slave数据库,你需要确保slave服务器也能够使用定义在connection中或mysql.servers表中的link的用户名/密码 连接上远程服务器。

2、远程服务器必须是MySQL数据库

3、在访问FEDERATED表中定义的远程数据库的表前,远程数据库中必须存在这张表。

4、FEDERATED 表不支持通常意义的索引,服务器从远程库获取所有的行然后在本地进行过滤,不管是否加了where条件或limit限制。

–查询可能造成性能下降和网络负载,因为查询返回的数据必须存放在内存中,所以容易造成使用系统的swap分区或挂起。

5、FEDERATED表不支持字段的前缀索引

6、FEDERATED表不支持ALTER TABLE语句或者任何DDL语句

7、FEDERATED表不支持事务

8、本地FEDERATED表无法知道远程库中表结构的改变

9、任何drop语句都只是对本地库的操作,不对远程库有影响

在这个架构中：•本地数据库1：这是启用了Federated存储引擎的MySQL数据库服务器。它包含一个定义为Federated类型的表（例如表A），这个表并不在本地存储数据。•表A(Federated)：在本地数据库中的表，其数据实际上是通过远程连接映射到另一个数据库的实体表上。•远程连接：当对本地的Federated表进行操作时，Federated引擎会通过标准的MySQL客户端协议使用TCP/IP网络连接到远程数据库。•远程数据库2：这是一个独立的MySQL数据库服务器，其中包含了实际的数据，即实体表A。•实体表A：存在于远程数据库的实际数据表，所有对本地Federated表的操作都会通过网络传输并执行于该实体表上。这样的架构允许应用程序像访问本地表一样访问远程数据库中的表，从而简化了跨数据库的数据访问和集成。

FEDERATED 引擎实战案例

MySQLFEDERATED存储引擎提供了一种方法，使得本地MySQL数据库能够透明地访问远程MySQL服务器上的表。以下是一个实际使用FEDERATED引擎的例子：
创建远程服务器上的实体表
首先，在远程MySQL服务器上创建一个实体表（假设远程服务器的IP是
192.168.1.100
，端口是默认的3306，数据库名为
remote_db
）：
-- 在远程服务器上执行
CREATEDATABASEIFNOTEXISTS remote_db;USE remote_db;CREATETABLE employees (
    id INTPRIMARYKEYAUTO_INCREMENT,
    name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50));

在本地启用并配置FEDERATED引擎
确保在本地MySQL服务器上已安装并启用了FEDERATED引擎，可以通过如下方式检查和安装：
SHOWENGINES;-- 检查FEDERATED是否可用且支持YESINSTALLPLUGIN federated SONAME 'ha_federated.so';-- 如果不可用，则安装插件
-- 配置my.cnf文件（Linux系统下通常是/etc/my.cnf或Windows下的my.ini），添加federated引擎到mysqld段落中，并重启MySQL服务

创建本地FEDERATED表映射到远程表
接下来，在本地数据库中创建一个FEDERATED表，该表将指向远程数据库的
employees
表：
-- 在本地服务器上执行
CREATEDATABASEIFNOTEXISTS local_db;USE local_db;CREATETABLE federated_employees (
    id INTPRIMARYKEY,
    name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50))ENGINE=FEDERATEDCONNECTION='mysql://remote_user:[email protected]:3306/remote_db/employees';-- `remote_user`和`remote_password`分别是远程数据库的用户名和密码

现在，当你在本地的
federated_employees
表上执行查询时，实际上就是在远程服务器上的
employees
表上执行操作：
-- 本地查询示例
INSERTINTO local_db.federated_employees (name, position, department)VALUES('JohnDoe', 'Manager', 'HR');SELECT*FROM local_db.federated_employees WHERE department ='HR';

以上就是一个完整的MySQLFEDERATED引擎实际使用的例子，通过这种方式，本地数据库可以像操作本地表一样来操作远程数据库中的数据。

三、核心思想描述

DBLINK数据库直连的方式数据传输性能肯定比包含中间商赚差价的传统JavaJDBC的方式性能快上10倍，但是上章节也讲述了FEDERATED表不支持事务！则在数据迁移过程中，会存在数据丢失的情况。这种情况要怎么解决呢？

笔者在2亿的数据量迁移的过程中，遇到了很多数据库的报错！丢数据的情况！
比如：MYSQL最大数据库包限制，还有遇到一些锁的错。

show VARIABLES like '%max_allowed_packet%';

最大配置值1G 所以需要补充分页机制，限制每一个批量数据包的大小！

所以必须引入分页机制，来规范每个数据包的最大值不超过数据库允许的最大值！

四、引入分页算法

上亿的数据要进行数据分页！
笔者规定了页数为5万！5万一个页，不超过最大的包限制！
分页的公式，
设每页显示的数据个数为 pageSize，当前是第 pageNo 页，那么就有如下 LIMIT 查询公式

LIMIT (pageNo - 1) * pageSize, pageSize;

初始偏移量 (pageNo - 1) * pageSize
备注：理解区块
求显示第3 个数据，
是不是应该每页1个，显示第3页，（3-1）1，1，所以应该是limit 2,1
反算的话：结果/pageSize+1=pageNo
查询员工表 employees 中的第 32、33条员工的数据*

SELECT employee_id, last_name, salary
FROM employees
LIMIT 31, 2;

实际案例：
20万数据，5万一页
limit 0，50000
limit 50001，50000
limit 100001，50000
limit 150001，50000

将数据分页处理，然后分页插入！

insert into 映射表 select * from 源表 limit 0，50000 
insert into 映射表 select * from 源表 limit 50001，50000
insert into 映射表 select * from 源表 limit  100001，50000
insert into 映射表 select * from 源表 limit 150001，50000

五、引入MYSQL分区机制

什么是表分区

通俗地讲表分区是将一个大表,根据条件分割成若干个小表。
mysql5.1开始支持数据表分区。如:某用户表的记录超过了600万条仓储信息，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

为什么要做表分区

为了改善大型表以及具有各种访问模式的表可伸缩性客观理性，和提高数据库效率

表分区有哪些优点

与单个磁盘或文件系统分区相比，可以存储更多的数据
通过删除与增加那些数据相关的分区，可以很容易的删除或增加那些数据
一些查询可以得到很好的优化
通过跨多个磁盘甚至服务器来分散数据查询来获得更大的查询吞吐量

基本分区类型

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区∶基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
key分区：类似于hash分区，区别在于key分区只支持计算一列或多列。

range分区

ALTER TABLE titles
partition by range (year(from_date))
partition po1 values less than (1985),
partition po2 values less than (1986),
partition po3 values less than (1987),
partition po4 values less than (1988),
partition p05 values less than (1999),
partition p06 values less than (MAXVALUE)

在MySQL中，RANGE分区是按照指定的范围将数据分配到不同的分区中的方式。以下是一个创建一个RANGE分区表的例子，假设我们有一个销售记录表，其中包含交易日期，并且我们希望根据交易年份来分区：
– 创建一个名为sales的表并进行RANGE分区
C

REATE TABLE sales (id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product VARCHAR(100) NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2)) PARTITION BY RANGE (YEAR(sale_date))(
    -- 分区定义：2010年及以前的交易记录放入partition_2010分区
    PARTITION partition_2010 VALUES LESS THAN (2011),
    
    -- 2011年及以前（不包括2012年）的交易记录放入partition_2011分区
    PARTITION partition_2011 VALUES LESS THAN (2012),
    
    -- 类似地，逐年划分分区直到某个结束年份
    PARTITION partition_2012 VALUES LESS THAN (2013),
    PARTITION partition_2013 VALUES LESS THAN (2014),
    ...
    PARTITION partition_2020 VALUES LESS THAN (2021),
    -- 如果想包含所有2021年及以后的记录，可以设置一个MAXVALUE分区
    PARTITION future_sales VALUES LESS THAN MAXVALUE
);

– 插入示例数据

INSERT INTO sales (customer_id, product, sale_date, amount) VALUES
(1, 'Product A', '2010-01-01', 100.00),
(2, 'Product B', '2011-07-01', 200.00),
(3, 'Product C', '2013-12-31', 300.00),
(4, 'Product D', '2020-10-10', 400.00),
(5, 'Product E', '2022-03-01', 500.00);

在这个例子中， sales 表被按 sale_date 列的年份进行了分区，每个分区包含了相应年份内的所有交易记录。这样可以根据时间范围快速定位和操作特定年度的数据。

将上亿的大表，按特有的时间、空间进行分块！在配合分页机制！将每个数据块变得大小可控！同时保证大数据的传输性能！

上亿的大表，最终被切割成规范大小的数据块进行迁移！

六、引入主键补漏机制处理迁移过程中丢失的数据

DBLINK数据库直连的方式数据传输性能肯定比包含中间商赚差价的传统JavaJDBC的方式性能快上10倍，但是上章节也讲述了FEDERATED表不支持事务！则在数据迁移过程中，会存在数据丢失的情况。这种情况要怎么解决呢？

所以要引入迁移之后的补漏机制，将丢的数据再补上，直到数据一致！
补漏机制算法很简单，通过源与目标的数据库各个表主键进行对比即可。

举例：

insert into 映射表 select * from 源表  where  ID not in(select ID FROM 映射表)

将映射表不在源表ID的记录再补充插入！也就是将过程中意外丢失的数据再补充下！

七、引入CLICKHOUSE数据库OLAP技术提升数据对比的性能

clickhouse-简介

​ ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System) , 主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。 ClickHouse的全称是Click Stream，Data WareHouse，简称ClickHouse
​ ClickHouse是一个完全的列式分布式数据库管理系统(DBMS)，允许在运行时创建表和数据库，加载数据和运行查询，而无需重新配置和重新启动服务器，支持线性扩展，简单方便，高可靠性，容错。它在大数据领域没有走 Hadoop 生态，而是采用 Local attached storage 作为存储，这样整个 IO 可能就没有 Hadoop 那一套的局限。它的系统在生产环境中可以应用到比较大的规模，因为它的线性扩展能力和可靠性保障能够原生支持 shard + replication 这种解决方案。它还提供了一些 SQL 直接接口，有比较丰富的原生 client。

优点

灵活的MPP架构,支持线性扩展，简单方便，高可靠性
多服务器分布式处理数据 ,完备的DBMS系统
底层数据列式存储,支持压缩,优化数据存储,优化索引数据 优化底层存储
容错跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍,其可处理的数据级别已达到10亿级别
功能多：支持数据统计分析各种场景，支持类SQL查询，异地复制部署
海量数据存储,分布式运算,快速闪电的性能,几乎实时的数据分析 ,友好的SQL语法,出色的函数支持

缺点

不支持事务，不支持真正的删除/更新 (批量)
不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下
不支持二级索引
不擅长多表join 大宽表
元数据管理需要人为干预
尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作

应用场景

1.绝大多数请求都是用于读访问的, 要求实时返回结果
2.数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
3.数据只是添加到数据库，没有必要修改
4.读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
5.表很“宽”，即表中包含大量的列
6.查询频率相对较低（通常每台服务器每秒查询数百次或更少）
7.对于简单查询，允许大约50毫秒的延迟
8.列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
9.在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
10.不需要事务
11.数据一致性要求较低 [原子性 持久性 一致性 隔离性]
12.每次查询中只会查询一个大表。除了一个大表，其余都是小表
13.查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

笔者将数据对比的任务交给clickhouse去对比，数据性能又提升几倍！

八、个人总结

笔者将个人真实生产生活案例给朋友们分享！
解决这个实际问题用到了比较宽的技术栈，DBA数据库技术，JAVA技术，OLAP技术！算法！需要工程师的技术广度，与探索精神！
笔者是拿数据仓库这套思路来解决大数据迁移的瓶颈，这套解决方案确实比传统方法JDBC快几十倍！生产中真正验证过的。

作者本人简介：现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目。并维护真实企业级生产数据库300余个。紧急处理数据库生产事故上百起，挽回数据丢失所操作的灾难损失不计其数。