shardingJDBC分表实战

1 添加meven依赖

<!-- 数据库连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!-- sharding-jdbc 数据库分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!-- 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

2 定义用户实体

@Data
@TableName("user")
public class User {

    @TableId()
    private Long id;
    private String name;
    private Integer age;
    private Date birthday;
    private String cmd;
    private String siteId;
}

@Data
@TableName("user_test")
public class UserTest {

    @TableId()
    private Long id;
    private String name;
    private Integer age;
    private Date birthday;
    private String cmd;
    private String siteId;
}

3 配置文件

server:
  port: 8488
# mybatis-plus 配置
mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 声明一个数据库(虚拟的)
spring:
  shardingsphere:
    datasource:
      names: db1
# 声明虚拟数据库对应的连接,驱动,用户名,密码,连接池等信息
      db1:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
        username: root
        password: rootroot
        type: com.alibaba.druid.pool.DruidDataSource
    sharding:
      tables:
        user_test:
          actual-data-nodes: db1.user_test_$->{0..9}
          key-generator:
            column: id
            type: SNOWFLAKE
          table-strategy:
#            inline:
#              sharding-column: site_id
#              algorithm-expression: user_test_$->{site_id % 10}
            standard:
              sharding-column: site_id
              precise-algorithm-class-name: com.example.demo.config.TableShardingAlgorithm
        # 定义用户表
        user:
          actual-data-nodes: db1.user_$->{0..9}
          # 定义id字段使用雪花算法
          key-generator:
            column: id
            type: SNOWFLAKE
          table-strategy:
            standard:
              # 分表字段
              sharding-column: site_id
              # 自定义分表逻辑
              precise-algorithm-class-name: com.example.demo.config.TableShardingAlgorithm
    props:
      sql:
        show: true

4 定义分片逻辑类

Component
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> preciseShardingValue) {
        // 获取站点编号最后一位
        String value = Integer.valueOf(preciseShardingValue.getValue().substring(9)) % 10 + "";
        for (String tableName : tableNames) {
            if (tableName.endsWith(value)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }
}

5 测试

@Test
    void contextLoads() {
        List<UserTest> userTestList = Lists.newArrayList();
        for (int i = 10; i < 20; i++) {
            UserTest userTest = new UserTest();
            userTest.setAge(i);
            userTest.setName("张三");
            userTest.setBirthday(new Date());
            userTest.setCmd("张三今年" + i + "岁");
            userTest.setSiteId("EDOG1000" + i);
            userTestList.add(userTest);
        }
        userTestService.saveBatch(userTestList);
    }

6 shardingJDBC分片算法笔记

6.1 分类：

精确分片算法，对应PreciseShardingAlgorithm，需要配合StandardShardingStrategy使用
范围分片算法，对应RangeShardingAlgorithm，需要配合StandardShardingStrategy使用
复合分片算法，对应ComplexKeysShardingAlgorithm，需要配合ComplexShardingStrategy使用
Hint分片算法，对应HintShardingAlgorithm，需要配合HintShardingStrategy使用。

6.2 分片策略
标准分片策略，StandardShardingStrategy只支持单分片键，
        PreciseShardingAlgorithm是必选的，用于处理=和IN的分片
        RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片
复合分片策略：对应ComplexShardingStrategy，支持多分片键，提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持
行表达式分片策略：对应InlineShardingStrategy，使用Groovy的表达式，支持=和IN的分片操作，支持单分片键
Hint分片策略：对应HintShardingStrategy
不分片策略，对应NoneShardingStrategy

6.3 分片规则

包含数据源配置、表配置、绑定表配置以及读写分离配置等

6.4 分片策略配置

数据源分片策略，DatabaseShardingStrategy
表分片策略，TableShardingStrategy

根据分片键的不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)。 不携带分片键的SQL则采用广播路由。
6.5 内核剖析

解析引擎-> 路由引擎-> 改写引擎-> 执行引擎->归并引擎