MySQL Shell import_table数据导入
1. import_table介绍
这一期我们介绍一款高效的数据导入工具,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的综合场景
- 数据自定义顺序导入
- 数据函数处理
- 自定义数据取值
示例数据如下：
1 2 3 4 5 6 7 8 9 10 11 | [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" |
示例表结构：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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方式导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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方式导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 多文件导入(模糊匹配)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | ## 在导入前我生成好了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个并发.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ## 导出测试需要的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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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,则每个线程每次导入的数据量也相应减少
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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性能对比
- 使用相同库表
- 不对数据做特殊处理,原样导入
- 不修改参数默认值,只指定必备参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- 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能力有限情况下)
以上就是MySQL Shell import_table数据导入详情的详细内容，更多关于import_table数据导入的资料请关注自学编程网其它相关文章！
- 本文固定链接: https://zxbcw.cn/post/221201/
- 转载请注明：必须在正文中标注并保留原文链接
- QQ群： PHP高手阵营官方总群(344148542)
- QQ群： Yii2.0开发(304864863)