天天看点
返回

Springboot基于sharding-jdbc3分库分表记录操作

Sharding-jdbc分库分表

Apache ShardingSphere(Incubator) 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。它通过关注不变，进而抓住事物本质。关系型数据库当今依然占有巨大市场，是各个公司核心业务的基石，未来也难于撼动，我们目前阶段更加关注在原有基础上的增量，而非颠覆。

目前所有的可用发布都是进入Apache孵化器之前的发布版本，Apache官方发布将从4.0.0版本开始。

一. 项目相关依赖

1.spring-boot版本基于1.5.19

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.19.RELEASE</version>
        <relativePath/> 
</parent>

2.sharding-jdbc基于3.0.0

<dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
</dependency>
<dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.0.0</version>
</dependency>

3.mybatis基于1.3.1

<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
</dependency>

二. 单库分表, 具体配置如下

sharding.jdbc.datasource.names=ds_0

sharding.jdbc.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_0.username=root
sharding.jdbc.datasource.ds_0.password=li123456
sharding.jdbc.datasource.ds_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_0.minimumIdle=10
sharding.jdbc.datasource.ds_0.connectionTestQuery=select 1

sharding.jdbc.config.sharding.tables.t_user_info.actual-data-nodes=ds_0.t_user_info_$->{0..4}
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.algorithm-expression=t_user_info_$->{user_id % 5}
sharding.jdbc.config.sharding.tables.t_user_info.key-generator-column-name=user_id

sharding.jdbc.config.sharding.binding-tables=t_user_info
sharding.jdbc.config.sharding.default-data-source-name=ds_0
sharding.jdbc.config.sharding.props.sql.show=true

三.分库分表, 具体配置如下：

#------------------------------------------------------------------------
#                           多库分片                                     #
#------------------------------------------------------------------------
sharding.jdbc.datasource.names=ds_0,ds_1,ds_2

sharding.jdbc.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_0.username=root
sharding.jdbc.datasource.ds_0.password=li123456
sharding.jdbc.datasource.ds_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_0.minimumIdle=10
sharding.jdbc.datasource.ds_0.connectionTestQuery=select 1

sharding.jdbc.datasource.ds_1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_1.jdbcUrl=jdbc:mysql://localhost:3306/ops_1?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_1.username=root
sharding.jdbc.datasource.ds_1.password=li123456
sharding.jdbc.datasource.ds_1.maximumPoolSize=50
sharding.jdbc.datasource.ds_1.minimumIdle=10
sharding.jdbc.datasource.ds_1.connectionTestQuery=select 1

sharding.jdbc.datasource.ds_2.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_2.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_2.jdbcUrl=jdbc:mysql://localhost:3306/ops_2?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_2.username=root
sharding.jdbc.datasource.ds_2.password=li123456
sharding.jdbc.datasource.ds_2.maximumPoolSize=50
sharding.jdbc.datasource.ds_2.minimumIdle=10
sharding.jdbc.datasource.ds_2.connectionTestQuery=select 1

sharding.jdbc.config.sharding.tables.t_user_info.database-strategy.inline.sharding-column=company_id
sharding.jdbc.config.sharding.tables.t_user_info.database-strategy.inline.algorithm-expression=ds_$->{company_id.longValue() % 3}
sharding.jdbc.config.sharding.tables.t_user_info.actual-data-nodes=ds_$->{0..2}.t_user_info_$->{0..4}
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.algorithm-expression=t_user_info_$->{user_id % 5}
sharding.jdbc.config.sharding.tables.t_user_info.key-generator-column-name=user_id

sharding.jdbc.config.sharding.binding-tables=t_user_info
sharding.jdbc.config.sharding.default-data-source-name=ds_0
sharding.jdbc.config.sharding.props.sql.show=true

四.分库分表+读写(主从, 可以配置一主多从)

#------------------------------------------------------------------------
#                        多库分片 + 读写                                  #
#------------------------------------------------------------------------
sharding.jdbc.datasource.names=ds_master_0,ds_master_0_slave_0,ds_master_1,ds_master_1_slave_0,ds_master_2,ds_master_2_slave_0

#master_0/slave_0/1/2
sharding.jdbc.datasource.ds_master_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_0.username=root
sharding.jdbc.datasource.ds_master_0.password=li123456
sharding.jdbc.datasource.ds_master_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_0.minimumIdle=10
sharding.jdbc.datasource.ds_master_0.connectionTestQuery=select 1

