基于springboot+jpa 实现多租户动态切换多数据源 - 数据隔离方案选择分库还是分表

一时失言乱红尘 2023-09-23 20:27 149阅读 0赞

多租户动态多数据源系列  
1、基于springboot+jpa 实现多租户动态切换多数据源 - 数据隔离方案选择分库还是分表  
2、[基于springboot+jpa 实现多租户动态切换多数据源 - 基于dynamic-datasource实现多租户动态切换数据源][springboot_jpa _ - _dynamic-datasource]  
3、[基于springboot+jpa 实现多租户动态切换多数据源 - 使用Flyway实现多数据源数据库脚本管理和迭代更新][springboot_jpa _ - _Flyway]

#### 目录 ####

*  需求背景
 *  数据隔离方案考究
 *   *  自身业务现状
     *   *  数据库现状
         *  业务其他现状
     *  mysql数据库现状
     *  分库还是分表
     *  水平或垂直分库分表利弊分析
     *  最终结论
 *  参考

## 需求背景 ##

**项目当前架构**：当前架构数据共享仅支持在跨机构之间，如果集团内部如果需要不同子公司做数据共享，则需要子公司分别单独部署该系统。

**新需求**：实际场景中，集团可能统一部署该服务，不希望每个子公司单独部署和运维，子公司更多的是是使用者的角色。 因此***既要**满足集团之间数据共享（一个集团部署一个项目），**又要**满足集团内部子公司之间数据共享（还是集团只部署一个项目，子公司共用该平台，但要做到数据隔离），**还要**满足公司内部数据共享。*

## 数据隔离方案考究 ##

> 究竟是采用分库还是分表，在参考了诸多前辈的文章后，对我所做的业务进行了一定程度的分析，分析方面主要为两个方向：一是自身业务压力的承载能力和业务流量特点；二是所采用的数据库和服务器本身的承载能力

### 自身业务现状 ###

#### 数据库现状 ####

现有模式下数据库表数量：70个（项目启动后固定生成的数据表）+ n（用户可自由导入数据生成的数据表）  
预估一般子机构数量：200内  
机构及子机构存储的数据总量：由机构及子机构决定（未知：(70+n)\*200）

#### 业务其他现状 ####

*  无峰谷流量波动，较为平稳
 *  数据库操作读多写少
 *  没有使用缓存中间件技术 - 暂不需要

--------------------

### mysql数据库现状 ###

*  **最大连接数**：Mysql5.5～5.7：默认的最大连接数都是151，上限为：100000 ；Mysql5.0版本：默认的最大连接数为100，上限为16384
 *  **表数量**：一般最多20亿个表，InnoDB允许多达 40 亿张表。（底层文件系统可能对代表表的文件数量有限制）
 *  **数据库的数量限制**：Mysql对数据库的数量没有限制。底层文件系统可能对目录的数量有限制。

有一组数据可以参考：库物理文件大小<100G，表<100，字段<200，单表记录数<500W  
此范围内的写入读取性能是比较好的

--------------------

### 分库还是分表 ###

<table> 
 <thead> 
  <tr> 
   <th>分析点</th> 
   <th>分表</th> 
   <th>分库</th> 
   <th>分库还是分表</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>数据库数量</td> 
   <td>所有机构共用一个数据库</td> 
   <td>机构数量决定 预估200内</td> 
   <td><font><strong>分库</strong></font>较好（单库能够支撑的并发量是有限的；业务量剧增，单库数据量越来越大，给存储造成巨大压力）</td> 
  </tr> 
  <tr> 
   <td>单库表数量</td> 
   <td>(70+n)*200 &gt; 1.4w</td> 
   <td>70+n</td> 
   <td><font><strong>分库</strong></font>较好（分表情况下单库的表数量较多，且表数量也不可估量；SQL 操作会变慢）</td> 
  </tr> 
  <tr> 
   <td>单库数据量</td> 
   <td>集团及子机构的数据量总和</td> 
   <td>具体单个机构的数据量</td> 
   <td><font><strong>分库</strong></font>（子机构数据总量不可估量）</td> 
  </tr> 
  <tr> 
   <td>连接数</td> 
   <td>机构及子机构连接数总和</td> 
   <td>最大为机构及子机构连接数总和</td> 
   <td><font><strong>分库</strong></font>（分表情况下，若每个机构同时取得100个连接，则100*200&gt;16384，易出现数据库连接数过多问题）</td> 
  </tr> 
  <tr> 
   <td>读写压力</td> 
   <td>集中在一个库</td> 
   <td>分散在多个库，减轻压力</td> 
   <td><font><strong>分库</strong></font>（分表情况下增加了数据库读写压力）</td> 
  </tr> 
 </tbody> 
