Master MySQL Sharding with Sharding-JDBC: Sorting, Distributed IDs, and Scaling

What – Definition of Sharding

Sharding splits a massive table into many identical smaller tables to spread read/write load, and splits a large database into multiple independent instances to distribute CPU, disk, and I/O pressure. The core idea is horizontal scaling to break the physical limits of a single monolithic database.

Why – When to Apply Sharding

Sharding should be introduced only after one of the following thresholds is reached:

Single‑table row count exceeds 50 million and indexing or archiving no longer yields performance gains.

Database QPS is saturated and CPU or disk I/O stays above 80 % for an extended period.

Daily data growth quickly approaches or exceeds 100 million rows.

Frequent large‑table DDL, lock contention, or observable business instability.

Recommended progression: index optimization → read/write splitting → data archiving → sharding as the final fallback.

Where – Four Splitting Patterns

1. Vertical Table Splitting (column split)

Applicable: wide tables, large BLOB fields, mixed hot/cold columns.

Logic: keep frequently accessed short columns in the main table; move long text, logs, or attachments to an extension table.

Benefit: reduces row size, improves cache hit rate, lowers disk I/O.

2. Vertical Database Splitting (business split)

Applicable: monolith‑to‑microservice migration, resource contention among business modules.

Logic: separate databases by business domain such as user, order, product, payment.

Benefit: business decoupling, resource isolation, independent failure domains.

3. Horizontal Table Splitting (row split, most common)

Applicable: massive single‑table data, heavy read/write pressure, columns cannot be trimmed.

Logic: keep the schema unchanged and distribute rows evenly across many child tables.

Benefit: each table remains lightweight; query, index, and DDL performance stay stable.

4. Horizontal Database‑Table Splitting (ultimate solution)

Applicable: billion‑scale data and high‑concurrency core internet services.

Logic: combine multiple databases with multiple tables per database for full horizontal scaling.

How – Four Sharding‑Key Strategies

Strategy 1 – Hash Modulo (most popular)

Rule: sharding_key % total_shards, e.g., user_id % 16 spreads rows evenly across 16 tables.

✅ Uniform data distribution, no hot spots.

❌ Expansion requires massive data migration.

Suitable for: high‑frequency queries on user or merchant dimensions.

Strategy 2 – Time‑Range Sharding

Rule: create tables per year or month, e.g., order_202609, order_202610.

✅ Natural hot‑cold separation, simple archiving, zero‑migration scaling.

❌ New data concentrates on the latest table, creating a hotspot.

Suitable for: append‑only data such as orders, logs, or transaction records.

Strategy 3 – ID‑Range Sharding

Divide the ID space into fixed intervals, each interval stored in a dedicated shard.

✅ Extremely fast range queries.

❌ Prone to data skew and obvious hotspots.

Strategy 4 – Consistent Hashing

Improves on simple modulo by moving only a small portion of data during scaling.

Suitable for: scenarios that require frequent scaling and are sensitive to migration cost.

Sharding‑JDBC in Practice

Sharding‑JDBC provides client‑side sharding without middleware and incurs zero performance overhead.

Key advantages: no SQL changes, no business‑code modifications, seamless Spring Boot integration.

spring:
  shardingsphere:
    sharding:
      tables:
        user_order:
          actual-data-nodes: ds0.order_$->{0..15}
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: user_id % 2
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: user_id % 16

The core logic routes queries automatically based on user_id, making the sharding transparent to the application.

Five Critical Distributed Challenges

Distributed ID duplication: auto‑increment IDs are unique only within a single database. Solutions include Snowflake algorithm, segment allocation, or Redis auto‑increment.

Cross‑database Join failure: data in different databases cannot be joined directly. Solutions include column redundancy, application‑level joins, wide‑table design, or Elasticsearch joins.

Pagination & sorting issues: limit‑offset pagination across shards can produce missing, duplicate, or out‑of‑order results. Solutions include in‑memory merge sort, cursor pagination, and avoiding large offsets.

Distributed transaction problems: local transactions collapse across shards, causing inconsistency. Solutions include eventual consistency, TCC, SAGA, or local message tables.

Shard expansion data migration: hash‑modulo expansion forces full data reshuffle, incurring high migration cost. Solutions include dual‑write migration, gray‑release, and consistent‑hash optimization.

Common Production Pitfalls

Premature sharding or over‑design introduces bugs and operational overhead.

Choosing an inappropriate sharding key triggers full‑shard scans and degrades performance.

Ignoring pagination‑sorting anomalies; small test data hides issues that appear in production.

Relying on auto‑increment IDs after sharding causes primary‑key collisions.

Skipping dual‑write verification during data migration results in data loss or dirty data.

Key Takeaways

Sharding is the final fallback; prioritize index tuning, archiving, and read/write splitting first.

Vertical splitting decouples business and trims fields; horizontal splitting handles massive data.

Hash sharding gives uniform distribution; time‑range sharding simplifies archiving and scaling.

Sharding‑JDBC provides client‑side sharding with optimal performance and zero code intrusion.

Remember the five core challenges: unique IDs, cross‑shard joins, pagination/sorting, distributed transactions, and migration costs.