Master ShardingSphere: Hands‑On Sharding‑JDBC Sharding & Partitioning Guide

This chapter introduces a practical ShardingSphere sharding and partitioning implementation, allowing readers to experience the benefits and potential issues of database sharding.

Official website: https://shardingsphere.apache.org/

Article Overview

The previous article "Sharding Design and Common Issues" discussed problems encountered during sharding; this chapter provides a concrete hands‑on case.

ShardingSphere Ecosystem

1. What is ShardingSphere?

Apache ShardingSphere is an open‑source distributed database middleware ecosystem that provides standardized data sharding, distributed transactions, and database governance functions, leveraging existing relational databases rather than creating a new one.

It offers data sharding, distributed transaction, and database governance.

It builds on relational databases to utilize their compute and storage capabilities.

Since version 5.x, it supports plug‑in architecture with features such as data sharding, read/write splitting, multiple replicas, data encryption, shadow testing, and supports MySQL, PostgreSQL, SQLServer, Oracle, etc.

The ecosystem includes sharding‑JDBC, sharding‑proxy, sharding‑sidecar and their combinations.

2. Design Philosophy

ShardingSphere adopts a "database+" design, aiming to build a standard ecosystem above the database and supplement missing capabilities, effectively enhancing the database.

Image source: official website

3. Core Concepts

1. Logical Table

A set of horizontally split tables sharing the same logical structure, e.g., order tables split into t_orders_0 … t_orders_9 with logical name t_orders.

2. Actual Table

The physical tables that exist in the database, such as t_orders_0 … t_orders_9.

3. Data Node

The smallest unit of sharding, composed of a data source name and a table name, e.g., ds_0.t_orders_0.

4. Binding Table

Tables that share the same sharding rule, e.g., t_orders and t_orders_item bound on order_id, which avoids Cartesian product in joins.

SELECT i.* FROM t_orders o JOIN t_orders_item i ON o.order_id=i.order_id WHERE o.order_id IN (10,11);

Without binding, the routing would generate four SQL statements (Cartesian product); with binding, only two statements are generated.

5. Broadcast Table

Tables that exist in all sharding data sources with identical structure and data, suitable for small dictionary tables.

6. Sharding Key

The database column used for sharding, e.g., order_id. If absent, full routing occurs with poorer performance.

7. Sharding Algorithm

Supported operators: =, >=, <=, >, <, BETWEEN, IN. Implementations are custom by developers. Four built‑in types are provided: PreciseShardingAlgorithm, RangeShardingAlgorithm, ComplexKeysShardingAlgorithm, HintShardingAlgorithm.

8. Sharding Strategy

Combines a sharding key with a sharding algorithm. Five strategies are available: StandardShardingStrategy, ComplexShardingStrategy, InlineShardingStrategy, HintShardingStrategy, NoneShardingStrategy.

Sharding‑JDBC Practical Example

Requirement Analysis

Create an order table t_orders for sharding tests.

Preparation

Create physical tables t_orders_1 and t_orders_2.

DROP TABLE IF EXISTS `t_orders_1`; CREATE TABLE `t_orders_1` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', `order_id` bigint NULL COMMENT 'Order Code', `customer_id` varchar(255) NOT NULL COMMENT 'Customer ID', `amount` decimal(10,2) NOT NULL COMMENT 'Order Amount', `status` tinyint NOT NULL COMMENT 'Order Status', `create_date` datetime NOT NULL COMMENT 'Creation Date', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Order Table' ROW_FORMAT=Dynamic;

Environment Configuration

Import dependencies

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>4.1.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
</dependency>

Create entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private Long orderId;
    private Integer customerId;
    private Double amount;
    private Integer status;
    private Date createDate;
}

Create mapper interface

@Mapper
@Repository
public interface OrdersMapper {
    @Insert("insert into t_orders(id,order_id,customer_id,amount,status,create_date) values(#{id},#{orderId},#{customerId},#{amount},#{status},#{createDate})")
    void insert(Orders orders);
}

Now proceed with sharding implementation.

Horizontal Table Sharding

Rule: even order_id → t_orders_1, odd order_id → t_orders_2.

