???您好，我是賈斯汀，這是我寫了蠻久的一套作業學習實用的資料庫資料遷移實戰演練+性能優化方案~???
???也希望看到最后覺得有幫助的小伙伴，在文末或者點擊這里，直接跳到文章末尾，關注一下博主哦~???

北海僑港（圖）

【實戰前言】

（1）不管你是學生，還是已經作業了的小伙伴，可能你在過去、現在或者未來，會遇到這樣的問題，公司/專案用的是Oracle/DB2/MySQL等關系型資料庫，因公司發展需求，需要完成舊資料庫資料安全遷移到新資料庫的重要使命，新舊資料庫可能是同一種型別的資料庫，也可能是不同型別的資料庫，相同型別資料庫還好，比如都是MySQL資料庫，那么你主要只需要考慮如何將資料安全、高效的完成遷移就好，而不同型別的資料庫，比如從DB2遷移資料到MySQL，這種情況就需要在進行資料遷移之前，先按照新的資料庫MySQL的建表規范，正確完成資料表的重建作業~

（2）本文主要分享我個人在實際作業當中，==如何使用Kettle這款基于純Java實作（意味著擴平臺特性，也就是Windows/Linux等作業系統通用）的開源ETL資料挖掘工具，經過性能優化（性能是默認效率的5 ~ 10倍以上）之后，實作新舊資料庫之間資料的安全高效遷移~

（3）我個人也是作業用到，一開始完成UAT測驗環境模擬遷移DB2資料庫一千萬左右資料量到MySQL資料庫的程序，到最后在實際生產環境安全高效完成五千萬資料量從DB2遷移到MySQL的方案落實，不過由于個人學習環境限制，就不裝DB2資料庫了，本文將以本地MySQL（模擬舊庫）遷移資料到遠程MySQL（模擬新庫），進行實戰演練，原理是一樣的，要說區別主要在于資料庫型別不同，在進行資料遷移之前，需要先按規范建立好新資料庫的相關庫表~

（4）Kettle腳本的制作、測驗以及性能優化這部分的作業，主要在Windows下通過可視化界面來完成，實際的UAT測驗環境以及生產環境資料庫服務器大多都是在Linux的，因此Linux也需要搭建一套Kettle環境，并且將在Windows下性能優化好的Kettle腳本，放到Linux環境，同時如果資料量非常大的話（億級以上資料量），還可以根據大表制作多個Shell腳本來執行準備好的Kettle作業腳本，利用更良好的CPU性能并發執行腳本，在單個腳本執行性能瓶頸的基礎上再次成倍數提高資料遷移效率，更高效完成舊庫資料遷移到新庫，節省實際投產時的時間成本~

前言廢話有點多了，哈哈哈，進入正文吧~

---

測驗庫表及資料

（1）創建測驗庫表

分別在Windows（模擬舊庫環境）及Linux（模擬新庫環境），創建一個測驗庫test以及兩張測驗庫表test.demo_info、test.demo_info2，這里為了方便測驗，我兩張表除了表名不一樣，其他欄位都一樣，測驗表的主鍵為ID，使用了auto_increment設定主鍵從1開始自增長，MySQL中int型別占用4個byte位元組，即最大數值是(2^31)-1即2147483647，大概二十多億，數值夠大，自增長主鍵實際使用問題不大~

– 建庫測驗庫

create database if not exists test default character set utf8 collate utf8_general_ci;
use test;

– 創建測驗表demo_info

