Mysql數(shù)據(jù)庫10億大數(shù)據(jù)條件下遷移實(shí)戰(zhàn)

一、背景描述：

在實(shí)際工作中，有些特殊的場景需要進(jìn)行生產(chǎn)數(shù)據(jù)遷移，并且生產(chǎn)環(huán)境的數(shù)據(jù)量非常大，比如上10億數(shù)據(jù)，并且遷移實(shí)施的時間并有所限制，比如9小時內(nèi)、48小時內(nèi)，必須恢復(fù)生產(chǎn)。
并且在這么龐大的數(shù)據(jù)量條件下，還要進(jìn)行數(shù)據(jù)過濾調(diào)整！
此種情況下，傳統(tǒng)Java jdbc多線程的技術(shù)路線遠(yuǎn)遠(yuǎn)不能滿足實(shí)際場景的需求。這個時候該怎么辦？

二、核心技術(shù) MySQL FEDERATED 存儲引擎的介紹

技術(shù)簡介：

FEDERATED存儲引擎能讓你訪問遠(yuǎn)程的MySQL數(shù)據(jù)庫而不使用replication或cluster技術(shù)(類似于Oracle的dblink),使用FEDERATED存儲引擎的表,本地只存儲表的結(jié)構(gòu)信息,數(shù)據(jù)都存放在遠(yuǎn)程數(shù)據(jù)庫上,查詢時通過建表時指定的連接符去獲取遠(yuǎn)程庫的數(shù)據(jù)返回到本地。

FEDERATED 存儲引擎架構(gòu)

1 本地服務(wù)器 FEDERATED 存儲引擎的表只存放表的.frm結(jié)構(gòu)文件
2 遠(yuǎn)程服務(wù)器 存放了.frm和數(shù)據(jù)文件
3 增刪改查操作都是通過建立的連接來訪問遠(yuǎn)程數(shù)據(jù)庫進(jìn)行操作,把結(jié)果返回給本地。
4 遠(yuǎn)程數(shù)據(jù)表的存儲引擎為MySQL支持的存儲引擎,如MyISAM,InnoDB等

操作步驟：
遠(yuǎn)程庫:
開啟 FEDERATED 存儲引擎
建立遠(yuǎn)程訪問用戶
授予訪問對象的權(quán)限
本地庫：

FEDERATED 引擎使用注意事項(xiàng)

1、FEDERATED 表可能會被復(fù)制到其他的slave數(shù)據(jù)庫,你需要確保slave服務(wù)器也能夠使用定義在connection中或mysql.servers表中的link的用戶名/密碼 連接上遠(yuǎn)程服務(wù)器。

2、遠(yuǎn)程服務(wù)器必須是MySQL數(shù)據(jù)庫

3、在訪問FEDERATED表中定義的遠(yuǎn)程數(shù)據(jù)庫的表前,遠(yuǎn)程數(shù)據(jù)庫中必須存在這張表。

4、FEDERATED 表不支持通常意義的索引,服務(wù)器從遠(yuǎn)程庫獲取所有的行然后在本地進(jìn)行過濾,不管是否加了where條件或limit限制。

–查詢可能造成性能下降和網(wǎng)絡(luò)負(fù)載,因?yàn)椴樵兎祷氐臄?shù)據(jù)必須存放在內(nèi)存中,所以容易造成使用系統(tǒng)的swap分區(qū)或掛起。

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

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

7、FEDERATED表不支持事務(wù)

8、本地FEDERATED表無法知道遠(yuǎn)程庫中表結(jié)構(gòu)的改變

9、任何drop語句都只是對本地庫的操作,不對遠(yuǎn)程庫有影響

