Sharding a 100-Million-Row Order Table Without Breaking Merchant Queries
Most sharding tutorials stop at splitting by user ID, but a production order system breaks the moment a seller, CSR, or internal tool queries without that key. This design shows the full dual-path architecture—MySQL for buyers, Elasticsearch for merchants—that keeps both sides fast without scanning every shard.
When an e-commerce order table hits hundreds of millions of rows, a simple user query can take 12 seconds. Sharding by `user_id` with a hash-modulo strategy solves the buyer side, but the real trouble starts when a merchant needs to list their store's orders without a user ID to route by. The answer is physical isolation: keep the primary MySQL cluster for buyer lookups and build a separate query path for merchants.
The merchant path uses Canal to stream binlog changes into an Elasticsearch index keyed by `seller_id`, turning multi-second cross-shard scans into millisecond searches. For teams that cannot add ES, a lightweight heterogeneous index table sharded by `seller_id` works, backed by RocketMQ transactional messages for consistency. A modified Snowflake algorithm embeds 12 bits of the user ID into every order number, so a lookup by order ID can reverse-engineer the exact shard without broadcasting to every database.
Migration from a single database uses a dual-write, gradual-cutover pattern with DataX for historical backfill. The design caps shard counts at powers of two to make future doubling expansions cheap, and it bans deep pagination and cross-database JOINs outright to keep latency predictable.
Sharding is not a storage problem—it is a connection-count and DDL-pain problem. Modern SSDs let a single table run fine past 10 million rows, but an `ALTER TABLE` on a 50-million-row table locks out writes for an unacceptable window.
The merchant-query blind spot is the real interview filter. Anyone can recite modulo sharding; recognizing that a seller has no `user_id` and therefore needs a physically separate query path separates a book answer from production experience.
Embedding routing information into the ID itself is a quiet but powerful pattern. It turns a distributed-lookup problem into a local computation, eliminating the need for a separate mapping service or global scan.
Deep pagination is a universal trap across MySQL and Elasticsearch. The fix is not technical but product-level: cap the page depth, switch to cursors, and scope queries to recent data by default.