Sharding-Jdbc的分片算法及分表分库

上一篇 <<<Sharding-Jdbc实现读写分离
下一篇 >>>Sharding-Jdbc与MyCat区别

---

分片算法

分片算法目前需要业务方开发者自行实现，目前支持通过等号（doEqualSharding）、BETWEEN（doBetweenSharding）和IN（doInSharding）分片。
未来Sharding-JDBC也将会实现常用分片算法，如range，hash和tag等。

分片查询底层原理

和Mycat的查询原理一样
a.非分片关键字查询会搜索所有的分库分表，结果归并后按照sql语句排序返回，如果未设置排序，则按分库随机返回结果
b.分片关键字查询会直接定位到对应的分库，执行相应的sql语句返回结果。

SpringBoot整合Sharding-Jdbc方式

1.原生配置方式，自己需要实现接口。

a.分库算法类需要实现SingleKeyDatabaseShardingAlgorithm<T>接口
b.分表算法类需要实现SingleKeyTableShardingAlgorithm<T>接口

1.1代码水平单库拆分多表

• 核心：分表算法类需要实现SingleKeyTableShardingAlgorithm<T>接口

创建db_0数据库

CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

<dependencies>
    <!-- jpa -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.29</version>
    </dependency>
    <!-- 引入shardingjdbc依赖信息 -->
    <dependency>
        <groupId>io.shardingjdbc</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>2.0.3</version>
    </dependency>
    <dependency>
        <groupId>com.dangdang</groupId>
        <artifactId>sharding-jdbc-self-id-generator</artifactId>
        <version>1.4.2</version>
    </dependency>
</dependencies>
###数据库访问连接
spring:
  jdbc:
    db0:
      password: root
      className: com.mysql.jdbc.Driver
      #数据库名称由代码中植入
      url: jdbc:mysql://10.211.55.26:3306/%s?characterEncoding=utf-8
      username: root
  jpa:
    database: mysql
    show-sql: true
    hibernate:
      ## 自己建表
      ddl-auto: none
  application:
    name: sharding-jdbc-first


/**
 * 数据源相关配置信息
 */
@Configuration
public class DataSourceConfig {
   @Value("${spring.jdbc.db0.className}")
   private String className;
   @Value("${spring.jdbc.db0.url}")
   private String url;
   @Value("${spring.jdbc.db0.username}")
   private String username;
   @Value("${spring.jdbc.db0.password}")
   private String password;

   @Bean
   public IdGenerator getIdGenerator() {
      return new CommonSelfIdGenerator();
   }

   @Bean
   public DataSource getDataSource() {
      return buildDataSource();
   }

   private DataSource buildDataSource() {
      /**
       * 设置数据库，多个库组个往里面添加
       */
      Map<String, DataSource> dataSourceMap = new HashMap<>(2);
      dataSourceMap.put("ds_0", createDataSource("ds_0"));
      // dataSourceMap.put("ds_1", createDataSource("ds_1"));
      /**如果有多个数据库，则必须指定默认数据库*/
      DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
      /**数据分片的逻辑表(t_order)，对应水平拆分的真实存在的物理表(t_order_0和t_order_1)，同一类表的总称。*/
      TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
            .dataSourceRule(rule).build();
      /**分片策略*/
      ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
            .tableRules(Arrays.asList(orderTableRule))
            //根据userid分片字段
            .tableShardingStrategy(new TableShardingStrategy("user_id", new TableShardingAlgorithm())).build();
      // 创建数据源
      DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
      return dataSource;
   }

   private DataSource createDataSource(String dataSourceName) {
      // 使用druid连接数据库
      DruidDataSource druidDataSource = new DruidDataSource();
      druidDataSource.setDriverClassName(className);
      druidDataSource.setUrl(String.format(url, dataSourceName));
      druidDataSource.setUsername(username);
      druidDataSource.setPassword(password);
      return druidDataSource;
   }
}


