mysql - 如何避免SQL中的嵌套子查询

Question

我刚刚向我的网站添加了一个标记系统，我正在尝试找出运行可扩展查询的最有效方法。下面是一个基本的 mysql 查询，用于返回给定用户的标签匹配项:

SELECT
   scans.scan_index,
   scans.scan_id,
   scans.archive_folder 
FROM
   tags 
   INNER JOIN
      interpretationtags USING (tagid) 
   INNER JOIN
      interpretations USING (interpretation_id) 
   INNER JOIN
      scans 
      ON scans.scan_id = interpretations.scan_id 
      AND scans.archive_folder = interpretations.archive_folder 
   INNER JOIN
      archives 
      ON scans.archive_folder = archives.archive_folder 
WHERE
   archives.user_id = "google-authd...." 
   AND tags.tag = "tag1"

但是当我想查询同一扫描的多个标签时，它会变得粘滞。您会看到，标签存在于不同的解释中，并且每个扫描有多种解释。这是两个标签的有效查询 使用子查询:

SELECT
   a.scan_index,
   a.scan_id,
   a.archive_folder 
FROM
   (
      SELECT
         scans.scan_index,
         scans.scan_id,
         scans.archive_folder 
      FROM
         tags 
         INNER JOIN
            interpretationtags USING (tagid) 
         INNER JOIN
            interpretations USING (interpretation_id) 
         INNER JOIN
            scans 
            ON scans.scan_id = interpretations.scan_id 
            AND scans.archive_folder = interpretations.archive_folder 
         INNER JOIN
            archives 
            ON scans.archive_folder = archives.archive_folder 
      WHERE
         archives.user_id = "google-auth2..." 
         AND tags.tag = "tag1"
   )
   as a 
   INNER JOIN
      interpretations 
      ON a.scan_id = interpretations.scan_id 
      AND a.archive_folder = interpretations.archive_folder 
   INNER JOIN
      interpretationtags USING(interpretation_id) 
   INNER JOIN
      tags USING(tagid) 
WHERE
   tags.tag = "tag2"

由于这是在 LAMP 堆栈上运行，因此我编写了一些 PHP 代码来迭代我想要包含在此 AND 式搜索中的 标签，从而构建多嵌套查询。这是一加三

SELECT
   b.scan_index,
   b.scan_id,
   b.archive_folder 
FROM
   (
      SELECT
         a.scan_index,
         a.scan_id,
         a.archive_folder 
      FROM
         (
            SELECT
               scans.scan_index,
               scans.scan_id,
               scans.archive_folder 
            FROM
               tags 
               INNER JOIN
                  interpretationtags USING (tagid) 
               INNER JOIN
                  interpretations USING (interpretation_id) 
               INNER JOIN
                  scans 
                  ON scans.scan_id = interpretations.scan_id 
                  AND scans.archive_folder = interpretations.archive_folder 
               INNER JOIN
                  archives 
                  ON scans.archive_folder = archives.archive_folder 
            WHERE
               archives.user_id = "google..." 
               AND tags.tag = "tag1"
         )
         as a 
         INNER JOIN
            interpretations 
            ON a.scan_id = interpretations.scan_id 
            AND a.archive_folder = interpretations.archive_folder 
         INNER JOIN
            interpretationtags USING(interpretation_id) 
         INNER JOIN
            tags USING(tagid) 
      WHERE
         tags.tag = "tag2"
   )
   as b 
   INNER JOIN
      interpretations 
      ON b.scan_id = interpretations.scan_id 
      AND b.archive_folder = interpretations.archive_folder 
   INNER JOIN
      interpretationtags USING(interpretation_id) 
   INNER JOIN
      tags USING(tagid) 
WHERE
   tags.tag = "tag3"

即使是 4 个嵌套子查询也能以最少的数据快速运行，但当我处理 100k 行数据时，我认为这不是一个可扩展的解决方案。我怎样才能做到这一点而不恢复到这个丑陋低效的代码？

Answer 1

如果没有表结构和示例数据，很难确定，但我认为您的方向是错误的。您应该从扫描开始并找到所有适当的标签，然后对这些标签进行过滤(这应该是一个简单的 IN 表达式):

SELECT
   scans.scan_index,
   scans.scan_id,
   scans.archive_folder 
FROM
   scans
   INNER JOIN
      archives 
      ON scans.archive_folder = archives.archive_folder 
   INNER JOIN
      interpretations 
      ON scans.scan_id = interpretations.scan_id 
      AND scans.archive_folder = interpretations.archive_folder 
   INNER JOIN
      interpretationtags USING (interpretation_id) 
   INNER JOIN
      tags USING (tagid) 
WHERE
   archives.user_id = "google-authd...." 
   AND tags.tag IN("tag1", "tag2")

请注意，根据您的SELECT字段列表，我认为您实际上根本不需要JOIN到archives。