PolarDB：如何最佳化資料匯入匯出

測試環境

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命令列匯出資料

• mysqldump匯出工具

• select into outfile語句匯出資料（預設關閉）

• Batch Tool工具匯出資料（PolarDB-X配套的匯入匯出工具）

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

• source語句匯入資料

• MySQL命令匯入資料

• 程式匯入資料

• load data語句匯入資料

• Batch Tool工具匯入資料（PolarDB-X配套的匯入匯出工具）

MySQL原生命令使用樣本

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 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. 問題：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-spryb387va****.polarx.singapore.rds.aliyuncs.com  -P3306 -uroot -pPassword -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匯出1000萬行資料為例，資料量大概2GB左右。

匯入方法對比

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

總結

1. PolarDB-X相容MySQL營運上常用的資料匯入匯出方法，但這些方法大多為MySQL單機模式設計，只支援單線程操作，效能上無法充分利用所有分布式資源。

2. PolarDB-X提供Batch Tool工具，非常貼合分布式情境，在多線程操作下，能夠達到極快的資料匯入匯出效能。