在這個架構(gòu)中：?本地數(shù)據(jù)庫1：這是啟用了Federated存儲引擎的MySQL數(shù)據(jù)庫服務(wù)器。它包含一個定義為Federated類型的表（例如表A），這個表并不在本地存儲數(shù)據(jù)。?表A(Federated)：在本地數(shù)據(jù)庫中的表，其數(shù)據(jù)實(shí)際上是通過遠(yuǎn)程連接映射到另一個數(shù)據(jù)庫的實(shí)體表上。?遠(yuǎn)程連接：當(dāng)對本地的Federated表進(jìn)行操作時，F(xiàn)ederated引擎會通過標(biāo)準(zhǔn)的MySQL客戶端協(xié)議使用TCP/IP網(wǎng)絡(luò)連接到遠(yuǎn)程數(shù)據(jù)庫。?遠(yuǎn)程數(shù)據(jù)庫2：這是一個獨(dú)立的MySQL數(shù)據(jù)庫服務(wù)器，其中包含了實(shí)際的數(shù)據(jù)，即實(shí)體表A。?實(shí)體表A：存在于遠(yuǎn)程數(shù)據(jù)庫的實(shí)際數(shù)據(jù)表，所有對本地Federated表的操作都會通過網(wǎng)絡(luò)傳輸并執(zhí)行于該實(shí)體表上。這樣的架構(gòu)允許應(yīng)用程序像訪問本地表一樣訪問遠(yuǎn)程數(shù)據(jù)庫中的表，從而簡化了跨數(shù)據(jù)庫的數(shù)據(jù)訪問和集成。

FEDERATED 引擎實(shí)戰(zhàn)案例

MySQL FEDERATED存儲引擎提供了一種方法，使得本地MySQL數(shù)據(jù)庫能夠透明地訪問遠(yuǎn)程MySQL服務(wù)器上的表。以下是一個實(shí)際使用FEDERATED引擎的例子：
創(chuàng)建遠(yuǎn)程服務(wù)器上的實(shí)體表
首先，在遠(yuǎn)程MySQL服務(wù)器上創(chuàng)建一個實(shí)體表（假設(shè)遠(yuǎn)程服務(wù)器的IP是
192.168.1.100
，端口是默認(rèn)的3306，數(shù)據(jù)庫名為
remote_db
）：
-- 在遠(yuǎn)程服務(wù)器上執(zhí)行
CREATE DATABASE IF NOT EXISTS remote_db;
USE remote_db;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50)
);

在本地啟用并配置FEDERATED引擎
確保在本地MySQL服務(wù)器上已安裝并啟用了FEDERATED引擎，可以通過如下方式檢查和安裝：
SHOW ENGINES;  -- 檢查FEDERATED是否可用且支持YES
INSTALL PLUGIN federated SONAME 'ha_federated.so';  -- 如果不可用，則安裝插件
-- 配置my.cnf文件（Linux系統(tǒng)下通常是/etc/my.cnf或Windows下的my.ini），添加federated引擎到mysqld段落中，并重啟MySQL服務(wù)

創(chuàng)建本地FEDERATED表映射到遠(yuǎn)程表
接下來，在本地數(shù)據(jù)庫中創(chuàng)建一個FEDERATED表，該表將指向遠(yuǎn)程數(shù)據(jù)庫的
employees
表：
-- 在本地服務(wù)器上執(zhí)行
CREATE DATABASE IF NOT EXISTS local_db;
USE local_db;

CREATE TABLE federated_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50)
) ENGINE=FEDERATED
CONNECTION='mysql://remote_user:remote_password@192.168.1.100:3306/remote_db/employees';

-- `remote_user`和`remote_password`分別是遠(yuǎn)程數(shù)據(jù)庫的用戶名和密碼

現(xiàn)在，當(dāng)你在本地的
federated_employees
表上執(zhí)行查詢時，實(shí)際上就是在遠(yuǎn)程服務(wù)器上的
employees
表上執(zhí)行操作：
-- 本地查詢示例
INSERT INTO local_db.federated_employees (name, position, department) VALUES ('John Doe', 'Manager', 'HR');
SELECT * FROM local_db.federated_employees WHERE department = 'HR';

以上就是一個完整的MySQL FEDERATED引擎實(shí)際使用的例子，通過這種方式，本地數(shù)據(jù)庫可以像操作本地表一樣來操作遠(yuǎn)程數(shù)據(jù)庫中的數(shù)據(jù)。