sharding.jdbc.datasource.ds_master_0_slave_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_0_slave_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0_slave_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_0_slave_0.username=root
sharding.jdbc.datasource.ds_master_0_slave_0.password=li123456
sharding.jdbc.datasource.ds_master_0_slave_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_0_slave_0.minimumIdle=10
sharding.jdbc.datasource.ds_master_0_slave_0.connectionTestQuery=select 1

#master_1/slave_0/1/2
sharding.jdbc.datasource.ds_master_1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1.jdbcUrl=jdbc:mysql://localhost:3306/ops_1?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_1.username=root
sharding.jdbc.datasource.ds_master_1.password=li123456
sharding.jdbc.datasource.ds_master_1.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_1.minimumIdle=10
sharding.jdbc.datasource.ds_master_1.connectionTestQuery=select 1

sharding.jdbc.datasource.ds_master_1_slave_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_1_slave_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1_slave_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_1?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_1_slave_0.username=root
sharding.jdbc.datasource.ds_master_1_slave_0.password=li123456
sharding.jdbc.datasource.ds_master_1_slave_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_1_slave_0.minimumIdle=10
sharding.jdbc.datasource.ds_master_1_slave_0.connectionTestQuery=select 1

#master_2/slave_0/1/2
sharding.jdbc.datasource.ds_master_2.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_2.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_2.jdbcUrl=jdbc:mysql://localhost:3306/ops_2?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_2.username=root
sharding.jdbc.datasource.ds_master_2.password=li123456
sharding.jdbc.datasource.ds_master_2.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_2.minimumIdle=10
sharding.jdbc.datasource.ds_master_2.connectionTestQuery=select 1

sharding.jdbc.datasource.ds_master_2_slave_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_master_2_slave_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_2_slave_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_2?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_master_2_slave_0.username=root
sharding.jdbc.datasource.ds_master_2_slave_0.password=li123456
sharding.jdbc.datasource.ds_master_2_slave_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_master_2_slave_0.minimumIdle=10
sharding.jdbc.datasource.ds_master_2_slave_0.connectionTestQuery=select 1

#sharding rule
sharding.jdbc.config.sharding.tables.t_user_info.database-strategy.inline.sharding-column=company_id
sharding.jdbc.config.sharding.tables.t_user_info.database-strategy.inline.algorithm-expression=ds_$->{company_id.longValue() % 3}
sharding.jdbc.config.sharding.tables.t_user_info.actual-data-nodes=ds_$->{0..2}.t_user_info_$->{0..4}
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.inline.algorithm-expression=t_user_info_$->{user_id % 5}
sharding.jdbc.config.sharding.tables.t_user_info.key-generator-column-name=user_id

sharding.jdbc.config.sharding.binding-tables=t_user_info
sharding.jdbc.config.sharding.default-data-source-name=ds_0
sharding.jdbc.config.sharding.props.sql.show=true

#master/slave
sharding.jdbc.config.sharding.master-slave-rules.ds_0.master-data-source-name=ds_master_0
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slave-data-source-names=ds_master_0_slave_0
sharding.jdbc.config.sharding.master-slave-rules.ds_1.master-data-source-name=ds_master_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.slave-data-source-names=ds_master_1_slave_0
sharding.jdbc.config.sharding.master-slave-rules.ds_2.master-data-source-name=ds_master_2
sharding.jdbc.config.sharding.master-slave-rules.ds_2.slave-data-source-names=ds_master_2_slave_0

五.自定义精确分片, 规则需要自定义

#------------------------------------------------------------------------
#                           单库自定义precise分片                          #
#------------------------------------------------------------------------
sharding.jdbc.datasource.names=ds_0

sharding.jdbc.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_0.username=root
sharding.jdbc.datasource.ds_0.password=li123456
sharding.jdbc.datasource.ds_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_0.minimumIdle=10
sharding.jdbc.datasource.ds_0.connectionTestQuery=select 1

sharding.jdbc.config.sharding.tables.t_user_info.actual-data-nodes=ds_0.t_user_info_$->{0..4}
sharding.jdbc.config.sharding.tables.t_user_info.key-generator-column-name=user_id
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.standard.sharding-column=user_id
sharding.jdbc.config.sharding.tables.t_user_info.table-strategy.standard.precise-algorithm-class-name=com.lance.sharding.config.UserPreciseShardingAlgorithm

