MySQL的慢组通过/以便通过

问题描述：

下面的查询是相对缓慢（0.7秒〜6K行）MySQL的慢组通过/以便通过

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的索引。项目有49列，用户76和交易17.

你可以提供模式，特别是三个表的索引？ – TetonSig 2011-12-25 20:56:43

我能想到的唯一事情是首先选择由WHERE子句筛选并按子查询日期排序的项目（或者在视图中，如果MySQL不支持子查询中的ORDER BY），然后在封闭查询中执行你的加入。（我不会写这个作为答案，因为SO不鼓励推测性答案。） – 2011-12-25 20:57:49

你有没有尝试在'GROUP BY'中包含'items.DATE'（在第一个位置）？ – 2011-12-25 23:16:15

答

索引可以影响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...也可以查看更多关于发生的事情，您可能会获得一些进一步的见解。

答

这些东西，可以（读不能保证）的帮助。

消除*项目。*并单独列出每个字段。 49 列是不是你真的需要他们所有？
通常，引擎会优化查询，以便在连接上考虑限制条件。也许引擎使用的计划没有这样做（需要解释计划结果知道），所以重新安排where子句和加入可能（不可能）迫使引擎考虑这一点。（见下文）
重建表的统计数据，如果许多更新，插入，删除具有发生随着时间的推移，它可能表的统计信息是关闭和必须 rebuilt 为每个表

。

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

答

SELECT * FROM（SELECT * FROM WHERE 1 GROUP BY ID限0,10 wp_users）为X ORDER BY ID DESC

上面的查询运行完美，我有一个很长的数据库，用它。它通过我们从内部选择查询获得的10个OR（xx）项目的列表进行排序，所以它非常快！

MySQL的慢组通过/以便通过

相关推荐