关于分页只能写 sql 的痛苦

[garyxi24]

由于现在公司项目都是在数据库分页，只能写 sql 实现而且只能是一条 sql 。本人是个 sql 菜鸡，简单筛选还好说，复杂逻辑筛选用 sql 写就很痛苦，好不容易怼出来 sql 像💩一样，又臭又长，同样的逻辑如果内存分页比如用 java 去实现就可以很清晰、很舒服。
所以我是想请教一下有什么更好的分页方式，摆脱写 sql 的噩梦，上 es Hadoop spark flink 这些大数据生态组件？

可能我的表达有误让大家理解为复杂的是分页，当然不是，我不至于说连 limit 都写不出来。复杂在于各种奇葩的过滤条件，我需要写各种 join 和子查询的 where 子句，而这起源于分页，由于是分页，所以我必须要写 sql 而且只能是一条 sql 。这条 sql 在某些情况下会无比的冗长和复杂，后续的迭代去修改和优化也是痛苦的事情。
另外我当然清楚不能在内存分页，尤其在大数据量的情况下，至于说 java 也不是说鼓吹 java 贬低 sql ，我并没有这样的意思。我的诉求是想要代码清晰的集合运算，比如我可以把目标表的所有数据作为主集合和其他表数据组成的集合进行交并补拿到我想要的数据再去分页，在 java 里，我可以把各个集合用变量暂时存储，通过变量进行操作，操作逻辑封装成方法，这样思路就很清晰，后续迭代也很方便修改和优化。

另外想跟大家探讨一个 sql 问题，比如我有一张 user 用户表、一张 role 权限表、还有一张 user_role 用户权限关联表，我现在想筛出至少有一种权限不存在的 user ，有什么好的 sql 实现？

还有想听听大家对于微服务架构下，分页查询如何实现，比如我对 a 服务数据分页，但过滤条件在 b 服务数据的情况。

看了大家的回复，感觉微服务、分库分表架构下，单纯 sql ，或者说数据库下分页已经不好解决问题了，不仅仅是筛选，连做分页的主表也有可能要从不同的服务去拿数据拼接字段，这时候感觉只能上 es 这种中间件，当然也会有更多的技术复杂度进来，比如说要做 es 和数据库同步等。

谢谢大家热情回复，帖子原意只是小小的吐槽一下，后来想慢慢去拓展思维，不只局限于 sql 数据库，更想讨论一下更复杂架构下的分页实现。

[akira]

那就换成用 java 去写咯。。。

[thinkershare]

学会写 SQL, 可以不写, 但不可以不会.
另外 SQL 只要不使用 join 和子查询, 再复杂能复杂到哪儿去.
现在大部分数据库都支持原生的分页, 写分页已经足够简单.
总不可能将数据全部查询到内存, 然后调用内存排序,然后 skip/take 吧...

[LeegoYih]

我觉得 ORM 能解决你的痛点，比如 JPA ，而不是 MyBatis 。

像 Elasticsearch 这种，分页性能更差，基本上都不用分页，通常用滚动搜索。

[garyxi24]

@thinkershare “另外 SQL 只要不使用 join 和子查询, 再复杂能复杂到哪儿去”，复杂不就在这两个地方吗，或者说在于主表和其他表的关联上，纯单表当然没那么复杂了

[chihiro2014]

JPA 包治百病

[garyxi24]

@LeegoYih jpa 也只适用简单过滤吧，复杂过滤还是要写原生 sql 吧

[LeegoYih]

@garyxi24

现在很少写 join 和子查询，因为数据库基本都是分库分表，或者是微服务物理隔离的，所以 JPA 比较合适。

如果不是这种场景，需要手写复杂 SQL ，可以尝试一下 MyBatis 分页插件，比如：MyBatis-PageHelper 、MyBatis-Plus ，但是这俩插件在一些场景下可能会有 Bug ，而且代码质量也堪忧。

也可以参考我写的分页插件： https://github.com/yihleego/mypages
使用了 ANTLR4 分析语法树，支持多种使用场景。

[sadfQED2]

我好奇，如果不在数据库分页，难道还有其他地方可以分页吗？难道你以前都是把数据加载到程序里面，然后用程序分页？

另外，SQL 分页的难点在哪？举个例子看看

[Cbdy]

用 es

[liaohongxing]

建议放弃编程 ，写个 sql 都能成痛苦 。

[bthulu]

自己参考 mybatis 分页插件写个小工具, 用于将你写的任意 sql 转换为带分页的 sql, 这样你就只管写不分页的 sql, 写完了用小工具处理下就好了

[chendy]

楼主的分页是有多复杂？
就算是手撸分页也就是在 sql 里按数据库的语法加上 skip 和 limit 的参数就完事了
还是说查询条件太多还分散在各个表，拼起来难受？

[catcn]

你把分页抽取成一个通用的方法不就可以了。

[noparking188]

