Detailed explanation of MySQL batch SQL insert performance optimization

For some systems with large amounts of data, the problems faced by the database include low query efficiency and long data storage time. Especially for a reporting system, the time spent on data import may be as long as several hours or even more than ten hours a day. Therefore, it makes sense to optimize database insert performance.

After some performance tests on MySQL innodb, I found some methods that can improve insert efficiency, which are for your reference.

1. One SQL statement inserts multiple records.

Commonly used insert statements include:

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); 

Modified to:

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 insertion efficiency of the program. The main reason for the high execution efficiency of the second SQL here is that the amount of logs after merging (MySQL binlog and innodb transactions make logs) is reduced, which reduces the amount and frequency of log flushing, thereby improving efficiency. By merging SQL statements, the number of SQL statement parsing can be reduced, and the IO of network transmission can be reduced.

Here are some test comparison data, which are the import of a single data and the conversion into a SQL statement for import, testing 100, 1,000, and 10,000 data records respectively.

2. Perform the insert process in a transaction.

Change the insert to:

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 transactions can improve the efficiency of data insertion. This is because when performing an INSERT operation, MySQL will establish a transaction internally, and the actual insertion processing operation will be performed within the transaction. By using transactions, the cost of creating transactions can be reduced, and all insertions are committed after execution.

A test comparison is also provided here, which is the case of not using transactions and using transactions when the number of records is 100, 1,000, and 10,000.

3. Data is inserted in order.

Ordered data insertion means that the inserted records are arranged in order based on the primary key. For example, datetime is the primary key of the record:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

Modified to:

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); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

Since the database needs to maintain index data when inserting data, out-of-order records will increase the cost of maintaining the index. We can refer to the B+tree index used by innodb. If each record is inserted at the end of the index, the index positioning efficiency is very high and the index adjustment is small. If the inserted record is in the middle of the index, B+tree needs to be split and merged, which will consume more computing resources and the index positioning efficiency of the inserted record will decrease. When the data volume is large, there will be frequent disk operations.

The following provides a performance comparison of random data and sequential data, with records of 100, 1,000, 10,000, 100,000, and 1 million.

From the test results, the performance of the optimization method has been improved, but the improvement is not very obvious.

Comprehensive performance test:

Here is a test of using the above three methods to optimize INSERT efficiency.

From the test results, we can see that the performance improvement of the method of merging data + transactions is obvious when the data volume is small. When the data volume is large (more than 10 million), the performance will drop sharply. This is because the data volume exceeds the capacity of innodb_buffer at this time. Each index positioning involves more disk read and write operations, and the performance drops rapidly. The method of merging data + transactions + ordered data still performs well when the data volume reaches tens of millions or more. When the data volume is large, ordered data index positioning is more convenient and does not require frequent read and write operations on the disk, so a higher performance can be maintained.

Note:

1. SQL statements have a length limit. When merging data in the same SQL, the length of the SQL statement must not exceed the SQL length limit. This can be modified through the max_allowed_packet configuration. The default value is 1M, which was changed to 8M during testing.

2. The transaction size needs to be controlled. If the transaction is too large, it may affect the execution efficiency. MySQL has an innodb_log_buffer_size configuration item. If this value is exceeded, the innodb data will be flushed to the disk, and the efficiency will decrease. Therefore, a better approach is to commit the transaction before the data reaches this value.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.