Optimizing MySQL Inserts with Multithreading, Prepared Statements, Batch Execution, and Transactions

When inserting data into MySQL, the total execution time can be broken down into connection overhead (30%), query transmission (20%), query parsing (20%), the actual insert operation (10% × row count), index updates (10% × index count), and connection closing (10%). The most time‑consuming parts are the connection and parsing processes.

Although MySQL’s write phase is exclusive, each row still requires parsing, auto‑increment ID allocation, primary‑key uniqueness checks, and other logic, so multithreading can improve overall efficiency.

Multithreaded Insert (Single Table)

Using multiple threads to insert into the same table can be faster than a single thread because the overhead of connections and parsing is distributed across threads.

Multithreaded Insert (Multiple Tables)

After partitioning data into multiple tables, multithreaded insertion can be applied to each table to further improve throughput.

Prepared SQL

Using the PreparedStatement interface allows the database to pre‑compile the SQL statement; subsequent executions only need to bind parameters, avoiding repeated compilation 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

Normal insert SQL: INSERT INTO TBL_TEST (id) VALUES(1) Multi‑value insert SQL: INSERT INTO TBL_TEST (id) VALUES (1), (2), (3) Using multi‑value inserts reduces the total SQL length, lowers network I/O, decreases the number of connections, and allows the database to parse the statement once while inserting multiple rows.

Transaction (N Statements per Commit)

Submitting a large number of INSERT statements within a single transaction can greatly improve performance. The example below commits every 1,000 statements (or at the end of the list) and rolls back on error.

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();
                    }
                    // Commit every 1000 statements or at the last statement
                    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 roughly 100,000 rows takes about 10 seconds.

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

References: https://www.cnblogs.com/aicro/p/3851434.html http://blog.jobbole.com/29432/