背景
在日常开发中，不可避免的要用到分批查询或分页查询，其中的场景有很多，有的是WEB页面的分页查询效果，或移动端向下滑动的分页查询，有的则是因为目标数据量巨大，不得已而分批查询。无论是出于性能考虑，还是大报文考虑，抑或页面的效果，分批或分页查询都是研发的日常。
本文尝试，对日常项目用到的分批分页查询做一下方案的回顾和浅析。
查询场景及方案
一、普通分批分页查询场景
方案1 普通LIMIT OFFSET分页查询方式
通过数据库直接LIMIT OFFSET 的方式是最简单，也是最常用的分页查询方式。
SELECTid,warehouse_no,location_no,sku,sku_level,lot_no,pack_code,owner_no,extend_content
FROMst_stock
WHEREdeleted = 0AND warehouse_no = '6_666'
ORDER BYid ASC
LIMIT 100,10该方法直接简单，开发和运维简单，可读性高，但当offset值（偏移量）非常大时，弊端也比较明显：深分页性能问题比较严重，例如 LIMIT 1000000, 10 。
当执行LIMIT 1000000, 10时，SQL的处理流程是：
扫描并读取前1,000,000条记录
丢弃这1,000,000条记录
返回接下来的10条记录
这意味着即使只需要10条数据，数据库也必须访问和处理大量的"无用"数据。
简言之，深分页，IO开销大：需要读取大量无用数据页；内存消耗高：大量数据加载到内存后被丢弃；CPU消耗高：排序、过滤操作消耗大量CPU资源。
方案2 基于子查询或二次查询的分页查询
SELECTs.id,warehouse_no,location_no,sku,sku_level,lot_no,pack_code,owner_no,extend_content
FROMst_stock s
JOIN(SELECTidFROMst_stockWHEREdeleted = 0AND warehouse_no = '6_666'ORDER BYid ASC LIMIT 100,10)s2
ONs.id = s2.id或
SELECTs.id,s.warehouse_no,s.location_no,s.sku,s.sku_level,s.lot_no,s.pack_code,s.owner_no,s.extend_content
FROM st_stock s
WHERE EXISTS (SELECT 1FROM (SELECT idFROM st_stockWHERE deleted = 0AND warehouse_no = '6_666'ORDER BY id ASCLIMIT 100,10) AS s2WHERE s.id = s2.id
);
除了直接在SQL中进行分页处理，还可以通过二次查询的方式来实现。
第一步，先分页查询id列表；
SELECT id
FROM st_stock
WHERE deleted = 0AND warehouse_no = '6_666'
ORDER BY id ASC
LIMIT 100,10;id字段有主键索引，避免回表。
第二步，以第一步的id列表作为in条件，查询库存信息。
SELECTid,warehouse_no,location_no,sku,sku_level,lot_no,pack_code,owner_no,extend_content
FROM st_stock
WHERE id IN (id1, id2, id3, ...);
注意：下面的SQL方式是错误的，SQL语法不支持：
SELECTid,warehouse_no,location_no,sku,sku_level,lot_no,pack_code,owner_no,extend_content
FROMst_stock swhere id in (SELECTidFROMst_stockWHEREdeleted = 0AND warehouse_no = '6_666'ORDER BYid ASC LIMIT 100,10)SQL 错误 [1235] [42000]: This version of SQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
解决方案就是使用上面的方式实现。
方案3 游标分页，滚动式查询
SELECTid,warehouse_no,location_no,sku,sku_level,lot_no,pack_code,owner_no,extend_content
FROMst_stock
WHEREdeleted = 0AND warehouse_no = '6_666'AND id > 100
ORDER BYid ASC
LIMIT 10与方案一相比，最大的区别是增加了id条件，本次id的条件是上一次查询结果集中的最大id，通过id滚动式查询，缩小检索范围。
上图就是一个游标分页查询的案例。
二、动态数据分批分页导出查询场景
对于动态变化的数据，想要分批分页导出，而且想要保证数据的准确性，该如何处理呢？
方案1 对目标数据加锁
将导出条件对应的目标数据锁定，导出结束后再解锁这批数据。导出时间被锁定的数据行，不能update、delete，可以select。
| SKU | …… | locked | |
|---|---|---|---|
| 2019609892142206976 | 123 | …… | 1 |
| 2019695225349345280 | 456 | …… | 1 |
| 2019326832070885376 | 789 | …… | 1 |
| 2027414057350348800 | 110 | …… | 0 |
| 2027414069316685824 | 118 | …… | 0 |
优势
劣势
方案2 生成导出数据快照
将导出条件对应的目标数据生成导出库存快照数据，导出执行是将本次版本的快照数据导出，导出数据快照过时可以清理。
实时数据
| id | SKU | …… |
|---|---|---|
| 2019609892142206976 | 123 | …… |
| 2019695225349345280 | 456 | …… |
| 2019326832070885376 | 789 | …… |
| 2027414057350348800 | 110 | …… |
| 2027414069316685824 | 118 | …… |
快照数据
| id | SKU | …… |
|---|---|---|
| 2019609892142206976 | 123 | …… |
| 2019695225349345280 | 456 | …… |
| 2019326832070885376 | 789 | …… |
优势
劣势
三、内存分页查询场景
在日常研发过程中遇到的分页查询，大部分都可以借助SQL数据库、ES等存储中间件自身的分页功能实现，但个别场景下并不符合，比如数据并未存储在SQL数据库或ES中，而是内存计算出来的一种结果数据；或者数据库中存储的数据维度并不符合，并不能通过简单的GROUP BY等方式实现维度加工；或者数据库中存储的数据，需要通过第三方RPC远程接口实时获取特殊属性打标过滤后，才可以作为目标数据使用。
在这些场景下，我们会用到内存分页的方式处理。
内存分页方案
上面的示例，是一个简单的内存分页处理方式。
总结
本文回顾了日常研发过程中，经常遇到的普通分批分页查询场景、动态数据分批分页导出查询场景、内存分页查询等场景，探讨了对应的解决方案。方案并非固定一成不变的，也有各自的利弊和局限性，在合适场景下，选择合适的方案即可。