关于分库分表后的分页场景问题

来源：9-10 分表 + 主从数据库实践

mekio

2023-02-22

廖师兄，你好。请问一下，课程中基于sharedingsphere做分库分表后，某些业务场景下做分页查询的效率会不会有影响。其实在实际的项目工作中。当分页效率有瓶颈的时候。是否会考虑用游标查询或者流式查询的方式。这方面能帮我答疑一下。基于mabatisplus的分页查询游标查询及流式查询的使用方式及使用场景的区别吗？最好给个demo学习一下。谢谢！

写回答

1回答

好帮手慕小蓝

2025-02-10

在使用ShardingSphere进行分库分表后，分页查询的效率确实可能会受到影响，尤其是在数据量非常大的情况下。这是因为分库分表后，分页查询需要在多个数据库或表中进行数据聚合和排序，可能会导致性能瓶颈。为了解决这个问题，游标查询和流式查询是两种常见的优化方式。

1. 分页查询的效率问题

问题：

在分库分表后，分页查询（如LIMIT offset, size）需要在每个分片上进行查询，然后将结果聚合到内存中进行排序和分页。
当offset很大时，查询效率会显著下降，因为需要扫描大量无效数据。

解决方案：

使用游标查询或流式查询来避免一次性加载大量数据。

2. 游标查询

原理：

游标查询通过数据库的游标机制逐条获取数据，而不是一次性加载所有数据。
适合处理大数据量的查询，避免内存溢出。

使用场景：

需要逐条处理数据的场景，如数据导出、批量处理等。

缺点：

游标查询会占用数据库连接，直到游标关闭。
不适合需要随机访问数据的场景。

3. 流式查询

原理：

流式查询通过JDBC的ResultSet流式获取数据，逐条处理，避免一次性加载所有数据。
与游标查询类似，但流式查询通常由框架或驱动实现，不需要显式使用游标。

使用场景：

需要逐条处理大数据量的场景。

优点：

不占用数据库连接，适合长时间的数据处理。

4. 分页查询、游标查询和流式查询的区别

特性	分页查询	游标查询	流式查询
数据加载方式	一次性加载所有数据	逐条加载数据	逐条加载数据
内存占用	高，可能内存溢出	低，适合大数据量	低，适合大数据量
数据库连接	短连接	长连接，直到游标关闭	短连接
适用场景	小数据量分页	大数据量逐条处理	大数据量逐条处理
随机访问	支持	不支持	不支持

5. 分页查询的优化建议

避免深分页：
使用WHERE条件限制查询范围，而不是直接使用LIMIT offset, size。
例如，记录上一页的最大ID，下一页查询时使用WHERE id > last_max_id LIMIT size。
使用游标或流式查询：
对于大数据量的导出或处理，优先使用游标或流式查询。
分库分表优化：
在ShardingSphere中，尽量使用分片键进行查询，避免全表扫描。
总结
分页查询：适合小数据量的分页场景，但在分库分表后可能存在性能问题。
游标查询：适合大数据量的逐条处理场景，但会占用数据库连接。
流式查询：适合大数据量的逐条处理场景，不占用数据库连接。
在实际项目中，可以根据业务需求选择合适的查询方式。对于大数据量的导出或处理，优先使用游标查询或流式查询；对于普通的分页需求，可以优化分页逻辑或使用ShardingSphere的分片键来提升性能。