MySQL深分页

深分页是一个老生常谈，出现在数据长久累积，查询分页时的慢查询问题；

本篇为记录当数据量大时，对于分页业务的几种解决方式。

背景

大数据列表中，在没有引入搜索引擎前，存在10000+页码的数据。

当用户选择大页面数据时，由于MySQL分页的特性，即搜索前10000页偏移量，丢弃前10000页不相关数据。导致MySQL从索引树上搜寻、回表、再搜寻，需要N量级的开销；即需要丢弃的数据越多，搜寻下标耗时越久。

解决方案

根据资料和相关了解简单有以下三种说法

分页游标

根据拿到上一页最后一条数据的主键ID，然后在SQL语句中将该主键作为条件，只查询大于该ID的数据，随后在进行条件分页查询

SELECT FROM test WHERE create_time>='2023-07-18' limit 10;

//下一页
SELECT from test WHERE create_time>='2023-07-18' AND id > 10 LIMIT 10;

优点：在可以拿到上一页最后一条数据的场景中完全可用，比如APP的下一页下一页；或者根据业务条件查询拿到上一条数据。

缺点：只适用与自增ID，同时局限性很大：必须拿到上一条数据，在APP上一页一页操作是不可能翻到10000页数据的，而WEB上该方法又很难适用

子查询SQL

当ID同样为自增主键时：

可通过先将条件中分页数据的临界值查询出来，在进行ID的过滤

SELECT FROM test WHERE id > (SELECT FROM test WHERE create_time >='2023-7-18' LIMIT 1) LIMIT 10000,10

当ID为不规则主键时：

可通过子查询分页，只查询ID，虽然没有根本性解决问题，但是可以解决索引回表的问题。

SELECT  FROM test WHERE id IN ( SELECT id FROM ( SELECT id FROM test WHERE create_time>='2023-07-18' LIMIT 100000,10 ) as t);

Inner Join关联查询

和子查询相似，通过检索主键值解决索引回表的问题。

SELECT FROM test INNER JOIN ( SELECT id FROM test WHERE create_time>='2023-07-18'     LIMIT 100000,10) AS t ON test.id=t.id;

题外话

深分页问题再原则上来说是没有一个根本性的解决方案的，即使由于数据体量问题采用了ES/Lucene等搜索引擎；

由于数据搜索的局限性，都存在 过滤搜寻>找到范围值>丢弃前n页数据问题

虽然搜索引擎使用的是倒表结构，可以有效的根据条件找到目录值，不过依然无法精准的去定位页码数量。

不过我们可以通过业务级的手段去优化查询速度。

业务级优化

可以简单实现的是，根据数据库表中数据的特性，新加一个分组字段。

比如年份/业务标识/组织ID等等...

在查询上进行强制条件分组，这样用户所见的页码最大数就是我们可以进行控制的数量。

第二就是可以根据数据量进行分表，将一个页面展示的数据根据分表去划分新旧区域。

最后就是 建议产品谨慎考虑深分页问题，最好砍掉 ：）