ShardingShpere的sharding-jdbc效果测试

野性酷女 2022-10-07 02:57 172阅读 0赞

sharding-jdbc在目前微服务和各种saas软件等数据量日益加大的情况下变得使用的人多了起来。

## sharding-jdbc读写分离测试 ##

数据库实体

@Data
    @Table(name = "city")
    @Entity
    public class City implements Serializable {
        @Id
        @Column(name = "id")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private  Long id;
        @Column(name = "name")
        private String name;
        @Column(name = "province")
        private String province;
    }

Jpa操作类

public interface CityRepository extends JpaRepository<City,Long> {
    }

SpringBoot启动类

@SpringBootApplication
    @EnableTransactionManagement
    public class RunBoot {
        public static void main(String[] args) {
            SpringApplication.run(RunBoot.class,args);
        }
    }

具体读写分离配置

spring.shardingsphere.props.sql.show=true
    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/test1
    spring.shardingsphere.datasource.master.username=root
    spring.shardingsphere.datasource.master.password=351848327
    
    
    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/test2
    spring.shardingsphere.datasource.slave0.username=root
    spring.shardingsphere.datasource.slave0.password=351848327
    
    #master-slave
    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
    spring.shardingsphere.sharding.tables.city.key-generator.column=id
    spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

测试类和方法

@RunWith(SpringRunner.class)
    @SpringBootTest(classes = RunBoot.class)
    public class TestMasterSlave {
        @Resource
        CityRepository cityRepository;
        @Test
        public  void testMasterSlave(){
            City city = new City();
            city.setName("shanghai");
            city.setProvince("shanghai");
            cityRepository.save(city);
        }
    
        @Test
        public void findAll(){
            List<City> list = cityRepository.findAll();
            list.forEach(c->{
                System.out.println(c.getId()+" "+c.getName()+" "+c.getProvince());
            });
        }
    }

分别执行两个方法可以看到日志，实际操作写的时候是在master库，操作读的时候是在slave库 ![file][] ![file][file 1]

## sharding-jdbc读写分离强制某库执行操作 ##

这种方法主要是针对那些数据量大，数据无法及时同步到从库，可以直接从主库读取 具体配置

spring.shardingsphere.props.sql.show=true
    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/test1
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=351848327
    
    
    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/test2
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=351848327
    
    spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.zhao.hint.MyHint

具体Hint的规则

public class MyHint implements HintShardingAlgorithm<Long> {
    
    
        @Override
        public Collection<String> doSharding(Collection<String> targetNames, HintShardingValue<Long> hintShardingValue) {
            Collection<String> results = new ArrayList<>();
            for (String each :targetNames){
                for (Long value:hintShardingValue.getValues()){
                        if (each.endsWith(String.valueOf(value%2))){
                            results.add(each);
                        }
                }
            }
            return results;
        }
    }

Hint测试类

@RunWith(SpringRunner.class)
    @SpringBootTest(classes = RunBoot.class)
    public class TestHint {
        @Resource
        CityRepository cityRepository;
        @Test
        public void testHint(){
            HintManager hintManager =HintManager.getInstance();
            hintManager.setDatabaseShardingValue(0L);
            List<City> list = cityRepository.findAll();
            System.out.println("主库表大小"+list.size());
            hintManager.setDatabaseShardingValue(1L);
            List<City> list1 = cityRepository.findAll();
            System.out.println("从库表大小"+list1.size());
        }
    }

执行后可以看到两次查询分别从主库和从库中获取了数据 ![file][file 2]

## sharding-jdbc分库分表 ##

分库分表中要处理的包括id生成，分表列，广播表,字表跟随父表进行分表等配置 测试试题类

@Data
    @Table(name = "position")
    @Entity
    @ToString
    public class Position implements Serializable {
       @Id
       @Column(name = "Id")
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       private Long Id;
       @Column(name = "name")
       private String name;
       @Column(name = "salary")
       private  String salary;
       @Column(name = "city")
       private  String city;
    }
    
    
    @Data
    @Table(name = "position_detail")
    @Entity
    public class PositionDetail implements Serializable {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private  Long id;
        @Column(name = "pid")
        private  Long pid;
        @Column(name = "description")
        private  String description;
    }
    
    @Data
    @Table(name = "b_order")
    @Entity
    public class Border implements Serializable {
        @Id
        @Column(name = "id")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long id;
        @Column(name="is_del")
        private Boolean isDel;
        @Column(name = "company_id")
        private Integer companyId;
        @Column(name = "position_id")
        private long positionId;
        @Column(name = "user_id")
        private Integer userId;
        @Column(name = "publish_user_id")
        private Integer publishUserId;
        @Column(name = "resume_type")
        private Integer resumeType;
        @Column(name = "status")
        private String status;
        @Column(name = "create_time")
        private Date createTime;
        @Column(name = "operate_time")
        private Date operateTime;
        @Column(name = "work_year")
        private String workYear;
        @Column(name = "name")
        private  String name;
        @Column(name = "position_name")
        private String positionName;
        @Column(name = "resume_id")
        private Integer resumeId;
    }

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/test1
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=351848327
    
    
    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://rlocalhost:3306/test2
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=351848327
    
    
    #sharding
    spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
    
    spring.shardingsphere.sharding.tables.position.key-generator.column=id
    #spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
    
    spring.shardingsphere.sharding.tables.position.key-generator.type=zhao-sharding-key
    
    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}
    
    spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
    #spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
    
    spring.shardingsphere.sharding.tables.position_detail.key-generator.type=zhao-sharding-key
    
    
    #broadcast
    spring.shardingsphere.sharding.broadcast-tables=city
    spring.shardingsphere.sharding.tables.city.key-generator.column=id
    spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
    
    
    #sharding-database-table
    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}
    spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
    spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

