MySQL排序优化

Question

以下是表的结构：-

文章：ID，标题，描述，发布日期时间，浏览次数，已发布

主键：ID

使用的查询：

Select Title FROM Article ORDER By ViewsCount DESC, PublishedDateTime ASC

您可以看到，我混合使用 ASC 和 DESC，并根据 MySQL Order By 优化，索引将不会被使用。

我考虑使用一个由 ViewsCount 和 PublishedDateTime 组成的复合索引。您是否建议使用两个不同的键而不是使用复合索引？但是我之后读到过的是，如果两个字段都要使用，则复合索引比使用两个不同的键更好。

一些额外的信息：

该表包含超过 550K 条记录，而且我在添加和删除索引时遇到了很大的麻烦。您们有什么建议吗？我应该在小样本上进行测试吗？

以下是更多见解：

使用的索引：
1）ViewsCount
2）PublishedDateTime
3）ViewsCount 和 PublishedDateTime（名为 ViewsDate_Index）

A) EXPLAIN 查询使用 ASC 和 DESC:

EXPLAIN SELECT title FROM  `article` ORDER BY ViewsCount DESC , PublishedDateTime ASC  LIMIT 0 , 20    

====+===============+=========+======+===============+=====+=========+======+========+================+
id  | select_type   | table   | type | possible_keys | key | key_len | ref  | rows   | Extra
1   | SIMPLE        | article | ALL  | NULL          | NULL| NULL    | NULL | 550116 |  Using filesort
====+===============+=========+======+===============+=====+=========+======+========+================+

B) 使用相同的排序顺序解释查询：

EXPLAIN SELECT title FROM  `article` ORDER BY ViewsCount DESC , PublishedDateTime DESC  LIMIT 0 , 20

====+===============+=========+=======+===============+=================+=========+=============+========+================+
id  | select_type   | table   | type  | possible_keys | key             | key_len | ref         | rows   | Extra
1   | SIMPLE        | article | index | NULL          | ViewsDate_Index | 16      | NULL        | 550116 |  
====+===============+=========+=======+===============+=================+=========+=============+========+================+

你可以看到，如果ViewsCount和PublishedDateTime按照相同的排序顺序排列，则使用ViewsDate_Index索引。我发现奇怪的一件事是，possible_keys为空，但它仍然选择了一个索引。有人能解释一下这个原因吗？

此外，关于在这个表上添加索引的任何建议，因为添加新索引需要很长时间。任何关于此方面的解决方法或帮助都将不胜感激。

- ConcealedIdentity

---

你是否有性能测试用例？如果没有，那么你就没有性能问题，其他一切都是“过早的”。如果有，那么你可以尝试各种方法并运行性能分析来确定哪个最适合你。 - user166390

请参考以下类似问题：https://dev59.com/dVPTa4cB1Zd3GeqPgR6S - greut

@pst：我已经修改了帖子并分享了更多细节。 - ConcealedIdentity

Answer 1

首先，运行整个查询并查看其性能。当您有一些基准时，将查询插入到MySQL控制台中，并在其前面添加EXPLAIN。MySQL不会执行查询，但它会显示执行查询的计划，包括它认为需要优化的位置，它将使用哪些索引，它必须遍历多少行以及它将遍历每组行的效率等其他信息。通过基准测试来评估性能问题是最好的方法。经常使用它。

- jmkeyes

---

我如何知道MySQL认为哪些地方是重要的优化点。 - ConcealedIdentity

因为MySQL对您的数据了解得比您更多。插入您的查询，将EXPLAIN放在前面，然后查看结果。MySQL会告诉您获取所需结果所需执行的确切操作。使用这些结果来放置索引。 - jmkeyes

我已经使用EXPLAIN提供了更多的见解。您能否请看一下并回答文章末尾提到的几个问题。 - ConcealedIdentity

你有没有任何添加索引到大量数据表中的技巧或解决方法？实际上，这妨碍了我在找到最佳解决方案之前进行不同测试。 - ConcealedIdentity

Answer 2

在实践中，即使在这里使用ORDER BY ViewsCount, PublishedDateTime，索引也不会被使用，因为您选择了所有列并且没有应用任何条件。这是一个真正的查询吗？因为任何条件都会破坏您的优化。

如果您的表很小，您将作为整个表进行提取，索引只会减慢您的查询速度。（与原始查询相关：SELECT * FROM article ORDER BY ViewsCount DESC, PublishedDateTime;）

更新

如果您有500K+行数据，我认为您将使用LIMIT子句。我会执行以下操作：

1. 在（ViewCount，PublishedDateTime）上添加索引

2. 将查询重写如下：

   SELECT Title
   FROM (
       SELECT id
       FROM article
       ORDER BY ViewsCount DESC, PublishedDateTime
       LIMIT 100, 100
   ) ids
   JOIN article
   USING (id);
   

从覆盖索引的数据子集中操作将有助于排序。连接只会通过ID获取标题。

更新2

另一个查询，当ViewCount的基数相对较小时可能效果更好（尽管您应该进行基准测试）：

SELECT Title
FROM (
  SELECT ViewCount
  FROM article
  GROUP BY ViewCount DESC) as groups
JOIN article USING (ViewCount)
LIMIT 0, 100;

它也假定您在表上拥有（ViewCount，PublishedDateTime）索引。

- newtover

---

我已经提供了更详细的信息。你现在可以帮忙吗？ - ConcealedIdentity

谢谢。但是那并没有帮助太多。我希望你已经仔细查看了我分享的完整信息。我只是感到困惑，因为你认为我会假设LIMIT。而如果你能看到我分享的查询，你就会发现我在其中使用了LIMIT。 - ConcealedIdentity

请告知我是否需要分享“解释”结果。 - ConcealedIdentity

@ConcealedIdentity，如果您已经设置了索引，我知道执行计划。但是我现在不明白的是，您如何衡量某些操作是否有很大帮助或没有多大帮助。 - newtover

由于我的当前查询和您提出的查询执行时间大致相同。 - ConcealedIdentity

Answer 3

ORDER By ViewsCount DESC, PublishedDateTime ASC

如果你有MySQL 8.0，它可以高效地处理

INDEX(ViewsCount DESC, PublishedDateTime ASC)

8.0 是 MySQL 开始支持在索引规范中使用 ASC/DESC。

- Rick James