mysql - 为什么 DISTINCT 使这个查询比没有 DISTINCT 花费的时间长 10 倍？

Question

我有这个 mysql 查询:

SELECT DISTINCT post.postId,hash,previewUrl,lastRetrieved
FROM post INNER JOIN (tag as t1,taggedBy as tb1,tag as t2,taggedBy as tb2,tag as t3,taggedBy as tb3)
ON post.id=tb1.postId AND tb1.tagId=t1.id AND post.id=tb2.postId AND tb2.tagId=t2.id AND post.id=tb3.postId AND tb3.tagId=t3.id
WHERE ((t1.name="a" AND t2.name="b") OR t3.name="c") 
ORDER BY post.postId DESC LIMIT 0,100;

运行该查询大约需要 15 秒，而没有 DISTINCT 的相同查询只需不到一秒。

<小时/>

EXPLAIN 查询的输出 with DISTINCT:

+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                      | rows | Extra                 |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-----------------------+
|  1 | SIMPLE      | post  | index  | PRIMARY             | postId  | 4       | NULL                     |    1 | Using temporary       |
|  1 | SIMPLE      | tb1   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index; Distinct |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb1.tagId |    1 | Distinct              |
|  1 | SIMPLE      | tb2   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index; Distinct |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb2.tagId |    1 | Distinct              |
|  1 | SIMPLE      | tb3   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index; Distinct |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb3.tagId |    1 | Using where; Distinct |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-----------------------+
7 rows in set (0.01 sec)

EXPLAIN 查询的输出不带 DISTINCT:

+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                      | rows | Extra       |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | post  | index  | PRIMARY             | postId  | 4       | NULL                     |    1 | NULL        |
|  1 | SIMPLE      | tb1   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb1.tagId |    1 | NULL        |
|  1 | SIMPLE      | tb2   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb2.tagId |    1 | NULL        |
|  1 | SIMPLE      | tb3   | ref    | PRIMARY,tagId       | PRIMARY | 4       | e621datamirror.post.id   |   13 | Using index |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,name,name_2 | PRIMARY | 4       | e621datamirror.tb3.tagId |    1 | Using where |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------------+------+-------------+

<小时/>

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `postId` int(11) NOT NULL,
  `hash` varchar(32) COLLATE utf8_bin NOT NULL,
  `previewUrl` varchar(512) COLLATE utf8_bin NOT NULL,
  `lastRetrieved` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `postId` (`postId`),
  UNIQUE KEY `hash` (`hash`),
  KEY `postId_2` (`postId`),
  KEY `postId_3` (`postId`)
) ENGINE=InnoDB AUTO_INCREMENT=692561 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=157876 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `taggedBy` (
  `postId` int(11) NOT NULL,
  `tagId` int(11) NOT NULL,
  PRIMARY KEY (`postId`,`tagId`),
  KEY `tagId` (`tagId`),
  CONSTRAINT `taggedBy_ibfk_1` FOREIGN KEY (`postId`) REFERENCES `post` (`id`) ON DELETE CASCADE,
  CONSTRAINT `taggedBy_ibfk_2` FOREIGN KEY (`tagId`) REFERENCES `tag` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

<小时/>

是什么导致这个查询如此缓慢？我怎样才能加快速度？

我希望我已经提供了足够的信息，以便你们可以给我一些有意义的答案。如果我遗漏了一些内容，我很乐意添加它。

Answer 1

即使在 @SlimGhost 的合理(但已删除)答案中，也正在讨论一些事情。

DISTINCT 与 GROUP BY

虽然GROUP BY有时可以用来替换 DISTINCT ，不要这样做；它们的用途不同。

它们都需要某种形式的额外努力。 (稍后我将讨论 10x。)两者都必须发现共同的值——要么在整行中(对于 DISTINCT )，要么在分组的项目中。这可以通过至少两种方式之一来完成。 (可能大多数引擎都内置了这些选项。)请注意 DISTINCT或GROUP BY逻辑上必须位于 WHERE 之后，但在ORDER BY之前和LIMIT .

• 在生成输出时保留某种内部关联数组。如果优化器可以看到不会有“太多”可能的不同值，那么这是实用的。
• 对输出进行排序；然后对输出进行重复数据删除或分组。无论大小如何，这都有效。

排序依据+限制

请注意，查询正在执行 DISTINCT超过 4 列:post.postId, hash, previewUrl, lastRetrieved 。目前尚不清楚这些是否都在 post 中或者分散在 7 张 table 上。 (请通过限定每一列来澄清。)

假设需要完成 JOIN 才能找到 4 列。

假设没有 DISTINCT 。现在，操作是

1. 浏览post在ORDER BY post.postID订单。
2. 对于每个这样的行，执行 JOIN 并检查 WHERE。
3. 100 行通过 WHERE 后，停止。

但与 DISTINCT ，优化器不能为了停止而做出这样的简化假设。相反:

1. 浏览post在ORDER BY post.postID命令。 (从 t1/t2/t3 开始是不可能的，因为 OR 。)实际上，不清楚优化器是否会按照这个顺序进行。
2. 对于每个这样的行，执行 JOIN 并检查 WHERE。
3. 做一些关于 DISTINCT 的事情.
4. 100 行通过 WHERE 后，停止。注意:这可能涉及 post 中的更多行(也许 10 倍？)

请记住，优化器对于 postId 是否有效一无所知。与 hash 的比例为 1:1等等。所以，它不能做出简化的假设。假设 JOIN 中有 200 行，其中最小的 postId ，以及hash碰巧是按降序排列的。听起来像是需要“排序”。

EXPLAIN FORMAT=JSON SELECT ... 可能为您提供一些详细信息。

哎呀。您同时拥有 id和UNIQUE(postid) ？摆脱id并转动postId进入PRIMARY KEY 。仅此一项就可以加快速度。

什么是 hash的哈希值？

请使用JOIN ... ON ...语法。

您在 postId 上有 3 个索引;去掉多余的两个。

为什么使用 DISTINCT？

现在我看到所有 SELECTed列来自一个表，并且它们显然很容易区分，为什么还要考虑使用 DISTINCT .

(更新)

加入

FROM post INNER JOIN (tag as t1,taggedBy as tb1,...
                   ON post.id=tb1.postId AND tb1.tagId=t1.id AND ...
 -->
FROM post
JOIN tag       AS  t1 ON post.id = tb1.postId
JOIN taggedBy  AS tb1 ON tb2.tagId = t2.id
...  (each ON is next to the JOIN it applies to)

加速技术

SELECT p2.postId, p2.hash, p2.previewUrl, p2.lastRetrieved
    FROM (
        SELECT DISTINCT postId           -- Only the PRIMARY KEY
            FROM post
            JOIN ... etc
            WHERE ... ...
            ORDER BY postId
            LIMIT 100
         ) x
    JOIN post AS p2  ON x.postId = p2.id   -- self join for getting rest of fields
    ORDER BY x.postId   -- assuming you need the ordering

这使得DISTINCT在内部查询中，您仅获取一列( postId )。 (我不确定这种技术对您的情况是否有很大帮助。)