How to optimize performance? Detailed explanation of examples of MySQL implementing batch insertion to optimize performance

This article mainly introduces the tutorial of MySQL to implement batch insertion to optimize performance. The running time is given in the article to indicate the comparison after performance optimization. Friends in need can refer to it

For some data with large amounts of data, In large systems, the database faces not only low query efficiency but also long data storage time. Especially for reporting systems, the time spent on data import may last for several hours or more than ten hours every day. Therefore, it makes sense to optimize database insertion performance.
After some performance tests on MySQL innodb, we found some methods that can improve insert efficiency for your reference.

1. One SQL statement inserts multiple pieces of data.
Commonly used insert statements such as

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

are 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 why the second SQL execution efficiency is high here is that the amount of logs after merging (MySQL's binlog and innodb's transaction logs) are reduced, which reduces the amount and frequency of log flushing, thereby improving efficiency. By merging SQL statements, it can also reduce the number of SQL statement parsing and reduce network transmission IO.
Here are some test comparison data, which are to import a single piece of data and convert it into a SQL statement for import, and to test 100, 1,000, and 10,000 data records respectively.

#2. Perform insertion processing in the transaction.
Change the insertion 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;

3. Insert data in order.
Orderly insertion of data means that the inserted records are arranged in order 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);

is 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, disordered records will increase the cost of maintaining the index. We can refer to the B+tree index used by innodb. If each inserted record is 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 is required. Processes such as splitting and merging will consume more computing resources, and the index positioning efficiency of inserted records will decrease. When the amount of data is large, there will be frequent disk operations.
The following provides a performance comparison of random data and sequential data, which are recorded as 100, 1000, 10000, 100000 and 1 million respectively.

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

Comprehensive performance test:
Here is a test that uses the above three methods to optimize INSERT efficiency.

It can be seen from the test results that the performance improvement of the method of merging data + transactions is obvious when the amount of data is small. When the amount of data is large, the performance improvement is obvious. (more than 10 million), the performance will drop sharply. This is because the amount of data exceeds the capacity of innodb_buffer at this time. Each index positioning involves more disk read and write operations, and the performance drops quickly. The method of using merged data + transactions + ordered data still performs well when the data volume reaches tens of millions. When the data volume is large, the ordered data index positioning is more convenient and does not require frequent read and write operations on the disk. Therefore, high performance can be maintained.

Notes:
1. SQL statements have a length limit. When merging data in the same SQL, the SQL length limit must not be exceeded. It can be modified through the max_allowed_packet configuration. The default is 1M, modified to 8M during testing.
2. Transactions need to be controlled in size. If a transaction is too large, it may affect execution efficiency. MySQL has the innodb_log_buffer_size configuration item. If this value is exceeded, the innodb data will be flushed to the disk. At this time, the efficiency will decrease. So a better approach is to commit the transaction before the data reaches this value.

The above is the detailed content of How to optimize performance? Detailed explanation of examples of MySQL implementing batch insertion to optimize performance. For more information, please follow other related articles on the PHP Chinese website!