mysql之我应该避免在查询大表时使用 ORDER BY 吗

在我们的应用程序中，我们有一个页面向用户显示一组数据，实际上是其中的一部分。它还允许用户通过自定义字段对其进行排序。所以最后一切都归结为这样的查询:

SELECT name, info, description FROM mytable 
WHERE active = 1 -- Some filtering by indexed column 
ORDER BY name LIMIT 0,50; -- Just a part of it 

只要表的大小相对较小(仅在我们部门本地使用)，这种方法就可以正常工作。但现在我们必须扩展这个应用程序。让我们假设，该表有大约一百万条记录(我们预计很快就会发生)。订购会发生什么？我是否理解正确，为了执行此查询，MySQL 每次都必须对一百万条记录进行排序并给出其中的一部分？这似乎是一个非常耗费资源的操作。

我的想法是简单地关闭该功能并且不让用户选择他们的自定义排序(可能只是过滤)，以便顺序是自然的(按 id 降序排列，我相信索引可以处理那个)。

或者有没有办法让这个查询在排序时更快地工作？

更新:

这是我从官方 MySQL 上读到的 developer page .

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

....

The key used to fetch the rows is not the same as the one used in the ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

所以是的，看起来 mysql 确实会遇到这样的查询问题？那么，我该怎么做 - 根本不使用订单部件？

请您参考如下方法:

这里的“问题”似乎是您有 2 个要求(在示例中)

• 活跃 = 1
• 按名称排序 LIMIT 0, 50

前者可以通过在 active 字段上添加索引轻松解决 您可以通过在 name

由于您在同一个查询中执行这两项操作，因此您需要将其合并到一个索引中，以便您快速解析 active 值，然后从那里获取前 50 个 name s.

因此，我想这样的事情会帮助你:

CREATE INDEX idx_test ON myTable (active, name) 

(理论上，一如既往，先试后买!)

请记住，天下没有免费的午餐；您需要考虑添加索引也有缺点:

• 索引会使您的 INSERT/UPDATE/DELETE 语句(稍微)变慢，通常效果可以忽略不计，但只有测试才会显示
• 索引将需要数据库中的额外空间，将其视为位于实际数据旁边的附加(隐藏)特殊表。索引将只保存所需的字段 + 原始表的 PK，这通常比整个表的数据少很多，但对于“数百万行”，它可以加起来。
• 如果您的查询选择了一个或多个不属于索引的字段，那么系统将必须首先从索引中获取匹配的主键字段，然后通过PK。这可能仍然比没有索引时快(很多)，但在执行类似 SELECT * FROM ... 的操作时请记住这一点:您真的需要所有字段吗？
• 在示例中您使用了 active 和 name 但从文本中我了解到这些可能是“动态的”，在这种情况下您必须预见各种组合。从实际角度来看，这可能不可行，因为每个索引都会带来上述缺点，并且每次添加索引时，您都会再次将 supra 添加到该列表(累积)。

PS:为了简单起见，我使用PK，但在 MSSQL 中，它实际上是聚集索引的字段，这通常是同一件事。我猜 MySQL 的工作方式类似。