优化MySQL查询以避免“Using where; Using temporary; Using filesort”错误。

Question

我使用MySQL为我的网站构建了一个自定义论坛。列表页面本质上是一个表格，包含以下列：主题、最后更新时间和#回复数。

数据库表格有以下列：

id
name
body
date
topic_id
email

一个主题的 topic_id 为“0”，而回复的 topic_id 则为其父主题的 topic_id。

SELECT SQL_CALC_FOUND_ROWS
    t.id, t.name, MAX(COALESCE(r.date, t.date)) AS date, COUNT(r.id) AS replies
FROM
    wp_pod_tbl_forum t
LEFT OUTER JOIN
    wp_pod_tbl_forum r ON (r.topic_id = t.id)
WHERE
    t.topic_id = 0
GROUP BY
    t.id
ORDER BY
    date DESC LIMIT 0,20;

这个表格中大约有2100个项目，查询通常需要长达6秒的时间。我在“topic_id”列上添加了一个索引，但效果不明显。有没有什么方法可以加快查询速度而不需要进行重大重构？

编辑：还没有完全解决。我似乎无法使下面的示例正常工作。

- Matt

Answer 1

SELECT  id, name, last_reply, replies
FROM    (
        SELECT  topic_id, MAX(date) AS last_reply, COUNT(*) AS replies
        FROM    wp_pod_tbl_forum
        GROUP BY
                topic_id
        ) r
JOIN    wp_pod_tbl_forum t
ON      t.topic_id = 0
        AND t.id = r.topic_id
UNION ALL
SELECT  id, name, date, 0
FROM    wp_pod_tbl_forum t
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    wp_pod_tbl_forum r
        WHERE   r.topic_id = t.id
        )
        AND t.topic_id = 0
ORDER BY
       date DESC
LIMIT 0, 20

如果您的表是MyISAM或id不是PRIMARY KEY，您需要在(topic_id, id)上创建一个组合索引。

如果您的表是InnoDB并且id是PRIMARY KEY，只需在(topic_id)上创建一个索引即可（id将被隐式添加到索引中）。 更新 如果您在(topic_id, id)和(date, id)上都有索引，则此查询很可能会更有效：

请参阅我博客中关于性能细节的文章：

• 选择最后的论坛帖子

这个查询在100,000行样本数据上完成时间为30毫秒：

SELECT  id, name, last_reply,
        (
        SELECT  COUNT(*)
        FROM    wp_pod_tbl_forum fc
        WHERE   fc.topic_id = fl.topic_id
        ) AS replies
FROM    (
        SELECT  topic_id, date AS last_reply
        FROM    wp_pod_tbl_forum fo
        WHERE   id = (
                SELECT  id
                FROM    wp_pod_tbl_forum fp
                WHERE   fp.topic_id = fo.topic_id
                ORDER BY
                        fp.date DESC, fp.id DESC
                LIMIT 1
                )
                AND fo.topic_id <> 0
        ORDER BY
                fo.date DESC, fo.id DESC
        LIMIT 20
        ) fl
JOIN    wp_pod_tbl_forum ft
ON      ft.id = fl.topic_id
UNION ALL
SELECT  id, name, date, 0
FROM    wp_pod_tbl_forum t
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    wp_pod_tbl_forum r
        WHERE   r.topic_id = t.id
        )
        AND t.topic_id = 0
ORDER BY
       last_reply DESC, id DESC
LIMIT  20

这个查询需要两个索引才能高效执行。

如果你的表是 InnoDB 类型 并且 id 是主键，那么可以省略在上述的 indexes 中添加 id 索引。

- Quassnoi

---

字段列表中的列'date'不明确是什么意思？ - Matt

@Quassnoi - 你能解释一下发生了什么吗？如果主题没有回复，"UNION ALL" 会用 "date" 替换 "last_reply" 吗？ - Matt

在您的情况下，LEFT JOIN 是低效的，因为您无法使用 INDEX FOR GROUP BY 按表中不在 JOIN 中的列进行分组。我将您的查询重写为两个其他查询的组合，第一个使用 INDEX FOR GROUP BY 来构建聚合，第二个则没有 JOIN 和 GROUP BY。 - Quassnoi

Answer 2

你可能想将其拆分为一组子查询（作为内部查询）。我需要模式来真正运行，但如果你

SELECT t.id, t.name, MAX(COALESCE(r.date, t.date)) AS date, COUNT(r.id) AS replies  
FROM (
   SELECT (id, name, date)
   FROM wp_pod_tbl_forum
   WHERE topic_id = 0  
) as t 
LEFT OUTER JOIN
   wp_pod_tbl_forum r
WHERE
   r.topic_id = t.id
GROUP BY
    t.id
ORDER BY
    date DESC LIMIT 0,20;

这可能有助于加快速度，但它甚至可能不是最佳答案（可能存在错误）。

有很多方法来做到这一点，但在 SQL 调优时最重要的事情是在执行操作之前尽可能减少每个集合。

- Dave Morgan