时间戳

首页 > 解决方案 > 为什么从 MYSQL 中提取行的最后一部分（LIMIT 和 OFFSET）比提取所有其他部分要慢得多？

mysql - 为什么从 MYSQL 中提取行的最后一部分（LIMIT 和 OFFSET）比提取所有其他部分要慢得多？

问题描述

我必须从数据库（MYSQL MariaDB）中提取相当大的数据块，因此我决定将其分成小块（限制 = 1000 行）。总的来说，我需要提取 ~9228 行（听起来不多，但当我尝试通过一个查询一次获取所有 9228 行时，获取时间达到 100 - 120 秒）。

当我获取前 8 个数据块（每个 1000 行）时，每个查询都很好 ~0.4 秒。但是，当我尝试提取最后 228 行时，一切都变得非常慢 - 如果我使用 80 秒，LIMIT 1000 OFFSET 9000或者当我使用 LIMIT 的确切行数时为 50 秒LIMIT 228 OFFSET 9000。但是用于获取总行数的查询需要 30 秒，因此两次查询总共需要 80 秒。

我获取数据的 sql 查询如下所示：

SELECT events.eventid, functions.triggerid FROM events
INNER JOIN functions ON  events.objectid = functions.triggerid
WHERE
events.name LIKE 'DISCONNECT MSK-AP%'
OR events.name LIKE 'AP MSK-AP%'    # '%MSK-AP%'  is much slower than OR
AND events.value = 1
AND events.clock >= '1588280400' 
AND events.clock <= '1590958799'
GROUP BY events.eventid
ORDER BY events.eventid DESC
LIMIT 1000 OFFSET 0;  # SO OFFSET COULD BE 0, 1000, 2000,  ... 8000, 9000

我获取总行数的 sql 查询（慢了 30 秒！）如下：

SELECT COUNT(distinct(events.eventid)) FROM events
INNER JOIN functions ON  events.objectid = functions.triggerid
WHERE
events.name LIKE 'DISCONNECT MSK-AP%'
OR events.name LIKE 'AP MSK-AP%'
AND events.value = 1
AND events.clock >= '1588280400' 
AND events.clock <= '1590958799';

我的数据库版本：

protocol_version 10 slave_type_conversions
version 5.5.60-MariaDB version_comment MariaDB Server version_compile_machine x86_64 version_compile_os Linux

为什么获取最后一个块的最后一个查询与其他查询相比如此缓慢，我能做些什么来解决这个问题？临时数据库表可以在这种情况下提供帮助吗？

为什么我不确定问题的答案是否适合我的情况： 为什么 MYSQL 更高的 LIMIT 偏移会减慢查询速度？

因为问题与偏移大小无关，例如：

LIMIT 100 OFFSET 9100;- 0.25 秒但 LIMIT 100 OFFSET 9200;- 114 秒！

因此，当 offset + limit 接近或大于总行数（9228）时，就会出现问题！

标签： mysqldatabasemariadb

解决方案

OFFSET糟糕的表现。

更好的方法是“记住你离开的地方”。

讨论：http: //mysql.rjweb.org/doc.php/pagination

为什么比全读慢？

使用OFFSET时，查询首先计算 给出的行数OFFSET，然后传递 给出的行数LIMIT。所以，它变得越来越慢。最终偏移量与读取整个表所花费的时间大致相同。

推荐阅读