Boost MySQL Insert Speed with Multithreading, Prepared Statements, and Batch Techniques

Multithreaded Insert (Single Table)

Question: Why does inserting into the same table with multiple threads run faster than a single thread? Shouldn't write operations be exclusive?

Answer: The overall time distribution for an insert operation is roughly:

Connection overhead (30%)

Sending query to server (20%)

Parsing query (20%)

Insert operation (10% × number of rows)

Insert index (10% × number of indexes)

Closing connection (10%)

The real bottleneck is the connection and parsing phases, not the write itself.

Although MySQL write phase is exclusive, each row still requires parsing, ID generation, primary‑key checks, etc.; multithreading can parallelize these CPU‑bound steps and improve throughput.

Multithreaded Insert (Multiple Tables / Partitioned)

Use multithreading after partitioning tables.

Prepared SQL

Ordinary SQL using Statement

Prepared SQL using PreparedStatement

PreparedStatement lets the database pre‑compile the SQL, so subsequent executions only need to bind parameters, reducing compilation overhead and improving performance.

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 to batch
        pstmt.addBatch();
    }
    pstmt.executeBatch(); // execute batch
    pstmt.close();
    myBroker.freeConnection(conn); // return connection to pool
}

Multi‑Value Insert SQL

Single‑value: INSERT INTO TBL_TEST (id) VALUES(1) Multi‑value: INSERT INTO TBL_TEST (id) VALUES (1), (2), (3) Using multi‑value inserts reduces total SQL length, lowers network I/O and connection count, and allows the database to parse once and insert many rows.

Transaction (Commit Every N Statements)

Committing a large number of INSERT statements within a single transaction improves performance.

Typical optimizations: switch table engine to MyISAM and commit every ~1000 statements.

/// <summary>
/// Execute multiple SQL statements as a transaction.
/// </summary>
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();
                    }
                    if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1))
                    {
                        tx.Commit();
                        tx = conn.BeginTransaction();
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }
}

Inserting 100,000 rows takes roughly 10 seconds.