sharding.jdbc.config.sharding.binding-tables=t_user_info
sharding.jdbc.config.sharding.default-data-source-name=ds_0
sharding.jdbc.config.sharding.props.sql.show=true
           
@Slf4j
public class UserPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String>availableTargetNames, PreciseShardingValue<Long>shardingValue) {
        log.info("ShardingTables: {}, preciseShardingValue: {}",  JSON.toJSONString(availableTargetNames), JSON.toJSONString(shardingValue));
        return availableTargetNames.stream()
                .filter(t -> t.endsWith(shardingValue.getValue() % availableTargetNames.size()+""))
                .findFirst()
                .orElse(null);
    }
}

六.单库多建分表(订单根据年份+订单ID%4 = T_ORDER_2019_0)

#------------------------------------------------------------------------
#                           单库多列分片                                  #
#------------------------------------------------------------------------
sharding.jdbc.datasource.names=ds_0

sharding.jdbc.datasource.ds_0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds_0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_0.jdbcUrl=jdbc:mysql://localhost:3306/ops_0?useSSL=false&useUnicode=true&characterEncoding=utf-8
sharding.jdbc.datasource.ds_0.username=root
sharding.jdbc.datasource.ds_0.password=li123456
sharding.jdbc.datasource.ds_0.maximumPoolSize=50
sharding.jdbc.datasource.ds_0.minimumIdle=10
sharding.jdbc.datasource.ds_0.connectionTestQuery=select 1

sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.complex.sharding-columns=create_time,order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.complex.algorithm-class-name=com.lance.sharding.config.OrderComplexShardingStrategy

sharding.jdbc.config.sharding.binding-tables=t_order
sharding.jdbc.config.sharding.default-data-source-name=ds_0
sharding.jdbc.config.sharding.props.sql.show=true
           
@Slf4j
public class OrderComplexShardingStrategy implements ComplexKeysShardingAlgorithm {
    private final String TIME = "create_time";
    private final String ORDER_ID = "order_id";

    @Override
    @SuppressWarnings("unchecked")
    public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> shardingValues) {
        log.info("Tables: {}, preciseValue: {}",  JSON.toJSONString(availableTargetNames), JSON.toJSONString(shardingValues));
        Collection<Timestamp> years = shardingValues.stream().filter(t->t.getColumnName().equalsIgnoreCase(TIME)).map(o-> ((ListShardingValue<Timestamp>)o).getValues()).findFirst().orElse(null);
        Collection<Long> orders = shardingValues.stream().filter(t->t.getColumnName().equalsIgnoreCase(ORDER_ID)).map(o-> ((ListShardingValue<Long>)o).getValues()).findFirst().orElse(null);

        if(availableTargetNames == null || availableTargetNames.isEmpty()
                ||years == null || years.isEmpty()
                || orders == null || orders.isEmpty()){
            return null;
        }

        Timestamp date = years.toArray(new Timestamp[]{})[0];
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);

        long order = orders.toArray(new Long[]{})[0];
        String tables = availableTargetNames.toArray(new String[]{})[0];

        if(log.isDebugEnabled()){
            log.debug("===>Table: {}", tables+"_"+calendar.get(Calendar.YEAR)+"_"+order % 4);
        }
        return Collections.singletonList(tables+"_"+calendar.get(Calendar.YEAR)+"_"+order % 4);
    }
}

批量测试(100* 500)：

@Test
    public void batchSave() {
        BigInteger companyId = BigInteger.valueOf(1000);
        List<CompletableFuture<Void>> futures = IntStream.range(0, 100).mapToObj(j->CompletableFuture.runAsync(()->{
                List<UserInfo> list = IntStream.range(0, 500)
                        .mapToObj(i -> createUser(companyId, i))
                        .collect(Collectors.toList());
                userInfoMapper.batchSave(list);
            }, service)
        ).collect(Collectors.toList());

        CompletableFuture.allOf(futures.toArray(new CompletableFuture[]{})).join();
    }
Springboot基于sharding-jdbc3分库分表记录操作

七.实例代码：

https://github.com/leelance/spring-boot-all/tree/master/spring-boot-sharding

spring-boot Sharding-JDBC mybatis CompletableFuture HiKari
上一篇: hive的自定义函数的区别
下一篇: 设计模式之中介者模式与命令模式

继续阅读