Sharding-JDBC 分库分表入门

一、数据库结构

默认数据库：针对抽奖业务

默认数据库不参与分库分表，当访问的表不属于逻辑表时就会访问默认数据库

水平分库：针对用户参与活动的记录

考虑到该项目用户量较大，因此提前准备两个相同的库记录用户参与活动的信息，每个库又将活动记录表水平拆分成5个

lottery_01

lottery_02

二、sharding-jdbc引入

pom依赖

如果使用sharding-jdbc并且需要引入

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

		<dependency>
		            <groupId>com.alibaba</groupId>
		            <artifactId>druid</artifactId>
		            <version>1.1.21</version>
		 </dependency>
		 <!-- JAXB RI, EclipseLink MOXy -->
        <!--java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter解决  jaxb依赖包 -->
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-core</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>javax.activation</groupId>
            <artifactId>activation</artifactId>
            <version>1.1.1</version>
        </dependency>

yaml配置

思路：针对没有分库分表的数据源给它设置成默认的，分库分表所涉及到的表(逻辑表)给它们添加对应的节点范围和分库分表策略，当我们访问的表不属于逻辑表时，会访问默认数据源。

spring:
  shardingsphere:
    datasource:
      #不需要分表的默认数据源
      lottery:
          url: jdbc:mysql://localhost:3306/lottery?useUnicode=true
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
      #分库分表的第一个数据源
      lottery01:
        url: jdbc:mysql://localhost:3306/lottery_01?useUnicode=true
        username: root
        password: root
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      #分库分表的第二个数据源
      lottery02:
        url: jdbc:mysql://localhost:3306/lottery_02?useUnicode=true
        username: root
        password: root
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      names: lottery,lottery01,lottery02
    sharding:
      # 当访问的表不属于tables中定义的表时使用默认数据源
      default-data-source-name: lottery
      default-database-strategy:
        inline:
          # 默认数据源不分库也不分表
          algorithm-expression: lottery
          sharding-column: uId
      tables:
        # 对user_strategy_export(逻辑表)进行分表
        user_strategy_export:
          actual-data-nodes: lottery0${1..2}.user_strategy_export_00${1..4}
          key-generator:  #主键生成策略
            column: uId
            type: SNOWFLAKE
          table-strategy: #分表策略
            standard: #自定义精准分片算法
              sharding-column: uId
              precise-algorithm-class-name: cn.itedus.lottery.config.MyPreciseShardingAlgorithm
          database-strategy: #分库策略
            standard:
              sharding-column: uId
              precise-algorithm-class-name: cn.itedus.lottery.config.MyPreciseShardingAlgorithm
        user_take_activity: #只分库不分表
          actual-data-nodes: lottery0${1..2}.user_take_activity
          key-generator:
            column: uId
            type: SNOWFLAKE
          database-strategy:
            standard:
              sharding-column: uId
              precise-algorithm-class-name: cn.itedus.lottery.config.MyPreciseShardingAlgorithm
        user_take_activity_count: #只分库不分表
          actual-data-nodes: lottery0${1..2}.user_take_activity_count
          key-generator:
            column: uId
            type: SNOWFLAKE
          database-strategy:
            standard:
              sharding-column: uId
              precise-algorithm-class-name: cn.itedus.lottery.config.MyPreciseShardingAlgorithm

    props:
      sql:
        show: true

实体类

如果该实体类是针对分表的实体类，@tableName设置为逻辑表名，其余跟mybatis一样

自定义分片策略

本项目中使用uId作为分片单键，且uId是String类型，无法进行模运算，因此我们借鉴hashmap的扰动函数来实现分片策略

自定义精准分片策略，针对=和in操作，还有另外的分片策略暂时没用到

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(MyPreciseShardingAlgorithm.class);
	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
		LOGGER.info("collection:" + JSON.toJSONString(availableTargetNames) + ",preciseShardingValue:" + JSON.toJSONString(shardingValue));
		//availableTargetNames: user_strategy_export_001,user_strategy_export_002，user_strategy_export_003，user_strategy_export_004
		//shardingValue: {"logicTableName":"user_strategy_export","columnName":"uId","value":"wsad214213421"}
		{"logicTableName":"user_strategy_export","columnName":"order_id","value":396416249350848512}
		
		String value = shardingValue.getValue();
		int hashCode = value.hashCode();
		int index = (((value.hashCode() >>> 16) ^ value.hashCode()) & (availableTargetNames.size()-1))+1;
        for (String name : availableTargetNames) {
			//订单号取模加1 与 订单表t_order_1 和 t_order_2的尾号做比对，如相等，就直接返回t_order_1 或 t_order_2
			if (name.endsWith(String.valueOf(index))) {
				LOGGER.info("return name: " + name);
				return name;
			}
		}
		return null;
	}
}

三、测试

@Test
    public void test_user_take_insert(){
        UserTakeActivityCount count = new UserTakeActivityCount();
        count.setActivityid(100005l);
        count.setCreatetime(LocalDateTime.now());
        count.setUid("Uhdgkw7876890d");
        count.setLeftcount(2);
        count.setTotalcount(5);

        userTakeActivityCountService.save(count);
    }