MySQL中多个模式之间的交叉联接.特权与表现 [英] Cross join between multiple schemas in MySQL. Privileges and performance
问题描述
我正在构建两个应用程序，这些应用程序将以通用模式共享一些数据.我正在使用MySQL作为RDBMS.这是我的第一个架构(test_schema_1):
I'm building two applications which will share some data in common schema. I'm using MySQL as RDBMS. Here is my first schema (test_schema_1):
第二个(test_schema_2):
我最近了解到可以在多个架构之间进行JOIN.当我与root用户建立连接(无限制访问所有内容)时，我会执行以下操作:
I recently learned that I can do JOINs between multiple schemas. When I'm connected with root user (unlimited access to everything), I do:
SELECT * FROM
TEST_SCHEMA_2.USERS U
JOIN TEST_SCHEMA_1.MASTER_USERS MU ON U.MASTER_ID = MU.ID
JOIN TEST_SCHEMA_2.ROLES R ON U.ROLE_ID = R.ID
WHERE MU.APP_ID = 'darth_vader@death.star';
达到我的期望！对于我来说，这看起来很酷，因为我正在考虑使用这种设计进行生产.
And get what I expect! This looks very cool for me, as I'm thinking of going to production with this design.
但是我有点担心这种设计对性能的影响吗?这是一个好主意吗?如果将来我决定(用MongoDB术语)将数据库分片到不同的机器上怎么办?
But I'm a bit afraid of performance implications of such design? Is this a good idea? What if in future I decide to shard (in MongoDB terminology) the database on different machines?
我还想知道安全性问题.目前，我为每个架构都有一个单独的用户:
Also, I'm wondering of security problems. Currently, I have a separate user for each schema:
Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
% test_schema_1 t1 Y Y Y Y N N N N N N N N N N N N N N Y
localhost test_schema_1 t1 Y Y Y Y N N N N N N N N N N N N N N Y
% test_schema_2 t2 Y Y Y Y N N N N N N N N N N N N N N Y
localhost test_schema_2 t2 Y Y Y Y N N N N N N N N N N N N N N Y
因此，我希望用户t1无法从test_schema_2读取.但是上面的查询有效.他甚至可以插入test_schema_2:
So I expect that user t1 cannot read from test_schema_2. But the query above works. He even can insert in test_schema_2:
SELECT USER();
INSERT INTO TEST_SCHEMA_2.ROLES(ID, NAME) VALUES(4, 'TEST');
...
USER()
t1@localhost
-------------------------------------------
Updated Rows
1
我想念什么?
推荐答案
MySQL为所有数据库维护一组缓冲区.关于性能，表是否在不同的数据库中都没有关系.
MySQL maintains a single set of buffers for all databases. With regards to performances, it does not matter whether tables are in different databases.
您的用例是boderline，但似乎是合计来自单独数据库的数据的合理用例.
Your use case is boderline, but seems to be a reasonnable case for aggregating data from separate databases.
尽管如此，我会对这种体系结构保持谨慎.如果您的应用程序开始共享的不仅仅是几个表，请问问自己这些应用程序是否真的是不同的应用程序，或者是同一应用程序的模块.在后一种情况下，将所有表都放在一个sigle数据库中对我来说很有意义.
I would be careful with this architecture though. If your applications start sharing more than just a few tables, ask yourself whether these applications are really distinct applications, or rather modules of the same application. In the latter case, it would make sense to me to have all tables in one sigle database.
如果您担心安全性，那么请注意，大多数访问权限都可以授予基于每个表(甚至每个列).
If you are concerned about security, then please note that most access rights may be granted on a per-table (or even per-column) basis.
这篇关于MySQL中多个模式之间的交叉联接.特权与表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
