淘先锋技术网

首页 1 2 3 4 5 6 7

sharding sphere4.0.1——简单实现分表分库，读写分离，xa分布式事务

一 简介官网

始于当当开源的shardingjdbc，功能较单一，2018进入Apache基金会孵化器。目前最新apache版本ShardingSphere是4.0.1。ShardingSphere一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成.常用核心功能有分表分库（行表达式）与读写分离（基于SQL语句类型路由DQL/DML），分布式事务（xa事务，默认atomicos，如果能接受2pc的性能损失，分分钟解决分布式事务）

二 依赖

        <!--分表分库与读写分离-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.1</version>
        </dependency>

        <!--XA分布式事务依赖-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>4.0.1</version>
        </dependency>

三  配置文件

spring:
  shardingsphere:
    datasource:
      names: ds0,ds0slave0,ds0slave1,ds1,ds1slave0,ds1slave1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3306/ds0
        username: root
        password: 123456
      ds0slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3301/ds0
        username: root
        password: 123456
      ds0slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3302/ds0
        username: root
        password: 123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3306/ds1
        username: root
        password: 123456
      ds1slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3301/ds1
        username: root
        password: 123456
      ds1slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassCame: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.203.132:3302/ds1
        username: root
        password: 123456
    sharding:
      tables:
        t_order:
          actual-data-nodes: ms_ds$->{0..1}.t_order$->{0..1} #行表达式 ds1.t_order1,$->{}是为了区分spring 的${}
          ## 指定分库规则
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ms_ds$->{user_id % 2}
          ## 指定分表规则
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order$->{order_id % 2}

        t_order_item:
          actual-data-nodes: ms_ds$->{0..1}.t_order_item$->{0..1}
          ## 通过hint方式自定义分库规则,这里需要根据userid路由，但order_item表没有该路由键，固自定义hint,然后绑定主表与子表，避免关联查询导致的全数据源路由
          database-strategy:
            hint:
              algorithmClassName: com.construn.vehicle.user.core.HintSharding
          ## 指定分表规则
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_item$->{order_id % 2}
          ## 雪花算法主键生成
          key-generator:
            column: item_id
            type: SNOWFLAKE

      ## 绑定主表与子表，避免关联查询导致的全数据源路由
      binding-tables: t_order,t_order_item

      ## 配置广播表：以广播的形式增删改所有库（如果只涉及查询的话可以不配置，轮询查询）
      broadcast-tables: t_config
    
   
    ## 读写分离
      masterSlaveRules:
        ms_ds0:
          masterDataSourceName: ds0
          slaveDataSourceNames:
          - ds0slave0
          - ds0slave1
          loadBalanceAlgorithmType: ROUND_ROBIN
        ms_ds1:
          masterDataSourceName: ds1
          slaveDataSourceNames:
          - ds1slave0
          - ds1slave1
          loadBalanceAlgorithmType: ROUND_ROBIN
    ## 打印sql，显示当前sql路由的数据库
    props:
      sql:
        show: true

四/测试。参考https://blog.csdn.net/u011212394/article/details/101101633

建表脚本

create table t_config
(
  id               int                                 not null
    primary key,
  remark           varchar(50)                         null,
  create_time      timestamp default CURRENT_TIMESTAMP not null,
  last_modify_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
);

create table t_order0
(
  order_id         int                                 not null
    primary key,
  user_id          int                                 not null,
  config_id        int                                 not null,
  remark           varchar(50)                         null,
  create_time      datetime default CURRENT_TIMESTAMP  not null,
  last_modify_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
);

create table t_order1
(
  order_id         int                                 not null
    primary key,
  user_id          int                                 not null,
  config_id        int                                 not null,
  remark           varchar(50)                         null,
  create_time      timestamp default CURRENT_TIMESTAMP not null,
  last_modify_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
);

create table t_order_item0
(
  item_id          bigint auto_increment
    primary key,
  order_id         bigint                              not null,
  remark           varchar(50)                         null,
  create_time      timestamp default CURRENT_TIMESTAMP not null,
  last_modify_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
);

create table t_order_item1
(
  item_id          bigint auto_increment
    primary key,
  order_id         bigint                              not null,
  remark           varchar(50)                         null,
  create_time      timestamp default CURRENT_TIMESTAMP not null,
  last_modify_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
);

