MySQL Shell import_table資料導入

1. import_table介紹
2. Load Data 與 import table功能示例

2.1 用Load Data方式導入資料
2.2 用import_table方式導入資料

3. import_table特定功能

3.1 多檔案導入(模糊比對)
3.2 并發導入
3.3 導入速率控制
3.4 自定義chunk大小

4. Load Data vs import_table性能對比
5. 技術總結

1. import_table介紹

上期技術分享我們介紹了MySQL Load Data的4種常用的方法将文本資料導入到MySQL,這一期我們繼續介紹另一款更加高效的資料導入工具,MySQL Shell 工具集中的 import_table ,該工具的全稱是 Parallel Table Import Utility ,顧名思義,支援并發資料導入,該工具在MySQL Shell 8.0.23版本後,功能更加完善, 以下列舉該工具的核心功能

基本覆寫了MySQL Data Load的所有功能,可以作為替代品使用

預設支援并發導入(支援自定義chunk大小)

支援通配符比對多個檔案同時導入到一張表(非常适用于相同結構資料彙總到一張表)

支援限速(對帶寬使用有要求的場景,非常合适)

支援對壓縮檔案處理

支援導入到5.7及以上MySQL

2. Load Data 與 import table功能示例

該部分針對import table和Load Data相同的功能做指令示例示範,我們依舊以導入employees表的示例資料為例,示範MySQL Load Data的綜合場景

資料自定義順序導入

資料函數處理

自定義資料取值

## 示例資料如下
[root@10-186-61-162 tmp]# cat employees_01.csv
"10001","1953-09-02","Georgi","Facello","M","1986-06-26"
"10003","1959-12-03","Parto","Bamford","M","1986-08-28"
"10002","1964-06-02","Bezalel","Simmel","F","1985-11-21"
"10004","1954-05-01","Chirstian","Koblick","M","1986-12-01"
"10005","1955-01-21","Kyoichi","Maliniak","M","1989-09-12"
"10006","1953-04-20","Anneke","Preusig","F","1989-06-02"
"10007","1957-05-23","Tzvetan","Zielinski","F","1989-02-10"
"10008","1958-02-19","Saniya","Kalloufi","M","1994-09-15"
"10009","1952-04-19","Sumant","Peac","F","1985-02-18"
"10010","1963-06-01","Duangkaew","Piveteau","F","1989-08-24"

## 示例表結構
 10.186.61.162:3306  employees  SQL > desc emp;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| emp_no      | int           | NO   | PRI | NULL    |       |
| birth_date  | date          | NO   |     | NULL    |       |
| first_name  | varchar(14)   | NO   |     | NULL    |       |
| last_name   | varchar(16)   | NO   |     | NULL    |       |
| full_name   | varchar(64)   | YES  |     | NULL    |       |  -- 表新增字段,導出資料檔案中不存在
| gender      | enum('M','F') | NO   |     | NULL    |       |
| hire_date   | date          | NO   |     | NULL    |       |
| modify_date | datetime      | YES  |     | NULL    |       |  -- 表新增字段,導出資料檔案中不存在
| delete_flag | varchar(1)    | YES  |     | NULL    |       |  -- 表新增字段,導出資料檔案中不存在
+-------------+---------------+------+-----+---------+-------+

2.1 用Load Data方式導入資料

具體參數含義不做說明,需要了解文法規則及含義可檢視系列上一篇文章<MySQL Load Data的多種用法>

load data infile '/data/mysql/3306/tmp/employees_01.csv'
into table employees.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)
set emp_no=@C1,
    birth_date=@C2,
    first_name=upper(@C3),
    last_name=lower(@C4),
    full_name=concat(first_name,' ',last_name),
    gender=@C5,
    hire_date=@C6 ,
    modify_date=now(),
    delete_flag=if(hire_date<'1988-01-01','Y','N');

2.2 用import_table方式導入資料

util.import_table(
    [
        "/data/mysql/3306/tmp/employees_01.csv",
    ],
    {
        "schema": "employees", 
        "table": "emp",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "columns": [1,2,3,4,5,6],                   ## 檔案中多少個列就用多少個序号辨別就行
        "decodeColumns": {
            "emp_no":       "@1",                   ## 對應檔案中的第1列
            "birth_date":   "@2",                   ## 對應檔案中的第2個列
            "first_name":   "upper(@3)",            ## 對應檔案中的第3個列,并做轉為大寫的處理
            "last_name":    "lower(@4)",            ## 對應檔案中的第4個列,并做轉為大寫的處理
            "full_name":    "concat(@3,' ',@4)",    ## 将檔案中的第3,4列合并成一列生成表中字段值
            "gender":       "@5",                   ## 對應檔案中的第5個列
            "hire_date":    "@6",                   ## 對應檔案中的第6個列
            "modify_date":  "now()",                ## 用函數生成表中字段值
            "delete_flag":  "if(@6<'1988-01-01','Y','N')"  ## 基于檔案中第6列做邏輯判斷,生成表中對應字段值
        }
    })

3. import_table特定功能

3.1 多檔案導入(模糊比對)

## 在導入前我生成好了3分單獨的employees檔案,導出的結構一緻
[root@10-186-61-162 tmp]# ls -lh
總用量 1.9G
-rw-r----- 1 mysql mysql  579 3月  24 19:07 employees_01.csv
-rw-r----- 1 mysql mysql  584 3月  24 18:48 employees_02.csv
-rw-r----- 1 mysql mysql  576 3月  24 18:48 employees_03.csv
-rw-r----- 1 mysql mysql 1.9G 3月  26 17:15 sbtest1.csv

