mysql查询时offset过大影响性能的原因和优化详解

在使用 MySQL 数据库进行分页查询时，为了取得指定页码的数据，常常需要用到 LIMIT 关键字来指定返回记录的偏移量和总记录数。然而，当我们的偏移量越来越大时，服务端处理查询结果的性能将逐渐下降。本篇攻略将详细讲解这个问题的原因以及针对这个问题的优化方案。

问题原因

当我们使用 LIMIT 语句进行分页查询时，MySQL 数据库的处理机制通常是先扫描整个数据表，然后跳过前 N 条记录，最后再取出剩下的 M 条数据。其中，N 表示偏移量，M 表示每页显示的记录数。

由于 MySQL 数据库中存储的数据可以分为多个块，这些块的位置在磁盘上可能是不连续的，因此每次查询时，数据库需要定位到磁盘上不同的位置进行数据的读取。这个过程中，定位的距离越远，性能损耗就越大。

同时，当偏移量非常大时，需要跳过的记录数量会增加，导致查询效率更低。因此，我们在做分页查询时，尽量避免使用过大的偏移量。

优化方案

1. 通过主键优化查询

对于含有大量数据的数据表，我们可以通过指定唯一索引或主键进行分页查询。这样 MySQL 数据库就可以直接利用索引或主键中的顺序进行查询，而不需要进行全表扫描。

例如，对于 user 表中的 id 字段，我们可以使用如下 SQL 语句进行分页查询：

SELECT * FROM user WHERE id > $last_id ORDER BY id LIMIT $page_size

其中，$last_id 表示上一页最后一条记录的 id 值，$page_size 表示每页显示的条数。这样，我们就可以直接利用主键进行分页查询。

2. 通过缓存优化查询

对于一些数据更新频率低且查询频率高的数据表，我们可以使用缓存对数据进行缓存，从而减少数据库的访问次数。

例如，对于某个用户的订单列表，我们可以将用户的订单数据进行缓存，不需要每次访问数据库获取数据。我们可以使用一些缓存机制来实现缓存，例如 Redis 、Memcached 等。

示例说明

示例一

假设我们有一个 messages 表，其中包含 id（自增主键）、sender_id、receiver_id、message、create_time 等字段。现在我们需要查询 sender_id 为 100 的用户中，第 500 条到第 510 条记录的信息。

由于该表中数据量较大，因此我们不能使用 LIMIT 语句直接进行分页查询。此时，我们可以通过先查询 sender_id 为 100 的用户中，第 510 条记录所对应的 id 值作为偏移量，然后使用 >= 操作符进行查询，如下所示：

SELECT * FROM messages WHERE sender_id = 100 AND id >= $last_id ORDER BY id LIMIT 10

其中，$last_id 表示第 510 条记录所对应的 id 值。使用该方式进行分页查询，可以大大提高查询效率。

示例二

假设我们有一个名为 city 的数据表，其中包含 id（自增主键）、province_id 和 name 字段。现在我们需要查询 province_id 为 100 的省份中，第 9000 条到第 9010 条记录的信息。

该表中包含的数据较少，因此我们可以使用 LIMIT 语句进行分页查询。不过，由于需要查询第 9000 条到第 9010 条记录，因此偏移量较大，会导致查询效率降低，如下所示：

SELECT * FROM city WHERE province_id = 100 LIMIT 9000, 10

此时，我们可以通过指定 city 表中的主键（假设为 id 字段）进行分页查询，如下所示：

SELECT * FROM city WHERE province_id = 100 AND id > $last_id ORDER BY id LIMIT 10

其中，$last_id 表示上一页最后一条记录所对应的 id 值。使用该方式进行分页查询，可以提高查询效率。