使用ORDER BY和LIMIT的查询非常缓慢。

Question

由于使用了ORDER BY，我的查询非常缓慢。现在我明白为什么它很慢，但不知道如何让它更快。

表中有大约900,000条记录。(这就是为什么很慢的原因)

SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0   
ORDER BY name 
LIMIT 60000, 90;

我使用limit是因为我使用分页。SQL_NO_CACHE是因为我不想在测试中使用缓存。

这个查询需要大约60秒，时间太长了。它是一个后台任务，如果我能将其减少到5秒，那就可以了。

我在activated_at和deleted_at列上有索引，它们是时间字段。其他的是布尔型，所以不需要索引。

谢谢。

- Michael Koper

Answer 1

我认为索引是解决问题的答案，但您需要找到适合您查询的正确索引，因此您应该尝试以下方法：

EXPLAIN SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0   
ORDER BY name 
LIMIT 60000, 90;

如同这篇非常古老但有用的文章所详细介绍的那样：
http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

本文主要介绍了优化MySQL查询和索引的方法。请参考上述链接以获取更多信息。

- wallisds

---

对于其他阅读此答案的人：我并没有觉得这篇文章有帮助。简而言之，除非你对MySQL非常陌生并且还没有添加索引，否则我建议你继续寻找答案。 - nostromo

Answer 2

你可能会发现这篇文章有帮助：http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/，它涉及ORDER BY和LIMIT的组合。

- chiborg

---

我认为这里的罪魁祸首是大的LIMIT偏移量。正如文章所解释的那样，最好将上述查询转换为“where CONDITION查询”，因为在任何切片内查找都需要恒定的时间。 - rjha94

Answer 3

我猜测是按名称排序导致速度变慢。

为了测试，请移除ORDER子句并检查所需时间。

当您想要按名称排序时，名称字段可能应该建立索引。

- Hobhouse

---

谢谢您的快速回复。是的，我在名称上有一个索引。问题是，我需要按名称排序... - Michael Koper

Answer 4

我建议在最具选择性的布尔字段上添加索引，即如果数据库中只有5％的行具有is_provider = 0，那么您可以显着减少需要扫描其他属性的行数。 如果分布是50/50，那么没有太多意义，但我会查看分布情况，并让其指导您确定哪些索引可能有帮助。 当然，您应该根据实际性能（包括您可能拥有的其他查询）来指导您。

- tvanfosson

---

谢谢你的回答。我已经尝试过修改索引，但是添加或删除并没有任何区别。 - Michael Koper

@Michael - 你尝试过包含名称的复合索引吗？这应该可以减少排序所需的时间。你也可以尝试重构查询以帮助它选择正确的索引，也许使用嵌套。 - tvanfosson

Answer 5

你可以尝试一下

SELECT  * FROM (SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0   
ORDER BY name) t
LIMIT 60000, 90;

为了避免同时使用ORDER BY和LIMIT。

- Lalit Jha

Answer 6

名称列是否作为另一个索引的一部分进行索引？

按索引排序应该相对快速（无需文件排序），除非名称恰好是索引的一部分，而不是第一部分。 请在此处查看更多信息：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

请发布您的EXPLAIN结果，以便我们可以进一步帮助...

- Galz

Answer 7

这里的问题是，Mysql 首先尝试按照表中所有行的 Name 进行排序，然后再尝试根据 Where 条件选择记录。因此，您可以尝试首先基于 Where 条件选择记录，然后在结果集上运行 Order By name。

例如：

SELECT  * FROM (SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0) t ORDER BY name
LIMIT 60000, 90;

- Gaurav Singla

Answer 8

或者，删除Order By子句，并在读取记录后执行排序。这将把工作放在客户端上，但如果您的数据库速度较慢（似乎是这种情况），那么这可能更可靠和更快。

祝好，

Daniel

- Daniel Williams