Raisons pour lesquelles MySQL insère les données lentement

Les raisons pour lesquelles MySQL insère les données lentement : 1. L'efficacité de l'insertion est réduite en raison du code principal, du code étranger et de l'index 2. En raison de l'utilisation d'une boucle for pour exécuter en continu cette méthode d'insertion ; ; 3. Défaut de publication à temps des résultats de la requête.

Recommandé : "tutoriel vidéo MySQL" "tutoriel Java"

Les projets récents nécessitent l'importation d'une grande quantité de données, et le processus d'insertion nécessite également une interrogation et une insertion en même temps. La quantité de données insérées est d'environ 1 million. Au début, je pensais qu'un million de données n'était pas une grosse quantité, alors je me suis branché et branché et j'ai pris un repas. Quand je suis revenu, j'ai vu qu'après avoir inséré plus de 50 W de données, je ne pouvais en insérer que 10. morceaux par seconde. . Je me sens très étrange, pourquoi cela devient-il de plus en plus lent à mesure que je l'insère ? J'ai donc commencé à analyser la perte de temps d'insertion, et j'ai trouvé la solution suivante : (Moteur INNODB utilisé par mysql)

1. Analyser s'il est composé de main code, code étranger, efficacité d'insertion réduite causée par l'index

Code principal : le code principal étant requis pour chaque table, il ne peut pas être supprimé. MySQL créera automatiquement un index pour le code principal. Cet index est un index Btree par défaut, donc chaque fois que vous insérez des données, vous devez insérer un Btree supplémentaire. Cette complexité supplémentaire du temps d'insertion concerne log(n). Cet index ne peut pas être supprimé et ne peut donc pas être optimisé. Mais à chaque fois qu'il est inséré, en raison de la contrainte du code principal, il est nécessaire de vérifier si le code principal apparaît, ce qui nécessite log(n). Cette surcharge peut-elle être réduite ? La réponse est oui. Nous pouvons définir le code principal sur l'identifiant d'auto-incrémentation AUTO_INCREMENT , de sorte que la valeur actuelle d'auto-incrémentation soit automatiquement enregistrée dans la base de données pour garantir qu'aucun code primaire en double ne sera inséré, évitant ainsi la répétabilité vérification du code primaire.

Code étranger : Puisqu'il y a un code étranger dans la table d'insertion de mon projet, je dois détecter l'existence du code étranger dans une autre table à chaque fois que je l'insère. Cette contrainte est liée à la logique métier et ne peut être supprimée par hasard. Et ce coût en temps doit être constant et proportionnel à la taille de l’autre table, et ne doit pas devenir plus lent avec plus d’insertions. Donc exclu.

Index : Afin de réduire la perte de temps d'insertion de Btree, on peut ne pas créer d'index lors de la création de la table, et insérer d'abord toutes les données. Nous ajouterons ensuite des index à la table. Cette méthode réduit en effet le temps perdu.

Après les problèmes ci-dessus, j'ai testé à nouveau et j'ai constaté que la vitesse était un peu plus rapide, mais elle a recommencé à ralentir après avoir atteint 500 000 articles. Il semble que le nœud du problème ne soit pas là. J'ai donc continué à vérifier les informations et j'ai trouvé un autre problème clé :

2. Changer l'insertion unique en insertion par lots (référence : cliquez pour ouvrir le lien)

Étant donné que la méthode executeUpdate(sql) en Java n'effectue qu'une opération SQL, elle doit appeler diverses ressources en SQL. Si vous utilisez une boucle for pour exécuter en continu cette méthode à insérer, cela coûtera sans aucun doute très cher. Par conséquent, MySQL propose une solution : l'insertion par lots. C'est-à-dire que chaque sql n'est pas soumis directement, mais est d'abord stocké dans l'ensemble de tâches par lots Lorsque la taille de l'ensemble de tâches atteint le seuil spécifié, ces sql seront envoyés ensemble du côté mysql . . Sur une échelle de données de 1 million, j'ai fixé le seuil à 10 000, c'est-à-dire que 10 000 instructions SQL sont soumises en même temps. Le résultat final est plutôt bon, la vitesse d'insertion est environ 20 fois plus rapide qu'avant. Le code d'insertion par lots est le suivant :

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. Les VALUES d'une instruction UPDATE sont suivies de plusieurs (?,?,?,?)

Au début, je pensais que cette méthode était similaire à celle ci-dessus, mais après avoir lu les expériences faites par d'autres, j'ai découvert qu'utiliser cette méthode pour améliorer l'insertion par lots ci-dessus peut être 5 fois plus rapide. Plus tard, j'ai découvert que les instructions d'insertion dans le fichier SQL exporté par MySQL étaient également écrites comme ceci. . C'est UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... . C'est-à-dire que nous devons assembler une chaîne nous-mêmes en arrière-plan. Notez que puisque la chaîne n'est insérée que jusqu'à la fin, l'utilisation de StringBuffer peut l'insérer plus rapidement. Voici le 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后速度也不降低了。原来问题的本质在这里！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!