mysql - 为什么对三个表的 union mysql 查询比 join on impossible where 子句更快？

Question

我的模式是这样设置的，我有三个表 player coach 和 manager，其中所有三个表都有一个表的外键 employee 其中包含不超过一个自动递增的 id 和一个字符串 type 表示他们是雇员的类型。 “球员”或“教练”或“经理”。

但是，不同的员工根据他们工作的团队有不同的 ID。因此，我为每个团队(其中 x 是团队编号或名称)创建了一个查找表 team_x_lookup，它将团队 ID 映射到全局 ID。此外，我在每个 player coach 和 manager 表中为每个包含团队 ID 的团队生成了一个列，此列已编入索引。如果该员工不在该团队中，则此列显然将为空。

要从员工的团队 ID 中获取员工，我目前有两个选择语句

SELECT * FROM employee e
    LEFT JOIN player p ON (e.`type` = 'PLAYER' AND p.employee_id = e.id)
    LEFT JOIN coach c ON (e.`type` = 'COACH' AND c.employee_id = e.id)
    LEFT JOIN manager m ON (e.`type` = 'MANAGER' AND m.employee_id = e.id)
WHERE e.id = (
    SELECT employee_id FROM team_x_lookup t WHERE t.team_id = 6
);

SELECT * FROM (
    SELECT * FROM player WHERE team_id = 6 
    UNION
    SELECT * FROM coach WHERE team_id = 6
    UNION 
    SELECT * FROM manager WHERE team_id = 6
) as emp;

我对我执行 SELECT 的所有内容都有索引。运行解释分别给我这些:

1   PRIMARY e       const   PRIMARY,id_UNIQUE           PRIMARY 4   const   1   100.00  
1   PRIMARY p       const   PRIMARY,employee_id _UNIQUE PRIMARY 4   const   1   100.00  
1   PRIMARY c       const   PRIMARY,employee_id _UNIQUE PRIMARY 4   const   0   0.00    unique row not found
1   PRIMARY m       const   PRIMARY,employee_id _UNIQUE PRIMARY 4   const   0   0.00    unique row not found
2   SUBQUERY t      const   PRIMARY,team_id_UNIQUE      PRIMARY 4   const   1   100.00  

1   PRIMARY         <derived2>      ALL                                 6               100.00  
2   DERIVED         player          ref team_x_id_INDEX team_x_id_INDEX 5   const   1   100.00  Using index
3   UNION           coach           ref team_x_id_INDEX team_x_id_INDEX 5   const   1   100.00  Using index
4   UNION           manager         ref team_x_id_INDEX team_x_id_INDEX 5   const   1   100.00  Using index
N   UNION RESULT    <union2,3,4>    ALL                                                         Using temporary

我不喜欢第二种方法，因为它要求我可能为每个表增加 20 列。但是，我不明白联合查询是如何比查找表查询快 50% 的!联合不是必须对所有三个表进行选择，而交叉引用查询会注意到三个连接之一是可能的并丢弃其他两个吗？

我想知道如何使交叉引用表更快，另外因为 union 要求我从 union 中的每个表中选择相同数量的列，而我的每个表都有不同数量的列，所有这些都应该是返回。

编辑

我在数据库中有大约 30,000 个元素，我通过联合查询数据的前半部分和通过联接查询数据的后半部分来计算时间。我的联合时间约为 3.9 秒，加入时间约为 7.0 秒。

Answer 1

您可能可以通过删除子查询来提高第一个查询的效率，如下所示:

SELECT e.* FROM employee e
INNER JOIN team_x_lookup t ON e.id = t.employee_id
LEFT JOIN player p ON (e.`type` = 'PLAYER' AND p.employee_id = e.id)
LEFT JOIN coach c ON (e.`type` = 'COACH' AND c.employee_id = e.id)
LEFT JOIN manager m ON (e.`type` = 'MANAGER' AND m.employee_id = e.id)
WHERE t.team_id = 6