找个 SQL Builder 库

[keepeye]

这个世界不由 java 主导一切

[seliote]

能理解，尤其是涉及到属性对象过滤分页这样的，没什么好办法，数据不多就内存手动分页

[james2013]

我用 java 也是在数据库分页的，多表 join 也是这样
怎么可能在内存分页呢？

[lscho]

不明白 sql 分页有什么难的，不就 limit 吗。

而且内存分页是第一天写代码吗？数据量大了咋内存分页啊。

[tairan2006]

写 SQL 是基本技能…Flink/ES 甚至最新的 MongoDB(atlas)，都回到 SQL 上了。

当然，如果只是分页的需求，用插件就可以了…

[nothingistrue]

你发在了 mysql 区，而 mysql 分页只需要简单的在最外层末尾加上 limit first,max 即可。这你都痛苦，那遇见 oracle 的分页你得自杀。

[aliveyang]

最好的分页就是最初的分页，而不是写到内存里再分

[iseki]

没有特殊原因请不要在内存里分页，常规的 curdSQL 并不难，去把 PostgreSQL 文档前几章完整看一遍，有中文的。

[l0ve1o24]

是我孤陋寡闻了吗？难道数据库分页是非主流？

[jin7]

学一下 sql 又不难

[brader]

不在 sql 分页，难道你把数据全查出来？你是怕玩不死数据库？

[iseki]

聚合算出每个用户的权限数量，小于权限总数的用户

[garyxi24]

@LeegoYih 嗯，那我想问一下微服务情况下分页怎么实现好，比如我想以 a 服务的数据为主表分页，但过滤条件在 b 服务数据里

[iseki]

如果数据逻辑复杂，那 SQL 长是没办法的…适当用 CTE ，视图等等特性降低复杂度吧

[garyxi24]

@iseki 一针见血，我想到聚合后把 role concat 成集合，和所有 role 集合比较是否相等，还是太单纯了

[garyxi24]

@chendy “还是说查询条件太多还分散在各个表，拼起来难受？” Bingo ！

[oneisall8955]

已经有 es 组件，那用 es 索引吧，数据聚合在一起，分库分表的数据想分页更蛋疼

[liuligames]

1 、jpa ，感兴趣可以去看看 jpa dsl 。
2 、不管是 jpa or mybatis 主要还是使用 jdbc 连接数据库执行 sql 语句。
3 、想筛出至少有一种权限不存在的 user 这个问题太模糊了，不过可以先暴力解法。
4 、微服务分页，主要返回数据还是 b 服务，a 服务只是填充部分数据，b 服务分页后再请求 a 服务，a 服务填充 b 服务业务返回实体

[liuligames]

抱歉，没仔细看微服务架构分页这个问题，需要先请求 b 服务拿到过滤条件，在对 a 服务数据分页

[totoro52]

我们公司也是微服务框架， 并且前端的筛选超级复杂，需要每一个字段都筛选，包括自定义字段，其中有些模块的数据是几个模块组合出来的数据列表，所以我们检索数据不采用 SQL ，采用了 ES ，同时每一次增删改都会同步 ES 数据，在事务的加持下

[vone]

有没有可能你不需要写分页？

引申出来的问题是，写分页的根本目的是做什么？

[garyxi24]

@liuligames 那比如说，从 b 服务拿来的筛选条件会是满足条件的 a 服务数据的 id ，那有可能会有非常多的数据，这样的 rpc 响应也会非常大吧

[garyxi24]

@vone 分页是肯定要的，你不可能把所有数据展示出来，问题还是在复杂筛选上

[weirdo]

不管愿意不愿意，SQL 都是基础。。

p.s. 在数据库设计合理的情况下，sql 写出来 也可以很好看，逻辑也可以很清晰
在用上主从多节点 Oracle 的业务系统中，几百行上千行的查询是十分常见的

[liuligames]

@garyxi24 mongodb 数据平铺实时同步，很舒服。

[EminemW]

楼主应该是在做报表查询之类的需求吧，每个字段都要筛选

[totoro52]

@garyxi24 我可以跟你说一下我的解决方案：
数据存两份，一份存 ES ，一份存 mysql ，并且同步存（ ES 有延迟问题）
存进数据库时是有关系的，中间表这些全部按照范式设计，而 ES 则作为宽表使用，所有的关联信息一起写进 doc 中
引发的问题：
关联表数据更新，ES 宽表字段没更新
解决方案：
引入 canal ，监听数据库改动，动态更新 ES 数据
目前系统已上线，经过改造整体查询数据比起数据库上升几十倍

[totoro52]

@garyxi24 还有很多大厂禁止多表 JOIN ，有的甚至直接禁止 JOIN ，但有些场景必须使用关联，所以其实很多大厂都会自己的检索系统

[idragonet]

用 ORM 还需要什么自己写分页

[100Percent]

@liaohongxing #10 建议你放弃编程，连百万年薪都赚不到

