Gründe, warum MySQL Daten langsam einfügt

Die Gründe, warum MySQL Daten langsam einfügt: 1. Die Einfügungseffizienz wird aufgrund des Hauptcodes, des Fremdcodes und des Index verringert. 2. Aufgrund der Verwendung einer for-Schleife zum kontinuierlichen Ausführen dieser Methode zum Einfügen Abfrageergebnisse können nicht rechtzeitig veröffentlicht werden.

Empfohlen: „MySQL-Video-Tutorial“ „Java-Tutorial“

Aktuelle Projekte erfordern den Import einer großen Datenmenge, und der Einfügevorgang erfordert auch das gleichzeitige Abfragen und Einfügen. Die eingefügte Datenmenge beträgt etwa 1 Million. Zuerst dachte ich, dass 1 Million Daten keine große Menge seien, also steckte ich ein, steckte ein und aß etwas. Als ich zurückkam, sah ich, dass ich nach dem Einfügen von mehr als 50 W Daten nur noch 10 einfügen konnte Stücke pro Sekunde. . Ich fühle mich sehr seltsam, warum wird es immer langsamer, je mehr ich es einführe? Also begann ich, den Zeitverlust beim Einfügen zu analysieren und kam auf die folgende Lösung: (INNODB-Engine, die von MySQL verwendet wird)

1 Analysieren Sie, ob die Einfügungseffizienz aufgrund des Hauptcodes, des Fremdcodes usw. verringert wird index

Hauptcode: Da der Hauptcode für jede Tabelle benötigt wird, kann er nicht gelöscht werden. MySQL erstellt automatisch einen Index für den Hauptcode. Dieser Index ist standardmäßig ein Btree-Index, sodass Sie jedes Mal, wenn Sie Daten einfügen, einen zusätzlichen Btree einfügen müssen. Bei dieser zusätzlichen Einfügezeitkomplexität geht es um log(n). Dieser Index kann nicht gelöscht und daher nicht optimiert werden. Aufgrund der Hauptcodebeschränkung muss jedoch bei jedem Einfügen überprüft werden, ob der Hauptcode angezeigt wird, was log (n) erfordert. Kann dieser Overhead reduziert werden? Die Antwort ist ja. Wir können den Hauptcode auf die Auto-Inkrement-ID AUTO_INCREMENT setzen, sodass der aktuelle Auto-Inkrement-Wert automatisch in der Datenbank aufgezeichnet wird, um sicherzustellen, dass kein doppelter Hauptcode eingefügt wird, wodurch die Duplikatprüfung des Hauptcodes vermieden wird Code. Hinweis zum äußeren Code: Da sich in der Einfügetabelle meines Projekts ein externer Code befindet, müssen Sie beim Einfügen die Existenz des äußeren Codes in einer anderen Tabelle erkennen. Diese Einschränkung hängt mit der Geschäftslogik zusammen und kann nicht einfach gelöscht werden. Und dieser Zeitaufwand sollte konstant proportional zur Größe der anderen Tabelle sein und nicht mit mehr Einfügungen langsamer werden. Also ausgeschlossen.

Index: Um den Zeitverlust beim Einfügen von Btree zu reduzieren, können wir beim Erstellen der Tabelle zuerst alle Daten einfügen, ohne einen Index zu erstellen. Anschließend fügen wir der Tabelle Indizes hinzu. Diese Methode reduziert tatsächlich den Zeitaufwand.

Nach dem oben genannten Herumwerfen und Testen stellte ich fest, dass die Geschwindigkeit etwas höher war, aber nach Erreichen von 500.000 Bar begann sie wieder langsamer zu werden. Es scheint, dass hier nicht der Kern des Problems liegt. Also habe ich die Informationen weiter überprüft und ein Hauptproblem gefunden:

2. Ändern Sie die einzelne Einfügung in eine Stapeleinfügung (Referenz: Klicken Sie, um den Link zu öffnen)

Da die Methode „executeUpdate(sql)“ in Java nur a ausführt Bei der SQL-Operation müssen Sie verschiedene Ressourcen in SQL aufrufen. Wenn Sie diese Methode zum Einfügen kontinuierlich ausführen, ist dies zweifellos sehr teuer. Daher bietet MySQL eine Lösung: Batch-Einfügung. Das heißt, jede SQL wird nicht direkt übermittelt, sondern zunächst im Batch-Task-Set gespeichert. Wenn die Größe des Task-Sets den angegebenen Schwellenwert erreicht, werden diese SQL zusammen an das MySQL-Ende gesendet. In der Datenskala von 1 Million habe ich den Schwellenwert auf 10.000 festgelegt, d. h. es werden 10.000 SQL-Anweisungen gleichzeitig übermittelt. Das Endergebnis ist ziemlich gut, die Einfügegeschwindigkeit ist etwa 20-mal schneller als zuvor. Der Batch-Einfügungscode lautet wie folgt:

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. Auf die VALUES einer UPDATE-Anweisung folgen mehrere (?,?,?,?)

Ich dachte zunächst, dass diese Methode der oben genannten ähnelt , aber nachdem ich die von anderen durchgeführten Experimente studiert hatte, stellte ich fest, dass die oben beschriebene Verwendung dieser Methode zur Verbesserung der Stapeleinfügung fünfmal schneller sein kann. Später entdeckte ich, dass die Einfügeanweisungen in der von MySQL exportierten SQL-Datei ebenfalls so geschrieben waren. . Das ist UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... . Das heißt, wir müssen eine Zeichenfolge selbst im Hintergrund zusammenfügen. Beachten Sie, dass die Zeichenfolge mit StringBuffer schneller eingefügt werden kann, da sie nur bis zum Ende eingefügt wird. Hier ist der Code:

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后速度也不降低了。原来问题的本质在这里！

Das obige ist der detaillierte Inhalt vonGründe, warum MySQL Daten langsam einfügt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!