mysql插入資料變慢的原因：1、由主碼、外碼、索引造成的插入效率降低；2、由於使用for循環不停執行這個方法來插入；3、未能及時釋放查詢結果。
最近的專案需要匯入大量的數據，插入的過程中還需要邊查詢邊插入。插入的數據量在100w左右。一開始覺得100w的資料量不大，於是就插啊插，吃了個飯，回來一看，在插入了50多w條資料後，每秒就只能插10條了。 。覺得很奇怪，為啥越插越慢呢？於是就開始分析插入的時間損耗，想到瞭如下的解決方案：（mysql使用的INNODB引擎）
1.分析是否是由主碼，外碼，索引造成的插入效率降低
主碼：由於主碼是每張表必須有的，不能刪除。而mysql會對主碼自動建立一個索引，這個索引預設是Btree索引，因此每次插入資料要額外的對Btree進行一次插入。這個額外的插入時間複雜度約為log(n)。這個索引無法刪除，因此無法最佳化。但每次插入的時候，由於主碼約束需要檢查主碼是否出現，這又需要log(n)，能否減少這個開銷呢？答案是肯定的。我們可以設定主碼為自增id AUTO_INCREMENT ,這樣資料庫裡會自動記錄目前的自加值，保證不會插入重複的主碼，也就避免了主碼的重複性檢查。
外碼：由於我的項目的插入表中存在外碼，因此每次插入時需要在另一張表檢測外碼存在性。這個約束是跟業務邏輯相關的，不能隨便刪除。而這個時間開銷應當是與另一張表大小成正比的常數，不應當越插入越慢才對。所以排除。
索引：為了減少Btree插入的時間損耗，我們可以在建置表格時先不建置索引，先將所有的資料插入。之後我們再向表裡新增索引。該方法確實也降低了時間的開銷。
經過以上的折騰，再進行測試，發現速度快了一點，但是到了50w條後又開始慢了。看來問題的關鍵不在這裡。於是繼續查資料，又發現了個關鍵問題：
2.將單一條插入改為批次插入（參考：點選開啟連結）
## 由於java中的executeUpdate(sql)方法只是執行一條sql操作，就需要呼叫sql裡的各種資源，如果使用for循環不停的執行這個方法來插入，無疑是開銷很大的。因此，在mysql提供了一種解決方案：批量插入。也就是每次的一條sql不直接提交，而是先存在批任務集中，當任務集的大小到了指定閾值後，再將這些sql一起發送至mysql端。在100w的資料規模中，我將閾值設為10000，即一次提交10000條sql。最後的結果還挺好，插入的速度比之前快了20倍左右。批次插入程式碼如下：
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");
}3.一條UPDATE語句的VALUES後面跟上多條的(?,?,?,?)
# 這個方法一開始我覺得跟上面的差不多，但是在看了別人做的實驗後，發現利用這個方法改進上面的批量插入，速度能快5倍。後來發現，mysql的匯出sql檔中，那些插入語句也是這樣寫的。 。即UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... 。也就是我們需要在後台自己進行一個字串的拼接，注意由於字串只是不停的往末尾插入，用StringBuffer能夠更快的插入。下面是程式碼：
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");
}
做了以上的优化后，我发现了一个很蛋疼的问题。虽然一开始的插入速度的确快了几十倍，但是插入了50w条数据后，插入速度总是会一下突然变的非常慢。这种插入变慢是断崖式的突变，于是我冥思苦想，无意中打开了系统的资源管理器，一看发现：java占用的内存在不断飙升。 突然脑海中想到：是不是内存溢出了？
4.及时释放查询结果
在我的数据库查询语句中，使用到了pres=con.prepareStatement(sql)来保存一个sql执行状态，使用了resultSet=pres.executeQuery来保存查询结果集。而在边查边插的过程中，我的代码一直没有把查询的结果给释放，导致其不断的占用内存空间。当我的插入执行到50w条左右时，我的内存空间占满了，于是数据库的插入开始不以内存而以磁盘为介质了，因此插入的速度就开始变得十分的低下。因此，我在每次使用完pres和resultSet后，加入了释放其空间的语句：resultSet.close(); pres.close(); 。重新进行测试，果然，内存不飙升了，插入数据到50w后速度也不降低了。原来问题的本质在这里！
以上是mysql插入資料變慢的原因的詳細內容。更多資訊請關注PHP中文網其他相關文章！
