mysql - 仅当父行没有子行时才选择父行

Question

我有一个 MySQL 数据库，其中表 A 与表 B 具有一对多关系，我想选择表 B 中所有在表 A 中没有子项的行。我尝试使用

SELECT id FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.id=A.id)

和

SELECT id FROM A LEFT JOIN B ON A.id=B.id WHERE B.id IS NULL

这两个看起来都很慢。有没有更快的查询来实现同样的事情？

如果这是相关的，在我的数据库中，表 A 有大约 500,000 行，表 B 有大约 3 到 400 万行。

编辑:对于我数据库中的实际表，explain 给我:

+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
| id | select_type        | table            | type  | possible_keys | key                       | key_len | ref  | rows    | Extra                    |
+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | frontend_form471 | index | NULL          | frontend_form471_61a633e8 | 32      | NULL |  671927 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | SchoolData       | index | PRIMARY       | PRIMARY                   | 49      | NULL | 3121110 | Using where; Using index |
+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+

对于

select number from frontend_form471 where not exists (select * from SchoolData where SchoolData.`f471 Application Number`=frontend_form471.number)

和

+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+
| id | select_type | table            | type  | possible_keys | key                       | key_len | ref  | rows    | Extra                                          |
+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | frontend_form471 | index | NULL          | frontend_form471_61a633e8 | 32      | NULL |  671927 | Using index; Using temporary                   |
|  1 | SIMPLE      | SchoolData       | index | PRIMARY       | PRIMARY                   | 49      | NULL | 3121110 | Using where; Using index; Not exists; Distinct |
+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+

对于

select distinct number from frontend_form471 left join SchoolData on frontend_form471.number=SchoolData.`f471 Application Number` where SchoolData.`f471 Application Number` is NULL

在我的例子中，frontend_form471 是表 A，SchoolData 是表 B

Edit2: 在我的数据库的表 B (SchoolData) 中，id 是两部分主键的第一部分，因此它被索引并且 B 中仍然有多个条目具有相同的 id .

Answer 1

SELECT id FROM A LEFT OUTER JOIN B ON A.id=B.id WHERE B.id IS NULL

你可以这样做。外连接应该会带来一点性能，但不会太多。

新的数据库系统可能会优化您的查询，因此不会有任何差异。

这里的正确方法是缓存!如果可能，请尝试查询缓存器和应用程序级缓存。

当然你需要适当的索引。

我的意思是在两个表上，最好是哈希索引，因为与任何具有对数的树相比，它都有静态查找时间

尝试在查询之前添加解释，看看是什么真正减慢了速度。

如果你真的需要这个速度很快，你可以重构你的数据结构。

您可以创建一个触发器来标记表 A 中的标志是否在表中存在相应的条目。当然这个 id 数据冗余，但有时它是值得的。只需将其视为缓存。

最后一个想法:您可以尝试 SELECT id FROM A WHERE id NOT IN (SELECT id FROM B) 它可能会更快一些，因为不需要实际加入，但它也可能会更慢因为在 be 集合中的查找将是全扫描。我不确定这将如何处理，但可能值得一试。