MySQL 能用 OFFSET 分页查询吗

作者：fm

2023-07-24
广东
本文字数：892 字
阅读完需：约 3 分钟

最近业务线的后端服务出现了分页查询耗时过长的问题。

查找问题

通过 explain 分析 sql 语句，

explain select * from my_table where sys_time between '2023-06-21 23:59:59' and '2023-07-21 23:59:59' order by id limit 100 offset 80000;

explain select * from dja  where sys_utime  between  '2023-06-21 23:59:59' and  '2023-07-21 23:59:59' order by id limit 100 offset 80000;
id|select_type|table|type |possible_keys|key|key_len|ref|rows |Extra                            |--+-----------+-----+-----+-------------+---+-------+---+-----+---------------------------------+ 1|SIMPLE     |dja  |index|idx_sys_time |id |8      |   |80100|Using where with pushed condition|

复制代码

type：index --sql 使用了索引但是没用通过索引进行过滤。

Extra：Using where with pushed condition --NDB Cluster 正在使用下推优化来提高非索引列和常量之间直接比较的效率。

rows：80100 -- 找到所需要的 100 行记录所需要读取的行数需要读取 80100 行数据。

分析原因

通过调整 sql 语句优化索引，覆盖索引，调整 offset 参数等方法，explain 分析 sql 语句，观察执行耗时。发现优化索引、使用覆盖索引等调整，收效甚微。

但改变 offset 参数，效率可以提高数倍。耗时从 1s 以上，下降到 100ms 以内。

存储引擎 innoDB 的索引基于 B+树实现，执行语句时会根据 where 条件读取 offset+limit 行记录，然后丢弃 offset 行记录返回。

解决办法

不使用 offset 进行分页查询

修改后的 sql：

select * from  dja   where sys_time  between  '2023-06-21 23:59:59' and  '2023-07-21 23:59:59' and id > 1687363199840473880 order by id limit 100 ;id|select_type|table|type |possible_keys   |key|key_len|ref|rows|Extra                            |--+-----------+-----+-----+----------------+---+-------+---+----+---------------------------------+ 1|SIMPLE     |dja  |index|id,idx_sys_time |id |8      |   | 100|Using where with pushed condition|

复制代码

限制

使用主键 id 进行分页时，如果主键 id 不连续，无法跳跃查询，每次只能查询下一页。

发布于: 刚刚阅读数: 3

fm

关注

还未添加个人签名 2019-12-16 加入

还未添加个人简介

发布

暂无评论

创作场景

MySQL 能用 OFFSET 分页查询吗

fm

评论