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

Editor to share with you mysql methods to deal with high concurrency, I hope you will learn a lot after reading this article, let's discuss it together!

The solutions to mysql high concurrency are: optimizing SQL statements, optimizing database fields, adding caching, partitioning tables, reading and writing separation and vertical splitting, decoupling module, horizontal segmentation and so on.

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

(1) Optimization of sql statements in code

(2) Database field optimization and index optimization.

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

(4) Master-slave, separation of reading and writing

(5) Partition table

(6) Vertical split and decoupling module

(7) horizontal segmentation

Scenario analysis:

1. Method 1 method 2 is the simplest and fastest way to improve efficiency. Because every statement hits the index, it is the most efficient. However, if the index is built in order to optimize the sql, then the index will be flooded. For tables above tens of millions of levels, the cost of maintaining the index will greatly increase, but it will increase the memory overhead of the database.

2. Optimization of database fields. Once found that a senior programmer in the table field design, a date type, is designed as the varchar type, non-standard at the same time, can not check the written data, the efficiency of indexing is also different

3. Cache is suitable for business scenarios with more reads and writes and less updates, otherwise there is little objection to cache and low hit rate. Generally speaking, the main purpose of caching is to improve the interface processing speed and reduce the db pressure and other problems caused by concurrency.

4, the partition is not a sub-table, the result is still a table, but the stored data file is divided into several small blocks. In the case of very large table data, it can solve the problems that can not be loaded into memory at once, as well as the maintenance of large table data.

5. Vertical split divides the table into multiple tables according to columns. It is common to separate the extended data of the main table and the text data independently, thus reducing the pressure on disk io.

6. Horizontal split, the main purpose of horizontal split is to improve the concurrent read and write ability of a single table (the pressure is dispersed in each sub-table) and disk IO performance (a very large .MYD file is shared among the .MYD files of each small table). If there is no data above tens of millions of levels, why should we split it? it is possible to optimize only a single table; if there is not too much concurrency, the partitioned table can generally be satisfied. Therefore, in general, horizontal splitting is the last choice, and it still needs to be done step by step at the time of design.

After reading this article, I believe you have a certain understanding of mysql's method of dealing with high concurrency. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.