SpringBoot+Sharding-JDBC 自定义分片规则+自定义主键生成策略实现水平分表

Sharding-JDBC 实现分表

通过配置的形式将数据库按照规则进行分片等操作

Hello World

通过 Sharding-JDBC 实现水平分表: 数据库内用user_1 和 user_2 通过主键进行水平拆分,id为奇数放入user_1表,id为偶数放入user_2表

1 创建数据库和表

create database sharding character set = utf8;
use sharding;

create table user_1(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 birthday datetime,
 cmd varchar(200)
);

create table user_2(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 birthday datetime,
 cmd varchar(200)
);

2 创建SpringBoot工程

Sharding-jdbc 为我们提供了整合springBoot的启动器,配置起来非常简单

<!-- 数据库连接驱动 -->
<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>
<!-- reids 依赖(这里主要用于生成主键) -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

3配置application.properties文件

##基本配置(正常操作)
server.port=8998

# mybatis-plus 配置
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.mt.bean
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

spring.redis.host=120.25.227.88
spring.redis.password=123456


#sharding-jdbc的配置 ps:官网有详细的配置文件介绍:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/

#声明一个数据库(虚拟的)
spring.shardingsphere.datasource.names=db1

#声明虚拟数据库对应的连接,驱动,用户名,密码,连接池等信息
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource


#声明 表存放在对应的数据库 $->{0..1} 就是行内表达式
#这里的意思是db1库内有user虚拟表指向 user_1和user_2
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db1.user_$->{1..2}

#设置主键字段
spring.shardingsphere.sharding.tables.user.key-generator.column=id
# 设置主键生成策略 可选内置的 SNOWFLAKE(雪花算法)/UUID 
#也可以自定义(实现ShardingKeyGenerator,并配置META-INF/services/org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator) 
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

#设置 根据哪个字段进行分片
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
#分片规则奇数存入user_1  偶数存入user_2
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2 != 0 ? 1:2}

spring.shardingsphere.props.sql.show=true

4编写对应的对象

bean:

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

    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private Date birthday;
    private String cmd;
}

mapper:

public interface UserMapper extends BaseMapper<User> {}

测试类:

@SpringBootTest
class ShardingSphereApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        User user1 = new User();
        user1.setAge(10);
        user1.setName("张三");
        user1.setBirthday(new Date());
        user1.setCmd("张三今年10岁");
        
        User user2 = new User();
        user2.setAge(20);
        user2.setName("李四");
        user2.setBirthday( new Date() );
        user2.setCmd("李四今年20岁");
        
        userMapper.insert( user2 );
    }

}

经过测试用户张三被添加到 user_1,李四则被添加到 user_2

自定义主键生成策略

Sharding-jdbc为我们提供了2个默认的生成策略:

• SNOWFLAKE: 雪花算法(对应数据库 bigint类型 和java的Long类型)
• UUID: uuid生成策略(varchar和String类型)

仅仅这2种并不能满足我们的需求,因此sharding-jdbc为我们提供了ShardingKeyGenerator 接口来允许我们自定义主键生成策略

实现: 这里通过redis生成自增的主键

1 实现 ShardingKeyGenerator 接口

@Component
@Slf4j
public class KeyGenerator implements ShardingKeyGenerator, ApplicationContextAware {
    @Getter
    @Setter
    private Properties properties;

    //必须设置为静态,否则为null
    public static RedisTemplate redisTemplate;


    @Override
    public Comparable<?> generateKey() {
        ValueOperations valueOp = redisTemplate.opsForValue();
        return  valueOp.increment("id");
    }


    //设置在yaml内的名字
    @Override
    public String getType() {
        return "auto_increment";
    }


    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        redisTemplate = applicationContext.getBean(StringRedisTemplate.class);
    }
}

2 配置刚刚创建的类

我们需要在resource下创建文件

META-INF\services\org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

并配置我们刚刚编写的主机生成器

com.mt.config.Sharding.KeyGenerator

3 使用

只需要在配置文件内指定刚刚的生成器名即可

spring.shardingsphere.sharding.tables.user.key-generator.type=auto_increment

自定义表分片策略

上面演示的分表策略是sharding-jdbc为我们提供的 inline 的分片规则,可以通过编写行表达式实现简单的表分片策略,例如根据id取模,根据性别去分类。但是对于一些较为复杂的分区策略,行表达式可能无法满足我们的要求,因此我们需要自定义表分片策略

案例: 根据当前的年月分,将数据插入不同的表中,比如数据库内有order_202001,order_202002等我们需要通过订单生成的日期动态的存入不同的表中:

1 创建数据库表

create table `order_202001`(
 id int primary key auto_increment,
 date datetime,
 price decimal(10,2),
 cmd varchar(200)
);

create table `order_202002`(
 id int primary key auto_increment,
 date datetime,
 price decimal(10,2),
 cmd varchar(200)
);

create table `order_202003`(
 id int primary key auto_increment,
 date datetime,
 price decimal(10,2),
 cmd varchar(200)
);

2 创建entity对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("`order`")
public class Order {

    @TableId(type = IdType.AUTO)
    private Integer id;
    private Date date;
    private BigDecimal price;
    private String cmd;
}

3 创建分片逻辑类

@Component
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        
		//preciseShardingValue就是当前插入的字段值
        //collection 内就是所有的逻辑表
        //获取字段值
        Date time = preciseShardingValue.getValue();

        if(time == null){
            throw new UnsupportedOperationException("prec is null");
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        String year =sdf.format(time);

        for (String tableName : collection) {//循环表名已确定使用哪张表
            String name = tableName.substring(tableName.length() - 6);
            if(year.equals(name)){
                return tableName;//返回要插入的逻辑表
            }
        }
        return null;
    }

}

#声明虚拟表
spring.shardingsphere.sharding.tables.order.actual-data-nodes=db1.order_$->{2000..2099}0$->{1..9},db1.order_$->{2000..2099}1$->{0..2}
#声明表内的主键
spring.shardingsphere.sharding.tables.order.key-generator.column=id
#声明主键生成策略
spring.shardingsphere.sharding.tables.order.key-generator.type=order_auto_increment

#声明根据哪个字段进行分片
spring.shardingsphere.sharding.tables.order.table-strategy.standard.sharding-column=date
#自定义分片规则类
spring.shardingsphere.sharding.tables.order.table-strategy.standard.precise-algorithm-class-name=com.mt.config.Sharding.OrderTableShardingAlgorithm

4 通过mybatis-plus 进行添加操作

@RestController
@RequestMapping("order")
public class OrderController {

    @Autowired
    private OrderMapper orderMapper;

    @GetMapping("add")
    public String add(Order order){
        orderMapper.insert(order);
        return "success";
    }
}