At present, the company’s products are external enterprise services. After joining the company, I learned about the SaaS model and private deployment. When I first heard about SaaS, I didn’t quite understand it. After consulting materials, and in the subsequent research and development of functions, I have deepened my understanding of multi-tenant.

So let’s ask ourselves a few questions :1. What is multi-tenant architecture? 2. Multi-tenant architecture and its advantages and disadvantages? 3. What is the application scenario of multi-tenant architecture?

I. What is multi-tenancy

Multi-tenancy technology, or multi-tenancy technology, or SaaS, is a software architecture technology that enables the sharing of the same system or program components in a multi-user environment (usually for enterprise users) and ensures the isolation of data among users. Simply put: A single application instance runs on a server, which provides services for multiple tenants (customers). Multi-tenant is an architecture that enables the use of the same application in a multi-user environment and ensures data isolation between users. Then the key point is very easy to understand. The key point of multi-tenant is to realize the isolation of multi-user data under the same set of applications.

Multi-tenant architecture and data isolation scheme

Multi-tenant data storage schemes are as follows:

1. Independent database

That is, one database per tenant. This scheme has the highest level of user data isolation and the best security, but the cost is high.

  • Advantages: Provide independent databases for different tenants, which helps simplify the extended design of the data model and meet the unique needs of different tenants; In the event of a failure, data recovery is relatively simple.

  • Disadvantages: Increased number of database installations, resulting in increased maintenance and acquisition costs.

2. Shared database and independent Schema

This means that multiple or all tenants share the Database, but each tenant has a Schema (also called a user). The underlying libraries are: DB2, ORACLE, etc., a database can have multiple schemas.

  • Advantages: Provides a certain degree of logical data isolation, but not complete isolation, for tenants with high security requirements. Each database can support a larger number of tenants.

  • Disadvantages: In the event of a failure, data recovery is difficult because restoring the database involves data of other tenants;

3. Share databases, schemas, and tables

That is to say, we share a database and a table using fields for data isolation

That is, tenants share the same Database and Schema, but add TenantID multi-tenant data fields to the table. This is the most shared and least isolated pattern.

In simple terms, each insert of data requires a customer’s identity. In this way, the data of different customers can be distinguished in the same table, which is what our system currently uses (tenant_id).

  • Advantages: Compared with the three solutions, the third solution has the lowest maintenance and acquisition cost and allows the largest number of tenants per database.

  • Disadvantages: The lowest isolation level, the lowest security, need to increase the amount of security development in the design and development; Data backup and restoration are the most difficult, requiring table by table backup and restoration.

Iii. Scenarios for multi-tenant Architecture?

The main consideration in measuring the three modes is whether they are isolated or shared. 1. The better the isolation of cost factors, the higher the difficulty and cost of design and implementation, and the higher the initial cost. The better the sharing, the more users supported under the same operating cost, the lower the operating cost.

2. Security Factors Consider the security requirements of the business and customers. The higher the security requirements, the greater the preference for isolation.

3. In terms of the number of tenants, the following factors are mainly considered

  • How many tenants does the system support? Hundreds of? Thousands or tens of thousands? The more possible tenants, the more likely they are to share.

  • Average space required by each tenant to store data. The more data you store, the more you tend to segregate.

  • The number of end users accessing the system simultaneously per tenant. The more support they need, the more they tend to segregate.

  • Whether you want to provide additional services for each tenant, such as data backup and recovery. The more demand there is, the more isolation there is

4. The higher the sharing of technology reserves, the higher the requirements for technology.

Iv. Technical implementation

Here we choose the third scheme (shared database, shared Schema, shared data table) to achieve, which means that each data table needs a tenant id (tenant_id).

Now there is a database table (user) as follows:

The field name The field type describe
id int(11) A primary key
name varchar(30) The name
tenant_id int(11) Multi-tenant id

Take tenant_ID as a tenant ID, which is used to isolate data between tenants. To query the user of the current tenant, the SQL is as follows:

SELECT * FROM user WHERE tenant_id = 1;Copy the code

Suppose we add AND tenant_id =? To all other tenant-related tables except those shared by the system. Query conditions, data tables will be omitted when the situation leads to data leakage. Mybatis – Plus allows you to easily implement multi-tenant SQL parsers

Enter the demo of setting up the theme environment

1. Create a Spring Boot project

Pom file:

<? The XML version = "1.0" encoding = "utf-8"? >The < 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" >The < 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 - the SNAPSHOT < / version >    <name>mybatis-plus-multi-tenancy</name><description> Multi-tenant architecture based on 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 depend on -- -- >        <dependency>            <groupId>com.baomidou</groupId>            <artifactId>mybatis-plus-boot-starter</artifactId>            <version>${mybatis-plus.version}</version>        </dependency><! -- Test dependencies -->        <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>Copy the code

application.properties

