關於批量插入數據之我見（100萬級別的數據，mysql）

關於批量插入數據之我見（100萬級別的數據，mysql）

因前段時間去面試，問到如何高效向數據庫插入10萬條記錄，之前沒處理過類似問題，也沒看過相關資料，結果沒答上來，今天就查了些資料，總結出三種方法：

測試數據庫為mysql!!!

方法一:

public static void insert() {
		// 開時時間
		Long begin = new Date().getTime();
		// sql前綴
		String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";
		try {
			// 保存sql後綴
			StringBuffer suffix = new StringBuffer();
			// 設置事務為非自動提交
			conn.setAutoCommit(false);
			// Statement st = conn.createStatement();
			// 比起st，pst會更好些
			PreparedStatement pst = conn.prepareStatement("");
			// 外層循環，總提交事務次數
			for (int i = 1; i <= 100; i++) {
				// 第次提交步長
				for (int j = 1; j <= 10000; j++) {
					// 構建sql後綴
					suffix.append("(" + j * i + ", SYSDATE(), " + i * j
							* Math.random() + "),");
				}
				// 構建完整sql
				String sql = prefix + suffix.substring(0, suffix.length() - 1);
				// 添加執行sql
				pst.addBatch(sql);
				// 執行操作
				pst.executeBatch();
				// 提交事務
				conn.commit();
				// 清空上一次添加的數據
				suffix = new StringBuffer();
			}
			// 頭等連接
			pst.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 結束時間
		Long end = new Date().getTime();
		// 耗時
		System.out.println("cast : " + (end - begin) / 1000 + " ms");
	}

輸出時間：cast : 23 ms

該方法目前測試是效率最高的方法!

方法二：

public static void insertRelease() {
		Long begin = new Date().getTime();
		String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";
		try {
			conn.setAutoCommit(false);
			PreparedStatement pst = conn.prepareStatement(sql);
			for (int i = 1; i <= 100; i++) {
				for (int k = 1; k <= 10000; k++) {
					pst.setLong(1, k * i);
					pst.setLong(2, k * i);
					pst.addBatch();
				}
				pst.executeBatch();
				conn.commit();
			}
			pst.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Long end = new Date().getTime();
		System.out.println("cast : " + (end - begin) / 1000 + " ms");
	}

注：注釋就沒有了，和上面類同，下面會有分析！

控制台輸出：cast : 111 ms

執行時間是上面方法的5倍！

方法三：

public static void insertBigData(SpringBatchHandler sbh) {
		Long begin = new Date().getTime();
		JdbcTemplate jdbcTemplate = sbh.getJdbcTemplate();
		final int count = 10000;
		String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			// 為prepared statement設置參數。這個方法將在整個過程中被調用的次數
			public void setValues(PreparedStatement pst, int i)
					throws SQLException {
				pst.setLong(1, i);
				pst.setInt(2, i);
			}

			// 返回更新的結果集條數
			public int getBatchSize() {
				return count;
			}
		});
		Long end = new Date().getTime();
		System.out.println("cast : " + (end - begin) / 1000 + " ms");
	}

執行時間：cast : 387 ms

總結：方法一和方法二很類同，唯一不同的是方法一采用的是“insert into tb (...) values(...),(...)...;”的方式執行插入操作，

方法二則是“insert into tb (...) values (...);insert into tb (...) values (...);...”的方式，要不是測試，我也不知道兩者差別是如此之大！

當然，這個只是目前的測試，具體執行時間和步長也有很大關系！如過把步長改為100，可能方法就要幾分鐘了吧，這個可以自己測試哈。。。

方法三網上很推崇，不過，效率大家也都看到了，1萬條記錄，耗時6分鐘，可見其效率並不理想！而且方法三需要配置spring applicationContext環境才能應用！

不過，方法三在ssh/spring-mvc中可用性還是很高的！

剛才開始研究大數據方面的問題，以上也只是真實測試的結果，並不一定就是事實，有好的建議，大家請指正，謝謝！

相互學習，才能進步更快！

晚點會把源碼發上來，大家可以直接去下載測試！