Database/table sharding

Single Database, single table

Single Database, single table is the most common database design. For example, if a user table is stored in the database, all users can find it in the User table in the database.

 

Single Database, multiple tables

As the number of users increases, the data volume of the User table will increase. When the data volume reaches a certain level, the query of the User table will gradually slow down, thus affecting the performance of the entire dB. If MySQL is used, another more serious problem is that when a column needs to be added, MySQL locks the table, during which all the read/write operations can only wait.

Users can be horizontally split in some way to generate two tables with identical structures, such as user_0000 and user_0001. user_0000 + user_0001 +... The data is just a complete data.

 

Multi-database, multi-table

As the data volume increases, the storage space of a single database may be insufficient. As the query volume increases, a single database server cannot support it. In this case, you can further differentiate the database horizontally.

 

Database/table sharding rules

When designing a table, you must determine the database/table sharding rules for the table. For example, when a new user exists, the program must determine the table to which the user information is added. Similarly, when logging on, we must find the corresponding records in the database through the user account, all of these operations must follow a certain rule.

Routing

Find the corresponding table and database through the database/table sharding rule. For example, the database/table sharding rule is user_id mod 4. When a user registers a new account, the account ID is 123, we can use ID mod 4 to confirm that this account should be saved to the user_0003 table. When logging on to user 123, we use 123 mod 4 and confirm the record in user_0003.

Issues arising from database/table sharding and precautions

1. database/table sharding

If you have purchased a product, you need to save the transaction records. If you want to store the transaction records in the same table according to the user's latitude, therefore, it is very convenient to find the purchase status of a user, but the purchase status of a product is likely to be distributed in multiple tables, which is troublesome to find. On the contrary, you can easily find the purchase status of the product by table sharding by item dimension, but it is troublesome to find the transaction records of the buyer.

 

Therefore, common solutions include:

A. This method is basically impossible to solve through table scanning, and the efficiency is too low.

B. Record two data copies, one table sharding by user latitude and one table sharding by item dimension.

C. It can be solved through search engines. However, if real-time requirements are high, real-time search is required.

 

2. Joint query Problems

Joint query is basically impossible, because the associated tables may not be in the same database.

 

3. Avoid cross-database transactions

Avoid modifying the table in db1 when modifying the table in db0 in a transaction. One is that the operation is more complicated and the efficiency will also be affected.

 

4. Try to put the same group of data on the same DB server.

For example, if both the product and transaction information of seller a are stored in db0, when db1 fails, the items related to seller A can be used normally. That is to say, to prevent the data in the database from being dependent on the data in another database.

 

 

One master, multiple slave

In practical applications, the majority of cases are reading much larger than writing. MySQL provides a read/write splitting mechanism. All write operations must correspond to the Master. Read operations can be performed on the master and slave machines. The slave and master structures are identical, A master can have multiple slave instances or even slave instances. This method can effectively improve the QPS of the DB cluster.

All write operations are performed on the master and then synchronously updated to the slave. Therefore, synchronization from the master to the slave has a certain delay. When the system is busy, the latency problem is more serious, and the increase in the number of slave machines will also make the problem more serious.

In addition, it can be seen that the master node is the bottleneck of the Cluster. When there are too many write operations, it will seriously affect the stability of the master node. If the master node fails, the entire cluster will not work normally.

Therefore, 1. When the read pressure is very high, you can consider adding slave machine fraction to solve the problem, but when the slave machine reaches a certain number, you must consider database sharding. 2. When writing pressure is high, database sharding must be performed.

 

In addition, for various reasons, the database hardware configuration in the cluster may be different. In some cases, the performance is high and some performance is low. In this case, the program can be used to control the read/write ratio of each machine, achieve load balancing.

 

Database/table sharding