多租户数据库设计 mysql 多租户数据库连接池

转载

数据大侠客 2023-09-20 16:31:15

文章标签 多租户数据库设计 mysql mybatis 数据库 mysql spring 文章分类 MySQL 数据库

多租户实现分类
既然多租户设计的难点在于隔离用户数据，又同时共享资源。那么可以根据用户数据的物理分离程度来进行分类。

分为三类：数据库（DataBase）、模式（Schema）、数据表（Table）。

分离数据库
每一个租户分配一个数据库连接池，根据租户id获取对应的连接池

多租户数据库设计 mysql 多租户数据库连接池_mysql

模式（Schema）
应用使用一个数据库连接池，切换不同的 Schema 。就可以切换不同租户。（可以简单理解为。java里面的包名称。不同的包名下，可以有相同类名的 class ）

MySQL 不支持 Schema 使用数据库代替！ SQL Server 和 PostgreSQL 支持 Schema、

多租户数据库设计 mysql 多租户数据库连接池_mysql_02

数据表（Table）
给每一个表结构，添加一个 tenant_id 字段，在 select、insert、update、delete 中都加上 tenant_id 的条件，此方式也是最简单，改动代码最少的。但是数据量大的时候，单表压力较大！

多租户数据库设计 mysql 多租户数据库连接池_mysql_03

以上内容截取自 Spring Boot JPA MySQL 多租户系统 Part1 - 基础实现

本次使用第二种方式 “模式（Schema）” 基于 spring-boot 2.6.14 和 mybatis-plus 3.5.2 以及 liquibase 4.17.2

liquibase 是用来管理表结构变化的版本控制！重中之重！！！
因为每创建一个租户，都需要创建表结构，所以必须要有版本来控制表结构，
而我们的项目不可能表结构，一次创建永远不修改。
那么已存在的租户，表结构，也需要跟着修改！

<dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>4.17.2</version>
        </dependency>

创建一个 DatabaseManager 用于管理，数据库的表结构！

package com.xaaef.molly.core.tenant;

import com.xaaef.molly.core.tenant.props.MultiTenantProperties;

import javax.sql.DataSource;

public interface DatabaseManager {

    default String getOldDbName(String url) {
        var startInx = url.lastIndexOf("?");
        var sub = url.substring(0, startInx);
        int startInx2 = sub.lastIndexOf("/") + 1;
        return sub.substring(startInx2);
    }


    /**
     * TODO 租户创建表结构
     *
     * @author WangChenChen
     * @date 2022/12/11 11:04
     */
    void createTable(String tenantId);


    /**
     * TODO 租户删除表结构
     *
     * @author WangChenChen
     * @date 2022/12/11 11:04
     */
    void deleteTable(String tenantId);


    /**
     * TODO 获取多租户信息
     *
     * @author WangChenChen
     * @date 2022/12/11 11:04
     */
    MultiTenantProperties getMultiTenantProperties();


}

createTable() : 创建租户的时候，创建表结构

deleteTable() : 删除租户的时候，删除表结构

实现类

package com.xaaef.molly.core.tenant.schema;

import com.xaaef.molly.core.tenant.DatabaseManager;
import com.xaaef.molly.core.tenant.props.MultiTenantProperties;
import liquibase.Liquibase;
import liquibase.database.jvm.JdbcConnection;
import liquibase.resource.ClassLoaderResourceAccessor;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;

import static org.springframework.util.ResourceUtils.CLASSPATH_URL_PREFIX;


/**
 * 多租户 基于 模式（Schema）
 * 此方式，是 默认连接池，切换 租户的 schema，
 * 因为 mysql 数据库不支持 schema 所以只能使用 数据库 代替
 *
 * @author WangChenChen
 * @date 2022/12/11 11:29
 */


@Slf4j
@Component
@AllArgsConstructor
@ConditionalOnProperty(prefix = "multi.tenant", name = "db-style", havingValue = "Schema")
public class SchemaDataSourceManager implements DatabaseManager {

    // 默认租户的数据源
    private final DataSource dataSource;

    private final MultiTenantProperties multiTenantProperties;

    private final DataSourceProperties dataSourceProperties;


    @Override
    public MultiTenantProperties getMultiTenantProperties() {
        return multiTenantProperties;
    }

