MySQL 的 limit 深分页问题

MySQL 中一般使用 limit 子句实现分页功能，语法为limit offset, size

但是当 offset 值过大时，查询就会变得很慢，如limit 1000000, 10。
这是是因为 MySQL 是通过先查询出来 offset+size 条记录，然后再将前面 offset 丢弃，只取剩余部分来实现的。
那么前面的查询其实都是多余的操作，这就导致了性能的浪费。要解决深分页问题，主要考虑减少查询的浪费

一般有两种方案：

• 偏移值
• 覆盖索引 + 子查询

偏移值

偏移值是指采用 where 语句直接定位 offset 的值，然后只需要使用 limit size 子句获取数据
具体由两种做法：
1、是通过前端传递上次查询的主键最大值，
2、是根据前端传递的页码和页大小计算出来偏移值

缺点：
第一种：
要求偏移值字段必须是递增，且只能一页一页的查询，不能跨页，因为跨页时记录的上次最大值就不能用了（适用于 App 端）
第二种：
要求偏移值字段必须是递增且连续的，如果有断层计算就会错误

像这样：

select * from t_bigdata where id > 2000000 order by id asc limit 10;

覆盖索引 + 子查询

给查询条件创建一个辅助索引，通过辅助索引根据分页条件查询主键 id，辅助索引中包含了需要的数据可以直接使用覆盖索引，只深分页索引表，丢弃的只有索引字段，数据量小，性能不会很差。
而且具有一定的通用性，可以进行随机分页，数据字段也不要求连续

缺点：
需要创建辅助索引

sql 示例：

-- 由于版本问题子查询不能用到 limit 这里套了一层表
select 
* 
from t_bigdata 
where 
id in 
(
select t.id id 
from (select id from t_bigdata order by create_time limit 2000000, 10) t
)
-- 耗时 3.97s

问题：这种写法会导致外层全表扫，比直接查主表还慢

解决方法，使用 join 关联

select 
* 
from t_bigdata tb 
join 
(select id from t_bigdata order by create_time limit 2000000, 10) t
on tb.id = t.id
-- 耗时：0.357s