MYSQL如何正确使用limit offset

分页查询

大家应该都知道，如果查询数据库的数据比较多，我们通常会采用分页来处理。

假设有以下表结构：

CREATE TABLE `table_name` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `biz` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '业务线',
   `operate_status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '操作状态：0，1，2',
   `ctime` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'DB级别创建时间',
   `mtime` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'DB级别更新时间',
   PRIMARY KEY (`id`),
   KEY `idx_mtime` (`mtime`),
   KEY `idx_biz_status_result_time` (`biz`,`operate_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

你应该很清楚下面这句sql是如何来获取分页数据的

select id,biz from table_name where biz = xx and operate_status in(0,1) limit 100 offset 0

offset 的值： 通过传入的page 和pageSize 可计算得到

offset  = （page - 1） * pageSize   // page从1开始

如果你不是按照上面的形式来处理分页的，欢迎给我留言，让我也学习一下你的宝贵经验。

问题来了

你觉得上面的分页sql处理有没有什么问题呢？如果有问题？那么这个问题是如何发生的呢？

给你一点思考的时间

 OK， 我们一起来分析一下

这个sql来处理分页数据，是会存在问题的，导致的结果就是分页查询的数据不准确，那么这个问题是如何发生的呢？

根本原因就是： 数据库索引的选择

我们发现，当我们执行查询时,  发现满足条件的数据有120条左右， 执行的sql如下：

select * from table_name where biz = 100002 and operate_status in (0,1)

通过explain 解析该sql，发现extra字段只出现了： Using where（全表扫描）

开始分页操作， 取第一页的50条数据，sql如下：

select * from table_name where biz = 100002 and operate_status in (0,1) limit 50 offset 0

奇怪的问题马上就开始了,

第一页返回的数据，不是满足条件的数据中主键最小的。

通过explain 解析该sql，发现extra字段只出现了：Using index condition （通过索引初步过滤，回表过滤其他条件）， 命中了索引： idx_biz_status_result_time

接着查询第二页

select * from table_name where biz = 100002 and operate_status in (0,1) limit 50 offset 50

返回的数据，是接着第一页返回数据中之后满足查询条件的数据，看着没有问题

通过explain 解析该sql，发现extra字段只出现了：Using index condition （通过索引初步过滤，回表过滤其他条件）， 命中了索引： idx_biz_status_result_time

接着查询第三页

select * from table_name where biz = 100002 and operate_status in (0,1) limit 50 offset 100

问题出现了

查询第三页的时候，返回的数据中出现了在第二页已经返回了的数据结果，同时有些数据一直没有返回，比如前面提到的，查询第一页的时候没有返回满足条件的主键id最小的那几条数据。

通过explain 解析该sql，发现extra字段只出现了： Using where（全表扫描），注意和查询第一页，第二页时extra字段返回的结果对比

最终结果： 分页数据返回不准确，分页查询存在问题。

通过以上分析，你大概了解了出现问题的根本原因，那你知道有哪些方法能解决上面出现的问题么？

欢迎留言👏

END