优化SQL查询

Question

我该如何进一步优化这个查询？

SELECT * FROM
    (SELECT `item`.itemID, COUNT(`votes`.itemID)  AS `votes`,
           `item`.title, `item`.itemTypeID, `item`.
           submitDate, `item`.deleted, `item`.ItemCat,
           `item`.counter, `item`.userID, `users`.name,
           TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' ,
           `myItems`.userID as userIDFav, `myItems`.deleted as myDeleted
      FROM    (votes `votes` RIGHT OUTER JOIN item `item`
                  ON (`votes`.itemID = `item`.itemID))
           INNER JOIN
              users `users`
           ON (`users`.userID = `item`.userID)
    LEFT OUTER JOIN
              myItems `myItems`
           ON (`myItems`.itemID = `item`.itemID)
     WHERE (`item`.deleted = 0)
     GROUP BY `item`.itemID,
              `votes`.itemID,
              `item`.title,
              `item`.itemTypeID,
              `item`.submitDate,
              `item`.deleted,
              `item`.ItemCat,
              `item`.counter,
              `item`.userID,
              `users`.name,
              `myItems`.deleted,
              `myItems`.userID
    ORDER BY `item`.itemID DESC) as myTable
where myTable.userIDFav = 3 or myTable.userIDFav is null
            limit 0, 20 

我正在使用MySQL。

谢谢。

- Armadillo

Answer 1

这个查询的分析器会给出什么结果？ 如果不知道表中有多少行，就无法进行任何优化。因此，请运行分析器，您将看到哪些部分的成本最高。

- theomega

Answer 2

当然，正如@theomega所说，看看执行计划。

但我建议尝试“清理”语句。（我不知道哪个更快 - 这取决于表的大小。）通常，我会尝试从一个干净的语句开始，并从那里开始优化。但是通常，一个干净的语句使优化器更容易想出一个好的执行计划。

所以这里有一些关于你的语句的观察，可能会使事情变慢：

- 几个外连接（使得优化器难以找到要使用的索引） - 一个group by - 很多列进行分组

就我所了解的你的SQL而言，这个语句应该完成大部分你的工作：

SELECT `item`.itemID, `item`.title, `item`.itemTypeID, `item`.
       submitDate, `item`.deleted, `item`.ItemCat,
       `item`.counter, `item`.userID, `users`.name,
       TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' 
  FROM    (item `item` INNER JOIN users `users`
       ON (`users`.userID = `item`.userID)

WHERE

当然，这样做会忽略你外部连接的表中的信息，建议尝试通过子查询添加所需的列：

SELECT `item`.itemID, 
       (SELECT count (itemID)
        FROM votes v
       WHERE v.itemID = 'item'.itemID) as 'votes', <etc.>

这样，您可以摆脱一个外连接和分组。外连接被子查询替换，因此存在一种权衡，可能对“更清晰”的语句不利。

根据项和myItems之间的基数，您可以执行相同的操作，或者必须坚持使用外连接（但无需重新引入分组）。

希望这可以帮助您。

- IronGoofy

Answer 3

一些快速的半随机想法：

您的itemID和userID列是否已经建立索引？

如果在查询开头添加"EXPLAIN"并运行它，会发生什么？它使用索引吗？它们是否合理？

您需要运行整个内部查询并对其进行过滤，还是可以将where myTable.userIDFav = 3 or myTable.userIDFav is null部分移动到内部查询中？

- Mike Woodhouse

Answer 4

你的 Group By 列表中似乎有太多字段了，因为其中一个是 itemID，我怀疑你可以使用内部 SELECT 进行分组，然后使用外部 SELECT 返回所需的字段集。

- AnthonyWJones

Answer 5

你不能在WHERE (item.deleted = 0)中添加where子句myTable.userIDFav = 3 or myTable.userIDFav is null吗？

敬礼
Lieven

- Lieven

Answer 6

看一下你的查询语句是如何构建的。你连接了很多东西，然后将输出限制为20行。你应该在items和myitems上进行外连接，因为你的条件只适用于这两个表，将输出限制为前20行，然后再进行连接和聚合。在这里，你正在执行很多将被丢弃的工作。

- SFA