作者：余振兴
爱可生 DBA 团队成员，相熟 Oracle、MySQL、MongoDB、Redis，最近在盘 TiDB，善于架构设计、故障诊断、数据迁徙、灾备构建等等。负责解决客户 MySQL 及我司自研 DMP 数据库治理平台日常运维中的问题。热衷技术分享、编写技术文档。
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
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.empcharacter set utf8mb4fields 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.sbtest1character set utf8mb4fields 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 能力无限状况下）
5. 技术总结
- import_table 蕴含了 Load Data 简直所有的性能
- import_table 导入的效率比 Load Data 更高
- import_table 反对对导入速度，并发以及每次导入的数据大小做精密管制
import_table 的导入进度报告更加具体，便于排错及工夫评估，包含：
- 导入速度
- 导入总耗时
- 每批次导入的数据量，是否存在 Warning 等等
- 导入最终的汇总报告