Example analysis of routing strategy design after MySQL database and table partitioning

Overview

The first problem that is designed after sharding the database and sharding the tables is how to select the routing key and how to route the key. Routing keys should exist and be unique in each table. The routing strategy should try to ensure that data is evenly distributed.

If you are archiving a large amount of data, you can choose time as the routing key. For example, using the creation time of the data as the routing key, create a table every month or quarter. Using time as the routing strategy after sharding databases and tables can achieve data archiving. The historical data access traffic is small, and the traffic will be sent to the latest database table.

You can also design its business-related routing key. This ensures that the resources of each database can well bear the traffic.

Support Scenarios

From the user's perspective, after the takeout order platform is divided into databases and tables, it needs to support real-time viewing of the status of the takeout orders ordered and tracking of order information. Merchants need to query order information, analyze the quality of dishes through orders, 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 , you may need to query multiple tables when querying.

Routing strategy

If an order is randomly inserted into a table when creating an order, or you do not know which table it is inserted into, you need to query all orders when querying The table can ensure the accuracy of the query.

If there are certain rules when inserting an order, it will be inserted into the database according to this rule, and when querying, the corresponding rules will also be executed to query the corresponding table. This reduces the complexity of data operations. Both users and merchants can follow the same routing strategy when querying data, which can be achieved by designing a routing strategy.

Customer routing key

According to the routing strategy analysis in the previous section, you now need to select a routing key . The client allows the data of the same user id to be saved in a fixed table, so the user id can be selected as the routing key.

In the case of a single database, the user places an order, generates an order, uses the user id as the routing key, takes the hash value of the user_id and then takes the modulo of the number of tables to obtain the corresponding table that needs routing, and then data input.

#In the case of multiple databases and multiple tables, you need to find the corresponding database first and then find the corresponding table. Routing strategy for multiple databases and multiple tables: User places orders -> Generate orders - > Routing strategy: Modulo the number of databases based on the hash value of the user ID to find the corresponding database -> Divide the hash value of the user ID by the pair The number of tables, and then modulo the number of tables to find the corresponding table.

The key point of routing strategy design is to design according to the specific business scenario, and use the hash value modulo as the routing key that is more closely related to the user information

MERCHANT ROUTING KEY

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

The user's perspective uses user_id as the routing key, and the merchant's perspective uses the merchant's ID as the routing key. How do merchants route data through routing keys. When you place an order, Youhu sends the order number of your teammate to MQ. The merchant can consume this MQ, then obtain the order information based on the order number, and then insert the order information into the merchant's database table. The merchant's routing policy and the user's routing policy are the same.

Complete data flow diagram for client and merchant:

The above is the detailed content of Example analysis of routing strategy design after MySQL database and table partitioning. For more information, please follow other related articles on the PHP Chinese website!