This is the 26th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Multithreaded insert (single table)

Q: Why is a multithreaded insert to the same table faster than a single thread? Shouldn’t simultaneous writes to a table be exclusive?

A: When inserting data, the overall time allocation looks like this:

  • Link time (30%)

  • Sending a query to the server (20%)

  • Parse query (20%)

  • Insert operations (10% * number of entries)

  • Insert index (10% * number of indexes)

  • Close links (10%)

From this you can see that the real time is not the operation, but the link, parsing process.

MySQL inserts data exclusively in the write phase, but inserts a data still need to be parsed, calculated, and finally performed write processing, such as to assign an increment ID to each record, verify the primary key unique key attribute, or some other logical processing, all need to be calculated, so it is said that multi-threading can improve efficiency.

Multi-threaded insert (multi-table)

Use multithreaded inserts after partitioning the table.

Pretreatment of SQL

  • The Statement interface is used to execute SQL

  • Pre-processed SQL, which is executed using the PreparedStatement interface

Using the PreparedStatement interface allows the database to precompile SQL statements and later pass in only parameters, avoiding the database from compiling SQL statements every time, so performance is better.

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (? ,? ,? ,?) ";
for (int i = 0; i < m; i++) {
    // Get the connection from the 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 processing
            pstmt.addBatch();
    }
    pstmt.executeBatch();    // Perform batch processing
    pstmt.close();
    myBroker.freeConnection(conn); // connect to the return pool
}
Copy the code

Multi-value insert SQL

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

  • INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

Using multiple values to insert SQL reduces the total length of SQL statements, which reduces network IO and connection times. The database can insert multiple data after SQL parsing.

Transaction (N commit at a time)

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

  1. Change the storage engine for the table to myISAM
  2. Concatenate SQL into strings and commit transactions every 1000 or so.
/// <summary>
        // execute multiple SQL statements to implement database transactions.
        /// mysql database
        // 
        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();
                            }
                            // It was added later
                            if (n > 0 && (n % 1000= =0 || n == SQLStringList.Count - 1)) { tx.Commit(); tx = conn.BeginTransaction(); }}//tx.Commit(); // Commit once
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        tx.Rollback();
                        throw newException(E.Message); }}}}Copy the code

10W pieces of data take about 10s!