How mysql handles high concurrency

Mysql high concurrency solutions include: 1. Optimize SQL statements; 2. Optimize database fields; 3. Add cache; 4. Partition tables; 5. Read and write separation and vertical split; 6. Decoupling Modules, horizontal slicing, etc.

Most of the bottlenecks in high concurrency are in the background. The normal optimization plan for storing mysql is as follows:

(1) Optimization of sql statements in the code

(2) Database field optimization, index optimization

(3) Add cache, redis/memcache, etc.

(4) Master-slave, read-write separation

(5) Partition table

(6) Vertical split, decoupled module

(7) Horizontal split

Video course recommendation →：《 tens of millions of data concurrency solutions (theoretical practice)》

Program analysis:

1. Method 1 and Method 2 are the simplest and the fastest way to improve efficiency. Because every statement hits the index, it is the most efficient. However, if the index is built to optimize SQL, then the index will be overflowing. For tables with tens of millions or more, the cost of maintaining the index will be greatly increased, which will in turn increase the memory overhead of the database.

2. Optimization of database fields. A senior programmer once discovered that in the design of table fields, a date type was designed as a varchar type. While it was not standardized, the written data could not be verified, and the efficiency of indexing was also different

3. Caching is suitable for business scenarios with relatively low read, write, and relatively low frequency of updates. Otherwise, there will be little cache objection and the hit rate will not be high. Caching is usually mainly used to improve interface processing speed, reduce DB pressure caused by concurrency and other problems caused by it.

4. Partitioning is not a table. The result is still one table, but the stored data file is divided into multiple small blocks. When the table data is very large, it can solve the problems of being unable to load into memory at once and maintaining large table data.

5. Vertical split splits the table into multiple tables by columns. It is common to separate the extended data of the main table and the text data to reduce the pressure on disk IO.

6. Horizontal splitting. The main purpose of horizontal splitting is to improve the concurrent reading and writing capabilities of a single table (the pressure is distributed to various sub-tables) and disk IO performance (a very large .MYD file is distributed to various small tables). .MYD file of the table). If there is no data of more than 10 million levels, why should it be dismantled? It is also possible to optimize only a single table; and if there is not too much concurrency, a partitioned table can generally meet the requirement. Therefore, under normal circumstances, horizontal splitting is the last choice, and you still need to go step by step during design.

The above is the detailed content of How mysql handles high concurrency. For more information, please follow other related articles on the PHP Chinese website!