Meituan 2: how long did it take you to insert 100W pieces of data into MySQL database?

Time:2021-10-26

Meituan 2: how long did it take you to insert 100W pieces of data into MySQL database?

catalogue

  • Multi threaded insert (single table)
  • Multi threaded insert (multi table)
  • Preprocessing SQL
  • Multi value insert SQL
  • Transaction (n items submitted once)

Multi threaded insert (single table)

Q: why is multi-threaded insertion of the same table faster than single thread? Shouldn’t writes to a table be exclusive at the same time?

A: when inserting data, the overall time allocation is as follows:

  1. Link time (30%)
  2. Send query to server (20%)
  3. Parse query (20%)
  4. Insert operation (10% * number of entries)
  5. Insert Index (10% * number of indexes)
  6. Close links (10%)

It can be seen from here that the real time-consuming process is not the operation, but the link and parsing process.

MySQL inserts data exclusively in the write phase, but inserting a piece of data still needs to be parsed, calculated, and finally written. For example, it needs to be calculated to assign a self increasing ID to each record, verify the unique key attribute of the primary key, or some other logical processing. Therefore, multithreading can improve efficiency.

Multi threaded insert (multi table)

Use multi-threaded insertion after partitioning the table.

Preprocessing SQL

  • General SQL, that is, execute SQL using the statement interface
  • Preprocessing SQLThat is, execute SQL using the Preparedstatement interface

Using the Preparedstatement interface allows the database to precompile SQL statements. In the future, only parameters need to be passed in to avoid compiling SQL statements every time. Therefore, the performance is better.

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 batch 
            pstmt.addBatch(); 
    } 
    pstmt.executeBatch();    // Execute batch 
    pstmt.close(); 
    myBroker.freeConnection(conn); // Connection pool 
}

Multi value insert SQL

  • Normal insert SQLINSERT INTO TBL_TEST (id) VALUES(1)
  • Multi value insert SQLINSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

Using multiple values to insert SQL, the total length of SQL statements is reduced, that is, the network IO is reduced, and the number of connections is reduced. Multiple data can be inserted after one SQL parsing of the database.

Transaction (n items submitted once)

Committing a large number of insert statements in a transaction can improve performance.

1. Modify the storage engine of the table to MyISAM

2. Splice SQL into strings and commit transactions every 1000 or so.

  • Execute multiple SQL statements to realize database transactions.
  • Mysql database
  • Multiple SQL statements
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();
                    }
                    //Later added  
                    if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1))
                    {
                        tx.Commit();
                        tx = conn.BeginTransaction();
                    }
                }
                //tx.Commit();// Original one-time submission  
            }
            catch (System.Data.SqlClient.SqlException   E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }
}

10W data takes about 10s!

Write at the end

Welcome to my official account.Calm as a yard】, a large number of Java related articles and learning materials will be updated in it, and the sorted materials will also be put in it.

If you think it’s good, just like it and pay attention! Pay attention, don’t get lost, keep updating!!!