Relevant introduction to mysql optimization insert performance

mysql insert statement syntax

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

Methods to improve insert performance
1. One The sql statement to insert multiple pieces of data

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

can be written as

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

2. Use transactions

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;

Note
1. There is a limit on the length of sql statements. Please pay attention when merging sql statements. The length limit can be modified through the max_allowed_packet configuration item, and the default is 1M.
2. If the transaction is too large, it will affect the execution efficiency. Mysql has the innodb_log_buffer_size configuration item. If it exceeds this value, disk data will be used, which will affect the execution efficiency.
Description of configuration items about transactions:
innodb_buffer_pool_size
If you use Innodb, then this is an important variable. Compared to MyISAM, Innodb is more sensitive to buffer size. MySIAM may be fine using the default key_buffer_size for large amounts of data, but Innodb feels slow when using the default value for large amounts of data. Innodb's buffer pool will cache data and indexes, so there is no need to leave space for the system cache. If you only use Innodb, you can set this value to 70%-80% of the memory. Same as key_buffer, if the amount of data is relatively small and does not increase much, then do not set this value too high to increase memory usage.
innodb_additional_pool_size
The effect of this is not very 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 uses.
innodb_log_file_size
It is very important when writing a lot, especially large amounts of data. Be aware that larger files provide higher performance, but database recovery will take more time. I generally use 64M-512M, depending on the space of the server. innodb_log_buffer_size
The default value is fine for most applications with medium write operations and short transactions. If you update frequently or use a lot of blob data, you should increase this value. But if it is too large, it is also a waste of memory, because it will always flush (how do you say this word in Chinese?) once every second, so there is no need to set it to more than 1 second. 8M-16M should generally be enough. Small applications can be set smaller. innodb_flush_log_at_trx_commit##Complaining that Innodb is 100 times slower than MyISAM? Then you probably forgot to adjust this value. The default value of 1 means that every transaction commit or instruction outside the transaction needs to write the log to the hard disk (flush), which is very time-consuming. Especially when using battery backed up cache. Setting it to 2 is fine for many applications, especially those transferred from MyISAM tables. It means not writing to the hard disk but writing to the system cache. The logs are still flushed to disk every second, so you generally won't lose more than 1-2 seconds of updates. Setting it to 0 will be faster, but the security is poor. Even if MySQL hangs up, transaction data may be lost. while a value of 2 will only work across the entire operating system Data may be lost only when it hangs.
This article explains the relevant introduction about mysql optimization insert performance. For more information, please pay attention to the PHP Chinese website.

How to use php Commonly used custom methods

How to use XOR encryption through php /Decrypt file

How to get the name of a variable through php

The above is the detailed content of Relevant introduction to mysql optimization insert performance. For more information, please follow other related articles on the PHP Chinese website!