同 database 不同 schema 多租户连接池问题

[dandankele]

我开发的是一个多租户的系统，使用的是mysql，采用的是同 database 不同 schema 方式的数据库隔离方式。每个租户对应一个 schema ，并且拥有特定的 username 和 password 才能连接访问。

目前在用 Hikari 尝试做数据库连接池，由于多租户的特性，我需要延时到 runtime 中才能确定哪个租户，才能使用对应的连接信息建立连接，建立连接时如果不使用 username 和 password 就无法认证和完成连接。但如果使用了 username 和 password 成功建立连接，并放入连接池，等处理下一个租户时，从连接池中拿的连接的 schema 就不对了。

但如果要为每个租户创建一个连接池，有些浪费，首先就是不确定每个租户对数据库的使用程度如何，不可能为每个租户设置特定大小的连接池，否则会存在连接数的浪费，所以势必要采用共享连接池才能最大的利用上。然后就是阿里云的 RDS 一个 database 实例有最大连接数的限制，所以更要求最好是共享连接池。

我也查了下相关的 github ，也有类似的issue还没人解决。而且 mysql 应该是不支持在一个连接中切换 user 的吧，必须 quit 断开连接使用新的 user 重新建立连接。

难道我要创建一个高权限帐号，可以访问所有 schema 才行吗？还有其他方法吗？

有处理过这问题的朋友吗？

[bthulu]

mysql 哪来的 schema? mysql 只有 database 没有 schema!!!

[28Sv0ngQfIE7Yloe]

dynamic-datasource

[root71370]

mybatis 动态数据源

[dandankele]

动态数据源我实现了，现在的问题是如何针对我的场景把连接进行池化复用，各位要审题啊

[RedBeanIce]

提问
1 ，租户数量问题，目前一共有多少呢，一年后一共有多少呢


1 ，如果是租户数量少，一个 schema 一个数据库链接池，就简单解决问题了。不用动太多脑子。

[dandankele]

@RedBeanIce 租户数量适中吧，我也想过一个 schema 一个连接池，然后每个池子最大连接数设置为 database 的最大允许的连接数量，空闲时间设置短一点，一个池子如果空闲连接多的话会释放给其他忙的池子。。但感觉又有些不妥。在每个租户都需要较为繁忙时，某个池子的空闲连接来不及释放给另一个池子

[LeegoYih]

我之前实现的多租户方案是逻辑隔离，所有表都存一个租户 ID ，CRUD 自动拼接租户 ID 。
物理隔离，如果改表结构、维护基础数据还挺难受的，虽有有一些工具可以同步，但是最终还是要是人去检查一遍。

[RedBeanIce]

@dandankele 我没有看过源码，不知道是否支持动态扩容。

HikariCP 的数据库连接池，是否支持动态扩容。如果支持动态扩容的话，那么给每个链接池一个较小的初始数量。
如果是该租户用的人多，那么动态的扩容。。。

[RedBeanIce]

@RedBeanIce 我翻了翻 Hikari 的源码，，好像是支持自己改造数据库链接池的。
可以动态的处理。

[dandankele]

@RedBeanIce 我感觉我这情况已经不是改 Hikari 内部实现的问题了，是 mysql 本身好像就不支持在一个连接中直接切换成另一个用户，不切换成另一个用户就看不到其拥有的 schema = =！

[dandankele]

@LeegoYih 是的啊，各有利弊。。

[Belmode]

其实最合理，最安全，最可靠的方式，就是一个租户一个连接池。
出初始连接池和最大连接池，做个限制

[RedBeanIce]

@dandankele ......所以我表述的是，多个数据库连接池，连接池动态扩容。

[jorneyr]

mysql 的 database 和 schema 是同一个东西，也可以理解 mysql 只有 database ，没有 schema 。
即使是 PostgreSQL 这种支持 database/schema 的，连接也是使用 database ，连接建立后 set search path 指定要访问的 schema 。

