Author: lonely key guest
https://juejin.im/post/5d255ab9e51d454f73356dcd

Multi threaded insert (single table)

Q: why is multi-threaded insertion of the same table faster than single thread? Shouldn't writes to a table be exclusive at the same time?

A: when inserting data, the overall time allocation is as follows:

• Link time (30%)

• Send query to server (20%)

• Parse query (20%)

• Insert operation (10% * number of entries)

• Insert index (10% * number of indexes)

• Close links (10%)

It can be seen from here that the real time-consuming process is not the operation, but the link and parsing process.

MySQL inserts data exclusively in the write phase, but inserting a piece of data still needs to be parsed, calculated, and finally written. For example, it needs to be calculated to assign a self increasing id to each record, verify the unique key attribute of the primary key, or some other logical processing. Therefore, multithreading can improve efficiency.

Multi threaded insert (multi table)

Use multi-threaded insertion after partitioning the table.

Preprocessing SQL

• Normal SQL, that is, execute SQL using the Statement interface

• Preprocess SQL, that is, execute SQL using the PreparedStatement interface

Using the PreparedStatement interface allows the database to precompile SQL statements. In the future, only parameters need to be passed in to avoid compiling SQL statements every time. Therefore, the performance is better.

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (?, ?, ?, ?)";
for (int i = 0; i < m; i++) {
    //Get connection from pool
    Connection conn = myBroker.getConnection();
    PreparedStatement pstmt = conn.prepareStatement(sql);
    for (int k = 0; k < n; k++) {
            pstmt.setString(1, RandomToolkit.generateString(12));
            pstmt.setString(2, RandomToolkit.generateString(24));
            pstmt.setDate(3, new Date(System.currentTimeMillis()));
            pstmt.setDate(4, new Date(System.currentTimeMillis()));
            //Add batch
            pstmt.addBatch();
    }
    pstmt.executeBatch();    //Execute batch
    pstmt.close();
    myBroker.freeConnection(conn); //Connection pool
}

Multi value insert SQL

• Normal insert SQL: INSERT INTO TBL_TEST (id) VALUES(1)

• Multi value insert SQL: INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

Using multiple values to insert SQL, the total length of SQL statements is reduced, that is, the network IO is reduced, and the number of connections is reduced. Multiple data can be inserted after one SQL parsing of the database.

Transaction (N items submitted once)

Committing a large number of INSERT statements in a transaction can improve performance.

1. Modify the storage engine of the table to InnoDB
2. Splice sql into strings and commit transactions every 1000 or so.

/// <summary>
///Execute multiple SQL statements to realize database transactions.
///< / summary > MySQL database
///< param name = "sqlstringlist" > multiple SQL statements < / param >
public void ExecuteSqlTran(List<string> SQLStringList)
{
    using (MySqlConnection conn = new MySqlConnection(connectionString))
    {
        if (DBVariable.flag)
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            MySqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                    //Later added
                    if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1))
                    {
                        tx.Commit();
                        tx = conn.BeginTransaction();
                    }
                }
                //tx.Commit();// Original one-time submission
            }
            catch (System.Data.SqlClient.SqlException E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }
}

10w data takes about 10s!

Recent hot article recommendations:

1.Java 15 officially released, 14 new features, refresh your understanding!!

2.Finally got the IntelliJ IDEA activation code through the open source project. It's really fragrant!

3.I wrote a section of logic in Java 8. My colleagues can't understand it directly. Try it..

4.Drop Tomcat, the performance of underwow is very explosive!!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!