# data source configurationspring.datasource.type=com.zaxxer.hikari.HikariDataSourcespring.datasource.hikari.minimum-idle=3spring.datasource.hikari.maximum-pool-size=10Cannot be less than 30 seconds, otherwise default back to 1800 secondsspring.datasource.hikari.max-lifetime=30000spring.datasource.hikari.connection-test-query=SELECT 1spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3306/multi? useUnicode=true&characterEncoding=UTF-8spring.datasource.username=rootspring.datasource.password=rootlogging.level.com.xd.mybatisplusmultitenancy=debugCopy the code

The corresponding SQL database initializes the schema file

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 'tenant_id ',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;SET FOREIGN_KEY_CHECKS = 1;Copy the code

MybatisPlusConfig Core configuration: TenantSqlParser Multi-tenant processor

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* @ Description tenants processor - mainly realize the mybatis - plus https://mp.baomidou.com/guide/tenant.html* @author Created by Lihaodong (alias: xiaodong) [email protected] * @Date 2019-08-09 23:34* @ Version 1.0* /@Slf4j@Componentpublic class MyTenantHandler implements TenantHandler {/ * ** Multi-tenant identifier* /    private static final String SYSTEM_TENANT_ID = "tenant_id";/ * ** Tables to be filtered* /    private static final List<String> IGNORE_TENANT_TABLES = new ArrayList<>();    @Autowired    private MyContext apiContext;/ * ** the tenant Id     *     * @return* /    @Override    public Expression getTenantId() {// Fetch the service provider ID of the current request from the current system context and inject it into SQL through the parser.        Long tenantId = apiContext.getCurrentTenantId();Log.debug (" Current tenant is {}", tenantId);        if (tenantId == null) {            return new NullValue();        }        return new LongValue(tenantId);    }/ * ** Tenant field name     *     * @return* /    @Override    public String getTenantIdColumn() {        return SYSTEM_TENANT_ID;    }/ * ** Determine whether to filter based on the table name* Omit some tables: for example, the sys_tenant table does not need to perform such processing itself     *     * @param tableName     * @return* /    @Override    public boolean doTableFilter(String tableName) {        return IGNORE_TENANT_TABLES.stream().anyMatch((e) -> e.equalsIgnoreCase(tableName));    }}Copy the code

MybatisPlus configuration

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* @author Created by Lihaodong (alias: xiaodong) [email protected] * @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 parsing processing interception: added tenant processing callback.        List<ISqlParser> sqlParserList = new ArrayList<>();// Attack SQL to block the parser and join the parse chain        sqlParserList.add(new BlockAttackSqlParser());// Multi-tenant interception        TenantSqlParser tenantSqlParser = new TenantSqlParser();        tenantSqlParser.setTenantHandler(myTenantHandler);        sqlParserList.add(tenantSqlParser);        paginationInterceptor.setSqlParserList(sqlParserList);        return paginationInterceptor;    }/ * ** Performance analysis interceptor, not recommended for production use* Used to observe the SQL execution and execution duration* /    @Bean(name = "performanceInterceptor")    public PerformanceInterceptor performanceInterceptor() {        return new PerformanceInterceptor();    }}Copy the code

Customize the context of the system

package com.xd.mybatisplusmultitenancy.config;import org.springframework.stereotype.Component;import java.util.Map;import java.util.concurrent.ConcurrentHashMap;/ * * * @Classname ApiContext* @description Specifies the current system context* @author Created by Lihaodong (alias: xiaodong) [email protected] * @Date 2019-08-09 22:47* @ Version 1.0* /@Componentpublic 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);    }}Copy the code

Entity, Mapper omitted…

