关于mysql:技术分享-MySQL-Shell-importtable-数据导入

4次阅读

共计 6351 个字符，预计需要花费 16 分钟才能阅读完成。

作者：余振兴

爱可生 DBA 团队成员，相熟 Oracle、MySQL、MongoDB、Redis，最近在盘 TiDB，善于架构设计、故障诊断、数据迁徙、灾备构建等等。负责解决客户 MySQL 及我司自研 DMP 数据库治理平台日常运维中的问题。热衷技术分享、编写技术文档。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

上期技术分享我介绍了 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

该局部针对 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    |       |  -- 表新增字段, 导出数据文件中不存在
+-------------+---------------+------+-----+---------+-------+

具体参数含意不做阐明，须要理解语法规定及含意可查看 系列上一篇文章《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');

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 分独自的 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 列做逻辑判断, 生成表中对应字段值
        }
    })

在试验并发导入前咱们创立一张 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"
    })

能够通过 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"
    })

默认的 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"
    })

应用雷同库表
不对数据做非凡解决，原样导入
不批改参数默认值，只指定必备参数

-- 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"
    })

能够看到，Load Data 耗时约 5 分钟，而 import_table 则只有不到一半的工夫即可实现数据导入，效率高一倍以上（虚拟机环境磁盘 IO 能力无限状况下）

import_table 蕴含了 Load Data 简直所有的性能
import_table 导入的效率比 Load Data 更高
import_table 反对对导入速度，并发以及每次导入的数据大小做精密管制
import_table 的导入进度报告更加具体，便于排错及工夫评估，包含：
- 导入速度
- 导入总耗时
- 每批次导入的数据量，是否存在 Warning 等等
- 导入最终的汇总报告

正文完

mysql

发表至： mysql

2021-04-27

0

关于mysql:MySQL-高频面试题都在这了

关于mysql:故障分析-一条du命令引发的内存不足报警

关于mysql:MySQL数据库常用概念和数据类型

关于mysql:mysql创建12个月的日期视图

关于java:零基础学java的最佳学习方法和最全java知识大纲含100G学习资料

关于mysql:技术分享-MySQL-Shell-importtable-数据导入

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. 技术总结

站内搜索