php - 改进查询/表 : Long "Copying to tmp table"

Question

我在 mysql 中有以下查询:

  SELECT t.ID
    FROM forum_categories c, forum_threads t 
    INNER JOIN forum_posts p ON p.ID = t.Last_post 
    WHERE t.ForumID=36 OR (c.Parent=36 AND t.ForumID=c.ID) 
ORDER BY t.Last_post DESC LIMIT 1

表格forum_threads如下所示:
ID --- 标题 --- ForumID -- Last_post (最后论坛帖子的 ID)

表格forum_posts如下所示:
ID --- 内容 -- 作者

最后是表forum_categories，如下所示:
ID -- 姓名 --- 父级(另一个 forum_categoriey)

(均简化)
forum_posts 表当前包含约 200,000 行，forum_threads 表当前包含约 5,000 行

不知何故，这些查询有时需要大约 1-2 秒。
我已经将“Last_post”编入索引，但没有帮助。
“复制到临时表”持续时间占此查询整个执行时间的约 99%

我还增加了tmp_table_size和sort_buffer_size，但仍然没有区别。

有什么想法吗？

Answer 1

当你有这样的东西时，查询应该会更好

select t.id 
from forum_threads t 
inner join forum_posts p ON p.ID = t.Last_post 
inner join forum_categories c on t.ForumID=c.ID 
WHERE t.ForumID=36 OR c.Parent=36 
ORDER BY t.Last_post 
DESC LIMIT 1

现在，对于少量数据来说，它看起来非常好，而且查询时间也非常短。

接下来如何针对大量数据改进它，答案是 INDEX。

• 发生了 2 次连接

• 还有一个 where 子句

因此您需要正确索引表以避免全表扫描。

您可以运行以下命令来查看表上的当前索引

show indexes from forum_threads;
show indexes from forum_posts ;
show indexes from forum_categories ;

上述命令将显示与表关联的索引。现在考虑没有索引的事实，因此我们需要将索引建立为

alter table forum_threads add index Last_post_idx (`Last_post`);
alter table forum_posts add index ID_idx (`ID`);
alter table forum_categories add index ID_idx (`ID`);

最后

alter table forum_threads add index ForumID_idx (`ForumID`);
alter table forum_categories add index Parent_idx (`Parent`);

现在我们在表上有了索引，查询应该更快。

注意:两个表之间的连接键应具有相同的数据类型和大小，以便索引正常工作。例如

inner join forum_posts p ON p.ID = t.Last_post  

表中的 ID 和 Last_post 应具有相同的数据类型和大小。

现在我们在使用 OR 条件的查询上仍然存在问题，即使使用正确的索引，查询在某些情况下也会尝试扫描整个表。

WHERE t.ForumID=36 OR c.Parent=36 

那么如何摆脱这个问题，有时 UNION 在这种情况下效果更好。这意味着您运行一个带有条件的查询

WHERE t.ForumID=36

后跟 UNION 具有不同 where 条件的相同查询

WHERE c.Parent=36

但是优化需要更深入地了解表以及将在这些表上执行的可能查询。

上面的解释只是我们如何提高查询性能的一个想法，实时有很多可能性，这些可以在拥有完整的表结构和将要应用于它们的可能查询的同时进行处理。