</table>

### 水平或垂直分库分表利弊分析 ###

<table> 
 <thead> 
  <tr> 
   <th>水平or垂直区分</th> 
   <th>垂直分库</th> 
   <th>垂直分表</th> 
   <th>水平分库</th> 
   <th>水平分表</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>判断依据</td> 
   <td>根据业务耦合性，将关联度低的不同表存储在不同的数据库，如按业务分类进行独立划分</td> 
   <td>基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中</td> 
   <td>当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了</td> 
   <td>根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果</td> 
  </tr> 
  <tr> 
   <td>优点</td> 
   <td>1. 解决业务系统层面的耦合，业务清晰；2. 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等；3. 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈</td> 
   <td>1. 解决业务系统层面的耦合，业务清晰；2. 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等；3. 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈</td> 
   <td>1. 不存在单库数据量过大、高并发的性能瓶颈，提升了系统稳定性和负载能力； 2. 应用端改造较小，不需要拆分业务模块</td> 
   <td>应用端改造较小，不需要拆分业务模块</td> 
  </tr> 
  <tr> 
   <td>缺点</td> 
   <td>1. 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度；2. 分布式事务处理复杂；3. 依然存在单表数据量过大的问题（需要水平切分）</td> 
   <td>1. 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度；2. 分布式事务处理复杂；3. 依然存在单表数据量过大的问题（需要水平切分）</td> 
   <td>1. 跨分片的事务一致性难以保证；2. 跨库的join关联查询性能较差；3. 数据多次扩展难度和维护量极大</td> 
   <td>1. 数据多次扩展难度和维护量极大；2. 库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO</td> 
  </tr> 
 </tbody> 
</table>

### 最终结论 ###

**采用水平分库方案**

## 参考 ##

*  [MySQL 5.7 参考手册][MySQL 5.7]
 *  [MySQL分库分表方案][MySQL]
 *  [“分库分表" ？选型和流程要慎重，否则会失控][Link 1]
 *  [MySQL：互联网公司常用分库分表方案汇总！][MySQL 1]
 *  [水平分库分表的关键步骤以及可能遇到的问题][Link 2]
 *  [有关分库分表 ShardingSphere-JDBC，这是我见过整理的最全的笔记了][ShardingSphere-JDBC]

[springboot_jpa _ - _dynamic-datasource]: https://kunzhi.blog.csdn.net/article/details/128470979
[springboot_jpa _ - _Flyway]: https://kunzhi.blog.csdn.net/article/details/128482197
[MySQL 5.7]: https://dev.mysql.com/doc/refman/5.7/en/database-count-limit.html
[MySQL]: https://zhuanlan.zhihu.com/p/84224499
[Link 1]: https://cloud.tencent.com/developer/article/1511810
[MySQL 1]: https://cloud.tencent.com/developer/article/1623139
[Link 2]: https://www.infoq.cn/article/key-steps-and-likely-problems-of-horizontal-split-table
[ShardingSphere-JDBC]: https://xie.infoq.cn/article/c36e7ca5e9c353d4d881671db