mysql - 优化查询 - 1 个数据库中的用户订阅，另一个数据库中的 3 级数据。查找用户订阅的顶层

Question

我有一个应用程序，它存储用户可以订阅/取消订阅的过滤器的分层列表。

涉及2个数据库:

1. db1:存储分层列表
2. db2:存储用户的订阅首选项

两个数据库位于同一服务器上。

分层列表(在db1中)由如下3个表组成:

mysql> DESCRIBE regulations;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)        | NO   |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+

mysql> DESCRIBE groups;
+---------------+-----------------+------+-----+---------+----------------+
| Field         | Type            | Null | Key | Default | Extra          |
+---------------+-----------------+------+-----+---------+----------------+
| id            | int(4) unsigned | NO   | PRI | NULL    | auto_increment |
| name          | varchar(255)    | NO   |     | NULL    |                |
| regulation_id | int(4) unsigned | NO   | MUL | NULL    |                |
+---------------+-----------------+------+-----+---------+----------------+

mysql> DESCRIBE filters;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100)         | NO   |     | NULL    |                |
| group_id | int(4) unsigned      | NO   | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

所以层次结构是:

• 法规
  • groups(外键:regulation_id)
    • 过滤器(外键:group_id)

用户订阅了 1 个或多个 filters.id。它们存储在单独的数据库中(数据库名称:db2)，其中 f_id 字段对应于 filters.id。表结构如下:

mysql> DESCRIBE tbl_alerts;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| tbl_id | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| u_id   | mediumint(8) unsigned | NO   |     | NULL    |                |
| f_id   | smallint(5) unsigned  | NO   |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+

我需要知道用户订阅了哪个regulations.name。

我这样做的方法是选择 tbl_alerts 中的所有 f_id (假设用户 ID 为 123，由 u_id)，例如

SELECT f_id FROM tbl_alerts WHERE u_id = 123;

然后在 IN 条件中使用它，如下所示:

SELECT
DISTINCT(db1.regulations.`name`)
FROM
db1.groups
JOIN db1.regulations
ON db1.groups.regulation_id = db1.regulations.id 
JOIN db1.filters
ON db1.filters.group_id = db1.groups.id
WHERE db1.filters.`id` IN (
    SELECT f_id FROM db2.tbl_alerts WHERE u_id = 123 
)

有没有更优化的写法？

使用 5.5.60-MariaDB

Answer 1

您可以尝试以下“跨数据库”联接查询:

SELECT
  DISTINCT db1.regulations.name
FROM
  db1.groups
JOIN db1.regulations
  ON db1.groups.regulation_id = db1.regulations.id 
JOIN db1.filters
  ON db1.filters.group_id = db1.groups.id 
JOIN db2.tbl_alerts 
  ON db2.tbl_alerts.f_id = db1.filters.id 
WHERE db2.tbl_alerts.u_id = 123

请注意，DISTINCT 关键字不是函数，因此不需要括号。现在，关于性能部分，您需要在当前的查询集与单个查询之间进行基准测试。