How to Scale MySQL with ShardingJdbc: Vertical vs Horizontal Partitioning

Why Data Sharding Is Needed

In the era of massive online services, relational databases such as Oracle and MySQL often hit CPU, disk I/O, and memory limits as data volume grows. To keep storage costs controllable and maintain performance, teams look for data‑sharding solutions that distribute data across multiple ordinary database servers.

Data Splitting Methods

Vertical Partitioning

Vertical partitioning separates tables by business domain—e.g., order tables go to an order database, user tables to a user database, and billing tables to a billing database. This isolates workloads and aligns with micro‑service design.

Advantages of vertical partitioning:

Clear separation of business domains, supporting micro‑service architecture.

Easier integration and extension between subsystems.

Management convenience by grouping related tables on dedicated servers.

Ability to apply different caching and DB strategies based on hot or cold data.

Disadvantages of vertical partitioning:

Cross‑database JOINs require API calls and in‑memory merging, increasing complexity.

If a particular domain experiences explosive growth, its dedicated database still faces I/O bottlenecks.

Cross‑database transaction consistency is harder to guarantee, often needing distributed transactions.

Horizontal Partitioning

Horizontal partitioning (sharding) distributes rows of a single table across many databases based on a sharding key (e.g., user ID, timestamp). Each shard stores only a subset of the total rows, keeping table size manageable and preserving SQL performance.

Advantages of horizontal partitioning:

Each shard’s table size stays within a predictable range, improving query efficiency.

Both ShardingJdbc and MyCat require minimal changes to the application layer; only routing rules need to be defined.

System stability and load capacity are enhanced.

Disadvantages of horizontal partitioning:

Cross‑shard JOINs become complex.

Ensuring data consistency across shards is challenging.

Data migration and scaling operations demand significant maintenance effort.

Choosing an Open‑Source Sharding Middleware

For “flow” or “detail” type data (e.g., billing records, usage logs), the author recommends horizontal sharding. Two popular open‑source options are ShardingJdbc and MyCat.

ShardingJdbc is a lightweight, client‑side JDBC driver that can be added as a JAR to a Spring Boot project without extra deployment. It parses, rewrites, routes, and merges SQL across shards, offering low‑cost integration. Drawbacks include tighter coupling with the application, Java‑only support, and limited traceability.

MyCat is a proxy‑based distributed database system that speaks the MySQL protocol. It sits between the client and multiple MySQL servers, offering strong extensibility and transparency to the application. Its downsides are the need for an additional middleware deployment, added network latency, and higher operational complexity.

Typical Business Scenarios Solved by ShardingJdbc

Using ShardingJdbc, three core SQL patterns are addressed:

Data insertion (INSERT) : High‑frequency writes (e.g., 100 000 VM performance records per hour) are spread across multiple databases and tables, reducing per‑table write pressure.

Aggregated queries (SELECT + SUM + GROUP BY) : ShardingJdbc parses and routes the query to each shard, then merges partial results to produce the final aggregation.

Data deletion (DELETE) : Periodic cleanup of processed detail data can be routed to the appropriate shard based on filter conditions, preventing accidental full‑shard deletions.

Designing a Spring Boot System with ShardingJdbc

The integration architecture places ShardingJdbc between the Spring Boot application and five sharding databases (sharding_db00 ~ sharding_db04). Business data is first written to the appropriate shard according to routing rules (e.g., customer ID → database, user ID → table). Aggregated results are merged by ShardingJdbc and then written to a shared database (share_db) for downstream processing such as billing or reporting. Exceptions and idempotency checks are also stored in the shared database.

The routing rule diagram shows that the customer ID determines the target database, while the user ID determines the target table. For example, a query on test_msg_queue_bill_record will automatically be executed across all five databases and their respective tables without manual iteration.

Conclusion

The article first outlines vertical and horizontal data‑splitting approaches, then focuses on horizontal sharding for high‑volume “flow/detail” data. It compares ShardingJdbc with MyCat, demonstrates how ShardingJdbc handles INSERT, GROUP‑BY aggregation, and DELETE scenarios, and presents a concrete Spring Boot integration architecture with routing rules. Advanced ShardingJdbc features such as read/write splitting, flexible transactions, dynamic routing, and governance are mentioned as topics for future articles.