站長(zhǎng)資訊網(wǎng)
首先看下我們的目標(biāo)：向mysql數(shù)據(jù)庫(kù)中批量插入10000條數(shù)據(jù)
操作環(huán)境：Mysql和Java代碼都運(yùn)行在我本地Windows電腦(i7處理器，4核，16G運(yùn)行內(nèi)存，64位操作系統(tǒng)
1、JPA單線程執(zhí)行
代碼省略，大概需要39S左右
2、JPA多線程執(zhí)行
大概需要37S左右，并沒(méi)有想象中的快很多
（免費(fèi)學(xué)習(xí)視頻分享：java視頻教程）
原因： 多線程只是大大提高了程序處理數(shù)據(jù)的時(shí)間，并不會(huì)提高插入數(shù)據(jù)庫(kù)的時(shí)間，相反在我這邊JPA的框架下，多線程也就意味著多連接，反而更加消耗數(shù)據(jù)庫(kù)性能
package com.example.demo.controller; import com.example.demo.entity.Student; import com.example.demo.service.StudentServiceInterface; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.xml.bind.ValidationException; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.concurrent.CountDownLatch; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; @RestController @RequestMapping("/student") public class StudentController { @Autowired private StudentServiceInterface studentServiceInterface; // 來(lái)使主線程等待線程池中的線程執(zhí)行完畢 private CountDownLatch threadsSignal; // 每個(gè)線程處理的數(shù)據(jù)量 private static final int count = 1000; // 我的電腦為4核 線程池大小設(shè)置為2N+1 private static ExecutorService execPool = Executors.newFixedThreadPool(9); /** * 多線程保存 * * @return * @throws ValidationException */ @GetMapping() public String saveStudentEnableThread() throws ValidationException { Long begin = new Date().getTime(); // 需要插入數(shù)據(jù)庫(kù)的數(shù)據(jù) List<Student> list = new ArrayList<>(); for (int i = 0; i < 10000; i++) { Student student = new Student(); student.setName("張三"); student.setAge(10); list.add(student); } try { if (list.size() <= count) { threadsSignal = new CountDownLatch(1); execPool.submit(new InsertDate(list)); } else { List<List<Student>> lists = dealData(list, count); threadsSignal = new CountDownLatch(lists.size()); for (List<Student> students : lists) { execPool.submit(new InsertDate(students)); } } threadsSignal.await(); } catch (Exception e) { System.out.println(e.toString() + " 錯(cuò)誤所在行數(shù)：" + e.getStackTrace()[0].getLineNumber()); } // 結(jié)束時(shí)間 Long end = new Date().getTime(); return "10000條數(shù)據(jù)插入花費(fèi)時(shí)間 : " + (end - begin) / 1000 + " s"; } /** * 數(shù)據(jù)組裝 * 把每個(gè)線程要處理的數(shù)據(jù) 再組成一個(gè)List * 我這邊就是把10000條數(shù)據(jù) 組成 10個(gè)1000條的集合 * * @param target 數(shù)據(jù)源 * @param size 每個(gè)線程處理的數(shù)量 * @return */ public static List<List<Student>> dealData(List<Student> target, int size) { List<List<Student>> threadList = new ArrayList<List<Student>>(); // 獲取被拆分的數(shù)組個(gè)數(shù) int arrSize = target.size() % size == 0 ? target.size() / size : target.size() / size + 1; for (int i = 0; i < arrSize; i++) { List<Student> students = new ArrayList<Student>(); //把指定索引數(shù)據(jù)放入到list中 for (int j = i * size; j <= size * (i + 1) - 1; j++) { if (j <= target.size() - 1) { students.add(target.get(j)); } } threadList.add(students); } return threadList; } /** * 內(nèi)部類,開(kāi)啟線程批量保存數(shù)據(jù) */ class InsertDate extends Thread { List<Student> list = new ArrayList<Student>(); public InsertDate(List<Student> students) { list = students; } public void run() { try { // 與數(shù)據(jù)庫(kù)交互 studentServiceInterface.save(list); threadsSignal.countDown(); } catch (ValidationException e) { e.printStackTrace(); } } } }
3、傳統(tǒng)JDBC插入
大概需要8S左右，相較于前兩種方式已經(jīng)快很多了，代碼如下：
package com.example.demo.controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.xml.bind.ValidationException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.util.Date; @RestController @RequestMapping("/student1") public class StudentController1 { @GetMapping() public String saveStudentEnableThread() throws ValidationException { // 開(kāi)始時(shí)間 Long begin = new Date().getTime(); Connection connection = null; try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "admin", "123456");//獲取連接 if (connection != null) { System.out.println("獲取連接成功"); } else { System.out.println("獲取連接失敗"); } //這里必須設(shè)置為false，我們手動(dòng)批量提交 connection.setAutoCommit(false); //這里需要注意，SQL語(yǔ)句的格式必須是預(yù)處理的這種，就是values(?,?,...,?)，否則批處理不起作用 PreparedStatement statement = connection.prepareStatement("insert into student(id,`name`,age) values(?,?,?)"); // 塞數(shù)據(jù) for (int i = 0; i < 10000; i++) { statement.setInt(1, i+1); statement.setString(2, "張三"); statement.setInt(3, 10); //將要執(zhí)行的SQL語(yǔ)句先添加進(jìn)去，不執(zhí)行 statement.addBatch(); } // 提交要執(zhí)行的批處理，防止 JDBC 執(zhí)行事務(wù)處理 statement.executeBatch(); connection.commit(); // 關(guān)閉相關(guān)連接 statement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } // 結(jié)束時(shí)間 Long end = new Date().getTime(); // 耗時(shí) System.out.println("10000條數(shù)據(jù)插入花費(fèi)時(shí)間 : " + (end - begin) / 1000 + " s"); return "10000條數(shù)據(jù)插入花費(fèi)時(shí)間 : " + (end - begin) / 1000 + " s"; } }
4、最后檢查一下數(shù)據(jù)是否成功存庫(kù)，一共30000條，沒(méi)有丟數(shù)據(jù)
完成！
