The reasons why mysql inserts data slowly: 1. The insertion efficiency is reduced due to the main code, foreign code, and index; 2. Due to the use of a for loop to continuously execute this method to insert; 3. Failure to release in time search result.

Recommended: "mysql video tutorial" "java tutorial"

Recent projects require importing a large amount of data, and the insertion process also requires querying and inserting at the same time. The amount of data inserted is about 1 million. At first, I felt that 1 million pieces of data was not a large amount, so I plugged in and plugged in, had a meal, and when I came back, I found that after inserting more than 50w pieces of data, I could only insert 10 pieces per second. . I feel very strange, why does it become slower and slower the more I insert it? So I started to analyze the time loss of insertion, and came up with the following solution: (INNODB engine used by mysql)

1. Analyze whether it is composed of main code, foreign code, Reduced insertion efficiency caused by index

Main code: Since the main code is required for every table, it cannot be deleted. MySQL will automatically create an index for the main code. This index is a Btree index by default, so each time you insert data, you need to insert an additional Btree. This extra insertion time complexity is about log(n). This index cannot be deleted and therefore cannot be optimized. But every time it is inserted, due to the main code constraint, it is necessary to check whether the main code appears, which requires log(n). Can this overhead be reduced? The answer is yes. We canset the primary code to the auto-increment id AUTO_INCREMENT, so that the current auto-increment value will be automatically recorded in the database to ensure that no duplicate primary code will be inserted, thus avoiding the repeatability check of the primary code.

Foreign code: Since the foreign code exists in the insertion table of my project, the existence of the foreign code needs to be detected in another table each time it is inserted. This constraint is related to business logic and cannot be deleted casually. And this time cost should be a constant proportional to the size of the other table, and should not become slower with more inserts. So excluded.

Index: In order to reduce the time loss of Btree insertion, we can not create an index when creating the table, and insert all the data first. We will then add indexes to the table. This method indeed reduces time overhead.

After the above troubles, I tested it again and found that the speed was a little faster, but it started to slow down again after reaching 500,000. It seems that the crux of the problem is not here. So I continued to check the information and found a key problem:

2. Change single insertion to batch insertion (reference: click to open the link)

Since the executeUpdate(sql) method in Java only performs a SQL operation, it needs to call various resources in SQL. If you use a for loop to continuously execute this method to insert, it will undoubtedly be very expensive. Therefore, MySQL provides a solution: batch insert. That is to say, each sql is not submitted directly, but is first stored in the batch task set. When the size of the task set reaches the specified threshold, these sql are then sent to the mysql end. In the data scale of 1 million, I set the threshold to 10,000, that is, 10,000 SQL statements are submitted at one time. The final result is pretty good, the insertion speed is about 20 times faster than before. The batch insertion code is as follows:

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. The VALUES of an UPDATE statement is followed by multiple (?,?,?,?)

I thought this method was similar to the one above at first, but after reading experiments done by others, I found that using this method to improve the batch insertion above can be 5 times faster. Later I discovered that the insert statements in the MySQL exported SQL file were also written like this. . That is UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... . That is to say, we need to splice a string ourselves in the background. Note that since the string is only inserted to the end, it can be inserted faster using StringBuffer. Here is the 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("");  
            // 外層循環(huán)，總提交事務次數(shù)  
            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);  
                // 添加執(zhí)行sql  
                pst.addBatch(sql);  
                // 執(zhí)行操作  
                pst.executeBatch();  
                // 提交事務  
                conn.commit();  
                // 清空上一次添加的數(shù)據(jù)  
                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");  
    }

? ? ? ? 做了以上的優(yōu)化后，我發(fā)現(xiàn)了一個很蛋疼的問題。雖然一開始的插入速度的確快了幾十倍，但是插入了50w條數(shù)據(jù)后，插入速度總是會一下突然變的非常慢。這種插入變慢是斷崖式的突變，于是我冥思苦想，無意中打開了系統(tǒng)的資源管理器，一看發(fā)現(xiàn)：java占用的內(nèi)存在不斷飆升。 突然腦海中想到：是不是內(nèi)存溢出了？

4.及時釋放查詢結果

? ? ? ? 在我的數(shù)據(jù)庫查詢語句中，使用到了pres=con.prepareStatement(sql)來保存一個sql執(zhí)行狀態(tài)，使用了resultSet=pres.executeQuery來保存查詢結果集。而在邊查邊插的過程中，我的代碼一直沒有把查詢的結果給釋放，導致其不斷的占用內(nèi)存空間。當我的插入執(zhí)行到50w條左右時，我的內(nèi)存空間占滿了，于是數(shù)據(jù)庫的插入開始不以內(nèi)存而以磁盤為介質了，因此插入的速度就開始變得十分的低下。因此，我在每次使用完pres和resultSet后，加入了釋放其空間的語句：resultSet.close(); pres.close(); 。重新進行測試，果然，內(nèi)存不飆升了，插入數(shù)據(jù)到50w后速度也不降低了。原來問題的本質在這里！

The above is the detailed content of Reasons why mysql inserts data slowly. For more information, please follow other related articles on the PHP Chinese website!