How to optimize MySQL insert Performance

How to optimize MySQL insert performance for some systems with a large amount of data, in addition to low query efficiency, there is also a very important problem is the long insertion time. 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. Www.2cto.com 1. Insert multiple data records into one SQL statement. Common INSERT statements, such as [SQL] insert INTO 'insert _ table' ('datetime', 'uid', 'content', 'type') VALUES ('0 ', 'userid _ 0', 'content _ 0', 0); insert into 'insert _ table' ('datetime', 'uid', 'content', 'type ') VALUES ('1', 'userid _ 1', 'content _ 1', 1); changed to: [SQL] 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. Rate. 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. Insert multiple records into a single piece of data insert 1 hundred 0.149 s 0.011s1 1.231 11.678 s 0.047s1 0.218 s 2. Insert data into a transaction. Modify the INSERT statement to [SQL] START TRANSACTION; insert INTO 'insert _ table' ('datetime', 'uid', 'content', 'type ') VALUES ('0', 'userid _ 0', 'content _ 0', 0); insert into 'insert _ table' ('datetime', 'uid ', 'content', 'type') VALUES ('1', 'userid _ 1', 'content _ 1', 1 );... COMMIT; using things can improve the efficiency of data insertion, because when performing an INSERT operation, MySQL will create a transaction internally and perform real insertion processing in the transaction. You can use things to reduce the consumption of creating things. All inserts are submitted only after execution. Www.2cto.com also provides a test comparison, where the number of items not used and the number of items used is 10 thousand, and respectively. The number of records does not use transactions 1 hundred 0.149 s 0.033s1 1.231 11.678 s 0.115s1 s 1.050s performance test: here we provide a test to optimize the INSERT efficiency using both of the above two methods. That is, multiple data entries are merged into the same SQL statement and inserted into the transaction. Number of records insert a single data entry merged data + transaction Insert 10 thousand 0m15. 977 s 0m0. 309s10 million 1m52. 204 s 0m2. 271100000 18m31. 317 s 0m23. from the test results in S, we can see that the insert efficiency is increased by about 50 times, which is a very objective number. Note: The length of the www.2cto.com 1. SQL statement is limited. You must not exceed the SQL length limit when merging data in the same SQL statement. You can modify it through the max_allowed_packe configuration. The default value is 1 MB. 2. The transaction size needs to be controlled. A large 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 data level of the configuration item.