MySQL 亿级数据导入导出/数据迁移笔记

川长思鸟来 2022-05-13 01:30 656阅读 0赞

最近MySQL的笔记有点多了，主要是公司Oracle比较稳定维护较少，上周被安排做了一个MySQL亿级数据的迁移，趁此记录下学习笔记；

数据迁移，工作原理和技术支持数据导出、BI报表之类的相似，差异较大的地方是导入和导出数据量区别，一般报表数据量不会超过几百万，而做数据迁移，如果是互联网企业经常会涉及到千万级、亿级以上的数据量。

导入和导出是两个过程，即使做数据迁移我们也要分开来看，同时，导入/导出方式又分为：

1、MySQL自带导入/导出方式

2、各类客户端导入/导出方式

先总结下导出：

1、导出对于字段较少/字段内容较少的数据，通过客户端方式可以采用navicat等工具导出，我这里本次导出三个字段，都是11位数字以内的值，用navicat导出每分钟大约250万数据，

2、MySQL自带的导出语句：select into outfile语句；

SELECT ... FROM TABLE_A --可以加where条件
    INTO OUTFILE "/path/to/file" --导出文件位置
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- 字段分割符和包含符
    LINES TERMINATED BY '\n';--换行符

这里fields之前很简单都可看懂，不做说明，讲下fields之后的：

FIELDS TERMINATED BY ',' 代表我字段和字段之间用 逗号 分开 ，如：字段 A 字段 B，导出时候显示格式为：A,B

OPTIONALLY ENCLOSED BY '"' 代表字段内容用双引号包含，导出格式如： "A","B"

LINES TERMINATED BY '\\n';每条数据换行区分，导出格式如：

"A","B"

"A1","B1"

当然，字段区分和包含符号可以自行定义，定义为：'    \#  都可以

用MySQL自带导出/导入优点是速度极快，缺点是：只能导出文件是在服务器主机所在的本机地址，对于bi之类拿到不数据库主机权限的同事这个方式可能奢望了。不过好在对于字段/内容较少的报表第三方客户端工具导出速度也不算特别慢；

导入：

重点记录导入，导入主要是dba做数据迁移了，方式也分客户端和MySQL自带方式：

这里极度推荐用MySQL导入方式，原因是我之前要迁移1.3亿数据，用navicat客户端导入数据要22小时，耗时太长且不确定太多，本身navicat等工具就会有假死风险的存在，所不建议超过1万以上的数据通过navicat导入；

MySQL自带导入方式：

\--官方文档定义如下，注释是我自己理解添加的：

LOAD DATA 、
    [LOW_PRIORITY | CONCURRENT]--无人使用数据库再执行/立即执行
    [LOCAL]--带这个参数指服务端即不是服务器主机上读取文件，不带这个参数是默认在服务器主机上读取文件
    INFILE 'file_name' --读取文件地址、文件名
        [REPLACE | IGNORE]--遇到重复数据是：替换/重复写入，建议使用ignore重复写入
        INTO TABLE tbl_name --导入到那个表
        [PARTITION (partition_name [, partition_name] ...)]--这行参数可以不要，建议用后面的fields
        [CHARACTER SET charset_name]--设定导入内容字符格式，utf-8还是GBK等都可以指定
    
    
        [{FIELDS | COLUMNS}  --fields标识符
            [TERMINATED BY 'string'] --系统字段通过什么符号区分
            [[OPTIONALLY] ENCLOSED BY 'char']--系统字段本身的起始和结束用什么符号区分
            [ESCAPED BY 'char']--转义符，如果是文本文件，在文本字段中有特殊字符如双引号，可通过定义转义符忽略文本文件特殊字符
        ]
        [LINES  --lines标识符
            [STARTING BY 'string'] --定义行开头的字符串，如果行开头没有字符标识，一般可以不写
            [TERMINATED BY 'string']--行结束字符串标识，通过定义字符来区分行与行的数据
        ]
        [IGNORE number {LINES | ROWS}]--忽略文件中前面多少行，一般都不写
    --后面都是指定插入到哪些字段
        [(col_name_or_user_var
            [, col_name_or_user_var] ...)]
        [SET col_name={expr | DEFAULT},
            [, col_name={expr | DEFAULT}] ...]

![70][]

原文上说的用load data能速度极快的导入数据到数据库中，但是如果要使用fields和lines参数，则必须要带一个参数值且fields必须在lines参数之前；

本次我使用的语句是：

load data infile '/data/files/T_CUST_INFO.txt' --默认指定服务器文件夹
    ignore into table t_dq_user --允许重复记录插入
    fields terminated by ','  --判断字段通过逗号标识来分隔开
    lines terminated by '\n'(CustID,DeviceNo,logintype);--通过换行标识来解析成为每一条数据和插入到我指定的字段

