前段时间在《看潮企业管理软件》项目里做到第17个开发节点正好是整个系统的核心单据查询模块。这个模块迭代到第四个阶段标题编号是4-2主要解决销售单、采购单、出入库单、费用单这些业务单据的复杂条件查询、权限隔离和数据聚合问题。如果你正在开发企业管理系统尤其是那种每天有成百上千张单据在流转的业务后台这一篇的内容可以直接平移到你自己的项目里。我先把这段工作的背景交代清楚方便你理解后续所有设计决策。这个模块一开始只是简单的条件查询后来随着业务数据量变大、部门权限变复杂、财务对账要求变多查询功能被迫做了好几轮重构。4-2这个阶段我重点做了三件事把查询条件的组合逻辑从硬编码改成可配置的数学表达式把数据权限从“用户ID过滤”升级成“组织集合过滤”以及在列表查询的同时完成金额汇总和状态分布统计。下面我会把每一步的思考过程、代码实现和踩坑记录都展开讲。1. 查询模块的整体设计与数学建模思路1.1 单据查询到底在解决什么问题企业管理软件里单据查询不是一个普通的列表页它承担着业务流转、财务复核、库存核对、经营分析等多重职责。以《看潮企业管理软件》为例系统里跑着采购订单、销售订单、其他入库单、其他出库单、费用报销单、收款单、付款单等十几种单据类型。每种单据都有自己的主表和明细表主表记录单号、往来单位、经办人、部门、金额、状态、业务日期明细表记录商品或费用项目、数量、单价、金额。查询模块要面对的真实场景是这样的财务人员想查“上个月A供应商所有已审核且金额大于5万的采购单”仓库人员想查“最近一周所有未出库的销售订单”老板想看“本季度每个月的费用趋势”。这些查询条件不是固定的而是动态组合的。如果代码里写死条件每来一个新需求就改一次SQL系统不出三个月就会变成一团乱麻。所以我的第一个设计原则是把查询条件当作一个可组合的数学表达式来处理而不是一段拼接的SQL字符串。每个条件本质是一个谓词用户勾选的每一个条件都是对结果集合的一次交集操作。这样设计的好处是不管未来新增多少种查询维度代码层面都只需要维护好条件解析和组合逻辑业务上新增检索项的成本降到最低。1.2 条件组合与分页模型的数学拆解单据查询在数学上可以看作一个集合筛选过程。假设所有单据构成全集U用户选择的条件分别为P1日期范围、P2往来单位、P3金额区间、P4审批状态最终的结果集就是R U ∩ P1 ∩ P2 ∩ P3 ∩ P4温故一下中学数学里的集合论这个公式就是查询模块的核心。写代码的时候我其实是把每个条件转换成一个SQL片段然后通过AND关键字做交集运算。这是最简单的场景但实际业务里还会出现OR条件。比如“查询经办人是张三或者部门是销售部的所有单据”这里就出现了并集关系。为了解决这类问题我在查询条件DTO里增加了一个逻辑分组字段类似MyBatis动态SQL里的嵌套条件把同一组的条件用括号包起来保证交并集运算的顺序正确。分页模型也是个值得说道的数学问题。普通分页的公式是offset (pageNum - 1) * pageSize这个大家都懂。但是在单据查询场景下如果用户停留在第20页然后又修改了查询条件再次查询页码应该回到第1页。这类交互看起来简单但如果在前后端没有约定清楚就会出现“查询条件变了但跳回第20页”的体验问题。我在4-2阶段把前端路由参数和后端请求参数统一了分页协议页码用pageNum、每页条数用pageSize每次条件变更强制重置pageNum为1同时后端对pageSize做上限校验超过200条直接按200条处理防止用户一页拉出几千条数据把接口拖垮。1.3 为什么要引入“编程与数学”的视角这个系列标题叫“编程与数学”不是随便挂个名字。我总结过在单据查询模块至少有四个地方需要数学思维集合运算决定条件组合的正确性排列组合决定查询条件维度的可扩展性时间复杂度分析决定索引和查询性能优化方向统计学里的分组聚合决定汇总统计的实现方式。去掉数学视角写代码也能跑但很难写出结构清晰、性能可控、易于扩展的模块。比如在优化慢查询的时候如果不理解B树的查找时间复杂度是O(log n)就不会明白为什么某些查询走了索引还是慢为什么某些条件下索引会失效。这些细节我放在后面章节展开。2. 核心实现查询条件与动态SQL的可复用设计2.1 查询DTO怎么设计才不重复单据查询最容易犯的错误是为每种单据写一套独立的查询类结果代码里充斥着大量重复字段startDate、endDate、supplierName、customerName、status、amountMin、amountMax、deptId、userId。我在4-2阶段做了一次大的抽象把通用查询字段抽成一个基类BaseQueryDTO里面放分页参数、时间范围、金额范围、单据状态、关键词模糊搜索、数据权限字段。public class BaseQueryDTO { private Integer pageNum 1; private Integer pageSize 20; // 日期范围 private LocalDate startDate; private LocalDate endDate; // 金额范围 private BigDecimal amountMin; private BigDecimal amountMax; // 单据状态多个状态用逗号分隔 private String statusList; // 关键词用于单号、备注等模糊搜索 private String keyword; // 数据权限过滤字段 private Long deptId; private Long userId; // 排序字段和排序方向 private String orderBy; private String orderDirection; }每种具体单据的查询类继承这个基类再扩展自己的专属条件。比如销售订单查询需要客户ID、销售员ID、订单来源采购单查询需要供应商ID、采购员ID、到货状态。这样的继承体系让代码结构清晰通用条件的解析逻辑只需要写一遍。2.2 动态SQL的组合策略与条件解析有了查询DTO之后接下来就是怎么把对象转成SQL。MyBatis的if标签是大多数人的第一选择但我用了一段时间后发现业务复杂的查询DTO条件多了之后XML里的if判断会变得非常臃肿。而且每种单据都要写一套几乎一模一样的动态SQL。后来我换了一种思路在service层先把查询条件解析成一个统一的查询参数对象将条件集合抽象为ListQueryCondition每个QueryCondition包含字段名、操作符、值。这样XML里的通用查询SQL写一次所有单据共用一套解析逻辑。public class QueryCondition { private String fieldName; private String operator; // eq, ne, gt, ge, lt, le, like, in, between private Object value; private Object secondValue; // between 右侧值 }具体到代码实现我在service里有一个构建条件的方法protected ListQueryCondition buildCommonConditions(BaseQueryDTO query) { ListQueryCondition conditions new ArrayList(); if (query.getStartDate() ! null) { conditions.add(new QueryCondition(biz_date, ge, query.getStartDate())); } if (query.getEndDate() ! null) { conditions.add(new QueryCondition(biz_date, le, query.getEndDate())); } if (query.getAmountMin() ! null) { conditions.add(new QueryCondition(total_amount, ge, query.getAmountMin())); } if (query.getAmountMax() ! null) { conditions.add(new QueryCondition(total_amount, le, query.getAmountMax())); } // 其他通用条件... return conditions; }再配合一个条件转SQL的方法循环遍历条件列表按操作符生成SQL片段用AND连接。遇到关键字搜索就映射到单号、备注等多个字段的OR条件并且用括号包起来避免和外面的AND产生歧义。2.3 索引设计背后的成本和收益条件查询的性能很大程度上取决于索引设计。但索引不是越多越好每多一个索引写入数据时的维护成本就多一份。这个成本收益分析是我在处理单据查询时反复权衡的事情。我在实践中的经验是先分析查询模式最频繁的过滤条件再决定联合索引的字段顺序。单据查询里业务日期是最常见的查询条件然后是单据状态、往来单位客户或供应商最后是经办人。以销售订单为例我设计的联合索引是CREATE INDEX idx_sale_order_query ON biz_sale_order (biz_date, status, customer_id, sale_user_id);这个索引基于B树结构能够高效支撑“按日期范围过滤再按状态、客户、销售员进一步缩小范围”的查询模式。它之所以高效是因为InnoDB引擎中最左前缀原则下查询条件必须从索引最左侧字段开始匹配。日期是第一个字段后续字段的匹配建立在日期过滤后的小范围上。同理“查某客户的所有单据”这种场景就是直接从customer_id开始匹配但如果客户ID不是联合索引的第一个字段这个查询就走不上这个索引。我还做了一个额外优化对单据号字段建立唯一索引因为单据查询经常直接输入精准单号。这样等于把精确匹配和范围查询的路径分成两条精准查单号走唯一索引条件组合查询走联合索引。3. 权限与数据隔离过滤查询结果的集合运算3.1 数据权限模型的集合本质企业管理软件和普通互联网应用最大的区别在于数据不是所有用户都能看的。销售员只能看自己和本部门的单据部门主管能看整个部门的单据财务能看全公司的单据老板能看所有层级的数据。这层数据隔离如果处理不好轻则业务协作混乱重则财务数据泄露。从集合论角度看每个用户能查询到的单据集合取决于他拥有的权限角色集合。我设计的规则是用户最终可见数据 用户本人数据 ∪ 本部门数据 ∪ 下级部门数据 ∪ 全部数据具体取哪几个集合取决于角色配置。这套模型借鉴了RBAC基于角色的访问控制的思想在角色里存储数据范围枚举值SELF、DEPT、DEPT_TREE、ALL。3.2 从角色到部门树的数据权限过滤实现实现数据权限过滤需要两张辅助表一张是部门表保存部门层级关系另一张是用户表保存所属部门。查询的时候先根据登录用户的角色算出他可见的部门ID集合再把这个集合作为条件之一拼进查询SQL。核心代码如下public SetLong getVisibleDeptIds(SysUser user) { SetLong result new HashSet(); for (SysRole role : user.getRoles()) { switch (role.getDataScope()) { case SELF: // 仅本人数据部门集合置为空 break; case DEPT: result.add(user.getDeptId()); break; case DEPT_TREE: result.addAll(getSubDeptIds(user.getDeptId())); break; case ALL: return null; // null表示不限制 } } return result; }这段代码里我故意把“仅本人”和“全部数据”都做了一个特殊标记。比如SELF场景下查询条件里加的是create_user_id 当前用户IDALL场景下不加数据权限条件其他场景则是在SQL中追加dept_id IN (可见部门集合)。为什么这么设计因为如果“仅本人”也用部门集合过滤就会把别人在本部门下的单据查出来业务上就走样了。所以权限过滤条件必须按照“用户ID”和“部门ID”两种粒度分别追加。3.3 数据权限过滤的常见雷区这个模块我踩过一个很深的坑多角色数据范围合并时权限取交集还是并集的问题。比如一个用户同时持有销售员角色SELF和销售主管角色DEPT_TREE他应该看本人数据加上整个部门的树状数据这时候权限结果是并集。但如果系统里存在“敏感数据角色”和“普通数据角色”的互斥关系且需要同时限制就要用交集。我最终的方案是默认取并集因为企业里用户兼职场景远多于互斥场景对于确实需要互斥的数据通过字段级别的控制来实现而不是在数据权限上叠加交集避免模型复杂到没有开发者能维护。还有一个坑是审批流场景的历史数据权限。单据在审批过程中审批人能看到这笔单但这位审批人可能不属于制单部门。如果用严格的部门数据权限过滤审批人反而看不到待审单据。这个问题的解法是把“审批人”也作为一个隐藏的数据权限维度在查询SQL里额外加一个条件如果当前用户是该单据的审批人即使不属于可见部门范围也能查询到这条数据。实现上就是(dept_id IN (可见部门) OR (approver_id 当前用户ID AND 单据状态为待审))这样的逻辑组合。这也是集合运算里A∪B而不是A∩B的应用场景。4. 聚合统计与列表查询的协同优化4.1 列表页汇总数据的实现策略单据列表页通常需要显示三组汇总数据筛选结果的总金额、总数量、单据总张数。以前的做法是查询列表之后再用同样的条件单独执行一次汇总SQL。数据量小的时候没有问题但一旦加上权限条件和多表关联汇总查询会明显变慢。我在4-2阶段做了一次优化把“查询结果金额汇总”从“列表查询”中拆出来并列执行。因为列表查询走联合索引取的是分页数据汇总查询走的是索引扫描取全量数据两者并发执行比先后执行快。具体实现是CompletableFuture异步并发两个查询一个查列表一个查汇总。这里要特别注意金额汇总查询在SQL执行层面仍然要拼上和列表查询完全一致的过滤条件否则汇总的数据和列表展现的数据就不是同一个集合。我在项目里把条件构建方法做成公共方法列表查询和汇总查询同时复用避免条件不一致。4.2 状态分布统计的数学意义除了汇总金额列表页下方还会展示各状态单据数量分布。这个统计的逻辑是对结果集按状态字段分组然后计算每组的计数。SQL很简单SELECT status, COUNT(*) AS cnt FROM biz_sale_order WHERE biz_date BETWEEN ? AND ? AND dept_id IN (...) GROUP BY status但这张统计表有一个很微妙的问题分页列表当前页显示的可能是20条数据而状态分布统计是整个结果集的全部单据不是当前页的单据。从产品体验角度讲用户看到统计数量大于列表数据量会以为是bug。所以在前端交互上我明确加了一个提示文案“统计基于当前筛选条件下的全部单据”这样用户就知道统计结果不是当前页的数据汇总而是整个筛选结果的分组统计结果。4.3 大结果集导出与内存控制单据查询还有一个隐藏杀手数据导出。财务部门特别喜欢点“导出全部”一次性拉出数万条记录。如果直接用普通列表查询接口然后循环加载明细再写入Excel内存一定会被打爆。我的做法是导出接口不走列表查询的接口而是走独立的流式查询接口。使用MyBatis的游标查询模式每次从数据库取500条写入Excel后再取下一批。导出过程中前端显示进度条进度值根据已导出行数和预估总行数的比例计算。总行数可以先用COUNT查询拿到然后每处理完一个批次进度就增加一批的比例。导出还有一个权限校验上的细节要校验导出用户是否勾选了“允许导出”的角色权限因为有些敏感单据类型不允许下载到本地。这个校验放在导出接口入口处放在列表查询里会造成不必要的性能开销。5. 常见问题与排查技巧实录5.1 慢查询案例时间字段使用函数导致索引失效项目上线后出现了一个典型的慢查询销售订单列表按日期查询耗时3秒以上。一开始我以为是数据量太大的问题后来用EXPLAIN分析执行计划发现where biz_date BETWEEN条件明明有索引可用执行计划却显示全表扫描。问题出在代码里写的是WHERE DATE(biz_date) BETWEEN ? AND ?对日期字段加了一层DATE函数让索引直接失效了。改成WHERE biz_date ? AND biz_date DATE_ADD(?, INTERVAL 1 DAY)之后查询回到了毫秒级。这个坑特别隐蔽因为开发环境的数据量小全表扫描也感觉不出来生产环境数据量一大性能问题立刻暴露。5.2 权限过滤漏数据部门树递归查询的边界问题部门树递归查询也出过状况。公司组织架构调整某个部门被合并到另一个部门下面历史单据的dept_id还是旧部门的ID。在数据权限过滤时如果新部门主管的可见部门集合不包含旧部门ID他就查不到历史单据。解决方法是给部门表增加一个merge_history记录表专门保存部门合并前后的映射关系。查询的时候把旧部门映射也加入权力部门集合。做完这个处理之后部门调整对历史单据查询的影响就消失了。5.3 分页深度过大深翻页的性能优化用户翻页翻到第1000页时LIMIT 19980, 20会产生严重的性能问题。因为数据库需要先扫描前19980条数据然后丢弃才能返回目标数据。在这种场景下我用的是“延迟关联”的优化方案先只查询主键ID再通过主键关联回查完整记录。-- 优化前 SELECT * FROM biz_sale_order WHERE biz_date ? AND dept_id IN (...) ORDER BY id DESC LIMIT 19980, 20; -- 优化后 SELECT o.* FROM biz_sale_order o INNER JOIN ( SELECT id FROM biz_sale_order WHERE biz_date ? AND dept_id IN (...) ORDER BY id DESC LIMIT 19980, 20 ) tmp ON o.id tmp.id ORDER BY o.id DESC;这种写法的原理是让子查询只扫描主键索引而不是所有字段数据量大了之后性能差距非常明显。我还额外加了一个经验值当用户翻页超过100页时前端弹出提示建议用户缩小查询条件或者改用导出功能。不是所有需求都要靠技术硬扛合理的交互设计往往能解决一半的性能问题。写在最后的一点心得单据查询这个模块表面上看是CRUD实际上隐含了集合运算、树形结构、分组聚合、性能分析等各种编程和数学的知识点。我在开发过程中最大的体会是设计阶段多花半小时想清楚查询模型和权限模型比上线后天天加班修复数据问题要省心得多。这个4-2阶段做完之后新需求进来基本只需要加字段和条件不需要动查询框架本身。后面如果还有机会我打算把查询条件做成语义化配置界面让业务人员自己组合查询条件不再依赖开发改代码。最后再分享一个小技巧所有查询模块的调试日志一定要记录完整SQL和参数哪怕当时觉得很啰嗦出了问题排查时它就是救命稻草。