使用 sharding-jdbc 进行分表

由于单表所支撑的数据量有限，打算使用 sharding jdbc 进行分库分表。由于之前阅读过相关的博客，考虑使用基因法进行分表。

此时 shardingsphere jdbc 的发布版本已更新到 5.2.0，本人一直的习惯，中间件用最新的版本。网上的示例有部分是基于 4.x 版本的，所以不能照搬，需要从源码的文档中获取有用信息。

基于 springboot + mybatis，使用 sharding-jdbc。

引入 maven 依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.0</version>
</dependency>

本示例中分了 2 库 2 表：

ds-0: [ msg_in_0, msg_in_1 ]

ds-1: [ msg_in_0, msg_in_1 ]

表结构如下：

CREATE TABLE `msg_in_0` (
  `message_id` bigint NOT NULL COMMENT '消息id',
  `src_unid` varchar(32) NOT NULL,
  `dst_unid` varchar(32) DEFAULT NULL,
  `payload` varchar(64) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `ack_status` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`message_id`)
) 

CREATE TABLE `msg_in_1` (
  `message_id` bigint NOT NULL COMMENT '消息id',
  `src_unid` varchar(32) NOT NULL,
  `dst_unid` varchar(32) DEFAULT NULL,
  `payload` varchar(64) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `ack_status` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`message_id`)
) 

在 2 个库中分别创建表 msg_in_0 和表 msg_in_1。

分表策略是基于 src_unid 进行分表，取 src_unid 的哈希值对总表数 4 取模，获得基因，并附加在 message_id 的后面。

配置文件如下：

spring:
  shardingsphere:
    datasource:
      names: ds-0, ds-1
      ds-0:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://172.16.101.93:3306/ds-0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: root
      ds-1:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://172.16.101.93:3306/ds-1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: root
    mode:
      repository:
        type: JDBC
      type: Standalone
    props:
      sql-show: true
    rules:
      sharding:
        tables:
          in_msg:
            actual-data-nodes: ds-$->{0..1}.msg_in_$->{0..1}
            database-strategy:
              standard:
                sharding-algorithm-name: msgId2Db
                sharding-column: message_id
            tableStrategy:
              standard:
                sharding-algorithm-name: msgId2Table
                sharding-column: message_id
          in_msg_send_view:
            actual-data-nodes: ds-$->{0..1}.msg_in_$->{0..1}
            database-strategy:
              standard:
                sharding-algorithm-name: userId2Db
                sharding-column: src_unid
            tableStrategy:
              standard:
                sharding-algorithm-name: userId2Table
                sharding-column: src_unid
        sharding-algorithms:
          msgId2Db:
            props:
              algorithmClassName: com.sharding.MsgId2Db
              strategy: STANDARD
            type: CLASS_BASED
          msgId2Table:
            props:
              algorithmClassName: com.sharding.MsgId2Table
              strategy: STANDARD
            type: CLASS_BASED
          userId2Db:
            props:
              algorithmClassName: com.sharding.UserId2Db
              strategy: STANDARD
            type: CLASS_BASED
          userId2Table:
            props:
              algorithmClassName: com.sharding.UserId2Table
              strategy: STANDARD
            type: CLASS_BASED

这里没有使用内置的 sharding 算法，懒得去折腾，直接手写了。

public class MsgId2Db implements StandardShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        long val = preciseShardingValue.getValue();
        long remain = val % shards;

        long dbIndex, tableIndex;
        if (remain == 0) {
            dbIndex = dbLen - 1;
            tableIndex = tableLen - 1;
        } else {
            dbIndex = (remain - 1) / tableLen;
            tableIndex = (remain - 1) % tableLen;
        }
        return dbName + dbIndex;
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        return null;
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

public class MsgId2Table implements StandardShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        long val = preciseShardingValue.getValue().longValue();
        long remain = val % shards;

        long dbIndex, tableIndex;
        if (remain == 0) {
            dbIndex = dbLen - 1;
            tableIndex = tableLen - 1;
        } else {
            dbIndex = (remain - 1) / tableLen;
            tableIndex = (remain - 1) % tableLen;
        }
        return tableName + tableIndex;
    }
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        return null;
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

public class UserId2Db implements StandardShardingAlgorithm<String> {
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        long val = Math.abs(preciseShardingValue.getValue().hashCode());
        long remain = val % shards;

        long dbIndex, tableIndex;
        if (remain == 0) {
            dbIndex = dbLen - 1;
            tableIndex = tableLen - 1;
        } else {
            dbIndex = (remain - 1) / tableLen;
            tableIndex = (remain - 1) % tableLen;
        }
        return dbName + dbIndex;
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

public class UserId2Table implements StandardShardingAlgorithm<String> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        long val = Math.abs(preciseShardingValue.getValue().hashCode());
        long remain = val % shards;

        long dbIndex, tableIndex;
        if (remain == 0) {
            dbIndex = dbLen - 1;
            tableIndex = tableLen - 1;
        } else {
            dbIndex = (remain - 1) / tableLen;
            tableIndex = (remain - 1) % tableLen;
        }
        return tableName + tableIndex;
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

public class DbConf {
    public static final int dbLen = 2;
    public static final int tableLen = 2;
    public static final int shards = dbLen * tableLen;
    public static String dbName = "ds-";
    public static String tableName = "msg_in_";
}