MySQL limit offset分页查询可能存在的问题

MySQL limit offset分页查询语句

有 3 种形式：

• limit 10：不指定 offset，即 offset = 0 ，表示读取第 1 ~ 10 条记录。
• limit 20, 10：offset = 20，因为 offset 从 0 开始，20 表示从第 21 条记录开始读取，所以表示读取第 21 ~ 30 条记录。
• limit 10 offset 20：和上一条功能相同，只是换了一种写法，不常用。

limit offset分页查询语句可能存在的问题

• 功能问题，漏掉部分数据

  在查询出满足条件一页数据后，对这些数据做了修改，修改后这些数据不再满足查询条件，再查询下一页数据时就会漏掉部分满足条件的数据。

• 性能问题

  深度分页问题，数据量很大时，例如 limit 10000000, 100 ，存储引擎层会查询 10000100 条数据返回给 server层，server 层过滤掉前 10000000 条后返回最后 100 条给客户端。

其中深度分页的性能问题网上很多人都写过，本文主要对第一个问题说明一下，其实两个问题的解决方案都是一样的。

limit offset分页查询语句漏掉部分数据问题详细说明

场景描述

存在如下表，主键 id 是自增整数，需要在应用程序中每天 00:00:00 遍历这个表，查询所有 flag字段为 0 的记录，将flag 字段修改为 1。

存在的问题

如果使用 limit offset 分页查询，每页查询 2 条数据，查询第一页数据：

SELECT * FROM student where flag = 0 limit 0,2;

然后将这两条记录的 flag 字段改为 1：

UPDATE student set flag = 0 where id in (6,8);

此时数据库中的数据变为：

接着循环查询第 2 页的数据：

SELECT * FROM student where flag = 0 limit 2,2;

这样就导致 id 为 9 和 10 的这两条记录没有被查询到，这两条数据就不会被处理。

解决方案

查询第一页

SELECT * FROM student where id > 0 and flag = 0 order by id limit 2;

处理这两天数据：UPDATE student set flag = 0 where id in (6,8);

查询第二页数据时将第一页最后一条数据的id （值为8）传入查询条件中：

SELECT * FROM student where id > 8 and flag = 0 order by id limit 2;

这样就不会再漏掉数据。

再补充说明一下：如果在定时任务执行过程中一直有数据写入，可能导致定时任务执行时间过长，为了避免这种情况，可以在定时任务开始处理数据时先查询一下当前数据库中的主键 id 的最大值，本次任务只处理到这条记录，执行过程中新增的数据等到第 2 天定时任务再处理。应用程序执行的 SQL 变为：

SELECT id FROM student order by id desc limit 1;// 假设上一条命令查询的最大 id 是 15
SELECT * FROM student where id > 0 and id < 15 and flag = 0 limit 2;// 假设上一条命令中最后 1 条记录的 id 是 8
SELECT * FROM student where id > 8 and id < 15 and flag = 0 limit 2;

对应的应用程序代码按照 SQL 执行的逻辑写就可以。