java分库分表-sharding-jdbc

最新推荐文章于 2024-05-01 19:17:00 发布

q琦一

最新推荐文章于 2024-05-01 19:17:00 发布

阅读量64

点赞数

分类专栏： Java springBoot 文章标签： java

本文链接：https://blog.csdn.net/weixin_48164819/article/details/135216487

版权

Java 同时被 2 个专栏收录

109 篇文章 1 订阅

订阅专栏

springBoot

51 篇文章 0 订阅

订阅专栏

java分库分表-sharding-jdbc

为啥要分库分表？
表数据太多，查询太慢
有哪些拆分方式？
垂直拆分：将字段根据业务拆分到不同表里，表中的字段将会减少即列减少，行不变
水平拆分：按照一定的规则如按照时间或者id的序列值拆分等，行减少，列不变

1. 引入sharding-jdbc的jar包

<dependency>
  <groupId>io.shardingsphere</groupId>
  <artifactId>sharding-jdbc-core</artifactId>
  <version>3.1.0</version>
</dependency>

2.yml配置数据库连接

spring:
  datasource-master:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456


  datasource-slave:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-order:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_order?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-seat-1:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_seat_1?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-seat-2:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_seat_2?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-seat-3:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_seat_3?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-seat-4:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_seat_4?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

  datasource-seat-5:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/12306_seat_5?serverTimezone=Asia/Shanghai&useTimezone=true&characterEncoding=UTF-8&useSSL=false&useUnicode=true&connectTimeout=1000&socketTimeout=60000&allowMultiQueries=true
    username: root
    password: 123456

3.数据库基础配置步骤

1.创建DB数据库实例
2.初始化spring事务管理器并获取
3.创建并且获取SqlSessionFactory（是创建SqlSession的工厂），SqlSession实例来直接执行被映射的SQL语句，相当于jdbc的connection
4.创建SqlSessionTemplate帮助Spring管理Mybatis的事务
注解使用：
@Primary当有俩个DataSource实例的时候需要指定一个默认的实例，否则会报错
@Bean(name = ”masterDB“) 交给spring管理，并起了个名字 masterDB
@ConfigurationProperties(prefix = “spring.datasource-master”) 读取配置文件的数据库连接实例

例：订单数据源配置

package com.next.db;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.next.orderDao", sqlSessionTemplateRef = "orderSqlSessionTemplate")
public class OrderDataSourceConfig {

    @Bean(name = DataSources.TRAIN_ORDER_DB)
    @ConfigurationProperties(prefix = "spring.datasource-order")
    public DataSource orderDB() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "orderTransactionManager")
    public DataSourceTransactionManager orderTransactionManager(@Qualifier(DataSources.TRAIN_ORDER_DB) DataSource orderDB) {
        return new DataSourceTransactionManager(orderDB);
    }

    @Bean(name = "orderSqlSessionFactory")
    public SqlSessionFactory orderSqlSessionFactory(@Qualifier(DataSources.TRAIN_ORDER_DB) DataSource orderDB) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(orderDB);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:orderMappers/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "orderSqlSessionTemplate")
    public SqlSessionTemplate orderSqlSessionTemplate(@Qualifier("orderSqlSessionFactory") SqlSessionFactory orderSqlSessionFactory) {
        return  new SqlSessionTemplate(orderSqlSessionFactory);
    }
}

例：基础数据源的配置（主从数据源）


package com.next.db;


/**
 * @desc 基础数据源的配置（主从数据源）
 */

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import io.shardingsphere.api.algorithm.masterslave.RoundRobinMasterSlaveLoadBalanceAlgorithm;
import io.shardingsphere.api.config.rule.MasterSlaveRuleConfiguration;
import io.shardingsphere.shardingjdbc.api.MasterSlaveDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;
@Configuration
@MapperScan(basePackages = "com.next.dao", sqlSessionTemplateRef = "sqlSessionTemplate")
public class BasicDataSourceConfig {


    /**
     * @desc 会生成一个DB的实例（主数据源实例）
     */
    @Primary //当有俩个DataSource实例的时候需要指定一个默认的实例，否则会报错，此处使用masterDB作为默认实例
    @Bean(name = DataSources.MASTER_DB) //交给spring管理，并起了个名字 masterDB
    @ConfigurationProperties(prefix = "spring.datasource-master") //读取配置文件的数据库连接实例
    public DataSource masterDB() {
        return DataSourceBuilder.create().build();
    }