插入是很简单的语句，这里我不做具体举例操作，我要分享的是如何提高插入效率；

因为在我第一次用语句插入的时候，从晚上12点开始执行，到第二天11点还没有执行完。所以不是说用了load不配置其他的就一定很快；

本次我插入的数据格式如图：

![70 1][]

文本格式如下：

![70 2][]

一共有1亿4千万条数据，以文本文档的形式导出的，有4.3G大小；通过ftp软件上传到服务器/data/files文件夹中；

吐槽点1：

由于项目要求三个字段都要有索引，所以我在建表的时候就加了索引，导致耗时遥遥无期；

原因：

索引是要占空间的，如果导入三个字段都要加索引，代表了我要每个字段都写入索引一次，耗时比不加索引多了几倍；

优化方法：

导入前把表的索引去掉留自增id一个，等导入完之后再添加

吐槽点2：

engine的选择：

MySQL的engine对如load写入是不一样的，特别是有master-slave主从备份的机制：

对MyISAM引擎：   
          (1)对master服务器进行  ‘load’ 操作，   
          (2)在master上所操作的load.txt文件，会同步传输到slave上，并在tmp\_dir 目录下生成 load.txt文件   
               master服务器插入了多少，就传给slave多少   
          (3)当master上的load操作完成后，传给slave的文件也结束时，   
               即：在slave上生成完整的 load.txt文件   
               此时，slave才开始从 load.txt 读取数据，并将数据插入到本地的表中

对innodb引擎：   
          (1)主数据库进行  ‘Load’ 操作   
          (2)主数据库操作完成后，才开始向slave传输 load.txt文件，   
               slave接受文件，并在 tmp\_dir 目录下生成 load.txt 文件   
               接受并生成完整的load.txt 后，才开始读取该文件，并将数据插入到本地表中

所以追求极致速度，几十亿数据的，可以考虑选择myisam引擎，MySQL默认应该是innodb；不过本次我并没有更改引擎，本人不推荐更改默认的innodb引擎，毕竟Oracle官方主推引擎，综合性最强，除非有特殊性，不推荐使用myisam。如果用了myisam，注意一下两点：

用了myisam，可以调整几个session值扩大读取内存，提高读取数据，语句如下：

SET SESSION BULK\_INSERT\_BUFFER\_SIZE = 256217728 ;  
SET SESSION MYISAM\_SORT\_BUFFER\_SIZE = 256217728 ;

对于MyISAM引擎，导入前的唯一校验可以先关闭，之后再打开：

SET UNIQUE\_CHECKS=0 --关闭

SET UNIQUE\_CHECKS=1 --打开

吐槽点3：

虽然MySQL支持本地客户端读取文件，但是由于各种网络原因，在几十几百条数据的情况下没有什么影响，但是到了亿级数据量，即使1毫秒的影响也会放的特别大，所以建议用ftp传到服务器上进行读取

吐槽点4：

经验分享，导入之后看看服务器状态：top 命令看看主机cpu MySQL占用情况，理论上会占用较多cpu，我的第一次耗时贼长的那次，cpu占用10%，这是极度不正常的导入，第二次正常导入cpu占用到了110%，这才是再急速写入的状态；最后1.4亿数据只耗时：7分多中，所以一定要在执行语句后监控下服务器，否则语句不一定在正常执行。

![70 3][]

cpu占用：

![70 4][]

注意：load和insert最大的区别是：load只操作语法一次，之后就是一直是数据批量插入，而insert 是每一个数据操作一次，也遍历一次字段索引，所以insert本身对于大数据来说是极慢的。

总结：

本次优化我感觉最大最明显的变化是，去除索引后，导入速度极快，索引，重要的事情再说一遍：

导入时候可以先去掉索引，导入完之后再添加。

\--------------------------------------------------------------------------------------------------------------------------------

2020.7.3更新

MySQL导入大数据时一定要注意max最大事物限制，前几个月在做数据迁移时，在MySQL8.0 MGR集群上发生了大事物限制导致实例出问题重启了MySQL，默认配置应该是一亿五千万的事物限制，当时导入的数据比较大也没做参数扩展同时也没做数据切分导入或者限流导入，导致数据库堵塞重启，按照公司要求7\*24\*365机制，这算是事故了，如果高要求的公司，建议导入的时候注意MySQL本身配置或者导入进行事物提交限制；

[70]: /images/20220513/f456a73ee0324f2298361b5449c9325a.png
[70 1]: /images/20220513/3e9e77b2041e4674abfb8b1b50b6b8b7.png
[70 2]: /images/20220513/3dad88e3820c4946af10e84a826ba9ab.png
[70 3]: /images/20220513/788f0e9a742b4d76a07d5f13e3402d2c.png
[70 4]: /images/20220513/005f74733c6448fa8d5afc15815e6bbb.png