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

This article mainly introduces how mysql should optimize the performance of insert. I hope I can supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in industry information.

Insert statement syntax of mysql

Insert into `table` (`field1`, `field2`) values ('value1','value2')

Methods to improve the performance of insert

1. Insert multiple pieces of data in one sql statement

INSERT INTO `insert_ table` (`uid`, `content`, `type`) VALUES ('userid_0',' content_0', 0); INSERT INTO `insert_ table` (`uid`, `content`, `type`) VALUES ('userid_1',' Table1)

It can be written as

INSERT INTO `insert_ table` (`uid`, `content`, `type`) VALUES ('userid_0',' content_0', 0), ('userid_1',' content_1', 1)

two。 Use transaction

START TRANSACTION;INSERT INTO `insert_ table` (`uid`, `content`, `type`) VALUES ('userid_0',' content_0', 0); INSERT INTO `insert_ table` (`uid`, `content`, `type`) VALUES ('userid_1',' content_1', 1);... COMMIT

Be careful

There is a limit to the length of 1.sql statements, so be careful when merging sql statements. The length limit can be modified through the max_allowed_packet configuration item, which defaults to 1m.

two。 Transactions will affect execution efficiency. Mysql has innodb_log_buffer_size configuration items. Exceeding this value will use disk data and affect execution efficiency.

Configuration item description for transactions:

Innodb_buffer_pool_size

If you use Innodb, this is an important variable. Innodb is more sensitive to buffer size than MyISAM. It may be OK for MySIAM to use the default key_buffer_size for large amounts of data, but Innodb feels like crawling with default values for large amounts of data. Innodb's buffer pool caches data and indexes, so there is no need to leave space for the system's cache. If you only use Innodb, you can set this value to 70% of memory. 80% of memory. As with key_buffer, if the amount of data is small and does not increase much, then not setting this value too high can also increase memory usage.

Innodb_additional_pool_size

The effect of this is not obvious, at least when the operating system can allocate memory reasonably. But you may still need to set it to 20m or more to see how much memory Innodb will allocate for other purposes.

Innodb_log_file_size

It is very important for writing a lot of data, especially when there is a large amount of data. Note that large files provide higher performance, but database recovery takes more time. I usually use 64M-512M, depending on the space of the cloud server. Innodb_log_buffer_size

The default value is fine for most medium write operations and short transactions. If you update frequently or use a lot of blob data, you should increase this value. But too big is also a waste of memory, because 1 second will always flush (how to say this word in Chinese? Once, so you don't need to set it to more than 1 second. 8M-16M should usually be enough. Smaller applications can be made a little smaller. Innodb_flush_log_at_trx_commit

Complain that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk, which is time-consuming. Especially when using battery powered cache (Battery backed up cache). Setting to 2 is OK for many applications, especially those transferred from the MyISAM table, which means writing to the system cache instead of writing to the hard disk. Logs still flush to the hard disk per second, so you don't usually lose updates of more than 1-2 seconds. Setting it to 0 is faster, but the security aspect is poor, and even if the MySQL is down, the transaction data may be lost. A value of 2 will only lose data when the entire operating system is down. This article explains the related introduction of mysql optimizing insert performance, please pay attention to more related content.

Related recommendations:

How to use common customization methods of php

How to encrypt / decrypt files using XOR through php

How to get the name of a variable through php

These are the details about mysql optimizing insert performance, please pay attention to other related articles!

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.