Mastering Database Sharding: Theory, Design, and TDDL Implementation

This article is suitable for beginners who need to transform a single‑database‑single‑table architecture into a multi‑database‑multi‑table solution.

It mainly discusses the factors to consider during database sharding, the corresponding solutions, and the pitfalls encountered.

1 Foreword

Before implementing sharding, we must understand two questions: what sharding is and why it is needed.

1 What is sharding?

Sharding literally means splitting a single database into multiple databases (分库) and splitting a single table into multiple tables (分表), distributing data across them.

2 Why sharding?

Key reasons: improving performance and increasing availability.

From a performance perspective

When data volume and QPS grow, read/write latency becomes a bottleneck. If QPS exceeds the capacity of a single database, splitting databases can distribute connection pressure. Similarly, when a single table becomes too large, horizontal partitioning (splitting tables) can alleviate read/write performance issues.

From an availability perspective

A single database failure can cause 100% data loss. By splitting databases, the impact of a failure is reduced proportionally (e.g., 2 databases → 50% impact, 4 databases → 25% impact).

2 How to shard

Three possible schemes:

Only split databases

Only split tables

Both databases and tables

Choosing a partitioning scheme depends on business data volume. For example, a customer‑service system generates about 65,000 tickets per day, projected to 80,000 per day, with related operation‑log and form tables. Over five years this yields ~146 million tickets, suggesting 32 tables for tickets and 512 tables for logs when using 4 databases.

3 How to partition data

Two common methods:

Horizontal partitioning: distribute rows based on a business dimension (e.g., member ID) across databases/tables.

Vertical partitioning: split a table's columns into separate tables (e.g., separating product, buyer, and payment info).

Horizontal sharding is chosen for the described scenario because all operations are member‑centric.

4 New problems after sharding

Ensuring uniform data distribution to avoid hot spots. A consistent‑hash algorithm with a reduced ring (4096 nodes) is used, and the database index is calculated as:

int index = (Math.abs(buyerId.hashCode()) % 4096) / (4096 / DB_COUNT);

For ID generation, a segmented ID format (18 digits) is used: date + version + db‑index + sequence. Java implementation:

public static final int DB_COUNT = 4;
public Long build(){
    int dbIndex = indexDbByBuyerId(buyerId);
    StringBuilder pid = new StringBuilder(18)
        .append(DateFormatUtils.format(timeInMills, "yyMMdd"))
        .append(version)
        .append(String.format("%02d", dbIndex))
        .append(String.format("%08d", seqNum % 10000000));
    return Long.valueOf(pid.toString());
}

public int indexDbByBuyerId(Long buyerId){
    return (Math.abs(buyerId.hashCode()) % 4096) / (4096 / DB_COUNT);
}

5 Transaction handling in a sharded environment

Two approaches:

Distributed transactions managed by middleware – simple but incurs performance overhead.

Application‑controlled small transactions – better performance but requires careful design.

In the described system, both ticket and its operation‑log are routed using the same member‑ID based rule, ensuring they reside in the same database.

6 Data migration after sharding

Two migration strategies:

Offline migration: schedule a maintenance window, use Alibaba Data Transmission Service (DTS) for full data copy, switch TDDL configuration, restart services.

Online migration: copy historical data via DTS, switch configuration with minimal downtime, then incrementally copy recent data.

7 TDDL configuration tips

When writing Groovy scripts for TDDL routing, use x.intdiv(y) for integer division because the / operator returns a double in Groovy.

// Java
System.out.println(5 / 3); // 1
// Groovy
println(5 / 3);       // 1.6666666667
println(5.intdiv(3)); // 1

For reference, see the official Groovy documentation on integer division.

Reference materials [1] https://baijiahao.baidu.com/s?id=1622441635115622194&wfr=spider&for=pc [2] http://www.zsythink.net/archives/1182 [3] https://www.aliyun.com/product/dts [4] https://docs.groovy-lang.org/latest/html/documentation/core-syntax.html#integer_division [5] https://github.com/alibaba/tb_tddl