Designing Routing Keys for Sharding in an Order Platform

Overview: After sharding (分库分表), the first problem is selecting a routing key that exists uniquely in each table and ensures even data distribution.

If archiving large data, time (creation timestamp) can be used as the routing key, creating tables per month or quarter for archival and low traffic on historical data.

Alternatively, business‑related routing keys can be designed to balance load across databases.

Supported scenarios: In a food‑delivery order platform, users need real‑time order status, while merchants need to query orders for analysis and decision‑making.

Routing strategy: Random insertion requires querying all tables; a rule‑based insertion allows queries to target the appropriate table using the same routing rule.

User‑side routing key: Use user_id as the routing key; hash the user_id and mod by table count to determine the target table, both for single‑database and multi‑database sharding.

Merchant‑side routing key: Merchants have a separate set of tables; use merchant_id as the routing key, following the same hash‑mod strategy as the user side.

Data flow: When an order is placed, its order ID is sent to an MQ; merchants consume the MQ, retrieve order details, and insert them into the merchant’s database tables using the routing key.