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

作者:随时从零开始 时间:2024-01-15 16:43:44 

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

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

来源:https://blog.csdn.net/oldbai001/article/details/51693139

标签:MySql,千万级大数据
0
投稿

猜你喜欢

  • python绘制多个曲线的折线图

    2021-08-01 13:58:52
  • python pandas loc 布尔索引示例说明

    2021-02-28 22:43:19
  • JAVA使用DBUtils操作数据库

    2024-01-18 11:28:36
  • Flask使用Pyecharts在单个页面展示多个图表的方法

    2021-10-12 18:16:35
  • html中的sub与sup标签

    2009-03-06 13:12:00
  • 使用python svm实现直接可用的手写数字识别

    2023-06-09 13:57:19
  • 停止密码输入掩饰?[译]

    2009-07-22 18:44:00
  • golang设置http response响应头与填坑记录

    2024-05-21 10:22:24
  • Python3.x版本中新的字符串格式化方法

    2021-10-12 17:32:23
  • Javascript动画效果(3)

    2024-04-22 13:07:59
  • 浅谈对Python变量的一些认识理解

    2021-06-10 05:06:52
  • 10个杀手级应用的Python自动化脚本

    2023-10-06 14:01:53
  • 如何捕获人家站点的页面?

    2009-11-11 19:19:00
  • 初探MS SQL CE+Codesmith

    2009-05-11 09:03:00
  • 使用Protocol Buffers的C语言拓展提速Python程序的示例

    2022-12-04 08:46:20
  • Vue 服务端渲染SSR示例详解

    2024-05-28 15:50:39
  • MySQL数据库基本SQL语句教程之高级操作

    2024-01-24 08:08:11
  • 基于PHP+MySQL的聊天室设计

    2023-11-22 19:26:25
  • python 字典和列表嵌套用法详解

    2022-04-12 10:58:12
  • python3启动web服务引发的一系列问题汇总

    2022-10-31 09:11:58
  • asp之家 网络编程 m.aspxhome.com