ShardingSphere-JDBC Database Sharding Tutorial Project

In the author's view, message queues , caching , and database sharding are the three key solutions for high-concurrency systems.

Database sharding is widely used because the implementation is relatively simple, but sharding is not just about splitting data - it also requires consideration of capacity expansion and contraction (full synchronization, incremental synchronization, data validation, etc.).

The author created an educational database sharding demo project to practically demonstrate the technical system of database sharding. The project includes three modules:

shardingjdbc4-spring : Implements database sharding using ShardingSphere-JDBC 4.X

shardingjdbc5-spring : Implements database sharding using ShardingSphere-JDBC 5.X

idgenerator : A simple server-side ID generator based on gRPC

The business scenario is based on an O2O company's order service. When an enterprise user creates a purchase order, the following records are generated: order base table t_ent_order (1 record), order detail table t_ent_order_detail (1 record), and order item table t_ent_order_item (N records). With an estimated 100 million records per year, the sharding strategy is: order base table and detail table are sharded by ent_id into 4 databases, while order item table is sharded by ent_id into 4 databases and further divided into 8 tables per database.

The project requires creating 4 databases (ds_0, ds_1, ds_2, ds_3) and executing the SQL script in each. The project is a typical SpringBoot application with controller, entity, and service layers. The pom.xml needs to include the ShardingSphere dependency:

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

The configuration includes data sources (ds0, ds1, ds2, ds3), SQL logging (sql.show), and sharding rules. The logical table t_ent_order_item maps to actual tables t_ent_ent_order_item_0 through t_ent_order_item_7 . The actual data nodes follow the pattern ds$->{0..3}.t_ent_order_item_$->{0..7} . Two key configurations are needed: actual nodes (the smallest unit of data sharding combining datasource and table name) and sharding algorithms (including sharding columns and algorithms for both database and table sharding).

After configuration, the application can be started and tested via Swagger UI. Tests include creating orders and querying by order ID. For example, inserting 1 order record and 1 order detail into ds3 shard, with 2 order item records into the t_ent_order_item_7 table in ds3.