最佳實踐—如何優化資料導入導出

測試環境

本文檔的測試環境要求如下表：

環境	參數
PolarDB-X版本	polarx-kernel_5.4.11-16282307_xcluster-20210805
節點規格	16核64GB
節點個數	4個

測試用表如下：

CREATE TABLE `sbtest1` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    `c` char(120) NOT NULL DEFAULT '',
    `pad` char(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `k_1` (`k`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`);      

導入導出工具介紹

PolarDB-X常見的資料導出方法有：

• mysql -e指令行導出資料
• musqldump工具導出資料
• select into outfile語句導出資料（預設關閉）
• Batch Tool工具導出資料（PolarDB-X配套的導入導出工具）

PolarDB-X常見的資料導入方法有：

• source語句導入資料
• mysql指令導入資料
• 程式導入資料
• load data語句導入資料
• Batch Tool工具導入資料（PolarDB-X配套的導入導出工具）

MySQL原生指令使用示例

mysql -e指令可以連接配接本地或遠端伺服器，通過執行sql語句，例如select方式擷取資料，原始輸出資料以制表符方式分隔，可通過字元串處理改成

','

分隔，以csv檔案方式存儲，方法示例：

mysql -h ip  -P port -u usr -pPassword db_name -N -e "SELECT id,k,c,pad FROM sbtest1;" >/home/data_1000w.txt
## 原始資料以制表符分隔，資料格式：188092293    27267211    59775766593-64673028018-...-09474402685    01705051424-...-54211554755
mysql -h ip  -P port -u usr -pPassword db_name -N -e "SELECT id,k,c,pad FROM sbtest1;" | sed 's/\t/,/g' >/home/data_1000w.csv
## csv檔案以逗号分隔，資料格式：188092293,27267211,59775766593-64673028018-...-09474402685,01705051424-...-54211554755      

原始資料格式适合load data語句導入資料，使用方法可參考：

LOAD DATA 語句

，示例如下：

LOAD DATA LOCAL INFILE '/home/data_1000w.txt' INTO TABLE sbtest1;
## LOCAL代表從本地檔案導入，local_infile參數必須開啟      

csv檔案資料适合程式導入，具體方式可檢視

使用程式進行資料導入

。

mysqldump工具使用示例

mysqldump工具可以連接配接到本地或遠端伺服器，詳細使用方法請參見

使用mysqldump導入導出資料

• 導出資料示例：

mysqldump -h ip  -P port -u usr -pPassword --default-character-set=utf8mb4 --net_buffer_length=10240 --no-tablespaces --no-create-db --no-create-info --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset  --hex-blob db_name [table_name] > /home/dump_1000w.sql      

• mysqldump導出資料可能會出現的問題及解決方法，這兩個問題通常是mysql client和mysql server版本不一緻導緻的。

1. 1. 問題：mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode''解決方法：添加“--set-gtid-purged=OFF”參數關閉gtid_mode。
   2. 問題：mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version''解決方法：檢視mysqldump --version和mysql版本是否一緻，使用和mysql版本一緻的mysql client。

• 導出的資料格式是SQL語句方式，以Batch Insert語句為主體，包含多條SQL語句，

  INSERT INTO `sbtest1` VALUES (...),(...)

  ，“net_buffer_length”參數将影響batch size大小。
• SQL語句格式合适的導入資料方式：

方法一：souce語句導入資料
source /home/dump_1000w.sql
方法二：mysql指令導入資料
mysql -h ip  -P port -u usr -pPassword --default-character-set=utf8mb4 db_name < /home/dump_1000w.sql      

Batch Tool工具使用示例

Batch Tool是阿裡雲内部開發的資料導入導出工具，支援多線程操作。

• 導出資料：

## 導出“預設值=分片數”個檔案
java -jar batch-tool.jar -h ip  -P port -u usr -pPassword -D db_name -o export -t sbtest1 -s ,
## 導出整合成一個檔案
java -jar batch-tool.jar -h ip  -P port -u usr -pPassword -D db_name -o export -t sbtest1 -s , -F 1      

• 導入資料：

## 導入32個檔案
java -jar batch-tool.jar -hpxc-spryb387va1ypn.polarx.singapore.rds.aliyuncs.com  -P3306 -uroot -pPassw0rd -D sysbench_db -o import -t sbtest1 -s , -f "sbtest1_0;sbtest1_1;sbtest1_2;sbtest1_3;sbtest1_4;sbtest1_5;sbtest1_6;sbtest1_7;sbtest1_8;sbtest1_9;sbtest1_10;sbtest1_11;sbtest1_12;sbtest1_13;sbtest1_14;sbtest1_15;sbtest1_16;sbtest1_17;sbtest1_18;sbtest1_19;sbtest1_20;sbtest1_21;sbtest1_22;sbtest1_23;sbtest1_24;sbtest1_25;sbtest1_26;sbtest1_27;sbtest1_28;sbtest1_29;sbtest1_30;sbtest1_31" -np -pro 64 -con 32
## 導入1個檔案
java -jar batch-tool.jar -h ip  -P port -u usr -p password -D db_name -o import -t sbtest1 -s , -f "sbtest1_0" -np      

導出方法對比

測試方法以PolarDB-X導出1000w行資料為例，資料量大概2GB左右。

方式	資料格式	檔案大小	耗時	性能（行/每秒）	性能（MB/S）
mysql -e指令 導出原始資料	原始資料格式	1998MB	33.417s	299248	59.8
mysql -e指令導出csv格式	csv格式		34.126s	293031	58.5
mysqldump工具（net-buffer-length=10KB）	sql語句格式	2064MB	30.223s	330873	68.3
mysqldump工具（net-buffer-length=200KB）		2059MB	32.783s	305036	62.8
batch tool工具檔案數=32（分片數）			4.715s	2120890	423.7
batch tool工具檔案數=1			5.568s	1795977	358.8

總結：

1. mysql -e指令和mysqldump工具原理上主要是單線程操作，性能差别并不明顯。
2. Batch Tool工具采用多線程方式導出，并發度可設定，能夠極大提高導出性能。

導入方法對比

測試方法以PolarDB-X導入1000w行資料為例，源資料是上一個測試中導出的資料，資料量大概2GB左右。

source語句（net-buffer-length=10KB）		10m24s	16025	3.2
source語句（net-buffer-length=200KB）		5m37s	29673	5.9
mysql指令導入（net-buffer-length=10KB）		10m27s	15948
mysql指令導入（net-buffer-length=200KB）		5m38s	29585
load data語句導入		4m0s	41666	8.3
程式導入batch-1000thread-1		5m40s	29411
程式導入batch-1000thread-32		19s	526315	105.3
		19.836s	504133	100.8
		10.806s	925411	185.1

1. source語句和mysql指令導入方式，都是單線程執行SQL語句導入，實際是Batch Insert語句的運用，Batch size大小會影響導入性能。Batch size和mysqldump導出資料時的“net-buffer-length”參數有關。建議優化點如下：

• • 推薦将“net-buffer-length”參數設定大，不超過256K，以增大batch size大小，來提高插入性能。
  • 使用第三方工具，例如mysqldump，進行mydumper（備份）和myloader（導入）等，可多線程操作。

1. load data語句是單線程操作，性能優于mysql指令和source語句。
2. 程式導入靈活性較好，可自行設定合适的batch size和并發度，可以達到較好性能。推薦batch大小為1000，并發度為16~32。
3. Batch Tool工具支援多線程導入，且貼合分布式多分片的操作方式，性能優異。

總結

1. PolarDB-X相容MySQL運維上常用的資料導入導出方法，但這些方法大多為MySQL單機模式設計，隻支援單線程操作，性能上無法充分利用所有分布式資源。
2. PolarDB-X提供Batch Tool工具，非常貼合分布式場景，在多線程操作下，能夠達到極快的資料導入導出性能。