多租户分库分表技术文档

分库分表技术文档
2022-07-13 李某某

1需求

1.1多租户实现分库分表

1.2系统实现主从数据源

2系统功能

2.1多租户实现分库分表

2.1.1功能描述

根据租户的数量和租户的自定义编号实现对应的分库和分表.假设现有租户1001-1006,2001-2005,并且此时单个库无法存储全部信息,因此将1001-1006划分到一个数据库,将2001-2005划分到一个数据库实现分库.
假设每个租户数据都存储在表t_sys_org中,根据租户数据隔离的原则,依据租户自定义编号(即create_by字段,可以自定义),对表进行划分,模式为t_sys_org_${create_by},例如t_sys_org_1001,t_sys_org_2001,t_sys_org_2002.
2.1.2系统配置
A.Docker配置
利用1个mysql,1个shardingsphere-proxy容器模拟实现,配置如下
图1 nysql安装

                                  																	图2 sharding安装

B.Shardingsphere配置
Shardingsphere-proxy主要配置server.xml以及config-sharding.xml,其中server.xml配置模拟数据库连接时的用户名和密码.这里是root,root

																									图3.server.xml配置

图4模拟mysql连接
Config-sharding.xml主要配置需要分库分表的数据源,数据表的真实位置,以及分库分表的策略.
其中schemaName即在模拟连接中显示的数据库名称,

图5 模拟的数据库名称

图6模拟效果
datasources部分不再赘述,一般的数据源连接模式,其中ds_0代表第一个库,ds-1代表第二个库

rules部分规定了分库分表的具体规则.actualDataNodes代表数据真实的存储位置,这里用到了grvvoy的inline语法,ds_0.t_sys_log_KaTeX parse error: Expected group after '_' at position 28: …,ds_1.t_sys_log_̲{2001…2005}代表在ds_0库的t_sys_log1001到t_sys_log_1006中,以及ds_1库中的t_sys_log2001到t_sys_log_2005中.

图7真实存储位置

tableStrategy规定了分表策略具体来说是利用哪一个字段分表,shardingColumn中填写要分表的字段,shardingAlgorithmName规定了设置中该字段的名字

图8分表用到的字段
bindingTables规定了使用库中的若干表,用逗号隔开即可

图9使用到的表

defaultDatabaseStrategy代表默认的分库策略,也是设置利用哪一个字段分库,shardingColumn中填写要分库的字段,shardingAlgorithmName规定了设置中该字段的名字

图10 分库使用到的字段
defaultTableStrategy是默认分表用到的字段,我们未用到
shardingAlgorithms代表分库分表算法,database_inlin是分库字段,即craete_by字段,create_by_inline是分表字段,也是create_by

algorithm-expression
是具体的算法类型.我们利用craete_by/2000来区分库(<1000/2000=0,>=2000/2000=1),这里grvvoy不同于Java会出现小数,因此利用函数intdiv取整.同样create_by_inline是分表字段,我们直接用create_by字段分表.

图11 具体的算法

2.2多数据源

2.2.1功能描述

不需要分部分表的字段使用主数据源,需要分部分表的字段使用模拟数据源

2.2.2系统配置

使用mybatis-plus实现多数据源需要配置文件设置主从数据源,并且规定mysqlxml位置

图12 整体结构
将分部分表的数据源配置为从数据源

图13 从数据源配置
经常访问的设置为主数据源

图14配置主数据源
在yml中做好数据源连接以及xml位置配置即可

图15 application.yml

3附件

3.1Server.yaml

rules:

• !AUTHORITY
  users:
  • root@%:root # @:
  • sharding@:sharding
    provider:
    type: ALL_PRIVILEGES_PERMITTED

3.2 Config-sharding.yaml

schemaName: sharding_proxy_db

dataSources:
ds_0:
url: jdbc:mysql://server:3317/ssp_0?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://server:3317/ssp_1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:

• !SHARDING
  tables:
  t_sys_log:
  actualDataNodes: ds_0.t_sys_log_KaTeX parse error: Expected group after '_' at position 28: …,ds_1.t_sys_log_̲{2001…2005}
  tableStrategy:
  standard:
  shardingColumn: create_by
  shardingAlgorithmName: create_by_inline
  keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake
  bindingTables:
  • t_sys_log
    defaultDatabaseStrategy:
    standard:
    shardingColumn: create_by
    shardingAlgorithmName: database_inline
    defaultTableStrategy:
    none:
    shardingAlgorithms:
    database_inline:
    type: INLINE
    props:
    algorithm-expression: ds_KaTeX parse error: Expected group after '_' at position 97: …sion: t_sys_log_̲{create_by}

3.3Sql

CREATE DATABASE ssp_0 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE ssp_0;

– auto-generated definition
create table t_sys_log_1001
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;

– auto-generated definition
create table t_sys_log_1002
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
– auto-generated definition
create table t_sys_log_1003
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
– auto-generated definition
create table t_sys_log_1004
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
– auto-generated definition
create table t_sys_log_1005
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
– auto-generated definition

CREATE DATABASE ssp_1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE ssp_1;

– auto-generated definition
create table t_sys_log_2001
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;

– auto-generated definition
create table t_sys_log_2002
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
create table t_sys_log_2003
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
create table t_sys_log_2004
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;
create table t_sys_log_2005
(
id varchar(32) not null comment ‘日志ID’
primary key,
application_name varchar(32) not null comment ‘服务名称’,
log_type int(2) default 0 not null comment ‘日志类型（0-未知、1-登录日志、2-操作日志、3-定时任务日志）’,
operate_type int(2) null comment ‘操作类型（0-未知、1-查询、2-添加、3-更新、4-删除、5-导入、6-导出）’,
log_content varchar(1000) null comment ‘日志描述信息’,
ip varchar(100) null comment ‘IP’,
method varchar(500) null comment ‘请求java方法’,
request_url varchar(255) null comment ‘请求路径’,
request_param longtext null comment ‘请求参数’,
request_type varchar(10) null comment ‘请求类型’,
cost_time bigint null comment ‘耗时(单位：毫秒)’,
create_by varchar(32) null comment ‘创建人’,
create_time datetime null comment ‘创建时间’,
update_by varchar(32) null comment ‘更新人’,
update_time datetime null comment ‘更新时间’
)
comment ‘系统日志表’ charset = utf8mb4;