mysql - MySQL慢速分组/排序

以下查询相对较慢(在约6000行时为0.7秒)

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而不是item.DATE进行订购，则可大大提高速度。事务联接到一个大表(约25万行)，并且是一对多的。日期列具有索引。

有什么方法可以普遍提高ORDER BY的性能吗？

编辑:在items.user，transactions.item和items.date上建立索引。项有49列，用户76和事务17。

最佳答案

索引会影响ORDER BY子句的性能。这个MySQL manual page可能值得您花时间。本质上，如果您按列进行排序，该列是MySQL用于查询的索引的一部分，则MySQL可以将索引用于排序，而不是数据本身。

在您的特定查询中，DATE列具有索引这一事实无关紧要，因为该索引可能未在您的查询中使用。您的WHERE语句包含items.ACTIVE和items.DELETED_AT，如果这些列的索引用于不包括WHERE列的DATE，则MySQL无法使用该索引对DATE进行排序，并且很可能诉诸于文件种类。

如果您可以提供一个可由WHERE和ORDER BY都使用的索引，那么您将获得优化的提升。在这种情况下，items.ACTIVE似乎是低基数列，因此，假设items.DELETED_AT是日期，我可能会为该表尝试像INDEX(DELETED_AT,DATE)这样的索引。

使用 EXPLAIN SELECT... 也可以查看有关该处发生的事情的更多信息，您可能会得到一些进一步的见解。

关于mysql - MySQL慢速分组/排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8631488/