MySQL:如何改进这个 UNION？

Question

我有两个表，a 用于访问，p 用于提供者。然后我有第三个表用于将表连接在一起，标准规范化。然而，提供者表是一个父/子表，连接表有一个选项是否应该授予所有提供者 child 访问权限。

CREATE TABLE p (
    p_id int PRIMARY KEY,
    name varchar(32),
    parent_id int,
    FOREIGN KEY (parent_id) REFERENCES p(p_id)
);

CREATE TABLE a (
    a_id int PRIMARY KEY,
    name varchar(32)
);

CREATE TABLE ap (
    a_id int,
    p_id int,
    sub tinyint,
    FOREIGN KEY (a_id) REFERENCES a(a_id),
    FOREIGN KEY (p_id) REFERENCES p(p_id)
);

一些样本数据，1 个提供者和 2 个子提供者。 2 个访问用户，1 个没有 child 访问权限，1 个有 child 访问权限。

INSERT INTO p VALUES(1, 'a', null);
INSERT INTO p VALUES(2, 'a.a', 1);
INSERT INTO p VALUES(3, 'a.b', 1);

INSERT INTO a VALUES(1, 'user 1');
INSERT INTO a VALUES(2, 'user 2');

INSERT INTO ap VALUES(1, 1, 0);
INSERT INTO ap VALUES(2, 1, 1);

结果是我希望根据第三个表获得用户有权访问的提供商列表。

目前我已经通过使用 UNION 加入两个查询来解决这个问题。第一个查询选择可能的子提供者，第二个查询选择主要提供者。

SELECT p_id, name
FROM p
WHERE parent_id IN(
    SELECT p_id FROM ap WHERE a_id = 1 AND sub = 1
)
UNION
SELECT ap.p_id, p.name
FROM ap
LEFT JOIN p ON p.p_id = ap.p_id
WHERE a_id = 1;

我不喜欢这个查询，它很丑，必须有更聪明的方法:)

Answer 1

如果我的逻辑是正确的，那么您需要 p 中满足以下条件之一的所有记录:

• p_id 匹配 ap 中给定 a_id 的记录。
• parent_id 匹配 ap 中给定 a_id 的记录。

这建议使用 exists 作为条件:

select p.p_id, p.name
from p
where exists (select 1
              from ap
              where ap.p_id = p.p_id and ap.a_id = 1
             ) or
      exists (select 1
              from ap
              where ap.p_id = p.parent_id and ap.sub = 1 and ap.a_id = 1
             )

使用 ap(p_id, a_id, sub) 上的复合索引，这应该比您的查询版本有更好的性能。