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

The editor will share with you how to divide the database and table in MYSQL. I hope you will get something after reading this article. Let's discuss it together.

Text:

-

In the use of MYSQL, people often ask how to divide the database and table, and they often hear two answers.

1 sub-database and sub-table, we use the way of business logic + business program, and expect to split some tables in the system to different MYSQL servers according to the actual environment, so as to solve the following two key problems.

1 is subject to the storage capacity of MYSQL's own single table, and this processing expands the storage capacity of MYSQL's single table.

(2) restricted by the data throughput ability of MYSQL stand-alone (including high availability of MHA based on MGR cluster), after breaking up the data, it improves the storage speed of the database and the speed of data extraction.

This is probably the most important requirement in the sub-database table.

2 sub-database sub-table, the use of middleware, sub-table operation, this is another way of sub-table, compared with the above way, this way is more general, through the middleware way to automatically split the data flow to different MYSQL database servers, in order to achieve the same effect as the above using specific logic, and meet the needs of data storage expansion and larger throughput.

The difference between the two sub-table schemes of 1 and 2, individuals understand that the first scheme is closer to the business, and if it is their own company to develop the program, then mistakes, troubleshooting, are more convenient. However, if there is a problem with the self-defined scheme, and it has been in production for a period of time, the cost of reneging on it will be higher.

Scheme 2 adopts the way of middleware, which is mostly, or needs to be put online quickly, the business direction is more flexible, and requires a little more sub-table tables, in the case of more tables.

From the cost point of view, program 1 will invest more manpower and material resources, while plan 2 will invest less manpower and material resources. Because the middleware is generated according to the needs of many businesses, it has strong versatility and high adaptability. Option 1 depends on each family's own development level and experience.

But why option 2 is useless by some companies in the case of low cost also summarizes the following points

(1) the use of middleware involves the overall key business (the table of sub-table is generally the main force in the business) the solution of developing sub-database and sub-table is more bottomed.

(2) some problems in the design of middleware, such as the execution of complex query statements, the return of data, the distribution of transactions, the processing of failures, and so on, all have to be complex and single-database system, so they are afraid of problems in the later stage, which can not be solved, so they are not used.

Here we look at the middleware from a logical point of view.

1 whether the logic of primary key generation in middleware can meet the logic envisaged in the enterprise business and meet the needs of the development for primary key design.

2 the standard of the syntax of MYSQL supported by middleware, and whether it can bear the query of some complex statements, so as to reduce the difficulty of developers when designing the system.

(3) the problem of the cost of data migration, maintenance and change of sub-database and sub-table when the middleware is not in use.

Based on the above considerations, standardization, stability, related support and so on should be considered on the basis of the use of middleware.

In terms of details, the use of middleware will be considered a little more.

For example, for the demand of learning cost, the addition of middleware should not increase the cost of using the database, whether in terms of personnel or technology, the closer to the original database, the better.

Support the native database protocol, which is also a standard to reduce the cost of learning and improve the sense of convenience.

Of course, there seems to be no characteristic in the requirements of middleware, but the difficult part is the rollback of transactions. it is normal to roll back transactions in an MYSQL database, but it is not a simple thing to send a transaction to multiple MYSQL and carry out transaction rollback.

1 how to deal with the failure of a node in multiple MYSQL nodes

2 how to break up the execution plan to multiple nodes and maintain the execution efficiency

3 how to roll back the whole transaction after the operation fails

4 split the complex SQL, split the complex query into the basic relational algebra query tree, send it to each node, and then return the data for merging and so on.

(5) the correctness of the data returned after the execution of the complex SQL.

Based on the complexity and requirements of middleware, in fact, most of the middleware we can use are using mycat. To understand and understand the definition of MYSQL middleware more clearly is the first step of how to do a good job of library and table. In fact, there are other middleware to choose from.

After reading this article, I believe you have a certain understanding of "how to divide the database and table in MYSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for 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.