Mysql深度分页优化思路和方案

前言

在我们业务表的数据量很大的时候，前端有一个分页查询的需求，如果分页很深的时候，如何优化查询速度

例如：我们系统有一张业务数据表，我们需要根据前端传过来的分页下标pageIndex和每页查询的数量pageSize两个参数以及其它的条件来查询这张业务表的里面的对应的数据并且返回给前端。

---

一、普通分页的优化方法

一般分页不是很深的情况下，我们一般可以通过以下方法解决大部分的分页问题

1. 通过增加主键排序，例如：order by id
2. 如果需要根据时间排序，就给常用的字段增加索引，包括时间字段。例如：order by create_time

以上两种手段其实可以解决大部分的分页问题了。但是如果后面的页数很深了，比如从100w条开始取20条，我们就会发现再执行sql语句就会非常慢，这是因为mysql的优化器在发现sql查询的行数超过一定比例的时候，就会自动转换成全表扫描，可以自己模拟数据测试一下。

二、什么是Mysql的深度分页？

查询偏移量过大的分页的场景我们称为深度分页，例如以下sql语句就是一个典型的深度分页场景

SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20

三、深度分页的优化方案

1、强制索引 force index（不推荐）

一开始想着使用force index强制走索引，但是我的leader跟我说过，不建议添加强制索引来进行sql优化，主要有以下几种缺点：

1. 影响选择性最佳的索引：强制使用索引可能会影响数据库引擎选择性最佳的索引，导致查询性能下降
2. 增加更新操作的时间：强制使用索引后，数据库更新操作的时间会增加，因为索引文件需要被更新
3. 降低查询的灵活性：如果强制使用索引过于固定，会降低查询的灵活性，不方便后期维护。

2、ID范围查询

如果那种不需要页码的场景下，比如滑动加载（消息列表这种），还有那种只有上下页按钮点击的网站分页，我们可以通过where id > #{上次查询的最后一条记录的id} 进行优化

# 查询指定 ID 范围的数据
SELECT * FROM t_xxx WHERE id > 1000000 AND id <= 1000020 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询
SELECT * FROM t_xxx WHERE id > 1000000 LIMIT 20

3、子查询+INNER JOIN

可以先根据时间字段（create_time）或者id排序查询到id，比如：

SELECT id FROM t_xxx ORDER BY create_time DESC LIMIT 1000000,20

这个子查询先查出来，作为临时表，然后再让主表join这个临时表去联表查询需要的t_xxx对应的信息字段，这样也可以达到一个很好的效果，最终sql语句就是这样：

SELECT * FROM t_xxx INNER JOIN (SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,20) AS t_temp ON t_xxx.id = t_temp.id

4、子查询+ID过滤

也可以通过子查询+ID过滤优化的方式进行优化，例如：

SELECT * FROM t_xxx WHERE name = 'xxx' AND id >(SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,1) ORDER BY id LIMIT 20

总结

优化这种深度分页的场景，一般在企业里面有两个方向，第一就是产品方向进行优化，第二就是技术方向优化

产品方向：

1. 参考谷歌/百度搜索分页，每次只能跳转到当前页前后10页，也就是最多可以跳10页，要想达到深分页情况需要耐心。
2. 前端不支持跳页，只能通过上下页这种

技术方向：

1. 即使没有排序条件，也最好带上主键或者时间排序
2. 对排序字段添加索引
3. 通过子查询+INNER JOIN的方式
4. 通过子查询+ID过滤的方式（这其实是3的变种，要求id是自增的）
5. 每次查询记录上一次查询的id，然后基于这个id查询下一次（类似4）
6. 调整mysql相关配置，比如sort_buffer_size大小（注意并不是越大越好，根据情况设置）
7. 如果是基于内容搜索的话，可以使用Elasticsearch这种全文搜索引擎来进行优化