    /**
     * @desc 会生成一个DB的实例（从数据源实例）
     */
    @Bean(name = DataSources.SLAVE_DB)
    @ConfigurationProperties(prefix = "spring.datasource-slave")
    public DataSource slaveDB() {
        return DataSourceBuilder.create().build();
    }


    /**
     * @desc 会生成一个DB的实例（主从数据源实例）
     * @Qualifier 可以传入spring管理的bean
     */
    @Bean(name = "masterSlaveDataSource")
    public DataSource masterSlaveDataSource(@Qualifier(DataSources.MASTER_DB) DataSource masterDB,
                                            @Qualifier(DataSources.SLAVE_DB) DataSource slaveDB) throws SQLException{
        Map<String, DataSource> map = Maps.newHashMap();
        map.put(DataSources.MASTER_DB, masterDB);
        map.put(DataSources.SLAVE_DB, slaveDB);

        MasterSlaveRuleConfiguration masterSlaveRuleConfiguration =
                new MasterSlaveRuleConfiguration("ds_master_slave",
                        DataSources.MASTER_DB,
                        Lists.newArrayList(DataSources.SLAVE_DB),
                        new RoundRobinMasterSlaveLoadBalanceAlgorithm()
                );
        return MasterSlaveDataSourceFactory.createDataSource(map, masterSlaveRuleConfiguration,
                Maps.newHashMap(), new Properties());
    }


