First, the problems before the database and table

  • The user requests are too large. Procedure

Because single-server TPS, memory, IO are finite. Solution: Spread requests across multiple servers; In fact, the user request and the execution of an SQL query is essentially the same, is to request a resource, but the user request will pass through the gateway, routing, HTTP server and so on.

  • The reservoir is too big

Limited processing capacity of a single database; The disk space on the server where the single library resides is insufficient. IO bottleneck solution for operations on a single library: Split into more and smaller libraries

  • Single table is too big

CRUD is a problem; Index bloat and query timeout solution: split into multiple tables with smaller data sets.

Two, the method of database and table

1, vertical split

The vertical table

That is, “big table split small table”, based on the column field. Generally, there are many fields in the table, and the uncommonly used, large data and long length (such as text type fields) are split into “extended table”. Generally for the kind of hundreds of columns of the large table, but also to avoid the query, the amount of data caused by the “cross-page” problem.

Vertical depots

Vertical branch library is aimed at the separation of different businesses in a system, such as User a library, commodity Producet a library, Order Order a library. Once shard, put it on multiple servers, not one. Why is that? Let’s imagine a shopping website offering services externally, there will be CRUD of users, goods, orders, etc. Before the split, everything falls into a single library, which makes the database’s single-library processing capacity a bottleneck. After dividing the database vertically, if it is still placed on a database server, as the number of users increases, it will make the processing capacity of a single database become a bottleneck, and the disk space, memory and TPS of a single server are very tight. So we split it into multiple servers, so that all the above problems are solved, and we don’t have to face stand-alone resource problems in the future. Similar to the “governance” and “downgrade” mechanism of services, the separation of database services can also manage, maintain, monitor and expand the data of different services. Database is often the most easy to become the bottleneck of the application system, and the database itself belongs to the “stateful”, compared with the Web and application server, is more difficult to achieve “horizontal expansion”. The connection resources of database are precious and the processing capacity of single machine is limited. In high concurrency scenario, the vertical branch database can break the bottleneck of IO, connection number and hardware resources of single machine to some extent.

2, horizontal split

The level of table

For a single table with a large amount of data (such as order table), according to certain rules (RANGE,HASH modulus, etc.), cut into multiple tables. But these tables are still in the same library, so library-level database operations still have IO bottlenecks. Not recommended.

Horizontal sub-database sub-table

The data of a single table is shard to multiple servers, each server has the corresponding library and table, but the data set in the table is different. Horizontal library table can effectively relieve the performance bottleneck and pressure of single and single library, and break through the bottleneck of IO, connection number, hardware resources, etc. RANGE, HASH Modulus

The level of depots

High availability, master/slave synchronization

Three, the problems faced after the database and table

Transaction support, distributed transactions

Multi-library result set union

Cross-database joins

Iv. Products of separate database and table scheme

5. Mycat high availability architecture scheme

Reading and writing separation

Depots table

reference

Juejin. Cn/post / 684490…