Improving MySQL Insert Performance with Multithreading, Prepared Statements, and Batch Transactions

Multithreaded Insert (Single Table)

Question: Why does inserting into the same table with multiple threads run faster than a single thread, given that write operations should 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)

Index insertion (10% × number of indexes)

Closing connection (10%)

The most time‑consuming parts are the connection and parsing phases, not the actual write. Although MySQL’s write phase is exclusive, each row still requires parsing, ID generation, primary‑key checks, and other calculations, so multithreading can improve overall efficiency.

Multithreaded Insert (Multiple Tables)

After sharding tables, use multiple threads to insert data concurrently.

Prepared SQL

Ordinary SQL: executed via the Statement interface.

Prepared SQL: executed via the PreparedStatement interface.

Using PreparedStatement allows the database to pre‑compile the SQL once and then reuse it with different 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 a 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 to batch
        pstmt.addBatch();
    }
    pstmt.executeBatch(); // Execute batch
    pstmt.close();
    myBroker.freeConnection(conn); // Return connection to pool
}

Multi‑Value INSERT SQL

Ordinary INSERT: INSERT INTO TBL_TEST (id) VALUES (1)

Multi‑value INSERT: INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

Using multi‑value INSERT reduces the total SQL length, cuts network I/O, lowers the number of connections, and allows a single parse to insert many rows.

Transaction (Commit N Statements at Once)

Submitting a large number of INSERT statements within a single transaction can greatly improve performance. Common tricks include switching the table engine to MyISAM and committing every ~1000 statements.

///
/// Execute multiple SQL statements as a database transaction.
///
public void ExecuteSqlTran(List
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 can take roughly 10 seconds with these optimizations.

End Note

If you enjoyed this article, please share, like, and star the public account to receive future updates. Feel free to comment on the resources you need.