利用Sharding-Jdbc进行分库分表的操作代码

分库分表是数据库水平扩容的重要手段之一。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技术站

利用Sharding-Jdbc进行分库分表的操作代码

准备工作

配置文件示例

在代码中配置Sharding-Jdbc

使用Sharding-Jdbc进行分库分表的操作

相关文章