Shulou(Shulou.com)06/01 Report--

This article mainly introduces the introduction of MySQL batch SQL insertion performance optimization, has a certain reference value, friends in need can refer to. Let's take a look at it with me.

For some systems with a large amount of data, the problem faced by the database is not only the low query efficiency, but also the long time of data storage. Especially like a reporting system, the time spent on data import can be as long as several hours or more than a dozen hours a day. Therefore, it makes sense to optimize database insertion performance.

After some performance tests of MySQL InnoDB, some methods that can improve the efficiency of insert are found 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 ('0mm,' userid_0', 'content_0', 0)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('1customers,' userid_1', 'content_1', 1)

Modified to:

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0th,' userid_0', 'content_0', 0), (' 1th, 'userid_1',' content_1', 1)

The modified insertion operation can improve the insertion efficiency of the program. The main reason for the high efficiency of the second kind of SQL is that the log volume (MySQL's binlog and innodb's transactions let the log) be reduced after the merger, and the data amount and frequency of log flushing are reduced, thus improving the efficiency. By merging SQL statements, it can also reduce the number of parsing of SQL statements and reduce the IO of network transmission.

Some test comparison data are provided here, which are to import a single piece of data and convert it into a SQL statement for import, testing 100,1000 and 10,000 data records respectively.

2. Insert processing in the transaction.

Modify the insert to:

START TRANSACTION

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0mm,' userid_0', 'content_0', 0)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('1customers,' userid_1', 'content_1', 1)

...

COMMIT

Using transactions can improve the efficiency of data insertion, because when you perform an INSERT operation, a transaction is established within the MySQL, and the actual insert processing is performed within the transaction. By using transactions, you can reduce the consumption of creating transactions, and all inserts are committed after execution.

A test comparison is also provided here, where the number of records is 100, 1000, and 10, 000, respectively, when no transactions are used and transactions are used.

3. Data is inserted in an orderly manner.

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 ('1customers,' userid_1', 'content_1', 1)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0mm,' userid_0', 'content_0', 0)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('2percent,' userid_2', 'content_2',2)

Modified to:

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0mm,' userid_0', 'content_0', 0)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('1customers,' userid_1', 'content_1', 1)

INSERT INTO `insert_ Table` (`datetime`, `uid`, `content`, `type`)

VALUES ('2percent,' userid_2', 'content_2',2)

Because the index data needs to be maintained when the database is inserted, unordered 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 back of the index, the positioning efficiency of the index is very high, and the adjustment to the index is small; if the inserted record is in the middle of the index, B+tree is required to split and merge, which will consume more computing resources, and the index positioning efficiency of inserting records will decline, and there will be frequent disk operations when the amount of data is large.

The performance comparison between random data and sequential data is provided below, which is recorded as 100,1000,10,000, 100000, and 1 million, respectively.

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

4. Comprehensive performance test

Here is a test of INSERT efficiency optimization using the above three methods at the same time.

From the test results, we can see that the performance improvement of the method of merging data + transactions is obvious when the amount of data is small, and the performance will decline sharply when the amount of data is large (more than 10 million). This is because the amount of data exceeds the capacity of innodb_buffer, and each positioning index involves more disk read and write operations, resulting in a rapid decline in performance. However, the performance of merging data + transaction + ordered data is still good when the amount of data reaches tens of millions of levels. when the amount of data is large, the location of ordered data index is more convenient, and there is no need to read and write to the disk frequently. so you can maintain high performance.

Note:

1. There is a length limit for SQL statements, which must not exceed the SQL length limit in the same SQL for data merging. It can be modified through max_allowed_packet configuration. The default is 1m, and it is changed to 8m during testing.

2. The size of the transaction needs to be controlled, and too large a transaction may affect the efficiency of execution. MySQL has an innodb_log_buffer_size configuration item. If you exceed this value, the data of innodb will be brushed to disk, and the efficiency will be reduced. So it is better to commit the transaction before the data reaches this value.

The above is a brief introduction to MySQL batch SQL insertion performance optimization, of course, the detailed use of the above differences also need to be used by everyone to understand. If you want to know more, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.