Database Sharding and Partitioning Strategy for High‑Volume Order Systems

As order volume grows to billions of records, a single‑database, single‑table design becomes insufficient, prompting a redesign of the data storage architecture.

Data classification : Order data is divided into three categories—hot data (last 3 months, high query frequency), cold data A (3‑12 months, lower query frequency), and cold data B (older than 1 year, rarely accessed). Hot data stays in MySQL with sharding, cold data A is moved to Elasticsearch for fast search, and cold data B is archived in Hive.

MySQL sharding and partitioning :

Business‑level sharding: Separate different business modules (users, products, orders) into distinct databases to reduce load on a single instance.

Table sharding: Use a shard key (e.g., order_id) and modulo operation to distribute rows across multiple tables, e.g., order_id % 100 for 100 tables.

Database sharding: Route data to different databases using a similar modulo strategy, e.g., order_id % number_of_databases. If the key is not numeric, apply a hash function first.

Combined sharding strategy : When both database and table sharding are needed, compute an intermediate variable: shard_key % (db_count * tables_per_db), then derive the target database and table indices from this value.

Overall architecture : Write requests follow the sharding rules to insert data into the appropriate MySQL instance/table. Read requests first determine whether the query targets hot or cold data based on the order ID timestamp; hot data is fetched from MySQL, cold data A from Elasticsearch, and cold data B from Hive. A scheduled job periodically migrates aging data from MySQL to Elasticsearch or Hive.

The article also includes diagrams illustrating the separation of business modules into multiple databases, the sharding formulas, and the read/write flow of the system.