欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySql 快速插入千万级大数据的方法示例

程序员文章站 2022-07-07 11:31:34
在数据分析领域,数据库是我们的好帮手。不仅可以接受我们的查询时间,还可以在这基础上做进一步分析。所以,我们必然要在数据库插入数据。在实际应用中,我们经常遇到千万级,甚至更大...

在数据分析领域,数据库是我们的好帮手。不仅可以接受我们的查询时间,还可以在这基础上做进一步分析。所以,我们必然要在数据库插入数据。在实际应用中,我们经常遇到千万级,甚至更大的数据量。如果没有一个快速的插入方法,则会事倍功半,花费大量的时间。

在参加阿里的天池大数据算法竞赛中(流行音乐趋势预测),我遇到了这样的问题,在没有优化数据库查询及插入之前,我花了不少冤枉时间,没有优化之前,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,(新版本就使用默认就可以,老版本可能需要)。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。