How to optimize MySQL insertion performance

Let's talk about how to optimize the performance of MySQL insertion. The secret of the text is to be close to the topic. So, no gossip, let's go straight to the following, and I'm sure you'll benefit from reading this article on how to optimize MySQL insertion performance.

Construction of cp website [Penguin 21717-93408]

The insertion performance of MySQL can be optimized from the following aspects.

Code optimization

Multiple values

That is, splicing into an insert values sql, such as

INSERT INTO MyTable

(Column1, Column2, Column3)

VALUES

('John', 123,' Lloyds Office')

('Jane', 124,' Lloyds Office')

('Billy', 125,' London Office')

('Miranda', 126,' Bristol Office')

A transaction

Open a transaction and commit the transaction after the batch operation, instead of committing the transaction once at a time, so the io is too high and the insertion is too slow.

Insert as few fields as possible and use default values as much as possible

Note: max_allowed_packet defaults to 1m. This value needs to be raised if the insert values sql is too large.

Is there anything you don't understand about how to optimize MySQL insertion performance above? Or if you want to know more about it, you can continue to follow our industry information section.