Java精選面試題（微信小程序）：5000+道面試題和選擇題，包含Java基礎、并發(fā)、JVM、線程、MQ系列、Redis、Spring系列、Elasticsearch、Docker、K8s、Flink、Spark、架構設計、大廠真題等，在線隨時刷題！

本文主要講述通過MyBatis、JDBC等做大數(shù)據(jù)量數(shù)據(jù)插入的案例和結(jié)果。

30萬條數(shù)據(jù)插入插入數(shù)據(jù)庫驗證

• 實體類、mapper和配置文件定義

• • User實體

  • mapper接口

  • mapper.xml文件

  • jdbc.properties

  • sqlMapConfig.xml

• 不分批次直接梭哈

• 循環(huán)逐條插入

• MyBatis實現(xiàn)插入30萬條數(shù)據(jù)

• JDBC實現(xiàn)插入30萬條數(shù)據(jù)

• 總結(jié)

驗證的數(shù)據(jù)庫表結(jié)構如下：

CREATE TABLE `t_user` (   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',   `username` varchar(64) DEFAULT NULL COMMENT '用戶名稱',   `age` int(4) DEFAULT NULL COMMENT '年齡',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶信息表';

話不多說，開整！

實體類、mapper和配置文件定義

User實體

/**  * 用戶實體  *  * @Author zjq  */ @Data public class User {     private int id;     private String username;     private int age; }

mapper接口

public interface UserMapper {     /**      * 批量插入用戶      * @param userList      */     void batchInsertUser(@Param("list") List userList); }

mapper.xml文件

"batchInsertUser"  parameterType= "java.util.List" >     insert into t_user(username,age) values     "list"  item= "item"  index= "index"  separator= "," >         (          #{item.username},          #{item.age}         )    

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/test jdbc.username=root jdbc.password=root

sqlMapConfig.xml

"1.0" encoding="UTF-8" ?> "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">         "jdbc.properties" >                 type = "com.zjq.domain.User" alias = "user" >             "developement" >         "developement" >             type = "JDBC" >             type = "POOLED" >                 "driver"  value= "${jdbc.driver}" />                 "url"  value= "${jdbc.url}" />                 "username"  value= "${jdbc.username}" />                 "password"  value= "${jdbc.password}" />                                         "com/zjq/mapper/UserMapper.xml" >    

不分批次直接梭哈

MyBatis直接一次性批量插入30萬條，代碼如下：

@Test public void testBatchInsertUser() throws IOException {     InputStream resourceAsStream =             Resources.getResourceAsStream("sqlMapConfig.xml");     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);     SqlSession session = sqlSessionFactory.openSession();     System.out.println("===== 開始插入數(shù)據(jù) =====");     long startTime = System.currentTimeMillis();     try {         List userList = new ArrayList<>();         for (int i = 1; i <= 300000; i++) {             User user = new User();             user.setId(i);             user.setUsername("共飲一杯無 " + i);             user.setAge((int) (Math.random() * 100));             userList.add(user);         }         session.insert("batchInsertUser", userList); // 最后插入剩余的數(shù)據(jù)         session.commit();         long spendTime = System.currentTimeMillis()-startTime;         System.out.println("成功插入 30 萬條數(shù)據(jù),耗時："+spendTime+"毫秒");     } finally {         session.close();     } }

可以看到控制臺輸出：

Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

圖片

超出最大數(shù)據(jù)包限制了，可以通過調(diào)整max_allowed_packet限制來提高可以傳輸?shù)膬?nèi)容，不過由于30萬條數(shù)據(jù)超出太多，這個不可取，梭哈看來是不行了

既然梭哈不行那我們就一條一條循環(huán)著插入行不行呢

循環(huán)逐條插入

mapper接口和mapper文件中新增單個用戶新增的內(nèi)容如下:

/**  * 新增單個用戶  * @param user  */ void insertUser(User user); "insertUser"  parameterType= "user" >     insert into t_user(username,age) values         (          #{username},          #{age}         )

調(diào)整執(zhí)行代碼如下：

@Test public void testCirculateInsertUser() throws IOException {     InputStream resourceAsStream =             Resources.getResourceAsStream("sqlMapConfig.xml");     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);     SqlSession session = sqlSessionFactory.openSession();     System.out.println("===== 開始插入數(shù)據(jù) =====");     long startTime = System.currentTimeMillis();     try {         for (int i = 1; i <= 300000; i++) {             User user = new User();             user.setId(i);             user.setUsername("共飲一杯無 " + i);             user.setAge((int) (Math.random() * 100));             // 一條一條新增             session.insert("insertUser", user);             session.commit();         }         long spendTime = System.currentTimeMillis()-startTime;         System.out.println("成功插入 30 萬條數(shù)據(jù),耗時："+spendTime+"毫秒");     } finally {         session.close();     } }

執(zhí)行后可以發(fā)現(xiàn)磁盤IO占比飆升，一直處于高位。

圖片

等啊等等啊等，好久還沒執(zhí)行完

圖片

先不管他了太慢了先搞其他的，等會再來看看結(jié)果吧。

two thousand year later …

控制臺輸出如下：

圖片

總共執(zhí)行了14909367毫秒，換算出來是4小時八分鐘。太慢了。。

圖片

還是優(yōu)化下之前的批處理方案吧

MyBatis實現(xiàn)插入30萬條數(shù)據(jù)

先清理表數(shù)據(jù)，然后優(yōu)化批處理執(zhí)行插入：

-- 清空用戶表 TRUNCATE table  t_user;

以下是通過 MyBatis 實現(xiàn) 30 萬條數(shù)據(jù)插入代碼實現(xiàn)：

/**  * 分批次批量插入  * @throws IOException  */ @Test public void testBatchInsertUser() throws IOException {     InputStream resourceAsStream =             Resources.getResourceAsStream("sqlMapConfig.xml");     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);     SqlSession session = sqlSessionFactory.openSession();     System.out.println("===== 開始插入數(shù)據(jù) =====");     long startTime = System.currentTimeMillis();     int waitTime = 10;     try {         List userList = new ArrayList<>();         for (int i = 1; i <= 300000; i++) {             User user = new User();             user.setId(i);             user.setUsername("共飲一杯無 " + i);             user.setAge((int) (Math.random() * 100));             userList.add(user);             if (i % 1000 == 0) {                 session.insert("batchInsertUser", userList);                 // 每 1000 條數(shù)據(jù)提交一次事務                 session.commit();                 userList.clear();                 // 等待一段時間                 Thread.sleep(waitTime * 1000);             }         }         // 最后插入剩余的數(shù)據(jù)         if(!CollectionUtils.isEmpty(userList)) {             session.insert("batchInsertUser", userList);             session.commit();         }         long spendTime = System.currentTimeMillis()-startTime;         System.out.println("成功插入 30 萬條數(shù)據(jù),耗時："+spendTime+"毫秒");     } catch (Exception e) {         e.printStackTrace();     } finally {         session.close();     } }

使用了 MyBatis 的批處理操作，將每 1000 條數(shù)據(jù)放在一個批次中插入，能夠較為有效地提高插入速度。同時請注意在循環(huán)插入時要帶有合適的等待時間和批處理大小，以防止出現(xiàn)內(nèi)存占用過高等問題。此外，還需要在配置文件中設置合理的連接池和數(shù)據(jù)庫的參數(shù)，以獲得更好的性能。

圖片

在上面的示例中，我們每插入1000行數(shù)據(jù)就進行一次批處理提交，并等待10秒鐘。這有助于控制內(nèi)存占用，并確保插入操作平穩(wěn)進行。

圖片

五十分鐘執(zhí)行完畢，時間主要用在了等待上。

如果低谷時期執(zhí)行，CPU和磁盤性能又足夠的情況下，直接批處理不等待執(zhí)行：

/**  * 分批次批量插入  * @throws IOException  */ @Test public void testBatchInsertUser() throws IOException {     InputStream resourceAsStream =             Resources.getResourceAsStream("sqlMapConfig.xml");     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);     SqlSession session = sqlSessionFactory.openSession();     System.out.println("===== 開始插入數(shù)據(jù) =====");     long startTime = System.currentTimeMillis();     int waitTime = 10;     try {         List userList = new ArrayList<>();         for (int i = 1; i <= 300000; i++) {             User user = new User();             user.setId(i);             user.setUsername("共飲一杯無 " + i);             user.setAge((int) (Math.random() * 100));             userList.add(user);             if (i % 1000 == 0) {                 session.insert("batchInsertUser", userList);                 // 每 1000 條數(shù)據(jù)提交一次事務                 session.commit();                 userList.clear();             }         }         // 最后插入剩余的數(shù)據(jù)         if(!CollectionUtils.isEmpty(userList)) {             session.insert("batchInsertUser", userList);             session.commit();         }         long spendTime = System.currentTimeMillis()-startTime;         System.out.println("成功插入 30 萬條數(shù)據(jù),耗時："+spendTime+"毫秒");     } catch (Exception e) {         e.printStackTrace();     } finally {         session.close();     } }

則24秒可以完成數(shù)據(jù)插入操作：

圖片圖片

可以看到短時CPU和磁盤占用會飆高。

把批處理的量再調(diào)大一些調(diào)到5000，在執(zhí)行：

圖片

13秒插入成功30萬條，直接蕪湖起飛

JDBC實現(xiàn)插入30萬條數(shù)據(jù)

JDBC循環(huán)插入的話跟上面的mybatis逐條插入類似，不再贅述。

以下是 Java 使用 JDBC 批處理實現(xiàn) 30 萬條數(shù)據(jù)插入的示例代碼。請注意，該代碼僅提供思路，具體實現(xiàn)需根據(jù)實際情況進行修改。

/**  * JDBC分批次批量插入  * @throws IOException  */ @Test public void testJDBCBatchInsertUser() throws IOException {     Connection connection = null;     PreparedStatement preparedStatement = null;     String databaseURL = "jdbc:mysql://localhost:3306/test";     String user = "root";     String password = "root";     try {         connection = DriverManager.getConnection(databaseURL, user, password);         // 關閉自動提交事務，改為手動提交         connection.setAutoCommit(false);         System.out.println("===== 開始插入數(shù)據(jù) =====");         long startTime = System.currentTimeMillis();         String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";         preparedStatement = connection.prepareStatement(sqlInsert);         Random random = new Random();         for (int i = 1; i <= 300000; i++) {             preparedStatement.setString(1, "共飲一杯無 " + i);             preparedStatement.setInt(2, random.nextInt(100));             // 添加到批處理中             preparedStatement.addBatch();             if (i % 1000 == 0) {                 // 每1000條數(shù)據(jù)提交一次                 preparedStatement.executeBatch();                 connection.commit();                 System.out.println("成功插入第 "+ i+" 條數(shù)據(jù)");             }         }         // 處理剩余的數(shù)據(jù)         preparedStatement.executeBatch();         connection.commit();         long spendTime = System.currentTimeMillis()-startTime;         System.out.println("成功插入 30 萬條數(shù)據(jù),耗時："+spendTime+"毫秒");     } catch (SQLException e) {         System.out.println("Error: " + e.getMessage());     } finally {         if (preparedStatement != null) {             try {                 preparedStatement.close();             } catch (SQLException e) {                 e.printStackTrace();             }         }         if (connection != null) {             try {                 connection.close();             } catch (SQLException e) {                 e.printStackTrace();             }         }     } }

上述示例代碼中，我們通過 JDBC 連接 MySQL 數(shù)據(jù)庫，并執(zhí)行批處理操作插入數(shù)據(jù)。具體實現(xiàn)步驟如下：

• 獲取數(shù)據(jù)庫連接。

• 創(chuàng)建 Statement 對象。

• 定義 SQL 語句，使用PreparedStatement對象預編譯 SQL 語句并設置參數(shù)。

• 執(zhí)行批處理操作。

• 處理剩余的數(shù)據(jù)。

• 關閉 Statement 和 Connection 對象。

使用setAutoCommit(false)來禁止自動提交事務，然后在每次批量插入之后手動提交事務。每次插入數(shù)據(jù)時都新建一個PreparedStatement對象以避免狀態(tài)不一致問題。在插入數(shù)據(jù)的循環(huán)中，每 10000 條數(shù)據(jù)就執(zhí)行一次executeBatch()插入數(shù)據(jù)。

另外，需要根據(jù)實際情況優(yōu)化連接池和數(shù)據(jù)庫的相關配置，以防止連接超時等問題。

總結(jié)

實現(xiàn)高效的大量數(shù)據(jù)插入需要結(jié)合以下優(yōu)化策略（建議綜合使用）：

1.批處理：批量提交SQL語句可以降低網(wǎng)絡傳輸和處理開銷，減少與數(shù)據(jù)庫交互的次數(shù)。在Java中可以使用Statement或者PreparedStatement的addBatch()方法來添加多個SQL語句，然后一次性執(zhí)行executeBatch()方法提交批處理的SQL語句。

• 在循環(huán)插入時帶有適當?shù)牡却龝r間和批處理大小，從而避免內(nèi)存占用過高等問題：

• • 設置適當?shù)呐幚泶笮。号幚泶笮≈冈谝淮尾迦氩僮髦胁迦攵嗌傩袛?shù)據(jù)。如果批處理大小太小，插入操作的頻率將很高，而如果批處理大小太大，可能會導致內(nèi)存占用過高。通常，建議將批處理大小設置為1000-5000行，這將減少插入操作的頻率并降低內(nèi)存占用。

  • 采用適當?shù)牡却龝r間：等待時間指在批處理操作之間等待的時間量。等待時間過短可能會導致內(nèi)存占用過高，而等待時間過長則可能會延遲插入操作的速度。通常，建議將等待時間設置為幾秒鐘到幾十秒鐘之間，這將使操作變得平滑且避免出現(xiàn)內(nèi)存占用過高等問題。

  • 可以考慮使用一些內(nèi)存優(yōu)化的技巧，例如使用內(nèi)存數(shù)據(jù)庫或使用游標方式插入數(shù)據(jù)，以減少內(nèi)存占用。

• 總的來說，選擇適當?shù)呐幚泶笮『偷却龝r間可以幫助您平穩(wěn)地進行插入操作，避免出現(xiàn)內(nèi)存占用過高等問題。

2.索引:在大量數(shù)據(jù)插入前暫時去掉索引，最后再打上，這樣可以大大減少寫入時候的更新索引的時間。

3.數(shù)據(jù)庫連接池：使用數(shù)據(jù)庫連接池可以減少數(shù)據(jù)庫連接建立和關閉的開銷，提高性能。在沒有使用數(shù)據(jù)庫連接池的情況，記得在finally中關閉相關連接。

數(shù)據(jù)庫參數(shù)調(diào)整：增加MySQL數(shù)據(jù)庫緩沖區(qū)大小、配置高性能的磁盤和I/O等。

來源：https://blog.csdn.net/qq_35427589/article/details/129665307

公眾號“Java精選”所發(fā)表內(nèi)容注明來源的，版權歸原出處所有（無法查證版權的或者未注明出處的均來自網(wǎng)絡，系轉(zhuǎn)載，轉(zhuǎn)載的目的在于傳遞更多信息，版權屬于原作者。如有侵權，請聯(lián)系，筆者會第一時間刪除處理！

最近有很多人問，有沒有讀者交流群！加入方式很簡單，公眾號Java精選，回復“加群”，即可入群！

文章有幫助的話，點在看，轉(zhuǎn)發(fā)吧！