How to solve the high concurrency problem in MySQL database

Preface

We all know that startups initially use monolithic applications as their primary architecture, usually in the form of a single monolithic database. However, with the iteration of versions, the database needs to withstand more high concurrency, which has become a point that needs to be considered in architectural design.

So to solve the problem, we have to talk about solutions. But there are many options, how should we choose?

Optimization and solutions

Basically, our optimization should start with several keywords: short distance, less data, and dispersed pressure.

Short distance

The so-called short distance means that the path from the front end to the database should be short.

1. Page static. The data of some pages remains unchanged during certain periods of time, so this page can be made static, which can increase the access speed.
2. Use cache. Everyone knows about cache, and the reason it is fast is that it is based on memory. Therefore, using memory-based cache can reduce access to the database and speed up access.
3. Batch read. In high concurrency situations, multiple queries can be combined and run at once to reduce the speed of accessing the database.
4. Delayed modification. Delayed modification means that in high concurrency situations, the data that has been modified multiple times may be placed in the cache, and then the data in the cache may be updated to the database periodically; or it may be asynchronously synchronized to the database through parsing through the cache synchronization strategy.
5. Use an index. Needless to say, there are many types of indexes, such as normal index/primary key index/combined index/full-text index, etc.
   

Less data

The so-called less data actually means less data to be queried.

1. Sub-table. The so-called table segmentation actually includes horizontal segmentation and vertical splitting. Friends who have played stand-alone games know that some historical forms often contain hundreds of millions of data. In this case, for MySQL, even if indexes are added and SQL is further optimized, it is difficult to achieve faster query speeds. Then we can achieve this through the operation of sub-table. For example, the most common method is to split the table horizontally based on the time dimension, keeping this year's data and storing last year's data in another table.
2. Separate active data. In fact, this is a bit like caching, but the difference is that the data is still on MySQL. For example, in a product query business, there can be an active table for some popular/frequently searched products. When querying, query the active table first, and if not found, query the total product table.
3. Chunking. This block division is somewhat similar to the "index sequential search" in the algorithm. Through data-level optimization, the data is placed in different blocks, and we only need to calculate and find the corresponding blocks.
   

Distribute pressure

The so-called pressure distribution actually means distributing the pressure of different database servers.

1. Cluster. I believe everyone is clear about the concept of cluster. For business servers, it actually means deploying multiple servers with the same business process, and distributing requests to different servers through load balancing or other methods. The same goes for databases, which direct data to specific database servers through specific rule strategies.
2. distributed. The so-called distribution actually means allocating the business logic originally in the same process to different servers for execution, achieving the effect of "concurrent" execution and speeding up the execution.
3. Divide the database and tables. The main methods of database and table sharding are horizontal and vertical sharding. For a single table with high access frequency and huge data volume, you can reduce the data in the single table and split it horizontally according to specific dimensions to increase the database throughput. This is horizontal splitting of the table. For multiple tables with low business coupling, different tables can be stored in different databases, and the database can be split vertically to improve the database write capability. This is vertical splitting of the database.
4. Establish master-slave relationship. The purpose of establishing a master-slave relationship is actually to separate reading and writing. We all know that as long as the transaction level of the database is high enough, concurrent reading will not cause data confusion, but concurrent writing will. So when setting up a master-slave server, generally speaking, writes will be written on the master server, and reads will be made on the slave server. So basically let the master server do the transactional operations and the slave server do the select queries. In this way, changes caused by transactional operations (add/delete/modify) are synchronized to the slave databases in the cluster.
   

Conclusion

The above is the details of how MySQL handles high concurrency. For more information about MySQL high concurrency, please pay attention to other related articles on 123WORDPRESS.COM!