public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

   /**
    * 同一个数据库中分表的策略
    * @param availableTargetNames 分表的集合 t_order_0 和t_order_1
    * @param shardingValue userid 分片字段值
    * @return
    */
   @Override
   public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
      for (String tableName : availableTargetNames) {
         //tableName = t_order_0
         // shardingValue.getValue()=2
         // t_order_0 2%2=0
         if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
            return tableName;
         }
      }
      throw new IllegalArgumentException();
   }

   @Override
   public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {

      return null;
   }

   @Override
   public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
         ShardingValue<Long> shardingValue) {

      return null;
   }

}

1.25.代码水平拆分为多库

• 分库算法类需要实现SingleKeyDatabaseShardingAlgorithm<T>接口

和单库多表相比的代码改动点：

/**
 * 数据源相关配置信息
 */
@Configuration
public class DataSourceConfig {
   @Value("${spring.jdbc.db0.className}")
   private String className;
   @Value("${spring.jdbc.db0.url}")
   private String url;
   @Value("${spring.jdbc.db0.username}")
   private String username;
   @Value("${spring.jdbc.db0.password}")
   private String password;

   @Bean
   public IdGenerator getIdGenerator() {
      return new CommonSelfIdGenerator();
   }

   @Bean
   public DataSource getDataSource() {
      return buildDataSource();
   }

   private DataSource buildDataSource() {
      /**
         * 设置数据库，多个库组个往里面添加
       */
      Map<String, DataSource> dataSourceMap = new HashMap<>(2);
      dataSourceMap.put("ds_0", createDataSource("ds_0"));
      dataSourceMap.put("ds_1", createDataSource("ds_1"));
      /**如果有多个数据库，则必须指定默认数据库*/
      DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
      /**数据分片的逻辑表(t_order),和物理表一致，则不需要实际物理表*/
      TableRule orderTableRule = TableRule.builder("t_order")
            .dataSourceRule(rule).build();
      /**分片策略*/
      ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
            .tableRules(Arrays.asList(orderTableRule))
            //根据userid分片字段
            .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DatabaseShardingAlgorithm())).build();
      // 创建数据源
      DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
      return dataSource;
   }

   private DataSource createDataSource(String dataSourceName) {
      // 使用druid连接数据库
      DruidDataSource druidDataSource = new DruidDataSource();
      druidDataSource.setDriverClassName(className);
      druidDataSource.setUrl(String.format(url, dataSourceName));
      druidDataSource.setUsername(username);
      druidDataSource.setPassword(password);
      return druidDataSource;
   }
}

public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
   @Override
   public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) {
      for (String database : databases) {
         System.out.println("database:" + database + ",----" + shardingValue.getValue());
         if (database.endsWith(shardingValue.getValue() % 2 + "")) {
            return database;
         }
      }
      throw new IllegalArgumentException();
   }
   @Override
   public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {

      return null;
   }
   @Override
   public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
         ShardingValue<Long> shardingValue) {

      return null;
   }
}

2.通过配置文件形式配置。

案例比如：t_order 拆分程t_order_0 t_order _1

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <!--必须要用M3版本，用M2版本会有问题-->
        <version>3.0.0.M3</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.29</version>
    </dependency>
</dependencies>

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
sharding:
  jdbc:
    ####ds1
    datasource:
      names: ds1
      ds1:
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.211.55.26:3306/ds_0?characterEncoding=utf-8
        username: root
    config:
      sharding:
        tables:
          #如果要对不同的表进行分片，则类似t_order写多个接口
          t_order:
            table-strategy:
              inline:
                #### 根据userid 进行分片
                sharding-column: user_id
                algorithm-expression: ds_0.t_order_$->{user_id % 2}
            actual-data-nodes: ds1.t_order_$->{0..1}
        props:
          sql:
            ### 开启分片日志
            show: true

---

推荐阅读：
<<<MySQL自带主从复制原理
<<<MyCat实现读写分离与动态数据源切换
<<<分表分库与分区的区别及拆分策略
<<<MyCat的分片查询原理
<<<Sharding-Jdbc实现读写分离
<<<Sharding-Jdbc与MyCat区别