Multi-dimensional Sharding for Massive Order Data

As data volumes grow, especially with operational data like orders, single-table databases quickly hit performance ceilings. Beyond tens of millions of rows, even optimized indexing fails to provide adequate performance for common operations like pagination, statistical reporting, and time-range queries. This limitation necessitates horizontal scaling strategies like sharding.

Composite Sharding Strategy: User ID Hashing + Time-Based Partitioning

The core of this solution is a multi-dimensional sharding approach. It leverages user ID hashing for database sharding (horizontal partitioning) and order creation date for table partitioning within each database (vertical partitioning). This composite strategy is designed to cater to two primary high-frequency query patterns:

• User-Dimension Queries: All orders for a single user reside in the same physical database shard, allowing queries to be routed directly and efficiently without cross-database joins.
• Time-Dimension Queries: Within each user's database shard, orders are further segmented by creation date into monthly tables, controlling table size and optimizing time-range queries.

Routing Rules and Data Management

• Database Sharding: `hash(user_id) % num_databases` ensures all user orders are co-located. This minimizes distributed transactions for user-specific operations.
• Table Partitioning: Monthly tables (e.g., `orders_202606`) are created within each database, keeping table sizes manageable (e.g., under one million records). Automated creation of these tables simplifies operations.
• Order ID Enhancement: Embedding a sharding identifier into the order ID allows direct routing to the correct database and table, bypassing user ID routing for specific order lookups.
• Hot/Cold Data Separation: Orders older than 12 months are migrated to an archive repository, offloading the primary online database and improving performance for hot data.

Handling Cross-Dimensional Queries

While single-user queries are efficient due to co-location, queries spanning multiple users or global time ranges require more complex handling:

• User Dimension Query: The middleware routes to the correct database, then queries all relevant monthly sub-tables for that user, merging and paginating results.
• Global Time Statistics: The middleware performs parallel queries across the corresponding monthly sub-tables in *all* databases. Results are then aggregated and summarized. Caching strategies are crucial to mitigate the overhead of these cross-database, cross-table operations.