Why Multi‑Threaded Inserts Outperform Single Thread in MySQL

Multi‑Threaded Insert (Single Table)

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

Answer: Insertion time is divided as follows:

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, not the write itself.

MySQL’s write phase is exclusive, but each row still requires parsing, ID generation, primary‑key checks, etc.; multithreading reduces overall latency.

Multi‑Threaded Insert (Multiple Tables)

Using partitioned tables with multithreaded inserts.

Prepared SQL

Ordinary SQL uses Statement.

Prepared SQL uses PreparedStatement, which pre‑compiles the statement and improves performance.

Example code:

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (?,?,?,?)";
for (int i = 0; i < m; i++) {
    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()));
        pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.close();
    myBroker.freeConnection(conn);
}

Multi‑Value Insert SQL

Single‑value: INSERT INTO TBL_TEST (id) VALUES(1)

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

Multi‑value inserts reduce total SQL length, network I/O, and connection count, allowing one parse to insert many rows.

Transaction (Commit Every N Statements)

Committing many INSERTs in a single transaction improves performance. Steps:

Change table engine to MyISAM.

Build SQL strings and commit every ~1000 rows.

Example transaction code:

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 about 10 seconds.