## 導入指令,其中對對檔案用employees_*做模糊比對
util.import_table(
    [
        "/data/mysql/3306/tmp/employees_*",
    ],
    {
        "schema": "employees", 
        "table": "emp",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "columns": [1,2,3,4,5,6],                   ## 檔案中多少個列就用多少個序号辨別就行
        "decodeColumns": {
            "emp_no":       "@1",                   ## 對應檔案中的第1列
            "birth_date":   "@2",                   ## 對應檔案中的第2個列
            "first_name":   "upper(@3)",            ## 對應檔案中的第3個列,并做轉為大寫的處理
            "last_name":    "lower(@4)",            ## 對應檔案中的第4個列,并做轉為大寫的處理
            "full_name":    "concat(@3,' ',@4)",    ## 将檔案中的第3,4列合并成一列生成表中字段值
            "gender":       "@5",                   ## 對應檔案中的第5個列
            "hire_date":    "@6",                   ## 對應檔案中的第6個列
            "modify_date":  "now()",                ## 用函數生成表中字段值
            "delete_flag":  "if(@6<'1988-01-01','Y','N')"  ## 基于檔案中第6列做邏輯判斷,生成表中對應字段值
        }
    })
    
## 導入指令,其中對要導入的檔案均明确指定其路徑
util.import_table(
    [
        "/data/mysql/3306/tmp/employees_01.csv",
        "/data/mysql/3306/tmp/employees_02.csv",
        "/data/mysql/3306/tmp/employees_03.csv"
    ],
    {
        "schema": "employees", 
        "table": "emp",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "columns": [1,2,3,4,5,6],                   ## 檔案中多少個列就用多少個序号辨別就行
        "decodeColumns": {
            "emp_no":       "@1",                   ## 對應檔案中的第1列
            "birth_date":   "@2",                   ## 對應檔案中的第2個列
            "first_name":   "upper(@3)",            ## 對應檔案中的第3個列,并做轉為大寫的處理
            "last_name":    "lower(@4)",            ## 對應檔案中的第4個列,并做轉為大寫的處理
            "full_name":    "concat(@3,' ',@4)",    ## 将檔案中的第3,4列合并成一列生成表中字段值
            "gender":       "@5",                   ## 對應檔案中的第5個列
            "hire_date":    "@6",                   ## 對應檔案中的第6個列
            "modify_date":  "now()",                ## 用函數生成表中字段值
            "delete_flag":  "if(@6<'1988-01-01','Y','N')"  ## 基于檔案中第6列做邏輯判斷,生成表中對應字段值
        }
    })

3.2 并發導入

在實驗并發導入前我們建立一張1000W的sbtest1表(大約2G資料),做并發模拟,import_table用 threads 參數作為并發配置, 預設為8個并發.

## 導出測試需要的sbtest1資料
[root@10-186-61-162 tmp]# ls -lh
總用量 1.9G
-rw-r----- 1 mysql mysql  579 3月  24 19:07 employees_01.csv
-rw-r----- 1 mysql mysql  584 3月  24 18:48 employees_02.csv
-rw-r----- 1 mysql mysql  576 3月  24 18:48 employees_03.csv
-rw-r----- 1 mysql mysql 1.9G 3月  26 17:15 sbtest1.csv

## 開啟threads為8個并發
util.import_table(
    [
        "/data/mysql/3306/tmp/sbtest1.csv",
    ],
    {
        "schema": "demo", 
        "table": "sbtest1",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "threads": "8"
    })

3.3 導入速率控制

可以通過 maxRate 和 threads 來控制每個并發線程的導入資料,如,目前配置線程為4個,每個線程的速率為2M/s,則最高不會超過8M/s

util.import_table(
    [
        "/data/mysql/3306/tmp/sbtest1.csv",
    ],
    {
        "schema": "demo", 
        "table": "sbtest1",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "threads": "4",
        "maxRate": "2M"
    })

3.4 自定義chunk大小

預設的chunk大小為50M,我們可以調整chunk的大小,減少事務大小,如我們将chunk大小調整為1M,則每個線程每次導入的資料量也相應減少

util.import_table(
    [
        "/data/mysql/3306/tmp/sbtest1.csv",
    ],
    {
        "schema": "demo", 
        "table": "sbtest1",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4",
        "threads": "4",
        "bytesPerChunk": "1M",
        "maxRate": "2M"
    })

4. Load Data vs import_table性能對比

使用相同庫表
不對資料做特殊處理,原樣導入
不修改參數預設值,隻指定必備參數

-- Load Data語句
load data infile '/data/mysql/3306/tmp/sbtest1.csv'
into table demo.sbtest1
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'

-- import_table語句
util.import_table(
    [
        "/data/mysql/3306/tmp/sbtest1.csv",
    ],
    {
        "schema": "demo", 
        "table": "sbtest1",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "characterSet": "utf8mb4"
    })

5. 技術總結

import_table包含了Load Data幾乎所有的功能
import_table導入的效率比Load Data更高
import_table支援對導入速度,并發以及每次導入的資料大小做精細控制
import_table的導入進度報告更加詳細,便于排錯及時間評估,包括

導入速度
導入總耗時
每批次導入的資料量,是否存在Warning等等
導入最終的彙總報告

MySQL Shell import_table資料導入

1. import_table介紹

2. Load Data 與 import table功能示例

2.1 用Load Data方式導入資料

2.2 用import_table方式導入資料

3. import_table特定功能

3.1 多檔案導入(模糊比對)

3.2 并發導入

3.3 導入速率控制

3.4 自定義chunk大小

4. Load Data vs import_table性能對比

5. 技術總結

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案