使用外键进行更高效的查询，其中在表1中，而不是在表2中

xpcnnkqh 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(351)

我有三张table：测试、放大和荧光。test是amp和fluorescence的外键，它们都是连接表。荧光每个测试id有50到100行，而amp通常少于2行。
我需要得到不同的测试id的清单是荧光，但不是在放大器。两个子查询还指定一个通道。
现在，我使用以下查询：

SELECT test FROM db_test WHERE test NOT IN 
(SELECT test_id FROM db_amp WHERE channel=0)
AND test IN (SELECT test.id FROM db_fluorescence WHERE channel=0);

当这个查询工作时，需要0.5秒。这已经比我想要的要长了，而且随着数据库的增长，它会越来越长。
我对sql还比较陌生，所以我知道有一种更有效的方法可以做到这一点。
我还考虑了一种变通方法，比如在测试表中添加一个字段，该字段指示以下状态：

1. 0 - In neither Amp/Fluorescence
2. 1 - In Amp
3. 2 - In Fluorescence
4. 3 - In Both

然后反对它： SELECT test FROM db_test WHERE ch0_grade = 2; 虽然这肯定会更快，但我认为我应该能够通过改进原始查询找到更好的解决方案。此外，我认为第二个选项打破了常规，因为该字段完全是数据库中其他信息的派生项。

mysql

来源：https://stackoverflow.com/questions/51851489/more-efficient-query-using-foreign-key-where-in-table-1-but-not-in-table-2

1条答案

按热度按时间

tquggr8v1#

这个查询应该是准确的，并且在索引中有足够的性能：

SELECT test
FROM db_test
JOIN db_fluorescence ON db_fluorescence.test_id = db_test.test
LEFT JOIN db_amp ON db_amp.test_id = db_test.test
WHERE db_amp.test_id IS NULL;

因为我们需要执行查询，所以我会添加二级哈希索引来加快执行速度。
索引用于快速从数据库检索数据。用户看不到索引，它们只是用来加速搜索/查询。

CREATE INDEX index1
ON db_fluorescence(test_id);

CREATE INDEX index2
ON db_test(test_id);

CREATE INDEX index3
ON db_amp(test_id);

注意：使用索引更新表比不使用索引更新表需要更多的时间（因为索引也需要更新）。因此，只能在经常搜索的列上创建索引。

赞(0）回复(0）举报 2021-06-20

我来回答

使用外键进行更高效的查询，其中在表1中，而不是在表2中

1条答案

相关问题

热门标签

最新问答