Understanding Database Sharding: Concepts, Strategies, Mycat and Sharding-JDBC

1. What is Sharding (Database Partitioning)

Sharding means splitting data originally stored in a single database or table into multiple databases or tables.

2. Why Sharding

As data volume grows, a single database becomes a bottleneck in storage, CPU, memory, and I/O, and operations such as CRUD become increasingly costly; sharding distributes the load across multiple nodes.

3. Sharding Implementation Strategies

There are two main approaches: vertical partitioning (splitting tables by functional modules into different databases) and horizontal partitioning (splitting rows of a large table based on a rule such as user‑ID hash).

Vertical partitioning is suitable when the number of tables is large but each table is relatively small and business logic is clearly separated. Horizontal partitioning is chosen when a single table holds massive data or hot data, requiring careful evaluation of shard granularity and load balancing.

4. Common Principles and Frameworks

Both vertical and horizontal strategies are often combined; the article introduces two frameworks that implement these principles.

Mycat

Official site: http://www.mycat.io

Mycat is an open‑source middleware proxy that is transparent to developers, providing a clustered database solution with transaction support, ACID compliance, and the ability to replace expensive Oracle clusters.

Advantages

Development‑agnostic

No need to restart when adding or removing nodes

Cross‑language support (Java, PHP, etc.)

Disadvantages

Performance overhead due to an extra proxy layer

Does not support cross‑database queries

Typical Mycat scenarios include read/write separation, massive table sharding (up to billions of rows), multi‑tenant isolation, large‑scale reporting, and as a simple alternative to HBase for real‑time queries on huge datasets.

Sharding-JDBC

Official site: http://shardingsphere.apache.org/index_zh.html

Sharding-JDBC is a lightweight Java framework that enhances the JDBC layer, offering sharding capabilities without requiring additional deployment; it works as an enhanced JDBC driver compatible with any ORM framework.

Advantages

High performance

Supports cross‑database JDBC

Disadvantages

Increases development complexity

Only supports Java (no cross‑language support)

Key concepts include logical tables, real tables, data nodes, binding tables, and broadcast tables. Logical tables represent the whole set of horizontally split tables (e.g., t_order0, t_order1 belong to logical table t_order). Binding tables share the same sharding key to avoid Cartesian products, while broadcast tables are replicated on every node because they contain static reference data.

5. Common Sharding Strategies

1) Modulo sharding 2) Range sharding (often time‑based) 3) Business‑specific sharding (e.g., by city or tenant)

Time‑range sharding is useful for hot‑cold data separation, while city‑based sharding resembles multi‑tenant isolation.

Conclusion

The article provides a concise overview of database sharding concepts, vertical and horizontal partitioning strategies, and the underlying principles of two popular solutions—Mycat and Sharding-JDBC. Considering the company’s rapid data growth and the need for low‑cost, reliable, and well‑documented solutions, Sharding-JDBC is recommended, with a city‑based sharding strategy.