mysql - 使用外键在表1中但不在表2中进行更有效的查询

我有三个表：Test，Amp和Fluorescence。测试是连接表的Amp和Fluorescence的外键。每个test_id的荧光行数为50至100，而Amp通常少于2。

我需要获取荧光中而不是Amp中不同的test_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相当陌生，因此我知道有一种更有效的方法。

我也在考虑一种解决方法，例如将一个字段添加到Test表中，以指示状态：

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;

尽管这肯定会更快，但我认为我应该能够通过改进原始查询来找到更好的解决方案。另外，我认为第二种选择打破了常规性，因为该字段完全是数据库中其他信息的派生对象。

最佳答案

此查询应准确并具有足够的索引性能：

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);

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