springboot篇】二十一. 基于springboot电商项目 十三 分库分表介绍和案例

中国加油，武汉加油！

篇幅较长，配合目录观看

1. 分库分表的逻辑

1.1 为什么要使用分库分表

1. 考虑到高性能，单库中一个表的数据过多会影响到查询的效率

1.2 如何分库分表

1. 按照收货地址分库分表，会有跨库查询的情况
2. 按照用户id来分
3. 按照用户id后四位来分
   3.1. 库：用户id%库的数量
   3.2. 表：用户id/表的数量%表的数量

1.3 Test

@Test
void contextLoads() {

    Integer userId = 4000;

    Integer dbNum = 2; // *2
    Integer tabNum = 2;

    for (int i = userId; i < 4100; i++) {
        // 数据库规则
        Integer dbIndex = (i % dbNum) + 1;
        Integer tableIndex = (i / tabNum % tabNum) + 1;
        System.out.println("用户Id:" + i + ",数据库:" + dbIndex + ",表:" + tableIndex);
    }
	// 扩容模拟
    int x = 2;
    for (int i = 0; i < 3; i++) {
        Integer dbIndex = (userId % dbNum) + 1;
        Integer tableIndex = (userId / tabNum % tabNum) + 1;
        System.out.println("第【" + i + "】次用户Id:" + userId + ",数据库:" + dbIndex + ",表:" + tableIndex);
        dbNum *= x;
        tabNum *= x;
    }
}

2. Mybatis实现动态数据源

2.1 脚本准备

2.2 shop-temp新建datasource-demo（module-springboot）

1. Lombok
2. MySQL Driver

2.2.1 datasource-demo导包

<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>2.3</version>
</dependency>

2.2.2 定义Temp实体类

package com.wpj.entity;

import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("t_temp")
public class Temp {
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String name;
}

2.2.3 定义Mapper接口

package com.wpj.mapper;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.wpj.entity.Temp;

public interface ITempMapper extends BaseMapper<Temp> {
}

2.2.4 定义一个ServiceImpl实现类(偷懒)

package com.wpj.service.impl;

import com.wpj.entity.Temp;
import com.wpj.mapper.ITempMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TempServiceImpl {
    @Autowired
    private ITempMapper tempMapper;

    public List<Temp> getList(){
        return tempMapper.selectList(null);
    }
}

2.2.5 配置yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/nz1904-springboot-shop
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
mybatis-plus:
  type-aliases-package: com.wpj.entity
  mapper-locations: classpath:/mapper/*.xml

2.2.6 程序入口开启包扫描

package com.wpj;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(scanBasePackages = "com.wpj")
@MapperScan(basePackages = "com.wpj.mapper")
public class DatasourceDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DatasourceDemoApplication.class, args);
    }
}

2.2.7 Test

2.3 关闭自动创建数据源

package com.wpj;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(scanBasePackages = "com.wpj", exclude = DataSourceAutoConfiguration.class)
@MapperScan(basePackages = "com.wpj.mapper")
public class DatasourceDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DatasourceDemoApplication.class, args);
    }
}

2.4 配置yml

spring:
  datasource1:
    url: jdbc:mysql://localhost:3306/nz1904-springboot-shop
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
  datasource2:
    url: jdbc:mysql://localhost:3306/nz1904-springboot-shop-02
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
mybatis-plus:
  type-aliases-package: com.wpj.entity
  mapper-locations: classpath:/mapper/*.xml

2.5 定义ThreadLocalDBName动态指定数据源

package com.wpj.config;

public class ThreadLocalDBName {

    private static ThreadLocal<String> threadLocal = new ThreadLocal<String>();

    public static void set(String dbName) {
        threadLocal.set(dbName);
    }

    public static String get() {
        return threadLocal.get();
    }
}

2.6 定义DynamicDataSource动态传教数据源

package com.wpj.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

// MyBatis创建动态数据源
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    @Nullable
    protected Object determineCurrentLookupKey() {
        return ThreadLocalDBName.get();
    }
}

2.7 定义MyBatisConfig手动配置数据源

package com.wpj.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {

    @Value("${spring.datasource1.username}")
    private String username;
    @Value("${spring.datasource1.password}")
    private String password;
    @Value("${spring.datasource1.driver-class-name}")
    private String driverClass;
    @Value("${spring.datasource1.url}")
    private String url1;
    @Value("${spring.datasource2.url}")
    private String url2;
    
    @Bean
    public HikariDataSource dataSource1(){
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url1);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClass);
        return dataSource;
    }
    @Bean
    public HikariDataSource dataSource2(){
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url2);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClass);
        return dataSource;
    }
     @Bean
    public DynamicDataSource dynamicDataSource(){
        Map<Object, Object> map = new HashMap<>();
        map.put("db1", dataSource1());
        map.put("db2", dataSource2());
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(map);
        dynamicDataSource.setDefaultTargetDataSource(dataSource1());
        return dynamicDataSource;
    }
    /**
     * 因为我们要使用Mybatis-Plus，所以这里要创建MybatisSqlSessionFactoryBean
     * @return
     */
    @Bean
    public MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean(){
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        sqlSessionFactoryBean.setTypeAliasesPackage("com.wpj.entity");
        return sqlSessionFactoryBean;
    }
}

2.8 Test

public List<Temp> getList(){
	ThreadLocalDBName.set("db2");
	return tempMapper.selectList(null);
}