[tomorrow092]

感觉这个优点背离池化思想了。本身池化就是 池子里的东西都是一样的。

而你的场景中 每个 connection 都有自己的用户名和密码， 这就导致 你在创建 connection 和 从池子里捞 connection 的时候 掺杂了自己的业务逻辑了。得不偿失，不如每个租户一个连接池。

另一方面，本身就是一个数据库，大家也可以用相同的用户名和密码呀。 把每个租户拥有特定的 username 和 password 才能连接访问 这个逻辑提到上层让业务来保证，没必要依赖数据库的密码和用户名校验把。

[tomorrow092]

@tomorrow092 我号线理解偏差了。

你是 同一个数据库服务器上有多个租户的数据库， 你想搞一个连接池 连接多个库

[lesismal]

看 OP 的需求应该是想 database 隔离、但是怕连接池数量太大吧，如果是这样、好像可以用同一组连接池，语句里指定 database 更好些吧，比如 select * from database.table ，但可能已有代码要改动很多

[siweipancc]

连接建立后不可切换用户, 和你的需求与连接池设计违反, 真有这个需求只能抛弃池.

一个可能的危险设计是: 顶层使用 root, 密码校验交由服务层, 改写 sql 生成逻辑行 java.sql.Connection#nativeSQL

[dandankele]

