How to optimize MySQL insert performance and mysqlinsert

How to optimize MySQL insert performance and mysqlinsert

For systems with large data volumes, the query efficiency is low, and the insertion time is long. We have a business system. It takes 4-5 minutes to import data every day. This time-consuming operation is actually very risky. If the program has a problem, it is a pain to re-run the operation. Therefore, it is necessary to improve the efficiency of MySQL insert in a large data volume system.

After testing MySQL, we found some ways to improve the insert efficiency for your reference.

1. Insert multiple data records into one SQL statement.

Common insert statements are as follows:

1. Insert into 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('0', 'userid _ 0 ', 'content _ 0', 0 );
2. Insert into 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('1', 'userid _ 1 ', 'content _ 1', 1 );

Modify:

1. Insert into 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('0', 'userid _ 0 ', 'content _ 0', 0), ('1', 'userid _ 1', 'content _ 1', 1 );

The modified insert operation can improve the insert efficiency of the program. There are two main reasons for the high efficiency of the second SQL statement. One is to reduce the SQL statement parsing operation, and only one time can be parsed to insert data. The other is that the SQL statement is short, it can reduce the I/O of network transmission.

Here we provide some test and comparison data, namely, importing a single piece of data and converting it into an SQL statement, respectively, testing 1 million, 1 thousand, and 10 thousand data records.

2. Insert a transaction. Modify the insert statement:

1. Start transaction;
2. Insert into 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('0', 'userid _ 0 ', 'content _ 0', 0 );
3. Insert into 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('1', 'userid _ 1 ', 'content _ 1', 1 );
4. ...
5. COMMIT;

The use of transactions can improve data insertion efficiency, because during an INSERT operation, MySQL creates a transaction internally to perform real insertion processing in the transaction. You can use transactions to reduce the consumption of creating transactions. All inserts are committed only after execution.

The test comparison is also provided here, where no transaction is used and the number of transactions used is 10 thousand, and respectively.

Performance test: the above two methods are provided for the INSERT efficiency optimization test. That is, multiple data entries are merged into the same SQL statement and inserted into the transaction.

From the test results, we can see that the insert efficiency is about 50 times higher, which is a very objective number.

Note:

1. SQL statements have a length limit. data merging in the same SQL statement must not exceed the SQL length limit. You can modify the value through max_allowed_packet. The default value is 1 MB.

2. The transaction size needs to be controlled. Too large a transaction may affect the execution efficiency. MySQL has the innodb_log_buffer_size configuration item. If this value is exceeded, the log will use disk data, and the efficiency will decrease. Therefore, it is better to submit a transaction before the transaction size reaches the configuration item data level.