在数据量很大的情况下，怎么实现深度分页？
– 分库分表建索引
– 深度分页查询优化
– 深度随机跳页应该禁止

分页查询

一般的分页查询使用简单的 limit 子句就可以实现（指定 SELECT 语句返回的记录数）。

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

需注意以下几点：

• 第一个参数指定第一个返回记录行的偏移量，初始偏移量是 0
• 第二个参数指定返回记录行的最大数目
• 如果只给定一个参数：表示返回最大的记录行数
• 第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行

大表分页查询的存在问题

MySQL 分页查询逻辑：查询出 offset + rows 条记录，丢弃前 offset 行记录，保留 rows 条记录。因此，offset 越大，查询速度越慢。

SELECT * FROM task_instance limit 1000, 10              -- 0.0062 sec
SELECT * FROM task_instance limit 10000, 10             -- 0.032 sec 
SELECT * FROM task_instance limit 100000, 10            -- 0.062 sec
SELECT * FROM task_instance limit 1000000, 10           -- 0.617 sec
SELECT * FROM task_instance limit 10000000, 10      -- 5.772 sec

创建一个索引 (task_status, create_at)

SELECT * FROM task_instance where task_status = 5 order by create_at limit 1000, 10         -- 0.0079 sec
SELECT * FROM task_instance where task_status = 5 order by create_at limit 10000, 10        -- 0.072 sec
SELECT * FROM task_instance where task_status = 5 order by create_at limit 100000, 10       -- 0.597 sec
SELECT * FROM task_instance where task_status = 5 order by create_at limit 1000000, 10  -- 7.521 sec

结论：MySql 的索引是 B+ 树结构，不能随机定位到第N条记录，要找到N的位置，N越大，成本越大。

利用 B+ 树有序的特性优化

虽然找到第 N 条记录的效率比较低，但找到某一条数据在索引中的位置，其效率是很高的。然后利用 B+ 树结构数据是有序的特点，能很快的找到分页数据。

假设主键 id 是递增的情况。这种方式先定位 offset 位置的主键 id 然后查询。

-- 只能顺序翻页的情况，记录上一页的最大id，作为查询条件，查询比较稳定
select * from task_instance where id >= 100000 limit 10;     -- 0.0027 sec
select * from task_instance where id >= 1000000 limit 10;     -- 0.0028 sec
select * from task_instance where id >= 10000000 limit 10;    -- 0.0077 sec

-- 随机翻页的情况，定位到上一页的最大id，作为查询条件
SELECT * FROM task_instance where id > (select id from task_instance limit 100000,1) limit 10    -- 0.023 sec
SELECT * FROM task_instance where id > (select id from task_instance limit 1000000,1) limit 10   -- 0.261 sec
SELECT * FROM task_instance where id > (select id from task_instance limit 10000000,1) limit 10  -- 1.929 sec

查询非主键索引的情况

按照其他索引排序(可能存在重复记录 解决可以 >=其他索引 and unique index>lastEndId) 思路同上

-- 只能顺序翻页的情况，记录上一页的最大id，作为查询条件，查询比较稳定
SELECT * FROM task_instance where task_status = 5
and id >= 2286305  -- limit 1000,1
order by create_at limit 10             -- 0.0068 sec (0.0079 sec)

SELECT * FROM task_instance where task_status = 5
and id >= 1255586  -- limit 10000,1
order by create_at limit 10             -- 0.011 sec  (0.072 sec)

SELECT * FROM task_instance where task_status = 5
and id >= 1345635  -- limit 100000,1
order by create_at limit 10             -- 0.027 sec  (0.597 sec)

SELECT * FROM task_instance where task_status = 5
and id >= 2286315  -- limit 1000000,1
order by create_at limit 10             -- 0.267 sec (7.521 sec)

-- 随机翻页的情况，定位到上一页的最大id，作为查询条件
-- 效果不是很明显，可能是因为其他索引生效的原因
SELECT * FROM task_instance where task_status = 5
and id >= (select id from task_instance where task_status = 5 limit 1000,1) 
order by create_at limit 10             -- 0.0098 sec (0.0079 sec)

SELECT * FROM task_instance where task_status = 5
and id >= (select id from task_instance where task_status = 5 limit 10000,1) 
order by create_at limit 10             -- 0.039 sec  (0.072 sec)

SELECT * FROM task_instance where task_status = 5
and id >= (select id from task_instance where task_status = 5 limit 100000,1) 
order by create_at limit 10             -- 0.496 sec  (0.597 sec)

SELECT * FROM task_instance where task_status = 5
and id >= (select id from task_instance where task_status = 5 limit 1000000,1) 
order by create_at limit 10             -- 8.509 sec (7.521 sec)

基于行比较

https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html

MySQL 支持以下语法，可以很好的利用索引

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;   -- 等价写法

SELECT * FROM t1 WHERE c1=1 AND (c2,c3) > (1,1);
SELECT * FROM t1 WHERE c1=1 AND c2 > 1 OR ((c2 = 1) AND (c3 > 1))

查询 SQL 可以改为

SELECT * FROM task_instance where task_status = 5
and (id, create_at) >= (select id,create_at from task_instance where task_status = 5 limit 1000000,1)
order by create_at limit 10;

其他方案

如果是查询后几页的数据，可以将查询倒序（DESC），根据总数，算出倒序查询对应的 offset（total – offset -limit），然后使用 limit offset, rows 查询可以提高查询效率。

产品上的优化

• 尽可能的增加默认的筛选条件，如：时间范围，减少数据量的展示。
• 修改跳页的展现方式，改为滚动显示，或小范围跳页。

总结

• 对排序字段，筛选条件务必设置好索引（必须）
• 利用小范围页码的已知数据，或者滚动加载的已知数据，减少偏移量（核心）

参考：
https://blog.csdn.net/weixin_40834464/article/details/107351181
https://www.cnblogs.com/trytocatch/p/mysql-page-query.html
https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html