Sharding JDBC案例实战
阅读原文时间:2022年05月15日阅读:1

基础分库

以下实例基于shardingsphere 4.1.0 + SpringBoot 2.2.5.RELEASE版本

依赖导入:

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
    <springboot.version>2.2.5.RELEASE</springboot.version>
    <shardingsphere.version>4.1.0</shardingsphere.version>
</properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${springboot.version}</version>
            <scope>test</scope>
        </dependency>

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

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

    </dependencies>

场景:通过id字段取余分片到两个数据库

  1. 引入依赖

    org.apache.shardingsphere sharding-jdbc-spring-boot-starter

  2. 参数配置

    spring.shardingsphere.datasource.names=ds0,ds1

    spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=0490218292

    spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=0490218292

    spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}

  3. 测试插入数据

    @Test
    public void testAdd(){
    for (int i = 0; i <= 20; i++) {
    Position position=new Position();
    position.setId((long) i);
    position.setName("lagou"+i);
    position.setSalary("1000");
    position.setCity("beijing");
    positionRepository.save(position);
    }
    }

主键生成使用雪花算法

  1. id需要设置IDENTITY

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

  2. 参数配置

增加id设置:

#id设置
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
  1. 测试

    @Test
    public void testAdd(){
        for (int i = 0; i <= 20; i++) {
            Position position=new Position();
            position.setName("lagou"+i);
            position.setSalary("1000");
            position.setCity("beijing");
            positionRepository.save(position);
        }
    }

自定义主键生成器

  1. 自定义主键生成器类

    public class MyCustomId implements ShardingKeyGenerator {

    @Override
    public Comparable<?> generateKey() {
        return System.currentTimeMillis()+new Random().nextInt(100000);
    }
    
    @Override
    public String getType() {
        //自定义一个名称
        return "MYID";
    }
    
    @Override
    public Properties getProperties() {
        return null;
    }
    
    @Override
    public void setProperties(Properties properties) {
    
    }

    }

  2. 配置

在resources下创建META-INF/services目录,并创建一个文件,文件名为:org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

里面写自定义主键生成器的全类名

  1. 配置生成器类型的地方改为和我们自定义的生成器的类型一致

    #id设置
    spring.shardingsphere.sharding.tables.position.key-generator.column=id
    spring.shardingsphere.sharding.tables.position.key-generator.type=MYID

两表关联的分库

场景:职位表(position)和职位详情表(position_detail)是关联的两个表,关联关系是:position_detail.pid = position.id,那么我们期望在插入数据后,根据职位Id进行查询时能够只查询一个库,而不是笛卡尔积的进行查询。

  1. 完整的参数配置

    spring.shardingsphere.datasource.names=ds0,ds1

    spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=0490218292

    spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=0490218292

    #职位表设置
    spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
    #id设置
    spring.shardingsphere.sharding.tables.position.key-generator.column=id
    spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
    #职位表详情设置
    spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
    spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{pid % 2}
    #id设置
    spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
    spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE

可以看出position的id的分片策略和position_detail的pid的分片策略一致。

2. 测试

@Test
public void testQueryPosition(){
    Object positionAndDetailById = positionRepository.findPositionAndDetailById(730545854473043968L);
    System.out.println(positionAndDetailById);
}

可以看出,只查询了一个库:

广播表设置

场景:城市表属于基础表,数据量不大,每个库都可以存一样的数据。

  1. 广播表配置

    #广播表设置
    spring.shardingsphere.sharding.broadcast-tables=city
    spring.shardingsphere.sharding.tables.city.key-generator.column=id
    spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

  2. 测试

    @Test
    public void testAddCity(){
    City city=new City();
    city.setName("成都");
    city.setProvince("四川");
    cityRepository.save(city);
    }

和之前的不同,这一条数据的插入,两个库都有。且ID也是一致的。

分库且分表

场景:我们有一个订单表,可以根据公司id(companyId)进行分库,然后在根据id进行分表。

  1. 参数配置

    #订单表分库且分表
    spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
    spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id%2}
    spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
    spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
    #id设置
    spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
    spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

  2. 测试

    @Test
    @Repeat(100)
    public void testAddBOrder(){
        BOrder bOrder=new BOrder();
        bOrder.setDel(false);
        bOrder.setCompanyId(new Random().nextInt(10));
        bOrder.setPositionId(23);
        bOrder.setUserId(22);
        bOrder.setPublishUserId(11);
        bOrder.setResumeType(1);
        bOrder.setStatus("AUTO");
        bOrder.setCreateTime(new Date());
        bOrder.setOperateTime(new Date());
        bOrder.setWorkYear("2");
        bOrder.setName("lagou");
        bOrder.setPositionName("Java");
        bOrder.setResumeId(23443);
        bOrderRepository.save(bOrder);
    }

我们发现数据插入到了ds_0.b_order0、ds_0.b_order1、ds_1.b_order0、ds_1.b_order1四个node里面。

读写分离

  1. 参数配置

    spring.shardingsphere.datasource.names=master,slave0

    spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.master.username=root
    spring.shardingsphere.datasource.master.password=0490218292

    spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/ds_0_slave?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    spring.shardingsphere.datasource.slave0.username=root
    spring.shardingsphere.datasource.slave0.password=0490218292

    #读写分离
    spring.shardingsphere.masterslave.name=datasource
    spring.shardingsphere.masterslave.master-data-source-name=master
    spring.shardingsphere.masterslave.slave-data-source-names=slave0
    #多个读库时的负载均衡策略
    spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN

  2. 查询测试

    @Test
    public void test(){
        List<City> all = cityRepository.findAll();
        all.forEach(x->System.out.println(x));
    }

