版本：5.0.0-beta

内容

本文重点关注 ShardingSphere-JDBC 的分库分表、读写分离和数据加密的演示

准备工作

项目导入

代码 clone

# 全局变量设置 放开文件名长度限制（工程中存在长命名文件会导致下载出现 error: unable to create file ** Jaeger**.java: Filename too long 错误）
git config --global core.longpaths true
# 拉起 5.0.0-beta-release 分支，且克隆深度限制一层
git clone -b 5.0.0-beta-release --depth=1 https://gitee.com/Sharding-Sphere/sharding-sphere.git

导入依赖

mvn install -Dmaven.test.skip=true -Dmaven.javadoc.skip=true

注意：example 的版本可能需要修改成 5.0.0-beta，否则报错

环境搭建

MySQL 搭建

docker volume create v1

docker run -d -it --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v v1:/var/lib/mysql --privileged mysql:5.7

SQL 导入

建库

# 读写分离（暂时未真正读写库）
CREATE SCHEMA IF NOT EXISTS demo_write_ds;
CREATE SCHEMA IF NOT EXISTS demo_read_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_read_ds_1;

# 加密使用
CREATE SCHEMA IF NOT EXISTS demo_ds;
CREATE SCHEMA IF NOT EXISTS demo_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_1;

建表

# 读库订单相关表（暂时未真正读写库）
CREATE TABLE demo_read_ds_0.`t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address_id` bigint(20) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS demo_read_ds_0.t_order_item (
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    status VARCHAR(50),
    PRIMARY KEY (order_item_id)
)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE demo_read_ds_1.`t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address_id` bigint(20) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS demo_read_ds_1.t_order_item (
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    status VARCHAR(50),
    PRIMARY KEY (order_item_id)
)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

# 加密样例用
CREATE TABLE demo_ds.`t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address_id` bigint(20) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

功能演示

读写分离

入口

sharding-raw-jdbc-example 项目的 ShardingRawYamlConfigurationExample

配置

YAML 配置 readwrite-splitting.yaml

dataSources:
  write_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_write_ds?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
  read_ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_read_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
  read_ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_read_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456

rules:
- !READWRITE_SPLITTING
  dataSources:
    pr_ds:
      writeDataSourceName: write_ds
      readDataSourceNames: [read_ds_0, read_ds_1]

props:
  sql-show: false

开启 ShardingType.READWRITE_SPLITTING 模式

演示结果

-------------- Process Success Begin ---------------
---------------------------- Insert Data ----------------------------
---------------------------- Print Order Data -----------------------
order_id: 1, user_id: 1, address_id: 1, status: INSERT_TEST
.......
---------------------------- Print OrderItem Data -------------------
---------------------------- Delete Data ----------------------------
---------------------------- Print Order Data -----------------------
order_id: 1, user_id: 1, address_id: 1, status: INSERT_TEST
.......
---------------------------- Print OrderItem Data -------------------
-------------- Process Success Finish --------------

分库分表

入口

sharding-raw-jdbc-example 项目的 ShardingRawYamlConfigurationExample

配置

YAML 配置 sharding-databases-tables.yaml

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456

rules:
- !SHARDING
  tables:
    t_order: 
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy: 
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_address
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 2}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE
      props:
          worker-id: 123

props:
  sql-show: false

开启 ShardingType.READWRITE_SPLITTING 模式

演示结果

-------------- Process Success Begin ---------------
---------------------------- Insert Data ----------------------------
---------------------------- Print Order Data -----------------------
order_id: 641965069307457536, user_id: 1, address_id: 1, status: INSERT_TEST
.......
---------------------------- Print OrderItem Data -------------------
order_item_id:641965069907243009, order_id: 641965069307457536, user_id: 1, status: INSERT_TEST
.......
---------------------------- Delete Data ----------------------------
---------------------------- Print Order Data -----------------------
---------------------------- Print OrderItem Data -------------------
-------------- Process Success Finish --------------

可以看到订单ID为偶数的落在demo_ds_0，为奇数的落在demo_ds_1

加密

入口

encrypt-raw-jdbc-example 项目的 EncryptRawYamlConfigurationExample

配置

dataSources:
  unique_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456

rules:
- !ENCRYPT
  tables:
    t_user:
      columns:
        user_name:
          plainColumn: user_name_plain
          cipherColumn: user_name
          encryptorName: name_encryptor
        pwd:
          cipherColumn: pwd
          assistedQueryColumn: assisted_query_pwd
          encryptorName: pwd_encryptor
  encryptors:
    name_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
    pwd_encryptor:
      type: assistedTest

演示结果

[INFO ] 2021-09-07 10:46:23,943 --main-- [com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Starting... 
[INFO ] 2021-09-07 10:46:24,520 --main-- [com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Start completed. 
-------------- Process Success Begin ---------------
---------------------------- Insert Data ----------------------------
---------------------------- Print User Data -----------------------
user_id: 1, user_name: test_1, pwd: decryptValue
.......
---------------------------- Delete Data ----------------------------
---------------------------- Print User Data -----------------------
-------------- Process Success Finish --------------

对比数据库的结果：

参考

shardingsphere 5.x document

Apache ShardingSphere 研读（2）：ShardingSphere-JDBC 演示

内容

准备工作

项目导入

代码 clone

导入依赖

环境搭建

MySQL 搭建

SQL 导入

建库

建表

功能演示

读写分离

入口

配置

演示结果

分库分表

入口

配置

演示结果

加密

入口

配置

演示结果

参考