What Are Database Sharding, Table Sharding, and Combined Sharding? – Xianyu Interview Guide

Core Concepts

Table sharding : split a large table into multiple smaller tables within a single DB instance; solves large table size (slow queries, DDL blockage).

Database sharding : split a logical database into multiple physical databases; solves connection count, QPS, disk I/O limits.

Combined sharding : both database and table are split; solves both massive data volume and high concurrency.

Evolution Path

Typical MySQL bottleneck progression (cited from Alibaba Java Development Manual): single table >5 million rows / 2 GB, >20 million rows, TPS/QPS spikes, very large data files. After single‑database tuning, read/write separation, and caching fail, sharding is applied as the last resort.

Table Sharding: Vertical vs Horizontal

Vertical table sharding splits columns (hot vs cold). Example: user table with 30 columns – id, name, avatar in main table; bio, address, extend_json in extension table. Reduces column width, improves buffer‑pool hit rate.

user (30 columns)
 ├─ id, name, avatar          → main table (hot columns)
 └─ bio, address, extend_json → extension table (cold columns)

Horizontal table sharding splits rows by a shard key (e.g., user_id) into multiple tables such as order_0 … order_15.

order_0, order_1, …, order_15 → 16 tables storing the same logical order data

Database Sharding: Vertical vs Horizontal

Vertical database sharding separates databases by business domain (user_db, order_db, product_db, payment_db). Used for micro‑service boundaries.

Horizontal database sharding distributes the same business across instances using a shard key, e.g., user_db_0, user_db_1, user_db_2 with routing rule user_id % 3.

Combined Sharding

Stacking horizontal database sharding with horizontal table sharding yields a matrix (e.g., 4 databases × 16 tables = 64 tables). This handles massive data and high concurrency but introduces cross‑database joins, distributed transactions, global unique IDs, and higher operational complexity. Recommendation: adopt only when unavoidable.

Practical Considerations

Sharding key selection : choose the field most used in high‑frequency queries (e.g., user_id for order systems). Avoid fields that cause data skew such as create_time or id.

Cross‑database joins : three approaches – multiple queries in business logic, "binding" or "broadcast" tables, or synchronizing data to Elasticsearch for wide‑table queries. ES + sharding is common in production.

Pagination : global pagination is difficult. Common solutions – forbid cross‑page jumps, use cursor pagination ( WHERE id > last_id), or secondary‑query merging.

Distributed ID generators : Snowflake, Leaf, TinyID, or database‑segment IDs. Snowflake is classic but requires handling clock‑rollback.

Java Ecosystem Tools

ShardingSphere‑JDBC (client side): lightweight, no middleware, good performance.

ShardingSphere‑Proxy (proxy side): transparent to applications, ops‑friendly.

MyCat (proxy side): legacy solution, community activity declining.

TiDB / OceanBase (distributed DB): business‑transparent but higher cost.

In most Java projects, adding the ShardingSphere‑JDBC Maven dependency and configuring sharding rules is far cheaper than migrating to a distributed database.

Key Takeaways

Sharding is not a first‑step solution; it follows single‑database optimization, read/write separation, and caching. Distinguish the three concepts: table sharding addresses data volume, database sharding addresses pressure, combined sharding addresses both. Discuss vertical vs horizontal approaches, shard‑key choice, cross‑shard join handling, and pagination challenges to demonstrate practical experience.