ShardingSphere-Based Order Database Sharding at ZhaiZhai: Challenges, Implementation, and Results

In recent years, the rapid growth of ZhaiZhai's second‑hand business has exposed serious performance bottlenecks in the order system, including DB pressure during large promotions (QPS exceeding ten thousand), tables with billions of rows approaching server capacity, and lengthy backup/recovery times that increase data‑loss risk.

Initial mitigation measures such as optimizing large transactions, moving core order‑creation steps to the end of the workflow, adding caches with versioned keys, routing complex queries to Elasticsearch, and separating hot and cold data reduced pressure but still fell short under peak loads.

To fundamentally solve the problem, the team evaluated several data‑sharding solutions on efficiency, stability, learning curve, and implementation time, ultimately selecting ShardingSphere for its flexible sharding strategies, distributed transaction support, low intrusion, and rich documentation.

ShardingSphere offers three deployment modes—JDBC, Proxy, and Sidecar (planned). After comparing features, ShardingSphere‑JDBC was chosen because it acts as a lightweight Java framework that enhances the JDBC driver without requiring additional services, making it compatible with various ORM tools.

Version 5.x of ShardingSphere introduces new capabilities such as a distsql console, cross‑instance join queries, data‑gateway support for heterogeneous databases, online user‑permission management, and an automated probe module.

Key implementation details include the shard key design: order IDs are generated as timestamp + user identifier + machine code + incremental sequence , where the user identifier (extracted from the 9th‑16th digits of the buyer ID) provides good distribution. The sharding strategy uses 16 databases and 16 tables, with the user identifier modulo determining the database and its high four bits determining the table.

Data migration is performed online with a double‑write approach: write to both old and new shards, migrate historical data, verify consistency, and finally decommission the old database, ensuring zero downtime, data integrity, and instant rollback capability.

After migration, the single‑database capacity limit was eliminated, table sizes dropped from nearly a hundred million rows to the million‑row level, overall performance improved dramatically, and the risk of data loss due to oversized tables was greatly reduced. Comparative charts show a marked decrease in request latency and increased throughput during major promotional events.

The project demonstrates that large‑scale sharding initiatives test not only technical design but also team coordination, engineering rigor, and resilience, while ShardingSphere’s modular and extensible architecture simplifies development, allowing engineers to focus on business logic and achieve scalable data architecture.