我在 mysql 官网上看到有提供 C 的[API 接口 mysql_change_user]( https://dev.mysql.com/doc/c-api/8.0/en/mysql-change-user.html)，可以在同一个连接中重置会话，然后又看了下官方提供的 java 的 Driver 和相关代码，在 Connection 里果然发现了类似`changeUser`的封装方法。。看样子得进行一波魔改了。。不知道会不会成功

[dandankele]

另外关于 mysql 中有没有 schema 概念，我也不太清楚哈，没怎么用过其他数据库。。但意思就是那个意思。。每个租户在一个数据库实例中有一个数据库。。另外我看 mysql 术语库中有提到 schema ，https://dev.mysql.com/doc/refman/8.0/en/glossary.html

@lesismal select * from database.table 之前我也看到过，可以算是一个还好的备选方案吧，相比直接在表列上增加租户标识好一点。。


另外每个租户都设置单独用户名和密码主要出于安全考虑，我们是做 toB 的 SaaS 平台，就怕某个 B 被黑了数据库，也难顺着线找到其他的 B 然后再黑一次，虽然代码是一套的= =！

[ZZ74]

每次执行前 根据当前租户 执行 set schema 不就好了

[boolstone]

你 20 楼给的文档这个仅单词同名而已，

[totoro52]

都单独数据库了，不如也单独服务器算了， 这样就不用考虑多租户的问题了，单独数据库维护难度系数随着时间和商户的推移会越来越大。

[totoro52]

1.一个链接是建立在账号密码基础上的， 怎么可能能切到其他用户，mysql 也没有 schema 这个概念， 如果非要可以使用 PostgreSQL ，基本可以无痛迁移。
2.正确的思路是你这个用户是高权限，可以访问到其他库， 但这样还是和你一开始的想法冲突了， 说到底你还是得一个商户一个池，控制好池的大小和回收时间， 哪些商户流量大就给他多分配，哪些小就给他分配少， 这个更适合你的需求。
3.业务量小搞独立数据库纯纯玩死自己，就和小公司上来就来一套 springcloud 而不考虑运维成本一样，我做过的系统都是采取逻辑隔离，但我们的机制是当某个用户流量大的时候就会单独给他迁移出来，以免影响其他用户。

[weijancc]

schema 是 SQL 标准定义的, mysql 的 database 就是直接对应了 schema, 个人认为你应该在应用层限制租户可以访问哪些表

[whp1473]

(1)业务量比较小建议，使用租户 ID 做逻辑隔离
(2)业务量大可以考虑使用 独立 MySQL 进程-Database 来确定租户的数据位置，给予最高访问权限，然后同一个连接切换 Database
(3)业务继续增大，随着 MySQL 进程增多，所有服务端都连接所有 Database 可能会造成连接池耗尽。第一可以设置连接池没有最小连接数，一定时间不用都必须回收；第二在网关层将请求路由到不同分组的服务，该分组服务只优先连接对应 MySQL ，只有当该请求过多时才分流到其他服务
(4)分库分表导致数据的查询和统计困难，可以通过异构数据到 kafka 至 ES 查询。统计可以通过定时任务统计。
(5)数据量过大定时任务都无法统计，可以通过 Datax 抽取到 Hive 中做批处理然后统计结果回写到 MySQL

[whp1473]

@whp1473 理论上这套方法可以承载所有业务场景的数据，因为每个节点都可以水平扩容。MySQL 、ES 、Hive 、Hdfs 、Yarn

[visper]

不要搞黑魔法。直接多个连接池，最多配置下空闲回收策略。

[netnr]

我没明白多住户同 database 不同 schema
在 mysql 中怎么体现的，所以是一个租户一个库还是一个库里面表分租户

[liaojl]

MySQL 的 schema 和 database 是同一个东西，估计你得重新描述你当前的方案？我猜你说的应该是，同一个 MySQL 实例上不同的 database ？

[Kenyore]

@tomorrow092 我同意背离池化思想的这个看法。OP 这种场景更合适的方案其实还是每个租户一个连接池。空闲连接可以适当设置小一点

[sampeng]

做技术不要钻牛角尖。上千个连接也死不了人的。

[sampeng]

php 可是没有连接池的。新浪，微博，facebook 也没看死掉。真到你需要考虑连接数的时候用户数已经有商业价值了。也没啥问题

[kd9yYw2RyhQwAwzn]

po 主跟我们的场景很相似
我们参考了 AbstractRoutingDataSource 的思路 重新基于 AbstractDataSource 实现了个数据源 在 getConnection 方法做了增强
大致也是在运行时确认租户/一个方法内切换别的租户 为了适配这个有简单的实现了一个自定义的事务控制
目前对 mybatis 支持没有问题 JpaRepository 的一些默认方法支持会有问题

[5sheep]

为何如此拧巴，用 schema 做多租户解决方案

[SilenceLL]

重写 AbstractRoutingDataSource ，determineCurrentLookupKey 的 key 使用租户的数据库连接信息，

[HaibaraDP]

池化的东西必须一样，不一样的必须隔离开，要不以后排查问题非常麻烦

[dandankele]

@kd9yYw2RyhQwAwzn 我也用的 AbstractRoutingDataSource ，但你们租户之间都是用的同一个数据库帐号密码进行连接的吗？我现在卡在了数据库帐号密码切换上

[dandankele]

稍微看了下。。我主要问题好像应该还是在 user+password 的切换上，只要能切换用户，那么 set schema 就不是问题。虽然 mysql 的底层 Driver 支持在同一个连接中 changeUser(username,password)，但上层的很多库如 mybatis 、hikari 等都不支持明确的对一个连接切换用户，除非我是直接使用底层驱动开发，这显然不是太好。似乎只能采取一些折中的方式了？

[kd9yYw2RyhQwAwzn]

@dandankele 继承 DelegatingDS 把各个 database 的用户名密码设置为属性 重写 getConnection 方法手动设置用户名密码 然后 hikaridatasource 的底层数据源用 DelegatingDS 的继承类

[dyv9]

既然都多用户名多 schema 了，直接多 mysql instance 搞定了 😂，多租户可以多应用-多 DB ，也可以单应用多 DB ，还可以单应用单 DB ，你这个场景是单应用多 DB ，可以考虑把多 DB 做得彻底点，直接来多个 MySQL instance 搭配多个连接池。

[dyv9]

直接每个租户一个 java 进程一套配置，物理隔离不容易出错，出错日志找原因也简单，在前端 nginx 代理。