需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。
本文只探讨mysql插入速度。分别从数据库和代码两方面进行总结。
首先有表test:
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT,
`testId` INT NULL,
PRIMARY KEY (`id`),
INDEX `testId` (`testId` ASC));
最基本的实现方式:
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class InserterBasic {
public static void main(String[] args) throws Exception {
File file = new File("D://Downloads//BaiduYunDownload//id.txt");
BufferedReader reader = null;
reader = new BufferedReader(new FileReader(file));
String tempString = null;
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement statement = con.createStatement();
while ((tempString = reader.readLine()) != null) {
statement.execute("insert into test (testId) value ("+tempString+")");
}
statement.close();
con.close();
reader.close();
}
}
这是随手即可写出的最简单实现。但它面临着几个问题:首先读文件代码和JDBC代码混淆在一起,其次无法检测插入速度不利于对比。
加入速度检测代码
使用LinkedList作为一个queue,将读出的文件中数据读入其中
再通过Timer不断的检测这个queue的长度,以此来确认入库速度。
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.Queue;
import java.util.TimerTask;
public class InserterControl {
//包含一千万数据的容器
static Queue queue = new LinkedList();
static int prevSize = 0;
public static void main(String[] args) throws Exception {
InserterControl inserter = new InserterControl();
inserter.read();
inserter.control();
inserter.insert();
}
/**
* 监控
*/
void control(){
new java.util.Timer().schedule(new TimerTask() {
@Override
public void run() {
int thisSize = queue.size();
System.out.println((prevSize-thisSize)/10+"条/秒");
prevSize = thisSize;
}
}, 0, 1000*10);//每10秒钟统计一次入库速度
}
/**
* 读文件
* @throws Exception
*/
void read() throws Exception{
File file = new File("D://Downloads//BaiduYunDownload//id.txt");
BufferedReader reader = null;
reader = new BufferedReader(new FileReader(file));
String tempString = null;
while ((tempString = reader.readLine()) != null) {
queue.offer(tempString);
}
prevSize = queue.size();
reader.close();
}
/**
* 入库
* @throws Exception
*/
void insert() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement statement = con.createStatement();
while(!queue.isEmpty()){
statement.execute("insert into test (testId) value ("+queue.poll()+")");
}
statement.close();
con.close();
}
}
输出前10条结果:
39条/秒
37条/秒
38条/秒
37条/秒
35条/秒
29条/秒
25条/秒
36条/秒
35条/秒
35条/秒
平均速度:34.5条/秒。粗略估计80小时候完成。结果令人沮丧。
/**对数据库参数进行调整***************************************************************************************/
调整 innodb_flush_log_at_trx_commit=0。重启。truncate test;
(其实直接将存储引擎改成MyISAM,速度可直接到达9000条/秒~10000条/秒)
再来一次:
8764条/秒
9128条/秒
7439条/秒
6129条/秒
7578条/秒
6711条/秒
7105条/秒
6754条/秒
7175条/秒
6855条/秒
平均速度:7363.8条/秒,预计20分钟即可完成。效果拔群,轻而易举的就将速度翻了210倍。
删除索引
此表在建表之初加入索引,是为了提高其后的组函数查询效率,但是在如果只考虑插入数据的速度,索引显示是个累赘。试试把索引删掉会如何。此处删除testId上索引的同时也删除了主键。使用java代码来模拟自增主键:
static int id = 0;
synchronized static int getId(){
return id++;
}
//或者是使用java.util.concurrent.atomic.AtomicInteger进行id的自增
static AtomicInteger id = new AtomicInteger(0);
truncate test;
ALTER TABLE `test`
CHANGE COLUMN `id` `id` INT(11) NULL ,
DROP INDEX `testId` ,
DROP PRIMARY KEY;
输出:
平均速度:8150.6条/秒。每秒写入增加近1000。相信这个差距会随着数据量的扩充而不断增大。
/**java代码方面*******************************************************************************************/
禁用自动提交
con.setAutoCommit(false);
//...
int i=0;
while(!queue.isEmpty()){
if(i%30000==0){
con.commit();
}
i++;
//...
输出:
平均速度:8635.1条/秒。提升近500。
多线程
使用多个线程,模拟多连接。
//
static Queue queue = new ConcurrentLinkedQueue();
void doInsert(){
for(int i=0; i<10; i++){
new Thread(){
@Override
public void run() {
try {
insert();
} catch (Exception e) {
e.printStackTrace();
}
};
}.start();
}
}
输出:
18823条/秒
17953条/秒
18485条/秒
18511条/秒
18473条/秒
16979条/秒
16091条/秒
17270条/秒
17041条/秒
17777条/秒
平均速度:15891条/秒。又有了进一倍的提升。
/************************************************************************************************/
总结:
1. 经过上述调整,插入速度提升了450倍左右。十分钟左右即可完成。喜大普奔。
2.innodb_flush_log_at_trx_commit=0对InnoDB的插入速度影响显著。需要深入研究。
3. 诸如此类统计需求,更好的选择是MyISAM。