使用ORDER BY时，MySQL查询变得非常缓慢

Question

使用ORDER BY时，MySQL查询变得非常缓慢

3

这是一个执行时间大约为1毫秒的MySQL查询：

SELECT SQL_NO_CACHE DISTINCT invoice.*,
GROUP_CONCAT(DISTINCT line.guid) as line 
FROM invoice  
LEFT JOIN ligne ON line.invoice = invoice.guid 
GROUP BY invoice.guid 
WHERE acompte = 1
LIMIT 0, 100;

如果我在查询中添加ORDER BY invoice.date，查询会变得非常缓慢，需要大约3秒才能完成。

如果我删除LEFT JOIN（和GROUP_CONCAT），则查询时间再次为1毫秒。

我添加了EXPLAIN以查看当查询变慢时MySQL正在执行什么操作，我可以看到它正在使用一个临时文件：

1   SIMPLE  invoice index   NULL    PRIMARY 4   NULL    25385   Using temporary; Using filesort
1   SIMPLE  line    ref invoice invoice 5   gestixi.invoice.guid    1   Using index

我确信有一种方法可以加速这个查询，但我找不到。有什么想法吗？

请注意，我无法在“日期”上添加索引（顺便说一下，这没有改变任何内容），因为我希望我的用户能够对表中的每个字段进行排序。

另请注意，“invoice.guid”，“line.invoice”，“line.guid”和“acompte”都已经建立了索引。

编辑

如果我先执行一个没有LEFT JOIN但包含ORDER BY子句的查询以获取所需行的ID，然后再使用这些ID作为WHERE子句执行第二个查询（如上所述），则可以在不到10ms的时间内获得结果。

这使我相信必须有一种方法可以加速这个查询而无需添加索引。

- Nicolas BADIA

尝试在“invoice.date”字段上添加索引，因为当您使用order by子句时，它将被使用。检查带有索引的order by子句的explain，并检查是否已使用。 - Meherzad

就像我说的那样，我不想在 invoice.date 上添加索引，因为如果这样做，我将不得不在表的所有字段上添加索引。无论如何，我尝试在其上添加索引，但它没有被使用（也没有改变任何东西）。 - Nicolas BADIA

好的，我理解错了你的意思... 无论如何，请尝试在 invoice.date, invoice.guid 上创建复合索引，如果该索引未被使用，则尝试明确告诉查询优化器使用 use index。 - Meherzad

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RandomSeed · Accepted Answer

我担心如果您必须允许用户对任何字段进行排序（并且该排序使用索引），则需要为每个可能的排序创建一个索引。根据定义，否则不可能做到这一点。对给定行进行排序只能利用此行上的索引。

在这里我看不出有什么其他选择。要么减少要排序的行数（25k行是一个比较大的结果集，您的用户真的需要那么多行吗？），要么不允许对所有行进行排序。

请注意，查询通常无法使用表格中的多个索引。如其他人所建议的，复合索引对于您提到的查询更好，尽管我宁愿建议相反的顺序((guid, date))（查询首先需要选择每个guid，然后对应地对每个guid的行进行排序）。

还要在line(guid, acompte, invoice)上添加索引。

（上述关于索引的建议假设MyISAM表）

就查询本身的优化而言，考虑到简单的执行计划，很少有更多可做的工作。

您可能会获得更好的结果，也可能不会。

SELECT
    invoice.*, -- DISTINCT is redudant here because of the GROUP BY clause
    GROUP_CONCAT(ligne_acompte.guid) as line  -- DISTINCT is (presumably) redundant here because guid is (presumably) unique
FROM invoice  
LEFT JOIN (
    SELECT guid, invoice
    FROM line
    WHERE acompte = 1
) AS ligne_acompte ON ligne_acompte.invoice = invoice.guid 
GROUP BY invoice.guid
ORDER BY invoice.date;