php mysql concurrent solution

In projects, we often encounter When it comes to high concurrency problems, for example, if 100 people make changes to the same data at a certain point in time, this will cause problems, and the resulting data will be inaccurate. The usual methods to solve high concurrency include caching when reading data, writing When data is entered, it is added to the queue. Here are some common methods for dealing with high concurrency for your reference.

1. MySQL batch insertion optimization

For some systems with large amounts of data, in addition to low query efficiency, the database faces problems such as long data storage time. Especially for reporting systems, the time spent on data import may last for several hours or more than ten hours every day. Therefore, it makes sense to optimize database insertion performance.
After some performance tests on MySQL innodb, we found some methods that can improve insert efficiency for your reference.

1. One SQL statement inserts multiple pieces of data.
Commonly used insert statements such as:

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

are modified to:

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

The modified insert operation can improve the insertion efficiency of the program. The main reason why the second SQL execution efficiency is high here is that the amount of logs after merging (MySQL's binlog and innodb's transaction logs) is reduced, which reduces the amount and frequency of log flushing, thereby improving efficiency. By merging SQL statements, it can also reduce the number of SQL statement parsing and reduce network transmission IO.
Here are some test comparison data, which are to import a single piece of data and convert it into a SQL statement for import, and to test 100, 1,000, and 10,000 data records respectively.

#You can also perform insertion processing in a transaction.