一, 数据导入的方式
向 MySQL 数据库导入数据, 通常有两种办法, 第一种是利用 SOURCE 命令, 第二种是使用 LOAD DATA 命令.
SOURCE 命令是通过执行 SQL 文件中的 INSERT 语句来实现数据的导入. 正常情况下, 如果我们向单节点的 MySQL, 用 INSERT 语句批量写入数据, 在普通的 PC 机上, 写入 10 万条数据, 大概需要 7~8 分钟时间. 按照这个速度推算, 写入 1 千万条数据大概需要 10 个小时以上. 如果在集群条件下, 这个速度会更慢.
MySQL 的集群分为 PXC 和 Replication 集群. 其中 Replication 集群是异步传输的, 它只保证事物在当前节点成功写入, 数据是否能同步到其他节点, Replication 集群并不能给我们打包票. 所以 Replication 集群经常出现 A 节点写入数据, 但是在 B 节点读取不到数据的情况. 每年 Apple 秋季发布会之后, 很多人会到 Apple 官网抢购 iPhone 手机, 然而每年都有顾客会遇到付款之后, 订单依旧是未支付的状态. 这就是典型的 Replication 集群的效果, 数据出现了不一致. 如果采用 PXC 集群, 因为数据是同步传输, 所以我们在 A 节点写入数据, 提交事务的时候, PXC 必须保证所有 MySQL 节点都成功写入这条数据, 才算事务提交成功, 所以不会出现 A 节点写入数据, 在 B 节点上读取不到数据的情况. 因此 PXC 更加适合保存重要的数据, 例如交易记录, 学籍信息, 考试成绩, 用户信息等. 另外, 阿里巴巴在设计 OceanBase 数据库的时候也充分借鉴了 PXC 的原理.
再说回到数据导入, Replication 只保证本节点写入, 而 PXC 会保证所有节点写入. 因此说, 向 Replication 集群写入数据会比 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 程序来运行. 这一点与 Kotlin 很像, 但是 Xtend 语言的编译速度明显是 Kotlin 的 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 系统, 利用 split 把 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 集群导入数据, 该怎么做呢? 首先, 如果是 Replication 集群, 因为节点间是异步传输, 所以数据的导入速度最接近单节点 MySQL, 因此不用特别优化. 如果是 PXC 集群, 因为节点之间是同步传输, 所以写入速度较慢. 不妨关闭其他 PXC 节点, 只保留一个 MySQL 节点, 然后向该节点导入数据. 这是为了避免向集群导入数据的过程中, 同步的速度赶不上写入的速度, 导致 PXC 集群限速, 从而影响导入的速度. 当我们在一个 PXC 节点导入成功之后, 再陆续开启其他 PXC 节点, 那么就不会产生大规模写入限速的问题了.
来源: http://www.jianshu.com/p/f3094beb918a