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