一、分页原理

在探讨 Mysql 分页原理之前,我们先看一下几条关于分页的 Sql

从上面的 Sql 可以看出,随着分页的起始位置增大,分页的耗时也在不断增大,当进行最后一页分页的时候,耗时达到最大

那么为什么每一页耗时不一样呢,越往后翻页需要的时间越久呢?

针对于 limit m,n 这种分页语句,Mysql 会先查找出前 m + n 条记录,然后将前 m 条记录丢弃,只保留 n 条记录

例如 limit 1000,10 ,Mysql 会先找出前 1010 条记录,而结果仅仅需要返回第 1001 到 1010 的记录行,那么前 1000 条记录都会被抛弃,整个分页的代价还是很大的

二、如何优化分页

1、尽可能扫描少的数据

先看两条 Sql

// 优化前
select * from t2 limit 2999990,10;
// 优化后
select t2.* from t2 inner join (select id from t2 limit 2999990,10)temp on t2.id = temp.id;

查看两条 Sql 的耗时情况,一个耗时 0.94 s,优化后耗时 0.54 s,性能还是有提升的

查看执行计划分析

为什么第二条 Sql 会比第一条 Sql 耗时短呢

第一条 Sql 全表扫描聚集索引叶子节点的所有记录行

第二条 Sql 先全表扫描聚集索引的索引,获取到包含10 个 id 的衍生表 temp,然后使用 temp 表作为驱动表去关联 t2 表获取完整的数据

2、传上一次分页的最大 id 标识

假设上一页返回的最大 id 为 2999990

这种分页就是把 limit m,n 转换成 limit m,但是这种分页存在局限性,例如在分页的过程中在前面删除了两条数据,得到的结果就不一致了