Above the blue “programmer chases the wind”, select “Set as star”

Reply to “Materials” to get the interview materials sorted out

Original: juejin.cn/post/7128726681954549768


The first problem designed after the database is sub-table is how to select the routing key and how to route the key. The routing key should be present and unique in every table. The routing policy should try to ensure that the data is evenly distributed.

If it is a service that archives large data volumes, you can select the time as the routing key. For example, a table is created every month or quarter according to the time the data is created as the route key. The routing policy after the database is divided into tables by time can achieve data archiving, and the historical data access traffic is small, and the traffic will hit the latest database table.

It is also possible to design its business-related routing keys. This ensures that the resources of each database can bear the traffic well.

Scenarios are supported

The scenarios that need to be supported after the takeaway order platform is subdivided into tables and the user’s perspective need to view the status of the ordered takeaway orders in real time and track the order information. Merchants need to query the order information, analyze the quality of the dishes through the order, and make business decisions.

User Consumer = C-side Merchant Business = B-side

After the user places an order, the order may fall into different tables, and multiple tables may need to be queried when querying.

Routing policy

If you randomly insert an order into a table, or do not know which table to insert, you need to query all the tables when querying the order to ensure the accuracy of the query.

If there are certain rules when inserting orders, according to this rule inserted into the database, the corresponding rules are also executed into the corresponding table when querying. This reduces the complexity of data manipulation. This can be achieved by designing a routing policy that allows both users and merchants to follow the same routing policy when querying data.

User-side routing key

Based on the routing policy analysis in the previous section, you now need to select a route key. The user side allows the data of the same user id to be saved to a fixed table, so the user id can be selected as the most routed key.

In the case of a single library, the user places an order, generates an order, takes the user id as the route key, takes the hash value of the user_id and then modularizes the number of tables to obtain the corresponding table that needs to be routed, and then writes the data.

In the case of multiple libraries and multiple tables, you need to find the corresponding library first and then find the corresponding table. Multi-library multi-table routing policy: user release -> generate order – > routing policy: according to the hash value of the user id to the number of databases to find the corresponding database – > according to the hash value of the user id divided by the number of tables, and then the number of tables can be found by modularizing the number of tables.

The main point of routing policy design is to design according to the specific business scenario, and the hash value modulus as the routing key with a relatively large degree of correlation with user information

Merchant routing key

A set of tables is designed separately for the B-side of the merchant (the C-side and the B-side are independent).

The user’s perspective uses the user_id as the routing key, and the merchant’s perspective uses the merchant id as the routing key. How do merchants route data through the routing key? When you place an order, send the order number of your teammate to MQ, the merchant can go to the MQ, and then obtain the order information according to the order number, and then insert the order information into the merchant’s database table. The routing policy of the merchant is the same as the routing policy of the user.

Complete data flow chart for the user and merchant side:

 Are you watching