MySQL 千万级数据导入导出迁移笔记

本文是一个MySQL千万级数据的迁移记录，趁此记录下学习笔记。

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

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

• MySQL自带导入/导出方式
• 各类客户端导入/导出方式

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

2、数据量在几百万时，可以考虑使用mysqldump导出，mysql自带命令导入。

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

SELECT 字段名 FROM 表明  --可以加where条件
INTO OUTFILE "/data.txt"  --导出文件位置，这里要注意一个小问题，文件夹的权限要与mysql运行的用户权限一致，不然无法创建文件
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- 字段分割符和包含符
LINES TERMINATED BY '\n';  --换行符

• 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导入方式，原因是我之前要迁移3千万条数据，用navicat客户端导入数据要15小时，耗时太长且不确定太多，本身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}] ...]

原文上说的用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);--通过换行标识来解析成为每一条数据和插入到我指定的字段

插入是很简单的语句，这里我不做具体举例操作

影响插入速度还有几个原因，需要注意一下：

• 索引是要占空间的，耗时比不加索引多了几倍
• 推荐使用innodb引擎