自定义id生成器

@Slf4j
    public class MyShardingId implements ShardingKeyGenerator {
        private SnowflakeShardingKeyGenerator shardingKeyGenerator = new SnowflakeShardingKeyGenerator();
        @Override
        public Comparable<?> generateKey() {
            log.info("执行了自定义的id生成器");
    
            return shardingKeyGenerator.generateKey();
        }
    
        @Override
        public String getType() {
            return "zhao-sharding-key";
        }
    
        @Override
        public Properties getProperties() {
            return null;
        }
    
        @Override
        public void setProperties(Properties properties) {
    
        }
    }

执行的测试方法

@RunWith(SpringRunner.class)
    @SpringBootTest(classes = RunBoot.class)
    public class TestingShardingDatabase {
        @Resource
        PositionRepository positionRepository;
        @Resource
        PositionDetailRepository positionDetailRepository;
        @Resource
        CityRepository cityRepository;
        @Resource
        BorderRepository borderRepository;
    
        /**
         * 测试是否分库
         */
        @Test
        public void testAdd(){
            for (long i=1;i<=20;i++){
                Position position = new Position();
                //position.setId(i);
                position.setCity("shanghai");
                position.setName("zhao");
                position.setSalary("100.86");
                positionRepository.save(position);
            }
        }
    
        /**
         * 分库时主表和字表配置的规则一样，所以这里测试的是主表和字表在同库对应
         */
        @Test
        public void testAddDetail(){
            for (long i=1;i<=20;i++){
                Position position = new Position();
                //position.setId(i);
                position.setCity("shanghai");
                position.setName("zhao");
                position.setSalary("100.86");
                positionRepository.save(position);
                PositionDetail detail = new PositionDetail();
                detail.setPid(position.getId());
                detail.setDescription("This is message "+i);
                positionDetailRepository.save(detail);
            }
        }
    
        /**
         * 测试加载数据分库时走的那个库
         */
        @Test
        public void testLoadData(){
            Object object = positionRepository.findPositionLoadById(607972934107004929L);
            Object[] objects = (Object[]) object;
            System.out.println(objects[0]+" "+objects[1]);
        }
    
        /**
         * 广播表会写入多个库
         */
        @Test
        public void testBroadCast(){
            City city = new City();
            city.setName("shanghai");
            city.setProvince("shanghai");
            cityRepository.save(city);
        }
    
        /**
         * 测试分表时如何插入
         */
        @Test
        @Repeat(100)
        public void testShardingBorder(){
            Random random = new Random();
            int companyId = random.nextInt(10);
            Border border = new Border();
            border.setIsDel(false);
            border.setCompanyId(companyId);
            border.setCreateTime(new Date());
            border.setName("zhao");
            border.setWorkYear("2");
            borderRepository.save(border);
    
    
        }
    }

以上操作依赖的pom文件为

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
                <version>2.2.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
                <version>2.2.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <version>2.2.5.RELEASE</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.20</version>
            </dependency>
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>4.1.0</version>
            </dependency>
            
                
                
                
            
    
            
                
                
            
        </dependencies>

以上就是sharding-jdbc的一些基础使用的介绍。本文github地址[https://github.com/zhendiao/deme-code][https_github.com_zhendiao_deme-code]

> 欢迎搜索关注本人的公众号【微瞰技术】,以及总结的分类面试题[https://github.com/zhendiao/JavaInterview][https_github.com_zhendiao_JavaInterview]

![file][file 3]

[file]: /images/20221005/f68aec2248b24e698c0dd0df8a701f08.png
[file 1]: https://img-blog.csdnimg.cn/20210615210130923.png
[file 2]: /images/20221005/cbff5765055a41b68814f05b44d2e33d.png
[https_github.com_zhendiao_deme-code]: https://github.com/zhendiao/deme-code
[https_github.com_zhendiao_JavaInterview]: https://github.com/zhendiao/JavaInterview
[file 3]: /images/20221005/a67fe306cb134fa4acbbdac04f956b62.png