ShardingSphere实战示例以及分库分表的分布式事务问题

文章展示了ShardingSphere的JDBC和Proxy配置示例,包括数据源设置、分库分表策略以及SNOWFLAKE主键生成。同时,详细讨论了ShardingSphere支持的分布式事务类型,如XA和SeataBASE事务,以及它们的启用和配置方法。
摘要由CSDN通过智能技术生成

jdbc配置示例:

# 配置ds0 和ds1两个数据源
spring.shardingsphere.datasource.names=ds,ds0,ds1
#ds1 配置
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
#初始连接数
spring.shardingsphere.datasource.ds.initialSize=5
#最小空闲连接数
spring.shardingsphere.datasource.ds.minIdle=10
#最大连接数
spring.shardingsphere.datasource.ds.maxActive=30
spring.shardingsphere.datasource.ds.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root
#ds0 配置
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/test_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.initialSize=5
spring.shardingsphere.datasource.ds0.minIdle=10
spring.shardingsphere.datasource.ds0.maxActive=30
spring.shardingsphere.datasource.ds0.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
#ds1 配置
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/test_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.initialSize=5
spring.shardingsphere.datasource.ds1.minIdle=10
spring.shardingsphere.datasource.ds1.maxActive=30
spring.shardingsphere.datasource.ds1.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

# 对于没有做任何业务拆分的表,直接走本默认数据源即可
spring.shardingsphere.sharding.default-data-source-name=ds

spring.shardingsphere.sharding.default-database-strategy.hint.algorithm-class-name=com.tuling.tulingmall.sharding.OrderAllRangeHintAlgorithm
spring.shardingsphere.sharding.default-table-strategy.hint.algorithm-class-name=com.tuling.tulingmall.sharding.OrderAllRangeHintAlgorithm
# oms_order分片策略
# 节点 ds0.oms_order_0,ds0.oms_order_1,ds1.oms_order_0,ds1.oms_order_1
spring.shardingsphere.sharding.tables.oms_order.actual-data-nodes=ds$->{0..1}.oms_order_$->{0..1}
#分库策略
spring.shardingsphere.sharding.tables.oms_order.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.oms_order.database-strategy.inline.algorithm-expression=ds$->{id % 2}
# 分表策略
spring.shardingsphere.sharding.tables.oms_order.table-strategy.inline.sharding-column=id
# 注意下,对于除法,groovy会计算出浮点数,而不是整数。即 3/2=1.5,如果需要计算出整数 3.intdiv(2)=1
spring.shardingsphere.sharding.tables.oms_order.table-strategy.inline.algorithm-expression = oms_order_$->{((id+1) % 4).intdiv(2)}
# 复合分片算法
#spring.shardingsphere.sharding.tables.oms_order.table-strategy.complex.sharding-columns=id
#spring.shardingsphere.sharding.tables.oms_order.table-strategy.complex.algorithm-class-name = com.sharding.OrderComplexShardingAlgorithm
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.oms_order.key-generator.column=id
spring.shardingsphere.sharding.tables.oms_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.oms_order.key-generator.props.worker.id=123
# 使用自定义主键生成策略
#spring.shardingsphere.sharding.tables.oms_order.key-generator.type=CUSTOM
#spring.shardingsphere.sharding.tables.oms_order.key-generator.props.redis.prefix=order:id:prefix:

# 节点 ds0.oms_order_item_0,ds0.oms_order_item_1,ds1.oms_order_item_0,ds1.oms_order_item_1
spring.shardingsphere.sharding.tables.oms_order_item.actual-data-nodes=ds$->{0..1}.oms_order_item_$->{0..1}
# 分库策略 按order_id分片
spring.shardingsphere.sharding.tables.oms_order_item.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.oms_order_item.database-strategy.inline.algorithm-expression=ds$->{order_id % 2}
# 分表策略
spring.shardingsphere.sharding.tables.oms_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.oms_order_item.table-strategy.inline.algorithm-expression=oms_order_item_$->{((order_id+1) % 4).intdiv(2)}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.oms_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.oms_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.oms_order_item.key-generator.props.worker.id=123

# 配置绑定表,防止笛卡尔乘积
spring.shardingsphere.sharding.binding-tables[0]=oms_order,oms_order_item
# 打印SQL语句
spring.shardingsphere.props.sql.show=true

proxy配置示例:shardingProxy的config-sharding.yaml

schemaName: sharding_db
    
dataSources:
  ds:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
  ds0:
    url: jdbc:mysql://localhost:3306/test_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
  ds1:
    url: jdbc:mysql://localhost:3306/test_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root

