MySQL慢查询：分组/排序

Question

以下查询速度比较慢（大约0.7秒，6千行左右）。

SELECT items.*, COUNT(transactions.ID)
   FROM items
  INNER JOIN users ON (items.USER = users.ID)
   LEFT JOIN transactions ON (items.id = transactions.item)
  WHERE items.ACTIVE = 1
    AND items.DELETED_AT IS NULL
  GROUP BY items.ID
  ORDER BY items.DATE DESC
  LIMIT 20

当按照items.ID DESC而非items.DATE排序时，速度会显著加快。交易连接到一个大表（约250k行），是一对多的关系。日期列有索引。

有没有通用的方法来提高ORDER BY的性能？

编辑：items.user、transactions.item和items.date上都有索引。Items有49个列，users有76个，transactions有17个。

- Seliquity

---

你能提供三个表的模式和特别是索引吗？ - TetonSig

我能想到的唯一方法是首先在子查询中选择按日期排序并通过WHERE子句过滤的项目，（如果MySQL不支持子查询中的ORDER BY，则使用视图），然后在封闭查询中进行连接。 （我没有将此写为答案，因为SO不鼓励投机取巧的回答。） - Mike Nakis

你尝试过在 GROUP BY 中包含 items.DATE 吗？（而且要放在第一个位置）？ - Andriy M

Answer 1

索引可能会影响ORDER BY子句的性能。这个MySQL手册页面可能值得您花费一些时间来阅读。基本上，如果您按使用于查询的索引的一列进行排序，MySQL可以使用该索引进行排序，而不是使用数据本身。

在您特定的查询中，DATE列具有索引并不重要，因为该索引可能未在您的查询中使用。您的WHERE语句包含items.ACTIVE和items.DELETED_AT，如果那些列有一个用于不包括DATE列的WHERE的索引，则MySQL无法使用索引按DATE排序，并且可能重新排序以文件排序方式处理。

如果您可以想出一个可同时由WHERE和ORDER BY使用的索引，则可以获得优化提升。在这种情况下，items.ACTIVE似乎是一个低基数列，因此假设items.DELETED_AT是一个日期，我可能会尝试使用INDEX(DELETED_AT,DATE)索引。

使用EXPLAIN SELECT...来查看更多关于这方面的信息，您可能会得到一些进一步的见解。

- zombat

Answer 2

SELECT *
FROM (SELECT * FROM wp_users WHERE 1 GROUP BY ID limit 0,10) as X
ORDER BY ID DESC

上述查询很完美，我在一个非常长的数据库中使用过它。它按照内部选择查询获取到的10个或(xx)个项目进行排序，因此非常快速！

- Sanni Poriya

---

你甚至没有使用 Left_join。 - Monclee

Answer 3

以下是可能（并非保证）有所帮助的事项：

1. 消除项目中的星号*，并单独列出每个字段。49个列实在太多了，您真的需要全部吗？
2. 通常引擎会优化查询，以便在连接时考虑限制条件。也许引擎使用的计划没有这样做（需要查看解释计划结果），因此重新排列where子句和连接可能会有所帮助（不太可能）。 （见下文）
3. 如果经过一段时间的更新、插入和删除操作后，表格统计信息可能会失效，需要为每个表格重建。

SELECT items.[list fields], COUNT(transactions.ID)
   FROM items
  INNER JOIN users ON (items.USER = users.ID)
        AND items.Active=1 
        AND items.DELETED_AT is Null
   LEFT JOIN transactions ON (items.id = transactions.item)
  GROUP BY items.ID
  ORDER BY items.DATE DESC
  LIMIT 20

- xQbert