[LeegoYih]

@garyxi24 应该在设计的时候就避免这种情况发生，可以设计为：
- 如果 b 服务中的数据是长期不变的，可以在 a 服务中冗余一些数据
- 如果 a 和 b 服务是属于同一种业务，那么就不应该拆分为两个服务
- 如果以上都不满足，大概率是需求或者设计有问题了，可以考虑用妥协的方案，比如：伪分页（不展示总数）、滚动搜索（不支持跳页，只能一页一页翻）

PS：实际上 es 的分页性能比数据库差很多，有分页需求肯定是不适合的，通常只用滚动搜索。

[liaohongxing]

@100Percent 我为我说话有些戾气道歉 ，我看贴之前楼主没有那么多补充说明，没有提到微服务等场景， 这标题也起的有问题，但是万变不离其宗，离开 SQL 很难弄 ，引入 ES 组件在微服务多条件，多服务聚合场景下，去用 ES, 涉及到的数据同步， 多服务联合，多服务数据聚合，复杂度增加 500%

[wangxiaoaer]

@LeegoYih #7 很好奇现在的业务不用 join 的话，关联查询如何做？ 几百万条数据总不能分别查出来，程序里面自己做匹配吧

[wangxiaoaer]

@garyxi24 #27 这种情况我的理解是：

1 这样的微法就是瞎捷豹微，服务拆分不一定合理。

2 如果必须这么做，弄个统计库，把需要的表都同步过去。但如果应用本身已经分表分库，数据量可能很大了，这个统计库可能同步成本比较高。

[garyxi24]

@EminemW 也不是每个字段都要筛选哈，只是会有筛选条件复杂的情况。至于报表我觉得是另一条路线，实时性要求不那么高，可以搭建数仓，查起来方便一些。

[dk7952638]

只有 Java 才能救编程！

[yogogo]

@garyxi24 #49 可以用 JPA 的 Specification ，多条件筛选，join 多表查询很好用

[bzj]

我想了半天内存分页是啥玩意，怎么说呢，又菜又爱抱怨

[zhaozs1]

另外想跟大家探讨一个 sql 问题，比如我有一张 user 用户表、一张 role 权限表、还有一张 user_role 用户权限关联表，我现在想筛出至少有一种权限不存在的 user ，有什么好的 sql 实现？
select * from user u
where
(
not exsists ( select ur.roleid from user_role ur where ur.userid = u.id)
or
not exsists ( select ua.uaid from user_admin ua where ua.userid = u.id)
)

[cnoder]

你这查起来这么痛苦是因为数据库没设计好

[garyxi24]

@yogogo 子查询呢，支持的怎么样

[garyxi24]

@zhaozs1 user_admin 什么意思，另添加的一张表代表拥有所有权限吗？

[garyxi24]

@cnoder 并不是这样哈，本人是做 tob 的，像 erp 这种数据库设计表之间的关联很多，是会非常复杂的。

[garyxi24]

@LeegoYih 嗯，拿我之前的公司举例，主做企业管理系统这块，模块很多，比如项目管理、客户关系管理、计划管理，都会拆成一个个服务。当然，一般情况下，各模块功能相对独立，不会出现那种情况，但像一些基础数据，比如主数据模块里的客户数据、物料数据，是会被其他模块依赖的，所以可能会出现那种情况。

[yogogo]

@garyxi24 #55 子查询还没试过，我也是因为要写那么长的 SQL 语句才开始使用 Specification

[vone]

@garyxi24 根据我的经验，分页实际开发人员偷懒的做法。

举个例子，假设某个业务，数据总量为 1000 行，如果 50 行分一页则需分 20 页。分页只是简单粗暴数据切分，并没有业务上的依据，对业务的操作的人员来说，第 1 页的重要性和第 20 页的重要性几乎一样。


所以为什么要分页？答案：因为开发人员无法解决一次性查询全量数据的性能问题。

业务人员真的需要分页？答案：不需要，谁也不想一页一页点 20 次。

当业务人员的查询条件对应的数据有 1000 行时，他实际想干什么? 答案：他可能真的是想一次性看 1000 行数据。


如果不使用分页，只根据实际业务进行限制则会有以下做法：
1 、默认数据限制起止时间，如默认只显示最近 7 天，但提供：最近一月、最近一季度、全部时间、自定义时间的切换选项
2 、默认只显示需要处理的数据状态，如默认不显示已完成、已删除、已作废，但是提供可以查询全部状态的选项
3 、默认只显示我负责、待我处理的数据内容
4 、根据业务重要性做排序，把最需要展示、处理的放在最上面
5 、增加一个总行数输入框，限制默认的最大数据量，但可以修改（如：1000 行）

[garyxi24]

@vone 我理解你的意思了，这是从业务需求角度解决了分页问题，有时候比从技术角度解决问题更有效，当然前提是能够搞定产品或是客户哈。