Order By 导致我的查询运行非常缓慢。

Question

Order By 导致我的查询运行非常缓慢。

4

我有一个如下的SQL查询

select *
from incidents
where remote_ip = '192.168.1.1' and is_infringement = 1
order by reported_at desc
limit 1;

目前这个查询需要运行 313.24 秒。

如果我移除 order by，那么查询变成了：

select *
from incidents
where remote_ip = '192.168.1.1' and is_infringement = 1

那么，运行只需要0.117秒。

reported_at列已被索引。

所以，有两个问题。首先，为什么使用order_by语句会花费这么长时间？其次，如何加快速度？

编辑：回答下面的问题，以下是使用explain时的输出：

'1'，'SIMPLE'，'incidents'，'index'，'uniqueReportIndex，idx_incidents_remote_ip'，'incidentsReportedAt'，'4'，NULL，'1044'，'100.00'，'Using where'

表创建语句如下：

CREATE TABLE `incidents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `incident_ip_id` int(10) unsigned DEFAULT NULL,
  `remote_id` bigint(20) DEFAULT NULL,
  `remote_ip` char(32) NOT NULL,
  `is_infringement` tinyint(1) NOT NULL DEFAULT '0',
  `messageBody` text,
  `reported_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Formerly : created_datetime',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueReportIndex` (`remote_ip`,`host_id_1`,`licence_feature`,`app_end`),
  UNIQUE KEY `uniqueRemoteIncidentId` (`remote_id`),
  KEY `incident_ip_id` (`incident_ip_id`),
  KEY `id` (`id`),
  KEY `incidentsReportedAt` (`reported_at`),
  KEY `idx_incidents_remote_ip` (`remote_ip`)
)

注意：我省略了一些与主题无关的字段，所以索引比字段多，但是您可以安全地假设所有索引的字段都在表中。

- SamBremner

2

没有排序的情况下，您的查询只会检索任意一条记录。有排序的情况下，需要先对数据进行排序，然后检索第一条记录。 - Prisoner

2

“reported_at”列已经被索引。这取决于它如何被索引。在查询前加上EXPLAIN并将输出放入问题中。同时，也请提供表结构（即CREATE TABLE语句）。 - axiac

等一下。你的第二个查询没有 limit 子句？你可以在不到1秒的时间内检索所有记录吗？有多少条记录？ - Cristian Lupascu

@axiac 的编辑在上面。 - SamBremner

@Rodney，我尝试了升序排列，结果很明显，现在只需要大约3秒钟。唯一的问题是我需要最新的记录，而不是最老的。 - SamBremner

显示剩余5条评论

1个回答

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

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

- axiac · Accepted Answer

EXPLAIN的输出结果显示，由于ORDER BY从句，MySQL决定使用incidentsReportedAt索引。它按照索引提供的顺序从表数据中读取每一行并检查其中的WHERE条件。这需要读取大量分散在整个表中的信息。流程不佳。

更新：

OP在列reported_at和report_ip上创建了一个索引（如原始答案中建议的），执行时间从313秒降至133秒。有所改善，但仍不够好。我认为导致执行时间长的原因是对每一行访问表数据以验证WHERE子句中的is_infringement = 1部分，即使将其添加到索引中也帮助不大。

OP在评论中表示：

“经过进一步研究，并将索引更改为相反的方式（remote_ip，reported_at），查询现在非常快（0.083秒）。”

这个索引更好，因为 remote_ip = '192.168.1.1'条件过滤掉了很多行。可以使用现有的uniqueReportIndex索引实现同样的效果。可能是原来的基于reported_at的索引让MySQL认为最好使用它按所需的ORDER BY顺序检查行，而不是先过滤再排序。

我认为MySQL使用新的(remote_ip，reported_at)索引进行过滤(WHERE remote_ip = '192.168.1.1')和排序(ORDER BY reported_at DESC)。 WHERE条件提供了一个易于识别并使用此索引进行排序的小候选行列表。

原始答案如下，提供的建议不正确，但帮助OP找到了正确的解决方案。创建在列reported_at和report_ip上的新索引，然后查看EXPLAIN所说的内容以及查询执行的情况。这应该会更快。甚至可以在列reported_at、report_ip和is_infringement上创建新的索引（索引中的列顺序非常重要）。

在三个列上创建的索引帮助MySQL识别行，而无需读取表数据（因为“WHERE”和“ORDER BY”子句中的所有列都在索引中）。仅因为“SELECT *”而返回行时，它才需要读取表数据。创建新索引后（可以是两个或三个列），请删除旧索引“incidentsReportedAt”。它不再需要；它使用磁盘和内存空间，需要时间进行更新，但未被使用。将使用新索引（在第一位置具有“reported_at”列）。

两个列上的索引需要更多地读取表数据以满足“is_infringement = 1”的条件。查询可能比使用三列索引执行地稍慢。另一方面，在表更新和磁盘和内存空间使用方面会获得一些收益。

选择在两个或三个列上创建索引的决策取决于提问中运行查询的频率以及其服务对象（访客、管理员、cron作业等）。