如何向数据库集群快速导入千万条数据

一，数据导入的方式

向MySQL数据库导入数据，通常有两种办法，第一种是利使用SOURCE命令，第二种是用LOAD DATA命令。

SOURCE命令是通过执行SQL文件中的INSERT语句来实现数据的导入。正常情况下，假如我们向单节点的MySQL中，使用INSERT语句批量写入数据，在普通的PC机上，写入10万条数据，大概需要7?8分钟时间。按照这个速度推算，写入1千万条数据大概需要10个小时以上。假如在集群条件下，这个速度会更慢。

MySQL的的集群分为PXC和复制集群。其中复制集群是异步传输的，它只保证事物在当前节点成功写入，数据能否能同步到其余节点，复制集群并不能给我们打包票。所以复制集群经常出现一个节点写入数据，但是在乙节点读取不到数据的情况。每年的苹果秋季发布会之后，很多人会到苹果官网抢购iPhone手机，然而每年都有顾客会遇到付款之后，订单仍旧是未支付的状态。这就是典型的复制集群的效果，数据出现了不一致。假如采使用PXC集群，由于数据是同步传输，所以我们在一个节点写入数据，提交事务的时候，PXC必需保证所有的MySQL节点都成功写入这条数据，才算事务提交成功，所以不会出现一个节点写入数据，在乙节点上读取不到数据的情况。因而PXC更加适合保存重要的数据，例如交易记录，学籍信息，考试成绩，使用户信息等。另外，阿里巴巴在设计的Oc eanBase数据库的时候也充分借鉴了PXC的原理。

再说回到数据导入，复制只保证本节点写入，而PXC会保证所有节点写入。因而说，向复制集群写入数据会比PXC快，但都比单节点MySQL的速度慢。

SOURCE命令执行的是INSERT语句，所以导入速度与我们执行INSERT语句没什么差别，假如数据量不多，还没什么问题，但是呢，假如从遗留数据库导出的数据特别多，上千万，甚至上亿。那么使用SOURCE导入数据就会耗时很长。因而啊，我们要选择LOAD DATA导入。

为什么说LOAD DATA导入速度比SOURCE快很多倍呢，这是由于数据库执行SQL语句的时候会先校验语法，而后优化SQL，最后再执行。但是LOAD DATA导入的纯数据，于是就跳过了SQL的校验和优化，导入的速度也就大大提升了。

二，准备工作

下面我们通过程序来生成一个TXT文档，向文档中写入1千万条数据，再通过LOAD DATA导入数据。

这里我用IBM的的Xtend语言，来生成1千万条数.Xtend语言可以与Java的语言完美兼容，除了语法更加简洁优雅之外，的Xtend代码会被编译成Java的代码。所以我们编写的代码最终会以Java的程序来运行。这一点与科特林很像，但是的Xtend语言的编译速度显著是科特林的4-6倍，开发效率真的是非常高。从我个人角度来说，非常推荐用的Xtend来改善Java的啰嗦的语法。另外，大家可以在Eclipse中的软件商店中找到该插件，安装之后，你就能编写的Xtend语言了。

import java.io.FileWriter
import java.io.BufferedWriter
class Test {
def static void main(String[] args) {
var writer=new FileWriter("D:/data.txt")
var buff=new BufferedWriter(writer)
for(i:1..10000000){
buff.write(i+",测试数据
")
}
buff.close
writer.close
}
}

接下来我们把TXT文件上传到Linux的系统，利使用分裂把TXT文件切分成多个文件，这样即可以使用的Java多线程同时把多个TXT文件导入到数据库。

split -l 1000000 -d data.txt

修改MySQL的的配置文件

innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 200M

创立要导入数据的表

CREATE TABLE t_test(
 id INT UNSIGNED PRIMARY KEY,
 name VARCHAR(200) NOT NULL
);

三，编写的Java程序，执行多线程导入

由于Java的语言自带了线程池，所以我们先定义出来的Runnable任务，而后交给多线程去执行导入TXT文档。

import org.eclipse.xtend.lib.annotations.Accessors
import java.io.File
import java.sql.DriverManager
class Task implements Runnable{
@Accessors
File file;

override run() {
var url="jdbc:mysql://192.168.99.131:8066/test"
var username="admin"
var password="Abc_123456"
var con=DriverManager.getConnection(url,username,password)
var sql='''
load data local intfile '/home/data/?file.name?' ignore into table t_test 
character set 'utf8' 
fields terminated by ',' optionally enclosed by '"' 
lines terminated by '
' (id,name);
'''
var pst=con.prepareStatement(sql);
pst.execute
con.close
LoadData.updateNum();
}
}
import com.mysql.jdbc.Driver
import java.sql.DriverManager
import java.util.concurrent.LinkedBlockingQueue
import java.util.concurrent.ThreadPoolExecutor
import java.util.concurrent.TimeUnit
import java.io.File
class LoadData {
var static int num=0;
var static int end=0;
var static pool=new ThreadPoolExecutor(1,5,60,TimeUnit.SECONDS,new LinkedBlockingQueue(200))
def static void main(String[] args) {
DriverManager.registerDriver(new Driver)
var folder=new File("/home/data")
var files=folder.listFiles
end=files.length //线程池结束条件
files.forEach[one|
var task=new Task();
task.file=one;
pool.execute(task)
]
}
synchronized def static updateNum(){
num++;
if(num==end){
pool.shutdown();
println("执行结束")
}
}
}

在Linux系统上执行Java程序。我本地的主机配置是AMD瑞龙2700X，16GB内存和固态硬盘，1千万数据，只使用了1分钟不到的时间就成功导入了。假如使用SOURCE语句导入这些数据，需要10个小时以上。换做LOAD DATA指令，仅仅1分钟，速度提升了3万多倍，太让人吃惊了。

这是向单节点导入数据，假如向MySQL的集群导入数据，该怎样做呢？首先，假如是复制集群，由于节点间是异步传输，所以数据的导入速度最接近单节点的MySQL，因而不使用特别优化。假如是PXC集群，由于节点之间是同步传输，所以写入速度较慢。不妨关闭其余PXC节点，只保留一个MySQL的节点，而后向该节点导入数据。这是为了避免向集群导入数据的过程中，同步的速度赶不上写入的速度，导致PXC集群限速，从而影响导入的速度。当我们在一个PXC节点导入成功之后，再陆续开启其余PXC节点，那么就不会产生大规模写入限速的问题了。

作者：神思者l

链接：https://www.imooc.com/article/74111

来源：慕课网