在数据分析领域，数据库是我们的好帮手。不仅可以接受我们的查询时间，还可以在这基础上做进一步分析。所以，我们必然要在数据库插入数据。在实际应用中，我们经常遇到千万级，甚至更大的数据量。如果没有一个快速的插入方法，则会事倍功半，花费大量的时间。
在参加阿里的天池大数据算法竞赛中（流行音乐趋势预测），我遇到了这样的问题，在没有优化数据库查询及插入之前，我花了不少冤枉时间，没有优化之前，1500万条数据，光插入操作就花费了不可思议的12个小时以上（使用最基本的逐条插入）。这也促使我思考怎样优化数据库插入及查询操作，提高效率。
在不断优化过程中，性能有大幅提升。在按时间序列从数据库查询并汇总生成2万6000多首歌曲的下载，播放，收藏数过程中，通过查询生成的操作速度提高从预估的40多小时降低到一小时多。在数据库插入方面，性能得到大幅提升；在新的数据集上测试，5490万+的数据，20分钟完成了插入。下面分享一下我的心得。
优化过程分为2步。第一步，实验静态reader从csv文件读取数据，达到一定量时，开始多线程插入数据库程序；第二步，使用mysq批量插入操作。
第一步，读取文件，开始插入多线程
在这里，达到一定量的量是个需要斟酌的问题，在我的实验中，开始使用100w作为这个量，但是出现了新的问题，java 堆内存溢出，最终采用了10w作为量的标准。
当然，可以有其他的量，看大家自己喜欢那个了。
我要评论
import java.io.bufferedreader;
import java.io.filenotfoundexception;
import java.io.filereader;
import java.io.ioexception;
import java.util.arraylist;
import java.util.list;
import preprocess.importdatabase;
public class muiltthreadimportdb {
/**
* java多线程读大文件并入库
*
* @param args
*/
private static int m_record = 99999;
private static bufferedreader br = null;
private arraylist<string> list;
private static int m_thread = 0;
static {
try {
br = new bufferedreader(
new filereader(
"e:/tianci/ijcai15 data/data_format1/user_log_format1.csv"),8192);
} catch (filenotfoundexception e) {
e.printstacktrace();
}
try {
br.readline(); // 去掉csv header
} catch (ioexception e) {
e.printstacktrace();
}
}
public void start() {
string line;
int count = 0;
list = new arraylist<string>(m_record + 1);
synchronized (br) {
try {
while ((line = br.readline()) != null) {
if (count < m_record) {
list.add(line);
count++;
} else {
list.add(line);
count = 0;
thread t1 = new thread(new multithread(list),integer.tostring(m_thread++));
t1.start();
list = new arraylist<string>(m_record + 1);
}
}
if (list != null) {
thread t1 = new thread(new multithread(list),integer.tostring(m_thread++));
t1.start();
}
} catch (ioexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
new muiltthreadimportdb().start();
}
}
第二步，使用多线程，批量插入数据
class multithread implements runnable {
private arraylist<string> list;
public multithread(arraylist<string> list) {
this.list = list;
}
public void run() {
try {
importdatabase insert = new importdatabase(list);
insert.start();
} catch (filenotfoundexception e) {
e.printstacktrace();
}
display(this.list);
}
public void display(list<string> list) {
// for (string str : list) {
// system.out.println(str);
// }
system.out.print(thread.currentthread().getname() + " :");
system.out.println(list.size());
}
}
批量操作中，使用mysql的preparestatement类，当然也使用了statement类的批量操作，性能比不上前者。前者可以达到1w+每秒的插入速度，后者只有2000+；
public int insertuserbehaviour(arraylist<string> sqls) throws sqlexception {
string sql = "insert into user_behaviour_log (user_id,item_id,cat_id,merchant_id,brand_id,time_stamp,action_type)"
+ " values(?,?,?,?,?,?,?)";
prestmt = conn.preparestatement(sql);
for (int i = 0; i < sqls.size(); i++) {
userlog log =new userlog(sqls.get(i));
prestmt.setstring(1, log.getuser_id());
prestmt.setstring(2, log.getitem_id());
prestmt.setstring(3, log.getcat_id());
prestmt.setstring(4, log.getmerchant_id());
prestmt.setstring(5, log.getbrand_id());
prestmt.setstring(6, log.gettimestamp());
prestmt.setstring(7, log.getactiontype());
prestmt.addbatch();
if ((i + 1) % 10000 == 0) {
prestmt.executebatch();
conn.commit();
prestmt.clearbatch();
}
}
prestmt.executebatch();
conn.commit();
return 1;
}
当然，也实验了不同的mysql存储引擎，innodb和myism,实验结果发现，innodb更快（3倍左右），可能和mysq的新版本有关系，笔者的mysql版本是5.6。
最后总结一下，大数据量下，提高插入速度的方法。
java代码方面，使用多线程插入，并且使用批处理提交。
数据库方面，表结构建立时不要使用索引，要不然插入过程过还要维护索引b+树；修改存储引擎，一般默认是innodb，（新版本就使用默认就可以，老版本可能需要）。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持移动技术网。
如对本文有疑问， 点击进行留言回复！！
centos8使用Apache httpd2.4.37安装web服务器的步骤详解
网友评论