# MyBatis configuration
mybatis:
  type-aliases-package: com.example.demo.entity

spring:
  shardingsphere:
    datasource:
      names: ds1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://1.92.XXX.XXX:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
    sharding:
      tables:
        t_orders:
          actual-data-nodes: ds1.t_orders_$->{1..2}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_orders_${order_id%2+1}
    props:
      sql:
        show: true

Test code inserts ten orders with SnowFlake IDs; results match expectations.

@Test
public void addOrders() {
    for (int i = 1; i <= 10; i++) {
        Orders orders = new Orders();
        // Generate 16‑digit order ID using SnowFlake
        orders.setOrderId(SnowFlakeUtil.getDefaultSnowFlakeId());
        orders.setCustomerId(i);
        orders.setAmount(1000.0 * i);
        orders.setStatus(1);
        orders.setCreateDate(new Date());
        ordersMapper.insert(orders);
    }
}

Result confirms correct routing.

Note the distinction between logical primary key id and business primary key order_id . Using order_id as the sharding key is typical, often generated by UUID or SnowFlake.

Horizontal Database Sharding

Two data nodes (Node1, Node2) host databases mydb and mydb2. Tables t_orders_1 and t_orders_2 are created on both. Sharding rule: customer_id odd/even decides database, order_id odd/even decides table.

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.162.128:3306/mydb2?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
    sharding:
      tables:
        t_orders:
          actual-data-nodes: ds$->{1..2}.t_orders_$->{1..2}
          database-strategy:
            inline:
              sharding-column: customer_id
              algorithm-expression: ds$->{customer_id%2+1}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_orders_$->{order_id%2+1}
    props:
      sql:
        show: true

Results show correct database and table routing.

Vertical Database Sharding

Node1 hosts mydb with t_orders; Node2 hosts mydb with t_customer. Sharding rule directs different tables to different databases.

# Entity classes omitted for brevity

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.162.128:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
    sharding:
      tables:
        t_orders:
          actual-data-nodes: ds1.t_orders
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_orders
        t_customer:
          actual-data-nodes: ds2.t_customer
          table-strategy:
            inline:
              sharding-column: customer_id
              algorithm-expression: t_customer
    props:
      sql:
        show: true

Vertical sharding aligns with business domains and is straightforward in production.

Vertical Table Sharding

Used when a table contains large columns that degrade performance. Example: split t_order_log into t_order_log_base and t_order_log_detail (one‑to‑one).

spring:
  shardingsphere:
    datasource:
      names: ds1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
    sharding:
      tables:
        t_order_log_base:
          actual-data-nodes: ds$->{1}.t_order_log_base
          table-strategy:
            inline:
              sharding-column: order_log_id
              algorithm-expression: t_order_log_base
        t_order_log_detail:
          actual-data-nodes: ds$->{1}.t_order_log_detail
          table-strategy:
            inline:
              sharding-column: order_log_id
              algorithm-expression: t_order_log_detail
    props:
      sql:
        show: true

@Mapper
@Repository
public interface OrdersLogMapper {
    @Insert("insert into t_order_log_base(order_log_id,order_trans_id,customer_id,create_time) values(#{orderLogId},#{orderTransId},#{customerId},#{createTime})")
    void insertOrderBase(OrderLog orderLog);

    @Insert("insert into t_order_log_detail(order_log_id,order_log_detail_info) values(#{orderLogId},#{orderLogDetail})")
    void insertOrderDetail(OrderLog orderLog);
}

Query results show data correctly stored in both base and detail tables.

Broadcast Table

Common dictionary tables replicated to all shards.

spring:
  shardingsphere:
    sharding:
      broadcast-tables: order_dict_common
      tables:
        order_dict_common:
          key-generator:
            column: id
            type: SNOWFLAKE

Read/Write Splitting

Configure master‑slave for read/write separation (master: 1.92.80.47, slave: 192.168.162.168).

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.162.128:3306/mydb?useUnicode=true&serverTimezone=UTC
        username: root
        password: ******
    masterslave:
      name: mymaster
      master-data-source-name: ds1
      slave-data-source-names: ds2

Ensure master‑slave replication works; the configuration enables read queries to be routed to the slave.