    /**
     * @desc 数据源事务管理器，初始化事务获取事务
     */
    @Primary
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier(DataSources.MASTER_DB) DataSource masterDB) {
        return new DataSourceTransactionManager(masterDB);
    }


    /**
     * @desc 创建并且获取SqlSessionFactory，是创建SqlSession的工厂，SqlSession实例来直接执行被映射的SQL语句，相当于jdbc的connection
     */
    @Primary
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier(DataSources.MASTER_DB) DataSource masterDB) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        //设置数据源
        bean.setDataSource(masterDB);
        //指定 MyBatis的XML映射器文件的位置,扫描的位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
        return bean.getObject();
    }


    /**
     * @desc 主要是帮助Spring管理Mybatis的事务
     */
    @Primary
    @Bean("sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4.座位分库分表

package com.next.db;

import com.google.common.collect.Maps;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

@Configuration
@MapperScan(basePackages = "com.next.seatDao", sqlSessionTemplateRef = "trainSeatSqlSessionTemplate")
public class SeatDataSourceConfig {

    @Bean(name = DataSources.TRAIN_SEAT_DB_1)
    @ConfigurationProperties(prefix = "spring.datasource-seat-1")
    public DataSource trainSeatDB1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = DataSources.TRAIN_SEAT_DB_2)
    @ConfigurationProperties(prefix = "spring.datasource-seat-2")
    public DataSource trainSeatDB2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = DataSources.TRAIN_SEAT_DB_3)
    @ConfigurationProperties(prefix = "spring.datasource-seat-3")
    public DataSource trainSeatDB3() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = DataSources.TRAIN_SEAT_DB_4)
    @ConfigurationProperties(prefix = "spring.datasource-seat-4")
    public DataSource trainSeatDB4() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = DataSources.TRAIN_SEAT_DB_5)
    @ConfigurationProperties(prefix = "spring.datasource-seat-5")
    public DataSource trainSeatDB5() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "trainSeatShardingDataSource")
    public DataSource trainSeatShardingDataSource(@Qualifier(DataSources.TRAIN_SEAT_DB_1) DataSource trainSeatDB1,
                                                  @Qualifier(DataSources.TRAIN_SEAT_DB_2) DataSource trainSeatDB2,
                                                  @Qualifier(DataSources.TRAIN_SEAT_DB_3) DataSource trainSeatDB3,
                                                  @Qualifier(DataSources.TRAIN_SEAT_DB_4) DataSource trainSeatDB4,
                                                  @Qualifier(DataSources.TRAIN_SEAT_DB_5) DataSource trainSeatDB5
    ) throws SQLException {
        ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();

        // 设置分库的映射
        Map<String, DataSource> dataSourceMap = Maps.newHashMap();
        dataSourceMap.put(DataSources.TRAIN_SEAT_DB_1, trainSeatDB1);
        dataSourceMap.put(DataSources.TRAIN_SEAT_DB_2, trainSeatDB2);
        dataSourceMap.put(DataSources.TRAIN_SEAT_DB_3, trainSeatDB3);
        dataSourceMap.put(DataSources.TRAIN_SEAT_DB_4, trainSeatDB4);
        dataSourceMap.put(DataSources.TRAIN_SEAT_DB_5, trainSeatDB5);

        // 设置表策略
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration();
        // 1\6 DB1
        // 2\7 DB2
        // 3\8 DB3
        // 4\9 DB4
        // 5\10 DB5
        tableRuleConfiguration.setLogicTable("train_seat");
        tableRuleConfiguration.setActualDataNodes(
                DataSources.TRAIN_SEAT_DB_1 + ".train_seat_1," +
                        DataSources.TRAIN_SEAT_DB_2 + ".train_seat_2," +
                        DataSources.TRAIN_SEAT_DB_3 + ".train_seat_3," +
                        DataSources.TRAIN_SEAT_DB_4 + ".train_seat_4," +
                        DataSources.TRAIN_SEAT_DB_5 + ".train_seat_5," +
                        DataSources.TRAIN_SEAT_DB_1 + ".train_seat_6," +
                        DataSources.TRAIN_SEAT_DB_2 + ".train_seat_7," +
                        DataSources.TRAIN_SEAT_DB_3 + ".train_seat_8," +
                        DataSources.TRAIN_SEAT_DB_4 + ".train_seat_9," +
                        DataSources.TRAIN_SEAT_DB_5 + ".train_seat_10"
        );
        // 设置分库策略
        tableRuleConfiguration.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(
                "train_number_id", new TrainSeatDatabaseShardingAlgorithm()));
        // 设置分表策略
        tableRuleConfiguration.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(
                "train_number_id", new TrainSeatTableShardingAlgorithm()));

        shardingRuleConfiguration.getTableRuleConfigs().add(tableRuleConfiguration);

        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new ConcurrentHashMap(), new Properties());
    }

    @Bean(name = "trainSeatTransactionManager")
    public DataSourceTransactionManager trainSeatTransactionManager(@Qualifier("trainSeatShardingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "trainSeatSqlSessionFactory")
    public SqlSessionFactory trainSeatSqlSessionFactory(@Qualifier("trainSeatShardingDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:seatMappers/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "trainSeatSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("trainSeatSqlSessionFactory") SqlSessionFactory trainSeatSqlSessionFactory) {
        return new SqlSessionTemplate(trainSeatSqlSessionFactory);
    }

}

5.座位分库分表策略

train_number_id是 1\6 —> 数据库是DB1
train_number_id是 2\7 —> 数据库是DB2
train_number_id是 3\8 —> 数据库是DB3
train_number_id是 4\9 —> 数据库是DB4
train_number_id是 5\10 —>数据库是DB5

分库策略：表后缀取5的余数就是所分的库
分表策略：表后缀取10的余数就是所在的表，余数是0的在10表
在这里插入图片描述

座位分库策略

package com.next.db;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

public class TrainSeatDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    private final static String PREFIX = "trainSeatDB";

    private String determineDB(int val) {
        int db = val % 5;
        if (db == 0) {
            db = 5;
        }
        return PREFIX + db;
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        String actualDbName = determineDB(shardingValue.getValue());
        if (availableTargetNames.contains(actualDbName)) {
            return actualDbName;
        }
        throw new IllegalArgumentException();
    }
}

座位分表策略

package com.next.db;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

public class TrainSeatTableShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    private final static String PREFIX = "train_seat_";

    private String determineTable(int val) {
        int table = val % 10;
        if (table == 0) {
            table = 10;
        }
        return PREFIX + table;
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        String actualTableName = determineTable(shardingValue.getValue());
        if (availableTargetNames.contains(actualTableName)) {
            return actualTableName;
        }
        throw new IllegalArgumentException();
    }
}