shardingRule:
  tables:
    oms_order:
      actualDataNodes: ds$->{0..1}.oms_order_$->{0..1}
      databaseStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: ds$->{id % 2}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: oms_order_$->{((id+1) % 4).intdiv(2)}
      keyGenerator:
        column: id
        type: SNOWFLAKE
        props:
          worker.id: 123
    oms_order_item:
      actualDataNodes: ds$->{0..1}.oms_order_item_$->{0..1}
      databaseStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: ds$->{id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: oms_order_item_$->{((order_id+1) % 4).intdiv(2)}
      keyGenerator:
        column: id
        type: SNOWFLAKE
        props:
          worker.id: 123
  bindingTables: 
    - oms_order,oms_order_item
  defaultDataSourceName: ds

主键生成策略:
默认支持UUID和雪花算法,也可以自定义一个id生成策略。
配置spring.shardingsphere.sharding.tables.oms_order.key-generator.type=CUSTOM,然后在项目的Resource/META-INF.service/路径下新建一个properties文件,文件名以自定义的实现策略类的全路径命名,再将类的全路径copy到文件中,并在该类中定义type属性等于CUSTOM,代码片段如下:

public class OrderPreciseShardingAlgorithm implements PreciseShardingAlgorithm {
    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        // collection中保存所有真实节点
        collection.stream().forEach((item)->{
            log.info("actual node table:{}",item);
        });
        final Long columnValue = (Long)preciseShardingValue.getValue();
        final String columnName = preciseShardingValue.getColumnName();
        final String logicTableName = preciseShardingValue.getLogicTableName();

        //preciseShardingValue中保存分片逻辑,包含逻辑表,分片字段和查询的条件值
        log.info("logic table name:{},rout column:{}", logicTableName, columnName);
        log.info("column value:{}", columnValue);

        //采用自定义的ID生成器后,ID的前四位数字就是年份
        final String year = columnValue.toString().substring(0, 4);

        RedisOpsUtil redisOpsUtil = TulingmallOrderApplication.getBean("redisOpsUtil");
        final Integer shardingKey = redisOpsUtil.get("shardingYear:" + year, Integer.class);

        String actualTableName = logicTableName+"_"+year+"_"+(columnValue % shardingKey);
        //返回的结构就是具体的实际表。sharddingsphere会把逻辑表改成这个实际表
        return actualTableName;
    }
}

关于ShardingSphere的扩展点:

ShardingSphere基于SPI机制提供了非常多的扩展点,具体可以参见估官方文档,开发者手册部分。

ShardingAlgorithm扩展点就列出了ShardingSphere默认提供的多种分片策略:
InlineShardingAlgorithm:基于⾏表达式的分⽚算法
ModShardingAlgorithm:基于取模的分⽚算法
HashModShardingAlgorithm:基于哈希取模的分⽚算法
FixedIntervalShardingAlgorithm:基于固定时间范围的分⽚算法
MutableIntervalShardingAlgorithm:基于可变时间范围的分⽚算法
VolumeBasedRangeShardingAlgorithm:基于分⽚容量的范围分⽚算法
BoundaryBasedRangeShardingAlgorithm:基于分⽚边界的范围分⽚算法

分库分表的分布式事务

XA事务和BASE柔性事务
ShardingSphere基于SPI扩展分布式事务管理器。官方提供了两种实现方式, XA事务和Seata AT事务。

XA事务
需要引入maven依赖

org.apache.shardingsphere sharding-transaction-xa-core ${shardingsphere.version} 然后使用的话,需要在MyBatis中打开@EnableTransactionManagement注解。然后注入PlatformTransactionManager对象。

然后在事务开始的方法上打开@Transactional注解 和 @ShardingTransactionType(TransactionType.XA) 注解。

Seata AT事务
首先需要提前部署好Seata,采用nacos配置中心,把Seata的一大堆配置项(1.5.1版本之后优化成一个文件了)都提前导入到nacos中。

使用时,需要引入依赖以下依赖,并且把XA事务的依赖去掉。

org.apache.shardingsphere sharding-transaction-base-seata-at ${sharding-sphere.version} 然后需要在resource下添加seata.conf文件。文件内容:

client {
application.id = example ## 应用唯一id
transaction.service.group = my_test_tx_group ## 所属事务组
}
然后需要在每个数据分片建立undo_log表

CREATE TABLE IF NOT EXISTS undo_log
(
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT ‘increment id’,
branch_id BIGINT(20) NOT NULL COMMENT ‘branch transaction id’,
xid VARCHAR(100) NOT NULL COMMENT ‘global transaction id’,
context VARCHAR(128) NOT NULL COMMENT ‘undo_log context,such as serialization’,
rollback_info LONGBLOB NOT NULL COMMENT ‘rollback info’,
log_status INT(11) NOT NULL COMMENT ‘0:normal status,1:defense status’,
log_created DATETIME NOT NULL COMMENT ‘create datetime’,
log_modified DATETIME NOT NULL COMMENT ‘modify datetime’,
PRIMARY KEY (id),
UNIQUE KEY ux_undo_log (xid, branch_id)
) ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8 COMMENT =‘AT transaction mode undo table’;

五、SharingProxy的分布式事务
​ 对于ShardingProxy,由于他的功能并没有ShardingJDBC那么灵活,所以在ShardingProxy中使用分布式事务的方式就简单很多。

​ 对于XA事务:可以直接在config配置文件中将事务直接配置为XA。默认就会采用Atomikos来实现,当然也可以自行选择其他的实现框架,但是需要手动替换jar包。。而如果要使用seata的BASE柔性事务,就需要自行将jar包拷贝到lib目录下,比较麻烦,一般项目中很少用到。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值