分库分表是数据库水平扩容的重要手段之一。Sharding-Jdbc是一个开源的分布式的关系型数据库中间件,它提供了比较完整的分库分表方案。下面就介绍一下如何使用Sharding-Jdbc进行分库分表的操作代码。
准备工作
- 在Maven中引入Sharding-Jdbc相关的依赖包。
- 编写Sharding-Jdbc的配置文件,配置主要包括数据源信息和分库分表等规则。
配置文件示例
spring:
shardingsphere:
datasource:
names: ds_0, ds_1
ds_0:
url: jdbc:mysql://localhost:3306/db_0?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: root
type: com.zaxxer.hikari.HikariDataSource
ds_1:
url: jdbc:mysql://localhost:3306/db_1?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: root
type: com.zaxxer.hikari.HikariDataSource
sharding:
tables:
user:
actualDataNodes: ds_${0..1}.user_${0..3}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: userShardingAlgorithm
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
userShardingAlgorithm:
type: INLINE
props:
algorithm-expression: user_${id % 4}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
props:
sql:
show: true
- 该示例中配置了两个数据源:ds_0和ds_1。
- 表user分散在两个数据源中,每个数据源都分了4个表(user_0, user_1, user_2, user_3)。
- user表的分库分表策略是根据shardingColumn=id进行筛选,使用Inline算法,id%4的余数即为表名后缀。
- user表的主键生成策略是用雪花算法生成。
在代码中配置Sharding-Jdbc
在代码中引入Sharding-Jdbc的配置,示例如下:
@Bean
public DataSource shardingDataSource() throws SQLException {
return ShardingDataSourceFactory.createDataSource(readConfigFile(), readRules());
}
private ShardingRule readRules() {
return ShardingRule.builder()
.dataSourceRule(dataSourceRule())
.tableRules(Collections.singleton(tableRule()))
.databaseShardingStrategy(new DatabaseShardingStrategy("id", new UserDatabaseAlgorithm()))
.tableShardingStrategy(new TableShardingStrategy("id", new UserTableAlgorithm()))
.build();
}
private DataSourceRule dataSourceRule() {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", dataSource0());
dataSourceMap.put("ds_1", dataSource1());
return new DataSourceRule(dataSourceMap, "ds_0");
}
private TableRule tableRule() {
TableRule tableRule = TableRule.builder("user")
.actualTables(Arrays.asList("user_0", "user_1", "user_2", "user_3"))
.dataSourceRule(dataSourceRule())
.build();
return tableRule;
}
private DataSource dataSource0() {
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db_0?useUnicode=true&characterEncoding=utf8&useSSL=false");
hikariDataSource.setUsername("root");
hikariDataSource.setPassword("root");
return hikariDataSource;
}
private DataSource dataSource1() {
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db_1?useUnicode=true&characterEncoding=utf8&useSSL=false");
hikariDataSource.setUsername("root");
hikariDataSource.setPassword("root");
return hikariDataSource;
}
- 在readRules()方法中,根据Sharding的规则配置DataSourceRule和TableRule。
- 在dataSourceRule()方法中,配置了两个数据源ds_0和ds_1。
- 在tableRule()方法中,配置了user表的实际表名列表和DataSourceRule。
- 在数据源ds_0和ds_1中都有user表的4个实际表user_0、user_1、user_2和user_3。
- 在readRules()方法中,使用了DatabaseShardingStrategy和TableShardingStrategy两个策略。
使用Sharding-Jdbc进行分库分表的操作
使用Spring JdbcTemplate进行数据库操作的示例:
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public User getUserById(Long id) {
String sql = "SELECT * FROM user WHERE id = ?";
Object[] params = {id};
return jdbcTemplate.queryForObject(sql, params, new UserRowMapper());
}
@Override
public void saveUser(User user) {
String sql = "INSERT INTO user (id, name, age) VALUES (?, ?, ?)";
Object[] params = {user.getId(), user.getName(), user.getAge()};
jdbcTemplate.update(sql, params);
}
}
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
return user;
}
}
- 在DatabaseShardingStrategy和TableShardingStrategy中,分别使用了UserDatabaseAlgorithm和UserTableAlgorithm两个算法。
- 以上是Sharding-Jdbc进行分库分表的基本操作流程。
再给一个Sharding-Jdbc分库分表的实际项目实战缺省的示例:
spring:
shardingsphere:
datasource:
names: ds_master,ds_slave0,ds_slave1,ds_slave2
ds_master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowMultiQueries=true
username: root
password: admin123
ds_slave0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3307/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowMultiQueries=true
username: root
password: admin123
ds_slave1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3307/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowMultiQueries=true
username: root
password: admin123
ds_slave2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3307/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowMultiQueries=true
username: root
password: admin123
sharding:
default-database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds${user_id % 4}
tables:
t_order:
actual-data-nodes: ds${0..3}.t_order_${0..1}
database-strategy:
hint:
sharding-algorithm-name: order-database-strategy
sharding-column: order_id
table-strategy:
hint:
sharding-algorithm-name: order-table-strategy
sharding-column: order_id
sharding-algorithms:
order-database-strategy:
type: hint
props:
algorithm-class-name: com.soul.blog.repository.ShardingAlgorithm.OrderDatabaseShardingAlgorithm
order-table-strategy:
type: hint
props:
algorithm-class-name: com.soul.blog.repository.ShardingAlgorithm.OrderTableShardingAlgorithm
props:
sql:
show: true
simple: true
package com.soul.blog.repository;
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.metadata.DataSourcePoolMetadataProvidersConfiguration;
import org.springframework.boot.autoconfigure.transaction.PlatformTransactionManagerAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.google.common.collect.Maps;
import com.soul.blog.constant.DataSourceNames;
import io.shardingsphere.api.config.HintShardingStrategyConfiguration;
import io.shardingsphere.api.config.ShardingRuleConfiguration;
import io.shardingsphere.api.config.TableRuleConfiguration;
import io.shardingsphere.api.config.algorithm.HintShardingAlgorithmConfiguration;
import io.shardingsphere.api.config.rule.DataSourceRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRule;
import io.shardingsphere.api.config.strategy.HintShardingStrategyConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.api.config.strategy.TableShardingStrategyConfiguration;
import io.shardingsphere.api.config.strategy.standard.StandardShardingStrategyConfiguration;
import io.shardingsphere.api.datasource.ShardingDataSource;
import io.shardingsphere.api.spring.datasource.SpringShardingDataSource;
import io.shardingsphere.api.spring.transaction.SpringShardingTransactionManager;
@AutoConfigureAfter({DataSourcePoolMetadataProvidersConfiguration.class})
@Configuration
@EnableTransactionManagement(order = 2)
@EnableConfigurationProperties(ShardingDatabasesProperties.class)
public class ShardingJdbcConfiguration {
@Autowired
private Environment env;
@Autowired
private List<DataSource> dataSourceList;
@Autowired
private ShardingDatabasesProperties shardingDatabasesProperties;
@Bean(name = "shardingDataSource")
public DataSource getShardingDS() throws Exception {
DataSourceRuleConfiguration dataSourceRuleConfiguration = new DataSourceRuleConfiguration();
dataSourceRuleConfiguration.setName("mainDataSourceGroup");
int i = 0;
for (DataSource dataSource : dataSourceList) {
dataSourceRuleConfiguration.getDataSources().put(DataSourceNames.getRealDataSourceName(i), dataSource);
i++;
}
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "mainDataSourceGroup.t_order${0..3}");
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new HintShardingStrategyConfiguration(new HintShardingAlgorithmConfiguration()));
orderTableRuleConfig.setTableShardingStrategyConfig(new HintShardingStrategyConfiguration(new HintShardingAlgorithmConfiguration()));
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.getTableRuleConfigs().add(orderTableRuleConfig);
shardingRuleConfiguration.getBindingTableGroups().add("t_order");
shardingRuleConfiguration.getBroadcastTableNames().add("t_config");
Object databases = shardingDatabasesProperties.getDatabases();
Map<String, DataSource> dataSourceMap = Maps.newHashMap();
dataSourceMap.putAll(dataSourceRuleConfiguration.getDataSources());
ShardingDataSource shardingDataSource = new ShardingDataSource(dataSourceMap, shardingRuleConfiguration, Maps.newHashMap(), new Properties());
return shardingDataSource;
}
@Bean
public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("shardingDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "shardingTX")
public PlatformTransactionManager getShardingTX(@Qualifier("shardingDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
package com.soul.blog.repository.ShardingAlgorithm;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.springframework.stereotype.Component;
import com.google.common.collect.Range;
import com.soul.blog.constant.DataSourceNames;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingAlgorithm;
@Component
public class OrderDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
Set<String> tables = new HashSet<>();
for (String table : availableTargetNames) {
if (table.startsWith("mainDataSourceGroup.t_order")) {
tables.add(table);
}
}
Integer value = shardingValue.getValue() % DataSourceNames.number;
if (value == 0) {
return "ds_master";
} else {
for (String table : tables) {
Integer id = Integer.valueOf(table.substring(table.length() - 1)) % DataSourceNames.number;
if (id.equals(value)) {
return table;
}
}
}
for (String table : tables) {
if (table.endsWith("0")) {
return table;
}
}
return "订单不存在:user_id=" + shardingValue.getValue();
}
}
package com.soul.blog.repository.ShardingAlgorithm;
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;
import org.springframework.stereotype.Component;
import com.google.common.collect.Range;
import com.soul.blog.constant.DataSourceNames;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import io.shardingsphere.api.config.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import io.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import io.shardingsphere.api.sharding.standard.StandardShardingAlgorithm;
import io.shardingsphere.api.sharding.standard.StandardShardingStrategyConfiguration;
import lombok.extern.slf4j.Slf4j;
@Component
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
Set<String> tableNames = new HashSet<>();
// 获取实际表名列表
for (String name : availableTargetNames) {
if (name.startsWith("t_order")) {
tableNames.add(name);
}
}
for (String tableName : tableNames) {
if (tableName.endsWith(shardingValue.getValue() % DataType.orderTable.length + "")) {
return tableName;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
Set<String> tableNames = new HashSet<>();
// 获取实际表名列表
for (String name : availableTargetNames) {
if (name.startsWith("t_order")) {
tableNames.add(name);
}
}
Range<Long> range = shardingValue.getValueRange();
for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i ++) {
// 决定落到哪个库哪张表
String existTableName = null;
for (String each : tableNames) {
if (each.endsWith(i % DataType.orderTable.length + "")) {
existTableName = each;
}
}
if (null != existTableName) {
tableNames.add(existTableName); //确定类型
// 将排序列用于限制路由范围
if (tableNames.size() == DataType.orderTable.length) {
break;
}
}
}
if (tableNames.size() == 0) {
throw new IllegalArgumentException();
}
return tableNames;
}
}
其中,还有一些Sharding-Jdbc的配置细节和辅助类,这里不作赘述。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:利用Sharding-Jdbc进行分库分表的操作代码 - Python技术站