    /**
     * 创建表
     *
     * @author WangChenChen
     * @date 2022/12/7 21:05
     */
    @Override
    public void createTable(String tenantId) {
        log.info("tenantId: {} create table ...", tenantId);
        try {
            // 判断 schema 是否存在。不存在就创建
            var conn = dataSource.getConnection();
            // 判断数据库是否存在！不存在就创建
            String tenantDbName = multiTenantProperties.getPrefix() + tenantId;
            String sql = String.format("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;", tenantDbName);
            conn.createStatement().execute(sql);
            // 创建一次性的 jdbc 链接。只是用来生成表结构的。用完就关闭。
            var conn1 = new JdbcConnection(getTempConnection(tenantDbName));
            var changeLogPath = multiTenantProperties.getOtherChangeLog();
            // 使用 Liquibase 创建表结构
            if (multiTenantProperties.getOtherChangeLog().startsWith(CLASSPATH_URL_PREFIX)) {
                changeLogPath = multiTenantProperties.getOtherChangeLog().replaceFirst(CLASSPATH_URL_PREFIX, "");
            }
            var liquibase = new Liquibase(changeLogPath, new ClassLoaderResourceAccessor(), conn1);
            liquibase.update(tenantId);
            // 关闭链接
            conn1.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Override
    public void deleteTable(String tenantId) {
        log.info("tenantId: {} delete table ...", tenantId);
        String tenantDbName = multiTenantProperties.getPrefix() + tenantId;
        String sql = String.format("DROP DATABASE %s ;", tenantDbName);
        try {
            var conn = getTempConnection(tenantDbName);
            conn.createStatement().execute(sql);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 创建 临时的 jdbc 连接。 用于生成表结构。用完就关闭
     *
     * @return
     * @author WangChenChen
     * @date 2022/12/8 12:44
     */
    private Connection getTempConnection(String tenantDbName) throws Exception {
        // 获取默认的数据名称
        var oldDbName = getOldDbName(dataSourceProperties.getUrl());
        // 替换连接池中的数据库名称
        var dataSourceUrl = dataSourceProperties.getUrl().replaceFirst(oldDbName, tenantDbName);
        //3.获取数据库连接对象
        return DriverManager.getConnection(dataSourceUrl,
                dataSourceProperties.getUsername(),
                dataSourceProperties.getPassword());
    }


}

多租户的配置类。包括，默认的租户ID, 租户数据库的前缀。以及生成表结构的语句

package com.xaaef.molly.core.tenant.props;

import com.xaaef.molly.core.tenant.enums.DbStyle;
import lombok.Getter;
import lombok.Setter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * <p>
 * 多租户全局配置
 * </p>
 *
 * @author WangChenChen
 * @version 1.1
 * @date 2022/12/9 11:53
 */

@Getter
@Setter
@Component
@ConfigurationProperties(prefix = "multi.tenant")
public class MultiTenantProperties {

    /**
     * 是否开启租户模式
     */
    private Boolean enable = true;

    /**
     * 是否开启租户模式
     */
    private Boolean enableProject = false;

    /**
     * 数据库名称前缀
     */
    private String prefix = "molly_";

    /**
     * 默认租户ID
     */
    private String defaultTenantId = "master";

    /**
     * 默认 项目ID
     */
    private String defaultProjectId = "master";

    /**
     * 多租户的类型。
     *
     * 一定要在配置文件里指定....
     */
    private DbStyle dbStyle;


    /**
     * 创建表结构
     */
    private Boolean createTable = Boolean.TRUE;


    /**
     * 其他 数据库 创建表结构的 Liquibase 文件地址
     */
    private String otherChangeLog = "classpath:db/changelog-other.xml";


    /**
     * 主 数据库 创建表结构的 Liquibase 文件地址
     */
    private String masterChangeLog = "classpath:db/changelog-master.xml";


}

package com.xaaef.molly.core.tenant.util;


import org.apache.commons.lang3.StringUtils;
import org.springframework.core.NamedInheritableThreadLocal;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import java.util.Objects;

import static com.xaaef.molly.core.tenant.consts.MbpConst.X_PROJECT_ID;
import static com.xaaef.molly.core.tenant.consts.MbpConst.X_TENANT_ID;


/**
 * <p>
 * </p>
 *
 * @author WangChenChen
 * @version 1.1
 * @date 2022/11/25 11:14
 */


public class TenantUtils {

    private final static ThreadLocal<String> TENANT_ID_THREAD_LOCAL = new NamedInheritableThreadLocal<>("TENANT_ID_THREAD_LOCAL");


    private final static ThreadLocal<String> PROJECT_ID_THREAD_LOCAL = new NamedInheritableThreadLocal<>("PROJECT_ID_THREAD_LOCAL");


    /**
     * 获取 租户ID
     */
    public static String getTenantId() {
        if (StringUtils.isNotBlank(TENANT_ID_THREAD_LOCAL.get())) {
            return TENANT_ID_THREAD_LOCAL.get();
        }
        var attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        if (Objects.isNull(attributes)) {
            return null;
        }
        var request = attributes.getRequest();
        return request.getHeader(X_TENANT_ID);
    }


    /**
     * 设置 租户ID
     */
    public static void setTenantId(String tenantId) {
        if (StringUtils.isNotBlank(tenantId)) {
            TENANT_ID_THREAD_LOCAL.set(tenantId);
        } else {
            TENANT_ID_THREAD_LOCAL.remove();
        }
    }




    /**
     * 获取 项目ID
     */
    public static String getProjectId() {
        if (StringUtils.isNotBlank(PROJECT_ID_THREAD_LOCAL.get())) {
            return PROJECT_ID_THREAD_LOCAL.get();
        }
        var attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        if (Objects.isNull(attributes)) {
            return null;
        }
        var request = attributes.getRequest();
        return request.getHeader(X_PROJECT_ID);
    }


    /**
     * 设置 项目ID
     */
    public static void setProjectId(String tenantId) {
        if (StringUtils.isNotBlank(tenantId)) {
            PROJECT_ID_THREAD_LOCAL.set(tenantId);
        } else {
            PROJECT_ID_THREAD_LOCAL.remove();
        }
    }


}

spring mvc 拦截器，从请求中获取，租户ID

package com.xaaef.molly.core.tenant;


import cn.hutool.core.util.StrUtil;
import com.xaaef.molly.common.util.JsonResult;
import com.xaaef.molly.common.util.ServletUtils;
import com.xaaef.molly.core.auth.jwt.JwtSecurityUtils;
import com.xaaef.molly.core.tenant.service.MultiTenantManager;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.xaaef.molly.core.tenant.util.TenantUtils;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;

import static com.xaaef.molly.core.tenant.consts.MbpConst.X_TENANT_ID;


/**
 * <p>
 * </p>
 *
 * @author WangChenChen
 * @version 1.1
 * @date 2022/11/15 11:41
 */


@Slf4j
@Component
@AllArgsConstructor
public class TenantIdInterceptor implements HandlerInterceptor {

    private final MultiTenantManager tenantManager;

    @Override
    public boolean preHandle(HttpServletRequest request,
                             @NotNull HttpServletResponse response,
                             @NotNull Object handler) throws Exception {
        /*
         * 从请求头中获取 如:
         * GET https://www.baidu.com/hello
         * x-tenant-id=master
         * */
        var tenantId = request.getHeader(X_TENANT_ID);
        if (StringUtils.isEmpty(tenantId)) {
            /*
             * 从URL地址中获取   如:
             * GET https://www.baidu.com/hello?x-tenant-id=master
             * */
            tenantId = request.getParameter(X_TENANT_ID);
        }
        if (StringUtils.isEmpty(tenantId)) {
            // 判断当前此请求，是否已经登录。
            if (JwtSecurityUtils.isAuthenticated()) {
                // 判断登录的用户类型。
                // 系统用户:  必须添加 租户ID.
                // 租户用户:  租户ID 在登录的时候，已经确定了
                if (JwtSecurityUtils.isMasterUser()) {
                    return writeError(response);
                } else {
                    TenantUtils.setTenantId(JwtSecurityUtils.getTenantId());
                    tenantId = JwtSecurityUtils.getTenantId();
                }
            } else {
                return writeError(response);
            }
        }
        // 校验租户，是否存在系统中
        if (!tenantManager.existById(tenantId)) {
            var err = StrUtil.format("租户ID {} 不存在！", tenantId);
            ServletUtils.renderError(response, JsonResult.fail(err));
            return false;
        }
        return HandlerInterceptor.super.preHandle(request, response, handler);
    }


    private static boolean writeError(HttpServletResponse response) {
        var err = StrUtil.format("请求头或者URL参数中必须添加 {}", X_TENANT_ID);
        ServletUtils.renderError(response, JsonResult.fail(err));
        return false;
    }

}

下面就是核心中的核心了 mybatis-puls 拦截器

package com.xaaef.molly.core.tenant.schema;

import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.xaaef.molly.core.auth.jwt.JwtSecurityUtils;
import com.xaaef.molly.core.tenant.props.MultiTenantProperties;
import com.xaaef.molly.core.tenant.util.TenantUtils;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

import static com.xaaef.molly.core.tenant.consts.MbpConst.TENANT_IGNORE_TABLES;


/**
 * <p>
 * </p>
 *
 * @author WangChenChen
 * @version 1.1
 * @date 2022/12/14 12:40
 */


@Slf4j
@Component
@AllArgsConstructor
public class SchemaInterceptor extends JsqlParserSupport implements InnerInterceptor {


    private final MultiTenantProperties props;


    @Override
    public void beforePrepare(StatementHandler sh, Connection conn, Integer transactionTimeout) {
        var mpBoundSql = PluginUtils.mpBoundSql(sh.getBoundSql());
        // 获取当前 sql 语句中的。表名称
        Set<String> tableName = getTableListName(mpBoundSql.sql());
        // 判断 表名称 是否需要过滤，即: 使用 公共库，而不是 租户 库。
        if (ignoreTable(tableName)) {
            // 切换数据库
            switchSchema(conn, props.getDefaultTenantId());
        } else {
            // 切换数据库
            switchSchema(conn, getCurrentTenantId());
        }
        InnerInterceptor.super.beforePrepare(sh, conn, transactionTimeout);
    }


    private String getCurrentTenantId() {
        // 判断当前此请求，是否已经登录。
        if (JwtSecurityUtils.isAuthenticated()) {
            // 判断登录的用户类型。
            // 系统用户:  可以操作任何一个 租户 的数据库。
            // 租户用户:  只能操作 所在租户 的数据库
            if (JwtSecurityUtils.isMasterUser()) {
                return TenantUtils.getTenantId();
            } else {
                return JwtSecurityUtils.getTenantId();
            }
        }
        return Optional.ofNullable(TenantUtils.getTenantId())
                .orElse(props.getDefaultTenantId());
    }


    private void switchSchema(Connection conn, String schema) {
        // PostgreSQL 和 SQL Server 可以使用 schema
        // conn.setSchema(schema);
        // 切换数据库
        String sql = String.format("use %s%s", props.getPrefix(), schema);
        try {
            conn.createStatement().execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    private final static TablesNamesFinder TABLES_NAMES_FINDER = new TablesNamesFinder();

    /**
     * 解析 sql 获取全部的 表名称
     */
    private static Set<String> getTableListName(String sql) {
        Statements statements = null;
        try {
            statements = CCJSqlParserUtil.parseStatements(sql);
        } catch (JSQLParserException e) {
            e.printStackTrace();
            return Set.of();
        }
        return statements.getStatements()
                .stream()
                .map(TABLES_NAMES_FINDER::getTableList)
                .flatMap(Collection::stream)
                .collect(Collectors.toSet());
    }


    /**
     * 过滤 公共的表。
     */
    private static boolean ignoreTable(Set<String> tableName) {
        return CollectionUtil.containsAny(TENANT_IGNORE_TABLES, tableName);
    }


}

主要方法

beforePrepare() : 在 sql 语句执行前的前置处理。根据PluginUtils.mpBoundSql(sh.getBoundSql()); 获取当前执行的 sql 语句。

getTableListName() ：根据 sql 语句，获取要执行的表名称。然后根据表名，判断此表，是公共表，还是租户表。如：sys_config 这种表，肯定是所有租户公用的。而 user，role，之类，肯定是每个租户独有的！

switchSchema() : 根据数据库名称，切换数据库，因为 mysql 不支持 Schema 。所有只能用数据库替代。

getCurrentTenantId() : 获取当前登录的用户所在的租户ID。当然也要判断用户类型。

如果是：系统用户，那么此用户就可以操作所有的租户数据库。也就是说，可以随便切换到其他租户的数据库。

如果是：租户用户，那么此用户只能操作 “默认数据库” 和 “租户所属的数据库”，默认数据库中存放了一些公共数据，如：sys_config 。

这个判断很简单，就是租户id ，是不是默认的租户id、

package com.xaaef.molly.core.tenant;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.xaaef.molly.common.util.JsonUtils;
import com.xaaef.molly.core.auth.jwt.JwtSecurityUtils;
import com.xaaef.molly.core.tenant.props.MultiTenantProperties;
import com.xaaef.molly.core.tenant.schema.SchemaInterceptor;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.time.LocalDateTime;

import static com.xaaef.molly.core.tenant.consts.MbpConst.*;


/**
 * <p>
 * </p>
 *
 * @author Wang Chen Chen
 * @version 1.0
 * @date 2021/7/8 9:21
 */


@Slf4j
@Configuration
@AllArgsConstructor
@EnableTransactionManagement
public class MybatisPlusConfig {

    private final MultiTenantProperties tenantProperties;

    /**
     * 单页分页条数限制(默认无限制,参见 插件#handlerLimit 方法)
     */
    private static final Long MAX_LIMIT = 100L;

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,
     * 需要设置 MybatisConfiguration#useDeprecatedExecutor = false
     * 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        // 设置 ObjectMapper
        JacksonTypeHandler.setObjectMapper(JsonUtils.getMapper());

        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 是否启用租户
        if (tenantProperties.getEnable()) {
            var schemaInterceptor = new SchemaInterceptor(tenantProperties);
            interceptor.addInnerInterceptor(schemaInterceptor);
        }

        //分页插件: PaginationInnerInterceptor
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setMaxLimit(MAX_LIMIT);

        //防止全表更新与删除插件: BlockAttackInnerInterceptor
        BlockAttackInnerInterceptor blockAttackInnerInterceptor = new BlockAttackInnerInterceptor();

        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        interceptor.addInnerInterceptor(blockAttackInnerInterceptor);

        return interceptor;
    }


}

到这里已经整合完成了。大概流程就是这样

1. web浏览器发送请求，请求头中携带 x-tenant-id 参数，用于区分是哪个租户

2.spring mvc 拦截器，拦截到租户id。保存到 ThreadLocal 中。

3.执行自己的业务

4.mybatis-plus 拦截器，拦截到业务中的 sql 语句，获取到表名称。判断是否为公共表，

如果是：公共表切换到默认数据库，

如果是：租户的表，就再次判断当前登录的用户类型，

如果是：系统用户，获取 ThreadLocal 中的租户id。切换到对应的数据库

如果是：租户用户，直接切换到所属的租户。租户用户，只能操作自己的库

系统用户登录

### 获取验证码
### http://localhost:18891/auth/captcha/codes?codeKey=5jXzuwcoUzbtnHNh
GET {{baseUrl}}/auth/captcha/codes?codeKey=5jXzuwcoUzbtnHNh
x-tenant-id: master
### [master]密码模式登录
POST {{baseUrl}}/auth/login
Content-Type: application/json
x-tenant-id: master
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0
{
"username": "admin",
"password": "123456",
"codeKey": "5jXzuwcoUzbtnHNh",
"codeText": "uj57i"
}
> {%
client.global.set("tokenValue", response.body.data.access_token);
client.global.set("refreshToken", response.body.data.refresh_token);
%}

租户用户登录

### 获取验证码 ### http://localhost:18891/auth/captcha/codes?codeKey=5jXzuwcoUzbtnHNh GET {{baseUrl}}/auth/captcha/codes?codeKey=applezrgegbtnHNrefh x-tenant-id: apple ### [master]密码模式登录 POST {{baseUrl}}/auth/login Content-Type: application/json x-tenant-id: apple User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0 { "username": "apple", "password": "apple", "codeKey": "applezrgegbtnHNrefh", "codeText": "9xwbm" } > {% client.global.set("tokenValue", response.body.data.access_token); client.global.set("refreshToken", response.body.data.refresh_token); %}

执行获取默认租户的角色列表

多租户数据库设计 mysql 多租户数据库连接池_mysql_04