使用id限定优化mysql分页查询limit偏移量大问题

在工作中可能偶尔会遇到，当使用limit实现分页查询时，当limit的偏移量越大时，sql语句的耗时也越大。

如图：

偏移量为0时，sql语句耗时在35毫秒。

顺便说下偏移量与页码、页大小的关系：

偏移量 = (页码  - 1)  x  页的大小

比如页的大小是每页100行记录， 那么:

第一页的偏移量就是  (1 - 1) x 100 = 0

第二页的偏移量就是  (2 - 1) x 100 = 100

以此类推

当加大偏移量时：

当偏移量加到100000时，耗时也增大到228毫秒。

为什么偏移量会对性能有这么大影响呢？

上图是mysql的系统结构图，客户端程序发送sql语句查询请求给服务层，服务层会解析、优化sql语句，之后交给存储引擎，也就是说，存储引擎是真正完成查询的(增加、删除、修改也是由存储引擎负责的)。

select * from orderinfo limit 100000, 100

 

当存储引擎查询数据库文件后返回的不是一页的数据(100行)， 而是从第1行 到 第 (100000 + 100)行的数据一起返回给服务层。  服务层收到数据后会抛弃前面的100000行，只留下最后的100行返回给客户端。

数据库表中行数据、索引都是以文件的形式存储到磁盘(硬盘)上的，而硬盘的速度相对来说要慢很多，存储引擎运行sql语句时，需要访问硬盘查询文件，然后返回数据给服务层。当返回的数据越多时，访问磁盘的次数就越多，就会越耗时。这就是为什么偏移量越大、返回的数据越多，越耗时的原因。

所以说，如果想优化上面的sql时，必须要减少返回的数据。

当表的主键是有序的，或者是自增的，可以使用id限定查询，查询过程是：

当已经查询了某页的数据后，记录下该页最后一行记录的主键id值(本例中是dbid为主键)，查询下一页时就可以使用如下sql：

select * from orderinfo where 主键列名 > 当前页最后一行的主键值 limit 0, 100

比如：

 

当前页最后一行的主键值是132587，查询下一页就可以使用：

select * from orderinfo where dbid > 132587 limit 0, 100

 

 

 

那么第一页怎么查询呢？

可以选择一个比所有主键值都小的值，比如0或者负数 ：

select * from orderinfo where dbid > 0 limit 0, 100

如果不明白也可以观看视频教程：

https://www.bilibili.com/video/BV12G4y1x73m