目录
- 1 分库分表
1 分库分表
1.1 简介
Sharding
有两种:Sharding-Jdbc
和ShardingSphere
,最早是由当当网开源,捐给了Apache
,成为了Apache
顶级项目,后来又升级成了ShardingSphere
具体可以参考官网:https://shardingsphere.apache.org/index_zh.html
Apache ShardingSphere
是一款分布式 SQL
事务和查询引擎,可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强。
1.2 实操准备
1.2.1 Sharding与SpringBoot 公共依赖pom
springboot使用的是2.6.6 版本
<parent>
<artifactId>spring-boot-starter-parent</artifactId>
<groupId>org.springframework.boot</groupId>
<version>2.6.6</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.70</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
<!-- druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!--注意引入druid需要引入log4j,不然会报错-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.2</version>
</dependency>
</dependencies>
1.3 Sharding-Jdbc与SpringBoot
Sharding-Jdbc 4.1.1
与SpringBoot整合比较简单,只用引个依赖,和写个配置文件即可
注意
:如果是jdk1.8
则没有任何问题,如果jdk是比较超前的话比如jdk17
,同样的配置文件可能会找不到表明,必须在 jdk17
对应的表中加个下划线_
,才可以避免启动失败,如果是 jdk1.8
就没有这个问题
1.3.1 pom.xml
<!-- sharding-jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
1.3.2 配置文件
由于配置文件较多,所以分开写了两个
1.3.2.1 application.yml
spring:
application:
name: mybatis-plus-demo
profiles:
include: sharding_4
mybatis-plus:
mapper-locations: 'classpath:mapper/*Mapper.xml'
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 此处配置是 指定 mybatis-plus的 json处理文件
type-handlers-package: cn.util
1.3.2.2 application-sharding_4.yml
spring:
shardingsphere:
datasource:
# 指定多个数据源 用逗号分开
names: db0
db0:
url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
username: xxxx
password: xxxx
sharding:
# 指定 分表 还可以指定分库
tables:
user: # 此处表是逻辑表 对应数据库中 user0,user1
actual-data-nodes: db0.user$->{0..1}
table-strategy:
inline:
sharding-column: id #指定分库的 键
# 指定分库计算方法 此处是groovy 语法
algorithm-expression: user$->{id % 2}
props:
sql:
show: true # 日志显示SQL
注意
:此处数据库部分报红是正常,不用担心
1.4 ShardingSphere与SpringBoot
Sharding-Jdbc 5.2.0
与SpringBoot整合比较简单,只用引个依赖,和写个配置文件即可
1.4.1 pom.xml
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
1.4.2 配置文件
此处配置文件大部分是从官网摘取,找到对应版本号即可查阅,比如:5.2.0https://shardingsphere.apache.org/document/5.2.0/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/sharding/
1.4.2.1 application.yml
spring:
application:
name: mybatis-plus-demo
profiles:
include: sharding_jdbc
mybatis-plus:
mapper-locations: 'classpath:mapper/*Mapper.xml'
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 此处配置是 指定 mybatis-plus的 json处理文件
type-handlers-package: cn.util
1.4.2.2 application-sharding_jdbc.yml
spring:
shardingsphere:
datasource:
names: db0
db0:
url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
username: xxxx
password: xxxx
rules:
sharding:
tables:
user:
# 数据节点配置,采用Groovy表达式
actual-data-nodes: db0.user$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
user-inline:
type: INLINE
props:
algorithm-expression: user$->{id%2}
key-generators:
snowflake:
type: SNOWFLAKE
props:
# 日志显示具体的SQL
sql.show: true
注意
:此处数据库部分报红是正常,不用担心
1.4.3 自定义雪花算法
1.4.3.1 实现KeyGenerateAlgorithm接口
需要实现 KeyGenerateAlgorithm
接口
package cn.util;
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm;
import java.util.Properties;
@Slf4j
public class SnowFlake implements KeyGenerateAlgorithm {
@Override
public Comparable<?> generateKey() {
Snowflake snowflake = IdUtil.createSnowflake(1, 1);
Long id = snowflake.nextId();
System.out.println("自定义的id" + id);
return id;
}
// 返回 自定义 算法类型表示
@Override
public String getType() {
return "SNOWFLAKE_MINE";
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
}
1.4.3.2 添加配置
最后还需要添加一个SPI
配置(点击了解SPI机制 ),resource
下面的META-INF/services生
成一个文件org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm
,配置算法类的全路径。
cn.util.SnowFlake
1.4.3.3 使用自定义类型
在配置文件application-sharding_jdbc.yml
中把对应的key生成规则修改为自定义的SNOWFLAKE_MINE即可
1.4.3.4 MybatisPlus插入失效
如果使用的是 MybatisPlus
插入时发现用的 key 还是系统自带的 雪花算法,觉得没有生效,需要把MybatisPlus
对应的实体主键对应的注解 @TableId
注释掉即可
1.5 ShardingSphere与MybatisPlus多数据源
由于ShardingSphere
切换表或者库是自动的,如果想手动指定一个数据源还得用 MybatisPlus
多数据源 比较方便点
点击此处了解 springboot多数据源配置
1.5.1 pom.xml
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
1.5.2 配置文件
1.5.2.1 application.yml
spring:
application:
name: mybatis-plus-demo
profiles:
include: mbp_dynamic,sharding_jdbc
mybatis-plus:
mapper-locations: 'classpath:mapper/*Mapper.xml'
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
type-handlers-package: cn.util
1.5.2.2 application-mbp_dynamic.yml
spring:
datasource:
dynamic:
primary: master
datasource:
master:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test_2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
type: com.alibaba.druid.pool.DruidDataSource
username: xxxx
password: xxxx
1.5.2.3 application-sharding_jdbc.yml
spring:
shardingsphere:
datasource:
names: db0
db0:
url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
username: xxxx
password: xxxx
rules:
sharding:
tables:
user:
# 数据节点配置,采用Groovy表达式
actual-data-nodes: db0.user$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
user-inline:
type: INLINE
props:
algorithm-expression: user$->{id%2}
key-generators:
snowflake:
# 此处采用自定义 雪花算法
type: SNOWFLAKE_MINE
props:
# 日志显示具体的SQL
sql.show: true
1.5.3 ShardingSphere与多数据源整合
package cn.util;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
@Slf4j
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {
// 分表数据源名称
public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
//动态数据源配置项
@Autowired
private DynamicDataSourceProperties properties;
@Lazy
@Resource(name = "shardingSphereDataSource")
private AbstractDataSourceAdapter shardingDataSource;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后,就可以支持shardingjdbc原生的配置方式了
*/
@Bean
@Primary
public DataSource dataSource() {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
当在使用时在需要使用Sharding的地方 使用 @DS("sharding")
即可切换到Sharding
上面的 dynamicDataSourceProvider
方法也可以这样重构
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
//Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
// Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
标签:分库,数据源,分表,jdbc,sharding,import,Sharding,org,com
From: https://www.cnblogs.com/jingzh/p/17574494.html