mysql のデータ挿入が遅い理由

mysql のデータ挿入が遅い理由: 1. メインコード、外部コード、インデックスにより挿入効率が低下する; 2. このメソッドを挿入するために for ループを使用して連続実行するため; 3. 検索結果が期限内に公開されませんでした。

推奨: 「mysql ビデオ チュートリアル 」「java チュートリアル 」

最近のプロジェクトでは大量のデータをインポートする必要があり、挿入プロセスでもクエリと挿入を同時に行う必要があります。挿入されるデータ量は約100万件です。最初は100万データなんて大した量じゃないと思って、挿して挿してご飯食べて帰ってきたら、50w以上のデータを入れたら、 1秒あたり10個を挿入します。 。すごく不思議な気がするのですが、なぜ挿入すればするほどだんだん遅くなってしまうのでしょうか？そこで、挿入の時間損失を分析し始め、次の解決策を思いつきました: (INNODB エンジンは mysql で使用されています)

1. それがメインで構成されているかどうかを分析します。コード、外部コード、インデックスによる挿入効率の低下

メインコード: メインコードはテーブルごとに必要なため削除できません。 MySQL はメイン コードのインデックスを自動的に作成します。このインデックスはデフォルトでは Btree インデックスであるため、データを挿入するたびに追加の Btree を挿入する必要があります。この余分な挿入時間の複雑さは約 log(n) です。このインデックスは削除できないため、最適化できません。しかし、メインコードの制約により、挿入するたびにメインコードが出現するかどうかを確認する必要があり、そのためには log(n) が必要になります。答えは「はい」です。 プライマリ コードを自動インクリメント ID AUTO_INCREMENT に設定すると、現在の自動インクリメント値が自動的にデータベースに記録され、重複するプライマリ コードが挿入されなくなり、再現性が回避されます。プライマリコードのチェック。

外部コード: プロジェクトの挿入テーブルに外部コードが存在するため、挿入されるたびに別のテーブルで外部コードの存在を検出する必要があります。この制約はビジネス ロジックに関連しているため、気軽に削除することはできません。そして、今回のコストは他のテーブルのサイズに比例する定数である必要があり、挿入が増えても速度が遅くなることはありません。したがって除外されます。

インデックス: Btree 挿入の時間ロスを減らすために、テーブルの作成時に インデックスを作成せずに、最初にすべてのデータを挿入することができます。次に、テーブルにインデックスを追加します。この方法により、実際に時間のオーバーヘッドが削減されます。

上記のトラブルの後、再度テストしてみたところ、速度は少し速くなりましたが、500,000を超えると再び遅くなり始めました。問題の核心はここではないようです。そこで情報の確認を続けたところ、重要な問題が見つかりました:

2. 単一挿入をバッチ挿入に変更する (参考: クリックしてリンクを開きます)

JavaのexecuteUpdate(sql)メソッドはSQL操作だけを行うため、SQLで様々なリソースを呼び出す必要があり、このメソッドをforループで連続実行して挿入すると、間違いなく非常にコストがかかります。したがって、MySQL はバッチ挿入という解決策を提供します。つまり、各 SQL は直接送信されず、最初にバッチ タスク セットに保存されます。タスク セットのサイズが指定されたしきい値に達すると、これらの SQL は mysql エンドに送信されます。 100 万のデータ規模では、しきい値を 10,000 に設定します。つまり、一度に 10,000 の 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 中国語 Web サイトの他の関連記事を参照してください。