原因
当mysql数据库单表大于1千万以后，查询的性能就不能保证了，我们必须考虑分库，分表的方案了，还好，sharding-jdbc可以很优雅的与springboot对接，完成对mysql的分库和分表。
依赖整理
为了不影响其它小容量的表，所有添加了动态数据源，只对需要分库分表的进行配置即可文章来源：https://www.toymoban.com/news/detail-457341.html
- com.baomidou:dynamic-datasource-spring-boot-starter:3.3.1
- org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:4.1.1
- com.baomidou:dynamic-datasource-spring-boot-starter:3.3.1
- com.baomidou:mybatis-plus-boot-starter:3.4.1
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
配置整理
spring:
application.name: sharding-jdbc
datasource:
dynamic:
primary: master0
datasource:
master0:
url: jdbc:mysql://192.168.4.xx:3306/sharding0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: xxx
driver-class-name: com.mysql.jdbc.Driver
master1:
url: jdbc:mysql://192.168.4.xx:3306/sharding1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: xxx
driver-class-name: com.mysql.jdbc.Driver
shardingsphere:
datasource:
names: ds0,ds1
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.4.xx:3306/sharding0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: xxx
type: com.zaxxer.hikari.HikariDataSource
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.4.xx:3306/sharding1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: xxx
type: com.zaxxer.hikari.HikariDataSource #必须个type，否则报错
sharding:
tables:
t_order:
#key-generator:
# column: id
# type: SNOWFLAKE
actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} #需要开发人员手动按规则建立数据表
database-strategy:
inline:
sharding-column: id
algorithm‐expression: ds$->{id % 2}
table-strategy:
inline:
sharding-column: id
algorithm‐expression: t_order_$->{id % 2}
props:
sql:
show: true # 日志显示SQL
mybatis:
mapperLocations: classpath:mapper/*.xml
typeAliasesPackage: com.lind.shardingjdbc.entity
configuration:
mapUnderscoreToCamelCase: true
提前建立表分库和分表
文章来源地址https://www.toymoban.com/news/detail-457341.html
测试代码整理
- 配置类
@Configuration
@AutoConfigureBefore({ DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class })
public class DataSourceConfiguration {
// 分表数据源名称
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* shardingjdbc有四种数据源，需要根据业务注入不同的数据源
*
* <p>
* 1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>
* 2. 主从数据源: masterSlaveDataSource;
* <p>
* 3. 脱敏数据源：encryptDataSource;
* <p>
* 4. 影子数据源：shadowDataSource
*/
@Lazy
@Resource(name = "shardingDataSource")
AbstractDataSourceAdapter shardingDataSource;
// 动态数据源配置项
@Autowired
private DynamicDataSourceProperties properties;
@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原生的配置方式了
*/
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
- 实体类和mapper类
@Data
@TableName("t_order")
public class Order {
@TableId(type = IdType.ASSIGN_ID)
Long orderId;
double amount;
Integer userId;
}
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}
- 分表的测试
@GetMapping("insert")
@DS("sharding")
public ResponseEntity test() {
Order order = new Order();
order.setAmount(100);
order.setUserId(1);
orderMapper.insert(order);
return ResponseEntity.ok("success");
}
- 不进行分表的测试
@GetMapping("insert-not-sharding")
public ResponseEntity testNotSharding() {
Order order = new Order();
order.setAmount(101);
order.setUserId(2);
orderMapper.insert(order);
return ResponseEntity.ok("success");
}
到了这里，关于springboot~sharding-jdbc实现分库分表的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
