Does mysql support distribution?

The distributed solutions in MySQL are actually quite rich. Today, let’s briefly talk about the understanding of distributed solutions.

Recommended courses: MySQL tutorial

First of all, a database is a software, and its most basic functions are data storage and data query. Generally speaking, data processing methods are divided into reading and writing, so many scenarios of distributed solutions are actually based on these two dimensions.

Before starting the distributed solution, let’s talk about why there is a distributed solution. If a single machine can solve something, there is actually no need to consider distributed processing at all. If they are divided, they cannot be combined naturally. This is also a balance that needs to be mastered in distributed solutions. The HTAP solution mentioned in the industry now is actually a scenario that integrates OLTP and OLAP. From a stand-alone perspective, Oracle is definitely the best HTAP solution. But in addition to the price issue in Oracle, there is another issue, that is, scalability. Let’s not talk about the details of sharding for now. The design idea in Oracle is to share everything, so the partition table solution is still more appropriate.

But MySQL is obviously not good, because you almost never hear of partition table solutions being used in the Internet industry, because no matter how divided or expanded, the data is all on a single machine, and the performance of the single machine is not satisfactory. Therefore, single machine capacity and single machine performance are both bottlenecks, so there can be two or more instances to share the pressure.

Let me give you a simple example. From the perspective of data processing, data has reading and writing requirements, so our needs can be extended to read requirements and write requirements respectively.

The expansion of reading requirements is relatively simple, which is often called the separation of reading and writing. This kind of general middleware can support it.

As shown in the lower left corner of the scheme below, the demand for reading can be easily expanded. The read expansion here is linear, not exponential, and is transparent to the business.

The difficulty lies in writing extensions. The core of writing extensions is the part involving distributed transactions. If you can’t split it, don’t split it. If you really want to split it, then we can divide it into different dimensions, such as pipeline type Data, this type of data has very low dependency, so the writing requirement is insert, and the writing requirement is relatively simple. This method can be assisted by middleware solutions to achieve sharding sharding solutions. Many of the distributed solutions we usually understand are actually talking about this. The expansion of this solution is exponential, for example, 2 nodes become 4, 4 becomes 8, etc., which is transparent to the business.

But there is a more complex type, which is state data. We cannot split it directly, or directly shard it. We can split it according to the dimensions of the business. , this kind of split is not recommended to use middleware directly. For example, if a business is split, it can be split into business 1, business 2, and business 3. . . Business 8, then the split logic of these 8 businesses is not recommended to be made into a smooth hash method, but to be combined according to the priority of the business logic and other dimensions. For example, if business 1 has a high priority, then it can be an independent If the data volume and priority of Business 3-Business 6 are different, they can be one node. It is recommended that data writing routing rules be processed through the application layer. This is a more controllable solution. This extension solution is not transparent to the application and requires the cooperation and processing of the application. But the income is obviously the best balance state. For example, the concept of game servers, which is very common in the game industry, is divided into this way, so the expansion can be linear.

If we want to talk about a distributed solution based on this, it actually treats a cluster or business as a transparent node and uses other auxiliary solutions to meet expansion needs. It is a relational distributed solution. It is mostly handled based on static routing, which still requires a lot of extra work for capacity expansion and cannot achieve smooth elasticity. This is where NoSQL and NewSQL naturally come into play.

So when choosing a solution, you must have an overall view and a higher vision. It may not necessarily be MySQL or Oracle. It is naturally good to delve deeper into it. You can also consider other better solutions.

The above is the detailed content of Does mysql support distribution?. For more information, please follow other related articles on the PHP Chinese website!