大数据技术与架构
点击右侧关注，大数据开发领域最强公众号！
点击右侧关注，大数据真好玩！
本文是一个一个MySQL亿级数据的迁移记录，趁此记录下学习笔记。
数据迁移，工作原理和技术支持数据导出、BI报表之类的相似，差异较大的地方是导入和导出数据量区别，一般报表数据量不会超过几百万，而做数据迁移，如果是互联网企业经常会涉及到千万级、亿级以上的数据量。
导入和导出是两个过程，即使做数据迁移我们也要分开来看，同时，导入/导出方式又分为：
•MySQL自带导入/导出方式•各类客户端导入/导出方式
先总结下导出：
1、导出对于字段较少/字段内容较少的数据，通过客户端方式可以采用navicat等工具导出，我这里本次导出三个字段，都是11位数字以内的值，用navicat导出每分钟大约250万数据，
2、MySQL自带的导出语句：select into outfile语句；
这里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能速度极快的导入数据到数据库中，但是如果要使用fields和lines参数，则必须要带一个参数值且fields必须在lines参数之前；
本次我使用的语句是：
插入是很简单的语句，这里我不做具体举例操作，我要分享的是如何提高插入效率；
因为在我第一次用语句插入的时候，从晚上12点开始执行，到第二天11点还没有执行完。所以不是说用了load不配置其他的就一定很快；
本次我插入的数据格式如图：
文本格式如下：
一共有1亿4千万条数据，以文本文档的形式导出的，有4.3G大小；通过ftp软件上传到服务器/data/files文件夹中。
问题记录
•问题点1：
由于项目要求三个字段都要有索引，所以我在建表的时候就加了索引，导致耗时遥遥无期；
原因：
索引是要占空间的，如果导入三个字段都要加索引，代表了我要每个字段都写入索引一次，耗时比不加索引多了几倍
优化方法：
导入前把表的索引去掉留自增id一个，等导入完之后再添加
•问题点2：
数据库引擎的选择
MySQL的engine对如load写入是不一样的，特别是有master-slave主从备份的机制：
所以追求极致速度，几十亿数据的，可以考虑选择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分多中，所以一定要在执行语句后监控下服务器，否则语句不一定在正常执行。
cpu占用：
注意：load和insert最大的区别是：load只操作语法一次，之后就是一直是数据批量插入，而insert 是每一个数据操作一次，也遍历一次字段索引，所以insert本身对于大数据来说是极慢的。
总结：
本次优化我感觉最大最明显的变化是，去除索引后，导入速度极快，索引，重要的事情再说一遍：
导入时候可以先去掉索引，导入完之后再添加。
最后
MySQL导入大数据时一定要注意max最大事物限制，前几个月在做数据迁移时，在MySQL8.0 MGR集群上发生了大事物限制导致实例出问题重启了MySQL，默认配置应该是一亿五千万的事物限制，当时导入的数据比较大也没做参数扩展同时也没做数据切分导入或者限流导入，导致数据库堵塞重启，按照公司要求7*24*365机制，这算是事故了，如果高要求的公司，建议导入的时候注意MySQL本身配置或者导入进行事物提交限制。
References
[1] https://4m.cn/mUA5A
版权声明：
本文为大数据技术与架构整理，原作者独家授权。未经原作者允许转载追究侵权责任。
编辑｜冷眼丶
微信公众号｜import_bigdata
欢迎点赞+收藏+转发朋友圈素质三连