use test;
create table test.demo_info(
	id int(7) primary key not null auto_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info add index index_name(name);
alter table test.demo_info add index index_age(age);
alter table test.demo_info add index index_name_age(name,age);

alter table test.demo_info comment '測驗表';
alter table test.demo_info modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性別:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年齡';

– 創建測驗表demo_info2

create table test.demo_info2(
	id int(7) primary key not null auto_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info2 add index index_name(name);
alter table test.demo_info2 add index index_age(age);
alter table test.demo_info2 add index index_name_age(name,age);

alter table test.demo_info2 comment '測驗表2';
alter table test.demo_info2 modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info2 modify column name varchar(255) not null comment '姓名';
alter table test.demo_info2 modify column sex char(1) not null comment '性別:1-男,0-女';
alter table test.demo_info2 modify column age int(3) comment '年齡';

（2）構建測驗資料

只需要插入Windows（模擬舊庫環境）資料表的測驗資料，Linux（模擬新庫環境）不需要，我們的目的是要使用Kettle遷移資料到Linux（模擬新庫環境）資料表的~
這里構建測驗資料很簡單，因為兩張資料表的主鍵ID設定了自增長，直接新建文本，編輯文本另存為后綴.del檔案，再使用文本編輯器（notepad++ yyds 僅個人推薦，非廣告，哈哈哈！！！）編輯內容，這里先直接復制個不含ID的10w條資料（注意欄位資料間的特殊分隔符是0x0f，如下圖特殊符號所示）到檔案中~

通過MySQL的load data infile語法指定欄位快速插入資料，用這些資料，先來簡單的進行資料遷移的測驗，后面性能優化之后再用100w、1000w更多的資料量來進行資料遷移測驗，當然實際環境的話最好按你們實際資料庫大概有多少資料量，去構建多少的測驗資料~

這里提供下，Kettle資料遷移10w、100w、1000w 測驗del資料檔案，也可以可直接下載~
csdn 下載1~
mpan 下載2~ 提取碼：jj6l

說明：想學習和了解MySQL的load data infile匯出資料的語法和使用技巧的話，可以先看下我的這篇文章學習下：

MySQL如何使用load data infile、into outfile高效匯入匯出資料…

先執行這兩行命令，匯入10w條資料（性能優化前測驗使用）：

load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

通過load data infile分別匯入兩張表的資料，還挺快的，單表不到1秒完成10w條資料匯入~

匯入100w條資料（性能優化后測驗再使用）：

load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

匯入100w條資料（性能優化后測驗再使用）：

load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

測驗庫表及資料構建完成，接下來進入正文學習如何制作Kettle腳本及性能優化的實戰演練吧~

一、Kettle環境搭建

1.1 Kettle下載及安裝

Kettle安裝包官方下載 https://sourceforge.net/projects/pentaho/files/
我這里以最新版本pdi-ce-9.2.0.0-290.zip為例，直接下載zip壓縮包后解壓得到目錄data-integration即可~

這里通過雙擊Spoon.bat即可~
不過，在運行之前，還是要先裝好相關相關資料庫的連接驅動包，不然無法連接到資料庫~

1.2 驅動包下載

Kettle是基于純Java實作的，通過JDBC與資料庫建立連接，連接程序需要依賴資料庫連接驅動jar包，這里提供幾種常用的驅動包的官方下載地址如下：
官方Oracle Java驅動包下載： https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
官方DB2 Java驅動包下載：https://www.ibm.com/support/pages/node/382667
官方MySQL Java驅動包下載： https://dev.mysql.com/downloads/connector/j/

我這里下載MySQL的驅動包，選擇哪個版本呢？~

1.3 驅動包版本

關于驅動包的版本選擇，這個要根據實際相關資料庫的版本去選擇對應驅動包版本下載，這里我以MySQL資料庫驅動包及版本下載為例~
進入上面給的MySQL官方下載地址，可以看到最新的驅動是8.0.26的驅動（下拉選Platform Independent）：

這里說明一下，只要你的MySQL是5.6及以后的資料庫版本，直接下載8.0+的最新驅動包mysql-connector-java-8.0.26即可，不一定非要MySQL 5.7就下5.7的驅動包，而且MySQL官方檔案也說明建議MySQL 5.6以后的升級驅動包為8.0+的，如下：

即MySQL驅動包8.0+版本能夠兼容MySQL5.6及之后的所有版本的資料庫連接~
如果你使用的MySQL低于 5.6版本的，比如MySQL 5.5，驅動包8.0+是不兼容的，無法完成資料庫的連接~
這時，只需要去歷史版本Archives下載對應的5.1.+版本的驅動包即可~

解壓下載得到的zip壓縮包，得到官方MySQL連接驅動jar包~

1.4 驅動包安裝

將下載得到的MySQL資料庫連接驅動包mysql-connector-java-8.0.26.jar放到kettle如下目錄：

data-integration\lib

如果你覺得不夠穩妥的話，比如放到Linux會不會加載不到驅動，那么以下這幾個目錄，都放驅動包，妥妥的：

\data-integration\libswt
\data-integration\libswt\osx64
\data-integration\libswt\win64

二、Kettle使用

2.1 Windows

Windows下，主要通過運行Spoon.bat，打開Kettle客戶端可視化操作頁面，創建資料遷移用到的ktr轉換腳本、kjb作業腳本（轉換和作業后面會介紹），以及相關資料庫連接配置、讀寫性能優化配置等，配置完成后最后一般都是上傳kettle腳本到Linux上執行，當然也可直接在Windows下Kettle客戶端可視化頁面直接執行轉換或作業腳本，完成資料庫之間的資料遷移，但是生產一般是在Linux，而且Windows執行資料遷移的效率一般要比Linux慢一些~

2.1.1 資料庫連接測驗

雙擊\data-integration\Spoont.bat腳本運行~

視圖主物件樹 -> 轉換 ->右鍵新建 -> 直接快捷鍵Ctrl + S另存為test.ktr（自定義后綴，這里建議使用.ktr）
選中DB連接，按如下圖，操作驗證相關資料庫是否能正確連接，這里以MySQL資料庫為例~
注意：兩個DB連接，都要右鍵，設定為共享！！！

本地資料庫連接可以使用自己的IP，方便后面Linux中模擬兩臺IP服務器之間測驗：

幾種導致資料庫連接報錯的情況：
…
（1）缺少驅動或者無法正確加載到驅動都會報錯，提示：
Driver class org.gjt.mm.mysql.Driver could not be found…**
…
（2）遠程連接MySQL的用戶(root)，沒有被放開遠程連接的權限，，則會報錯如下：
Access denied for user ‘root’@’119.168.xxx.xxx’ (using password: YES)
…
（3）配置連接引數不對，會報錯如下：
Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed.
…
缺少驅動的具體就不說了，直接下載資料庫驅動包放到\data-integration\lib目錄下即可~
…
MySQL放開用戶的遠程連接（flush重繪權限生效）方式如下：
mysql> select user,host from mysql.user;
mysql> update mysql.user set host='%' where user = 'root';
mysql> flush privileges;
…
同樣如果需要在Kettle中建立Oracle、DB2連接時，如果沒有驅動也會報同樣類似的錯誤，這時需要下載資料庫連接驅動包并正確安裝驅動包即可~

2.1.2 Kettle作業和轉換

使用Kettle進行資料遷移的程序中，主要有兩個專業名詞即轉換（Kettle Transformation）和作業（Kettle Job）~

名詞	說明
轉換	一般檔案后綴命名為.ktr ，單表遷移資料，構建表輸入（讀取資料），表輸出（寫入資料），Linux下使用kitchen.sh腳本呼叫執行
作業	檔案后綴為.kjb，可以關聯多個.ktr執行，實作多表（串行/并行）遷移資料，Linux下使用pan.sh腳本呼叫執行

2.1.2.1 創建Kettle轉換

在前面資料庫連接測驗時，新建的test.ktr就是一個Kettle轉換檔案，只不過前面只是測驗了DB連接是否正常，至于表輸入和表輸出的相關配置，實作資料表資料遷移的相關配置還沒作具體說明~

選項	說明
表輸入	簡單理解為從指定的資料表（舊庫）讀取資料的程序~
表輸出	簡單理解為插入資料到指定的資料表（新庫）的程序~

（1）同前面創建test.ktr方式，創建demo_info資料表對應的轉換檔案demo_info.ktr
（2）雙擊表輸入進行編輯，如下圖：

（3）雙擊表輸出進行編輯，如下圖：

（4）同樣創建測驗表demo_info2對應的轉換檔案demo_info2.ktr

2.1.2.2 創建Kettle作業

（1）完成kjb作業的創建，并關聯demo_info表的ktr轉換，作業執行程序為 Start -> 轉換 -> 成功，如下圖：

注意這里Transformation一般建議使用相對路徑，移植性比較高，如：
${Internal.Entry.Current.Directory}/demo_info.ktr

表示kjb會加載跟自己同目錄的ktr檔案，建議不要寫死ktr的絕對路徑，容易出錯~

（2）同樣將測驗表demo_info2的轉換也添加到作業中~

最后，快捷鍵Ctrl + S或者點擊左上角保存，保存得到kjb作業檔案（跟ktr轉換放同一個目錄下）~

2.1.3 資料遷移測驗（未優化）

前面已通過在Windows下可視化來創建好Kettle資料遷移需要用到的作業和轉換了，測驗環境可以直接在Kettle客戶端點擊執行轉換（單表）或作業（多表）的運行按鈕，完成資料的遷移即可~

2.1.3.1 執行轉換測驗（單表）

建議在測驗每個表的轉換時，先少量資料，驗證每個轉換的正確性，最后執行作業時，關聯所有轉換完成多個資料表的全部資料遷移~

我這里先正常用前面插入的10w條記錄，測驗看看Kettle不做任何性能優化，單表demo_info資料遷移的效率如何~

通過轉換單表資料遷移的程序…

最后看到，Windows環境下，Kettle默認不做任何優化，單表資料遷移10w條資料大概100條左右/秒，總耗時16分左右，汗！！！雖然也跟我Linux服務器（目標資料庫）配置有關，就單核1G，但主要還是沒做性能優化~

同樣，測驗下demo_info2表的轉換是否正確，不過這里就不再測10w條記錄了，太慢了，雙擊修改下demo_info2表輸入，select查詢陳述句中加個limit 0,100測驗100條資料遷移，驗證轉換正確性即可~

驗證轉換demo_info2.ktr沒問題~

也可以檢查下Linux（模擬新庫環境）測驗表demo_info2的資料是否是100條~

2.1.3.2 執行作業測驗（多表）

把前面通過轉換遷移到Linux（模擬新庫環境）資料表demo_info和demo_info2的資料先清了，并重置ID從1開始自增~

- - Linux下mysql命令列執行：

use test;
delete from test.demo_info;
alter table test.demo_info auto_increment 1;

delete from test.demo_info2;
alter table test.demo_info2 auto_increment 1;

重新來測驗測驗下通過作業關聯兩個轉換的多表的資料遷移（ 資料量比較大的話，建議先配置每個轉換的SQL陳述句查詢少量資料遷移，先驗證完整作業執行的正確性）…

這里不用測驗也知道，因為都是沒做任何性能優化，因此跟前面通過轉換demo_info.ktr遷移demo_info單表10w條資料的耗時沒有任何區別，只不過是串行的加了一張表，耗時增加了一倍多一些~~

這里有時間也跑一下吧，花了37分鐘遷移20w條資料，還是大概90條記錄/秒，汗！！！前面16分鐘10w條資料，100條左右/秒~

雖然也跟個人電腦及服務器的CPU性能以及網路傳輸能力有關，但這些硬體條件有時候沒法改變，接下來會從軟體層面來進行優化，相信會有飛一樣的感覺！！！~~

2.1.4 資料遷移（性能優化）

通過前面不做任何性能優化的Kettle，在做資料遷移時，效率真的是非常低下，接下來對Kettle資料遷移優化，做完優化，整體遷移效率至少提升5 ~ 10倍以上的效率，主要從以下幾個方面的優化：

• 優化Kettle作業
• 優化Kettle轉換
• 化Kettle連接DB引數
• 優化JVM記憶體大小
• 優化目標表的欄位索引（臨時洗掉）

2.1.4.1 優化Kettle作業

（1）優化Kettle作業
第一步：統計資料量比較大的表
統計資料量比較大的表，將這些表作為并發執行的第一張表，其他小資料量的表平均放在后面即可，這里假設我們構建的兩張資料表demo_info和demo_info都是兩張大表~

第二步：重新改造作業為并發執行轉換~
把原來串聯執行的demo_info和demo_info2改成并發執行~

第三步：Kettle -> kjb作業檔案 -> 選中"Start" -> “右鍵” -> Run next Entries in parallel -> 確定(I understand)，完成并發生效，如圖即為并發執行：

2.1.4.2 優化Kettle轉換

（1）加大提交記錄數
加大每一個表對應的ktr轉換的表輸出，每次的提交記錄數~
加大步驟：Kettle -> ktr轉換檔案 -> 雙擊表輸出 -> 提交記錄數量: 10000 (默認1000，范圍1000~50000，百千萬級大表，建議最大設定為50000即可，最大不要超過65535！！)

2.1.4.3 優化Kettle連接DB引數

（1）使用連接池
配置步驟：kettle -> ktr轉換檔案 -> DB連接 -> 雙擊資料庫連接 ->連接池~
比如：
設定連接池初始化大小：50 最大空閑：151 以及勾選關鍵的幾個引數并賦值~

連接池的相關引數要配置多少合適呢？
這個可以按你們工程jdbc.properties資料庫配置中的引數值來填寫以及根據自己資料庫當前配置的max_connections最大連接數來確定~

注意：關于max_connections最大連接數，MySQL 5.7版本默認151，最大可設定為2的14次方=``16384
可以查看下自己的MySQL服務配置的最大連接數是多少：
mysql > show variables like '%max_connections%';

當然有些小伙伴說，資料庫最大連接數多少咱也不懂，咱也不敢改啊，OK，沒問題，如果你不敢改，那就使用資料庫默認的最大連接數，起碼默認最大連接數也有151，只不過可能會慢一些~

如果你有權限，想讓自己生產的資料遷移效率高一些，還是建議申請修改一下最大連接數，默認的太小了，可以通過修改MySQL的配置my.ini（Linux是my.cnf），加大MySQL的最大連接數，在[mysqld]組下添加或者修改以下內容：
max_connections=2000

Windows下直接net stop mysql/ net start mysql重啟生效MySQL服務~
Linux下可能會不生效，若不生效則需要配置Linux作業系統的limits.conf組態檔~
vi /etc/security/limits.conf
在最后加入以下MySQL服務配置資訊：

mysql hard nofile 65535
mysql soft nofile 65535

查看Linux最大連接顯示是否設定成功~

ulimit -n

重啟MySQL服務后，查看Linux下最大連接數是否設定成功~

（2）優化DB讀寫引數

– 表輸入（讀）核心引數優化
雙擊DB連接（讀庫），對選項下的相關引數進行配置：

- - 引數說明

引數及賦值	引數說明
defaultFetchSize=10000	每次與資料庫互動，讀多少條資料加入記憶體中快取，不設定默認把所有資料讀取出來，容易記憶體溢位（OOM），我這里設定10000，大表CPU性能高建議設定最大50000，不能超過65535
useServerPrepStmts=true	是否在使用服務端的預編譯陳述句，true表示以占位符的方式發送SQL到服務端進行拼接
cachePrepStmts=true	是否客戶端快取預處理陳述句
useCursorFetch=true	是否允許部分資料到客戶端就進行處理，如果為false表示所有資料到達客戶端后，才進行處理

引數不在于多，在于使用幾個簡單高效的引數即可，其他引數可以查看MySQL性能優化官方檔案：
https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html

– 表輸出（寫）核心引數優化
雙擊DB連接（寫庫），對選項下的相關引數進行配置：

- - 引數說明

引數及賦值	引數說明
defaultFetchSize=5000	每次與資料庫互動，從記憶體中讀取多少條資料寫入資料表，不設定默認把所有資料寫入， rewriteBatchedStatements設定true，該引數會失效
rewriteBatchedStatements=true	是否開啟批量寫入，true表示開啟，原多條insert變成單條insert執行
useServerPreStmts=false	是否使用服務端預編譯，設定為false，表示在客戶端編譯好
useCompression=true	是否使用壓縮，使用壓縮優化客戶端與服務端傳輸效率
useCursorFetch=true	是否允許部分資料到客戶端就進行處理，如果為false表示所有資料到達客戶端后，才進行處理

2.1.4.4 優化JVM記憶體大小

修改Kettle安裝目錄data-integration/Spoon.bat（Linux是Spoon.sh）檔案中的PENTAHO_DI_JAVA_OPTIONS引數中的JVM相關引數值~
默認如下：
PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m"

– JVM記憶體優化說明

引數	說明
-Xms	堆區最小記憶體，默認物理記憶體的1/64，建議-Xms、-Xmx設定成一樣，避免每次GC后調整堆區大小
-Xmx	堆區最大記憶體，默認物理記憶體的1/4 ，建議最大值設定為實際可用物理記憶體的3/4即可，不要超過80%，不過有個度，32位作業系統有限制，Winows 32限制2-3G，Linux限制2-3G~ ，64位作業系統不限制，取決于實際可用的物理記憶體~
-Xmn	新生代記憶體大小，JDK1.8 新生代:年老代=1:3 ，官方建議設定為最大堆區記憶體值的3/8，不知道這里不配置也行~
-XX:MaxPermSize	永久代最大記憶體，默認64m，JDK1.8取消了永久代，這里該引數就不要配置了，配置了也會失效，JDK1.7及之前版本，默認64m，建議加大一些，不然64m隨時發生OutOfMemoryError記憶體溢位~

（1）Windows配置JVM引數
查看物理記憶體~

理論上建議設定的JVM最大堆區記憶體值（8g * 3/4 = 6g即 6 * 1024m = 6144m）：
PENTAHO_DI_JAVA_OPTIONS="-Xms6144m" "-Xmx6144m"

不過我本地電腦實際物理記憶體就30%（2g）左右了，配置再大也沒用，再稍微優化下，勉強配置個3g=3072m最大堆區記憶體吧：

PENTAHO_DI_JAVA_OPTIONS="-Xms3072m" "-Xmx3072m"

（2）Linux配置JVM引數
查看Linux的記憶體大小（默認單位KB）：

cat /proc/meminfo |grep 'MemTotal'

cat /proc/meminfo |grep 'MemFree'

或（更簡單的）

free -m

可以看到我的"高性能"單核1g Linux服務器，就剩余83M空閑記憶體！！！！對寫資料效率影響大！

不過一般生產的機器基本標配都是4G或8G起步了吧，像我當時做資料遷移時，UAT環境就MySQL服務器16G，生產環境MySQL服務器的物理記憶體是32G（32768M，33554432KB，三千多萬KB，我們老板，有錢！！！），按堆區最大記憶體設定為物理記憶體的1/4，年輕代為最大堆區的3/8的話，可以設定的Kettle的JVM記憶體對應配置為：
-Xms24g -Xmx24g -Xmn9g -XX:MaxPermSize=1024m
不過單個作業執行，肯定用不到那么大記憶體，而且要考慮可能并發執行多個作業，建議單個作業最大配置個3g就OK了~

2.1.4.5 優化目標表的欄位索引（臨時洗掉）

在目標庫建立庫表時，先不要創建表的相關非主鍵欄位索引，如果已經創建的，建議先臨時洗掉相關表（新庫）的欄位索引，完成資料遷移之后，再重新建立欄位索引~

MySQL建立欄位索引，用得好會給查詢的速度有大大的提升，但是進行插入、修改資料時，MySQL也需要進行動態的維護索引，比較消耗性能，因此在做資料遷移時建議先臨時洗掉索引，特別是百千萬級大表，如果存在字串組合索引，在做資料遷移插入資料時效率非常低~

– 查看指定表的索引

show index from test.demo_info;

前面我們創建測驗表的時候，也建立了索引，因此需要先洗掉索引~

– 臨時洗掉非主鍵索引（資料遷移前）

alter table test.demo_info drop index index_name;
alter table test.demo_info drop index index_age;
alter table test.demo_info drop index index_name_age;

alter table test.demo_info2 drop index index_name;
alter table test.demo_info2 drop index index_age;
alter table test.demo_info2 drop index index_name_age;

– 重新建立非主鍵索引（資料遷移完成后）

alter table test.demo_info add index index_name(name);
alter table test.demo_info add index index_age(age);
alter table test.demo_info add index index_name_age(name,age);

alter table test.demo_info2 add index index_name(name);
alter table test.demo_info2 add index index_age(age);
alter table test.demo_info2 add index index_name_age(name,age);

2.1.5 重做資料遷移（性能優化后）

完成關鍵的幾個方面的性能優化后，接下來就是見證奇跡的時候了，來遷移下20w條資料，看看效率如何~
（1）測驗遷移兩張表20w條資料
執行作業~

16秒！！！！兩張表20w條資料，大概12500條/秒~
前面我們沒做任何優化前，單表10w條資料，要16分鐘，100條/秒左右，兩張表20w條資料，要37分鐘，90條/秒左右~

性能直接飆升120倍以上？？ 當然實際肯定不會提高那么高倍數的，只是資料量太少了，接下來再測一下200w條資料量遷移效率如何~

（2）測驗遷移兩張表200w條資料~
清理Windows本地資料：

delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;

同樣，清理Linux遠程資料：

delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;

Windows本地兩張表分別匯入100w條資料

load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile匯入資料還是挺快的，單表100w條資料，4秒左右~

完成本地庫兩張表資料匯入，執行重新執行作業，看看還是不是有120倍，如下所示：

耗時2分48秒，兩張表200w條資料，11904條/秒左右，不錯了，不過比我預期的低一些，畢竟我本地Windows配置和Linux遠程服務器的性能比較一般！！！

（3）測驗遷移兩張表2000w條資料~
同樣，來測驗下2000w，資料遷移效率如何~

Windows本地兩張表分別匯入1000w條資料：

load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

兩張表都load data infile匯入資料，單表匯入1000w條資料耗時45 ~ 50秒左右~

執行作業
耗時30分鐘左右，兩張表2000w條資料，大概11111條/秒的速度，個人覺得挺不錯了，最辣雞的配置了，生產好一些的配置，連接池配大一些，JVM引數再加大一些，單個作業能達到了25000+條/秒以上，Linux可以分成4個作業，并發執行，即便每個腳本下降到2w條/秒左右，但總的還是能達到8w條/秒，1分鐘 480w，10分鐘4800w，30分鐘內還是能輕松實作億級資料量的高效遷移的~

三、Linux

3.1 Kettle環境搭建

3.1.1 檢查JDK

Kettle基于純java撰寫，基于JDK環境運行，因此需要檢查下環境~

java -version

如沒有配置好JDK，需先配置好JDK環境~

3.1.2 目錄規劃

目錄位置及命名可以根據實際需要定義，這里建議創建這四個目錄：

mkdir -p $HOME/kettle
mkdir -p $HOME/kettle/kettle_file
mkdir -p $HOME/kettle/kettle_sh
mkdir -p $HOME/kettle/kettle_log
mkdir -p $HOME/kettle/sql

注意：Linux下$HOME表示當前操作用戶的主目錄，如我都是用mysql用戶操作的，主目錄/home/mysql

– 目錄說明

目錄	說明
kettle_file	存放資料遷移準備好的kettle的轉換ktr和作業kjb檔案
kettle_sh	存放Shell腳本，腳本內容是多表，通過kitchen.sh呼叫作業kjb，單表的話，通過pan.sh呼叫轉換ktr~
kettle_log	存放執行腳本以及呼叫Kettle作業和轉換進行資料遷移列印的日志檔案~
sql	存放統計資料量的SQL陳述句檔案~

先上傳kettle作業和轉換檔案到kettle_file目錄下~

3.1.3 kettle安裝包

提前在Windows下往安裝包pdi-ce-9.0.0.0-423.zip的lib目錄中放好資料庫驅動包~

上傳壓縮包到MySQL服務器，如：

$HOME/kettle/

解壓完成安裝：

cd $HOME/kettle/
unzip pdi-ce-9.0.0.0-423.zip

3.1.4 驗證kettle

執行測驗kettle命令：

cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version

分別出現關鍵資訊如下，表明kettle解壓安裝成功！

...
Kitchen - Start of run.
...
Pan - Start of run.
...

如果服務器記憶體不足，執行測驗可能會報錯如下：

關鍵報錯：
Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.

即最低至少要分配715849728 bytes約等于699M的記憶體，才能運行kitchen.sh腳本測驗~

查看了我的單核1gLinux服務器，總物理記憶體才990M左右，剩余記憶體就211M了，汗！！！

通過top命令查看，光是mysqld服務行程就占用了44%，大概占用435M了，基本沒記憶體了~

一般如果生產需要在Linux執行kettle腳本，記憶體肯定不會這么小的~
這種情況，我這里為了方便在Linux簡單測驗，只能先修改data-integration/spoon.sh中的JVM記憶體小一點了，改成128m吧，如下（搜索PENTAHO_DI_JAVA_OPTIONS定位）：

if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
    PENTAHO_DI_JAVA_OPTIONS="-Xms128m -Xmx128m"
fi

實際CPU性能好一些的測驗環境或者生產環境建議改為3g即3072m：

if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
    PENTAHO_DI_JAVA_OPTIONS="-Xms3072m -Xmx3072m"
fi

再次測驗下

cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version

3.2 檢查驅動及網路連接

kettle安裝成功不代表兩臺服務器之間能開始進行資料遷移了，還需要檢查相關資料庫驅動以及新舊資料庫兩臺服務器之間的網路是否連通~
（1）檢查驅動
檢查kettle安裝目錄lib目錄下表是否存在相關資料庫的連接驅動，如MySQL資料庫連接驅動包
data-integration/lib/mysql-connector-java-8.0.26.jar

（2）檢查網路及連接
在新資料庫服務器檢查與舊資料庫服務器的網路連通性以及連接權限指定埠~

– 檢查與舊資料庫服務器的網路是否連通

ping 舊資料庫服務器的IP

– 是否有權限連接到指定埠（如MySQL的3306埠）

telnet 就資料庫服務器的IP 3306

當然如果要在Linux執行telnet Windows的3306埠，需要做以下幾件件事：
第一：配置防火墻入站規則天機3306埠
防火墻未開啟，可以忽略這步~

第二：勾選telnet服務端

第三：開啟telnet服務

3.3 再次性能優化

這一步主要是，準備Shell腳本！！！

（1）主要是針對億級以上的資料量，再次進行性能優化的方案，資料量比較小的可以忽略~
（2）前面通過Windows下的可視化操作等對Kettle進行了優化，可以直接在Kettle客戶端執行作作業或者轉換，完成資料遷移，也可以直接上傳腳本到Linux服務器執行~
（3）實際的測驗環境或生產環境，大多一般都是兩臺遠程Linux服務器之間的資料遷移，如果庫表資料量達到億級以上，這種情況就可以根據每一張大表（如1000w以上）創建一個Shell腳本，每個腳本的內容是執行指定的作業~
（4）如果存在多張大表，則對應創建多個Shell腳本，這樣可以在實際投產時并發的執行Shell腳本，最大化的利用CPU的性能，并發資料遷移，再次成倍數的提高資料遷移效率，進一步節省投產的時間成本~~

億級以上資料量，Linux再次優化大概思路：
（1）假設有100張表，大概1億左右的資料量，統計超過1000w的表有5張~
（2）在Windows下給每張大表（如1000w以上）建一個對應的Kettle作業，這個作業除了關聯那張大表轉換之外，還要再平均關聯其他小表的轉換（比如平均再關聯20個小表）~
（3）不過雖然這里有5張表，但是建議總作業數不要超過4個，每個作業一般分配3g記憶體左右，4個作業總需物理記憶體就是12g左右記憶體，如果你生產記憶體遠遠大于12g，那沒啥問題，建5個作業，對應5個Shell腳本~~
（4）每一個作業的創建按照前面Windows下提的幾個方面優化，特別是并發執行，驗證每個作業的正確性
（5）創建每一個Shell腳本，如demo_info.sh、demo_info2.sh，Shell腳本內容是通過span.sh腳本去呼叫指定的作業
（6）準備好這幾張大表為主的Shell腳本后，就可以在Linux同時執行這幾個Shell腳本并發資料遷移~

為什么不直接一個作業，然后這個作業并發執行多個大表轉換即可？

因為Linux環境下，通過kettle的kitchen.sh腳本執行作業時，只會開啟一個Java行程，JVM的記憶體是有上限的，作業并發再多轉換，記憶體也就那么多，所以還是建議并行執行多個Shell腳本（對應多個作業）執行，意味著記憶體的疊加使用，可以最大化的利用生產比較好的CPU配置，更高效的完成資料的遷移~

當然，如果不想麻煩，就想一個腳本，一個作業，關聯所有轉換也可以，不過需要給Kettle配置JVM最大記憶體調到最優，比如一個作業整這么多轉換~

本文為方便測驗，這里就一個作業，創建一個資料遷移的Shell腳本test.sh進行測驗，內容如下：

#!/bin/sh
#一、使用自定義的JDK版本
export JAVA_HOME=/usr/java/jdk1.8.0.221
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
#二、執行kettle腳本，作業通過 kitchen.sh執行，轉換通過pan.sh執行
#作業（多表）
$HOME/kettle/data-integration/kitchen.sh -file=$HOME/kettle/kettle_file/test.kjb > $HOME/kettle/kettle_log/transTest.log 2>&1 &
#轉換（單表），這里先注釋，如果資料遷移資料總量不一致，排查出哪張表資料不一致，需要單獨遷移這張表，可以把這個放開，前面的作業注釋
#$HOME/kettle/data-integration/pan.sh -file=$HOME/kettle/kettle_file/demo_info.kjb > $HOME/kettle/kettle_log/demo_info.log 2>&1 &

注意JDK等相關目錄改成自己的~
將test.sh腳本上傳到kettle_sh目錄下：

$HOME/kettle/kettle_sh

往下進行資料遷移~

3.4 資料遷移

實際做資料遷移的時間成本，不只是kettle作業腳本的執行時間，你還需要花一些時間進行其他相關操作，比如統計資料遷移前（舊庫）和資料遷移后（新庫）的資料量總量~

（1）先統計舊庫的總資料量
提前準備一個selectCount.sql陳述句檔案，統計所有資料表的資料量，內容如：

select sum(selectCount.ct) as totalRows from (
  select count(*) as ct from test.demo_info
  union all
  select count(*) as ct from test.demo_info2	
) selectCount;

上傳selectCount.sql陳述句檔案到sql目錄下：

$HOME/kettle/sql/

執行SQL陳述句檔案，查看資料量（舊庫）~
mysql > source C:/Users/Administrator/Desktop/selectCount.sql;

（2）執行資料遷移Shell腳本

/bin/sh $HOME/kettle/kettle_sh/test.sh

可以查看Shell腳本呼叫的Kettle作業的執行行程~

ps -ef|grep kjb

測驗時，可以可以通過top命令，查看CPU性能消耗及記憶體使用情況~

（3）實時查看資料遷移日志
Shell腳本test.sh中，有指定日志檔案存放路徑~

tail -f $HOME/kettle/kettle_log/transTest.log

（4）最后統計新庫的總資料量
提前準備一個selectCount.sql陳述句檔案，統計所有新庫，資料表的資料量，內容如：

select sum(selectCount.ct) as totalRows from (
  select count(*) as ct from test.demo_info
  union all
  select count(*) as ct from test.demo_info2	
) selectCount;

執行SQL陳述句檔案，查看資料量~
mysql > source /home/mysql/kettle/sql/selectCount.sql;

（4）資料量不一致需查看日志

vi /home/mysql/kettle/kettle_log

日志中會記錄每張表的表輸入記錄、表輸出記錄，根據這個排查總資料量不一致，是哪張表導致的~

原創不易，覺得有用的小伙伴來個三連（點贊+收藏+評論 ）+關注支持一下，非常感謝~