mysql - 共享租户对象

Question

我有一个带有单个数据库的 Multi-Tenancy 应用程序。我有一个存储所有对象的“实体”表。 “sahred_entity”表用于存储租户X向租户Y共享的对象。例如“租户2”可以将“ID为4的实体”共享给“租户1”。

在下面的示例中，“ID 4 的实体”共享给“租户 1”和“租户 3”

+--------+--------------------------------------------------
| Table  | Create Table
+--------+--------------------------------------------------
| entity | CREATE TABLE `entity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int(10) unsigned NOT NULL,
  `added_at` timestamp NOT NULL,
  `color` varchar(20) NOT NULL,
  `size` varchar(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------

+---------------+---------------------------------------
| Table         | Create Table
+---------------+---------------------------------------
| shared_entity | CREATE TABLE `shared_entity` (
  `tenant_to` int(10) unsigned NOT NULL,
  `tenant_from` int(10) unsigned NOT NULL,
  `entity_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------

示例数据为

select * from entity;

+----+-----------+---------------------+--------+------+
| id | tenant_id | added_at            | color  | size |
+----+-----------+---------------------+--------+------+
|  1 |         1 | 2019-03-07 00:00:00 | red    | m    |
|  2 |         1 | 2019-03-07 00:00:00 | green  | xl   |
|  3 |         2 | 2019-03-07 00:00:00 | green  | xl   |
|  4 |         2 | 2019-03-07 00:00:00 | red    | m    |
|  5 |         3 | 2019-03-07 00:00:00 | yellow | l    |
+----+-----------+---------------------+--------+------+

select * from shared_entity;

+-----------+-------------+-----------+
| tenant_to | tenant_from | entity_id |
+-----------+-------------+-----------+
|         1 |           2 |         4 |
|         3 |           2 |         4 |
+-----------+-------------+-----------+ 

现在我需要创建一个简单的搜索查询。现在我找到了两种方法来做到这一点。第一种是通过自加入

SELECT e.* FROM `entity` as e
LEFT JOIN entity as e1 ON (e.id = e1.id AND e1.tenant_id = 1)
LEFT JOIN entity as e2 ON (e.id = e2.id AND e2.id IN (4))
WHERE (e1.id IS NOT NULL OR e2.id IS NOT NULL) AND e.`color` = 'red';

第二个是通过子查询和联合

SELECT * FROM 
(
    SELECT * FROM entity as e1 WHERE e1.tenant_id = 1
        UNION
    SELECT * FROM entity as e2 WHERE e2.id IN(4)
) as entity
WHERE color = 'red';

两个查询都返回预期结果

+----+-----------+---------------------+-------+------+
| id | tenant_id | added_at            | color | size |
+----+-----------+---------------------+-------+------+
|  1 |         1 | 2019-03-07 00:00:00 | red   | m    |
|  4 |         2 | 2019-03-07 00:00:00 | red   | m    |
+----+-----------+---------------------+-------+------+

但是哪种方法对于大型表更好？如何创建正确的索引？或者也许有更好的解决方案？

Answer 1

您还可以使用以下查询来获得相同的结果

SELECT *
FROM entity
WHERE (tenant_id = 1 or id = 4) AND color = 'red'

我不清楚为什么你需要所有的连接