本文是一个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引擎