三、核心思想描述

DBLINK數(shù)據(jù)庫直連的方式數(shù)據(jù)傳輸性能肯定比包含中間商賺差價的傳統(tǒng)JavaJDBC的方式性能快上10倍，但是上章節(jié)也講述了FEDERATED表不支持事務(wù)！則在數(shù)據(jù)遷移過程中，會存在數(shù)據(jù)丟失的情況。這種情況要怎么解決呢？

筆者在2億的數(shù)據(jù)量遷移的過程中，遇到了很多數(shù)據(jù)庫的報錯！丟數(shù)據(jù)的情況！
比如：MYSQL最大數(shù)據(jù)庫包限制，還有遇到一些鎖的錯。

show VARIABLES like '%max_allowed_packet%';

最大配置值1G 所以需要補(bǔ)充分頁機(jī)制，限制每一個批量數(shù)據(jù)包的大??！

所以必須引入分頁機(jī)制，來規(guī)范每個數(shù)據(jù)包的最大值不超過數(shù)據(jù)庫允許的最大值！

四、引入分頁算法

上億的數(shù)據(jù)要進(jìn)行數(shù)據(jù)分頁！
筆者規(guī)定了頁數(shù)為5萬！5萬一個頁，不超過最大的包限制！
分頁的公式，
設(shè)每頁顯示的數(shù)據(jù)個數(shù)為 pageSize，當(dāng)前是第 pageNo 頁，那么就有如下 LIMIT 查詢公式

LIMIT (pageNo - 1) * pageSize, pageSize;

*初始偏移量 (pageNo - 1) * pageSize
備注：理解區(qū)塊
求顯示第3 個數(shù)據(jù)，
是不是應(yīng)該每頁1個，顯示第3頁，（3-1）1，1，所以應(yīng)該是limit 2,1
反算的話：結(jié)果/pageSize+1=pageNo
查詢員工表 employees 中的第 32、33條員工的數(shù)據(jù)

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

實(shí)際案例：
20萬數(shù)據(jù)，5萬一頁
limit 0，50000
limit 50001，50000
limit 100001，50000
limit 150001，50000

將數(shù)據(jù)分頁處理，然后分頁插入！

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分區(qū)機(jī)制

什么是表分區(qū)

通俗地講表分區(qū)是將一個大表,根據(jù)條件分割成若干個小表。
mysql5.1開始支持?jǐn)?shù)據(jù)表分區(qū)。如:某用戶表的記錄超過了600萬條倉儲信息，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當(dāng)然也可根據(jù)其他的條件分區(qū)。

為什么要做表分區(qū)

為了改善大型表以及具有各種訪問模式的表可伸縮性客觀理性，和提高數(shù)據(jù)庫效率

表分區(qū)有哪些優(yōu)點(diǎn)

與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)
通過刪除與增加那些數(shù)據(jù)相關(guān)的分區(qū)，可以很容易的刪除或增加那些數(shù)據(jù)
一些查詢可以得到很好的優(yōu)化
通過跨多個磁盤甚至服務(wù)器來分散數(shù)據(jù)查詢來獲得更大的查詢吞吐量

基本分區(qū)類型

RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)
LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。
HASH分區(qū)∶基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
key分區(qū)：類似于hash分區(qū)，區(qū)別在于key分區(qū)只支持計(jì)算一列或多列。

range分區(qū)

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分區(qū)是按照指定的范圍將數(shù)據(jù)分配到不同的分區(qū)中的方式。以下是一個創(chuàng)建一個RANGE分區(qū)表的例子，假設(shè)我們有一個銷售記錄表，其中包含交易日期，并且我們希望根據(jù)交易年份來分區(qū)：
– 創(chuàng)建一個名為sales的表并進(jìn)行RANGE分區(qū)
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)) (
    -- 分區(qū)定義：2010年及以前的交易記錄放入partition_2010分區(qū)
    PARTITION partition_2010 VALUES LESS THAN (2011),
    
    -- 2011年及以前（不包括2012年）的交易記錄放入partition_2011分區(qū)
    PARTITION partition_2011 VALUES LESS THAN (2012),
    
    -- 類似地，逐年劃分分區(qū)直到某個結(jié)束年份
    PARTITION partition_2012 VALUES LESS THAN (2013),
    PARTITION partition_2013 VALUES LESS THAN (2014),
    ...
    PARTITION partition_2020 VALUES LESS THAN (2021),
    -- 如果想包含所有2021年及以后的記錄，可以設(shè)置一個MAXVALUE分區(qū)
    PARTITION future_sales VALUES LESS THAN MAXVALUE
);

