Summary of sub-database sub-table

Library single Table

Library single table is the most common database design, for example, there is a user table in database db , all users can be in the db library User found in the table.

Library Multi-table

As the number of users increases, the amount of data in the user table becomes larger, and when the amount of data reaches a certain level , the query to the user table slowly slows down, affecting the entire DB the performance. If you use MySQL, A more serious problem is that when you need to add a column,mysql locks the table, and all read and write operations can wait.

The user can be sliced horizontally in some way, resulting in a table of two tables with exactly the same user_0000,user_0001 ,user_ 0000 + user_0001 + ... Data is just a complete piece of data.

Multi-Library Multi-table

As the amount of data increases, a single db of storage space will be insufficient, with the increase in query volume of a single database server has no way to support. The database can be differentiated horizontally at this time.

Sub-database table rules

When designing a table, you need to determine what rules the table will use to divide the database into tables. For example, when a new user is available, the program has to decide which table to add this user information to, so when we log in we have to find the corresponding record in the database through the user's account, all of which need to follow a certain rule.

Routing

　Routing: The process of finding the corresponding tables and libraries through a library table rule.

such as the sub-database sub-table rules is user_id mod 4 Way, when the user newly registered an account, account ID of 123, we can through The ID mod 4 method determines that this account should be saved to the user_0003 table. When the user 123 Log in, we 123 mod 4 after determining the record in the user_0003 .

Problems arising from the sub-tables and the matters needing attention

1. the problem of the sub-database dimension

If the user buys a commodity , need to save the transaction record, if according to the latitude of the user table, each user's transaction is saved in the same table, so it is very convenient to find a user's purchase situation, but the purchase of a product is likely to be distributed in more than one table, It's a bit cumbersome to find. Conversely, according to the commodity dimension of the table, can be very convenient to find the purchase of this item, but to find out the buyer's transaction record is more troublesome.

So the common solution is as follows:

A. this approach is largely impossible and inefficient by way of a sweep of the table.

B. record two data, one according to the latitude of the user table, a copy according to the dimensions of the commodity table.

C. through search engine resolution, but if the real-time requirements are very high, but also related to real-time search.

2. problems with Federated queries

Federated queries are basically not possible because the associated tables may not be in the same database.

3. avoid cross-Library transactions

Avoid modifying the tables in the db0 while modifying the tables in a transaction, one of which is more complex to operate and will have a certain effect on the efficiency of the DB1.

4. Try to put the same set of data on the same DB Server

For example: Put seller a 's goods and trading information into the db0 , when db1 hung up, seller a the relevant things can be used normally. This means that the data in the database is not dependent on the data in another database.

A master multi-standby

In practical applications, the vast majority of cases are read far beyond writing.Mysqlprovides a mechanism for read and write separation, and all write operations must correspond to theMaster, the read operation can beMasterand theSlaveon the machine,Slavewith theMasterThe structure is exactly the same, aMastercan have multipleSlave,evenSlaveyou can hang it down .Slave,this way can effectively improveDBof the clusterQPS.

All write operations are done on master first and then synchronized to Slave , so sync from master to Slave The machine has a certain delay, when the system is very busy, the delay problem will be more serious,the increase in the number of Slave machines will also make this problem more serious.

In addition, it can be seen that master is the bottleneck of the cluster, when too many write operations can seriously affect the stability of the master , if the master hang up, the entire cluster will not work properly.

So,1. when the reading pressure is very high, you can consider adding Slave the fractional solution of the machine, but when Slave when the machine reaches a certain quantity, it has to consider the sub-Library. 2. When writing pressure is very high, it is necessary to carry out the library operation.

In addition, because of various reasons, the database hardware configuration in the cluster will be different, some high performance, some performance is low, this time can be programmed by the control of each machine read and write proportion, to achieve load balance.

Summary of sub-database sub-table