Interview Question: What Problems Arise After Sharding? List the Key Issues

Sharding Core Pain Points

Cross‑Database Join – Joining tables that reside on different shards causes severe performance degradation.

Pagination & Sorting – Global pagination requires merging data from many shards, inflating memory and I/O.

Distributed Transaction – Maintaining consistency across shards is difficult.

Global Unique ID – Auto‑increment primary keys conflict across shards.

Data Migration & Scaling – Adding or shrinking shards forces re‑sharding of existing data.

Cross‑Database Aggregation – COUNT / SUM / GROUP BY must be executed on every shard and aggregated, leading to N‑fold cost.

Deep Analysis

1. Cross‑Database Join Issue

In a single database a statement like SELECT ... JOIN works. After sharding, the order table may reside in database A while the order_item table resides in database B, making the join impossible.

Typical solutions (choose according to data characteristics):

Binding Table : Use the same sharding key for parent and child tables (e.g., t_order and t_order_item both sharded by order_id) so the data lands in the same shard and a local join works. ShardingSphere supports binding table configuration.

Broadcast Table : Small, rarely changed reference tables (dictionary, config) are copied to every shard, enabling local joins.

Federation Engine : ShardingSphere 5.x provides a federation engine for cross‑shard joins without binding tables, but performance is lower than local joins.

Business‑Layer Assembly : Query the master table first, fetch the foreign keys, then batch‑query the child table and join in memory. Simple but you must handle consistency yourself.

2. Pagination & Sorting Issue

Single‑database pagination such as LIMIT 10000, 10 becomes painful after sharding. If there are four shards and you need the global 10,000‑th page, each shard must return the first 10,010 rows, then the application merges, sorts, and extracts rows 10,000‑10,010. This inflates data volume N‑fold and stresses memory and DB.

Production solutions:

Prohibit Deep Pagination : Limit the UI to the first 100 pages (search engines adopt a similar rule).

Cursor Pagination (Scroll Search) : Remember the last record's ID or timestamp and query the next page with WHERE id > last_id LIMIT 10, avoiding large offsets.

Elasticsearch Assistance : Sync the heavy‑query data to ES and use its inverted index for deep pagination.

3. Distributed Transaction Issue

Cross‑shard writes cannot be handled by a single local transaction. For example, an order creation must deduct inventory in the order DB and deduct money in the account DB atomically.

Seata is the most widely used framework in China. It offers four modes:

XA – Based on the database XA protocol (2PC). Strong consistency, no intrusion, low performance. Typical scenario: banking, core finance.

AT – One‑phase commit + undo‑log rollback. Eventual consistency, no intrusion, high performance. Typical scenario: internet CRUD services.

TCC – Try‑Confirm‑Cancel three‑phase. Strong consistency, high intrusion (three interfaces), medium performance. Typical scenario: financial transactions requiring strong isolation.

Saga – Long transaction split + compensation. Eventual consistency, medium intrusion, high performance. Typical scenario: long‑process business such as travel booking.

In practice, about 80 % of internet services choose the AT mode because it offers good performance with low intrusion. TCC is reserved for strict isolation scenarios, while Saga fits long‑running workflows.

@GlobalTransactional
public void createOrder(OrderDTO orderDTO) {
    // write order DB
    orderMapper.insert(convert(orderDTO));
    // remote call to account service
    accountFeignService.decrease(orderDTO.getUserId(), orderDTO.getMoney());
    // remote call to inventory service
    storageFeignService.decrease(orderDTO.getProductId(), orderDTO.getCount());
}

The @GlobalTransactional annotation lets Seata automatically coordinate the three databases, making the business code almost unaware of the transaction.

4. Global Unique ID Issue

In a single DB AUTO_INCREMENT works, but after sharding each shard generates its own IDs, causing collisions. A distributed ID scheme is required.

Most popular solution: Snowflake (Twitter open‑source) generates a 64‑bit Long ID.

Snowflake layout (64 bits):

Sign Bit (1) : always 0 to keep the ID positive.

Timestamp (41) : millisecond precision, supports ~69 years. Placed in the high bits to keep IDs roughly increasing, which is friendly to B‑tree indexes.

Machine ID (10) : often split into 5‑bit data‑center and 5‑bit machine, supporting up to 1024 nodes. Usually managed by ZooKeeper or a config center.

Sequence (12) : per‑millisecond counter, up to 4096 IDs per node per ms (≈4.09 million IDs/s). Sufficient for most workloads.

Major pitfall: clock rollback. If the machine clock moves backwards, IDs may duplicate. ShardingSphere’s implementation records the last timestamp and throws an exception or waits when rollback exceeds a threshold.

Other options:

UUID : simple but 36‑character string, consumes space, unordered, poor B‑tree performance.

Segment Mode (Leaf‑Segment) : Meituan’s open‑source Leaf, batches IDs from the DB, high performance with traceability.

Redis INCR : fast but introduces a single point of failure.

5. Data Migration & Scaling Issue

When traffic grows, the number of shards must increase. If the original rule is hash(key) % 3 and you expand to six shards, the modulus changes to % 6, moving most data to new locations, thus requiring migration.

Typical smooth‑scaling approaches:

Double Expansion : Expand from N to 2N shards; each old shard keeps half of its data and writes the other half to the new shard, minimizing migration volume.

Consistent Hash : Replace modulus with consistent hash; only neighboring ranges change on expansion, greatly reducing data movement.

Dual‑Write Migration : New and old shards write simultaneously; a binlog listener (e.g., Canal) performs incremental sync. After full data copy, traffic is switched gradually.

Gray‑Scale Switch : Initially route 1 % of read traffic to the new shard for verification, then increase gradually; rollback is possible at any stage.

6. Cross‑Database Aggregation Issue

Simple count query SELECT COUNT(*) FROM orders must be executed on every shard and summed, resulting in N‑fold cost. Group‑by, sum, avg become even more painful.

Solutions:

Summary Table : Periodic batch job computes full statistics and writes them to a dedicated summary table; queries read the pre‑aggregated table.

Elasticsearch Async Sync : Sync binlog to ES and leverage its aggregation capabilities.

Offline Computation : Use OLAP systems (Hive, ClickHouse) for T+1 batch processing.

Typical Interview Q&A

Can AUTO_INCREMENT still be used after sharding? No – each shard would generate conflicting IDs; a distributed ID scheme is required.

How to choose a sharding key? Prefer the most frequently queried field (e.g., user_id , order_id ) so most queries can be routed to a single shard; avoid range‑query‑prone fields.

If a cross‑shard transaction fails, what to do? Choose the consistency level: strong consistency → Seata AT/TCC; eventual consistency → message queue + local message table with reconciliation.

How many shards should be created? Estimate 3‑5 years of data growth, calculate peak volume, then decide shard count (commonly #databases = #tables, e.g., 8 DB × 8 tables, 16 DB × 64 tables). Too few leads to painful migrations; too many adds operational complexity.

ShardingSphere vs MyCat? ShardingSphere is a lightweight JDBC‑layer solution (also offers a proxy) with low intrusion and high activity; MyCat is a proxy‑only solution suitable for heterogeneous languages. New projects usually pick ShardingSphere.

Memory Mnemonic

Six Sharding Pains : Join difficulty, pagination difficulty, transaction difficulty, ID difficulty, scaling difficulty, aggregation difficulty.

Coping Ideas : Binding/Broadcast for Join, cursor/ES for pagination, Seata for transactions, Snowflake for IDs, dual‑write smooth scaling, summary table for aggregation.