Skip to content

GitLab

mysqlimport · Changes

Page history
搬运lake.wiki中的内容 authored by 吴一博's avatar 吴一博
Show whitespace changes
Inline Side-by-side
Showing with 128 additions and 0 deletions
best_practice/mysqlimport.md 0 → 100644
View page @ 06367b0a
mysqlimport是对LOAD DATA INFILE命令的包装实现，因此load data infile可以实现更多的设置
# 字段分隔符（FIELDS TERMINATED BY）
* 默认为'\t'
# 行分割符（LINES TERMINATED BY）
* 默认'\n'
# 转义字符（ESCAPED BY）
* 默认'\'
# 封装字符(ENCLOSED BY)
* 默认''
# NULL值
* \N导入mysql表中时为NULL值
# 注意事项
* 使用默认或自定义分割符时，一定要将字段内容中的分割符转义，转义字符也要转义，否则会导致字段错乱
```python
datas = [["aba1d47bb64fd86f7c70cd0c8ad066eb", "西安市莲\t湖区莱丽奥\"洗涤用品商店", 1, datetime.datetime(2011, 1, 1), 1.1, {}, None],
["fe0bd55c7bf779b71ff93b288fd7f9a9", "鼓楼区萘,良文具店\\", 2, datetime.datetime(2012,2,2), 2.2, (), True],
["8c3b9637cd9ba381331992443f55aa20", "北京锦程安顺\n商贸中心", 3,datetime.datetime(2013, 3, 3), 3.3, [], False],
["1f59289eac9ae8afcb19a0abbb30e43b", "成都金力达财税\r咨询有限公司", 4, datetime.datetime(2014, 4, 4), 4.4, {}, None]]
```
如上的datas，若使用默认的字符设置，csv的格式应如下：(格式的效果除了\N，其他的都可以通过python的csv模块实现)
```csv
aba1d47bb64fd86f7c70cd0c8ad066eb 西安市莲\t湖区莱丽奥"洗涤用品商店 1 2011-01-01 00:00:00 1.1 {} \N
fe0bd55c7bf779b71ff93b288fd7f9a9 鼓楼区萘,良文具店\\ 2 2012-02-02 00:00:00 2.2 () True
8c3b9637cd9ba381331992443f55aa20 北京锦程安顺\n商贸中心 3 2013-03-03 00:00:00 3.3 [] False
1f59289eac9ae8afcb19a0abbb30e43b 成都金力达财税\r咨询有限公司 4 2014-04-04 00:00:00 4.4 {} \N
```
* 强烈建议不要将空字符串'',导入date，datetime，timestamp类型的字段中，否则其值会显示为'0000-00-00'，'0000-00-00 00:00:00',用sqoop等工具从mysql中读数据时遇到此类值会报错，且不能通过update语句将其set为null值
* mysqlimport 执行结束后会显示执行结果概览 如：Records: 65290 Deleted: 0 Skipped: 0 Warnings: 15，但是不能通过show warnings;语句查看警告信息，如果想查看warnings信息，建议使用load data infile 语句导入数据
# 如何使用mysqlimport
## 全量导入表
这里讨论的是大表的全量导入
* 事先建一张空表，存储引擎设为目标引擎，删除所有索引（除自增的主键id）
* 自增的主键id不用在csv文件中事先生成
* 将所有文件聚合为一个csv文件
* 若表中要建唯一键索引，要事先将csv文件的记录根据唯一键字段去重（可用Hadoopstreaming处理），且注意大小写字母的处理
* 数据导入表中之后一次性创建所有索引
* 若目标存储引擎为innodb，不用将引擎事先设为myisam再导csv文件再改引擎再建索引
## 【线上生产表导入记录】
```
方式一：除去所有索引（除自增的主键id）导入数据共耗时26min
CREATE TABLE `ip_trademark_flow_list`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`use_flag` tinyint(4) DEFAULT '0' COMMENT '使用标志',
`reg_no_cls` varchar(50) DEFAULT NULL COMMENT '注册号_国际分类',
`flow_code` varchar(20) NOT NULL COMMENT '流程编码',
`flow_date` datetime DEFAULT NULL COMMENT '流程日期',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COMMENT='商标流程列表';
mysqlimport方式
utn_ng_tm.ip_trademark_flow_list: Records: 115751615 Deleted: 0 Skipped: 0 Warnings: 0
csv文件大小：4G,记录数115751615，共耗时26min
除去所有索引（除自增的主键id）导入数据耗时：9min
索引建立：
type1:Query OK, 0 rows affected (17 min 0.31 sec)
alter table ip_trademark_flow_list add unique uidx_rcls(`reg_no_cls`, `flow_code`, `flow_date`),add index idx_create_time(`create_time`),add index idx_update_time(`update_time`);
type2：耗时18min
create unique index uidx_rcls on ip_trademark_flow_list(`reg_no_cls`, `flow_code`, `flow_date`);
Query OK, 0 rows affected (7 min 18.34 sec)
create index idx_create_time on ip_trademark_flow_list(`create_time`);
Query OK, 0 rows affected (5 min 27.53 sec)
create index idx_update_time on ip_trademark_flow_list(`update_time`);
Query OK, 0 rows affected (5 min 27.89 sec)
方式二：索引主键都建立好导入数据耗时：21min
mysqlimport方式
csv文件大小：4G,记录数115751615，共耗时21min
索引主键都建立好导入数据耗时：21min
2020-05-07 17:10:46
utn_ng_tm.ip_trademark_flow_list_test: Records: 115751615 Deleted: 0 Skipped: 0 Warnings: 0
2020-05-07 17:31:17
结论：线上的导入结果是索引都建好mysqlimport导入方式更快。
```
```
问题：
`reg_no_cls` varchar(50) NOT NULL COMMENT '注册号_国际分类',
`cat_code` varchar(40) DEFAULT NULL COMMENT '类型编码',
`cat_name` varchar(4000) NOT NULL COMMENT '类型名称',
UNIQUE KEY `uidx_rcls` (`reg_no_cls`, `cat_name`(255))
数据：use_flag reg_no_cls cat_code cat_name
0 1403523_11 1109 \N
1.cat_name是索引的一部分，建表的时候定义为非空字符，注意：load data数据里面的\N插入后变为了空字符而不是NULL
2.cat_name为空的时候要不要入库？
MySQL [utn_ng_tm]> select *,length(cat_name) from ip_trademark_category_list where reg_no_cls='1403523_11';
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
| use_flag | reg_no_cls | cat_code | cat_name | create_time | update_time | length(cat_name) |
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
| 0 | 1403523_11 | 1109 | | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 0 |
| 0 | 1403523_11 | 1105 | 冷藏设备 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 12 |
| 0 | 1403523_11 | 1107 | 加热装置 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 12 |
| 0 | 1403523_11 | 1106 | 干燥设备 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 12 |
| 0 | 1403523_11 | 1101 | 照明器械 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 12 |
| 0 | 1403523_11 | 1107 | 蒸汽发生设备 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 18 |
| 0 | 1403523_11 | NULL | 通风设备 | 2020-05-08 16:55:42 | 2020-05-08 16:55:42 | 12 |
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
总结:
1.cat_name作为联合索引的一部分,虽然可以定义位默认NULL，这样cat_name为空的入库为NULL，但是这样会带来更新问题，
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
| use_flag | reg_no_cls | cat_code | cat_name | create_time | update_time | length(cat_name) |
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
| 0 | 1403523_11 | 1109 | NULL | 2020-05-09 10:35:13 | 2020-05-09 10:35:13 | NULL |
| 0 | 1403523_11 | 1109 | NULL | 2020-05-09 10:35:13 | 2020-05-09 10:35:13 | NULL |
| 0 | 1403523_11 | 1109 | NULL | 2020-05-09 10:35:13 | 2020-05-09 10:35:13 | NULL |
+----------+------------+----------+--------------------+---------------------+---------------------+------------------+
即多次upsert数据会让数据重复而不会去重，这样就违背了设计联合索引的初衷，所以联合索引数据列的默认值不能设置为NULL
2.cat_name为空倾向于入库，数据保留最多的信息，怎么用由展示端决定，所以保留cat_code=1109这个数据
```
## 增量导入表