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

Xiaobian to share with you MySQL batch SQL insertion performance optimization examples, I hope you read this article after the harvest, let us discuss it together!

For some systems with large amount of data, the database faces problems such as low query efficiency and long storage time. Especially like reporting systems, the time spent importing data per day can be as long as several hours or tens of hours. Therefore, optimizing database insertion performance makes sense.

One SQL statement inserts multiple data

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 ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

The main reason for the high efficiency of SQL execution is that the combined log volume [binlog of mysql and transaction log of InnoDB] is reduced, which reduces the data volume and frequency of log flushing, thus improving efficiency.

By merging SQL statements, you can also reduce the number of SQL statements parsed and reduce IO transmitted over the network.

Test and compare data, which are respectively imported from a single data and converted into an SQL statement for import.

Insert in a transaction

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;

The use of transactions can improve the efficiency of data insertion, because when an insert operation is performed, MySQL will establish a transaction internally, and the real insert processing operation will be performed within the transaction.

By using transactions to reduce the cost of creating transactions, all inserts are committed after execution

Test comparison data, divided into written test not applicable transactions and use transaction operations

ordered insertion of data

Ordered insertion of data is the ordered ordering of inserted records on the primary key

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

Because index data needs to be maintained when a database is inserted, unnecessary records increase the cost of maintaining the index.

Referring to the B+tree index used by InnoDB, if every record inserted is at the end of the index, the index positioning efficiency is 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, which will consume more computing resources, and the index positioning efficiency of the inserted record will decrease, and frequent disk operations will occur when the data volume is large.

Test versus data, random versus sequential performance

Delete index first, rebuild index after insert

Performance Synthesis Test

The method of merging data + transaction improves performance obviously when the data amount is small, and drops sharply when the data amount is large. This is because the data amount exceeds the capacity of innodb_buffer at this time, and more disk read and write operations are involved in each positioning index, so the performance drops rapidly.

The method of merging data + transaction + ordering still performs well when the data volume reaches more than ten million levels. When the data volume is large, the ordered data index is more convenient to locate, and it does not need to frequently read and write to the disk. It can be maintained at a high level.

precautions

SQL statements are limited in length. When merging data in the same SQL, the SQL length limit must not be exceeded. It can be modified by max_allowed_packet configuration. The default is 1M. It can be modified to 8M during testing.

Transactions need to be controlled in size, and things that are too large may affect the efficiency of execution. MySQL has an innodb_log_buffer_size configuration item, exceeding this value will flush innodb data to disk, and efficiency will decrease. So it's a good idea to commit transactions before the data reaches this value.

After reading this article, I believe you have a certain understanding of MySQL batch SQL insertion performance optimization examples, want to know more related knowledge, welcome to pay attention to the industry information channel, thank you for reading!

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.