Unit testing

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* @author Created by Lihaodong (alias: xiaodong) [email protected] * @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;/ * ** Emulates the multi-tenant Id of the current system* /    @Before    public void before() {// Set the current multi-tenant ID in the context        apiContext.setCurrentTenantId(1L);    }    @Test    public void insert() {// Add dataUser User = new User().setName(" xiao Min ");// Determine whether a condition is true or false        Assert.assertTrue(userMapper.insert(user) > 0);        user = userMapper.selectById(user.getId());Log.info (" Insert data :{}", user);// Check whether it is equal        Assert.assertEquals(apiContext.getCurrentTenantId(), user.getTenantId());    }    @Test    public void selectList() {        userMapper.selectList(null).forEach((e) -> {Log.info (" Query data {}", e);            Assert.assertEquals(apiContext.getCurrentTenantId(), e.getTenantId());        });    }}Copy the code

Run results to insert data

The 2019-08-23 22:32:52. 77902-755 the INFO [main] O.S.S.C oncurrent. ThreadPoolTaskExecutor: Initializing ExecutorService 'applicationTaskExecutor'The 22:32:53 2019-08-23. 77902-210 the INFO [main]. MybatisPlusMultiTenancyApplicationTests: Started MybatisPlusMultiTenancyApplicationTests in 5.181 seconds (JVM running for 6.86)The 2019-08-23 22:32:53. 77902-613 the DEBUG [main] C.X.M.C onfig. MyTenantHandler: the current tenant to 1The 2019-08-23 22:32:53. 77902-614 the DEBUG [main] C.X.M.M apper. UserMapper. Insert: = = > Preparing: INSERT INTO user (name, tenant_id) VALUES (? 1),The 2019-08-23 22:32:53. 77902-648 the DEBUG [main] C.X.M.M apper. UserMapper. Insert: = = > the Parameters: xiao Ming (String)The 2019-08-23 22:32:53. 77902-701 the DEBUG [main] C.X.M.M apper. UserMapper. Insert: < = = Updates: 1Time: 64 ms - the ID: com. Xd. Mybatisplusmultitenancy. Mapper. UserMapper. InsertExecute SQL: INSERT INTO user (name, tenant_id) VALUES (' xiaomin ', 1)The 2019-08-23 22:32:53. 77902-720 the DEBUG [main] C.X.M.C onfig. MyTenantHandler: the current tenant to 1The 2019-08-23 22:32:53. 77902-722 the DEBUG [main] C.X.M.M apper. UserMapper. SelectById: = = > Preparing: SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 1 AND id = ?The 2019-08-23 22:32:53. 77902-726 the DEBUG [main] C.X.M.M apper. UserMapper. SelectById: = = > the Parameters: 1 (Long)The 2019-08-23 22:32:53. 77902-745 the DEBUG [main] C.X.M.M apper. UserMapper. SelectById: < = = Total: 1Time: 20 ms - the ID: com. Xd. Mybatisplusmultitenancy. Mapper. UserMapper. SelectByIdExecute SQL: SELECT ID, name, tenant_id FROM user WHERE user.tenant_id = 1 AND ID = 1The 22:32:53 2019-08-23. 77902-746 the INFO [main]. MybatisPlusMultiTenancyApplicationTests: Insert data :User(id=1, name= xiaoming, tenantId=1)The 22:32:53 2019-08-23. 77902-762 the INFO/Thread - 2 O.S.S.C oncurrent. ThreadPoolTaskExecutor: Shutting down ExecutorService 'applicationTaskExecutor'The 2019-08-23 22:32:53. 77902-764 the INFO/Thread - 2 com. Zaxxer. Hikari. HikariDataSource: HikariPool-1 - Shutdown initiated...The 2019-08-23 22:32:53. 77902-777 the INFO/Thread - 2 com. Zaxxer. Hikari. HikariDataSource: HikariPool-1 - Shutdown completed.Copy the code

Query data

The 2019-08-23 22:34:26. 77922-700 the INFO [main] O.S.S.C oncurrent. ThreadPoolTaskExecutor: Initializing ExecutorService 'applicationTaskExecutor'The 22:34:27 2019-08-23. 77922-100 the INFO [main]. MybatisPlusMultiTenancyApplicationTests: Started MybatisPlusMultiTenancyApplicationTests in 4.521 seconds (JVM running for 6.268)The 2019-08-23 22:34:27. 77922-412 the DEBUG [main] C.X.M.C onfig. MyTenantHandler: the current tenant to 1The 2019-08-23 22:34:27. 77922-414 the DEBUG [main] C.X.M.M apper. UserMapper. SelectList: = = > Preparing: SELECT id, name, tenant_id FROM user WHERE user.tenant_id = 1The 2019-08-23 22:34:27. 77922-442 the DEBUG [main] C.X.M.M apper. UserMapper. SelectList: = = > the Parameters:The 2019-08-23 22:34:27. 77922-464 the DEBUG [main] C.X.M.M apper. UserMapper. SelectList: < = = Total: 1Time: 22 ms - the ID: com. Xd. Mybatisplusmultitenancy. Mapper. UserMapper. SelectListExecute SQL: SELECT ID, name, tenant_id FROM user WHERE user.tenant_id = 1The 22:34:27 2019-08-23. 77922-467 the INFO [main]. MybatisPlusMultiTenancyApplicationTests: Query data User(id=1, name= xiaoming, tenantId=1)The 22:34:27 2019-08-23. 77922-480 the INFO/Thread - 2 O.S.S.C oncurrent. ThreadPoolTaskExecutor: Shutting down ExecutorService 'applicationTaskExecutor'The 2019-08-23 22:34:27. 77922-482 the INFO/Thread - 2 com. Zaxxer. Hikari. HikariDataSource: HikariPool-1 - Shutdown initiated...The 2019-08-23 22:34:27. 77922-492 the INFO/Thread - 2 com. Zaxxer. Hikari. HikariDataSource: HikariPool-1 - Shutdown completed.Copy the code

It is not difficult to see from the printed log that the current scheme is relatively perfect, only simple configuration, so that the developer is very convenient to develop, and at the same time to ensure the security of the data to the greatest extent

The source code download: https://github.com/LiHaodong888/SpringBootLearn

Specific project :https://gitee.com/li_haodong/pre

The resources

https://www.cnblogs.com/pingfan21/p/7478242.html  https://segmentfault.com/a/1190000017197768Copy the code