Optimizing MySQL Insert Performance with Multithreading, Prepared Statements, Batch Inserts, and Transactions

Question: Why can inserting into the same MySQL table with multiple threads be faster than using a single thread, even though write operations on a table are expected to be exclusive?

Time distribution of an insert operation:

1. Multiple connections – 30%

2. Sending queries to the server – 20%

3. Parsing queries – 20%

4. Actual insert work – 10% × number of rows

5. Index insertion – 10% × number of indexes

6. Closing connections – 10%

From this breakdown it is clear that the real bottleneck is not the write itself but the overhead of connections and query parsing.

MySQL locks the write phase, but each row still requires parsing, ID generation, primary‑key checks, etc.; therefore parallelizing these CPU‑bound steps can improve overall throughput.

Multithreaded Insert (Multiple Tables)

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

Prepared SQL (PreparedStatement)

Using PreparedStatement lets the database pre‑compile the SQL once and reuse it with different parameters, avoiding repeated parsing 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: INSERT INTO TBL_TEST (id) VALUES(1)

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

Using multi‑value inserts reduces the total SQL length, lowers network I/O, and decreases the number of connections, allowing many rows to be inserted with a single parse.

Transaction (Commit Every N Rows)

Committing a large number of INSERT statements within a single transaction greatly improves performance. Common practice: switch the table engine to MyISAM, batch ~1000 rows per transaction, and commit.

public void ExecuteSqlTran(List
SQLStringList) {
    using (MySqlConnection conn = new MySqlConnection(connectionString)) {
        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);
        }
    }
}

In tests, inserting 100,000 rows took roughly 10 seconds.

References:

https://blog.csdn.net/qq_36691683/article/details/89297261

https://www.cnblogs.com/aicro/p/3851434.html

http://blog.jobbole.com/29432/