我们可以优化这个MySQL SELECT查询(2个嵌套的SELECT和2个内连接)吗？

Question

SELECT id,name,color
FROM animals a
INNER JOIN animal_tags atags ON atags.animal_id = a.id
WHERE atags.tag_id = 70 OR a.id IN (
SELECT a2.id FROM animals a2
WHERE a2.inherit_tags = 1 AND a2.species IN
(SELECT species.id FROM species
INNER JOIN species_tags stags ON stags.species_id = species.id
WHERE stags.tag_id = 70))

基本上，我正在寻找任何与tag_id 70 (例如“毛皮”)有关联的动物，以及任何属于与tag_id 70有关联的物种的动物，并且该动物应该从该物种继承其标签。

我的另一个选择是基本上删除继承标签部分，只定义动物的标签……但不知何故，这看起来并不是很正常。(请注意，*_tags表只包含关系，并且每个标签都与存储标签详细信息的“tag_id”表相关联。这对于这个查询并不重要，只是为了向您展示我的表模式是完全规范化的。)

Answer 1

因为在结果集中实际上不需要来自标记表的值，所以不需要完整的连接。通常，查询优化器可以使用exists更有效地处理半连接。

此外，由于三个表都在species_id上连接，因此在不需要物种表的情况下，可以连接到中间表(animal - species - speciesTags)。你可以绕过物种，从动物连接到speciesTags。

最后，由于您已经在使用animals表，并且正在计算OR条件，因此不需要将该表连接到表本身。这应该被证明是更有效的。我也会确保你在tag_id，species_id等上有索引。

SELECT id, name, color
FROM animals a
WHERE EXISTS(SELECT 1 
               FROM animal_tags atags 
               where atags.tag_id = 70 
                   and atags.animal_id = a.id) 
    OR (a.inherit_tags = 1 
        AND EXISTS( SELECT 1 from species_tags stags
                   WHERE stags.species_id = a.species 
                        AND stags.tag_id = 70
                 )
        )

Answer 2

我相信这个问题应该是等价的。

SELECT id, name, color
    FROM animals a
        INNER JOIN animal_tags atags
            ON a.id = atags.animal_id
        LEFT JOIN species s
            INNER JOIN species_tags stags
                ON s.id = stags.species_id
                    AND stags.tag_id = 70
            ON a.species = s.id
                AND a.inherit_tags = 1
    WHERE atags.tag_id = 70
        OR s.id IS NOT NULL