Sharding JDBC案例实战

技术分享 3年前 (2022-05-16) 0 999+

基础分库

以下实例基于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字段取余分片到两个数据库

引入依赖

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

参数配置

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}

测试插入数据

@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);         }     }

主键生成使用雪花算法

id需要设置IDENTITY

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

参数配置

增加id设置：

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

测试

    @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);         }     }

自定义主键生成器

自定义主键生成器类

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) {      }  }

配置

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

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

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

#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进行查询时能够只查询一个库，而不是笛卡尔积的进行查询。

完整的参数配置

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); }

可以看出，只查询了一个库：

广播表设置

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

广播表配置

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

测试

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

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

分库且分表

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

参数配置

#订单表分库且分表 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

测试

    @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里面。

读写分离

参数配置

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

查询测试

    @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或者数据表中，而是在业务逻辑代码中
读写分离操作，如果需要强制在主库进行某些操作

自定义Hint实现类

public class MyHintShardingAlgorithm implements HintShardingAlgorithm<String> {       @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;     } }

配置自定义的Hint类

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

测试

    @Test     public void testHint(){         HintManager hintManager = HintManager.getInstance();         hintManager.addDatabaseShardingValue("city","master"); //        hintManager.setMasterRouteOnly();         List<City> all = cityRepository.findAll();         all.forEach(x->System.out.println(x));     }

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

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

数据加密

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

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

先创建个表

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;

创建实体类

@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; }

参数配置

#定义数据表真实明文列 #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

测试

    @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);         }     }

发表评论