Config.java

@Setter
@Getter
@ToString
public class Config {
 
    private Integer id;
 
    private String remark;
 
    private Date createTime;
 
    private Date lastModifyTime;
}

Order.java

@Getter
@Setter
@ToString
public class Order {
 
    private Integer orderId;
 
    private Integer userId;
 
    private Integer configId;
 
    private String remark;
 
    private Date createTime;
 
    private Date lastModifyTime;
}

OrderItem.java

@Getter
@Setter
@ToString
public class OrderItem {
    
    private Long itemId;
 
    private Integer orderId;
 
    private String remark;
 
    private Date createTime;
 
    private Date lastModifyTime;
}

ConfigMapper.java

@Mapper
public interface ConfigMapper {
    @Insert("insert into t_config(id,remark) values(#{id},#{remark})")
    Integer save(Config config);
 
    @Select("select * from t_config  where id = #{id}")
    Config selectById(Integer id);
}

OrderMapper.java

@Mapper
public interface OrderMapper {
 
    @Insert("insert into t_order(order_id,user_id,config_id,remark) values(#{orderId},#{userId},#{configId},#{remark})")
    Integer save(Order order);
 
    @Select("select order_id orderId, user_id userId, config_id configId, remark from t_order  where user_id = #{userId}")
    Order selectByUserId(Integer userId);
 
    @Select("select o.order_id orderId, o.user_id userId, o.config_id configId, o.remark from " +
            "t_order o inner join t_order_item i on o.order_id = i.order_id " +
            "where o.user_id =#{userId} and o.order_id =#{orderId}")
    List<Order> selectOrderJoinOrderItem(Integer userId, Integer orderId);
 
    @Select("select  o.order_id orderId, o.user_id userId, o.config_id configId, o.remark " +
            "from t_order o inner join t_config c on o.config_id = c.id " +
            "where o.user_id =#{userId} and o.order_id =#{orderId}")
    List<Order> selectOrderJoinConfig(Integer userId, Integer orderId);
}

OrderItemMapper.java

@Mapper
public interface OrderItemMapper {
    @Insert("insert into t_order_item(order_id,remark) values(#{orderId},#{remark})")
    Integer save(OrderItem orderItem);
}

OrderService.java

public interface OrderService {
    Integer saveOrder(Order order);
 
    Integer saveOrderItem(OrderItem orderItem, Integer userId);
 
    Order selectByUserId(Integer userId);
 
    List<Order> selectOrderJoinOrderItem(Integer userId, Integer orderId);
 
    List<Order> selectOrderJoinOrderItemNoSharding(Integer userId, Integer orderId);
 
    List<Order> selectOrderJoinConfig(Integer userId, Integer orderId);
 
    Integer saveConfig(Config config);
 
    Config selectConfig(Integer id);
}

OrderServiceImpl

@Service
public class OrderServiceImpl implements OrderService {
 
    @Autowired
    private OrderMapper orderMapper;
 
    @Autowired
    private OrderItemMapper orderItemMapper;
 
    @Autowired
    private ConfigMapper configMapper;
 
    @Override
    public Integer saveOrder(Order order) {
        return orderMapper.save(order);
    }
 
    @Override
    public Integer saveOrderItem(OrderItem orderItem, Integer userId) {
        try (HintManager hintManager = HintManager.getInstance()) {
            hintManager.addDatabaseShardingValue("t_order_item", userId);
            return orderItemMapper.save(orderItem);
        }
    }
 
    @Override
    public Order selectByUserId(Integer userId) {
        return orderMapper.selectByUserId(userId);
    }
 
    @Override
    public List<Order> selectOrderJoinOrderItem(Integer userId, Integer orderId) {
        try (HintManager hintManager = HintManager.getInstance()) {
            hintManager.addDatabaseShardingValue("t_order_item", userId);
            return orderMapper.selectOrderJoinOrderItem(userId, orderId);
        }
    }
 
    @Override
    public List<Order> selectOrderJoinOrderItemNoSharding(Integer userId, Integer orderId) {
        return orderMapper.selectOrderJoinOrderItem(userId, orderId);
    }
 
