数据库系列课程（08）-SpringBoot整合Sharding-Jdbc

1. 引言

先来理解几个概念：

• ​「LogicTable」​：数据分片的逻辑表，对于水平拆分的数据库(表)，同一类表的总称。例如订单信息表拆分为2张表,分别是​​t_order_0​​​、​​t_order_1​​​，他们的逻辑表名为​​t_order​​。
• ​「ActualTable」​：在分片的数据库中真实存在的物理表。即上个示例中的​​t_order_0​​​、​​t_order_1​​。
• ​「DataNode」​：数据分片的最小单元。由数据源名称和数据表组成，例：​​test_msg0.t_order_0​​。配置时默认各个分片数据库的表结构均相同，直接配置逻辑表和真实表对应关系即可。
• ​「ShardingColumn」​：分片字段。​用于将数据库(表)水平拆分的关键字段。SQL中如果无分片字段，将执行全路由，性能较差​。Sharding-JDBC支持多分片字段。
• ​「ShardingAlgorithm」​：分片算法。Sharding-JDBC通过分片算法将数据分片，支持通过等号、BETWEEN和IN分片。分片算法目前需要业务方开发者自行实现，可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法，如range，hash和tag等。

2. SpringBoot整合Sharding-Jdbc

SpringBoot整合Sharding-Jdbc分为两种方式

​方式一：原生配置方式，自己需要实现接口​

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

​方式二：通过配置文件形式配置​

2.1 案例分析

案例需求：t_order 需要拆分成t_order_0和 t_order _1

SpringBoot整合Sharding-Jdbc之前，先来定义数据库表结构：

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=InnoD

2.2 原生配置方式

1.添加maven依赖

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.0.3.RELEASE</version>
  <relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
</properties>

<dependencies>
  <!-- jpa -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
  </dependency>

  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
  </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>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.12</version>
  </dependency>

</dependencies>
<build>
  <plugins>
    <plugin>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-maven-plugin</artifactId>
    </plugin>
  </plugins>
</build>

2.application配置

###数据库访问连接
spring:
  jdbc:
    db0:
      className: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/%s?characterEncoding=utf-8
      username: root
      password: 123456
  jpa: 
    database: mysql 
    show-sql: true 
    hibernate: 
    ## 自己建表 
      ddl-auto: none 
  application: 
    name: sharding-jdbc-first

3.分表算法

​DataSourceConfig:​

// 数据源相关配置信息
@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() {
    // 1.设置分库映射
    Map<String, DataSource> dataSourceMap = new HashMap<>(2);
    dataSourceMap.put("ds_0", createDataSource("ds_0"));
    // dataSourceMap.put("ds_1", createDataSource("ds_1"));
    // 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库
    // 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，
    // 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
    DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
    // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
    TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
        .dataSourceRule(rule).build();
    // 3.具体的分库分表策略
    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;
  }
}

​TableShardingAlgorithm:​

public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

  // sql 中关键字 匹配符为 =的时候，表的路由函数
  public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
    for (String tableName : availableTargetNames) {
      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;
  }

}

4.分库算法

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

​DataSourceConfig:​

// 数据源相关配置信息
@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() {
    // 1.设置分库映射
    Map<String, DataSource> dataSourceMap = new HashMap<>(2);
    dataSourceMap.put("ds_0", createDataSource("ds_0"));
    dataSourceMap.put("ds_1", createDataSource("ds_1"));
    // 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库
    // 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，
    // 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
    DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
    // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
    TableRule orderTableRule = TableRule.builder("t_order").dataSourceRule(rule).build();
    // 3.具体的分库分表策略
    ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
        .tableRules(Arrays.asList(orderTableRule))
        .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;
  }
}

​DatabaseShardingAlgorithm:​

public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

  @Override
  public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) {
    for (String tableName : databases) {
      System.out.println("tableName:" + tableName + ",----" + shardingValue.getValue());
      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;
  }

}

2.3 配置文件方式

1.添加maven依赖：

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

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0.M3</version>
  </dependency>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.9</version>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
  </dependency>
</dependencies>

2.application配置：

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://127.0.0.1:3306/ds_1
        username: root
    config:
      sharding:
        tables:
          t_order:
            table-strategy:
              inline:
              #### 根据userid 进行分片
                sharding-column: user_id
                algorithm-expression: ds_1.t_order_$->{user_id % 2}
            actual-data-nodes: ds1.t_order_$->{0..1}
        props:
          sql: 
          ### 开启分片日志
            show: true