– 插入示例數(shù)據(jù)

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 列的年份進(jìn)行了分區(qū)，每個分區(qū)包含了相應(yīng)年份內(nèi)的所有交易記錄。這樣可以根據(jù)時間范圍快速定位和操作特定年度的數(shù)據(jù)。

將上億的大表，按特有的時間、空間進(jìn)行分塊！在配合分頁機(jī)制！將每個數(shù)據(jù)塊變得大小可控！同時保證大數(shù)據(jù)的傳輸性能！

上億的大表，最終被切割成規(guī)范大小的數(shù)據(jù)塊進(jìn)行遷移！

六、引入主鍵補(bǔ)漏機(jī)制處理遷移過程中丟失的數(shù)據(jù)

DBLINK數(shù)據(jù)庫直連的方式數(shù)據(jù)傳輸性能肯定比包含中間商賺差價的傳統(tǒng)JavaJDBC的方式性能快上10倍，但是上章節(jié)也講述了FEDERATED表不支持事務(wù)！則在數(shù)據(jù)遷移過程中，會存在數(shù)據(jù)丟失的情況。這種情況要怎么解決呢？

所以要引入遷移之后的補(bǔ)漏機(jī)制，將丟的數(shù)據(jù)再補(bǔ)上，直到數(shù)據(jù)一致！
補(bǔ)漏機(jī)制算法很簡單，通過源與目標(biāo)的數(shù)據(jù)庫各個表主鍵進(jìn)行對比即可。

舉例：

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

將映射表不在源表ID的記錄再補(bǔ)充插入！也就是將過程中意外丟失的數(shù)據(jù)再補(bǔ)充下！

七、引入CLICKHOUSE數(shù)據(jù)庫OLAP技術(shù)提升數(shù)據(jù)對比的性能

clickhouse-簡介

? ClickHouse是俄羅斯的Yandex于2016年開源的一個用于聯(lián)機(jī)分析(OLAP:Online Analytical Processing)的列式數(shù)據(jù)庫管理系統(tǒng)(DBMS:Database Management System) , 主要用于在線分析處理查詢（OLAP），能夠使用SQL查詢實(shí)時生成分析數(shù)據(jù)報告。 ClickHouse的全稱是Click Stream，Data WareHouse，簡稱ClickHouse
? ClickHouse是一個完全的列式分布式數(shù)據(jù)庫管理系統(tǒng)(DBMS)，允許在運(yùn)行時創(chuàng)建表和數(shù)據(jù)庫，加載數(shù)據(jù)和運(yùn)行查詢，而無需重新配置和重新啟動服務(wù)器，支持線性擴(kuò)展，簡單方便，高可靠性，容錯。它在大數(shù)據(jù)領(lǐng)域沒有走 Hadoop 生態(tài)，而是采用 Local attached storage 作為存儲，這樣整個 IO 可能就沒有 Hadoop 那一套的局限。它的系統(tǒng)在生產(chǎn)環(huán)境中可以應(yīng)用到比較大的規(guī)模，因?yàn)樗木€性擴(kuò)展能力和可靠性保障能夠原生支持 shard + replication 這種解決方案。它還提供了一些 SQL 直接接口，有比較豐富的原生 client。

優(yōu)點(diǎn)

