实践篇-快速开始

# 一、快速开始

在shardingsphere-jdbc-demo (opens new window)项目中，我们对shardingsphere的分库分表功能、读写分离功能进行了验证，代码片段如下

# 1.1、引入相关依赖

<properties>
    <shardingsphere.version>5.3.2</shardingsphere.version>
</properties>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${shardingsphere.version}</version>
</dependency>

1
2
3
4
5
6
7
8

# 1.2、加入配置文件

# 1.2.1、配置sharding配置

resources/sharding/sharding-config.yaml

此处配置了读写数据源以及自定义分片策略

# JDBC 逻辑库名称。在集群模式中，使用该参数来联通 ShardingSphere-JDBC 与 ShardingSphere-Proxy。
# 默认值：logic_db
#打印SQL
props:
  sql-show: true
#为方便调试，暂时使用Standalone模式
mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds_0_write:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://mysql:3306/testsharding1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456
  ds_0_read:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://mysql:3306/testsharding1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456
  ds_1_write:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://mysql:3306/testsharding2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456
  ds_1_read:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://mysql:3306/testsharding2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456

rules:
  - !READWRITE_SPLITTING
      dataSources:
        readwrite_ds_0:
          staticStrategy:
            writeDataSourceName: ds_0_write
            readDataSourceNames:
              - ds_0_read
          loadBalancerName: round_robin
        readwrite_ds_1:
          staticStrategy:
            writeDataSourceName: ds_1_write
            readDataSourceNames:
              - ds_1_read
          loadBalancerName: round_robin
      loadBalancers:
        round_robin:
          type: ROUND_ROBIN
  - !TRANSACTION
    defaultType: LOCAL
#    defaultType: XA
#    providerType: Narayana/Atomikos
#    defaultType: BASE
#    providerType: Seata

  - !SHARDING
    tables:
      t_user:
        actualDataNodes: readwrite_ds_0.t_user_${1..2}


    #默认的数据库分片策略
#    defaultDatabaseStrategy:
#      hint:
#        shardingAlgorithmName: my-hint
    defaultTableStrategy:
      standard:
        shardingColumn: id
        shardingAlgorithmName: my-standard
#    bindingTables:
#      - t_user
#
#    broadcastTables:
#      - t_user
    #分片算法
    shardingAlgorithms:
      my-standard:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: com.example.demo.sharding.CustomShardingStandardAlgorithm

    #自定义的SQL审计策略
#    defaultAuditStrategy:
#      auditorNames:
#        - sharding_key_required_auditor
#    auditors:
#      sharding_key_required_auditor:
#        type: CUSTOM_SHARDING_CONDITIONS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

更多配置请参考ShardingSphere-JDBC YAML配置 (opens new window)

# 1.2.2、配置spring-boot

application.yml

server:
  port: 8081

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

spring:
  application:
    name: demo
  datasource:
    url: jdbc:shardingsphere:classpath:sharding/sharding-config.yaml
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver

1
2
3
4
5
6
7
8
9
10
11
12
13

# 1.3、自定义分片算法

CustomShardingStandardAlgorithm.java

@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
    if (availableTargetNames.size() == 1) {
        return shardingValue.getLogicTableName();
    }
    Integer value = shardingValue.getValue();
    int index = value % (availableTargetNames.size());
    return availableTargetNames.toArray()[index] + "";
}

1
2
3
4
5
6
7
8
9

添加到配置文件sharding-config.yaml中

#分片算法
shardingAlgorithms:
  my-standard:
    type: CLASS_BASED
    props:
      strategy: standard
      algorithmClassName: com.example.demo.sharding.CustomShardingStandardAlgorithm

1
2
3
4
5
6
7

# 二、验证

# 2.1、分库分表验证

在上面的配置中t_user表有两张分片表t_user_1和t_user_2,分片字段是id，分别查询id = 1 和 id = 2的数据

@Test
void selectById() {
    userMapper.selectById(1);
    userMapper.selectById(2);
}

1
2
3
4
5

日志如下：

Logic SQL: SELECT id,name,update_time,create_time FROM t_user WHERE id=? 
Actual SQL: ds_0_read ::: SELECT id,name,update_time,create_time FROM t_user_2 WHERE id=?  ::: [1]
.
.
.
.
Logic SQL: SELECT id,name,update_time,create_time FROM t_user WHERE id=? 
Actual SQL: ds_0_read ::: SELECT id,name,update_time,create_time FROM t_user_1 WHERE id=?  ::: [2]

1
2
3
4
5
6
7
8

日志主要有两点需要特别关注

第一个是Logic SQL，也就是逻辑SQL，未经过改写的原始SQL。

第二个是Actual SQL，也就是真实SQL，此处根据当前id到ds_44数据源

可以通过上面的日志查看路由是否符合预期

# 2.2、读写分离验证

在上面的配置中我们配置了两个读写数据源readwrite_ds_0(写ds_0_write、读ds_0_read)和readwrite_ds_1(写ds_1_write、读ds_1_read)

@Test
void update() {
    User u = new User();
    u.setId(1);
    u.setName("123");
    userMapper.updateById(u);
}

1
2
3
4
5
6
7

日志如下：

Logic SQL: UPDATE t_user  SET name=?  WHERE id=?
Actual SQL: ds_0_write ::: UPDATE t_user_2  SET name=?  WHERE id=? ::: [123, 1]

1
2

上面的SQL，在ds_0_write执行，可以看到，shardingsphere自动实现了读写分离

#Shardingsphere

← shardingsphere基础介绍实践篇-混合部署架构搭建→