Detailed explanation of MySQL batch SQL insert performance optimization

For some systems with large amounts of data, 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.

Recommended: "mysql tutorial"

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

1. Insert multiple pieces of data with one SQL statement

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

Using transactions can improve the efficiency of data insertion. This is because when an INSERT operation is performed, a transaction will be established internally in MySQL, and only within the transaction Perform actual insert processing operations. By using transactions, you can reduce the cost of creating transactions. All inserts are executed before committing.

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, 1000, and 10000.

#3. Data is inserted 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, the B tree needs to be split and merged. Waiting for processing 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 been improved, but the improvement is not very obvious.

4. Comprehensive performance test

Here provides a test that uses the above three methods at the same time 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 (10 million Above), 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 to order data still performs well when the data volume reaches tens of millions. When the data volume is large, the positioning of the ordered data index is more convenient and does not require frequent read and write operations on the disk, so it can Maintain high performance.

Notes:

1. SQL statements have a length limit. When merging data, the SQL length limit must not be exceeded in the same SQL. It can be modified through the max_allowed_packet configuration. , the default is 1M, and was 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 Detailed explanation of MySQL batch SQL insert performance optimization. For more information, please follow other related articles on the PHP Chinese website!