php - MySQL "in-where"很慢？

Question

我有两个表:Posts 和Tags，其中存储了用户发布的文章以及他们为文章附加的标签。 PostTags 表用于表示文章 ID 和标签 ID 的关系。结构如下:

帖子:

id | title | author_id | create_time | update_time | ... #(title, author_id, create_time) is unique

标签:

id | tag_text | create_time #tag_text is unique and index

帖子标签:

id | post_id | tag_id #(post_id, tag_id) is unique

我现在使用下面的sql来获取带有相应标签的文章(使用group_concat)。

SELECT p.id, p.title, t.tag AS Tags FROM Posts p 
LEFT JOIN Tags t on t.id IN 
    (SELECT tag_id FROM PostTags WHERE post_id=s.id) 
GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10

但我发现它非常慢(对于 2.5k 行文章和 600 个标签，它需要 >3s)。我怎样才能提高性能？

EXPLAIN结果如下:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 2569 | Using temporary; Using filesort

1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 616   

2 | DEPENDENT SUBQUERY | PostTags | index_subquery | unique_index,tag_id,post_id | 
tag_id | 4 | func | 1 | Using where

PS，我原来的sql是(with group_concat)

SELECT p.id, p.title, group_concat(DINSTINCT t.tag) AS Tags FROM Posts p 
LEFT JOIN Tags t on t.id IN 
    (SELECT tag_id FROM PostTags WHERE post_id=s.id) 
GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10

但是没有 group_concat 的情况是一样的。

Answer 1

MySQL documentation describes exactly this kind of situation :

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. [..] If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

使用另一个连接而不是子查询将是一个更优化的解决方案:

SELECT p.id, p.title, t.tag AS Tags FROM Posts p
LEFT JOIN PostTags pt on pt.post_id = p.id
LEFT JOIN Tags t on t.id = pt.tag_id
GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10