# 原因 當mysql資料庫單表大於1千萬以後,查詢的性能就不能保證了,我們必須考慮分庫,分表的方案了,還好,sharding-jdbc可以很優雅的與springboot對接,完成對mysql的分庫和分表。 # 依賴整理 > 為了不影響其它小容量的表,所有添加了動態數據源,只對需要分庫分表的進行配置 ...
原因
當mysql資料庫單表大於1千萬以後,查詢的性能就不能保證了,我們必須考慮分庫,分表的方案了,還好,sharding-jdbc可以很優雅的與springboot對接,完成對mysql的分庫和分表。
依賴整理
為了不影響其它小容量的表,所有添加了動態數據源,只對需要分庫分表的進行配置即可
- 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
提前建立表分庫和分表
測試代碼整理
- 配置類
@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");
}
作者:倉儲大叔,張占嶺,
榮譽:微軟MVP
QQ:853066980
支付寶掃一掃,為大叔打賞!