Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to insert 10 million-level big data in MySql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The first step is to read the file and start inserting multithreading

Here, reaching a certain amount is a problem that needs to be considered. In my experiment, 100w was used as this quantity, but there was a new problem: Java heap memory overflow, and finally adopted the standard of 10W as quantity.

Of course, there can be other quantities, depending on which one you like.

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 multithread reads large files and stores them in the library * * @ param args * / private static int m_record = 99999; private static BufferedReader br = null; private ArrayList 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 (); / / remove CSV Header} catch (IOException e) {e.printStackTrace ();} public void start () {String line; int count = 0; list = new ArrayList (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 (mthread thread +)); t1.start (); list = new ArrayList (m_record + 1) }} if (list! = null) {Thread T1 = new Thread (new MultiThread (list), Integer.toString (t1.start (); t1.start ();}} catch (IOException e) {e.printStackTrace ();} public static void main (String [] args) {new MuiltThreadImportDB (). Start ();}}

The second step is to use multithreading to insert data in bulk.

Class MultiThread implements Runnable {private ArrayList list; public MultiThread (ArrayList 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 list) {/ / for (String str: list) {/ / System.out.println (str) / /} System.out.print (Thread.currentThread (). GetName () + ":"); System.out.println (list.size ());}}

In batch operation, the prepareStatement class of mysql is used, of course, the batch operation of statement class is also used, and the performance is not as good as the former. The former can achieve the insertion speed of 1w + per second, while the latter is only 2000 +.

Public int insertUserBehaviour (ArrayList 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 (); iTunes +) {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;} after reading the above, do you have any further understanding of how to insert 10 million-level big data into MySql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.