赞
踩
多租户技术或称多重租赁技术,简称SaaS
,是一种软件架构技术,是实现如何在多用户环境下(多用户一般是面向企业用户)共用相同的系统或程序组件,并且可确保各用户间数据的隔离性。简单讲:在一台服务器上运行单个应用实例,它为多个租户(客户)提供服务。从定义中我们可以理解:多租户是一种架构,目的是为了让多用户环境下使用同一套程序,且保证用户间数据隔离。那么重点就很浅显易懂了,多租户的重点就是同一套程序下实现多用户数据的隔离。
即一个租户一个数据库,这种方案的用户数据隔离级别最高,安全性最好,但成本较高。
也就是说 共同使用一个数据库 使用表进行数据隔离
多个或所有租户共享Database,但是每个租户一个Schema(也可叫做一个user)。底层库比如是:DB2、ORACLE等,一个数据库下可以有多个SCHEMA。
优点:为安全性要求较高的租户提供了一定程度的逻辑数据隔离,并不是完全隔离;每个数据库可支持更多的租户数量。
缺点:如果出现故障,数据恢复比较困难,因为恢复数据库将牵涉到其他租户的数据;
也就是说 共同使用一个数据库一个表 使用字段进行数据隔离
即租户共享同一个Database、同一个Schema,但在表中增加TenantID多租户的数据字段。这是共享程度最高、隔离级别最低的模式。
简单来讲,即每插入一条数据时都需要有一个客户的标识。这样才能在同一张表中区分出不同客户的数据,这也是我们系统目前用到的(tenant_id)
衡量三种模式主要考虑的因素是隔离还是共享
。
1.成本角度因素
隔离性越好,设计和实现的难度和成本越高,初始成本越高。共享性越好,同一运营成本下支持的用户越多,运营成本越低。
2.安全因素
要考虑业务和客户的安全方面的要求。安全性要求越高,越要倾向于隔离。
3.从租户数量上考虑
主要考虑下面一些因素
4.技术储备
共享性越高,对技术的要求越高。
技术选型: Mybatis-Plus
这里我们选用了第三种方案(共享数据库,共享 Schema,共享数据表)来实现,也就意味着,每个数据表都需要有一个租户标识(tenant_id)
现在有数据库表(user)如下:
字段名 | 字段类型 | 描述 |
---|---|---|
id | int(11) | 主键 |
name | varchar(30) | 姓名 |
tenant_id | int(11) | 多租户id |
将tenant_id视为租户ID,用来隔离租户与租户之间的数据,如果要查询当前租户的用户,SQL大致如下:
SELECT * FROM user WHERE tenant_id = 1;
试想一下,除了一些系统共用的表以外,其他租户相关的表,我们都需要加上AND tenant_id = ?查询条件,数据表多的情况时就会漏加导致数据泄露。
幸亏有mybatis-plus这个插件,可以极为方便的实现多租户SQL解析器,官方文档如下:
多租户 SQL 解析器
环境搭建演示
pom.xml 文件
- <?xml version="1.0" encoding="UTF-8"?>
- <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
- <modelVersion>4.0.0</modelVersion>
- <parent>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-parent</artifactId>
- <version>2.1.7.RELEASE</version>
- <relativePath/>
- </parent>
- <groupId>com.xd</groupId>
- <artifactId>mybatis-plus-multi-tenancy</artifactId>
- <version>0.0.1-SNAPSHOT</version>
- <name>mybatis-plus-multi-tenancy</name>
- <description>基于Spring Boot Mybatis-Plus的多租户架构</description>
-
-
- <properties>
- <java.version>1.8</java.version>
- <mybatis-plus.version>3.1.2</mybatis-plus.version>
- </properties>
-
-
- <dependencies>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-web</artifactId>
- </dependency>
- <!--mysql-->
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <scope>runtime</scope>
- </dependency>
- <!--lombok-->
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <optional>true</optional>
- </dependency>
- <!--Mybatis-Plus依赖-->
- <dependency>
- <groupId>com.baomidou</groupId>
- <artifactId>mybatis-plus-boot-starter</artifactId>
- <version>${mybatis-plus.version}</version>
- </dependency>
- <!--测试相关依赖-->
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-test</artifactId>
- <scope>test</scope>
- </dependency>
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-test</artifactId>
- <version>5.2.0.M1</version>
- <scope>compile</scope>
- </dependency>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-test</artifactId>
- </dependency>
- </dependencies>
-
-
- <build>
- <plugins>
- <plugin>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-maven-plugin</artifactId>
- </plugin>
- </plugins>
- </build>
-
-
- </project>
-
application.properties
- # 数据源配置
- spring.datasource.type=com.zaxxer.hikari.HikariDataSource
- spring.datasource.hikari.minimum-idle=3
- spring.datasource.hikari.maximum-pool-size=10
- # 不能小于30秒,否则默认回到1800秒
- spring.datasource.hikari.max-lifetime=30000
- spring.datasource.hikari.connection-test-query=SELECT 1
- spring.datasource.driver-class-name=com.mysql.jdbc.Driver
- spring.datasource.url=jdbc:mysql://localhost:3306/multi?useUnicode=true&characterEncoding=UTF-8
- spring.datasource.username=root
- spring.datasource.password=root
-
- logging.level.com.xd.mybatisplusmultitenancy=debug
对应的SQL数据库初始化schema文件
- SET NAMES utf8mb4;
- SET FOREIGN_KEY_CHECKS = 0;
-
- -- ----------------------------
- -- Table structure for user
- -- ----------------------------
- DROP TABLE IF EXISTS `user`;
- CREATE TABLE `user` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
- `name` varchar(30) DEFAULT NULL COMMENT '姓名',
- `tenant_id` int(11) NOT NULL COMMENT '多租户ID',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
- SET FOREIGN_KEY_CHECKS = 1;
MybatisPlusConfig
核心配置:TenantSqlParser
多租户处理器
- package com.xd.mybatisplusmultitenancy.config;
-
- import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler;
- import lombok.extern.slf4j.Slf4j;
- import net.sf.jsqlparser.expression.Expression;
- import net.sf.jsqlparser.expression.LongValue;
- import net.sf.jsqlparser.expression.NullValue;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Component;
-
- import java.util.ArrayList;
- import java.util.List;
-
- /**
- * @Classname PreTenantHandler
- * @Date 2019-08-09 23:34
- * @Version 1.0
- */
- @Slf4j
- @Component
- public class MyTenantHandler implements TenantHandler {
-
- /**
- * 多租户标识
- */
- private static final String SYSTEM_TENANT_ID = "tenant_id";
-
- /**
- * 需要过滤的表
- */
- private static final List<String> IGNORE_TENANT_TABLES = new ArrayList<>();
-
- @Autowired
- private MyContext apiContext;
-
-
- /**
- * 租户Id
- *
- * @return
- */
- @Override
- public Expression getTenantId() {
- // 从当前系统上下文中取出当前请求的服务商ID,通过解析器注入到SQL中。
- Long tenantId = apiContext.getCurrentTenantId();
- log.debug("当前租户为{}", tenantId);
- if (tenantId == null) {
- return new NullValue();
- }
- return new LongValue(tenantId);
- }
-
- /**
- * 租户字段名
- *
- * @return
- */
- @Override
- public String getTenantIdColumn() {
- return SYSTEM_TENANT_ID;
- }
-
- /**
- * 根据表名判断是否进行过滤
- * 忽略掉一些表:如租户表(sys_tenant)本身不需要执行这样的处理
- *
- * @param tableName
- * @return
- */
- @Override
- public boolean doTableFilter(String tableName) {
- return IGNORE_TENANT_TABLES.stream().anyMatch((e) -> e.equalsIgnoreCase(tableName));
- }
- }
MybatisPlus的配置
- package com.xd.mybatisplusmultitenancy.config;
-
- import com.baomidou.mybatisplus.core.parser.ISqlParser;
- import com.baomidou.mybatisplus.extension.parsers.BlockAttackSqlParser;
- import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
- import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
- import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler;
- import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;
- import org.mybatis.spring.annotation.MapperScan;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import net.sf.jsqlparser.expression.Expression;
- import net.sf.jsqlparser.expression.LongValue;
-
- import java.util.ArrayList;
- import java.util.List;
-
- /**
- * @Classname MybatisPlusConfig
- * @Description TODO
- * @Date 2019-08-09 22:44
- * @Version 1.0
- */
- @Configuration
- @MapperScan("com.xd.mybatisplusmultitenancy.mapper")
- public class MybatisPlusConfig {
-
-
- @Autowired
- private MyTenantHandler myTenantHandler;
-
- @Bean
- public PaginationInterceptor paginationInterceptor() {
- PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
-
- // SQL解析处理拦截:增加租户处理回调。
-
- List<ISqlParser> sqlParserList = new ArrayList<>();
- // 攻击 SQL 阻断解析器、加入解析链
- sqlParserList.add(new BlockAttackSqlParser());
- // 多租户拦截
- TenantSqlParser tenantSqlParser = new TenantSqlParser();
- tenantSqlParser.setTenantHandler(myTenantHandler);
- sqlParserList.add(tenantSqlParser);
- paginationInterceptor.setSqlParserList(sqlParserList);
- return paginationInterceptor;
- }
-
- /**
- * 性能分析拦截器,不建议生产使用
- * 用来观察 SQL 执行情况及执行时长
- */
- @Bean(name = "performanceInterceptor")
- public PerformanceInterceptor performanceInterceptor() {
- return new PerformanceInterceptor();
- }
- }
自定义系统的上下文
- package com.xd.mybatisplusmultitenancy.config;
-
-
- import org.springframework.stereotype.Component;
-
-
- import java.util.Map;
- import java.util.concurrent.ConcurrentHashMap;
-
-
- /**
- * @Classname ApiContext
- * @Description 当前系统的上下文
- * @Date 2019-08-09 22:47
- * @Version 1.0
- */
- @Component
- public class MyContext {
-
-
- private static final String KEY_CURRENT_TENANT_ID = "KEY_CURRENT_PROVIDER_ID";
- private static final Map<String, Object> M_CONTEXT = new ConcurrentHashMap<>();
-
-
- public void setCurrentTenantId(Long tenantId) {
- M_CONTEXT.put(KEY_CURRENT_TENANT_ID, tenantId);
- }
-
-
- public Long getCurrentTenantId() {
- return (Long) M_CONTEXT.get(KEY_CURRENT_TENANT_ID);
- }
- }
-
Entity、Mapper 省略...
package com.xd.mybatisplusmultitenancy.test; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.xd.mybatisplusmultitenancy.MybatisPlusMultiTenancyApplication; import com.xd.mybatisplusmultitenancy.config.MyContext; import com.xd.mybatisplusmultitenancy.entity.User; import com.xd.mybatisplusmultitenancy.mapper.UserMapper; import lombok.extern.slf4j.Slf4j; import org.junit.Assert; import org.junit.Before; import org.junit.FixMethodOrder; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.MethodSorters; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.sql.Wrapper; /** * @Classname MybatisPlusMultiTenancyApplicationTests * @Description TODO * @Date 2019-08-09 22:50 * @Version 1.0 */ @Slf4j @RunWith(SpringRunner.class) @FixMethodOrder(MethodSorters.JVM) @SpringBootTest(classes = MybatisPlusMultiTenancyApplication.class) public class MybatisPlusMultiTenancyApplicationTests { @Autowired private MyContext apiContext; @Autowired private UserMapper userMapper; /** * 模拟当前系统的多租户Id */ @Before public void before() { // 在上下文中设置当前多租户id apiContext.setCurrentTenantId(1L); } @Test public void insert() { // 新增数据 User user = new User().setName("小明"); //判断一个条件是true还是false Assert.assertTrue(userMapper.insert(user) > 0); user = userMapper.selectById(user.getId()); log.info("插入数据:{}", user); // 判断是否相等 Assert.assertEquals(apiContext.getCurrentTenantId(), user.getTenantId()); } @Test public void selectList() { userMapper.selectList(null).forEach((e) -> { log.info("查询数据{}", e); Assert.assertEquals(apiContext.getCurrentTenantId(), e.getTenantId()); }); } }
运行结果
插入数据
2019-08-23 22:32:52.755 INFO 77902 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2019-08-23 22:32:53.210 INFO 77902 --- [ main] .MybatisPlusMultiTenancyApplicationTests : Started MybatisPlusMultiTenancyApplicationTests in 5.181 seconds (JVM running for 6.86)
2019-08-23 22:32:53.613 DEBUG 77902 --- [ main] c.x.m.config.MyTenantHandler : 当前租户为1
2019-08-23 22:32:53.614 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.insert : ==> Preparing: INSERT INTO user (name, tenant_id) VALUES (?, 1)
2019-08-23 22:32:53.648 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.insert : ==> Parameters: 小明(String)
2019-08-23 22:32:53.701 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.insert : <== Updates: 1
Time:64 ms - ID:com.xd.mybatisplusmultitenancy.mapper.UserMapper.insert
Execute SQL:INSERT INTO user (name, tenant_id) VALUES ('小明', 1)2019-08-23 22:32:53.720 DEBUG 77902 --- [ main] c.x.m.config.MyTenantHandler : 当前租户为1
2019-08-23 22:32:53.722 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.selectById : ==> Preparing: SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 1 AND id = ?
2019-08-23 22:32:53.726 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.selectById : ==> Parameters: 1(Long)
2019-08-23 22:32:53.745 DEBUG 77902 --- [ main] c.x.m.mapper.UserMapper.selectById : <== Total: 1
Time:20 ms - ID:com.xd.mybatisplusmultitenancy.mapper.UserMapper.selectById
Execute SQL:SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 1 AND id = 12019-08-23 22:32:53.746 INFO 77902 --- [ main] .MybatisPlusMultiTenancyApplicationTests : 插入数据:User(id=1, name=小明, tenantId=1)
2019-08-23 22:32:53.762 INFO 77902 --- [ Thread-2] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'
2019-08-23 22:32:53.764 INFO 77902 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-08-23 22:32:53.777 INFO 77902 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
查询数据
2019-08-23 22:34:26.700 INFO 77922 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2019-08-23 22:34:27.100 INFO 77922 --- [ main] .MybatisPlusMultiTenancyApplicationTests : Started MybatisPlusMultiTenancyApplicationTests in 4.521 seconds (JVM running for 6.268)
2019-08-23 22:34:27.412 DEBUG 77922 --- [ main] c.x.m.config.MyTenantHandler : 当前租户为1
2019-08-23 22:34:27.414 DEBUG 77922 --- [ main] c.x.m.mapper.UserMapper.selectList : ==> Preparing: SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 1
2019-08-23 22:34:27.442 DEBUG 77922 --- [ main] c.x.m.mapper.UserMapper.selectList : ==> Parameters:
2019-08-23 22:34:27.464 DEBUG 77922 --- [ main] c.x.m.mapper.UserMapper.selectList : <== Total: 1
Time:22 ms - ID:com.xd.mybatisplusmultitenancy.mapper.UserMapper.selectList
Execute SQL:SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 12019-08-23 22:34:27.467 INFO 77922 --- [ main] .MybatisPlusMultiTenancyApplicationTests : 查询数据User(id=1, name=小明, tenantId=1)
2019-08-23 22:34:27.480 INFO 77922 --- [ Thread-2] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'
2019-08-23 22:34:27.482 INFO 77922 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-08-23 22:34:27.492 INFO 77922 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
从打印的日志不难看出,目前这个方案还是比较完美的,仅需简单的配置,让开发者极大方便的进行开发,同时又最大程度的保证了数据的安全性
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。