核心内容摘要
从0-1用三个月快速入门CTF比赛，计算机大学生最强落地指南（建议收藏）_CTF夺旗赛
当业务逻辑稍微复杂一点也建立了合适的索引可能仍然解决不好接口访问数据库时的性能问题。
有时为了遵循数据库第三范式而精心设计的表结构和JOIN查询正成为新的性能瓶颈。
第一范式1NF要求表的每个格子只能存一个值第二范式2NF要求存在多列主键时属性列必须完全依赖于所有主键列而不是部分依赖第三范式3NF则要求属性列之间不能存在传递依赖即如果某列存储了其他列的 ID就不应再重复存储该 ID 对应的其他属性。
通过第三范式设计表结构可以实现数据无冗余、依赖唯一。
在实际业务中严格遵循它往往导致许多查询需要通过 JOIN 多张表才能凑齐一条完整信息。
要查询满足条件的所有行可能需要多次扫描全表、连接、排序耗时耗力。
这时我们就应考虑增加反规范化设计。
冗余列和派生列冗余列是指违反上述 2NF 或 3NF把原本依赖其他表的属性字段直接添加到当前表中。
最常见的做法是在订单表中增加用户名、商品名称等字段避免每次查询都去关联用户表或商品表。
派生列则是指通过其他列的值经过较为复杂的计算得出的新列例如订单总价由单价和数量计算而来若是简单计算通常用视图即可解决但若计算逻辑复杂或查询频繁则可考虑将其持久化为实体列。
引入冗余列或派生列时有一个简单的判断标准大部分读取操作是否都需要用到某个外键关联信息或某个计算值。
在记录历史数据如订单、操作日志的场景下为了保证历史记录的完整性和不可变即使主表的数据后续被修改或删除也有必要在历史表中冗余存储关键信息的快照。
JSON 字段在很多场景下JSON字段是对数据库表结构的有利补充。
例如可以在一个字段内存储一个由多个标签组成的数组或者存储一个结构化的对象信息。
从范式角度看存储数组违反了第一范式而存储对象则可能引入传递依赖违反第三范式。
使用JSON字段的判断标准在于确认字段内部的值是否仅与当前表记录本身相关其他表不会直接关联到该JSON字段内部的某个具体值。
如果JSON对象或数组内部的值不需要独立查询、更新或作为关联条件那么用 JSON 存储非常合理既能保持结构灵活也能减少关联开销。
图使用冗余列、派生列、JSON列的典型表结构
预连接、预聚合报表增加冗余列、派生列主要针对有明确筛选条件、范围较小的查询。
但对于报表类、分析类的查询其特点往往是需要扫描大量数据并进行复杂的连接和聚合无论如何优化都难以避免全表扫描。
这时我们可以参考数据仓库的分层设计思想构建预连接和预聚合的中间层。
通常可以分为以下几层数据接入与原始层ODS存放从业务数据库同步过来的原始数据仅做格式标准化。
数据明细与整合层DWD对原始数据进行清洗、转换并关联补充必要的字段形成一份干净、完整的明细数据。
数据轻度汇总层DWM根据常见的报表维度如按天、按部门、按产品类别对明细数据进行轻度汇总聚合形成中间结果。
例如预先计算好每天的销售额、订单数。
数据应用与报表层APP基于汇总层的数据进一步加工成业务可直接使用的结论性数据。
这一层数据通常只会计算一次保证历史数据不变以保证对外输出的报表与数据库数据之间的统一。
此处我们本身就是在设计业务数据库“数据接入与原始层”就直接对应原始表“数据明细与整合层”对应冗余列、派生列、查询视图“数据轻度汇总层”、“数据应用与报表层”才是我们针对报表数据查询需求需要增加的表。
通过这种分层预计算将频繁使用的关联统计结果批量写入或用计划任务提前计算在查询时只需扫描轻量级的汇
总结果从而实现了报表查询的及时响应。
数据源与查询视图反规范化是在规范设计与查询性能之间寻求平衡的重要手段。
实施时首先必须明确哪些表是底层数据源哪些表或列是通过数据源重复生成的并确保只直接修改数据源生成表则应通过定时任务或实时链路保持同步。
在查询时应只获取需要的数据列避免使用 SELECT *特别是在 ORM 框架中注意不要自动查询用不到的 TEXT、JSON 等大字段。
在写 WHERE 条件时尽量将过滤条件写在最内层的子查询中尽早减少数据处理量。
对于非索引字段的查询可以尝试结合索引字段过度限定。
例如查询某个结束日期范围内的记录如果开始日期字段有索引可以同时加上对开始日期的合理推断条件以便数据库能利用索引快速定位数据。
总的来说第三范式的规范化设计保证了“真相只有一个”利于维护一致的原始数据。
而反规范化则是在此基础上通过有策略地冗余、预计算和结构化存储固化常用的查询视图从而兼顾查询性能与存储效率。
即使采用反规范化设计也须时刻牢记“真相只有一个”的原则保证数据的更新顺序先修改原始表再更新衍生表和列。
这样才能在复杂业务中既保有清晰的模型又获得高效的访问体验。