mysql - 标记模式 - 查询单独运行时速度快，在一个 SELECT 中运行时速度慢

Question

我有一个奇怪的性能问题，用于为类似 Delicious 的书签 Web 应用程序创建“按标签过滤”小部件的查询。如果运行尽可能少的单独查询，则特定的、相对复杂的查询执行速度会快得多(1000 到 10000 倍)。

我已经在以下环境中对其进行了测试:

• Windows XP/MySQL 5.1.37(服务器和客户端)
• Ubuntu 11.10/MySQL 5.1.58(服务器和客户端)

问题没有出现在小型开发数据库中。我在生产使用期间发现了它，在数据库中的记录大量增加之后(目前 link_tags 表中大约有 100K 行和 11K 唯一标签)。

我使用以下数据库模式:

CREATE TABLE IF NOT EXISTS `link_tags` (
  `link_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  UNIQUE KEY `link_tag_id` (`link_id`,`tag_id`),
  KEY `tag_id` (`tag_id`),
  KEY `link_id` (`link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag` (`tag`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

架构很简单(另请参阅 http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html )，因此不需要进一步解释。

从技术上讲，有问题的查询(如下)检索与给定标签集相关的标签(具体来说，附加到由指定标签集标记的链接的所有标签)并计算每个找到的标签和标签集的链接数。

[ORIGINAL QUERY]

SELECT COUNT(*) AS link_count, tag FROM (
    SELECT
        t.tag AS tag,
        CONCAT(lt.tag_id,':',lt.link_id) AS tag_link_hash
    FROM
        link_tags lt, tags t
    WHERE
        t.id = lt.tag_id
        AND lt.link_id IN (
            SELECT
                link_id
            FROM
                link_tags lt2, links l2
            WHERE
                l2.id = lt2.link_id
                AND l2.created_by = ?  <-- user to filter tags for
                AND lt2.tag_id IN (
                   SELECT id FROM tags t2 WHERE tag IN (?)  <-- tags set to filter by
                )
            GROUP BY
                link_id
            HAVING
                COUNT(*) = ?)  <-- number of tags in filter
    GROUP BY
        tag_link_hash) tmp
GROUP BY
    tag
ORDER BY
    link_count DESC,
    tag ASC
[Results in X minutes - up to 4 hours]

在生产数据库中(正如我提到的 - 大约 100K link_tags 和 11K 标签)查询在几分钟到几小时内运行(取决于指定标签的出现频率)。奇怪的是，如果我将它分成几个查询，一切都会很顺利:

1) 查找给定标签名称的 id。

[REPLACEMENT QUERY 1]

SELECT id FROM tags t2 WHERE tag IN (?)

[Results in 0,0011 seconds]

2) 查找给定标签集(交叉点!)的所有 link_id。

[REPLACEMENT QUERY 2]

SELECT
    link_id
FROM
    link_tags lt2, links l2
WHERE
    l2.id = lt2.link_id
    AND l2.created_by = 1
    AND lt2.tag_id IN ( ? )  <-- here goes imploded result of query 1
GROUP BY
    link_id
HAVING
    COUNT(*) = ?  <-- number of tags

[Results in 0,0996 seconds]

3) 查找给定的一组 link_id 的所有标签，并按链接数对标签进行分组。

[REPLACEMENT QUERY 3]

SELECT COUNT(*) AS link_count, tag FROM (
    SELECT
        t.tag AS tag,
        CONCAT(lt.tag_id,':',lt.link_id) AS tag_link_hash
    FROM
        link_tags lt, tags t
    WHERE
        t.id = lt.tag_id
        AND lt.link_id IN ( ? )  <-- here goes imploded result of query 2
    GROUP BY
        tag_link_hash) tmp
GROUP BY
    tag
ORDER BY
    link_count DESC,
    tag ASC

[Results in 0,0543 seconds]

你知道发生了什么事吗？ EXPLAIN 显示了与分离查询总和大致相同的大型查询计划。不同之处在于每个步骤中处理的行数(这也很奇怪)。

您能否帮助重写原始查询，提示 MySQL 优化器高效地运行它或指出导致此行为的 MySQL 错误？

解释原始查询的结果:

id  select_type table       type    possible_keys   key         key_len ref                     rows    Extra
1   PRIMARY     <derived2>  ALL     N8LL            N8LL        N8LL    N8LL                    32      Using temporary; Using filesort
2   DERIVED     lt          index   tag_id          link_tag_id 8       N8LL                    78162   Using where; Using index; Using temporary; Using filesort
2   DERIVED     t           eq_ref  PRIMARY         PRIMARY     4       lstack_prod.lt.tag_id   1
3   DEPENDENT   t2          range   PRIMARY,tag     tag         767     N8LL                    2       Using where; Using temporary; Using filesort
    SUBQUERY
3   DEPENDENT   lt2         ref     link_tag_id,    tag_id      4       lstack_prod.t2.id       7
    SUBQUERY                        tag_id,link_id
3   DEPENDENT   l2          eq_ref  PRIMARY,        PRIMARY     4       lstack_prod.lt2.link_id 1       Using where
    SUBQUERY                        created_by

Answer 1

WHERE IN (select values from table)在MySQL中效率极低，会一直触发全表扫描和文件排序。通常，您应该将它们替换为 INNER JOIN。

我认为这应该有所帮助，但我没有尝试重新创建您的数据库，也没有运行此查询，因此可能存在拼写错误。

SELECT COUNT(*) AS link_count, tag FROM (
    SELECT
        t.tag AS tag,
        CONCAT(lt.tag_id,':',lt.link_id) AS tag_link_hash
    FROM
        link_tags lt
    JOIN tags t on t.id = lt.tag_id
    JOIN (SELECT
                link_id
            FROM
                link_tags lt2
            JOIN links l2 on l2.id = lt2.link_id
            JOIN tags t2 on t2.id = lt2.tag_id                
            WHERE
                AND l2.created_by = ?  <-- user to filter tags for
                AND t2.tag IN (?)  <-- tags set to filter by
            GROUP BY
                link_id
            HAVING
                COUNT(*) = ?) as eligible_links on eligible_links.link_id = lt.link_id
    GROUP BY
        tag_link_hash) tmp
GROUP BY
    tag
ORDER BY
    link_count DESC,
    tag ASC

但是，解释计划会很有帮助。