靈活的MPP架構(gòu),支持線性擴(kuò)展，簡單方便，高可靠性
多服務(wù)器分布式處理數(shù)據(jù) ,完備的DBMS系統(tǒng)
底層數(shù)據(jù)列式存儲,支持壓縮,優(yōu)化數(shù)據(jù)存儲,優(yōu)化索引數(shù)據(jù) 優(yōu)化底層存儲
容錯跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍,其可處理的數(shù)據(jù)級別已達(dá)到10億級別
功能多：支持?jǐn)?shù)據(jù)統(tǒng)計(jì)分析各種場景，支持類SQL查詢，異地復(fù)制部署
海量數(shù)據(jù)存儲,分布式運(yùn)算,快速閃電的性能,幾乎實(shí)時的數(shù)據(jù)分析 ,友好的SQL語法,出色的函數(shù)支持

缺點(diǎn)

不支持事務(wù)，不支持真正的刪除/更新 (批量)
不支持高并發(fā)，官方建議qps為100，可以通過修改配置文件增加連接數(shù)，但是在服務(wù)器足夠好的情況下
不支持二級索引
不擅長多表join 大寬表
元數(shù)據(jù)管理需要人為干預(yù)
盡量做1000條以上批量的寫入，避免逐行insert或小批量的insert，update，delete操作

應(yīng)用場景

1.絕大多數(shù)請求都是用于讀訪問的, 要求實(shí)時返回結(jié)果
2.數(shù)據(jù)需要以大批次（大于1000行）進(jìn)行更新，而不是單行更新；或者根本沒有更新操作
3.數(shù)據(jù)只是添加到數(shù)據(jù)庫，沒有必要修改
4.讀取數(shù)據(jù)時，會從數(shù)據(jù)庫中提取出大量的行，但只用到一小部分列
5.表很“寬”，即表中包含大量的列
6.查詢頻率相對較低（通常每臺服務(wù)器每秒查詢數(shù)百次或更少）
7.對于簡單查詢，允許大約50毫秒的延遲
8.列的值是比較小的數(shù)值和短字符串（例如，每個URL只有60個字節(jié)）
9.在處理單個查詢時需要高吞吐量（每臺服務(wù)器每秒高達(dá)數(shù)十億行）
10.不需要事務(wù)
11.數(shù)據(jù)一致性要求較低 [原子性 持久性 一致性 隔離性]
12.每次查詢中只會查詢一個大表。除了一個大表，其余都是小表
13.查詢結(jié)果顯著小于數(shù)據(jù)源。即數(shù)據(jù)有過濾或聚合。返回結(jié)果不超過單個服務(wù)器內(nèi)存大小

筆者將數(shù)據(jù)對比的任務(wù)交給clickhouse去對比，數(shù)據(jù)性能又提升幾倍！

八、個人總結(jié)

筆者將個人真實(shí)生產(chǎn)生活案例給朋友們分享！
解決這個實(shí)際問題用到了比較寬的技術(shù)棧，DBA數(shù)據(jù)庫技術(shù)，JAVA技術(shù)，OLAP技術(shù)！算法！需要工程師的技術(shù)廣度，與探索精神！
筆者是拿數(shù)據(jù)倉庫這套思路來解決大數(shù)據(jù)遷移的瓶頸，這套解決方案確實(shí)比傳統(tǒng)方法JDBC快幾十倍！生產(chǎn)中真正驗(yàn)證過的。

作者本人簡介：現(xiàn)任國內(nèi)某大型軟件公司大數(shù)據(jù)研發(fā)工程師、MySQL數(shù)據(jù)庫DBA，軟件架構(gòu)師。直接參與設(shè)計(jì)國家級億級別大數(shù)據(jù)項(xiàng)目。并維護(hù)真實(shí)企業(yè)級生產(chǎn)數(shù)據(jù)庫300余個。緊急處理數(shù)據(jù)庫生產(chǎn)事故上百起，挽回數(shù)據(jù)丟失所操作的災(zāi)難損失不計(jì)其數(shù)。

文章來源地址http://www.zghlxwxcb.cn/news/detail-858573.html

到了這里，關(guān)于高級DBA帶你處理Mysql數(shù)據(jù)庫10億大數(shù)據(jù)條件下遷移實(shí)戰(zhàn)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！