一站式解决mysql深分页问题_Mysql

引言

在后端开发或者面试中，常常会遇到深分页的问题。在处理电商平台海量商品数据、社交媒体时间线等场景时，深分页问题会导致用户体验急剧下降，甚至造成服务器崩溃。接下来让我们解析一下深分页问题。

什么是深分页问题😪

例如前端要求返回指定第1000000行到第1000000 + 10行数据，我们常常会写出这样的sql语句。

select * from user order by id limit 1000000, 10;

这种limit的offset，size中的offset如果很小则没有问题，一旦变得很大，那么查询的时间就会很长，接口响应时间也会变得很慢，严重影响用户使用体验。

为什么offset一旦变得很大，sql执行速度就会变得很慢呢？这是和mysql的执行流程有关的，以上面的sql语句为例子，当你执行这条sql语句时，mysql的server层会调用存储引擎获取到从0到1000000 + 10条数据，然后将前1000000数据抛弃，留下剩下的10条数据包装成结果集返回。获取这些多余数据耗费的时间非常大，所以sql查询速度相应的也会变慢了，这就是深分页带来的问题。

解决深分页问题🥱

1.优化sql语句（必做）

select * from user order by id limit 1000000, 10;

还是以这条sql语句为例子，去除深分页问题不谈，这样sql语句本身执行效率就不够高，至少要进行以下的优化：

避免使用select *语句，前端需要什么内容就查询什么字段，使用select *语句执行效率过慢。
确保查询的字段带有合适的索引，结合实际情况，为查询字段建立合适的索引，例如唯一索引，联合索引等
使用合适的where条件进行过滤

2.子查询优化

在第一个优化的基础上，我们可以选用子查询来进行优化，见以下sql语句为例：

select (字段) from user where id >= (select id from user order by id limit 1000000, 1) order by id limit 10

通过子查询查询单id字段，查询到前1000000 + 1条数据，然后丢弃前1000000数据，留下最后一条数据。因为这里只查询单个id字段数据，时间效率可以提升。如果有合适的where条件进行过滤，则可以起到覆盖索引的效果，可以进一步提高时间查询效率。

查到了一条id之后，存储引擎根据条件，就再走一遍主键索引（在为id加上了主键索引的前提下），然后向后取后十条字段数据就完成了该sql语句执行。这种优化可以提高时间效率1.5倍左右。

3.游标分页法优化

实现步骤：
1. 首次查询时，获取第一页数据，并返回一个游标。
2. 后续查询使用该游标来获取下一页数据。
优点：避免了偏移量带来的性能问题。
缺点：依赖稳定的排序，不适合随机访问页码。

select id,name from user where sex = 'male' and id > 10 limit 10;

在上面的sql语句中id > 10,这个10就是上一页的查询内容id，可以借助它再查询下一页，这样就可以避免一时间要查询大量数据而导致的时间效率低下的弊端，查询速度非常稳定，但适用场景有限，可以用作瀑布流，例如抖音，头条新闻等。

闲谈😊

其实深分页问题只能缓解，没有根治之法。如果你去浏览器进行查询，最多给你展示的也就几百页，不会到万的这个级别，用户一般也不会去翻到最后一页去查看感兴趣的内容，所以如果有人给你提出了这个需求，你需要和他反馈一下这个需求是否合理，可不可以改成其他相同效果的需求，例如不用直接跳转的，只有上一页，下一页这样的效果，这样用户体验也不错，服务器的压力也会变小。

在实际应用中，需要根据具体场景选择合适的解决方案，并与前端和产品团队充分沟通，找到最佳实践。