mysql插入數據為什么會變慢

這篇文章將為大家詳細講解有關mysql插入數據為什么會變慢，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

mysql插入數據變慢的原因：1、由主碼、外碼、索引造成的插入效率降低；2、由于使用for循環不停執行這個方法來插入；3、未能及時釋放查詢結果。

《java教程》

       最近的項目需要導入大量的數據，插入的過程中還需要邊查詢邊插入。插入的數據量在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插入數據為什么會變慢就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。