How to Scale an Order System: Sharding, Unique ID Strategies, and Migration Best Practices

Demand Background

The order system faces massive data volume, high concurrency, and poor scalability due to a single‑database, single‑table design. Problems include:

Large data volume : All orders stored in one database cause slow queries.

High concurrency : Many simultaneous requests overload the DB.

Poor extensibility : Schema changes become time‑consuming as data grows.

Current Business Issues

Primary key design : Both orderId (auto‑increment) and orderNo (custom) exist. Solution: merge them and use a custom orderNo as the sole primary key.

Excessive indexes : >10 indexes for C‑side and B‑side queries. Solution: sync OLTP order data to an OLAP store, keep C‑side queries on the sharding key, and move B‑side analytical queries to OLAP.

Historical data inconsistency : Old orderNo values do not follow the current rule and may contain letters. Solution: clean old order numbers with Flink to conform to the new rule.

Inconsistent foreign‑key usage : Related tables reference either orderId or orderNo. Solution: standardize on orderNo as the foreign key and sharding key; split related tables if needed.

Table redundancy : Non‑core fields (billing, invoicing, refund) are mixed into the order table. Solution: vertically split tables based on core relevance, update frequency, and field size.

Technical Selection

Database Types

Traditional DB : Single‑server, vertical scaling.

Distributed DB : Horizontal sharding, high availability.

Cloud‑Native DB : Containerized, micro‑service friendly.

Cloud‑Native Distributed DB : Combines distributed and cloud‑native features.

Product Comparison (Key Points)

PolarDB‑Partitioned Table (Alibaba Cloud) : Write‑once‑many‑read, shared storage, compute‑storage separation, auto read/write split, high reliability. Limitation: single‑key partitioning, no spatial types.

OceanBase (Ant Group) : Financial‑grade disaster recovery, horizontal scaling, multi‑tenant isolation. Limitation: higher cost, migration effort.

PolarDB‑X (Alibaba Cloud) : Cloud‑native distributed, real‑time HTAP, row‑column hybrid. Limitation: higher cost, no MySQL 8.0 support.

TiDB (PingCAP) : Open source, strong consistency, column & space indexes. Limitation: requires extra middleware for distributed transactions.

ShardingSphere (Traditional) : Lightweight client‑side sharding, supports multiple data sources, provides distributed transaction and read/write split. Limitation: configuration complexity.

Middleware Comparison

ShardingSphere‑JDBC (client‑side): Lightweight, easy integration, supports distributed transactions and read/write split. Requires code changes.

ShardingSphere‑Proxy (database proxy): Rich features, supports distributed primary key generation, but adds performance overhead and needs separate deployment.

Mycat : Open‑source proxy with similar features; community support is limited.

Unique ID Generation Options

Auto‑Increment (DB/Redis)

Pros: Simple.

Cons: Single point of failure, performance bottleneck, exposes business volume.

Use case: Small systems with low growth.

Snowflake Algorithm

Structure: 1 sign bit, 41‑bit timestamp (ms), 10‑bit machine ID (1024 nodes), 12‑bit sequence (4096 IDs per ms).

Pros: High performance, high availability, ordered IDs.

Cons: Requires a central node, clock rollback can cause duplicates, no business semantics.

Use case: High‑concurrency distributed systems generating tens of thousands of IDs per second.

UUID / Random

Pros: Simple, no central coordination.

Cons: Long strings, tiny collision risk, poor index performance.

Use case: Systems where ID length is not critical and performance requirements are modest.

Meituan Leaf (Distributed ID Service)

Leaf‑snowflake : Cluster deployment, automatically excludes nodes with clock rollback.

Leaf‑segment : Improves DB auto‑increment with batch generation and local cache.

Pros: High availability, mitigates drawbacks of auto‑increment and Snowflake.

Cons: Depends on ZooKeeper, requires separate deployment.

Custom Business ID

Example format:

orderType(1) + bizType(1) + yyMMddHHmmss(12) + random(4) + userIdSuffix(4)

Pros: Generated locally, contains business attributes, readable, ordered.

Cons: May conflict when QPS > 10k.

Use case: Small‑to‑medium systems needing readable IDs.

Sharding Key & Strategy Selection

Range Sharding (Time‑based)

Applicable to logs, records, statistics with clear time fields.

Pros: Natural partitioning, easy range queries, convenient archiving.

Cons: Potential data skew leading to hot nodes.

List Sharding (Tenant ID)

Applicable to SaaS tables partitioned by tenant ID or order type.

Pros: Efficient queries/filtering by attribute value.

Cons: Maintenance of sharding rules, possible data skew, harder to scale.

Hash Sharding (Custom Business ID)

Applicable to C‑side scenarios where user ID is used for sharding.

Pros: Even data distribution, load balancing.

Cons: Difficult to expand, range queries less efficient.

Implementation Steps

New database and tables : Create a main order table and partitioned tables with fields such as order number, user ID, status, amount.

Generate new order numbers : Offline generation and store mapping between old and new IDs.

Flink data sync : Join old data with the mapping table, replace old IDs, route to appropriate shards, and sync extended tables.

Distributed Transaction Integration (Seata)

Reference Spring Cloud multi‑datasource integration with Seata and ShardingSphere‑JDBC best practices to ensure ACID consistency across sharded databases.

Risk Assessment & Mitigation

Improper sharding key selection : May cause slow queries and uneven data distribution. Mitigation: analyze query patterns, choose high‑frequency fields as sharding key, use auxiliary indexes if needed.

Data migration risk : Potential data loss or service interruption. Mitigation: detailed migration plan with rehearsals, incremental sync + full verification, and rollback procedures.

Distributed transaction consistency : Risk of data inconsistency. Mitigation: use mature transaction framework (Seata), reduce transaction scope, implement compensation and monitoring.

Query performance degradation : Poor user experience. Mitigation: SQL optimization, avoid cross‑shard queries, use caching, enable read/write splitting and proper indexes.

Large code refactor effort : Extended development cycle and bugs. Mitigation: phased implementation, comprehensive test cases, gray‑release strategy.

Summary

The solution balances future business growth, operational costs, and server resources by adopting a ShardingSphere‑JDBC based sharding strategy with Seata for distributed transactions. It defines a unified primary key ( orderNo), selects appropriate sharding keys (time‑based range for logs, tenant ID list for SaaS, hash for user‑centric data), and outlines a zero‑downtime migration path using offline ID generation, Flink‑driven data cleaning, and incremental sync.