分库分表+读写分离的参数配置

#数据源
spring.shardingsphere.datasource.names=master0,slave0,slave1,master1,slave2,slave3

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/slave0?useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://localhost:3306/slave1?useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://localhost:3306/master1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://localhost:3306/slave2?useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://localhost:3306/slave3?useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

#分库分表
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=master$->{company_id % 2}
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=master$->{0..1}.b_order$->{0..1}
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order$->{id % 2}

#读写分离
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0, slave1
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2, slave3

强制路由

在一些应用场景中,分片条件并不存在于SQL,而存在于外部业务逻辑。因此需要提供一种通过在外部业务代码中指定路由配置的一种方式,在ShardingSphere中叫做Hint。如果使用Hint指定了强制分片路由,那么SQL将会无视原有的分片逻辑,直接路由至指定的数据节点操作。

使用场景:

  • 数据分片操作,如果分片键没有在SQL或者数据表中,而是在业务逻辑代码中
  • 读写分离操作,如果需要强制在主库进行某些操作
  1. 自定义Hint实现类

    public class MyHintShardingAlgorithm implements HintShardingAlgorithm {

    @Override
    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> hintShardingValue) {
        Collection<String> result=new ArrayList<>();
       if(hintShardingValue.getValues().contains("master")){
           ((ArrayList<String>) result).add("master");
       }else {
           ((ArrayList<String>) result).add("slave0");
       }
       return result;
    }

    }

  2. 配置自定义的Hint类

    spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.mmc.sharding.hint.MyHintShardingAlgorithm

  3. 测试

    @Test
    public void testHint(){
        HintManager hintManager = HintManager.getInstance();
        hintManager.addDatabaseShardingValue("city","master");

    // hintManager.setMasterRouteOnly();
    List all = cityRepository.findAll();
    all.forEach(x->System.out.println(x));
    }

还可以使用hintManager.setMasterRouteOnly()指定仅路由到主库。

测试过程中发现Hint的自定义策略和读写分离配置有冲突。配置了读写分离后自定义Hint类不生效了,仅hintManager.setMasterRouteOnly()还可以用。

数据加密

脱敏配置分为如下几个:数据源配置,加密器配置,脱敏表配置以及查询属性配置,其详情如下图所示:

  • 数据源配置:指Datasource的配置信息
  • 加密器配置:指使用什么加密策略进行加解密。目前ShardingSphere内置了两种加解密策略AES、MD5
  • 脱敏表配置:指定哪个列用于存储密文数据,哪个列存明文数据,以及在应用里用哪个列(应用层sql里使用的列名)
  • 查询属性配置:当数据库同时存了明文和密文的时候,该属性开关用于决定是直接查询数据库表里的明文,还是查密文然后通过解密后返回。
  1. 先创建个表

    CREATE TABLE c_user (
    Id bigint(11) NOT NULL AUTO_INCREMENT,
    name varchar(256) DEFAULT NULL,
    pwd_plain varchar(256) DEFAULT NULL,
    pwd_cipher varchar(256) DEFAULT NULL,
    PRIMARY KEY (Id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  2. 创建实体类

    @Entity
    @Table(name = "c_user")
    public class CUser implements Serializable {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name")
    private String name;
    
    /**
     * 逻辑列名
     */
    @Column(name = "password")
    private String password;

    }

  3. 参数配置

    #定义数据表真实明文列
    #spring.shardingsphere.encrypt.tables.c_user.columns.password.plain-column=pwd_plain
    #定义数据表真实密文列
    spring.shardingsphere.encrypt.tables.c_user.columns.password.cipher-column=pwd_cipher
    #定义加密器,名称为lagou_pwd
    spring.shardingsphere.encrypt.encryptors.lagou_pwd.type=aes
    spring.shardingsphere.encrypt.encryptors.lagou_pwd.props.aes.key.value=1234
    #指定加密器,password是逻辑列名,与实体类中的字段对应
    spring.shardingsphere.encrypt.tables.c_user.columns.password.encryptor=lagou_pwd

  4. 测试

    @Test
    public void testEncrypt(){
        CUser cUser=new CUser();
        cUser.setName("阿百川");
        cUser.setPassword("123456");
        cUserRepository.save(cUser);
    }
    
    @Test
    public void testQueryByPassword(){
        List<CUser> byPassword = cUserRepository.findByPassword("123456");
        System.out.println(byPassword);
    }

数据库存放的已经是密文了,通过明文密码也可以查询到数据了。

分布式事务

仅仅需要在测试方法上加上两个注解:

    @Transactional(rollbackFor = Exception.class)
    @ShardingTransactionType(TransactionType.XA)

TransactionType有XA、BASE、LOCAL三种

@Test
    @Transactional(rollbackFor = Exception.class)
    @ShardingTransactionType(TransactionType.XA)
    public void testAddDetail(){
        for (int i = 0; i <= 3; i++) {
            Position position=new Position();
            position.setName("lagou"+i);
            position.setSalary("1000");
            position.setCity("beijing");
            positionRepository.save(position);

            if(i==3){
                throw new RuntimeException();
            }
            PositionDetail positionDetail=new PositionDetail();
            positionDetail.setPid(position.getId());
            positionDetail.setDescription("详情");
            positionDetailRepository.save(positionDetail);
        }
    }