    @Override
    public List<Order> selectOrderJoinConfig(Integer userId, Integer orderId) {
        return orderMapper.selectOrderJoinConfig(userId, orderId);
    }
 
    @Override
    public Integer saveConfig(Config config) {
        return configMapper.save(config);
    }
 
    @Override
    public Config selectConfig(Integer id) {
        return configMapper.selectById(id);
    }
}

 

hint自定义分库路由

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

单元测试


@RunWith(SpringRunner.class)
@SpringBootTest
public class VehicleUserApplicationTests {


        @Autowired
        private OrderService orderService;

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

        public void testSaveOrder() {
            for (int i = 0; i < 10; i++) {
                Integer orderId = 1000 + i;
                Integer userId = 10 + i;

                Order o = new Order();
                o.setOrderId(orderId);
                o.setUserId(userId);
                o.setConfigId(i);
                o.setRemark("save.order");
                orderService.saveOrder(o);
                if (orderId>1005) {
                  int a=1/0;
                }
                OrderItem oi = new OrderItem();
                oi.setOrderId(orderId);
                oi.setRemark("save.orderItem");
                orderService.saveOrderItem(oi, userId);
            }
        }

        /**
         * 根据分片键查询
         *
         * @param
         * @return void
         * @author hujy
         * @date 2019-09-20 11:26
         */
        @Test
        public void testSelectByUserId() {
            Integer userId = 12;
            HintManager.getInstance().setMasterRouteOnly();
            Order o1 = orderService.selectByUserId(userId);
            System.out.println(o1);

            userId = 17;
            Order o2 = orderService.selectByUserId(userId);
            System.out.println(o2);

        }

        /**
         * 与分片子表关联
         *
         * @param
         * @return void
         * @author hujy
         * @date 2019-09-20 11:24
         */
        @Test
        public void testSelectOrderJoinOrderItem() {
            // 指定了子表分片规则
            List<Order> o1 = orderService.selectOrderJoinOrderItem(12, 1002);
            System.out.println(o1);
            // 未指定子表分片规则：导致子表的全路由
            List<Order> o2 = orderService.selectOrderJoinOrderItemNoSharding(12, 1002);
            System.out.println(o2);
        }

        /**
         * 与广播表关联
         *
         * @param
         * @return void
         * @author hujy
         * @date 2019-09-20 11:24
         */
        @Test
        public void testSelectOrderJoinConfig() {
            List<Order> o1 = orderService.selectOrderJoinConfig(12, 1002);
            System.out.println(o1);
            List<Order> o2 = orderService.selectOrderJoinConfig(17, 1007);
            System.out.println(o2);
        }

        /**
         * 广播表保存
         * 对所有数据源进行广播
         *
         * @param
         * @return void
         * @author hujy
         * @date 2019-09-20 11:23
         */
        @Test
        public void testSaveConfig() {
            for (int i = 0; i < 10; i++) {
                Config config = new Config();
                config.setId(i);
                config.setRemark("config " + i);
                orderService.saveConfig(config);
                System.out.println();
            }
        }

        /**
         * 广播表查询
         * 随机选择数据源
         *
         * @param
         * @return void
         * @author hujy
         * @date 2019-09-20 11:23
         */
        @Test
        public void testSelectConfig() {
            Config config1 = orderService.selectConfig(5);
            System.out.println(config1);

            Config config2 = orderService.selectConfig(7);
            System.out.println(config2);
        }

    @Test
    public void testUpdateConfig() {
        for (int i = 0; i < 10; i++) {
            Config config = new Config();
            config.setId(i);
            config.setRemark("configNEW " + i);
            orderService.updateConfig(config);
        }
    }

    }

五/其它注意事项

  1. 读写分离配置也可以通过java配置方式，详解官方文档
  2. 读写分离还是要依赖mysql的bin-log日志主从复制实现，主从复制有延迟，有时候必须主库读取，可以读数据前加入代码HintManager.getInstance().setMasterRouteOnly();强制路由到主库。主从复制数据源名貌似不能使用下划线，如ds0_slave0报错

  3. shardingjdbc实现xa分布式事务只需要加入依赖（也支持seata等），增加注解

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