sharding-jdbc使用案例自定义主键生成策略自定义表分区策略自定义库分区策略

本文链接：https://blog.csdn.net/dndndnnffj/article/details/110008361

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";
    }
}

参考博客: https://www.freesion.com/article/3959674242

分库

通过sharding-jdbc对表进行水平拆分,已经能解决大部分的问题的,但是,随着数据量不断增加,单靠分表也不能提高太大的能,还要靠分库来解决

场景: 有2个数据库:sharding1,sharding2 ,每个库内有分为的2个表 user_1,user_2,sharding1库内存放的是年龄小于18岁的用户,sharding2放的是大于等于18的用户,然后user_1 存放的是id为奇数的,user_2是id为偶数的

1 创建数据库和表

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


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

2 配置application.properties

# 配置逻辑库
spring.shardingsphere.datasource.names=db1,db2


# 配置第 1 个数据源
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding2?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root


# 配置 user 表规则
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db$->{1..2}.user_$->{1..2}

# 配置 user 主键生成策略
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=user_auto

#配置 表分片策略
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{ (id % 2)+1 }

#配置 库分片策略(主要就是增加了这个配置)
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=db$->{ (age>18 ? 2:1) }

剩下的操作不变

自定义分库策略

自定义分库策略和自定义分表一样,只需要实现 PreciseShardingAlgorithm接口,然后配置接口

public class UserDatabaseAlgorithm implements PreciseShardingAlgorithm<Integer> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
		//collection 内是所有的数据库名称
        Integer value = preciseShardingValue.getValue();

        if(value.compareTo(18) >=0){
            return "db2";
        }else{
            return "db1";
        }
    }


}

配置:

#配置 库分片策略
spring.shardingsphere.sharding.tables.user.database-strategy.standard.sharding-column=age
#这里指向刚刚自定义的类
spring.shardingsphere.sharding.tables.user.database-strategy.standard.precise-algorithm-class-name=com.mt.config.UserDatabaseAlgorithm

公共表

在某些情况下,我们可能回出现2个数据库内同一个名字的表,然后数据必须保持一致,在进行插入数据时回同时往2个数据库内插入数据,要么都成功,要么都失败

场景: 在sharding1 和sharding2 两个数据库内创建 common表

create table common(
	id int primary key,
	name varchar(20)
);

配置:

# 配置真实数据源
spring.shardingsphere.datasource.names=db1,db2



# 配置第 1 个数据源
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding2?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root


#配置2个数据库的公共表(重点)
spring.shardingsphere.sharding.broadcast-tables=common
#配置表主键
spring.shardingsphere.sharding.tables.common.key-generator.column=id
#配置主键生成策略
spring.shardingsphere.sharding.tables.common.key-generator.type=user_auto

使用:

创建 entity,mapper和service:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Common {
    private Integer id;
    private String name;
}


public interface CommonMapper extends BaseMapper<Common> {
}

@Service
public class CommonService {
    @Autowired
    private CommonMapper commonMapper;

    //这里必须加事务,否则可能出现一个数据库插入成功另一个不成功的情况
    @Transactional(rollbackFor = Exception.class)
    public void insert(Common common){
        commonMapper.insert(common);
    }
}

测试:

@SpringBootTest
class ShardingJdbcApplicationTests {

    @Autowired
    private CommonService commonService;

    @Test
    void contextLoads() {
        commonService.insert(new Common(1,"abcdadfa"));
    }
}

主从复制

sharding-jdbc为我们提供了Mysql主从复制的数据库动态切换功能,只需要进行相应的配置即可实现主从复制环境下的动态切换数据源

场景: mysql配置了主从复制主库是sharding1,从库是sharding2和sharding3。sharding1负责增删改操作,sharding2和sharding3只接受查询操作

1.创建数据库表

create table student(
	id int primary key auto_increment,
	name varchar(200)
);

2.配置

# 配置真实数据源
spring.shardingsphere.datasource.names=db1,db2,db3



# 配置第 1 个数据源
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding2?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root

# 配置第 2 个数据源
spring.shardingsphere.datasource.db3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db3.url=jdbc:mysql://localhost:3306/sharding3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root

#重点
#配置 虚拟数据库ds, 真实的主库为db1
spring.shardingsphere.sharding.master-slave-rules.ds.master-data-source-name=db1
#配置 虚拟数据库ds, 真实的从库为db2
spring.shardingsphere.sharding.master-slave-rules.ds.slave-data-source-names=db2,db3
#配置student表的真实数据库(注意配置的是ds数据库)
spring.shardingsphere.sharding.tables.student.actual-data-nodes=ds.student

3 测试

//编写bean mapper
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private Integer id;
    private String name;
}

public interface StudentMapper extends BaseMapper<Student> {}


//测试:
@Test
    public void tet2(){
        System.out.println("=====================插入数据库======================");
        studentMapper.insert(new Student(1,"abc"));

        System.out.println("=====================查询数据库======================");
        studentMapper.selectList(null);

        System.out.println("=====================查询数据库======================");
        studentMapper.selectList(null);


        System.out.println("=====================查询数据库======================");
        studentMapper.selectList(null);
    }