此笔记位置已失效,请从目录继续阅读。
全书导读
本书用缺陷跟踪系统贯穿关系建模、物理存储、查询和应用开发。每个反模式都从合理目标出发,说明看似方便的做法怎样把成本转移到别处,再比较识别信号、合理例外和替代方案。读者应先问数据表达的事实及必须保持的约束,再选择技术;反模式名称不是一份无条件禁令。
本笔记依据2011年中文版EPUB完整正文,包括译者序、四部分导引和规范化附录。全书是25个编号章节,其中第1章为引言,第2至25章讨论24个反模式。SQL、PHP和框架示例属于当时背景,本次没有执行这些代码。现代引擎能力、密码保护以及原书若干代码和算术错误,已在生成对应讲解时按具体证据限定;不把历史示例当成可直接上线的脚本。
可按全书顺序理解责任如何在数据库与应用之间分配,也可从当前问题进入某章,再回到附录理解键、依赖和规范化。正文先解释每个单元,独立卡片保存可复用的单一想法;实践卡为后续计划,不代表已进行数据库实验。
全书主线
数据库设计首先是在表达事实。一个产品可以联系多个账号,应该表达为关系中的多条关联,而不是让字符串解析承担关系运算;一条树边、一个属性和一种实体身份,也各有不同约束。逻辑设计部分说明省略表、键或关系的代价,往往会在查询与应用中重新出现。
物理设计进一步考虑机器怎样表示和访问事实。数值的精度、允许值集合、文件与事务边界、索引的读写代价,不能仅由方便输入或直觉决定。逻辑上相同的答案,可能有完全不同的性能与失败方式,需要结合引擎规则和实际测量。
查询部分把注意力转到语义:未知值不是零,分组结果必须有确定含义,随机取样需要明确概率,搜索要定义词或字符的匹配需求。把多个目标压进一条语句或依赖隐式列顺序,会使结果口径和调用契约难以维护。
应用部分最后检查边界上的责任:密码不能依赖可逆或快速摘要方案,值绑定不能替代标识符白名单,序列编号不承担展示行号职责,错误和版本变更都应可追踪。模型类也不应因为能直接访问表,就承包所有业务。附录从函数依赖和连接依赖解释为什么前面的结构容易或难以保持一致。
flowchart TD
A[明确事实与业务约束] --> B[关系与键表达逻辑模型]
B --> C[表示与索引承担物理代价]
C --> D[查询保持明确语义与口径]
D --> E[应用维护安全及演进边界]
E --> F[规范化解释依赖与异常]
F --> A
箭头表示解释关系,实际设计需要在这些层次之间反复核对。
译 者 序 一
SQL与NoSQL应按访问方式、数据形态和服务保证选择。内存型键值系统善于直接访问,关系数据库保留灵活查询和关系约束的用途。译者以当年的社区实践说明不存在通吃的存储产品;其中“任意三选二”的CAP速记须限定于分区下的保证。
按数据场景选择存储
面对多样的数据处理需求,先核对数据关系、查询方式和一致性要求,再决定存储技术。NoSQL产品通常优化某类访问场景,不能仅凭流行程度替代关系数据库;频繁变化的关联分析尤其要评估查询表达力。译者的2011年社区经验是例子,不是所有系统的选型结论。
按数据场景选择存储保存独立卡片。
原书依据 · 1 处
分区时的一致可用取舍
CAP讨论分布式服务在网络分区下的保证:若分区两侧无法通信,就不能同时保证每个正常节点上的请求都得到响应,并保证读写像在单一副本上按实时顺序发生。这里的一致性是强的一致性条件,可用性也不是泛指在线率。原书“三项最多取二”是速记,不能理解成任何时候任意放弃一项即可消除全部代价。补充限定核验于Gilbert与Lynch的2012年回顾论文:https://groups.csail.mit.edu/tds/papers/Gilbert/Brewer2.pdf 。
分区时的一致可用取舍保存独立卡片。
原书依据 · 1 处
Redis的数据结构存储
Redis是以键访问值的内存数据存储,值可以采用不同数据结构。它使程序直接对这些结构执行操作,而不是要求每次都把内容还原成应用对象处理。这里用于说明NoSQL会针对访问模型优化;不能由“键值”推断它能直接替代任意关系查询。
Redis的数据结构存储保存独立卡片。
译 者 序 二
数据库设计会在维护阶段反复接受现实检验。遇到性能或正确性问题,应先检查关系和SQL是否合理,再判断缓存能否解决真正的瓶颈;缓存本身不会修正错误的数据关系。
先检查数据库再加缓存
数据库查询出现问题时,直接加缓存可能只掩盖不合理的关系或SQL。先定位原始数据结构和数据库已有能力是否能解决问题,再评估缓存的收益与维护成本。译者回顾自己的设计盲点,并没有证明所有缓存都多余,也不能把当年的NoSQL内存开销概括成所有产品的共同限制。
先检查数据库再加缓存保存独立卡片。
原书依据 · 1 处
第1章 引言
SQL通过描述需要的关系结果,让数据库决定执行方式。对象程序按对象组织状态,关系数据库按行、列和约束组织事实,两者的映射差异需要学习,不能指望一个框架自动抹平。实现SELECT也远超词法解析:其语义、关系操作和执行需要数据库能力。
SQL的声明式表达
SQL查询描述希望得到哪些数据以及它们满足的关系条件,而不是逐步指定如何遍历每一行。数据库据此选择执行办法。它以关系和集合式操作组织问题,但常见SQL结果允许重复行,不能把SQL等同于数学上的无重复集合;原书将若干语言并列为声明式只宜作为对比线索。
SQL的声明式表达保存独立卡片。
原书依据 · 1 处
对象关系阻抗失配
对象模型用对象身份、引用及行为组织程序,关系模型用表、键和关联表达数据;二者映射时会出现表达方式和约束上的差异。这种阻抗失配不限于“写出了不一致的数据”。ORM能提供映射便利,但仍需理解关系与查询,才能判断生成的SQL是否合适。
对象关系阻抗失配保存独立卡片。
原书依据 · 1 处
1.1 谁需要这本书
本书面向已有SQL基本语法经验的开发者。学习价值在于用后果和替代方案辨别做法优劣;开发者与DBA也可据此讨论具体设计,而不是只争论习惯。
1.2 本书内容
反模式并不等于语法错误。某种方案确实解决眼前任务,却产生更严重或更隐蔽的后果,因此会在不同项目中反复出现。学习时要同时认识原始目标和失败机制。
反模式为何反复出现
反模式是为解决一个问题而采用、却往往引入其他问题的做法。它通常具有短期诱因,例如少改表、少写代码或看起来更统一,因此可能被独立重复发明。判断重点是目标与后果之间的因果关系,不是某段代码是否流行或能否立即运行。
反模式为何反复出现保存独立卡片。
原书依据 · 1 处
1.2.1 本书结构
四部分分别回答存什么、怎样落地存储、如何读写、应用怎样使用数据库。逻辑设计决定表中事实及关联;物理设计利用数据类型、索引和产品特性;查询层用SQL读写;应用层把这些能力组织进业务代码。附录补充规范化理论。
数据库设计的四个层次
数据库工作的逻辑层决定存储哪些事实及它们的关系;物理层决定如何用数据类型、表和索引实现;查询层负责读写这些数据;应用层决定程序如何调用数据库。层次相互影响,但不是同一个问题:缓存或复杂查询不一定能补救错误的关系建模。
数据库设计的四个层次保存独立卡片。
原书依据 · 1 处
1.2.2 反模式分解
分析反模式时,先说清要完成的任务,再描述现有方案及其额外后果,用识别信号找到现实中的对应情况;随后核对少数合理例外,最后选择能满足原目标的替代方案。保留例外可避免把经验退化为机械禁令。
分析反模式的五个步骤
遇到可疑设计,依次核对:原始目标是什么;常用方案怎样制造新问题;项目中有什么可识别信号;哪些条件下该方案仍合理;替代方案怎样完成同一目标并避免根因。识别信号只能提示检查,不能代替因果判断;合理例外也是完整分析的一部分。
分析反模式的五个步骤保存独立卡片。
原书依据 · 1 处
1.3 本书未涉及的内容
本书假设读者已会SQL基本语法,主要讨论开发者的设计与使用错误。性能调优、ORM、数据库管理和替代存储比较均超出主体范围;跨厂商示例也不意味着各产品完全支持同样语法。
1.4 规约
实体关系图把实体或表画成矩形,以连线及端点符号表达关系的数量约束。图1-1比较一对一、一对多和多对多,并展示用交叉表把多对多分解为两个一对多。后续模型比较建立在这些符号上。
实体关系图的基数
实体关系图用节点表示实体或表,用线表达关联,并用端点标记最少与最多可关联多少行。乌鸦脚表示“多”,短竖线表示“一”,圆圈表示可为零。多对多关系通常通过交叉表记录每对关联,分解为两组一对多;逻辑关系和实际外键约束要结合表定义核对。
实体关系图的基数保存独立卡片。
原书依据 · 1 处
1.5 范例数据库
贯穿全书的例子是缺陷跟踪库:Bugs保存缺陷,Accounts保存人员,Comments记录评论,BugStatus约束状态,Screenshots和Tags记录附属信息。BugsProducts用复合键把缺陷与产品的多对多关系展开。Bugs到Accounts有报告人、负责人、验证人三个不同角色,不能把三条外键误认成重复关系。
同表外键表达不同角色
一条缺陷同时关联报告人、负责人和验证人,三个字段都引用Accounts主键,却表达三个不同角色。连接这些关系时,应为每个角色使用独立的表别名和连接条件。它们不是重复外键;是否允许为空也反映该角色是否已确定。
同表外键表达不同角色保存独立卡片。
复合键标识一对关联
多对多关系可以用交叉表保存每一对关联。BugsProducts以(bug_id, product_id)为复合主键,使同一缺陷和同一产品的组合最多出现一次;两列分别作为外键限制两端实体存在。复合键约束组合的唯一性,不意味着单独一列必须唯一。
复合键标识一对关联保存独立卡片。
1.6 致谢
致谢说明家人、编辑和审阅者对写作的支持,是全书形成背景。
第一部分 逻辑型数据库设计反模式
第一部分集中于表、字段和关系的逻辑组织,接下来用多值属性、树、键、约束和动态属性等错误方案说明事实应该怎样表达。
第2章 乱穿马路
本章的核心不是字符串一律不能含逗号,而是关系中的多个独立账号不应靠一个编码列表维持身份。先明确多对多需求,再看查询、统计、更新和外键为何逐项变难,最后由交叉表恢复可约束的关联。展示用列表仍可以由查询结果生成。
产品从单一联系人变成多个联系人后,把账号列表塞进一个字符串虽然少改表,却让可存人数取决于ID位数和字符串长度。这个情境引出“乱穿马路”:试图绕开交叉表,反而失去关系数据原有的操作和约束能力。
逗号列表产生容量怪象
多联系人需求若用定长字符串保存逗号分隔的ID,能保存多少人会随ID位数变化,同样五个人也可能有不同容量占用。根因是把多条关联编码成单个字段,容量限制不再对应业务人数。它提示应重新建模多值关系;后续还须检查查询和完整性代价。
逗号列表产生容量怪象保存独立卡片。
2.1 目标:存储多值属性
原模型中每个产品只保存一个联系人,而同一账号可以联系多个产品;新需求允许每个产品也联系多个账号,双方因此形成多对多关系。单行字段放不下并不说明必须把多个ID拼成字符串。
交叉表替代逗号列表关联本项背景,实质主要讲解在对应位置。
2.2 反模式:格式化的逗号分隔列表
将account_id由整数外键改为VARCHAR保存列表,改动看似局限在单列,却改变了该列的语义:它不再是一个可直接约束和比较的账号标识。下面将逐项分析查询、统计、更新及完整性问题。
列表编码绕过逐项约束关联本项背景,实质主要讲解在对应位置。
2.2.1 查询指定账号的产品
把多个ID编码在字符串里,会把精确的键比较变成分词或模式匹配。除了边界误配风险,查询也难以直接利用普通键索引;书中正则写法属于当时MySQL语法,不宜直接复制。
列表匹配替代了键查询
查询“哪些产品包含账号12”时,逗号字符串要求识别词边界,普通等值比较已不适用。多表关联也必须逐项解析列表,使优化更困难;不能把这一点扩大成任何数据库都必定执行同样的全扫描计划。将关联拆成行后,才能直接按两个实体的键连接。
列表匹配替代了键查询保存独立卡片。
2.2.2 查询指定产品的账号
反向查询产品的联系人同样要把账号逐个与字符串列表匹配。问题不在JOIN这个操作本身,而在连接条件已经失去正常的键等值关系。
列表匹配替代了键查询关联本项背景,实质主要讲解在对应位置。
2.2.3 执行聚合查询
COUNT、SUM、AVG等聚合面向行或分组。把列表放在单格后,统计人数反而要数逗号并处理空值、空串及格式异常,算法与业务含义开始分离。
聚合应统计关联行
需要统计每个产品的联系人数量时,把关联保存成行,就能按产品分组统计。逗号列表则迫使程序把分隔符数量当成人数,异常输入和空列表会破坏这个对应关系。聚合函数计算行上的值,不会自动理解字符串中编码了几个实体。
聚合应统计关联行保存独立卡片。
2.2.4 更新指定产品的账号
对字符串列表删除一个账号通常要先读取、在应用中拆分修改、再回写整个值;关系中的局部变化因此变成整段重建。把每对关联分成一行后,可直接增删目标关系。
局部关系不该重写整串
删除一个联系人若需要读取列表、拆分、移除元素、拼回并覆盖整列,说明关系的存储粒度过粗。交叉表把每对关系独立成行,增删只影响该关系,也不必处理分隔符和排序。书中PHP示例只说明复杂度来源,不代表当代可直接运行的API。
局部关系不该重写整串保存独立卡片。
2.2.5 验证产品ID
字符列可容纳banana等内容,但业务所需的是存在的账号标识。字符合法不代表关联合法;把每项拆出来,才能分别应用类型和外键规则。
列表编码绕过逐项约束
逗号字符串中的每项无法直接接受账号整数类型和外键检查,因而可能出现非ID文字、重复或不存在的账号。数据库只看到了一个合法字符串,无法自动证明每一段都代表有效关系。关联成行后,每列类型检查格式,外键检查引用存在;两种检查解决不同问题。
列表编码绕过逐项约束保存独立卡片。
2.2.6 选择合适的分隔符
如果列表项本身能含逗号,更换分隔符只是把冲突推迟。数据表示还得处理转义与解析,原本简单的关联问题由此变成自定义编码协议。
换分隔符不消除歧义
当字段内容可包含任意字符时,换一个“少见”分隔符不能保证永不冲突,还需要转义和解析约定。若应用需要独立查询或更新每项,优先让每项占独立行;只有整个值始终作为不透明内容使用时,才适合保留整体编码。
换分隔符不消除歧义保存独立卡片。
2.2.7 列表长度限制
VARCHAR约束字符容量,业务却想约束联系人数量。ID位数变化会令两者失去对应关系,这不是把长度任意加大就能定义清楚的业务限制。
逗号列表产生容量怪象关联本项背景,实质主要讲解在对应位置。
2.3 如何识别反模式
频繁讨论最大列表长度、如何在SQL里分词,或寻找永不出现的分隔符,是应当检查数据模型的信号。信号不能单独证明设计错误,还要看应用是否真正操作列表项。
列表匹配替代了键查询关联本项背景,实质主要讲解在对应位置。
换分隔符不消除歧义关联本项背景,实质主要讲解在对应位置。
2.4 合理使用反模式
若外部输入就是一段列表文本,应用只原样保存和整体取出,从不查询或修改单项,那么拆表未必有收益。这一例外的关键是数据作为整体使用,而不是因为逗号列表天然更快。
不透明列表可整体存储
外部传来一段列表,应用只需完整存取、不校验或操作其中单项时,可以把它作为整体内容保存。若以后要按元素关联、聚合或更新,就应重新评估规范化。反规范化是基于访问需求的权衡,不能凭“少建一张表”假定性能更好。
不透明列表可整体存储保存独立卡片。
原书依据 · 1 处
2.5 解决方案:创建一张交叉表
Contacts每行记录一个产品与一个账号的关联。复合主键避免同一对重复,两个外键各自检查实体存在。交叉表的价值在于把关系本身变成可查询和约束的数据,而不是仅把字符串移到别处。
交叉表替代逗号列表
产品与账号多对多时,建立Contacts,一行只记录一个product_id与一个account_id。以两者组合唯一,并分别关联Products与Accounts,既能直接连接查询,也能独立增删与计数。交叉表还可保存关系自己的属性,例如加入日期;它不是实体表的重复拷贝。
结构图:
erDiagram
Products ||--o{ Contacts : 产品
Accounts ||--o{ Contacts : 联系人
Contacts {
bigint product_id PK,FK
bigint account_id PK,FK
}
图表达关系模型,不表示程序类的继承。
交叉表替代逗号列表保存独立卡片。
2.5.1 通过账号查询产品和反过来查询
关系成行后,按产品键连接Contacts与Products,按账号键连接Contacts与Accounts。原文第一个查询错误地把Products.account_id当连接键;在新设计中应连接product_id,不能把这个排印错误当模型要求。
列表匹配替代了键查询关联本项背景,实质主要讲解在对应位置。
交叉表替代逗号列表关联本项背景,实质主要讲解在对应位置。
2.5.2 执行聚合查询
把联系人独立存行后,分别按产品或账号分组即可统计数量。找最大分组时还要正确比较聚合结果;原文最后一条把未分组列和MAX混用,不能作为可靠的最大分组写法。
聚合应统计关联行关联本项背景,实质主要讲解在对应位置。
2.5.3 更新指定产品的相关联系人
关系变化可以直接表示为增加或删除Contacts中的一行,条件包含产品和账号两个键。无需取回全部联系人再重建。
局部关系不该重写整串关联本项背景,实质主要讲解在对应位置。
2.5.4 验证产品ID
每个账号占一个有类型的字段,外键再把它约束到实际存在的账号。类型与外键结合,使格式正确和引用有效分别成为数据库可以检查的条件。
列表编码绕过逐项约束关联本项背景,实质主要讲解在对应位置。
2.5.6 列表长度限制
关系数量不再取决于ID字符串的总长度。如果业务规定联系人上限,应显式检查关系行数和业务规则;物理存储容量仍然存在,但它与业务人数不是同一限制。
逗号列表产生容量怪象关联本项背景,实质主要讲解在对应位置。
2.5.7 其他使用交叉表的好处
交叉表既可给关联字段建索引,也可记录关系属性,例如加入日期和联系人顺序。外键是否自动创建索引取决于数据库产品,不能当作统一规则。
交叉表替代逗号列表关联本项背景,实质主要讲解在对应位置。
外键索引须按产品核对
声明外键不等于在所有数据库中都会自动建立引用列索引。关系完整性与查询访问路径是两项职责:先核对数据库及版本对外键的要求,再查看实际索引与连接计划。不能因为DDL中有FOREIGN KEY就认定关联查询一定具备合适索引。
外键索引须按产品核对保存独立卡片。
原书依据 · 1 处
第3章 单纯的树
树形数据有父子、祖先、路径和子树等不同访问需求。邻接表直接保存边,路径枚举保存路径字符串,嵌套集编码区间,闭包表保存可达关系;它们把查询复杂性与更新成本放在不同位置。递归查询能力改变了邻接表的适用边界,不能沿用旧引擎限制作统一结论。
评论回复可形成很深的层级。只保存父节点能轻松找到下一层,却未直接保存整条祖先路径;若每次都拉取整棵树并在应用内重组,传输和重建可能超过实际需要。树模型应依常见操作选择,而不是默认层级一定很浅。
树查询不能假定固定深度
回复分支深度未知时,用固定次数的自连接只能覆盖预设层数;先取全部评论再在应用中重建,又可能传输大量无关行。先明确需要直接子节点、整棵子树还是聚合结果,再选择递归查询或保存更多关系信息的模型。邻接表本身不是错误,问题在于操作需求与实现能力不匹配。
树查询不能假定固定深度保存独立卡片。
3.1 目标:分层存储与查询
树中的节点通过父子关系组成层级:根没有父节点,叶没有子节点,中间节点承上启下。组织关系与评论回复都可以用这种模型;这里讨论的普通树中非根节点只有一个直接父节点。
树的根叶与父子关系
树用父子关系组织节点。根没有父节点,叶没有子节点,非叶节点至少有一个子节点;普通有根树中,每个非根节点有且只有一个直接父节点。组织架构和评论回复是例子,多上级关系则已超出这种简单树假设,不能机械套用单父字段。
树的根叶与父子关系保存独立卡片。
原书依据 · 1 处
3.2 反模式:总是依赖父节点
邻接表让每个节点保存直接父节点的标识,根的parent_id为空,自引用外键保证被引用节点存在。它表达直接边,不预存所有可达路径;外键本身也不会证明结构无环。
邻接表只保存直接父边
邻接表为每个节点保存一个直接父节点ID,根的父ID为空,自引用外键检查父节点存在。新增叶子或读取直接子节点很自然,但多层祖先与后代要重复沿边遍历。存在性约束不等于树结构约束,仍应防止把节点移到自己的后代之下形成环。
结构图:
flowchart TD
A[根1] --> B[节点2]
B --> C[节点3]
A --> D[节点4]
D --> E[节点5]
D --> F[节点6]
F --> G[节点7]
箭头由父指向子;存储时子节点的parent_id反向引用父ID。
邻接表只保存直接父边保存独立卡片。
原书依据 · 1 处
3.2.1 使用邻接表查询树
一层自连接只能再扩展一层;固定写四个别名并不能查询任意深度。若只要一个分支或节点总数,全量取回后重建也会做多余工作。应按实际输出的粒度评估方案。
树查询不能假定固定深度关联本项背景,实质主要讲解在对应位置。
3.2.2 使用邻接表维护树
新增叶子与移动子树在邻接表中常只改一条父边;删除却必须先决定子孙的命运。删整棵子树与删一个中间节点并提升孩子,是两种不同业务动作,不能用同一条级联规则混同。
删节点前先定子树语义
删除树中的非叶节点时,先决定后代也删除,还是保留并改挂到别处。前者可由合适的级联外键或先子后父删除处理;后者应先更新子节点的父边,再删除该节点。移动邻接表子树可只改根的父边,但仍要保证不会形成环;整组操作必须保持结构一致。
删节点前先定子树语义保存独立卡片。
3.3 如何识别反模式
强行给树规定层数、频繁担心树操作代码、依靠定时脚本修孤立节点,都是模型与操作不匹配的信号。每次结构变更都应维护完整性,而不是把清理留给事后。
树查询不能假定固定深度关联本项背景,实质主要讲解在对应位置。
删节点前先定子树语义关联本项背景,实质主要讲解在对应位置。
3.4 合理使用反模式
若应用只关心直接父子、层级确实有限,邻接表是简单合理的选择;递归CTE还可在数据库内沿父边扩展。书中“SQLite和MySQL暂不支持”的判断属于出版时状态,现有文档已给出递归CTE能力。
递归CTE沿父边扩展
递归公共表表达式把查询分为起始集合和递归扩展:先找到根或指定起点,再把当前节点与其子节点连接,持续产生下一层。它使邻接表不必预写固定次数的连接。仍须控制循环和资源消耗,并核对数据库语法;MySQL 8.0及SQLite官方文档均说明递归CTE,原书不支持清单已经过时。
递归CTE沿父边扩展保存独立卡片。
原书依据 · 1 处
树设计以实际需求为准
设备关系若实际只有两层,为假想的任意深度同时重做数据库、界面和报表,会增加无用复杂度。先确认真实层级与操作,再选择最低足够复杂的表示。邻接表直接父子操作简单,支持递归查询时也能处理更深结构;不要把“可能扩展”直接当成所有复杂模型的理由。
树设计以实际需求为准保存独立卡片。
原书依据 · 1 处
3.5 解决方案:使用其他树模型
路径枚举、嵌套集与闭包表通过预存路径、区间或可达关系,换取某些查询更直接,但各自增加维护成本。下面按相同的评论需求比较它们,而不是寻找无条件最优模型。
3.5.1 路径枚举
路径枚举在每个节点保存从根到自己的ID序列,例如1/4/6/7/。以路径前缀匹配查后代,反向前缀关系查祖先;插叶可复制父路径再追加新ID。它把遍历成本换成了字符串维护成本,路径格式、节点存在性和长度上限不再由普通外键自动保证。
路径枚举保存完整祖先链
路径枚举把根到当前节点的序列保存在路径字段,例如1/4/6/7/。已知节点的后代具有相同路径前缀;祖先的路径则是当前路径的前缀,结果通常包含节点自身,若不需要须显式排除。用分隔符保留完整ID边界,不能把前缀1误当作ID10。
路径枚举保存完整祖先链保存独立卡片。
原书依据 · 1 处
路径枚举的维护边界
路径枚举新增叶子可复制父路径并追加新ID,但应用须保证整条路径格式和祖先存在性;移动子树还会牵动相关后代路径。VARCHAR长度仍约束最大可存路径。节点ID位数不固定时,字符串总长度不等于树深度,应按路径段数理解层级,不能照搬原文的长度捷径。
路径枚举的维护边界保存独立卡片。
原书依据 · 1 处
3.5.2 嵌套集
嵌套集为每个节点保存深度优先遍历进入和离开时的左右界。后代区间被祖先区间包含,因而子树查询可转为范围判断;这些边界数不是comment_id。查询便利的代价是插入、移动常需给多个节点重新编号,直接父子关系也要额外筛选。
嵌套集把子树编码为区间
嵌套集按深度优先遍历为节点分配左界和右界:进入时记左界,退出时记右界。祖先区间包含后代区间,因此范围比较可找整棵子树或所有祖先。示例根区间为1至14,节点4为6至13。边界与节点ID无关;原文若称“后代ID在区间内”,应理解为边界值。
嵌套集把子树编码为区间保存独立卡片。
原书依据 · 1 处
嵌套集的写入代价
插入节点需给左右界腾出位置,可能更新大量节点;移动子树还须保持嵌套关系。它更适合子树读取重要、结构变化较少的场景,不能仅凭范围查询简单就认定总体最快。所有重新编号操作应形成一致的结构变更,原文SQL只作思路示例。
嵌套集的写入代价保存独立卡片。
原书依据 · 1 处
嵌套集删除可提升后代
若只删除嵌套集中的一个中间节点,其他区间不变,则它的后代仍被更外层祖先包含,结构含义相当于少了一层。边界不连续本身不破坏包含关系。计算深度时要先约定是否把自身算进去:包含自身的祖先数与从根到节点的边数相差一。
嵌套集删除可提升后代保存独立卡片。
原书依据 · 1 处
嵌套集如何找直接父亲
直接父节点必须严格包含当前节点的区间,且二者之间不存在另一个严格包含当前节点的区间。不能只找任意祖先,也不能把节点自身当候选。原文示例的宽松包含条件会纳入自身,且混用Comment/Comments,应保留关系判据并重新核对实际SQL。
嵌套集如何找直接父亲保存独立卡片。
原书依据 · 1 处
3.5.3 闭包表
闭包表把节点与关系分开:Comments保存节点,TreePaths保存每一对祖先和后代,包含自身到自身。查询变成按一端查另一端,维护时则必须同时增删所有受影响路径。可增加路径长度区分直接边与间接可达关系。
闭包表保存全部可达关系
闭包表为每对祖先和后代保存一行,另含每个节点到自身的一行。节点数据与层级关系分离,按祖先查后代或按后代查祖先都可直接连接。它用额外关系行换查询便利;普通外键只保证两端节点存在,不会自动维护全部传递路径。
结构图:
erDiagram
Comments ||--o{ TreePaths : 祖先端
Comments ||--o{ TreePaths : 后代端
TreePaths {
bigint ancestor PK,FK
bigint descendant PK,FK
}
两个外键指向同一节点表的不同角色;自引用行表达零步路径。
闭包表保存全部可达关系保存独立卡片。
原书依据 · 1 处
闭包表插叶要继承祖先
向父节点下新增叶子时,建立新节点到自身的路径,再为父节点的每个祖先建立通往新节点的路径,父节点自身也包含在内。否则直接父边虽然存在,祖先查询仍会漏掉新节点。删除叶子要删除指向它的路径;是否删除节点数据是另一个业务动作。
闭包表插叶要继承祖先保存独立卡片。
原书依据 · 1 处
移动子树保留内部闭包
移动闭包表子树时,先删除旧外部祖先到子树各节点的路径,保留子树内部路径及自引用;再把新父节点及其祖先,与子树根及全部后代做组合,补齐新路径。移动前须保证新父不在该子树内,整组变更保持一致,不能只换一条直接边。
结构图:
flowchart TD
A[确定移动子树] --> B[删除旧外部路径]
B --> C[保留子树内部闭包]
C --> D[新父祖先乘子树后代]
D --> E[补齐新外部路径]
此图表达原文路径集合变换,不声称已经执行。
移动子树保留内部闭包保存独立卡片。
原书依据 · 1 处
路径长度区分直接关系
闭包表加入路径长度后,自身到自身为0,直接父子为1,更深关系逐层增加。查询直接子节点可同时按祖先和长度1筛选,无须从所有可达关系反推最近一层。插入和移动关系时也必须同步维护长度,不能只更新两个端点。
路径长度区分直接关系保存独立卡片。
原书依据 · 1 处
3.5.4 你该使用哪种设计
树模型没有统一赢家。邻接表少冗余、直接父子操作自然,递归能力改善深层读取;路径枚举便于按路径读但格式维护弱;嵌套集便于区间查子树却使写入复杂;闭包表增加关系存储,换取多层可达查询和关系维护的直接表达。
按树操作选择存储模型
选择树模型应先排序最常见操作,而不是只比较表数量。
| 模型 | 擅长 | 主要代价 |
|---|---|---|
| 邻接表 | 直接父子、新增、改父边 | 多层读取需递归 |
| 路径枚举 | 按路径查祖先/子树 | 字符串有效性与更新 |
| 嵌套集 | 子树范围查询 | 编号维护和直接父查询 |
| 闭包表 | 按可达关系查祖先/后代 | 额外路径行及一致维护 |
图3-5的简单/困难是相对比较,不是跨引擎性能实测。
按树操作选择存储模型保存独立卡片。
原书依据 · 1 处
第4章 需要ID
主键负责唯一识别,代理键只是实现这种职责的一种手段。固定添加名为id的列不自动消除业务重复,也不能使组合关系失去意义。命名、组合键、序列并发和业务唯一性应分别判断,先问身份是什么,再决定怎样生成和引用。
ArticleTags虽有自增id主键,同一文章和标签仍能出现多行,导致计数从实际五篇变成七条关联。行标识唯一不代表业务关系唯一;需要约束的是文章与标签这一对。
代理主键不防业务重复
交叉表每行都有不同自增ID,仍可能重复保存同一篇文章与同一标签,聚合就把重复关系算多次。主键只约束被选中的键,不能自动推断业务上的唯一组合。应对文章和标签的组合声明唯一性,或直接以该组合作主键;保留代理键也不免除业务约束。
代理主键不防业务重复保存独立卡片。
4.1 目标:建立主键规范
主键是用来唯一定位一行的约束,可供外键引用;代理键则是为标识记录而新增、没有业务含义的值。两者并非同义词。自动生成值的具体机制随产品而异,也不表示跨表跨库的全局唯一。
主键是一种唯一约束
主键指定一列或多列,用唯一且非空的键值定位一行,也可作为其他表引用的目标。它是约束,不是一种整数数据类型。主键唯一只针对所选键;如果业务还要求别的组合唯一,需要另行表达,不能只靠定期扫描来防止新重复。
主键是一种唯一约束保存独立卡片。
代理键不携带业务含义
代理键是额外增加、用于标识记录而不表达业务属性的键,例如数据库生成的整数。它让业务字段可以变化或重复,但并不代替业务唯一约束。自增、序列等是生成机制,不是“主键”的定义,也不能一概称为跨系统全局唯一。
代理键不携带业务含义保存独立卡片。
4.2 反模式:以不变应万变
若把“主键”固定理解为名叫id的自增整数,就会忽略自然键与复合键,以及真正需要约束的业务关系。约定可以减少配置,但不能替代模型判断。
代理键不携带业务含义关联本项背景,实质主要讲解在对应位置。
4.2.1 冗余键值
若已有稳定的业务标识且单独唯一,再加一个id可能只是增加第二套标识。但是否冗余还取决于引用成本和业务字段未来是否变化,不能只看两列目前都唯一。
评估自然键与代理键
已有唯一业务标识时,先评估它能否长期稳定、非空且便于引用。若适合作键,额外代理键可能冗余;若自然键过长、会变或可能允许重复,代理键可降低关联成本。两者取舍取决于业务及访问方式,不是“见到两列唯一就必须删一列”。
评估自然键与代理键保存独立卡片。
4.2.2 允许重复项
交叉表的组合必须唯一;只让自增id唯一会容许同一关系重复。保留id与否是另一项选择,不能用它替代组合约束。
代理主键不防业务重复关联本项背景,实质主要讲解在对应位置。
复合键标识一对关联关联本项背景,实质主要讲解在对应位置。
4.2.3 意义不明的关键字
连接多个都有id列的表时,结果按列名映射可能发生同名覆盖。使用表达实体含义的列名,或为查询输出显式起别名,可以让结果的身份清楚。
同名结果列可能互相覆盖
多个表都返回名为id的列时,按列名生成字典或关联数组的客户端可能覆盖同名项。用bug_id、account_id等明确名称,或在查询投影中指定不同别名,避免把两个实体的标识混同。具体覆盖或报错行为依驱动而异,不能仅凭SQL执行成功证明映射安全。
同名结果列可能互相覆盖保存独立卡片。
4.2.4 使用USING 关键字
USING用同名连接列表达等值关联;ON可以明确写不同列名或更复杂条件。为了简写而让无关实体都叫id会模糊含义,简洁应以语义正确为前提。
同名连接列可使用USING
连接两表时,USING可指定双方同名的关联列,表示这些列之间的等值匹配;列名不同时用ON明确比较。命名应表达同一键的语义,不能把两个无关的id仅因同名而连接。USING是可读性选择,不是所有数据库或所有连接条件下都可替代ON。
同名连接列可使用USING保存独立卡片。
原书依据 · 1 处
4.2.5 使用组合键之难
复合键比较和外键引用要包含全部组成列。另一个常见错误是用当前MAX加一生成ID,并发客户端可能读到相同最大值;应使用产品提供的原子生成机制,并在同一连接语境取得本次值。
引用复合键要覆盖全部列
复合键用多列共同定位记录,比较键或建立外键时也要包含对应的整组列。省掉一列会改变身份范围,不能为了少写字而假装一个分量已足够。它适合本身由多项共同定义的关系;额外代理键是否方便,仍需另行权衡。
引用复合键要覆盖全部列保存独立卡片。
MAX加一会并发撞号
两个客户端同时读取当前最大ID,再各自加一,会得到相同新ID。问题是读取与分配不是一个原子动作;仅锁住已有行也未必排除新插入。优先使用数据库支持的序列或自增机制,而不是自行推算下一个值;具体唯一范围、重置和循环行为须按产品配置核对。
MAX加一会并发撞号保存独立卡片。
原书依据 · 1 处
生成ID应取本次会话结果
插入记录后,使用数据库为当前连接或执行作用域提供的生成值接口,不要再查全表最大ID。其他客户端可以继续插入,而不会让本次读取误取别人的值。各产品接口的会话、作用域及序列规则不同,须结合驱动和连接池保证使用同一上下文,不能把名称相近当作语义完全一样。
生成ID应取本次会话结果保存独立卡片。
原书依据 · 1 处
4.3 如何识别反模式
没有单独id列不等于没有主键,主键也不要求与规范化的所有设计一起引入代理ID。检查应围绕唯一性与可定位性,而不是围绕列名是否符合某种习惯。
主键是一种唯一约束关联本项背景,实质主要讲解在对应位置。
4.4 合理使用反模式
统一id可能是框架惯例,也可能降低过长自然键的索引及引用成本。使用代理键本身合理,关键在于这种便利是否与业务约束和清楚的命名兼容。
评估自然键与代理键关联本项背景,实质主要讲解在对应位置。
4.5.1 直截了当地描述设计
主键名称应表达实体含义,外键通常沿用引用的键名;若表示报告人等角色,用reported_by这样的角色名更清楚。命名一致与角色清晰需共同考虑。
键命名应区分实体与角色
键名应让读者看出标识的是哪类实体。一般关联可沿用同一个键名,多个不同角色都指向账号时,则用reported_by等角色名区分。命名规则服务于理解关系,不能为了机械统一而抹去业务语义;查询输出仍要避免同名列歧义。
键命名应区分实体与角色保存独立卡片。
4.5.2 打破传统
有些框架允许配置主键名称,因而可以在保留框架能力时使用更有意义的键名。原文Rails API属于历史示例;这里保留可配置惯例这一原则,不把旧方法名当当前接口。
键命名应区分实体与角色关联本项背景,实质主要讲解在对应位置。
4.5.3 拥抱自然键和组合键
自然键要在业务上唯一、非空且足够稳定;若未来允许重复,就不能再承担原来的身份约束。交叉表常由两端键共同标识关系,引用它时也必须引用整组键。
评估自然键与代理键关联本项背景,实质主要讲解在对应位置。
复合键标识一对关联关联本项背景,实质主要讲解在对应位置。
引用复合键要覆盖全部列关联本项背景,实质主要讲解在对应位置。
第5章 不用钥匙的入口
外键把引用存在性放进所有写入口共同经过的约束层。应用事前查询可能在并发或新入口下失效,事后扫描又只能晚发现。级联、限制和缺省动作表达不同生命周期,但外键本身不能防止预约时间重叠等更强业务冲突,启用与引擎支持也必须真实核验。
设备预约系统因为引用关系不能保证有效,报表产生不一致,并通过持续巡检脚本补漏。问题在于错误先进入数据库,随后每条关系都要付出发现和修复成本。外键有助于防止无效引用,但不自动证明预约时段不会冲突。
事后巡检不能代替约束
引用错误先进入数据库,再靠周期脚本找孤立记录,会让系统在巡检间隔内使用不一致数据;关系越多,脚本与修复成本越高。应在写入处维护可表达的约束。设备预约案例说明巡检负担,外键只保证引用存在,并不能单独防止同一设备的时间重叠预约。
事后巡检不能代替约束保存独立卡片。
5.1 目标:简化数据库架构
引用完整性要求非空外键值能在被引用的键中找到。单列或多列关系都可表达;允许为空及复合键空值行为需要另按约束规则理解。省略外键的动机并不消除关系本身。
引用完整性限制悬空关系
外键把一列或一组列关联到被引用表的主键或其他受支持的唯一键,要求有效引用能找到对应记录。它约束关系存在性,不能检查所有业务规则。空值与复合外键的匹配规则依约束及产品而定,不能简单把每个NULL都判为非法引用。
引用完整性限制悬空关系保存独立卡片。
原书依据 · 1 处
5.2 反模式:无视约束
不声明外键会把完整性责任转给所有写入代码。架构表面更少约束,不代表整个系统更简单,因为每条更新路径都要重复检查与维护。
事后巡检不能代替约束关联本项背景,实质主要讲解在对应位置。
5.2.1 假设无瑕代码
先检查没有引用再删除账号,检查与删除之间仍可能插入新的缺陷,产生指向不存在账号的记录。正确性问题来自两步之间的并发窗口,不能仅靠“每个函数都先查过”证明安全。
先查再删仍会悬空引用
一个请求检查账号已无缺陷引用,另一请求随后插入引用该账号的缺陷,前者再删除账号,就产生悬空引用。检查和动作之间的状态会变化;应用侧要用正确的事务与并发控制覆盖全部写入路径,或由可用外键在数据库内强制引用规则。不能把一次SELECT当作未来一直成立的保证。
先查再删仍会悬空引用保存独立卡片。
原书依据 · 1 处
5.2.2 检查错误
查孤立行可以发现既成错误,但无法总是恢复真实业务事实。无效状态也许能改成默认值,丢失报告人却不能随便选一个账号补上。检测与修复是两项不同能力。
发现孤立记录不等于能修复
巡检能找到引用不到父记录的行,但未必能知道正确值。无效状态可能有业务默认值,丢失的报告人身份则无法由结构自动推断。修复必须有业务依据;任意填默认值可能把可见错误变成更隐蔽的错误事实。优先在写入时防止能由约束表达的问题。
发现孤立记录不等于能修复保存独立卡片。
原书依据 · 1 处
5.2.3 “那不是我的错!”
即使应用的公共函数都正确,临时SQL、脚本和后续新增路径仍可能绕开应用检查。数据库是多入口共享边界,约束可让入口遵循同一条关系规则。
约束覆盖多条写入路径
数据可能由应用、脚本、管理工具或新功能写入。只在一个公共函数中验证关系,无法保证其他入口永远遵守同一规则。把可表达的引用约束放到数据库边界,可在正常启用约束的写入路径统一检查;不应把它夸大为特权用户绝不可能关闭或绕开约束。
约束覆盖多条写入路径保存独立卡片。
原书依据 · 1 处
5.2.4 进退维谷
父键修改若分别改父行和子行,可能在中间状态违反即时约束。级联动作把这类关联修改交给数据库,但删除被引用父行时究竟限制、删除子行还是设置其他值,仍需业务决定。
级联动作处理关联键变更
父键被子表引用时,逐条修改可能遭遇中间状态的约束冲突。受支持的级联更新可把父键变化同步到引用它的子行;删除动作则可按业务选择限制、级联删除或设置空值/默认值。产品支持范围与列约束必须核对,级联不能代替对业务后果的判断。
级联动作处理关联键变更保存独立卡片。
5.3 如何识别反模式
反复编写父记录存在性查询或孤立行巡检,是应重新评估引用约束的线索。性能理由也要比较完整方案,包括应用查询和维护成本,而不是只计算约束本身。
事后巡检不能代替约束关联本项背景,实质主要讲解在对应位置。
5.4 合理使用反模式
若存储引擎确实不支持外键,只能使用其他一致性与巡检措施,并承担它们的限制;若只是当前模型无法表达,首先应判断是否建模本身有问题。
不能用外键时保留完整性责任
引擎不支持外键时,应用仍须维护引用关系,并用检测与修复流程控制遗漏。若模型过于灵活而无法表达外键,也要检查是否牺牲了关键类型或身份信息。不能因为没有声明约束的能力,就默认错误引用没有成本;应记录验证范围与尚存风险。
不能用外键时保留完整性责任保存独立卡片。
原书依据 · 1 处
5.5 解决方案:声明约束
防差错把阻止错误的规则放在错误进入系统的关口。外键是这种思路在数据库关系上的应用:让写入必须满足存在性条件,减少重复检查和事后修复。约束必须实际启用才有这种作用。
把防差错放在数据入口
防差错把规则设计到操作入口,使错误动作被阻止、纠正或及时暴露,而不只依赖事后发现。外键把引用存在性放到数据库写入边界,是这一思想的实例。它减少各入口重复实现检查的机会,但只覆盖声明并启用的规则,不保证全部业务逻辑正确。
把防差错放在数据入口保存独立卡片。
原书依据 · 1 处
5.5.1 支持同步修改
ON UPDATE与ON DELETE可分别规定父键变更和父行删除时的动作。RESTRICT拒绝仍有引用的删除;SET DEFAULT还要求产品支持且默认值满足外键。书中带MySQL类型的例子使用SET DEFAULT,不宜直接交给InnoDB运行。
级联动作处理关联键变更关联本项背景,实质主要讲解在对应位置。
5.5.2 系统开销过度?不见得
比较约束的开销时,也要计入手工检查、锁、巡检和修复。外键并非免费,也不保证所有工作负载更快;它以集中维护关系规则的方式减少了其他重复工作。
评估完整性成本要看全链路
评价外键开销应把替代方案一起计算:额外存在性查询、并发控制、孤立行巡检及修复都要资源。外键可减少重复代码并集中维护关系,但性能需结合索引、写入量和产品实现验证。不能只凭“外键有成本”就省略,也不能反过来保证它对任何场景都更快。
评估完整性成本要看全链路保存独立卡片。
原书依据 · 1 处
第6章 实体—属性—值
EAV把属性名和值改成数据,从而减轻不断增加列的压力,却同时削弱类型、必填、引用和查询的直接表达。单表继承、具体表继承和类表继承分别以列、复制和连接换取结构化约束;半结构化文档又有另一组权衡。选择取决于哪些变化真实存在,而非抽象上的无限灵活。
按日期分组要求记录中有语义相同、表示可比较的日期值。EAV把列名也变成数据后,同一属性可能用不同名字和格式出现,原本简单的报表必须先识别并解释属性。
动态属性先要统一语义
按日期统计记录,前提是每条记录的日期有一致名称和可比较表示。把属性名和值都动态化后,date_reported和report_date可能指同一含义,字符串格式又可能不同,报表必须先恢复统一语义。自由定义属性并不免除数据建模,只是把建模责任移到别处。
动态属性先要统一语义保存独立卡片。
6.1 目标:支持可变的属性
对象子类型既共享公共属性,也有各自独有属性。Bug与FeatureRequest都属于Issue,但前者有严重程度、受影响版本,后者有赞助商。存储设计需要同时表达共同身份和子类型差异,而不是把所有差异都化成无类型字符串。
子类型有公共与专有属性
子类型既是自身类型的实例,也可按父类型的公共属性处理。Issue抽取报告日期、报告人、优先级和状态;Bug再增加严重程度和受影响版本,FeatureRequest增加赞助商。关系存储需要决定公共字段与专有字段放在哪里,这与是否采用EAV是不同层次的问题。
结构图: 类图
classDiagram
class Issue {
+Date_reported
+Reporter
+Priority
+Status
}
class Bug {
+Severity
+Version_affected
}
class FeatureRequest {
+Sponsor
}
Issue <|-- Bug
Issue <|-- FeatureRequest
空心三角表示原文模型中的继承,属性名沿用图6-1。
子类型有公共与专有属性保存独立卡片。
原书依据 · 1 处
6.2 反模式:使用泛型属性表
EAV为每个实体属性保存一行:实体ID、属性名、属性值。增加属性无需新增列,但数据库不再能直接把每个业务属性当成固定列施加原有规则。少列的表结构和简单的数据使用并不是一回事。
EAV把属性列变为数据行
EAV用实体ID、属性名、属性值三项记录一个属性,通常以实体ID和属性名的组合标识它。新增属性只加行,无需改表列;代价是类型、必填、引用和报表重组都要重新处理。它并非真正没有模式,属性含义与规则仍要由元数据或应用维护。
EAV把属性列变为数据行保存独立卡片。
6.2.1 查询属性
查询某属性要先筛选属性名,再把attr_value解释为该业务值;它不再是直接读取固定列。动态灵活性因此进入每条查询的表达与维护。
动态属性先要统一语义关联本项背景,实质主要讲解在对应位置。
6.2.2 支持数据完整性
下面从必填、数据类型、引用和命名四个方面检查EAV的数据完整性代价。
6.2.3 无法声明强制属性
把attr_value设为非空,只能约束已存在的属性行,不能保证每个实体都存在date_reported这一行。必填属性从列级约束变成了跨行存在性规则。
非空值不保证属性行存在
EAV把日期保存为一行后,即使attr_value非空,也无法保证每个实体都有日期属性;缺失整行与行内NULL是两种情况。若日期业务上必填,需要额外跨行规则、触发器或应用校验,或恢复固定日期列。原文“SQL没有任何约束”过于绝对,核心是普通列级NOT NULL不能表达该规则。
非空值不保证属性行存在保存独立卡片。
原书依据 · 1 处
6.2.4 无法使用SQL的数据类型
同一个字符值列既存日期也存数字时,数据库只按字符列验证。为每种类型增加值列虽恢复部分类型约束,却增加取值和“哪一列生效”的规则。
通用值列丢失属性类型
EAV的通用字符值列允许日期属性写入普通文字,因为数据库不知道这一行的业务类型。改成多种类型值列可恢复部分类型检查,但还要保证每行只选择正确的一种,并在查询时恢复统一值。动态属性的灵活性不是免费的类型安全。
通用值列丢失属性类型保存独立卡片。
原书依据 · 1 处
6.2.5 无法确保引用完整性
外键约束作用于指定列上的所有适用行,不能随attr_name改变目标表。给通用attr_value加状态外键,会错误地要求所有属性值都来自状态表。
通用值列不能逐属性换外键
EAV若只希望status属性引用BugStatus,不能简单给通用attr_value列加状态外键,因为该约束也会作用于日期、描述等其他属性。外键目标在定义时确定,不随行的属性名切换。需要单独建模受约束属性,或承担额外验证机制,而不是以为一个通用外键能识别业务类型。
通用值列不能逐属性换外键保存独立卡片。
原书依据 · 1 处
6.2.6 无法配置属性名
属性名若由每条记录自由决定,同义词和重复命名会让聚合遗漏或重复。属性字典能统一名称,但也把模式管理重新引入系统,不能同时宣称完全自由且无需治理。
动态属性也需要名称治理
同一日期用不同属性名保存时,查询必须知道全部别名,否则会漏数;同一实体出现两个同义属性还可能重复或冲突。属性字典加外键可统一名称,但新属性也必须先登记。动态扩展与语义一致需要明确治理流程,不是取消表列后自然得到。
动态属性也需要名称治理保存独立卡片。
原书依据 · 1 处
6.2.7 重组列
要把EAV还原为一行,常要按每个已知属性分别连接;缺失属性需保留实体时要外连接。属性越多,查询更长且更依赖元数据;不能简单把这个现象断言成开销必定指数增长。
重组EAV要保留缺失属性
从多行EAV属性重组实体行时,每个属性分别筛选并连接;若某属性可能缺失,要用能保留实体的外连接,否则缺一项可能整行消失。此法还要求查询预先知道属性列表。它增加连接和优化复杂度,但实际成本取决于唯一约束、索引及计划,不能仅由连接数量推断指数级增长。
重组EAV要保留缺失属性保存独立卡片。
6.3 如何识别反模式
声称运行时任意扩展且不改元数据、查询需要接近连接数上限、普通报表难以实现,都是应检查动态属性代价的信号。关键是确认必要灵活性究竟在哪些对象上。
EAV把属性列变为数据行关联本项背景,实质主要讲解在对应位置。
重组EAV要保留缺失属性关联本项背景,实质主要讲解在对应位置。
6.4 合理使用反模式
只有局部对象确需动态属性时,可把灵活性限制在局部,保留其他关系表的规则。换成非关系存储也不会自动解决属性含义和查询组织;原文列出的项目是出版时可比较的不同存储路径。
Redis的数据结构存储关联本项背景,实质主要讲解在对应位置。
动态属性灵活性应局部化
若只有少数对象需要动态属性,把灵活结构限制在那里,其余数据仍用固定列和关系约束。采用EAV或非关系存储都要维护属性语义、验证和查询组织。选择时核对实际需要的动态程度,不能把放弃全部约束当成扩展性的必要条件。
动态属性灵活性应局部化保存独立卡片。
原书依据 · 1 处
BerkeleyDB键值存储
Berkeley DB在这里被作为可整合到程序中的键值存储候选。它提供的切入点是按键组织数据,而不是让关系表承载任意属性名;这并不自动补回SQL的关系约束和报表表达能力。此卡说明原书的选型线索,不代表已完成该产品的当代版本选型。
BerkeleyDB键值存储保存独立卡片。
原书依据 · 1 处
Cassandra列存储
Cassandra在原书中作为分布式、面向列的数据存储候选出现,用来说明可变属性未必必须由EAV关系表承载。它与键值、文档路线不同,仍应核对实际访问模型及约束需求。这里保留的是出版时对替代技术的定位,不据此推导当前版本特性。
Cassandra列存储保存独立卡片。
原书依据 · 1 处
CouchDB以文档组织属性
CouchDB在原书中被介绍为以JSON表达数据的文档数据库。把一组相关属性放在文档里,可避免为每个属性建立EAV行;相应地仍需明确文档的字段语义与查询方式,不能因格式灵活就认为无需模式治理。
CouchDB以文档组织属性保存独立卡片。
原书依据 · 1 处
Hadoop与HBase线索
原书把Hadoop与HBase列为分布式大数据处理和存储的阅读线索,借此提示关系数据库外还有其他组织数据的途径。这里不把两者等同于同一种数据库,也不把原文“组装DBMS”的简述当作可直接部署的技术方案;是否适合动态属性仍需独立核验。
Hadoop与HBase线索保存独立卡片。
原书依据 · 1 处
MongoDB的文档存储线索
MongoDB在此作为文档型存储的另一候选,与CouchDB并列,说明动态属性可以按文档而非逐属性行来组织。原书只给出类别定位,不能据此判断事务、索引和一致性能力;实际采用须继续查阅相应版本文档。
MongoDB的文档存储线索保存独立卡片。
原书依据 · 1 处
TokyoCabinet键值
Tokyo Cabinet在原书中作为键值存储路线的候选,联系DBM一类接口与已有键值系统。它的用途是提示可采用不同于关系表的访问模型,并不证明任意报表会因此更简单。项目现状和API未在此核验,不据这段简述提出生产采用建议。
TokyoCabinet键值保存独立卡片。
原书依据 · 1 处
6.5 解决方案:模型化子类型
子类型数目有限且属性已知时,可以先考虑固定列的几类继承映射。选择取决于常见查询是跨全部类型,还是只访问某一种类型;灵活性需求应先量化。
6.5.1 单表继承
单表继承把公共属性与所有子类型特有属性放进同一张表,以类型列区分每行。跨类型查询简单,但不适用字段会产生空值,并需要额外规则说明哪些属性属于哪些类型。
单表继承以类型列区分
单表继承把全部子类型放在同一表,用issue_type区分,公共字段和各类型专有字段同时存在。跨类型查询方便,适合类型和专有字段较少的场景;不适用的列为空,类型与字段有效性的对应规则仍须维护。并非有了类型列就自动阻止错误组合,新增类型也可能要改表。
单表继承以类型列区分保存独立卡片。
原书依据 · 1 处
6.5.2 实体表继承
实体表继承为每个具体子类型保存完整表,公共列在各表重复,专有列只出现在本类型。单类型访问清楚,跨类型查询与公共属性变更则要协调多张表。
实体表继承复制公共字段
实体表继承为每个具体类型建立独立表,同时包含公共和专有字段。它避免一行携带其他类型无关属性,适合很少跨类型查询的场景;公共字段变化要同步多表。跨类型合并必须明确选择兼容的公共列,原文两边直接SELECT星号会因列数/含义不同而失败,不能照搬。
实体表继承复制公共字段保存独立卡片。
原书依据 · 1 处
6.5.3 类表继承
类表继承把公共属性保存在Issues,Bugs和FeatureRequests只保存专有属性,并以同一issue_id作为主键和外键连接基表。可先统一查询公共属性,再按需读子类型;基表行是否必须且只能属于一个子类型,还需要额外规则。
类表继承共享公共基表
类表继承用基表保存公共字段,子类型表以同一键作为主键和外键保存专有字段。一个子类型行最多对应一个基表行,基表也最多有该子类型的一行;这不自动保证每个基表行必须出现子行,或不能同时属于两个子类型。适合经常统一查询公共字段、按需连接专有字段的场景。
结构图:
erDiagram
Issues ||--o| Bugs : 缺陷扩展
Issues ||--o| FeatureRequests : 特性扩展
Issues {
bigint issue_id PK
}
Bugs {
bigint issue_id PK,FK
}
FeatureRequests {
bigint issue_id PK,FK
}
这是关系表映射,不是程序类图;可选子行体现DDL实际保证的范围。
类表继承共享公共基表保存独立卡片。
原书依据 · 1 处
6.5.4 半结构化数据模型
Serialized LOB把动态属性序列化为XML或JSON等整体值,与固定的公共列分开。原书讨论的是把它作为不透明文本/大对象的成本;具备原生JSON能力的数据库可能查询内部属性,但业务模式与约束仍须设计。
序列化属性保留灵活边界
把公共字段保留为固定列,把经常变化的额外属性序列化到一个字段,是Serialized LOB模式。新增动态字段无需改表,若作为不透明大对象使用,则查询和验证要在解码后处理。不能把原书“SQL无法读内部属性”扩大成当前所有产品的限制;MySQL 8.0等原生JSON能力能访问元素,仍不自动补齐业务约束。
序列化属性保留灵活边界保存独立卡片。
原书依据 · 1 处
6.5.5 后处理
若无法迁移EAV,可直接读取实体的全部属性行,在应用中按实体ID组装对象,而不是为每个属性硬写连接。这样保留存储本来的粒度,但类型、必填及语义验证责任仍然存在。
遗留EAV按属性行后处理
无法改变遗留EAV时,先按实体读取属性名和值的行,再由应用按实体ID聚合并设置属性。它不要求查询预知全部属性,比逐属性自连接更自然。后处理只是适配存储形态,不能自动补齐类型、必填、引用或命名一致性;这些仍要有显式校验。
遗留EAV按属性行后处理保存独立卡片。
原书依据 · 1 处
第7章 多态关联
一列ID加类型名能告诉应用去哪里查,却不能让普通外键按行选择目标表。于是身份存在性和查询被推给应用。分别关联、专用交叉表或公共父实体提供不同替代方案,每个方案仍需说明互斥、完整性和实体寿命,不能仅因框架支持就认定约束已齐备。
评论既可以关联Bug,也可以关联FeatureRequest,但一个普通外键的目标表必须明确。把表名留到每行数据中决定,并不能让SQL语法自动按行切换被连接的表。需要重新表达共同身份或关联关系。
外键目标不能按行切换
若评论可关联不同类型的事件,不能把普通外键定义成“引用Bugs或FeatureRequests”,也不能用某行保存的表名直接作为该行JOIN目标。表及约束目标属于语句或模式结构,须预先确定。可改用共同事件基表,或为不同目标建立明确关联表,以恢复可检查的关系。
外键目标不能按行切换保存独立卡片。
7.1 目标:引用多个父表
模型想表达的是排他关联:每条评论对应一个Bug或一个特性需求。图7-2的弧线表示二选一,这一业务规则比“两个外键都存在”更强。
外键目标不能按行切换关联本项背景,实质主要讲解在对应位置。
7.2 反模式:使用双用途外键
多态关联以一种共同接口表示不同目标类型,但在数据库中把类型名与ID一起存储时,普通外键无法直接表达其存在性规则。
7.2.1 定义多态关联
多态关联保存issue_type和issue_id,应用根据类型决定ID属于哪张表。它把元数据名字存成字符串,失去了普通外键对具体目标的存在性约束,与EAV把属性名数据化有相似成本。
多态关联需要额外完整性
多态关联把目标类型名与目标ID一起保存,由应用解释应该查哪张表。普通外键只能固定指向一个目标,因而不能直接验证这种二元解释;类型字符串也可能拼错或指向不存在的表。框架可以封装访问逻辑,但数据库并不会因此自动得到等价引用约束。
多态关联需要额外完整性保存独立卡片。
元数据变数据转移了规则
把列名存进attr_name,或把目标表名存进issue_type,就把原来由数据库模式表达的结构移进普通字符串。数据库仍只看到值,应用必须解释名字、验证合法性并组织查询。灵活性来自规则位置的转移,而不是规则被取消;应明确谁维护这些额外语义。
元数据变数据转移了规则保存独立卡片。
原书依据 · 1 处
7.2.2 使用多态关联进行查询
不同父表可能有同样的ID,因此连接时必须同时核对类型与ID。读一条评论的目标时,仍需事先列出候选表,并用外连接保留不匹配一侧;类型标记不能让查询自动改表。
类型标记必须参与连接
不同目标表可以拥有相同ID。若只按issue_id连接,多态评论可能匹配错误类型;连接条件必须包含类型标记。要返回任意一种目标的详情,仍须显式列出候选表,外连接让不匹配一侧为空。类型标记解决解释歧义,不能补上数据库外键完整性。
类型标记必须参与连接保存独立卡片。
原书依据 · 1 处
7.2.3 非面向对象范例
多态关联不只发生于对象继承。地址若可能属于用户或订单,类型加ID同样缺少固定外键;再把各父表专有用途塞进地址行,会让无关职责和稀疏字段持续增长。
通用地址表混入多种角色
地址可能服务用户或订单时,以parent和parent_id解释归属,仍面临多态外键的验证问题;再添加users_usage、orders_usage会把不同关系的角色混进同一行。应明确“地址内容”和“某对象怎样使用地址”两类事实,后者可由独立关系表达,避免类型越多字段越散。
通用地址表混入多种角色保存独立卡片。
原书依据 · 1 处
7.3 如何识别反模式
无限关联资源、无法声明外键、必须由entity_type解释其他字段,都是检查多态关联的线索。Rails可封装这种访问,但框架支持与数据库约束是两项不同能力。
多态关联需要额外完整性关联本项背景,实质主要讲解在对应位置。
7.4 合理使用反模式
框架可以集中封装多态关联的查询和维护逻辑,降低应用重复实现的成本,但并不证明所有并发和外部入口都安全。选择这种方案时仍需知道完整性由谁保证。
框架封装不等于引用约束
使用成熟框架可减少多态关联的重复访问代码,但不能据此相信它不会出错或能约束所有写入入口。仍要确认删除、并发更新和外部脚本如何维护目标存在性及类型一致。框架封装是一种工程控制,不能冒充数据库固定外键的保证。
框架封装不等于引用约束保存独立卡片。
原书依据 · 1 处
7.5 解决方案:让关系变得简单
替代设计把“引用哪一类对象”的信息重新放入明确的表关系,分别使用独立交叉表或共同基表,以恢复固定外键。还要检查排他性和存在性是否都被覆盖。
7.5.1 反向引用
反向引用的思路是不让一条评论的外键选择不同父表,而让每种明确目标通过独立关系引用评论。
7.5.2 创建交叉表
分别建立BugsComments和FeaturesComments,每表都有确定的事件外键与评论外键,便可验证两端存在。它移除了类型字符串,但默认多对多关系并未自动满足“每条评论只能属于一个目标”。
分表关联恢复确定外键
为Bug和FeatureRequest各建一张评论关联表,每表都以固定外键引用自己的事件表及Comments。类型信息由关系表本身表达,不再依赖字符串解释,数据库可以检查两端存在。交叉表默认可记录多对多;若评论只允许一个归属,还需进一步限制基数和跨表互斥。
结构图:
erDiagram
Bugs ||--o{ BugsComments : 事件
Comments ||--o{ BugsComments : 评论
FeatureRequests ||--o{ FeaturesComments : 事件
Comments ||--o{ FeaturesComments : 评论
这是默认交叉表的基数;单评论单目标需要额外约束。
分表关联恢复确定外键保存独立卡片。
7.5.3 设立交通灯
给每张关联表的comment_id加唯一约束,可让评论在同一种关系内最多出现一次;但它仍可能同时出现在两张表中。表内唯一不等于跨表二选一。
表内唯一不保证跨表互斥
分别让BugsComments.comment_id与FeaturesComments.comment_id唯一,只能保证一条评论在每张表内至多关联一个对象。它仍可能同时关联一个Bug和一个FeatureRequest。若业务要求严格二选一,必须另外维护跨表互斥,或改变模型让该条件能在同一结构中表达。
表内唯一不保证跨表互斥保存独立卡片。
原书依据 · 1 处
7.5.4 双向查找
可以从目标沿关联表找评论,也可从评论外连接不同关联路径找目标。后者仍要列出候选表,但连接关系已有可检查的外键,不再按字符串动态解释目标。
分表关联恢复确定外键关联本项背景,实质主要讲解在对应位置。
7.5.5 合并跑道
UNION合并不同类型的结果时,各分支要有相同数量、顺序和兼容类型的列,缺少的专有属性可填NULL。另一方式是在已保证单一匹配路径的外连接结果中,用COALESCE选择公共字段的非空值;两者都不自动建立排他规则。
联合结果须对齐列语义
将不同子类型结果联合成统一形状时,分支必须按同样顺序提供数量相同、类型兼容的列,缺少的专有字段可用NULL占位。还要对齐每列业务含义,不能仅凑齐数量。UNION的去重语义也不能替代业务上单一归属的约束。
联合结果须对齐列语义保存独立卡片。
COALESCE不负责选择目标
外连接不同子类型后,COALESCE可从公共字段候选中取第一个非空值,合并统一结果列。它只选择值,不判断哪个目标在业务上合法;若多条路径同时匹配,可能拼出混合信息。使用前应保证关系排他或明确优先规则,不能靠这个函数掩盖模型冲突。
COALESCE不负责选择目标保存独立卡片。
原书依据 · 1 处
7.5.6 创建共用的超级表
若Bug和FeatureRequest本来共享事件身份,可建立Issues统一分配键,子表用同一键扩展,Comments只引用Issues。这样每条评论有明确的共同目标,避免用类型字符串选择外键表。
共同基表统一可引用身份
不同子类型若确实共享一个概念身份,可用Issues基表统一保存该身份,子表主键兼外键引用它,评论只对Issues建立固定外键。这样无需类型字符串切换引用目标;仍要另外决定每个Issue是否必须且只能有一个子类型。不能只为省外键而把语义无关对象强行归成同一类型。
结构图:
erDiagram
Issues ||--o{ Comments : 评论目标
Issues ||--o| Bugs : 缺陷扩展
Issues ||--o| FeatureRequests : 特性扩展
固定外键保证身份存在;图未声称两个子类型已由DDL自动互斥。
共同基表统一可引用身份保存独立卡片。
原书依据 · 1 处
第8章 多列属性
多个列若只是同一属性的第1、第2、第3个位置,数量上限和查询分支会渗入所有操作。独立子表用行表达数量变化,随后可以选择聚合展示。若列代表不同角色,如开始与结束时间,则不是本章所批评的任意位置重复。
联系人可能有多个电话,号码种类也会变化。若为每个预想的类别加列,会出现大量稀疏字段,并不断追问“最多支持几个”。需要区分固定语义的角色与同一属性的可变多个值。
编号列把值集固定成槽位
一个属性有不定数量的值时,用phone1、phone2或tag1、tag2等列分配槽位,会把数量上限写进模式。新增值可能迫使改表和改查询,而空槽位又要特殊处理。若各字段有稳定且不同的业务角色,分列可能合理;可变同义值集合则适合独立行表示。
编号列把值集固定成槽位保存独立卡片。
8.1 目标:存储多值属性
标签并不互斥,一个缺陷可以同时关联性能与打印等标签。这与逗号列表章的需求同源:需要保存多个同义值,而不是为了保留每格单值就必然增加多个同义列。
编号列把值集固定成槽位关联本项背景,实质主要讲解在对应位置。
8.2 反模式:创建多个列
tag1至tag3让每格只存一个标签,但标签可能放在任意槽位,未使用槽为空。下面各项操作必须反复跨槽位搜索,这就是形式上单值却仍没有正确表达集合。
编号列把值集固定成槽位关联本项背景,实质主要讲解在对应位置。
8.2.1 查询数据
查一个标签要搜索所有槽,查同时具有两个标签要把两组搜索再相与。AND优先于OR时必须正确加括号;IN可缩短表达,却不消除模型中同义多列。
多列标签放大查询条件
标签可能出现在任意tagN列,单标签查询必须覆盖每个槽;同时查两个标签,还要分别覆盖全部槽后再组合条件。正确括号能避免AND与OR优先级改变含义,IN也只能简写,不能解决增加新槽后所有查询都要更新的问题。
多列标签放大查询条件保存独立卡片。
8.2.2 添加及删除值
先读空槽再写标签有并发窗口:另一客户端可能已占用该槽,导致覆盖或冲突。逐列条件更新虽可减少窗口,仍有满槽不写入与复杂空值判断;把标签成行能直接操作目标关联。
先找空槽再写会覆盖
两个客户端都读到同一标签槽为空,再先后写入,后者可能覆盖前者。根因是“查空位”与“占空位”分离,读到的状态不保证动作时仍成立。可用正确的原子条件更新或并发控制保护动作,更自然的模型是每个标签独立成行,并用组合唯一性处理重复。
先找空槽再写会覆盖保存独立卡片。
原书依据 · 1 处
NULLIF将相等值变为空
NULLIF比较两个表达式,相等时返回NULL,否则返回第一个表达式的值。原文用它清除多个标签槽里等于目标标签的值。它只处理值比较,不会消除多列槽位模型,也不意味着任何含NULL的比较都为真;产品支持状态应按实际版本核对。
NULLIF将相等值变为空保存独立卡片。
原书依据 · 1 处
8.2.3 确保唯一性
同一个标签可以同时出现在tag2和tag3。每列自己的约束不自动表达这些槽位之间的集合唯一性,这也是值应独立成行的信号。
槽位唯一不等于值集唯一
同义多列里,一个标签可能重复出现在不同槽。给单列建唯一约束并不能表达“同一实体的全部标签不重复”,反而可能约束其他实体使用该标签。独立标签行配合实体ID与标签的组合唯一,更直接表达业务集合;跨列比较约束虽可写,也会随槽位增加而复杂。
槽位唯一不等于值集唯一保存独立卡片。
原书依据 · 1 处
8.2.4 处理不断增长的值集
达到三个标签上限后增加tag4,不仅改变表,也要求修改每条查询和更新语句。实际DDL是否重建或阻塞取决于产品与版本,但语义上所有相关代码仍需识别新槽。
增加槽位会扩散模式变更
多列值集增加一个槽位,会影响查找、增删、去重及报告中的每条相关表达式;漏改一处就可能静默漏数。改表还可能有锁和数据搬迁成本,具体依引擎与在线DDL能力。把值存成行可让数量增长通常只增加数据,而不扩大同义列及查询分支。
增加槽位会扩散模式变更保存独立卡片。
原书依据 · 1 处
8.3 如何识别反模式
多列属性与逗号列表都在处理多值关系,区别只是把多个值编码进一个字段或多个槽。任意数量上限与跨同义列搜索,是重新检查关系表达的线索。
编号列把值集固定成槽位关联本项背景,实质主要讲解在对应位置。
多列标签放大查询条件关联本项背景,实质主要讲解在对应位置。
8.4 合理使用反模式
报告人、修复人、验证人即使都引用Accounts,也具有不同且稳定的角色,分别设列是合理的。它们不是任意三个标签槽,偶尔查询“所有参与者”较复杂可作为有意接受的代价。
同表外键表达不同角色关联本项背景,实质主要讲解在对应位置。
8.5 解决方案:创建从属表
Tags用bug_id和tag组成主键,每个标签占一行,外键把它关联到缺陷。查询、增删和唯一性都按关系操作,不再寻找空槽;业务上限如果存在,应另外明确表达。
从属表让多值属性成行
同一缺陷的每个标签在Tags中独立占行,以(bug_id, tag)组合唯一,并引用缺陷主记录。查询一个标签按标签匹配,查同时具备两个标签可沿两组关系匹配;增删只动对应行,无需找空槽。表行数仍受资源限制,业务标签数上限也应显式定义。
从属表让多值属性成行保存独立卡片。
第9章 元数据分裂
按年份复制表或按类别不断加列,把原本应是数据的区别推入元数据,会迫使查询和迁移随数据增长。先恢复统一逻辑关系,再按实际负载选择分区、分片或纵向拆分;性能需要证据,物理分割也不能假定保留所有唯一与外键能力。
把每年收入写成revenue2002等列,会让时间增长不断触发表结构和迁移流程。年份本来是数据维度,却被变成了列名的一部分。
年份应是数据而非新列
按年收入或修复数若以年份命名新列,每到一年就要改表、改程序和协调迁移。更自然的是用年份值与实体ID定位一条年度事实,再用固定指标列保存数值。这里的成本来自数据维度不断制造新模式对象,不是年份这个维度本身复杂。
年份应是数据而非新列保存独立卡片。
9.1 目标:支持可扩展性
本章追求大表增长时的查询与维护可扩展性。数据量会影响成本,但性能也取决于访问路径、筛选比例和查询模式,不能只由总行数决定。
表行数不是查询成本全部
面对大表变慢,应检查实际访问行数、索引路径、返回列和工作负载,而不是直接推导“每表行越少必然越快”。拆表可能减少某些扫描,却增加路由、跨表查询和维护成本。先定位瓶颈,再选择分区或其他结构调整,收益仍需用实际计划和测量确认。
表行数不是查询成本全部保存独立卡片。
原书依据 · 1 处
9.2 反模式:克隆表与克隆列
元数据分裂把原本的数据值写进表名或列名,例如每年一张Bugs表或一个收入列。它与EAV方向相反:前者用值制造结构,后者把结构名字变成值;两者都转移了数据库原有的表达责任。
元数据分裂让数据制造结构
按年份或类别克隆表与列,会让新增数据要求新增模式对象。路由、跨表聚合、约束和字段同步随对象数量扩散。它与EAV把列名当字符串恰好相反:都是混淆数据维度与结构职责,应先判断维度是否可保存为普通字段。
元数据分裂让数据制造结构保存独立卡片。
9.2.1 不断产生的新表
按年拆表后,每次写入都要路由到正确年份表,新年忘建表便直接失败。这是让未来数据依赖未来模式对象的运行风险。
手工分表需要持续路由
按年份等值手工分表,应用既要选择正确目标,也要保证未来目标已创建。新年忘建表会让正常数据无法写入,日期修正又可能触发迁移。表路由和生命周期是新增责任,不能只比较单表行数就认为拆分免费。
手工分表需要持续路由保存独立卡片。
9.2.2 管理数据完整性
表名不会自动约束行里的年份。每表CHECK可验证归属,但创建下一张表时也必须正确更新规则,否则会拒绝正确数据或放入错误数据。
表名不自动约束数据归属
表叫Bugs_2009并不会阻止写入2010年的记录。手工拆表需要可用的CHECK或其他规则验证分片归属,并在每张新表保持规则正确。模式名只是标识,不是隐含谓词;约束是否实际执行还须按数据库与版本核对。
表名不自动约束数据归属保存独立卡片。
原书依据 · 1 处
9.2.3 同步数据
修正一条记录的日期可能改变它应在的年份表。此时不只是更新字段,还要安全地把整条记录移到另一张表,保持唯一性和引用关系。
分片键变化会牵动行迁移
手工按年份分表后,修正报告日期可能让行属于另一张表。只改日期会破坏归属规则,迁移则要协调插入、删除、唯一性和引用。应把它当作整体一致性动作;原书示例只展示基本步骤,不能据此认定并发、失败恢复和CHECK条件都已处理。
分片键变化会牵动行迁移保存独立卡片。
原书依据 · 1 处
9.2.4 确保唯一性
各表自己的自增值可能重复,跨表移动和统一查询就需要更大的身份范围。生成值的唯一范围必须覆盖整个逻辑实体集合,而不是只覆盖物理表。
跨表身份要统一唯一范围
逻辑上的同一实体被拆进多表后,若每表独立自增,同一ID可能代表不同记录,跨表迁移也可能撞号。需要覆盖逻辑全集的ID分配方案,或把分片标识纳入身份。共享序列或生成器只是候选,回滚、循环及连接语义要核对,不能直接复制旧生成器技巧就认定安全。
跨表身份要统一唯一范围保存独立卡片。
原书依据 · 1 处
9.2.5 跨表查询
跨年统计必须拼出全部相关表,新增年份还要更新所有联合查询。忘掉某张表可能不报错,只产生不完整结果。
分表聚合容易漏掉新表
手工分表后,全量统计要显式联合全部分表;新增分片时查询列表也要更新,遗漏会造成静默漏数。查询应对齐列和语义,并区分UNION去重与UNION ALL保留行的需要。统一逻辑表或受控路由可以减少手工维护,但不能凭合并语法保证统计完整。
分表聚合容易漏掉新表保存独立卡片。
9.2.6 同步元数据
分表中新增hours列必须同步到所有参与同一逻辑模型的表。结构漂移会破坏联合结果形状,SELECT星号更容易放大这种不一致。
克隆表需要同步模式演进
多张克隆表代表同一逻辑对象时,新增字段、类型和约束应按受控过程同步。只改一张表会使联合查询列数或含义不同,星号投影尤其脆弱。显式投影共同字段能控制结果形状,但并不能代替模式迁移治理。
克隆表需要同步模式演进保存独立卡片。
原书依据 · 1 处
9.2.7 管理引用完整性
引用一个被拆成多表的逻辑父对象,普通外键无法用通配符选择父表;查某个报告人的全部记录也要跨表合并。拆分影响两端的关系使用,而不仅是数据存放位置。
外键目标不能按行切换关联本项背景,实质主要讲解在对应位置。
分表聚合容易漏掉新表关联本项背景,实质主要讲解在对应位置。
9.2.8 标识元数据分裂列
将bugs_fixed_2008等指标拆为年份列,同样会让每年新增数据变成新增字段。应把项目、年份和指标值作为事实本身表达。
年份应是数据而非新列关联本项背景,实质主要讲解在对应位置。
9.3 如何识别反模式
每遇到一个新值就建表/列、接近对象数量上限、按变量构造年份表名,都是应重新检查模式边界的信号。共同结构的数据是否可由同一逻辑表加维度列表达,是第一步问题。
元数据分裂让数据制造结构关联本项背景,实质主要讲解在对应位置。
手工分表需要持续路由关联本项背景,实质主要讲解在对应位置。
9.4 合理使用反模式
若历史数据极少访问且无需与当前数据联合,归档可让活跃工作集更集中。按租户拆开独立数据也可能便于搬迁、负载调整与恢复;原书WordPress规模是2009年案例,不是当前架构数据。
低频历史数据可独立归档
历史数据很少访问且无需经常与当前数据联合时,可归档到兼容结构,缩小活跃工作集。收益来自访问边界清楚,而非任意拆表都更快;仍要定义保存、查询和恢复方式。若日常报表频繁跨历史与当前,归档会引入新的合并成本。
低频历史数据可独立归档保存独立卡片。
原书依据 · 1 处
租户边界可简化搬迁恢复
按独立租户组织数据库,可把单租户迁移、负载调整和恢复限制在较小范围。原书2009年WordPress案例用每博客独立数据说明这种管理收益;代价是更多数据库对象和跨租户操作治理。不能把当年的服务器与博客数量当作当前规模,实际方案须按工作负载评估。
租户边界可简化搬迁恢复保存独立卡片。
原书依据 · 1 处
9.5 解决方案:分区及标准化
扩展大表可从按行分区、按列拆分和关系标准化三个方向考虑。它们分别改变物理行归属、读取列集合和事实表达,不能混成同一种优化。
9.5.1 使用水平分区
数据库原生水平分区可把一个逻辑表按规则分为多个物理分区,让查询继续使用逻辑表名。原文把它也称分片,但这里并未说明跨服务器分片。分区仍有索引、外键和产品特性的限制,不能声称数据库会无条件处理全部问题。
原生分区保留单一逻辑表
原生水平分区按规则把行分到物理分区,对外仍使用同一逻辑表名,可减少应用手工拼接所有分表。它不等同于跨服务器分片,也不保证查询必然更快。须核对分区裁剪、唯一键与外键限制;原文MySQL示例主键未包含分区表达式所用列,不宜直接运行。
原生分区保留单一逻辑表保存独立卡片。
原书依据 · 1 处
哈希分区不等于一年一表
按年份哈希到固定数量分区时,不同年份可能落在同一分区;四个分区不表示只支持四年。新年份通常不必新建分区,但分区数量与容量仍需管理。这个规则与每年新建一张业务表不同,也与按年份范围定义的分区不同。
哈希分区不等于一年一表保存独立卡片。
原书依据 · 1 处
9.5.2 使用垂直分区
垂直拆分把很大或很少使用的列放到关联表。产品安装包与常用产品信息分开后,常规查询不会无意搬运二进制内容;用相同主键兼外键可保证每产品最多一条安装包记录。
垂直拆分隔离大而冷的列
很大且低频使用的字段可放进独立扩展表,主键兼外键关联主表。常规读取只访问热字段,需要时再取扩展内容,避免星号查询无意传输大对象。收益取决于引擎行外存储和访问方式;额外连接与维护也是成本,不能把MyISAM固定行经验推广到所有产品。
垂直拆分隔离大而冷的列保存独立卡片。
原书依据 · 1 处
9.5.3 解决元数据分裂列
把项目年度修复数存成project_id、year和bugs_fixed,组合键标识每项目每年一项指标。新年份只增加行,避免不断生成同义列。
年份应是数据而非新列关联本项背景,实质主要讲解在对应位置。
第二部分 物理数据库设计反模式
第二部分从逻辑关系转向数值类型、枚举、文件存储和索引的物理表示,继续比较看似简便的选择如何改变正确性与维护成本。
第10章 取整错误
数值类型是结果契约的一部分。二进制浮点难以精确表示许多十进制数,累积、比较和显示各有不同问题。计费金额通常需要明确的定点十进制或最小单位整数及舍入规则;精确类型仍有容量、标度和除法边界,不能把它当成任意精度数学。
项目成本按工时乘时薪统计,若两列用近似二进制浮点保存,输入时就可能偏离十进制业务值,汇总与比较再把偏差带入报表。金额问题不一定来自SQL拼写错误,也可能来自数据表示选择。
浮点金额污染成本报表
项目报表把每条缺陷工时乘程序员时薪并汇总,结果与人工金额相差几美元。近似二进制浮点不能精确表示许多十进制小数,输入和运算中的表示误差可能进入乘积与合计;界面显示两位并不能证明内部精确。复利反复乘同一近似因子也会传播误差,方向与大小依具体数据和运算。
对应方案:定点十进制保存计费金额。
浮点金额污染成本报表保存独立卡片。
10.1 目标:使用小数取代整数
非整数不等于只能用二进制浮点。钱需要精确到业务规定的刻度,目标应是按规定表达和运算,而不是照语言关键字习惯选FLOAT。
小数需求不等于浮点需求
业务需要2.5或19.95,只说明整数单位不足以直接表达,并不说明必须使用近似二进制浮点。先确定数值范围、十进制刻度和运算精度,再选择表示。原书以金额说明精确十进制需求,不能把所有小数都称为浮点数。
小数需求不等于浮点需求保存独立卡片。
10.2 反模式:使用FLOAT 类型
FLOAT或DOUBLE的名字与程序语言相似,容易让人不经核对就沿用。实际数值语义须按数据库看;接下来讨论的是常见IEEE二进制近似类型,而非所有名叫FLOAT的类型。
类型名称不能代替数值语义
数据库类型名与编程语言相似,不保证表示方式完全相同。常见FLOAT、REAL或DOUBLE采用有限二进制精度,但具体宽度与厂商语义有差异;Oracle FLOAT还是NUMBER的子类型。选择金额或测量类型时,应查实际精度、范围和舍入行为,不能只按关键字类比。
类型名称不能代替数值语义保存独立卡片。
10.2.1 舍入的必要性
有限位数无法表示所有实数,有些十进制有限小数在二进制里也会无限循环。因此提高二进制精度只能缩小误差,不能使所有十进制输入都精确。
有限二进制不能精确存十进制
如十进制的1/3需要无限重复一样,59.95等有限十进制值在二进制中也可能无限展开。有限二进制浮点只能保存附近可表示值。增加位数可缩小误差,却不能对所有十进制小数消除误差;“输入只有两位小数”并不保证内部仍是同一个精确值。
有限二进制不能精确存十进制保存独立卡片。
原书依据 · 1 处
10.2.2 在SQL中使用FLOAT
打印格式可能掩盖内部近似,放大或比较才暴露差别。容差比较适用于允许误差的计算,阈值必须按业务与误差规模确定;大量求和与连乘还会传播输入和计算误差。
浮点金额污染成本报表关联本项背景,实质主要讲解在对应位置。
显示精度不等于存储精度
数据库或客户端把浮点值显示成59.95,可能只是格式化舍去了尾数。放大运算和精确相等比较会暴露表示差异;但具体查询是否匹配还与常量类型和转换规则有关,不能把原书空结果当所有引擎的必然输出。验证应区分输入、存储、运算及显示四个阶段。
显示精度不等于存储精度保存独立卡片。
原书依据 · 1 处
容差比较要有误差尺度
近似数可按两者差值的绝对值是否小于阈值来判断近似相等。阈值过小仍会拒绝本应接受的误差,过大又会掩盖有意义差异;它必须由数值规模和允许误差决定。容差比较不是把近似金额变精确,也不能替代货币计算规定的精度与舍入规则。
容差比较要有误差尺度保存独立卡片。
原书依据 · 1 处
连乘会传播因子误差
因子每次都偏离目标,反复相乘会传播这种差异。原书用本应为1却变成0.999的示意因子说明:重复1000次约得0.3677。它不是某种IEEE运算的通用误差上界,也不证明所有误差单向增长;金融复利尤其要检查输入因子和中间精度。
连乘会传播因子误差保存独立卡片。
原书依据 · 1 处
IEEE二进制浮点的组成
二进制浮点用符号、指数和有效数字部分表示数值,指数让有限位数覆盖很大的数量级范围。精度和范围是不同能力:能存极大或极小值,不表示能精确保存所有日常十进制小数。原文只作格式概述,特殊值及非规格化细节不在此展开。
IEEE二进制浮点的组成保存独立卡片。
原书依据 · 1 处
10.4 合理使用反模式
科学测量等需要宽数量级且能接受近似的计算,可合理使用浮点。Oracle的FLOAT与BINARY_FLOAT又表明,同名和相近名类型不能推断相同表示方式。
类型名称不能代替数值语义关联本项背景,实质主要讲解在对应位置。
浮点适合容许近似的宽范围
科学计算等场景可能更需要宽数量级范围,并允许受控近似,二进制浮点便有合理用途。是否合适应同时看精度、范围、性能与误差传播,而不是把“钱不应误用近似类型”扩成“所有浮点都应禁用”。厂商具体类型含义仍须核对。
浮点适合容许近似的宽范围保存独立卡片。
原书依据 · 1 处
10.5 解决方案:使用NUMERIC 类型
NUMERIC/DECIMAL按声明的十进制精度和刻度表达值,适合需要精确十进制语义的金额。它也不是无限精度:超范围、超刻度和运算结果仍要处理;超刻度可能被舍入而非一律拒绝。
十进制精度与刻度不同
精度是允许的总有效数字数,刻度是小数点后的位数。以通常的NUMERIC(9,2)为例,共9位,其中2位小数,整数部分最多7位。超过范围会受产品规则处理,超过刻度也可能先舍入,并非原文所说一律非法;对实际输入和计算结果必须核对厂商行为。
十进制精度与刻度不同保存独立卡片。
原书依据 · 1 处
定点十进制保存计费金额
工时乘时薪及项目成本合计,使用符合业务范围和刻度的精确十进制类型,避免先把输入转为二进制近似数再计算。复利也应按业务所需中间精度处理,最终舍入必须明确。NUMERIC/DECIMAL仍有精度上限,1/3也不能有限精确保存;输入超刻度、乘积与汇总不能默认无限精确。
对应问题:浮点金额污染成本报表。
定点十进制保存计费金额保存独立卡片。
原书依据 · 1 处
第11章 每日新花样
允许值集合既可以属于类型定义,也可能是会变化的业务数据。把经常变化的状态塞进列定义,会增加部署与查询元数据的负担。查询表和外键便于表达允许集合及附加属性,但停用旧值与禁止新使用不是同一个约束,需要继续设计。
称谓候选值看似有限,国际化需求却会增加新值。若候选集合直接写进列定义,一次普通业务扩展可能升级成模式迁移;应先判断集合是否真正稳定。
业务候选值不一定固定
称谓、状态等候选集合可能随地区和业务流程变化。把集合写死在列定义中,新增选项就可能需要模式变更、应用协调与迁移验证。先判断集合是否真正固定;频繁变化且需查询或附带属性的值,通常适合保存为数据,而不是反复扩展模式。
业务候选值不一定固定保存独立卡片。
11.1 目标:限定列的有效值
有效值限制能简化使用者逻辑,例如拒绝BANANA状态。问题不是要不要验证,而是用什么结构保存会变化的合法集合。
合法值验证与集合管理分开
限制状态只能来自合法集合,可以让后续逻辑依赖明确含义。验证规则与候选值存储是两项选择:固定集合可由模式约束,可变集合可由检查表和外键表达。不能因为集合会变化,就取消合法值验证;也不能认为加了一个枚举类型就解决值的生命周期。
合法值验证与集合管理分开保存独立卡片。
原书依据 · 1 处
11.2 反模式:在列定义上指定可选值
CHECK、ENUM、域/UDT和触发器都可参与合法值控制,但作用方式与产品支持不同。MySQL ENUM还按定义位置编码与默认排序,不等同于普通文本的字母序。
检查约束验证列值条件
CHECK用布尔条件约束插入或更新后的数据,可检查允许值集合,也可表达开始时间早于结束时间等关系。它不是专门的枚举机制;是否执行、空值如何影响条件,须按数据库版本和语义核对。候选集会变时,还要衡量修改模式的代价。
检查约束验证列值条件保存独立卡片。
原书依据 · 1 处
MySQL枚举按定义次序排序
MySQL ENUM以声明中各选项的位置为内部索引,默认排序按定义次序,而不是标签字母序。若业务需要展示顺序,应明确设计,不要把文本外观误认为文本排序语义。这个行为是MySQL具体类型的规则,不是所有数据库枚举都由相同实现保证。
MySQL枚举按定义次序排序保存独立卡片。
域让列复用取值规则
域或用户自定义类型可把一组取值规则作为可复用的类型级定义,用于多列而不处处重复同样约束。它们有助于统一合法值含义,但语法和支持范围随产品而异,修改定义也会影响使用处。不能把模式层复用当作候选集合可无限自由变化。
域让列复用取值规则保存独立卡片。
11.2.1 中间的是哪个
当前数据里出现过的状态不等于所有允许状态。用DISTINCT填菜单会让尚未使用的新状态永远不可选;从元数据解析列表又增加产品耦合和维护成本。
已用值不等于允许值
从业务表取DISTINCT状态,只能得到已出现的值;若全部记录是NEW,菜单就无法选择尚未使用的FIXED。候选集合应有独立可信来源。解析类型定义或在应用再抄一份都可能增加耦合与不同步风险,可变候选表更方便直接查询和排序。
已用值不等于允许值保存独立卡片。
11.2.2 添加新口味
将新状态加到模式定义可能需要重新声明集合,并协调迁移与应用。具体DDL是否在线依产品与版本,不应把旧书中的停机过程当作所有系统必然行为。
候选变化不应无谓升级模式
频繁增删业务候选值若要求修改表或类型定义,会把普通数据维护变成模式迁移,增加回归和发布协调。某些产品支持在线或直接修改类型,不能笼统断言必停机;即便如此,也应评估候选集合放在哪层更便于查询、治理和演进。
候选变化不应无谓升级模式保存独立卡片。
原书依据 · 1 处
ETL是抽取转换再加载
ETL依次从来源抽取数据、按目标需要转换、再加载到目标存储。原书用它描述迁移期间转存、改结构与导回数据的过程。ETL本身不是必然停机的同义词,是否阻塞服务取决于迁移方案、增量同步与切换方式,不能由缩写推断运行窗口。
ETL是抽取转换再加载保存独立卡片。
原书依据 · 1 处
11.2.3 老的口味永不消失
废弃选项还可能被历史记录引用,直接移除会破坏已有事实。把FIXED拆成两个新状态时,不能任意猜历史数据应归哪类;需要保留语义并区分历史可引用与新建可选。
废弃值不应抹掉历史语义
将旧FIXED拆成CODE COMPLETE与VERIFIED,历史记录未必包含足以重新分类的信息。直接替换成某个新值或默认值会改写事实。应先定义迁移依据;常见做法是保留旧值供历史引用,同时限制它在新操作中的可选性,二者是不同规则。
废弃值不应抹掉历史语义保存独立卡片。
原书依据 · 1 处
11.2.4 可移植性低下
不同产品对枚举、CHECK、域、UDT和触发器的语法与能力不同。可移植性要求核对实际支持,而不是把任何一种类型名当作共同标准实现。
域让列复用取值规则关联本项背景,实质主要讲解在对应位置。
MySQL枚举按定义次序排序关联本项背景,实质主要讲解在对应位置。
11.3 如何识别反模式
如果只是加一个菜单选项就要做复杂模式发布,或代码与数据库的选项清单反复不同步,应检查候选值是否放错层。“不应该改”不等于业务上“不会改”。
业务候选值不一定固定关联本项背景,实质主要讲解在对应位置。
已用值不等于允许值关联本项背景,实质主要讲解在对应位置。
11.4 合理使用反模式
真正稳定的二选一集合可以适合枚举;CHECK也适合表达时间先后等约束。是否反模式取决于候选变化与语义需求,不在于用了哪个关键词。
固定规则可以留在模式中
若值集合确实稳定、无需频繁添加说明属性,枚举或CHECK可以直接表达规则。CHECK还可检查列之间的固定关系,例如开始时间早于结束时间。稳定需来自业务分析而非愿望,所谓二选一也可能日后扩展;不能凭现在只有两项就免除生命周期判断。
固定规则可以留在模式中保存独立卡片。
11.5 解决方案:在数据中指定值
检查表每行保存一个候选状态,业务表以外键引用它。合法集合成为可查询数据,同时数据库仍可拒绝无效引用;与取消验证完全不同。
检查表让候选集合成为数据
可变状态集合可用检查表保存,每行一个合法值,业务字段通过外键引用。读取菜单、排序和新增候选都使用普通数据操作,减少解析模式定义的需要。它保证引用存在,不自动检查新操作只能使用活跃状态;允许NULL与否也需由列约束另行决定。
检查表让候选集合成为数据保存独立卡片。
11.5.1 查询候选值集合
菜单从检查表直接查询候选行,就能获取尚未被业务数据使用的合法值,并按需要排序,而不是从业务数据去重猜测全集。
检查表让候选集合成为数据关联本项背景,实质主要讲解在对应位置。
11.5.2 更新检查表中的值
新增候选通常只需向检查表增加数据;重命名可结合受支持的级联更新。数据操作仍可能有锁和应用协调需求,只是无需每次重新定义类型。
检查表让候选集合成为数据关联本项背景,实质主要讲解在对应位置。
11.5.3 支持废弃数据
检查表给候选加active属性,历史记录仍可引用旧值,菜单只显示活跃选项。外键只验证旧值存在,并不会自动拒绝新写入使用inactive值。
停用候选保留历史引用
保留候选行并标记停用,可以让历史记录继续引用原值,同时让界面只展示活跃项。它将“存在过”与“现在可选”分开,避免删值改写历史。外键只检查行存在,若要阻止新记录选停用值,还需应用或额外规则验证;隐藏菜单本身不足以约束其他写入入口。
停用候选保留历史引用保存独立卡片。
原书依据 · 1 处
11.5.4 良好的可移植性
固定集合适合模式约束,可变集合适合数据表,这是生命周期判断而非绝对分类。检查表依赖普通表和外键,较易移植,但仍受具体产品的支持与资源限制。
固定规则可以留在模式中关联本项背景,实质主要讲解在对应位置。
检查表让候选集合成为数据关联本项背景,实质主要讲解在对应位置。
第12章 幽灵文件
文件路径只是一条引用,文件系统并不自动参加数据库事务、备份和权限控制。BLOB可以让部分生命周期与行绑定,外部对象存储则可能更适合大文件分发;两者都要明确失败后的清理、一致性和恢复责任,而不是只比较单次读取速度。
数据库恢复成功却丢失所有外部图片,说明备份范围只覆盖了关系记录,没有覆盖路径指向的数据。把图片放在同一台机器或目录附近,不等于它会被数据库备份自动收集。
恢复数据库不等于恢复附件
数据库只保存图片路径时,数据库备份通常不自动包含文件内容。即使数据库恢复为完整副本,图片仍可能全部缺失。应明确文件与数据库的备份范围、对应版本和恢复验证;“文件就在数据库旁边”不是备份策略,也不能以数据库行数正常证明应用恢复完整。
恢复数据库不等于恢复附件保存独立卡片。
12.1 目标:存储图片或其他多媒体大文件
目标不仅是存二进制内容,还要在读取账号或缺陷时取得正确的关联图片。数据本体与关系的生命周期应一起考虑。
12.2 反模式:假设你必须使用文件系统
图像可存为BLOB,也可把内容放在文件系统、表中只存路径。后一种选择让数据库只管理字符串,文件生命周期和访问规则必须另行设计;不能因为文件原本来自磁盘就默认它只能留在外部。
BLOB保存二进制内容
BLOB或类似二进制大对象类型把图片等字节内容作为数据库值保存,可与相关记录一起接受数据库的事务、备份和权限管理。图片不必先转成普通文本。大小、流式访问与引擎行为依产品而异,采用BLOB也不自动解决应用缓存、传输或所有并发业务问题。
BLOB保存二进制内容保存独立卡片。
原书依据 · 1 处
路径字符串不代表文件约束
表中只存文件路径时,数据库通常只验证字符串类型,不证明对应文件存在,也不会随文件重命名或移动自动改值。外部存储本身可以合理,但必须另行管理对象身份、生命周期和错误处理,不能把保存了路径等同于保存并约束了内容。
路径字符串不代表文件约束保存独立卡片。
12.2.1 文件不支持DELETE
删除路径记录不会自动删除外部文件,孤立图片可能持续堆积。若多个记录共用内容,删除时还要确认是否仍有有效引用,不能机械地一删行就删文件。
外部附件需要显式回收
删除数据库路径行不会自动删除文件内容,孤立对象会积累;反过来,盲删文件也可能破坏其他记录的引用。应设计并验证回收条件、时机和失败处理。原文强调行与文件的动作不相连,具体是否共享或延迟回收须由应用模型决定。
外部附件需要显式回收保存独立卡片。
原书依据 · 1 处
12.2.2 文件不支持事务隔离
文件删除通常在文件系统中立即生效,不参与数据库事务的提交可见性。其他客户端可能仍读到数据库行,却已经打不开图片,形成跨资源的不一致窗口。
外部文件不随数据库隔离
普通文件修改不属于数据库事务。一个客户端删行后立即删文件、稍后才提交,其他客户端可能仍看见旧行,却已无法读文件。具体数据库可见性依隔离级别而定,核心是两个资源没有共同事务边界;不能把所有隔离级别都描述为提交前绝不见修改。
外部文件不随数据库隔离保存独立卡片。
原书依据 · 1 处
12.2.3 文件不支持回滚操作
数据库回滚恢复的是数据库内部变更,已经unlink的普通文件不会自动复原。取消业务动作时,两个资源可能只回退一半。
回滚不能撤销普通文件删除
事务里删数据库行并删除外部文件后再回滚,数据库行可能恢复,文件内容却不会随之回来。数据库回滚只覆盖自身管理的资源。外部附件方案需要设计补偿、保留版本或其他一致性办法,并验证失败路径,不能把事务API当成跨文件系统的统一撤销能力。
回滚不能撤销普通文件删除保存独立卡片。
原书依据 · 1 处
12.2.4 文件不支持数据库备份工具
分别备份数据库和图片还不够:两次备份期间图片可能变化,恢复时需要能够配对的版本。原生数据库备份工具擅长其管理的数据,不会自动理解任意路径字段。
恢复数据库不等于恢复附件关联本项背景,实质主要讲解在对应位置。
12.2.5 文件不支持SQL的访问权限设置
表和列的SQL访问权限不会自动保护文件路径背后的字节。外部图片若有其他读取入口,需要独立的授权检查。
数据库权限不保护外部文件
即使路径列受到SQL权限控制,文件仍可能通过静态服务器、目录权限或其他入口被读取。外部附件必须单独定义谁可访问,并在实际读取路径执行授权;隐藏路径字段不等于限制内容访问。采用数据库内BLOB也仍需正确控制应用输出入口。
数据库权限不保护外部文件保存独立卡片。
原书依据 · 1 处
12.2.6 文件不是SQL数据类型
文件路径只是字符串,存在性与正确内容都要在实际访问时处理。程序拼路径、读取文件的成功与否,已经成为数据完整性的一部分。
路径字符串不代表文件约束关联本项背景,实质主要讲解在对应位置。
12.3 如何识别反模式
判断外部附件是否设计完整,应问备份能否在干净环境恢复、孤立文件如何清理、谁有读取权限,以及变更能否撤销。并非每个应用都需要同样强的事务,但这些取舍要有明确答案。
附件方案须验证完整生命周期
外部附件方案至少应回答:备份如何配对恢复,孤立对象如何回收,读取权限在哪里检查,修改是否可撤销。按实际业务选择所需保证,并在干净环境验证恢复结果。没有答案说明责任尚未设计清楚,不代表外部存储本身必定错误。
附件方案须验证完整生命周期保存独立卡片。
原书依据 · 1 处
12.4 合理使用反模式
外部文件可让数据库更小,也便于预览或批量编辑;简单单客户端拍照系统可能根本不需要复杂事务。选择应比较真实生命周期需求,而不是沿用“图片必须在库外”的惯例。
外置文件是有条件的取舍
若直接预览、批量编辑和缩小数据库等收益更重要,且备份、权限、回滚及并发的一致性需求已妥善处理,外部文件是合理选择。原书单客户端会议拍照例子需求简单,说明不能无条件排除方案;它不能证明多客户端或高一致性系统也无需额外设计。
外置文件是有条件的取舍保存独立卡片。
原书依据 · 1 处
12.5 解决方案:在需要时使用BLOB 类型
把内容放入数据库管理的二进制值,可让记录与内容共用相应事务和备份边界。仍要核对引擎、隔离级别、容量及接口;BLOB不是所有业务一致性问题的万能解。
同库内容共享事务备份边界
将附件字节与关系记录存入支持事务的数据库,可以一起提交、回滚和备份,也可统一应用数据库权限。这样消除了普通路径与文件两套生命周期的错位。保证范围仍取决于引擎与配置,事务并不自动防止所有丢失更新,容量与应用传输也须另外设计。
同库内容共享事务备份边界保存独立卡片。
原书依据 · 1 处
大对象导入导出分清位置
数据库可提供读取文件进二进制列、导出二进制值到文件的接口。MySQL LOAD_FILE读取服务器主机上的文件,要求相关文件与权限条件;DUMPFILE也是服务器侧输出。它们不是任意读取客户端本地文件的快捷方式,执行前要查实际产品文档及访问范围。
大对象导入导出分清位置保存独立卡片。
原书依据 · 1 处
输出图片要匹配内容类型
从BLOB取出图片字节供Web客户端显示时,要设置与实际图片编码一致的Content-Type,再输出原始字节,避免把二进制当普通HTML。数据库字段保存的是内容,不会自动生成HTTP响应;授权检查也应在输出前完成。示例中的JPEG应采用标准媒体类型image/jpeg。
输出图片要匹配内容类型保存独立卡片。
原书依据 · 1 处
第13章 乱用索引
索引通过附加结构缩小访问范围,也增加存储和写入维护。无索引、过多索引与不匹配查询的索引都是问题。需要从谓词、排序、连接、数据分布和计划出发判断,区分估计与实测,并避免把旧引擎行为当成所有数据库的规则。
索引不足、索引过多和查询无法利用索引,可能在同一个应用里同时存在。优化从认识真实工作负载开始,开发者提供业务访问方式,DBA提供诊断工具,两者需要合作。
索引设计要从工作负载出发
索引既不是越多越好,也不是越少越好。先明确程序最常执行、总耗时最高的访问,再选择能支持筛选、连接或排序的结构。开发者掌握实际查询需求,数据库人员掌握计划与实现,协作比凭字段直觉逐个加索引更可靠。
索引设计要从工作负载出发保存独立卡片。
13.1 目标:优化性能
索引是帮助定位数据的结构,能避免某些整表搜索;它的收益依赖查询能否利用其组织方式,而不是只要存在索引就会更快。
索引提供额外访问路径
索引按特定键组织值与记录位置,为筛选、连接或排序提供额外访问路径,可能减少要检查的数据。它需要空间和更新维护,优化器也可能因匹配行太多选择扫描。索引不是数据表的替代品,更不是所有查询都自动获益的开关。
索引提供额外访问路径保存独立卡片。
原书依据 · 1 处
13.2 反模式:无规划地使用索引
无规划索引有三种表现:该有的没有、维护无收益的过多、查询表达与索引组织不匹配。应分别诊断,而不是把所有慢查询都归为“少一个索引”。
索引设计要从工作负载出发关联本项背景,实质主要讲解在对应位置。
13.2.1 无索引
索引维护有成本,但定位记录同样有成本。它不仅帮助SELECT,也可能缩小UPDATE和DELETE寻找目标行的工作;应比较整体读写成本而非仅看维护动作。
索引维护成本要与定位收益比
插入、更新或删除可能维护索引,增加写入与空间成本;但读取以及更新/删除前定位目标行,也可能因索引减少扫描。应结合实际读写频率和访问范围比较收益,不能因有维护成本就去掉全部索引,也不能只数SELECT而忽略写语句定位。
索引维护成本要与定位收益比保存独立卡片。
原书依据 · 1 处
索引细节要按数据库核对
索引的可用类型、创建语法、维护和计划输出由数据库产品决定。通用数据结构思想可迁移,具体优化行为仍需看产品版本及实际执行计划。相似的CREATE INDEX或EXPLAIN名称不保证相同能力,不能以一套经验替代各引擎文档。
索引细节要按数据库核对保存独立卡片。
原书依据 · 1 处
13.2.2 索引过多
重复主键索引、很少匹配查询的列索引以及顺序不合适的组合索引,可能只增加维护成本。是否无用必须结合现有索引和真实查询验证,不能只因列名是summary或hours就下结论。
冗余索引仍要持续维护
已存在等价主键索引时,再建相同访问结构通常只增加空间和写入维护;从未服务重要查询的索引也可能没有收益。但“是否等价、是否使用”必须核对键顺序、约束用途和工作负载,不能凭列名或一次观察就删除。
冗余索引仍要持续维护保存独立卡片。
原书依据 · 1 处
组合索引的列序有含义
有序组合索引先按第一列、相同值内再按第二列组织。电话簿按姓再按名,便于先按姓定位,不等于全局按名已排序。使用时核对条件、排序与索引的关系;SQL里条件书写顺序不必照抄索引列序,非首列访问能力也受引擎策略影响。
组合索引的列序有含义保存独立卡片。
13.2.3 索引也无能为力
普通有序索引只对能利用其顺序的条件有帮助:先姓后名不等于先名排序,完整日期顺序不等于按月份聚集,任意位置子串也不天然对应连续键范围。表达式索引可支持明确的衍生表达式,但要匹配实际查询。
组合索引的列序有含义关联本项背景,实质主要讲解在对应位置。
表达式改变可用访问路径
给日期原值建普通有序索引,不一定能直接定位所有年份的四月,因为月份分散在不同年份。若产品支持,可为相应表达式或衍生列建立匹配索引,或按需求重写范围条件。是否可用取决于表达式匹配和优化器能力,不能把所有函数条件都绝对判为无索引。
表达式改变可用访问路径保存独立卡片。
原书依据 · 1 处
任意子串不匹配普通键顺序
查description中任意位置包含crash,普通按整串排序的索引通常不能把匹配项缩成一个前缀范围。继续添加相同类型索引可能没有帮助,应按语义评估全文等专门访问方式。全文检索与任意字符子串并不等价,选择替代方案前必须核对匹配需求。
任意子串不匹配普通键顺序保存独立卡片。
13.3 如何识别反模式
只有一句“如何更快”不足以诊断,需要表结构、索引、数据分布和目标指标。全加索引或全不用索引都只是缺乏依据的极端。
索引设计要从工作负载出发关联本项背景,实质主要讲解在对应位置。
13.4 合理使用反模式
不同值占比可粗看索引区分能力,但某个具体值的频率、覆盖性和查询用途同样重要。低基数列不必一律删索引;通用数据库初期的猜测也应以可回收的假设方式验证。
区分度不等于单值选择性
原书以不同值数量除以记录数描述索引分离率,可粗看键的区分能力;它不等于某次条件匹配多少行。低基数列也可能有稀有值,数据倾斜、覆盖查询和排序用途都会改变收益。命中大量行且需频繁回表时,扫描可能更划算;不能只凭全局比例机械删索引。
区分度不等于单值选择性保存独立卡片。
原书依据 · 1 处
13.5 解决方案:MENTOR你的索引
MENTOR把索引优化组织为测量、解释计划、提出候选、测试、调整资源与按需维护。先确认瓶颈在何处;案例中80%时间花在HTML解析,说明先猜数据库可能优化错地方。
MENTOR使索引优化有依据
MENTOR依次提醒测量耗时、解释执行计划、提出索引候选、验证改动、调整相关资源和评估维护。它是一条收集证据再行动的路径,不是每次都必须重建索引的清单。任何阶段发现瓶颈不在数据库,都应转向真实成本,而不是继续加索引。
结构图:
flowchart TD
A[测量实际成本] --> B[解释执行计划]
B --> C[提出候选]
C --> D[测试收益与代价]
D --> E[调整资源]
E --> F[按需维护]
F --> A
维护按证据选择,不表示每轮都执行重建。
MENTOR使索引优化有依据保存独立卡片。
原书依据 · 1 处
13.5.1 测量
优化优先级应看调用次数乘单次成本,而非只看最慢一条。采样还要控制结果缓存与测量开销,并用真实负载校正实验条件。
优化优先看总耗时
单次最慢的查询若很少调用,可能不是总成本最大的查询。把调用频率与每次耗时结合,优先处理对总体负载或关键延迟影响最大的部分。慢日志和跟踪能提供证据,但阈值、采样及测量开销会影响观察结果,仍须说明统计范围。
优化优先看总耗时保存独立卡片。
原书依据 · 1 处
性能测量须说明缓存条件
比较查询或索引方案时,要知道是否命中结果缓存,避免把绕过执行的结果误当索引收益。冷、热数据页缓存也会影响时间,应按目标分别控制并报告条件,而不是无条件清空全部缓存。上线观测要控制分析工具开销,并在需求结束后调整采样。
性能测量须说明缓存条件保存独立卡片。
原书依据 · 1 处
查询日志分析定位累计成本
pgFouine在原书中被介绍为分析PostgreSQL查询日志、定位应优先关注请求的工具。它代表的做法是将实际日志聚合为可比较的成本,而不是靠印象选查询。这里保留历史工具用途,不保证项目当前维护或适用于实际部署版本。
查询日志分析定位累计成本保存独立卡片。
原书依据 · 1 处
13.5.2 解释
执行计划说明数据库拟如何访问表、选择索引和连接,可能还含估计行数。图13-1显示Bugs扫描4650行估计、Products扫描3行,只有关联表使用主键;小表扫描未必值得优化,计划要与实际成本一起看。
执行计划解释访问与连接
查询执行计划描述表访问方法、连接次序、所选索引及可能的估计行数。它帮助把“慢”分解成扫描、连接或排序的原因;估计不等于实际执行统计,某个没用索引的步骤也不自动是瓶颈。应按产品文档解释字段,再与测量结果核对。
执行计划解释访问与连接保存独立卡片。
原书依据 · 1 处
临时排序信号需要结合规模
图13-1中Bugs为ALL,估计4650行,BugsProducts按主键关联,Products为ALL但仅估计3行;Extra提示临时结果与额外排序。这些字段用于提出候选优化,不证明每次扫描都该建索引,也不能由filesort字样直接断言一定写磁盘。先看规模、过滤与总成本,再验证方案。
临时排序信号需要结合规模保存独立卡片。
原书依据 · 1 处
13.5.3 挑选
根据计划可提出遗漏索引候选,也可借助顾问工具;建议仍须验证。覆盖索引把查询所需字段放入索引,可能减少回表,但可见性检查和引擎实现仍会影响是否真正只读索引。
索引顾问提出的是候选
索引顾问把工作负载统计和执行计划转成可能的结构调整建议,帮助发现遗漏访问路径。原书列出DB2、SQL Server、MySQL和Oracle的工具;建议不是自动正确的结论,还要核对写入维护、空间和其他查询回退,再测试效果。
索引顾问提出的是候选保存独立卡片。
原书依据 · 1 处
覆盖索引减少回表机会
若索引提供条件与输出所需列,数据库可能从索引取得结果,减少读取主表页面。增加覆盖列也扩大索引及写入成本;某些MVCC实现仍需检查表行可见性,不能只看列已包含就保证绝不回表。覆盖是否值得,应结合真实查询频率和计划验证。
覆盖索引减少回表机会保存独立卡片。
原书依据 · 1 处
13.5.4 测试
新增索引后重新测量相同负载,才能证明收益。应报告条件与实际差异,也检查写入和其他查询的代价;原书38%只是报告示例。
优化完成须有前后对照
索引调整后重测同样查询和代表性负载,比较耗时、扫描及资源,并检查写入和其他请求是否回退。预先定义指标和条件,结果才可解释。不能把“创建成功”当优化成功,也不能把书中的38%写成本次实测。
优化完成须有前后对照保存独立卡片。
原书依据 · 1 处
13.5.5 优化
缓存可减少频繁索引访问的I/O,但内存配置应与工作集和服务器总资源匹配。原文预载命令和默认配置属于特定引擎时期,不能直接用于所有数据库。
索引缓存按工作集分配
频繁访问的索引页若在内存中,可减少存储I/O;缓存预算应看工作集、命中情况和服务器总内存。不能一概调大到最大,也不能假设索引永远很小。预载与自动缓存的收益需测试,具体命令及缓存对象随引擎而异。
索引缓存按工作集分配保存独立卡片。
原书依据 · 1 处
13.5.6 重建
维护应针对实际问题:更新统计、回收空间与重建索引是不同动作。普通平衡树不会因删几行就自动失去高度平衡;页稀疏或碎片是否值得处理,需要量化收益和维护窗口。
统计刷新不等于索引重建
ANALYZE通常收集供优化器使用的分布统计,VACUUM按产品回收或整理空间,REINDEX等才明确重建索引。它们目的不同,不能把原书维护命令表当成等价替换清单。SQLite ANALYZE、SQLite VACUUM与PostgreSQL REINDEX官方说明已核验,实际选择须按产品及问题决定。
统计刷新不等于索引重建保存独立卡片。
原书依据 · 1 处
索引维护频率要有收益依据
不要机械地每周重建所有索引。先确认统计失真、页稀疏、碎片或其他具体问题,再比较维护耗时、锁与空间成本及可测收益。平衡树通常在更新中维持结构平衡,“有更新就越来越不平衡”不是通用解释;很少使用的大索引即使略有收益,也未必值得长时间维护。
索引维护频率要有收益依据保存独立卡片。
原书依据 · 1 处
第三部分 查询反模式
查询部分接下来分析NULL、分组、随机抽样、搜索、大查询与隐式列,说明结果正确性和表达边界比表面简写更重要。
第14章 对未知的恐惧
NULL表示未知或缺失语义,比较和布尔组合由三值逻辑决定。把它一律替换成零或魔法字符串,会把未知误写成已知事实。存储约束、查询条件和展示缺省各有职责,空值安全比较也要使用目标引擎支持的明确语法。
拼接姓名时,新增的中间名允许为空,普通字符串拼接遇到NULL可能令整个结果为空。数据未必丢了,而是表达式传播了缺失值。需要在显示语境中明确怎样处理没有中间名的情况。
可空中间名使全名变空
姓名拼接包含可空中间名时,在采用NULL传播语义的数据库中,没有中间名会使整个拼接结果为NULL。问题出在表达式,不代表姓和名丢失。可在查询里对中间名片段提供合适空白默认值,保留存储中“未提供”的含义;不同数据库拼接函数的NULL规则要核对。
可空中间名使全名变空保存独立卡片。
14.1 目标:辨别悬空值
NULL可表示未知或不适用,外连接也以它补充未匹配侧。具体语境不同,但它都不是普通数值或文本;程序应明确缺失意味着什么。
NULL表示缺失而非零
NULL是SQL用于表达缺失信息的特殊标记,可表示尚未知的离职时间、不适用的属性,或外连接未匹配一侧。它不是数字0、布尔假或空字符串。不同语境需由列含义说明,不能仅看到NULL就断言数据错误;函数无效输入是否返回NULL也依具体函数而定。
NULL表示缺失而非零保存独立卡片。
14.2 反模式:将NULL 作为普通的值,反之亦然
把NULL按普通0、空串或假值处理,会误解表达式结果;反过来随意选普通值替代NULL,也会污染数据含义。空串与NULL在个别厂商有特殊关系,但不是通用定义。
NULL表示缺失而非零关联本项背景,实质主要讲解在对应位置。
14.2.1 在表达式中使用NULL
未知工时加10仍然未知,未知字符串参与常见拼接也可能继续为NULL。布尔运算则有部分例外,需按三值逻辑而非“碰到NULL全都变空”处理。
缺失值会沿标量表达式传播
工时未知时,加10不能推断总工时;缺失字符串参与常见拼接也可能使结果缺失。NULL传播提醒程序先决定缺失的业务含义,而不是自动补零。并非所有函数或运算都同样传播,例如布尔短路真值规则和COALESCE有明确不同语义,须逐项理解。
缺失值会沿标量表达式传播保存独立卡片。
14.2.2 搜索允许为空的列
WHERE只保留条件为TRUE的行。等值和它的NOT条件都可能对NULL产生UNKNOWN,所以二者不是包含所有行的互补集合。查空值要用专门谓词。
WHERE只选择真值行
普通比较遇到NULL往往得到UNKNOWN。WHERE只选择TRUE,因此assigned_to等于123与其NOT条件都不选缺失负责人,二者并非全表互补。若业务需要“不是123且包括未指派”,应明确加入空值语义,而不是认为NOT自然包含所有其他情况。
WHERE只选择真值行保存独立卡片。
14.2.3 在查询参数中使用NULL
参数可绑定NULL,但普通等号不会因此变成空值判断。要让可空参数拥有明确匹配语义,需选择相应谓词或显式分支。
绑定NULL不改变比较语义
预处理语句参数绑定NULL本身通常合法,但普通等值比较仍按三值逻辑处理,不会自动匹配列中的NULL。必须先决定空参数代表查缺失、忽略条件还是某个业务状态,再用对应谓词或分支表达。不能把原文“不能传NULL”误读成绑定接口禁止NULL。
绑定NULL不改变比较语义保存独立卡片。
原书依据 · 1 处
14.2.4 避免上述问题
用-1等魔法值替代NULL,并没有消除特殊处理:它可能进入SUM/AVG,可能后来变成合法值,作为外键还会迫使建立没有实际含义的账号。NOT NULL应表达真实必填,而不是对缺失值的厌恶。
魔法值会污染聚合与关联
用-1等普通值标记未知工时,会让求和、平均自动把它当真值,查询必须处处排除。对账号外键再用占位ID,还可能被迫创建“没有人”账号。每列选择和记录不同魔法值会增加维护,值域扩展也可能冲突;应优先按真实语义表达缺失。
魔法值会污染聚合与关联保存独立卡片。
必填约束必须有业务含义
reported_by表示报告人,业务要求每条缺陷有真实报告者,可设为必填;assigned_to表示当前负责人,尚未指派可能合法,应允许相应缺失。是否NOT NULL由事实模型决定,不能为消除分支任意造默认人。默认值也必须有真实业务依据。
必填约束必须有业务含义保存独立卡片。
14.3 如何识别反模式
全名变空、否定条件漏计、魔法值后来变成合法值,都是缺失语义没有处理好的线索。测试需包含真正的NULL与边界组合,不能只用所有字段都有值的简单样本。
WHERE只选择真值行关联本项背景,实质主要讲解在对应位置。
魔法值会污染聚合与关联关联本项背景,实质主要讲解在对应位置。
可空字段测试要含缺失组合
测试可空字段应覆盖缺失、普通有效值及它们在拼接、比较、否定和聚合中的组合。只有全字段有值的数据会掩盖生产中真实缺失的路径。先写清预期业务语义,再核对数据库三值逻辑;测试成功也只证明实际覆盖的输入。
可空字段测试要含缺失组合保存独立卡片。
原书依据 · 1 处
14.4 合理使用反模式
文本导入导出可以用约定编码NULL,输入界面也可以转换缺失表示;关键是编码边界明确,而不是把魔法值永久混入业务列。如果缺失还要区分从未指派与已离任,应另外建模原因状态。
缺失编码只在边界转换
文本传输不能直接携带数据库内部NULL时,可约定专门标记,在导入边界还原为NULL;输入框也可按业务规则将空输入转成缺失。必须区分真实文本与编码标记,并保证往返不混淆。边界序列化不等于在业务列长期用魔法值代替NULL。
缺失编码只在边界转换保存独立卡片。
原书依据 · 1 处
一种NULL不能区分缺失原因
同样没有负责人,可能是从未指派,也可能是原负责人已离开。一个NULL只表达缺失,不能同时保存这些不同原因。若业务需要区分,应增加明确的状态或历史信息,而不是让一个缺失标记承担多重含义;具体模型按查询和生命周期决定。
一种NULL不能区分缺失原因保存独立卡片。
原书依据 · 1 处
14.5 解决方案:将NULL 视为特殊值
正确处理NULL需要采用TRUE、FALSE、UNKNOWN三值逻辑。下面把标量比较、布尔组合、检索谓词和默认值分别展开,避免用一种“空就是假”的直觉处理全部场景。
14.5.1 在标量表达式中使用NULL
两个未知年龄不代表相等,未知与已知年龄也无法确定大小。NULL等于NULL仍为UNKNOWN,这不是NULL与自身具有普通值不相等的身份规则,而是缺失信息无法支持比较。
缺失值会沿标量表达式传播关联本项背景,实质主要讲解在对应位置。
14.5.2 在布尔表达式中使用NULL
三值布尔逻辑并非所有含NULL的表达式都为空:FALSE AND UNKNOWN仍为FALSE,TRUE OR UNKNOWN仍为TRUE;其他不能确定的组合保留UNKNOWN。
三值逻辑保留可确定结果
UNKNOWN既不是TRUE也不是FALSE,但某些组合仍能确定结果。
| 运算 | 结果 |
|---|---|
| UNKNOWN AND TRUE | UNKNOWN |
| UNKNOWN AND FALSE | FALSE |
| UNKNOWN OR FALSE | UNKNOWN |
| UNKNOWN OR TRUE | TRUE |
| NOT UNKNOWN | UNKNOWN |
已确定的假可决定AND,已确定的真可决定OR;否定未知不会凭空补齐信息。
三值逻辑保留可确定结果保存独立卡片。
原书依据 · 1 处
14.5.3 检索NULL 值
IS NULL明确检测缺失;空值安全比较则能为两个可空值给出确定的相同/不同判断。给NULL加引号会变成普通字符串,类型转换有时巧合返回想要结果,仍是错误方法。
用专用谓词检测NULL
IS NULL直接判断表达式是否缺失,IS NOT NULL判断是否非缺失,二者返回确定真假。普通等号或不等号不能替代它们;带引号的NULL只是文本,可能触发报错或类型转换,偶然得到期望结果也不证明正确。
用专用谓词检测NULL保存独立卡片。
原书依据 · 1 处
空值安全比较明确相同与不同
IS DISTINCT FROM把两个缺失值视为没有差异,一个缺失一个非缺失视为不同,两个普通值按不等比较;其否定对应空值安全相等。可用于可空参数,但先确定业务上是否希望两个缺失视作相同。MySQL的空值安全等号与IS NOT DISTINCT FROM方向一致;产品支持须查版本,原书SQL Server不支持的判断已过时。
空值安全比较明确相同与不同保存独立卡片。
原书依据 · 1 处
14.5.4 声明NOT NULL 的列
业务上必需的报告时间、报告人和状态,应由NOT NULL表达;不应为每个必填字段强行发明默认值。缺失输入报错有时正是保护数据事实的正确行为。
必填约束必须有业务含义关联本项背景,实质主要讲解在对应位置。
14.5.5 动态默认值
COALESCE按顺序返回第一个非NULL值,可为当前查询提供显示默认值而不修改原始数据。对姓名拼接只替换缺失的中间片段,保留其他列的真实含义。
可空中间名使全名变空关联本项背景,实质主要讲解在对应位置。
查询默认值不必写回数据
仅为展示或单次计算处理缺失时,可用COALESCE选择第一个非NULL候选,而不把替代值写回存储。姓名中间名缺失可用合适空格片段代替,避免整串传播NULL。默认值必须符合该查询语境;把未知工时补0会改变平均等业务含义,不能无条件套用。
查询默认值不必写回数据保存独立卡片。
原书依据 · 1 处
第15章 模棱两可的分组
分组把多行归为一个结果,未分组列必须能从分组条件确定,或由明确聚合选出。最大日期与最大ID可以来自不同的行,宽松引擎返回某个值也不等于业务口径正确。关联子查询、连接、窗口等方案要先决定同日并列如何处理。
每产品最大报告日期与任意bug_id一起返回,可能把日期和另一行的ID拼在一起。MAX选择的是某个表达式值,不能自动为其他列选择同一条记录;还要处理日期并列。
最大值不自动携带同一行
按产品取最大日期时,同时返回未明确约束的bug_id,可能把一行ID与另一行日期组合。聚合表达式返回值,不自动返回“整条最大行”。若要对应记录,需按产品和最大日期回连,或明确排除同组更优行;日期并列时还要决定全保留还是用稳定规则选一条。
最大值不自动携带同一行保存独立卡片。
15.1 目标:获取每组的最大值
每组最大值与最大值所在行的其他字段是两个不同查询目标。只要日期时聚合足够,需要ID时还要保证字段来自被选中的行。
最大值不自动携带同一行关联本项背景,实质主要讲解在对应位置。
15.2 反模式:引用非分组列
歧义来自没有确定同一组内其他列应如何取值。下面的单值规则用于检查每个输出表达式是否已有明确含义。
15.2.1 单值规则
分组后每个输出表达式应当在该组上有确定的单值含义:分组键本身、明确聚合结果或由分组键能确定的属性。对任意未确定列,不能靠数据库猜测要哪一行。
分组输出必须有单值含义
分组把多行压成一组结果,每个输出表达式必须有明确取值:分组列在组内相同,聚合函数给出统计值,函数依赖可让部分其他属性也唯一确定。未满足这些条件的裸列通常会报错或产生实现相关结果。不能只因某份样例恰好相同就认定查询无歧义。
分组输出必须有单值含义保存独立卡片。
原书依据 · 1 处
15.2.2 我想要的查询
最大值可能并列,多个聚合也可能来自不同记录,而SUM/AVG根本不对应某一行。这些反例说明聚合结果不能推导任意其他字段来自哪条记录。
最大值不自动携带同一行关联本项背景,实质主要讲解在对应位置。
15.3 如何识别反模式
报错通常指出选择列既非分组键也非聚合。某些宽松模式允许裸列,但结果可能依实现;原书关于MySQL首行、SQLite末行的描述不能作为契约。DISTINCT则针对结果列组合去重,与无聚合分组在该例中有同样集合结果。
去重针对整个输出组合
DISTINCT按所有被投影列的组合去重,例如日期与报告人组合唯一,而不是分别去重每一列。没有聚合时,按同一列组GROUP BY也能在这个例子得到相同组合。二者都不保证输出顺序,执行成本也要看优化器,不能从语义相同推断计划必然相同。
去重针对整个输出组合保存独立卡片。
原书依据 · 1 处
宽松分组不是任意行契约
允许裸列的分组查询不能依赖“物理首行”或“末行”选择业务记录。现代MySQL ONLY_FULL_GROUP_BY会检查分组与函数依赖;SQLite对单个内置min/max有文档化特例,但并列和多聚合仍有限制。跨库业务应把选行规则明确写出来,不依靠旧版本偶然行为。
宽松分组不是任意行契约保存独立卡片。
原书依据 · 1 处
15.4 合理使用反模式
函数依赖表示某属性组确定后,另一属性也随之唯一确定。报告人ID引用Accounts的唯一身份时,同一ID可确定账号名;这与任意从多个缺陷中选择bug_id不同。引擎能识别哪些依赖仍需查支持范围。
函数依赖表示确定关系
若相同的X值总对应同一个Y值,就说X确定Y。主键确定该行其他属性是典型例子;按报告人ID分组时,若它连接到唯一账号,同组账号名也能确定。函数依赖来自键和业务约束,不能只由当前样本巧合推断;数据库是否接受省略分组列又是实现能力问题。
函数依赖表示确定关系保存独立卡片。
15.5 解决方案:无歧义地使用列
解决方向是让每个选择列的来源明确:只保留需要的统计、按最大值回连,或按完整排序规则确定目标行。
15.5.1 只查询功能依赖的列
如果业务只要最新日期,删除bug_id等不需要的歧义列就是最简单修复。不要为没有要求的整行身份引入额外查询复杂度。
只取业务需要的统计字段
报告只需要每组最新日期时,返回分组键与最大日期就足够,去掉无确定含义的其他列。先确认输出需求,再决定是否承担查对应整行、并列处理与额外连接的成本。简化查询应以满足真实任务为准,而不是把需要的字段偷偷丢掉。
只取业务需要的统计字段保存独立卡片。
原书依据 · 1 处
15.5.2 使用关联子查询
可以保留那些同组中不存在更新日期记录的候选行。相关子查询按外层行建立条件,并不等于物理执行一定逐行重跑;优化器可能改写。并列最大日期会保留多行。
排除同组更优行选最大
对每条候选记录,检查同一产品是否存在日期更晚的记录;不存在则该记录属于最大日期集合。NOT EXISTS表达反向存在性,而不是任意取一行。日期并列会保留多条,若只要一条,应再定义稳定次序;实际执行是否逐行相关求值由优化器决定。
排除同组更优行选最大保存独立卡片。
原书依据 · 1 处
15.5.3 使用衍生表
先聚合每产品最大日期,再按产品及日期与明细回连,可取得真正对应的所有记录。若要在并列日期中选最大ID,必须仍保留产品关联;原文第二个示例漏掉产品连接,可能串组。
先聚合再按完整组键回连
先得到每产品最大日期,再用产品ID和日期两个条件连接明细,就能找出对应记录。最大日期并列时自然返回多行。若再按ID破同分,也不能丢掉产品关联;原文第二例只按日期连接,可能把别的产品同日Bug混入,不能原样使用。
先聚合再按完整组键回连保存独立卡片。
原书依据 · 1 处
15.5.4 使用JOIN
外连接也能表达“没有更优行”:在同产品内寻找日期更晚,或同日期ID更大的记录,再保留找不到匹配的候选。这样把并列日期的选择也纳入同一规则。
反连接按完整顺序破同分
选每产品最新一条时,先定义完整优先级,例如日期越晚越优,同日期ID越大越优。把候选与同组更优记录外连接,仅保留找不到更优记录者,就能明确选择唯一赢家。规则依赖键唯一及日期有效性;JOIN或NOT EXISTS谁更快必须实际测量,不能保证某形式永远更好。
反连接按完整顺序破同分保存独立卡片。
原书依据 · 1 处
15.5.5 对额外的列使用聚合函数
分别MAX日期和MAX ID可以各自满足单值规则,却不保证它们来自同一行。只有确有ID随日期单调的约束时,这种捷径才成立;插入顺序也不必等于报告时间。
分别取最大不能保证同一行
一组中最大日期与最大ID可能来自不同行,分别聚合只解决标量唯一,没有解决对应关系。除非业务与约束能保证两者顺序一致,否则应按完整组键回连或明确选行。即使ID递增,补录旧日期或修正日期也可能打破这种假设。
分别取最大不能保证同一行保存独立卡片。
原书依据 · 1 处
15.5.6 连接同组所有值
若需求是返回整组ID而非对应最大日期的ID,可以字符串聚合;它表达的是集合展示,不负责选择某条记录。输出长度、顺序与产品函数都要另核对,不能拿列表反推最大行。
组内列表聚合不选择最大行
GROUP_CONCAT等把一组值连接成一个结果,可用于展示该组全部ID。它不说明哪个ID对应最大日期,结果顺序和长度限制也需明确。查询时拼出列表与在基础表里长期用逗号编码关系不同;前者是输出形式,后者改变可约束的数据模型。
组内列表聚合不选择最大行保存独立卡片。
原书依据 · 1 处
第16章 随机选择
随机取一行首先是概率与样本边界问题,其次才是性能。随机排序、随机键、后继键和随机偏移分别依赖连续键、均匀性、稳定顺序或全表扫描等条件。应说明稀疏键和并发快照怎样影响选择,不能只以语句短作为正确依据。
随机广告查询在小样本中正常,生产数据增大后却成为瓶颈。测试规模与真实访问方式不一致,会掩盖随数据量增长的开销。选择抽样算法时还要明确等概率是否是业务要求。
样本规模会掩盖随机查询成本
随机广告查询在少量样本上很快,不代表能扩展到生产规模。若算法每次都处理全部候选,增长后的总成本才会显现。诊断需要有代表性的规模与分布,并把“随机返回”进一步说明为是否需要每条等概率;性能与抽样正确性要同时评估。
样本规模会掩盖随机查询成本保存独立卡片。
16.1 目标:获取样本记录
随机样本用于轮播、审核、任务分配及测试,通常无需先把整张表搬到应用。先定义样本大小和概率目标,再选择数据库或应用侧算法。
抽样先明确目标分布
抽样不是简单要求“看起来会变”:轮播、审核或测试可能需要不同样本数量和概率保证。先定义候选全集、每条等概率是否必要以及数据变化边界,再比较查询与传输成本。普通伪随机抽样不等于安全用途随机性;原书不展开随机源实现。
抽样先明确目标分布保存独立卡片。
16.2 反模式:随机排序
按随机值排序再取一行,通常要为许多候选计算随机键并选择最前者,普通固定列索引无法直接预存这次随机顺序。LIMIT一行不意味着只处理一行;实际可能用部分排序,不能把排序等同于物理移动整张表。
随机排序为少量结果处理全集
按每行随机键排序再取一行,实现直观,但候选通常仍需被扫描和赋随机键,固定列索引无法预存每次变化的顺序。即使优化器只保留前若干项,也不等于只访问这些行。大候选集下成本随规模增长,不能靠返回行数小判断便宜。
随机排序为少量结果处理全集保存独立卡片。
原书依据 · 1 处
16.3 如何识别反模式
随机返回慢、取全表后应用抽样耗内存,以及某些记录出现明显更频繁,分别提示访问成本或概率设计问题。缓存未必修正算法的分布语义。
样本规模会掩盖随机查询成本关联本项背景,实质主要讲解在对应位置。
抽样先明确目标分布关联本项背景,实质主要讲解在对应位置。
16.4 合理使用反模式
候选规模很小且上限可信时,随机排序可能是最简单、足够快的办法。为了少量固定候选引入复杂抽样基础设施,未必有实际收益。
小候选集可接受随机排序
若候选集合确实小且增长受控,随机排序的绝对成本可能可接受,实现简单也有价值。先用实际规模与调用量验证,而不是因理论上要处理全集就一律替换。团队成员或短固定列表只是示例,不能把某组织人数永远不增长当作证明。
小候选集可接受随机排序保存独立卡片。
原书依据 · 1 处
16.5 解决方案:没有具体的顺序……
替代抽样方法在键连续性、等概率、内存与访问次数之间取舍,不能笼统说全部既高效又分布相同。下面逐一核对这些前提。
16.5.1 从1到最大值之间随机选择
从完整整数键域等概率选ID再精确查行,免去全体随机排序;但键必须覆盖范围且无空洞。删除产生缺号时可能查不到,空表也要单独处理。
按键域抽样要求连续完整
若所有候选键恰为1至M且每个都存在,等概率选这个闭区间的整数并按主键查行,可让每行等概率。键有空洞则可能无结果,候选还有额外筛选时也可能不匹配。随机整数生成须保证端点正确,空表与并发变化不能忽略;原CEIL表达式的零端点风险需另核对。
按键域抽样要求连续完整保存独立卡片。
原书依据 · 1 处
16.5.2 选择下一个最大值
随机阈值后取第一个更大有效键,能跨过缺口,却把缺口对应的概率集中给后面的记录。它只适合允许这种偏差的任务。
向上补洞会造成抽样偏差
随机阈值后取不小于它的最小有效键,会让长缺口后的记录承担更大概率。教学例:有效键为1和4,阈值等概率取1至4,则键1概率1/4,键4概率3/4。它减少无结果,却不再是均匀按行抽样;仅在允许偏差时考虑,不能用于宣称公平分配。
向上补洞会造成抽样偏差保存独立卡片。
原书依据 · 1 处
16.5.3 获取所有的键值,随机选择一个
取全体键再均匀选一个,能避开键缺号偏差且少传整行内容,但键列表仍随候选数增长,需要内存和两次访问。
键列表抽样节省整行不省规模
先获取候选主键列表,等概率选列表位置,再按键读完整行,可适应非连续ID并减少首轮传输宽度。它仍要传输和保存全部候选键,并通常执行第二次访问;大型集合可能耗尽应用内存,两次访问间的删除也会使选中行消失。适合逻辑简单、规模适中的场景。
键列表抽样节省整行不省规模保存独立卡片。
原书依据 · 1 处
16.5.4 使用偏移量选择随机行
可按总行数选择随机位置,再用稳定次序取该位置。偏移必须在0至N减1而不是含N;原ROUND写法会带来端点和概率问题,计数与读取还要面对并发变化。大偏移也未必便宜。
随机偏移须排除越界端点
N行候选的零基偏移应等概率取0至N−1,不能把N也包含;原ROUND生成0至N会越界并使端点权重不同。还应使用确定的候选顺序及一致数据视图,避免计数后变化造成漏取或偏差。OFFSET或窗口编号仍可能处理大量前置行,省去随机排序不等于常数成本。
随机偏移须排除越界端点保存独立卡片。
原书依据 · 1 处
窗口编号为结果行建立次序
ROW_NUMBER在明确的ORDER BY顺序内为结果行依次编号,可用编号选择某个位置。它给非连续业务键建立连续位置,但需要定义稳定顺序,处理并列时可加唯一键。编号生成与取第N行也有成本,不是只写窗口函数就无需扫描或排序。
窗口编号为结果行建立次序保存独立卡片。
16.5.5 专有解决方案
原生采样可能按页或块工作,返回数量也可能只是近似。采用TABLESAMPLE或SAMPLE前,要查抽样单位、分布与行数保证,不能默认它们都等价于均匀随机返回一行。
原生采样需核对抽样单位
数据库专有采样可减少手工处理,但语法中的“行数”未必保证精确返回那么多行。以SQL Server SYSTEM采样为例,页是采样单位,同页记录会一起被选,行数是近似目标。要核对候选范围、分布和空样本可能性,再决定是否满足均匀单行抽样;Oracle示例也需查实际语义。
原生采样需核对抽样单位保存独立卡片。
原书依据 · 1 处
第17章 可怜人的搜索引擎
子串匹配、按词检索与相关性搜索解决不同问题。逐行模式匹配可能在规模、边界和排序上不足,倒排结构把词项映射到文档,全文引擎还需处理分词、同步与权限。现代数据库自带能力已有变化,选型必须核验具体版本。
知识库搜索需要的不只是包含某段字符,还可能包括词形变化、多个词的组合和相关度排序。随着文档增多,分类与逐条文本匹配难以同时满足这些语义和速度要求。
全文搜索先定义语言语义
搜索crash时是否也匹配crashed、crashes和crashing,匹配多个词怎样组合,结果如何按相关度排序,都是搜索需求的一部分。字符子串匹配不自动具备这些能力。先定义词、词形和排序的目标,再选择分词与索引方案,不能仅凭查询返回了一些包含字符的文本就认为搜索正确。
全文搜索先定义语言语义保存独立卡片。
17.1 目标:全文搜索
关系查询通常按列值组织比较,而全文检索要分析值内部的词项。列的原子性是相对于数据模型和所需操作的概念,不等于SQL绝对不能高效查询内部内容。
原子值与内部检索需求不同
关系模型把一列的一个值作为当前操作中的整体,全文检索却关注文本内部的词项及位置。需要内部结构查询时,应明确新的表达和索引,而不是假定普通键比较已覆盖它。原子性取决于建模需求,不代表SQL对任何子串查询必然低效或不精确。
原子值与内部检索需求不同保存独立卡片。
原书依据 · 1 处
17.2 反模式:模式匹配断言
两端通配LIKE与普通正则通常需要检查很多文本内容。它们查字符子串,不天然查单词:搜索one可能匹配money等。给两端加空格也会漏掉标点和文本边界,单词边界规则仍需明确。
任意子串不匹配普通键顺序关联本项背景,实质主要讲解在对应位置。
子串匹配不等于单词搜索
搜索one的任意子串会同时命中money等无关词;两边加空格又可能漏掉行首、行尾和标点邻接。应按需要选择单词边界、分词和词形规则,而不是不断叠加例外。字符包含、词项匹配和词形检索是不同语义,性能优化不能偷换结果定义。
子串匹配不等于单词搜索保存独立卡片。
原书依据 · 1 处
17.3 如何识别反模式
围绕用户词不断写复杂正则、文档增长后搜索恶化,提示需求可能已经超过简单模式匹配。信号仍需与查询频率及语义复杂度一起判断。
全文搜索先定义语言语义关联本项背景,实质主要讲解在对应位置。
17.4 合理使用反模式
很少执行、数据少或语义简单的临时查询,可以合理使用模式匹配。为它长期维护专门索引也有成本,应比较实际收益。
低频简单搜索可用模式匹配
简单、低频或小规模的查询,用LIKE或正则可能已足够;专门索引需要空间和更新维护,不必为每个临时请求建设搜索系统。前提是匹配语义正确、实际延迟可接受。合理例外不代表把高频增长型全文需求继续交给无界扫描。
低频简单搜索可用模式匹配保存独立卡片。
原书依据 · 1 处
17.5 解决方案:使用正确的工具
全文检索可使用数据库扩展、独立引擎,或为重复搜索预存结果/词项关系。选择不仅看查询速度,还要承担索引同步和语义一致性。
17.5.1 数据库扩展
数据库全文扩展把词项索引与SQL结合,但配置、查询语法和同步方式各异。原书依次介绍MySQL、Oracle、SQL Server、PostgreSQL和SQLite;这些代码都属于出版时版本,应按实际数据库重新核对,不能复制旧部署步骤。
MySQL全文索引与布尔检索
MySQL FULLTEXT为文本列建立全文访问路径,MATCH配合AGAINST查询对应索引列,布尔模式可表达必须包含或排除词项。词项检索不等于任意子串,结果取决于解析和配置。原书主要讲MyISAM,MySQL 8.0官方说明InnoDB也支持;使用前应按引擎核对差异。
MySQL全文索引与布尔检索保存独立卡片。
原书依据 · 1 处
Oracle文本索引按用途区分
原书的Oracle文本路线分别面向:CONTEXT的一般文本检索、CTXCAT的短文本与结构化条件、CTXXPATH的XML路径,以及CTXRULE的文档分类规则。它们的查询接口和同步条件不同,不能互换。此处保留2011年用途比较,具体类型是否仍支持及维护方式应查目标版本,不把同步索引等同于重建整个索引。
Oracle文本索引按用途区分保存独立卡片。
原书依据 · 1 处
SQLServer全文同步
SQL Server全文检索可配置语言及词库,并以CONTAINS等接口查询;原文通过创建目录、登记列、启用变化跟踪和后台填充说明准备过程。后台填充需要时间,修改业务表不等于搜索结果立刻可见。旧sp_fulltext系列示例应按实际版本迁移,不直接当当前部署命令。
SQLServer全文同步保存独立卡片。
原书依据 · 1 处
文本向量保存可检索词项
PostgreSQL把文本转换为tsvector词项表示,再用查询词项表达式匹配。原文把原始文本与搜索向量分开保存,强调二者必须同步。词项表示服务搜索,不是原文替代品;如何生成、更新及分词配置必须与查询语义一致。
文本向量保存可检索词项保存独立卡片。
原书依据 · 1 处
GIN为文本向量提供访问路径
PostgreSQL示例在tsvector列上使用GIN,为词项到匹配记录的访问提供索引路径,再以全文匹配操作查询。保存搜索向量和建立索引是不同职责:前者定义可搜索内容,后者加速访问。索引并不替应用决定词形、语言和同步策略。
GIN为文本向量提供访问路径保存独立卡片。
原书依据 · 1 处
SQLite全文检索使用虚拟表
SQLite全文扩展通过虚拟表维护可检索文本,以MATCH查询,再按文档标识与业务行关联。原文FTS3把文档复制入另一表,因而后续更新也需同步。今天不能只按书中FTS1至3与重新编译步骤判断能力:官方已有FTS5,实际是否内置要核对运行库,不应盲目重编译。
SQLite全文检索使用虚拟表保存独立卡片。
原书依据 · 1 处
17.5.2 第三方搜索引擎
独立引擎把文本检索从关系数据库中分离,接口更可移植,但文档构建、更新和删除需要同步。自建倒排关系也把词项映射到文档,不过原文按用户词首次扫描再缓存的方案仍有首次成本、并发和失效问题。
Sphinx以数据源建索引
Sphinx在原书中通过配置SQL数据源、文档ID、筛选/排序属性和文本字段构建独立搜索索引,再按命中ID取回记录。它把检索访问与关系存储分开,需维护索引更新。原文对增量更新和旧命令的限制是出版时状态,不能作为当前版本选型结论。
Sphinx以数据源建索引保存独立卡片。
原书依据 · 1 处
Lucene需要应用提供文档
Lucene提供文本索引与查询能力,不自动把SQL库中的记录变成搜索文档。应用要构建文档并在新增、修改、删除时同步索引。它更像检索引擎组件而非完整业务搜索服务;将数据放入关系库不表示Lucene索引也已更新。
Lucene需要应用提供文档保存独立卡片。
原书依据 · 1 处
Solr提供搜索服务接口
Solr以服务接口暴露基于Lucene的索引和查询能力,使不同语言的应用通过请求使用检索服务。它减少直接嵌入Java API的需要,但仍要定义文档来源和同步。原书DataImportHandler是历史集成线索,不能直接假定当前版本仍包含相同模块。
Solr提供搜索服务接口保存独立卡片。
原书依据 · 1 处
倒排索引把词映射到文档
倒排索引以词项为入口,记录包含它的文档集合;一篇文档有多个词,一个词可出现在多篇文档。原文用Keywords和BugsKeywords交叉表表达这种多对多关系。它把重复搜索从逐文档匹配转成查词项关联,仍须定义分词、词形及更新维护。
结构图:
erDiagram
Keywords ||--o{ BugsKeywords : 词项
Bugs ||--o{ BugsKeywords : 文档
Keywords {
bigint keyword_id PK
string keyword UK
}
BugsKeywords {
bigint keyword_id PK,FK
bigint bug_id PK,FK
}
图表达词项到文档的倒排关系,不替代词义分析。
倒排索引把词映射到文档保存独立卡片。
原书依据 · 1 处
首次搜索缓存仍有维护成本
原文自建方案首次搜索某词时扫描文本并记录命中文档,后续复用关系;它是按需建立的检索缓存,不是首次就免扫描。插入新文档需匹配已知词,修改或删除也要更新关系;并发首次建词、失败与输入模式转义还须处理。热词收益不能证明所有查询都更快。
首次搜索缓存仍有维护成本保存独立卡片。
原书依据 · 1 处
搜索统计记录词项需求
在关键词表为每次搜索累计次数,可以观察实际词项需求,决定哪些词值得预热或改进。计数记录的是请求频率,不是匹配质量;还要考虑重复请求及统计范围,不能把高频直接等同于内容更相关。
搜索统计记录词项需求保存独立卡片。
原书依据 · 1 处
第18章 意大利面条式查询
将多个统计或集合目标塞进一条大查询,容易混淆结果粒度并重复计数。拆成语义单一的查询或先聚合再连接,可以让口径和维护更明确。本章印刷示例的连接条件与其笛卡儿积说明并不一致,下面分别解释真实语句和作者意图。
报表把产品数、开发人数、平均修复数与客户报告数混进一个连接聚合,容易混淆行数、实体数和分母。先定义每项指标的事实范围,再决定能否共享查询。
报表指标先定义统计口径
同一张报表可能需要项目数、实际修复人员数、每人修复均值和客户报告的已修复缺陷数。它们的对象、筛选与分母不同,不能把连接后的COUNT统称为数量。先定义每项口径,再单独验证;否则一条复杂查询可能同时把实体重复和业务条件混在一起。
报表指标先定义统计口径保存独立卡片。
18.1 目标:减少SQL查询数量
查询次数不是总工作量的可靠替代指标。少发一次请求可以降低往返,但把不相干任务强行合并可能增加连接与维护成本。
查询次数不等于总体成本
把任务压到单条SQL只能减少请求次数,不能自动减少扫描、连接、排序或开发维护成本。比较方案时同时看网络往返、数据库工作、结果正确性和理解难度。多个简单查询有时更合适,一条可优化的集合操作也可能更好;不能把任一形式当作固定优劣。
查询次数不等于总体成本保存独立卡片。
18.2 反模式:使用一步操作解决复杂问题
SQL能在一条语句中表达很多工作,不代表必须这样做。表达能力与某个任务的合适分解方式是两回事。
查询次数不等于总体成本关联本项背景,实质主要讲解在对应位置。
18.2.1 副作用
两个独立子集在同一父对象下同时连接,若没有进一步约束就可能互相组合:12个已修复与7个打开缺陷可形成84对,聚合因此放大。不过本节印出的SQL把两侧都按同一bug_id连接,不能产生描述中的84对;这是示例与解释不一致。
独立子集连接会放大计数
同一父对象分别连接两个独立一对多集合时,每侧行会与另一侧组合;12项与7项可产生84对,直接COUNT会重复统计。应检查连接后的行粒度,先分别聚合或拆分任务。此为原文图示意图;印出的查询却用相同bug_id连接互斥状态,不会产生所述笛卡儿积,不能把84当该SQL实测。
结构图:
flowchart TD
P[同一父对象] --> A[12项独立集合]
P --> B[7项独立集合]
A --> C[两侧组合84对]
B --> C
C --> D[直接计数重复]
图表示两侧独立组合的条件,不表示书中错误SQL的真实计划。
独立子集连接会放大计数保存独立卡片。
原书依据 · 1 处
18.2.2 那好像还不够??
大查询不仅可能算错,还会使修改、调试和优化更困难。复杂度应由真实依赖决定,不能只因希望少一次调用就让所有统计共享同一中间行集。
查询次数不等于总体成本关联本项背景,实质主要讲解在对应位置。
18.3 如何识别反模式
异常大的合计、难以修改的单查询,以及不断追加DISTINCT,提示需要检查连接粒度。去重有时掩盖重复结果,却不能说明原始关系和指标已经正确。
DISTINCT不是粒度修复
连接后计数异常时,先查一行代表什么,以及哪条关系导致重复。随意加DISTINCT可能隐藏膨胀,也可能删掉业务上应保留的重复事实,还增加去重成本。只有明确要统计不同实体或组合时,去重才有正确语义;它不是错误连接的通用修复。
DISTINCT不是粒度修复保存独立卡片。
原书依据 · 1 处
18.4 合理使用反模式
工具只接受一个结果集,或需要对合并结果统一排序时,复杂单查询可能有合理动机。此时仍须检查结果等价与成本,也可以评估工具或报表边界是否需要调整。
单结果集限制需显式权衡
报表组件若只接收一个查询结果,可能迫使多项统计在同一语句组合;统一排序也可能适合交给数据库。应把这个工具约束当作设计条件,选择可解释且结果正确的组合,必要时拆报表或调整接口。不能仅因复杂就拆,也不能因工具方便就忽略维护成本。
单结果集限制需显式权衡保存独立卡片。
原书依据 · 1 处
18.5 解决方案:分而治之
选择更简单方案的前提是能满足同样目标与结果。简约律在这里用于减少无必要的耦合,而不是以代码短为准忽略业务含义。
简约以结果等价为前提
比较能满足同一任务的方案时,优先选择更容易理解、验证和维护者。简单不等于字符少,也不等于总是拆分或总是合并。先证明输出和边界条件一致,再比较成本,才能避免用“简洁”掩盖口径变化。
简约以结果等价为前提保存独立卡片。
原书依据 · 1 处
18.5.1 一步一个脚印
分别统计已修复与打开缺陷,可以让每个查询只回答一个问题,再在应用或明确的组合层汇总。若报表要求同一时间点,还应保证多次查询看到一致数据。
独立统计可以分别求值
无逻辑关联的统计任务可各自查询并验证,再组合结果,避免不同子集互相乘开。需求新增时也可单独扩展。若多个指标必须代表同一时点,要用合适的一致视图或事务;拆分只减少表达耦合,不自动保证跨查询一致性或性能更优。
独立统计可以分别求值保存独立卡片。
原书依据 · 1 处
18.5.2 寻找UNION 标记
若仍要一个结果集,可在各分支先完成独立统计,再以兼容列的联合表达输出,并增加稳定的指标标签。标签不能依赖可能为空的连接字段。
联合结果须对齐列语义关联本项背景,实质主要讲解在对应位置。
18.5.3 解决老板的问题
拆开指标后分别定义统计对象:产品、不同修复人员、每个修复人员的缺陷数再求平均,以及客户报告的已修复缺陷。还要确认活跃产品是否可等同于Products全部行,以及均值是否包含零修复人员。
报表指标先定义统计口径关联本项背景,实质主要讲解在对应位置。
18.5.4 使用SQL自动生成SQL
重复的简单变更可由程序生成待检查语句,减少手工抄写;但生成与执行应区分。每行目标、值格式、转义和整体失败处理仍需验证,不能把输出文本当作已完成更新。
代码生成减少机械重复
代码生成用程序输出新的可执行或可编译文本,适合大量结构相似但值不同的操作。原书按设备聚合最后使用日期,再生成逐设备更新语句。生成结果仍须核对目标、转义、事务和影响行数;它减少重复输入,不免除业务验证,也不表示已执行。
代码生成减少机械重复保存独立卡片。
原书依据 · 1 处
第19章 隐式的列
列名与顺序都是查询和调用方的接口。星号展开、位置插入以及重名列覆盖,会让表结构变化成为隐蔽的行为变化。显式列清单使依赖可见;需要动态投影时,也应采用目标引擎明确支持的方式并说明契约。
书名与作者称谓都叫title,SELECT星号后按列名映射可能让作者空称谓覆盖书名。显式投影并给不同角色起别名,可以消除结果名称歧义。
同名结果列可能互相覆盖关联本项背景,实质主要讲解在对应位置。
19.1 目标:减少输入
星号省略输出列清单,未写列名的INSERT则依赖表定义顺序。它们节省输入,却把结果及输入契约隐藏在当前模式中。
隐式列让接口依赖表结构
SELECT星号把输出列交给当前表结构,省略INSERT列清单则按表定义顺序分配值。两者让代码与模式发生隐式耦合:新增、删除或调序可能改变接口。显式列清单不是纯风格要求,而是让读写依赖可见;临时查询可有不同权衡。
隐式列让接口依赖表结构保存独立卡片。
19.2 反模式:捷径会让你迷失方向
少写列名虽然更短,但会让后续结构变更、数据映射和传输开销更难预测。
19.2.1 破坏代码重构
新增列可让隐式INSERT参数数目不匹配,删列可让按序号取值读到别的字段。某些错误会立即报出,另一些则静默污染含义。
列序变化会破坏位置映射
省略插入列名时,值按表定义顺序分配;按结果序号读取星号输出时,也依赖原来的列位置。模式变更可能立即报参数错误,也可能让旧位置指向另一字段。应显式声明输入输出列并核对客户端映射;原文所称无法预测“行数”应为列结构,而非查询结果行数。
列序变化会破坏位置映射保存独立卡片。
19.2.2 隐藏的开销
星号会把不需要的字段也传给客户端,宽字段和高并发可放大网络与解码成本。ORM默认行为同样要检查,不能因对象映射方便就忽略实际投影。
投影多余列放大传输成本
返回更多或更大的字段,会增加网络传输、客户端解码和内存压力;并发和调用频率会放大总量。明确只取所需列可缩小结果,也可能帮助覆盖访问。具体收益需测量,不能把“SELECT星号”本身等同于所有查询瓶颈,ORM生成的投影也要核对。
投影多余列放大传输成本保存独立卡片。
19.2.3 你请求,你获得
原书说不能用“除几列外全部”的查询,是当时常见SQL接口的概括;部分现代产品有EXCLUDE或EXCEPT扩展。即使有简写,接口是否应随表结构自动变化仍要有意决定。
隐式列让接口依赖表结构关联本项背景,实质主要讲解在对应位置。
19.3 如何识别反模式
模式变更后旧调用遗漏、传输内容只有小部分用于展示,提示隐式依赖与过量投影。诊断时应查看实际返回字段及客户端使用,而不是只看查询文本长短。
列序变化会破坏位置映射关联本项背景,实质主要讲解在对应位置。
投影多余列放大传输成本关联本项背景,实质主要讲解在对应位置。
19.4 合理使用反模式
临时检查或确实需要动态适配全部字段时,星号可以合理使用。给星号加表别名可限定来自哪张表,但仍要防止结果同名与结构变化;短查询文本的收益也需与更宽响应比较。
通配符适合明确的动态边界
临时查询、探索当前数据或真正需要随模式变化的工具,可使用星号;也可用表别名限定某一表全部列,其他表显式选取。应明确接受哪些结构变化,并处理同名映射和大字段。请求文本较短不代表网络总量更小,响应宽度往往更值得检查。
通配符适合明确的动态边界保存独立卡片。
原书依据 · 1 处
19.5.1 预防错误
显式列让删除或重命名所需字段时立即报错,增加无关列则不改变输出。插入时未列字段是否默认、可空或报错,仍由列定义决定。
显式契约让错误尽早暴露
显式输入输出列声明了调用依赖。所需字段被删时立即报错,比继续按旧位置误读更易定位;新增无关列也不自动进入结果。插入未提供的列只有在有合适默认值、允许空值或可生成时才可省略,必填无默认仍应失败。尽早出错保护真实契约,不是让所有变更静默兼容。
显式契约让错误尽早暴露保存独立卡片。
原书依据 · 1 处
19.5.2 你不需要它
列清单也促使程序只选择需要的信息,降低每次请求无用数据。高频查询尤其应看累计传输量,而不是只看单次开发测试是否显得快。
投影多余列放大传输成本关联本项背景,实质主要讲解在对应位置。
19.5.3 无论如何你都需要放弃使用通配符
一旦要计算、别名或排除字段,就需要理解各列语义。显式清单让这种依赖更早可见,后续维护少受隐式打包形式束缚。
隐式列让接口依赖表结构关联本项背景,实质主要讲解在对应位置。
第四部分 应用程序开发反模式
应用部分把关注点扩展到密码、SQL注入、标识、错误处理、工程规范和对象职责,说明数据库使用必须与整个软件边界协调。
第20章 明文密码
认证系统需要验证口令而非恢复口令。明文、可逆密文和快速摘要各有风险,盐不能替代适合密码的慢哈希。传输、重置令牌、日志和服务凭据又是不同边界;本章历史算法参数不能直接作为当前安全配置。
攻击者冒充用户,利用客服的压力与可读密码取得账号秘密。忘记密码的支持流程应核验控制权并重置,而不是把现有密码念给来电者。
客服可读密码放大冒充风险
来电者声称自己是某员工并施压,客服从数据库读出密码后告知,却把秘密交给冒充者。问题既有身份核验失误,也有系统允许直接恢复原密码。应把遗忘处理设计为验证后的重置,不能把自报姓名、声音或投诉压力当身份凭据。
客服可读密码放大冒充风险保存独立卡片。
原书依据 · 1 处
20.1 目标:恢复或重置密码
密码遗忘流程通常依赖用户能控制注册的恢复渠道。这个前提应核实,恢复渠道不可访问时要有另外设计的身份恢复流程。
密码恢复依赖可信恢复渠道
邮件重置的依据是用户控制预先登记的邮箱,而不是知道账号名。若邮箱失守或已不可访问,这条恢复路径的可信前提就变化了。重置只能恢复服务访问权,不能证明任意来电者身份;应明确渠道绑定和变更规则,不随意把重置发送到临时提供的新地址。
密码恢复依赖可信恢复渠道保存独立卡片。
原书依据 · 1 处
20.2 反模式:使用明文存储密码
可把原密码发回给用户,意味着服务保留了能恢复它的表示,扩大了系统和支持人员可接触的秘密。应把验证与找回原文分开。
验证密码不需要恢复原文
登录只需判定提交凭据是否正确,不必让服务能读回原密码。若支持发送原密码,系统已保留可恢复表示,泄露面包括数据库、备份和支持流程。采用适当的密码验证存储与重置流程,可满足遗忘后的访问恢复需求,而不把原文当作可检索业务字段。
验证密码不需要恢复原文保存独立卡片。
20.2.1 存储密码
明文密码不仅可能从数据库本体泄露,也可能经过SQL文本、查询日志、网络和备份传播。只保护一个表的访问权限不足以覆盖整个秘密生命周期。
密码泄露面包括日志与备份
密码明文进入SQL文本后,可能出现在执行日志、诊断记录或未保护传输中;数据库明文存储还使备份成为泄露面。应减少明文经过的组件,限制日志与备份访问并保护传输。风险来自实际可读路径,不代表任何网络抓包都能解开正确使用的TLS。
密码泄露面包括日志与备份保存独立卡片。
原书依据 · 1 处
Wireshark分析流量
Wireshark是原书用来说明网络流量可被观察的分析工具。它帮助捕获和检查协议数据,不等于自动破解安全传输;可见内容取决于捕获位置、权限和加密边界。其与本章的关系是提醒不要把明文秘密暴露在可观察通道。
Wireshark分析流量保存独立卡片。
原书依据 · 1 处
20.2.2 验证密码
认证内部可能需要区分账号不存在与密码错误以监控失败,但对外差异会暴露账号是否存在。内部诊断与外部响应应分开,日志也不能记录凭据原文。
认证诊断与对外提示分开
内部可区分账号不存在、密码验证失败等原因以监控和处置,但对外应采用一致的失败提示,并考虑时间差,避免账号枚举。原书鼓励区分失败原因,不意味着要把细节直接告诉请求者。冻结或限流也须防止被滥用为拒绝服务,不能仅凭一次失败推断攻击。
认证诊断与对外提示分开保存独立卡片。
原书依据 · 1 处
20.2.3 在E-mail中发送密码
发送原密码会使邮件中的副本长期保留,可能被转发、备份或由其他入口读取。安全查看邮箱也不消除邮件内容本身是秘密副本的问题。
邮件不应携带原密码
把原密码发邮件,会把可重复使用的登录秘密复制到邮箱及其转发、备份和终端中。即使传输加密也无法控制之后所有副本的保存和访问。遗忘流程应验证恢复渠道后允许设置新密码,而不是再次分发旧秘密;重置链接本身也需保护。
邮件不应携带原密码保存独立卡片。
原书依据 · 1 处
20.3 如何识别反模式
能显示或恢复原密码是应调查存储和支持流程的强信号,但攻击者实际获取能力还取决于权限与密钥边界。设计重点是减少这种可恢复性和暴露面。
验证密码不需要恢复原文关联本项背景,实质主要讲解在对应位置。
20.4 合理使用反模式
用户认证密码通常不需读回,访问第三方服务的凭据却可能必须以可用形式提供,后者需要可逆机密保护与密钥管理。内部系统也不能因当前用户可信就省略威胁评估,使用边界会变化。
可读服务凭据不同于登录验证
访问外部服务时,程序可能必须提供实际凭据,单向密码验证表示不能替代这类秘密。需要可逆保护及受控的密钥、读取和轮换边界;用户登录则通常只需验证而不读回。区分责任再选择存储方案,不能把“登录应哈希”机械扩展为所有凭据都无法使用。
可读服务凭据不同于登录验证保存独立卡片。
原书依据 · 1 处
自报身份不等于证明身份
提供用户名或自报姓名只是声明身份;凭据验证用于证明控制了相应身份。两步应分清,不能把一个登录框或内部网络当作可靠验证的证明。原书关于小型内部系统的例外依赖环境假设,边界扩大时必须重新评估,不能据此默认无攻击风险。
自报身份不等于证明身份保存独立卡片。
原书依据 · 1 处
20.5 解决方案:先哈希,后存储
登录可以通过比较提交值的验证表示来确认密码,而无需从数据库恢复原文。原书后续给出快速SHA示例,但它不能作为今天完整的密码存储方案。
20.5.1 理解哈希函数
密码哈希用于验证而非可逆解密。固定摘要不能唯一保留所有输入信息,但攻击者仍可猜候选密码并比较;碰撞攻击与从特定摘要恢复密码也不是同一问题。原文把SHA-1弱点解释成推回输入并不准确。
哈希不可逆不等于无法猜解
密码哈希把输入映射成摘要,通常无法由摘要直接恢复原文,但攻击者可以猜输入再计算比较。SHA-256摘要为256位,即32字节,十六进制编码为64字符;编码长度不是原始摘要字节数。碰撞与原像恢复是不同安全性质,原文对SHA-1“推回输入”的概括不可照搬。
哈希不可逆不等于无法猜解保存独立卡片。
原书依据 · 1 处
快速摘要不适合密码存储
SHA-256等快速摘要允许攻击者迅速尝试大量候选密码,不能因摘要长或算法仍用于其他安全任务,就认定适合直接保存密码。应采用具有成本参数的专用密码哈希;原书“至少SHA-256总是好”是已过时建议。本轮以OWASP Password Storage核验此边界,不交付快速SHA方案。
快速摘要不适合密码存储保存独立卡片。
原书依据 · 1 处
20.5.2 在SQL中使用哈希
验证时使用与保存时匹配的算法和参数是必要条件,但不代表直接在SQL里做SHA就安全。账号锁定也应有明确状态,不宜把散列字段改成非法字符串混入控制逻辑。
凭据格式不能兼任账号状态
把密码摘要改成不可能出现的字符串来锁账号,会把凭据验证格式和账号状态混在一起,增加迁移、诊断和解锁的不明确性。应显式区分账号是否可登录与密码是否匹配,状态变化也应可追踪。原文noaccess只是一种历史技巧,不作为新系统推荐。
凭据格式不能兼任账号状态保存独立卡片。
原书依据 · 1 处
20.5.3 给哈希加料
每个密码使用不同随机盐,可阻止对大量账号直接复用同一预计算表,并避免相同密码显示相同摘要。盐不需要保密,却不能让快速哈希自动抵抗单账号猜测。
独立随机盐阻断预计算复用
为每份密码验证值使用独立随机盐,使相同密码也产生不同结果,预计算表难以跨账号复用。盐可与验证值一起保存,不是密钥;它不会阻止攻击者针对已知盐继续猜密码。因此盐应由专用密码哈希实现正确管理,并配合足够的成本参数,不能把“加盐SHA”当完成保护。
独立随机盐阻断预计算复用保存独立卡片。
原书依据 · 1 处
字典与彩虹表利用候选复用
字典攻击尝试常见密码候选;预计算摘要表把候选与结果预先关联,彩虹表则是另一种时间与存储权衡。独立盐限制这类跨用户复用,但弱密码仍可能被针对猜中。原书主要解释预计算威胁,不提供彩虹表实现,不应把加盐说成完全消除密码猜测。
字典与彩虹表利用候选复用保存独立卡片。
原书依据 · 1 处
20.5.4 在SQL中隐藏密码
在应用中处理密码可减少明文进入SQL和查询日志,但验证值本身仍需保护,传输也应使用TLS。把浏览器先哈希当成HTTPS替代会留下可重放凭据,不能照搬。
减少密码经过的明文路径
密码验证应尽量在受控应用边界使用合适库完成,减少原文进入SQL、日志和诊断链路。验证值同样是敏感数据,不应任意记录;数据库连接及浏览器到服务的传输都要有适当保护。原书应用侧快速SHA示例只是历史思路,不能替代专用密码哈希与安全验证函数。
减少密码经过的明文路径保存独立卡片。
原书依据 · 1 处
客户端哈希不能替代安全传输
浏览器先计算固定摘要,若服务直接把它当登录凭据,截获者仍可能重放这个摘要。它只是换了一种可用秘密,不自动保护传输或服务身份。应采用正确验证的HTTPS/TLS传输,不能把简单客户端哈希当加密通道替代;原书将其称为另一个解决办法需加此限定。
客户端哈希不能替代安全传输保存独立卡片。
原书依据 · 1 处
20.5.5 重置密码,而非恢复密码
遗忘处理应重置访问凭据,避免恢复旧密码。原书第二方案把短期令牌绑定到账号和过期时间,这是可保留的结构;但账号名加时间再MD5可预测,不能作为安全令牌生成方式。
重置令牌绑定账号与期限
密码重置应通过受控恢复渠道发送令牌,服务校验它对应的账号及期限后才允许设置新密码。现行OWASP还要求令牌由安全随机源生成、足够长、妥善存储并单次使用;原书账号名加时间做MD5不具备所需不可预测性。申请本身不应先改变账号;邮件中也不应恢复旧秘密。
结构图:
flowchart TD
A[申请重置] --> B[受控渠道发送令牌]
B --> C[验证账号绑定与期限]
C --> D[验证单次有效性]
D --> E[设置新密码并消费令牌]
账号绑定与期限来自原书;安全随机和单次消费为已核验的现行补充,不声称已实现。
重置令牌绑定账号与期限保存独立卡片。
原书依据 · 1 处
临时密码邮件仍是可用秘密
临时密码即使过期和首次登录强制修改,在有效期内仍能被截获者使用;短有效期只能缩小窗口,不能保证不被利用。原书将它列为恢复途径,但更应采用受控、一次性的重置流程,并避免申请即改动正常登录凭据。不要把“临时”当成无需保护。
临时密码邮件仍是可用秘密保存独立卡片。
原书依据 · 1 处
PBKDF2以计算成本加强验证
PBKDF2用可配置计算工作量加强基于密码的派生,原书将它作为比直接快速摘要更深入的方向。实际参数与用途需按当前指南选取,而不是只采用名称就安全;它不使弱密码不可猜,也不替代盐和正确凭据处理。
PBKDF2以计算成本加强验证保存独立卡片。
原书依据 · 1 处
bcrypt提供自适应密码哈希
bcrypt是带可调成本的密码哈希路线,原书以自适应哈希提示应让猜测更昂贵。采用时须用成熟实现并按当前输入限制与参数要求配置,不应手工叠加快速摘要或按旧代码定长字段生搬硬套。它与一般快速SHA摘要承担不同的存储任务。
bcrypt提供自适应密码哈希保存独立卡片。
原书依据 · 1 处
第21章 SQL注入
SQL注入源于把不可信数据当成语句结构解释。值绑定把值与语法分开,但表名、排序方向和可变列表需要各自的固定映射或占位组织。过滤字符、整数转换、存储过程和ORM都不能无条件替代这些边界,授权仍须单独执行。
本章以信用卡信息泄露的历史事件引入SQL注入:看似普通的查询拼接错误可能成为进入系统的突破口。下面关注可迁移的解析机制与防御,而不把历史规模当现状。
21.1 目标:编写SQL动态查询
动态查询让程序按变量改变筛选条件。安全边界在于变量仍是数据,还是被拼入待解析的SQL结构;动态性本身不是漏洞。
动态SQL要区分值与结构
程序运行时改变查询条件是正常需求。危险发生于外部值被当成SQL语法组成部分解释,而不是因为查询含变量。先区分值参数、表列标识和关键字等结构,再选择绑定或受控映射;不能把所有动态SQL一概视为不可使用。
动态SQL要区分值与结构保存独立卡片。
原书依据 · 1 处
21.2 反模式:将未经验证的输入作为代码执行
注入通过让输入改变解析结构成功;分号追加另一语句只是其中一种表现,也可能在同一语句中修改条件。是否允许多语句不决定是否存在注入。
注入把外部数据变成语法
SQL注入的根因是外部输入进入代码位置,改变数据库解析出的结构。它可以追加语句,也可只修改同一语句的条件或表达式;禁用多语句并不覆盖全部情况。防御要阻断数据变代码,而不是只过滤分号或某个攻击样例。
注入把外部数据变成语法保存独立卡片。
21.2.1 意外无处不在
合法名字里的单引号也会破坏直接拼接的SQL,说明漏洞不需要恶意输入才显现。应保留合法业务数据,用适当绑定处理,而不是删除人名中的字符。
合法引号也能打破拼接
姓名或项目名含单引号是正常业务输入,直接插入引号包围的SQL文字会提前结束字符串。即使只报错,也说明值与语法未分离。优先绑定数据参数,不应为了躲开语法问题删改合法名称;输入验证应针对业务格式,而非损坏原始内容。
合法引号也能打破拼接保存独立卡片。
原书依据 · 1 处
21.2.2 对Web安全的严重威胁
攻击者不必添加第二条语句,也可让单条更新的条件恒真,扩大修改范围。这直接说明防御需要保护整个解析边界,不能只看SQL能否通过语法检查。
注入把外部数据变成语法关联本项背景,实质主要讲解在对应位置。
21.2.3 寻找治愈良方
转义、参数绑定、存储过程和框架各有范围。绑定保护值位置,不能把一个值变成表名、列名或一串列表;存储过程和框架里的原始动态字符串仍可能有同样漏洞。
字符串转义有上下文边界
字符串引用函数只按特定方言、编码和连接配置保护文字值,不能代替标识符或语法片段验证。自写替换引号尤其容易遗漏上下文;优先绑定值参数。若确实使用库引用函数,须核对实际驱动配置,不把反斜杠视为所有SQL通用转义。
字符串转义有上下文边界保存独立卡片。
原书依据 · 1 处
参数只代表一个数据值
值参数不改变预定查询结构:传入带逗号文本仍是一个值,不是IN列表;传入列名文字也不会变成ORDER BY列引用。表名、关键字和任意表达式应由受控结构选择,不能靠同一个占位符实现。参数支持范围依驱动和语句位置核对。
参数只代表一个数据值保存独立卡片。
原书依据 · 1 处
预处理分开结构与绑定值
真正的参数绑定把查询结构与数据值分开,值不会重新被解析成SQL代码。预处理文本与绑定参数应分别理解,具体驱动也可能模拟预处理,须核对实现和配置。调试日志可能展示重建文本,不能反推运行时实际拼接方式;敏感参数也不应因调试而全量记录。
预处理分开结构与绑定值保存独立卡片。
存储过程也能包含不安全拼接
存储过程中的固定语句可避免某些拼接,但若过程再把参数连接到动态SQL并执行,数据仍可能进入语法位置。安全取决于动态边界如何处理,而不是代码存放在数据库还是应用。检查过程内所有动态执行点,并对值绑定、结构做受控选择。
存储过程也能包含不安全拼接保存独立卡片。
原书依据 · 1 处
框架安全接口必须实际使用
数据访问框架可提供绑定和引用接口,但若调用方绕过它们,继续传入拼接的原始SQL,框架名称不会自动保证安全。应核对每个实际调用路径及原始查询入口,不能把使用ORM或某个库当作注入审查已通过。
框架安全接口必须实际使用保存独立卡片。
原书依据 · 1 处
21.3 如何识别反模式
数据从数据库读出并不代表安全,它可能最初由用户提供;第二次被拼进查询时仍会变成语法。这是需要追踪来源而非只看当前存放位置的原因。
数据库读出的值也可能注入
用户输入可先被安全参数化保存,后来读出再拼进另一条SQL,就在第二次使用处越过代码边界。可信存储位置不洗白内容来源。审查应追踪数据从入口、持久化到每次动态执行的路径,值每次进入查询都应保持绑定,不能只保护第一次写入。
数据库读出的值也可能注入保存独立卡片。
原书依据 · 1 处
21.4 合理使用反模式
SQL注入没有需要保留的合理例外。动态业务需求可以满足,但应以不允许外部输入改写结构的方式实现。
21.5 解决方案:不信任任何人
按动态部分的职责组合防御:业务值验证并绑定,结构选项由预定义映射决定,剩余特殊场景按驱动与方言核对。
21.5.1 过滤输入内容
输入验证应确认整个值符合业务类型和范围,不宜截取看似安全的子串就继续执行。原示例的过滤、整数截断及未锚定正则存在含义与实现问题,不能作为完整防御。
输入验证不要静默改写含义
需要整数ID时,应验证整个输入确为允许范围内的整数,再按类型绑定;静默删字符或截取前缀可能把错误值变成另一合法ID。结构选项也应映射到明确允许项,而不是仅匹配一段字母数字。原正则未锚定且读取了不存在的捕获组,不能照搬为安全实现。
输入验证不要静默改写含义保存独立卡片。
原书依据 · 1 处
21.5.2 参数化动态内容
对简单动态值使用绑定,能让注入文本维持为数据;但绑定不替代类型、范围和授权检查。某些数据库会将字符转为数值,不能保证恶意外观的文本必定查不到行。
预处理分开结构与绑定值关联本项背景,实质主要讲解在对应位置。
21.5.3 给动态输入的值加引号
数据倾斜和参数化计划可能影响索引选择,应先查实际计划及驱动行为。不能因假设参数影响优化就直接退回不安全拼接;确有文字化需求时,也只能使用正确的库接口和受控值。
计划问题不应牺牲值绑定
参数化计划在数据倾斜等情况下可能不适合某个值,但具体是否发生取决于引擎、版本及计划策略。先通过实际计划验证,再选产品支持的处理方法。若必须文字化值,也需严格使用匹配方言的可靠引用接口;不应照“忽略查询参数”的旧建议把外部值直接拼回SQL。
计划问题不应牺牲值绑定保存独立卡片。
原书依据 · 1 处
21.5.4 将用户与代码隔离
表列名与排序方向属于查询结构,可让用户选择业务选项,再由固定映射生成受控片段。IN列表则按元素数生成占位符,只把形状放进结构,每个元素继续绑定。
结构选项映射到固定SQL片段
用户选“日期”或“向下”,程序将它映射到预定义列名和DESC等固定片段,再组成查询。未识别选项可拒绝或用明确默认值,不能把原输入当标识符。允许集合由代码控制,既隔离语法又隐藏实现细节;仍要检查用户是否有权选择该数据范围。
结构选项映射到固定SQL片段保存独立卡片。
原书依据 · 1 处
列表按元素数建立占位符
IN需要多个值时,按经过校验的元素数量生成相同数量占位符,值数组逐个绑定。一个逗号字符串不能自动展开为多参数。结构只含固定占位符而不含值文本;空列表、参数数量上限及元素类型也要明确定义,避免无效语法或过量资源使用。
列表按元素数建立占位符保存独立卡片。
原书依据 · 1 处
21.5.5 找个可靠的人来帮你审查代码
审查应找到全部动态SQL点,追踪所有外部来源,包括数据库值和存储过程,再检查每处值绑定与结构选择。第二位熟悉风险的人能提供独立视角,不能仅凭开发者自信放行。
注入审查追踪完整数据路径
先列出字符串插值、拼接和动态执行点,再追踪用户、文件、环境、服务与数据库值的来源。对值验证并绑定,对表列名和关键字使用固定映射,同时检查存储过程和框架原始SQL入口。独立审查帮助发现遗漏,但不能把“审查过”当未来任何修改都安全的永久证明。
注入审查追踪完整数据路径保存独立卡片。
原书依据 · 1 处
第22章 伪键洁癖
代理编号只需可靠标识记录,不负责填补删除后的空洞或排列展示顺序。重新编号会改变外部引用,回收编号可能误认旧实体,生成器容量也必须按位数和消耗速率计算。窗口行号提供展示顺序,UUID则有版本与生成条件,不能笼统视为绝对唯一随机数。
为填补资产编号断档而重排ID,会破坏季度报告、设备标签和新旧资产的身份对应。即使表内外键可级联,外部世界保存的编号也不会自动同步。
重编号会破坏跨系统身份
资产ID已经印在设备标签并出现在历史报表中,再为消除断档重新分配,会让同一编号指代不同资产,跨期追踪失效。数据库内级联也不能更新纸质标签或外部系统。身份稳定性比数字看起来连续更重要,重排前必须识别全部引用和冲突,不能把它当表内整理。
重编号会破坏跨系统身份保存独立卡片。
22.1 目标:整理数据
编号缺口可能来自删除、回滚或筛选,并不直接证明数据丢失。调查应看真实记录生命周期,而不是为了消除焦虑重用ID。
主键断档不证明记录丢失
查询结果中缺一个数字,不代表数据库丢了一行:记录可能删除、插入失败、生成器预分配,或查询本身只返回子集。先查业务与生成器记录,不应为补齐视觉序列重用身份。主键要求唯一和有效引用,通常不要求连续。
主键断档不证明记录丢失保存独立卡片。
22.2 反模式:填充角落
填补缺口通常有插入新行占旧号、或直接给现有行重编号两种形式。两者都会引入额外分配与引用责任。
22.2.1 不按照顺序分配编号
两个客户端都先查最小空号,再尝试插入,会同时选中同一值。唯一约束只能让其中一方失败,不能把这个分配过程自动变成无冲突。
查空号再占用存在并发竞争
先扫描空号再插入,与MAX加一有同样的检查—动作窗口:两个客户端可能取同一个候选。唯一约束可阻止重复落库,但调用方还会失败重试,扫描也增加成本。除非业务确需特殊编号协议,否则不要为补视觉缺口替代成熟ID生成机制。
查空号再占用存在并发竞争保存独立卡片。
22.2.2 为现有行重新编号
重编号要协调所有引用,而且生成器未必按当前最大值继续,补完的缺口还可能马上再出现。级联和生成器规则都不能代替身份稳定性判断。
重编号会破坏跨系统身份关联本项背景,实质主要讲解在对应位置。
主键断档不证明记录丢失关联本项背景,实质主要讲解在对应位置。
22.2.3 制造数据差异
删除旧账号后把其ID分给新人,迟到投诉仍可能按旧邮件编号找到新用户。数据库此刻唯一,不代表这个编号在历史与外部系统中仍然表达同一个主体。
重用ID会错接历史事件
旧账号删除后复用其ID,历史邮件、审计或外部引用可能仍指向旧主体,却被解释为新用户。唯一约束只检查当前存储,不能保证跨时间身份语义。需要稳定身份和相应保留策略,而不是因数字空闲就认为可安全再分配。
重用ID会错接历史事件保存独立卡片。
原书依据 · 1 处
22.3 如何识别反模式
回滚后想回收生成号、寻找最小空号或担忧数值耗尽,都是应区分连续展示与稳定身份的线索。生成器回滚规则与容量需要实际核对,不能接受无条件不会耗尽的保证。
查空号再占用存在并发竞争关联本项背景,实质主要讲解在对应位置。
22.4 合理使用反模式
代理键不表达业务顺序,不能为了表面连续随意改变;若编号承载业务规则,就应将它作为业务标识管理。它与内部稳定代理身份可以并存。
代理键不携带业务含义关联本项背景,实质主要讲解在对应位置。
22.5.1 定义行号
结果行号描述当前排序中的位置,主键描述记录身份。分页应基于确定的结果顺序,不能把ID缺口当丢行或要求主键等于显示序号。
窗口编号为结果行建立次序关联本项背景,实质主要讲解在对应位置。
行号属于结果而非实体身份
主键标识持久记录,行号标识当前查询结果的位置;连接、过滤、分组或排序后,二者自然不同。ROW_NUMBER可按明确顺序生成连续序号供分页,排序应含稳定破同分规则。旧书MySQL与SQLite不支持窗口函数的清单已过时;SQLite官方有明确示例。
行号属于结果而非实体身份保存独立卡片。
22.5.2 使用GUID
GUID/UUID用128位表示身份,具体生成有不同布局,并非全都是纯随机。它有利于多点生成,但值宽度、索引局部性和输入便利性要权衡。整数容量也须按速率计算,原书32位耗尽算术错了。
UUID身份需权衡生成与存储
UUID/GUID是128位标识,具体版本可包含时间、随机或其他构成,不等于统一的纯随机算法。它支持分散生成身份,但仍要正确实现生成并用唯一约束防冲突;16字节键比常见整数更宽,局部性也随布局变化。不能仅凭名称保证绝无碰撞或必定比整数慢,已核验RFC9562的版本边界。
UUID身份需权衡生成与存储保存独立卡片。
原书依据 · 1 处
整数容量要按分配速率计算
编号空间有限,寿命约为可用编号数除以分配速率。教学核算:无符号32位约42.95亿值,每秒1000个约49.7天,不是原书136年;正数有符号范围更小。无符号64位每秒百万个约58.45万年。应考虑已用范围、预分配与峰值,不能把“几乎不可能耗尽”当设计保证。
整数容量要按分配速率计算保存独立卡片。
原书依据 · 1 处
22.5.3 最主要的问题
面对补洞要求,应先理解业务担忧,解释缺口的正常来源,并列出重分配、引用更新、外部影响及测试成本。若只是报告不美观,可使用展示行号或另一个业务标识。
用身份成本回应补洞要求
对补齐ID的要求,先确认是否担心数据丢失或报告可读性,再解释生成、删除和筛选可产生正常缺口。给出重编号涉及的引用、标签、外部系统和验证成本,并提供展示序号或独立业务编号。沟通应围绕真实目标,不能只说“数据库就是这样”。
用身份成本回应补洞要求保存独立卡片。
原书依据 · 1 处
第23章 非礼勿视
每个调用都可能失败,忽略返回值只会把错误移动到较远位置。诊断应保留操作上下文和数据库信息,同时避免向用户或日志泄露秘密。接口可能通过返回值或异常报告失败,应以实际配置为准,不能机械套用历史PDO示例。
空结果与执行失败是不同事实。忽略API状态后只看到没有数据,会把语法错误错当数据库问题;先收集实际错误再判断原因。
空结果不等于查询执行成功
查询没有返回记录,可能是合法空集,也可能根本执行失败。应先检查连接、准备、执行和读取的状态,再解释结果。把“没有数据”直接归为数据库缺陷会跳过关键证据;原书支持案例最终是语法错误,说明诊断从实际错误开始。
空结果不等于查询执行成功保存独立卡片。
原书依据 · 1 处
23.1 目标:写更少的代码
少代码有助于理解和维护,但删除必要错误处理只是隐藏失败路径。衡量清晰度应包含正常和异常行为,而非只数行数。
简洁不能靠删除失败处理
减少重复代码可以降低维护成本,但错误检查、分类和恢复承担实际职责,不能仅因不够简短就删去。应通过适当抽象和统一边界保持清晰,同时让失败可见。代码少不必然缺陷少,遗漏失败路径也可能让用户只能看到崩溃。
简洁不能靠删除失败处理保存独立卡片。
23.2 反模式:无米之炊
不看API结果与不看最终SQL是两种信息丢失:前者藏掉运行失败,后者藏掉字符串实际结构。调试需要观察真实执行边界。
23.2.1 没有诊断的诊断
数据库操作每个阶段都可能失败,不能在prepare失败后继续调用execute。fetch的false还可能只是结果结束,需结合错误状态区分。未经处理的失败最终可能只给用户白屏。
数据库调用要逐层确认结果
连接、准备、执行和读取是不同阶段;前一步失败时,不应把无效对象继续传给后续调用。错误可能通过返回值或异常报告,取决于驱动和配置。读取false也可能表示结果结束,不可把正常空集一律当失败;应按API契约区分并保留诊断。
数据库调用要逐层确认结果保存独立卡片。
23.2.2 字里行间
拼接代码里很难看出的空格遗漏,在最终SQL中直接表现为BugsWHERE。保留最终结构和绑定参数的受控诊断,有助于先定位语法,再回到构造逻辑。
调试先看最终查询结构
动态SQL构造错误应先观察最终查询文本或预处理结构,例如漏空格使Bugs与WHERE粘连。之后再追踪生成逻辑,比只在分散字符串里想象结果更直接。值参数可单独诊断,但秘密应脱敏,不要为了看到完整SQL重新用不安全插值。
调试先看最终查询结构保存独立卡片。
原书依据 · 1 处
23.3 如何识别反模式
查询后崩溃、只给构造代码不看SQL、拒绝错误处理,都是应检查诊断链路的线索。工具能提示未处理结果,但不能替代对业务失败语义的判断。
数据库调用要逐层确认结果关联本项背景,实质主要讲解在对应位置。
简洁不能靠删除失败处理关联本项背景,实质主要讲解在对应位置。
23.4 合理使用反模式
当前层不负责恢复时,可以让异常沿调用栈传播到有责任的边界;这与吞掉失败不同。只有确认退出语义和资源后果允许时,才可有意忽略某些清理状态。
异常可传播但不能无处负责
当前函数无法决定恢复策略时,可保留异常交给有上下文的上层,避免每层都重复捕获。传播不等于忽略,必须有负责记录、回滚或响应的边界。清理错误是否可忽略也要依据资源契约判断,不能把程序即将退出当作任何失败都无后果。
异常可传播但不能无处负责保存独立卡片。
原书依据 · 1 处
23.5 解决方案:优雅地从错误中恢复
错误不可完全避免,关键是能获得原因并选择正确恢复动作。下面把状态检查与实际SQL观察合在可诊断的开发方式中。
23.5.1 保持节奏
逐阶段检查返回状态或异常,在错误发生处取正确对象的诊断信息,并停止无效的后续操作。对正常读取结束仍要按API契约处理。
数据库调用要逐层确认结果关联本项背景,实质主要讲解在对应位置。
23.5.2 回溯你的脚步
最终SQL和错误诊断应进入受控日志或调试工具,不应藏在HTML注释中向用户公开。ORM生成查询也要有可观察入口,数据库端日志则是另一个来源。
查询诊断不能泄露到页面
不要把SQL放在HTML注释中作为调试输出,页面源可暴露表结构与敏感参数。用受控日志或调试渠道保存必要结构、错误及脱敏参数,并限制访问。ORM也应能观察生成查询,数据库日志可补充证据;可诊断性与秘密最小化要一起设计。
查询诊断不能泄露到页面保存独立卡片。
原书依据 · 1 处
第24章 外交豁免权
数据库代码同样需要文档、版本控制和测试。元数据、迁移及数据各有不同恢复语义,回滚脚本不能自动复原被删除的业务事实。测试既要观察允许行为,也要验证约束拒绝非法写入;本次交付实践计划并不表示这些测试已经执行。
只有一位开发者知道数据库应用且没有版本、文档和测试,人员突然不可用后,接手者即使面对普通程序也要长期重建知识。技术债务不只是代码难看,还会形成恢复和交付风险。
技术债务积累未来变更成本
技术债务是捷径遗留的结构与知识负担,会让以后修改、验证和交接付出额外工作。原书无文档、测试及版本控制的接手案例显示,关键人缺席时这种成本会集中暴露。偿还要针对具体缺口重构、补证据和文档,不是给所有旧代码贴债务标签。
技术债务积累未来变更成本保存独立卡片。
原书依据 · 1 处
24.1 目标:采用最佳实践
版本、测试和文档是软件交付的组成部分,数据库代码也应适用。收益在于减少不确定与重复劳动,不能把“做了这些”当任何项目一定成功的保证。
数据库同样需要工程化管理
数据库结构、查询与存储逻辑影响软件行为,也应有版本、测试和需求文档。若只管理应用代码,无法重现它所依赖的数据结构和规则。工程实践应与风险和用途相称,目的在于可恢复、可验证和可交接,而不是形式上凑齐工具。
数据库同样需要工程化管理保存独立卡片。
24.2 反模式:将SQL视为二等公民
把SQL看成DBA个人负责的特殊领域,会让它绕过版本、测试和文档。角色分工不应造成质量责任空白;数据库也是应用可靠性的基础。
数据库同样需要工程化管理关联本项背景,实质主要讲解在对应位置。
24.3 如何识别反模式
不知道表列用途、用结构比较追赶环境漂移,或把DBA排除在版本培训外,都是应补查管理证据的线索。轻量流程也要有明确取舍,不能据名称判断它是否漏掉关键保证。
不明用途的结构不能先删
表列用途不明,可能是文档失效或依赖调查不全,不能据此认定它们无用。先查使用者、查询与业务规则,再提出删除或迁移方案。结构比较工具能发现差异,但不能自动说明差异的意图与正确方向;数据库演进仍需可追踪流程。
不明用途的结构不能先删保存独立卡片。
原书依据 · 1 处
24.4 合理使用反模式
真正即兴、用后即可丢弃的实验代码可以少做长期维护投入;一旦继续依赖它,就应转为有来源、用途和版本的正式资产。
临时代码以生命周期判断
临时实验若用完即可丢弃,可简化长期文档和测试投入;若后续仍需重用,就应记录用途与使用方式,纳入版本与相应验证。判断重点是实际依赖寿命,而不是文件名叫tmp。删除也要确认没有其他流程依赖,不能把清理动作当作证明。
临时代码以生命周期判断保存独立卡片。
原书依据 · 1 处
24.5 解决方案:建立一个质量至上的文化
质量保证从需求、实现到验证形成闭环;测试是其中的质量控制,不是全部过程。数据库同样需要能追溯到需求的设计和验收证据。
质量保证不只包含测试
质量保证覆盖明确需求、设计实现及验证符合性,测试只是发现和确认质量的一部分。三项可以迭代推进,不必机械线性,但不能只在最后跑测试而没有目标定义。数据库的文档、版本和测试共同保存从意图到结果的证据。
质量保证不只包含测试保存独立卡片。
原书依据 · 1 处
24.5.1 陈列A:编写文档
数据库文档既要说明结构,也要保存目的、业务规则和环境边界。ER图显示关系,文字解释为何有这些表列、哪些约束未在DDL表达;迁移脚本再让结构变化可追踪。
ER图不能代替对象用途文档
ER图适合表达实体和关联,不能独自解释每张表、列和视图为什么存在。文档应补充预期访问方式、规模、值域、空值和唯一性理由,以及谁依赖它。复杂数据库可分图,文字与图共同帮助接手者理解,而不是画一张难读全景图便算完成。
ER图不能代替对象用途文档保存独立卡片。
原书依据 · 1 处
文档要记录DDL未表达的规则
外键和非空只表达部分业务条件。例如负责人可空,不代表允许未指派就修复;状态变化的前提需要另行记录并实现。文档应说明隐式关系、角色和时序规则,让读者知道哪些由数据库强制,哪些依赖应用,而不是从列类型猜业务。
文档要记录DDL未表达的规则保存独立卡片。
原书依据 · 1 处
视图文档解释接口与权限
视图文档应说明服务哪些使用者、封装什么查询、是否可更新以及权限如何生效。普通视图保存查询定义,并非原文所说必然保存结果;物化结果属于另外的机制。若视图用于限制可见列或行,还需记录它的授权边界,不能只列字段。
视图文档解释接口与权限保存独立卡片。
原书依据 · 1 处
数据库过程也需要API契约
存储过程文档要说明目标、输入输出类型及含义、数据副作用、失败和权限边界;触发器也要记录何时执行以及验证、转换、级联或审计职责。调用者才能理解显式调用之外的动作,维护者也能判断变更影响,而不是把库内逻辑当隐形实现。
数据库过程也需要API契约保存独立卡片。
原书依据 · 1 处
环境文档记录边界不泄露秘密
环境文档应记录产品版本、拓扑、连接配置、备份恢复及权限责任,帮助开发与运维确认同一运行边界。原文还问数据库密码是什么,但正式共享文档应记录受控秘密获取方式,不写明文密码。ORM中的验证、转换或缓存职责也应说明,以免误以为全部规则在数据库。
环境文档记录边界不泄露秘密保存独立卡片。
原书依据 · 1 处
迁移记录结构演进步骤
迁移把数据库结构变化保存为可执行的版本步骤,并记录实例当前状态,使应用与模式可协调升级。升降脚本不天然互为数据恢复:删列等操作会丢内容,down重建列不能找回旧值。分支、失败恢复及破坏性变化仍需设计,不能只凭迁移编号证明实例正确。
迁移记录结构演进步骤保存独立卡片。
原书依据 · 1 处
24.5.2 寻找证据:源代码版本控制
可重建数据库的版本资产不仅是表定义,还包括触发器、过程、种子数据、图与文档及DBA脚本。它们应与使用它们的应用版本对应;数据库实例中的业务数据仍需单独备份。
数据库版本资产包括种子与脚本
要从版本构建可工作的数据库,除DDL还需触发器、过程、必要种子数据、ER图/文档与管理脚本,并明确对应应用版本。种子是系统开始工作所需的基准数据,不是任意生产数据快照。版本控制保存代码与结构意图,不能替代运行数据备份或自动撤销已经发生的数据副作用。
数据库版本资产包括种子与脚本保存独立卡片。
原书依据 · 1 处
24.5.3 举证:测试
数据库验证要独立检查真实结构和行为:对象存在、约束拒绝非法数据、触发器/过程副作用、种子及查询形状。测试前还要确认连接了正确实例,否则失败可能来自环境不符。
约束测试要验证非法操作被拒
数据库约束不能只看定义文件,要在隔离测试环境构造违反非空、唯一、外键等条件的操作,确认被拒且状态符合预期。同时验证合法路径,才能排除“所有写入都失败”的伪通过。负面测试检查实际强制能力,不能只用表存在证明业务约束正确。
约束测试要验证非法操作被拒保存独立卡片。
原书依据 · 1 处
数据库行为测试按责任分层
把对象结构、触发器、过程、种子、查询结果及ORM行为分别验证,失败时更易定位责任。真实数据库交互通常属于集成层测试,即使用单元测试框架组织,也不等于纯单元测试。应用单元测试和库行为测试互补,不能以模拟对象替代真实约束验证。
数据库行为测试按责任分层保存独立卡片。
原书依据 · 1 处
测试失败先核对实例与版本
数据库测试报错时,先核对连接的实例、身份及模式版本,再判断是代码、迁移还是环境问题。确认环境后才提出受控修复,不能见失败就修改数据库或连到生产重试。正确实例也不代表逻辑一定正确,仍需解释实际失败证据。
测试失败先核对实例与版本保存独立卡片。
原书依据 · 1 处
24.5.4 例证:同时处理多个分支
同时维护热修复与长期开发时,数据库结构也会不同。每分支或开发者使用独立实例,并通过配置选择连接,可减少互相修改模式的干扰;源码分支不会自动切换运行中的库。
分支工作应隔离数据库状态
应用切换分支不会自动把数据库变成对应版本。为并行开发、测试和部署阶段准备独立实例,通过配置选择连接,可避免一个人的模式变更破坏他人。隔离还需核对版本和测试数据,虚拟化只是实现手段;不能只因实例多就认定它们与生产一致。
分支工作应隔离数据库状态保存独立卡片。
原书依据 · 1 处
第25章 魔豆
能读写表的对象不等于完整业务模型。Active Record把常见持久化封装得很方便,但业务流程、跨对象协作和变化点仍应有清楚边界。共享稳定知识、封装数据访问和按职责组织服务,可以减少重复而不把所有逻辑挤进一个魔豆类。
评论数量这个小功能被复制进多个控制器后,修改和浏览器测试组合迅速膨胀。成本并非来自功能字面大小,而来自业务知识在界面后端分散重复。
重复业务查询放大小功能成本
多个界面都需要评论数,若查询与组装逻辑分别复制到各控制器,新增一项业务信息就要多处修改和验证。应把共同业务能力放到合适模型接口,让界面只依赖结果。测试数量多并非本身错误,问题是职责分散使同一逻辑必须反复穿越重型界面测试。
重复业务查询放大小功能成本保存独立卡片。
原书依据 · 1 处
25.1 目标:简化MVC的模型
MVC让控制器解释输入并协调调用,模型承载业务数据和行为,视图负责展示。模型不是一张表的同义词;把它缩成数据访问对象,会让其余业务逻辑失去稳定归属。
MVC按输入业务展示分工
MVC将输入协调、业务模型和界面展示分开:控制器接收输入并调用模型,模型处理相关数据和规则,视图呈现结果。模型可以涉及多张表或没有表。框架提供连接与复用机制,但不能替应用完成所有业务对象设计。
结构图:
flowchart TD
I[用户输入] --> C[控制器协调]
C --> M[模型业务数据与行为]
M --> C
C --> V[视图展示]
V --> O[用户可见输出]
图表示本章简化协作,不规定所有MVC框架的具体通信细节。
MVC按输入业务展示分工保存独立卡片。
原书依据 · 1 处
25.2 反模式:模型仅仅是活动记录
活动记录把行的数据与持久化操作包装为对象,适合简单CRUD。反模式不是使用它,而是强迫所有业务模型都等于某张表的活动记录;复杂查询与业务行为会由此泄露到调用者。
活动记录封装行与持久化
活动记录把数据库表或视图的行包装成对象,并提供读取、保存、更新和删除等持久化操作。它适合简单行级任务,但一张表的接口不一定覆盖跨表业务行为。应区分“使用活动记录存取数据”与“所有模型必须继承活动记录”,前者方便不证明后者合理。
活动记录封装行与持久化保存独立卡片。
原书依据 · 1 处
抽象泄露要求重新看边界
抽象隐藏细节以简化使用,遇到性能、表达能力或复杂行为时仍可能迫使调用者理解内部机制,这就是抽象泄露。本章以活动记录难以自然表达某些JOIN/GROUP BY为例。泄露不证明抽象毫无价值,但提示要调整接口职责,而非不断把底层细节暴露给每个控制器。
抽象泄露要求重新看边界保存独立卡片。
原书依据 · 1 处
25.2.1 活动记录模式连接程序模型和数据库结构
若模型与表一一对应,表结构变更就会扩散到模型及调用者;增加界面还会重复查询组合。数据库结构和业务接口不必相同,耦合范围应有意设计。
表映射不应决定全部业务接口
活动记录按表映射很直接,但若业务接口也直接暴露这种结构,数据库重构会影响所有调用者。跨表任务更应按业务能力封装,让表的变化尽量留在内部。并非所有改表都会必然破坏全部代码,关键是哪些物理细节成为外部契约。
表映射不应决定全部业务接口保存独立卡片。
原书依据 · 1 处
25.2.2 活动记录模式暴露了CRUD系列函数
assignUser本来封装保存与通知,但其他调用者可直接改assigned_to再save,绕过通知。业务动作如果仍能由裸CRUD完成,约定的方法名不足以强制流程。
裸CRUD可能绕过业务动作
需求要求指派缺陷同时通知负责人,若外部仍可直接改字段再save,就能绕过assignUser封装的动作。应让业务接口承担完整责任,并限制不应公开的修改路径。原文保存后发邮件也不保证两者原子完成,失败与重试还需另行设计;不能只加一个方法就声称约束已强制。
结构图: 类图
classDiagram
class BaseBugs
class CustomBugs {
+assignUser(Accounts $a)
}
class Accounts {
+account_id
+email
}
BaseBugs <|-- CustomBugs
CustomBugs ..> Accounts : 参数依赖
签名沿用原PHP示例,无声明返回类型;继承本身没有隐藏基类公开CRUD。
裸CRUD可能绕过业务动作保存独立卡片。
原书依据 · 1 处
25.2.3 活动记录模式支持弱域模型
弱域模型只有数据与CRUD,业务组织落到多个控制器。控制器不断组合Bugs、Accounts与Products,就使规则重复、变化扩散和接口内聚性降低。
弱域模型把业务散落控制器
模型只有数据和持久化接口时,验证、跨对象协调及规则可能分散在控制器中。多个界面重复同一组合逻辑,业务变化就要多处同步。本章所谓弱域模型强调这种职责失衡,不意味着任何简单数据对象都错误;复杂度低时可以有意采用简单CRUD。
弱域模型把业务散落控制器保存独立卡片。
原书依据 · 1 处
25.2.4 魔豆难以进行单元测试
模型即持久化对象会让业务测试必须启动数据库;业务又在控制器时,还要模拟HTTP、渲染并解析HTML。把规则与数据访问、展示分开,才能在适当层次验证。
测试夹具固定数据前提
测试夹具准备已知标准数据,让真实数据库测试从可比较前提开始。它有装载、清理和隔离成本,但不因此无用。若只想测试纯业务分支而每次都重建数据库,说明层次可能耦合;真实约束与查询仍应保留相应集成验证。
测试夹具固定数据前提保存独立卡片。
原书依据 · 1 处
业务测试不必总穿过HTTP
若业务规则只在控制器与活动记录组合里,验证一个分支可能要启动数据库、模拟请求和解析页面。将业务行为放到可独立调用的模型,再以替身隔离数据访问,可更快定位规则问题。控制器与数据库仍需集成测试,分层不是取消真实边界验证。
业务测试不必总穿过HTTP保存独立卡片。
25.3 如何识别反模式
控制器复制复杂查询、模型需要任意SQL以及业务测试不断增加数据库夹具,提示模型边界可能只是一层数据访问。DRY关注共同知识,而不是把所有类似代码强行塞进抽象控制器。
共享业务知识应有单一归属
同一业务查询和规则在多个控制器复制,会让修改必须同步多处。DRY要求共同知识有明确归属,例如模型的业务接口;它不等于见到相似语句就机械抽公共父类。先判断是否同一规则及生命周期,再选择复用边界。
共享业务知识应有单一归属保存独立卡片。
原书依据 · 1 处
25.4 合理使用反模式
简单CRUD或快速原型可以让模型与DAO暂时合一。合理捷径的前提是需求确实简单,或已为后续重构留出计划,而不是原型无期限变成正式架构。
简单CRUD与原型可用活动记录
简单行级操作可直接使用活动记录,原型也可用它快速验证需求反馈。若规则增长和跨表行为变多,应重新评估边界,并为重构及测试补齐投入。使用条件来自实际复杂度与阶段,不是框架默认就永久合理。
简单CRUD与原型可用活动记录保存独立卡片。
原书依据 · 1 处
25.5 解决方案:模型包含活动记录
模型承载业务对象的数据与行为,框架难以给出统一设计。需要从业务责任出发,再决定如何使用一个或多个DAO,而不是让持久化基类决定所有对象形状。
25.5.1 领会模型的意义
GRASP从职责分配帮助设计模型:有信息者负责,聚合者创建所需对象,接口降低耦合且围绕同一业务能力内聚。模型可使用DAO而非继承DAO,隐藏存储组合。
信息专家拥有完成任务的数据
把操作责任分配给掌握所需信息的对象。跨多表业务可能需要领域模型聚合多个数据访问结果,而不是让一个单行活动记录勉强承担全部工作。信息专家关注责任与信息的匹配,不意味着必须把所有数据复制进一个万能对象。
信息专家拥有完成任务的数据保存独立卡片。
原书依据 · 1 处
模型聚合DAO而非继承DAO
领域模型通过使用一个或多个DAO获得数据,把存储查询作为内部实现,而不必继承所有公开CRUD。控制器只依赖业务接口,存储结构调整就可尽量留在模型内部。组合有利于边界设计,但仍须避免把可修改DAO对象直接泄露给调用者。
模型聚合DAO而非继承DAO保存独立卡片。
创建责任跟随对象聚合
聚合多个DAO完成业务的模型,可以负责组织和创建它所需的访问对象,使控制器与视图不必知道具体存储实现。这是本章创造者原则的应用,不规定只能在构造函数硬编码依赖;生命周期与可替换性仍应支持测试。
创建责任跟随对象聚合保存独立卡片。
原书依据 · 1 处
低耦合让实现变化留在边界内
把业务接口与存储实现分开,可使数据库结构和查询变化尽量不影响调用方式。需求复杂度不会因此消失,而是被放到有责任和上下文的位置。低耦合不是没有依赖,而是减少不必要的细节依赖,避免控制器处处理解表关系。
低耦合让实现变化留在边界内保存独立卡片。
原书依据 · 1 处
高内聚接口表达业务行为
模型接口围绕同一业务能力组织数据和行为,例如assignUser比find、save更说明调用目的。单个DAO也可以被不同业务模型使用,避免把某张表涉及的一切规则堆进一个巨大活动记录类。接口表达意图之外,还要实际维护相应不变量。
高内聚接口表达业务行为保存独立卡片。
原书依据 · 1 处
25.5.2 将领域模型应用到实际工作中
领域模型把业务行为作为接口,内部使用数据访问对象。BugReport集中create、assignUser、get和search,控制器只传业务输入;示例简化了依赖,但语法和封装仍有未完成处,不能当完整实现。
领域模型隐藏跨表访问组合
领域模型围绕业务对象的数据与行为组织接口,持久化只是其内部实现。示例BugReport集中创建、指派、读取和搜索,控制器不再各自复制表访问。原get仍返回活动记录,可能泄露修改入口;assignUser也未补回通知。组合结构改善了职责,不证明所有不变量和副作用已经封装完整。
结构图: 类图
classDiagram
class BugReport {
+__construct()
+create($summary, $description, $reportedBy)
+assignUser($bugId, $assignedTo)
+get($bugId)
+search($status, $searchString)
}
class AdminController {
+assignAction()
}
class BugController {
+enterAction()
+displayAction()
}
class SearchController {
+bugsAction()
}
class DAO示意["数据访问对象(示意)"] {
+find($id)
}
AdminController ..> BugReport : 业务调用
BugController ..> BugReport : 业务调用
SearchController ..> BugReport : 业务调用
BugReport o-- DAO示意 : 聚合使用
BugReport签名按原PHP未声明返回类型;DAO节点是示意,不虚构原文未命名实现。菱形为聚合,虚线为调用依赖。
领域模型隐藏跨表访问组合保存独立卡片。
原书依据 · 1 处
25.5.3 测试简单对象
模型与DAO分离后,可用DAO替身测试业务接口,再分别验证HTTP控制器与真实数据库交互。测试边界更接近责任,失败也更易定位。
业务测试不必总穿过HTTP关联本项背景,实质主要讲解在对应位置。
25.5.4 回到地球
框架可以帮助存取和组织接口,但模型职责仍由开发者设计。把业务模型与表解耦,是为了更容易改变、测试与维护,而不是为增加层次本身。
模型聚合DAO而非继承DAO关联本项背景,实质主要讲解在对应位置。
第五部分 附录
附录以规范化规则补足前文建模原则,再给出可追溯参考书目。
附录A 规范化规则
规范化从依赖解释修改异常。候选键和函数依赖帮助判断2NF、3NF及BCNF,多值依赖和连接依赖进一步说明4NF与5NF为何需要无损分解。高级范式不是按表数量升级,也不是所有项目的无条件目标;必须说明真实业务依赖及连接是否会制造不存在的事实。
规范化用明确规则减少冗余与异常,把前文反模式的经验放回可推理的模型。它服务正确存储与完整性,不是与灵活性对立的任意限制。
A.1 关系是什么
关系是各属性取值域所构成笛卡尔积中的一组合法元组,不只是表与表之间的连线。球队与城市例子把全部可能组合限制成实际所属关系;关系也可有更多属性。
关系保存合法的属性组合
关系由一组属性及其合法取值组合构成,可理解为多个值域笛卡尔积的一个子集。表是常见呈现形式,“关系型”不只指表间外键。球队与所属城市只是双属性示例,实际可有更多属性;理论关系的集合语义与SQL默认允许重复的多重集行为应区分。
关系保存合法的属性组合保存独立卡片。
原书依据 · 1 处
A.1.1 行之间没有上下顺序
关系中的行没有内在上下位置。查询需要特定输出顺序时必须显式排序,若同键并列还需说明如何稳定破同分。
结果顺序必须显式约定
关系事实不依赖表内行的上下位置。SQL结果若未指定ORDER BY,就不能依赖当前返回次序;索引或一次执行恰好有序也不是契约。需要稳定分页或比较时,还应给排序并列项定义唯一的次序。数据可见范围与结果内容仍取决于事务和查询,不因无序就保证每次内容相同。
结果顺序必须显式约定保存独立卡片。
原书依据 · 1 处
A.1.2 列之间没有左右顺序
同一事实可按不同属性显示顺序呈现,含义由属性名与值对应决定。SQL客户端却可能按位置取值,因此不能把关系无列序的理论误当接口无列序。
列序变化会破坏位置映射关联本项背景,实质主要讲解在对应位置。
A.1.3 重复行是不允许的
理论关系是集合,不含重复元组;SQL表若没有相应约束则可以保存重复。主键确保所选身份唯一,业务事实唯一还要按其真正属性组合表达。
函数依赖表示确定关系关联本项背景,实质主要讲解在对应位置。
关系无重复不等于SQL自动去重
理论关系以元组集合表达事实,没有重复元组。SQL表和结果默认并不自动满足这种集合特征,需要键、唯一约束或查询去重按需求实现。不同球队共享城市不构成整行重复;额外代理ID虽让行身份不同,也不自动阻止同一业务事实重复。
关系无重复不等于SQL自动去重保存独立卡片。
原书依据 · 1 处
A.1.4 每一列只有一种类型,每一行只有一个值
关系头定义属性名与域,实例行应使用同一组含义。EAV的通用值列或多态ID让同样外观的值需要额外解释,说明类型相同不等于业务含义相同;但不能简单断言EAV底层三列表在形式上不是关系。
关系头统一属性与值域含义
关系头规定属性名及值域,元组按这些属性赋值,同一属性在各行应具有一致解释。EAV通用字符串和多态ID需要依其他列解释业务类型,削弱直接表达的约束能力。底层三列表仍可能是形式上合法关系;问题是业务模型语义转移,不能只因值内容不同就判它不满足关系定义。
关系头统一属性与值域含义保存独立卡片。
原书依据 · 1 处
A.1.5 行没有隐藏组件
逻辑身份应由显式属性和键表达,而不是依赖引擎内部位置。原文把Oracle ROWNUM举成物理存储细节并不准确,它是查询选择行的编号;也不能把这种编号当稳定身份。
行号属于结果而非实体身份关联本项背景,实质主要讲解在对应位置。
A.2 规范化的神话
规范化不等于加代理键、尽量拆属性或保证查询慢。反规范化可能优化某一访问,却恶化其他访问;决定偏离时应先有正常模型,再对照实际成本与一致性责任。
反规范化收益必须按访问验证
规范化主要减少冗余和异常,反规范化可能让某类读取更直接,却增加其他查询及写入一致性成本。先明确正确的事实模型,再以代表性负载测量偏离收益与维护代价。它不是加代理键或无限拆列,不能用这些误解证明规范化无用。
反规范化收益必须按访问验证保存独立卡片。
原书依据 · 1 处
A.3 什么是规范化
规范化逐步排除不同依赖造成的冗余,使事实表达与约束更清楚。各范式针对的异常不同,不能把它们当越拆越快的优化等级;图中的箭头表示要求加强,而不是开发必须经历的运行流程。
规范化针对冗余与异常
规范化按属性之间的依赖组织事实,减少同一事实重复保存及由此引起的更新异常,并支持完整性。范式是满足某组规则的性质,不是表数量或性能评分。常见层次逐步加强,但应说明采用哪种定义与依赖,尤其不能以单个代理主键掩盖其他候选键和业务依赖。
规范化针对冗余与异常保存独立卡片。
A.3.1 第一范式
第一范式要求关系形态中的每个属性保存相应域的单值,而不是用同义列或逗号字段编码可变重复组。标签独立成行,使值集进入可查询、可约束的关系结构。
从属表让多值属性成行关联本项背景,实质主要讲解在对应位置。
第一范式去除重复属性组
第一范式要求元组按关系头为各属性提供相应域的单值。可变多个标签不应挤成逗号列表或任意tagN槽,而应在从属关系中一行一个标签。原子性与所需操作和域定义有关,不是见到字符串能拆字符就必须拆表;固定不同角色列也不能误判为重复组。
第一范式去除重复属性组保存独立卡片。
原书依据 · 1 处
A.3.2 第二范式
标签创造者由标签本身决定,不由“某缺陷使用某标签”这整个组合决定。把它重复放进BugsTags会出现同标签不同创造者,拆到Tags后才只存一份。
第二范式排除部分依赖
在已有第一范式基础上,非主属性不应只依赖某个候选键的真子集。例中身份为(bug_id, tag),tagger描述该次打标,coiner却只依赖tag;把创造者移到Tags,可避免各缺陷行不一致。判断要看全部候选键及事实依赖,不是仅选一个单列代理主键就自动消除部分依赖。
第二范式排除部分依赖保存独立卡片。
原书依据 · 1 处
A.3.3 第三范式
当前负责人邮箱由账号决定,若每条缺陷重复保存,会在改邮箱时出现不同副本。将邮箱存进Accounts,让缺陷引用账号,可以消除这类传递依赖冗余;若业务需要历史邮箱快照,则是另一项事实需求。
第三范式处理传递依赖
缺陷键确定负责人,负责人账号再确定当前邮箱,因此邮箱经负责人传递依赖缺陷键。把当前邮箱重复存在每条缺陷会产生更新不一致,通常应放在Accounts再关联读取。原文“与主键毫无关联”不准确,问题是这种传递依赖;历史快照若有独立语义,应另行建模而非机械删值。
第三范式处理传递依赖保存独立卡片。
原书依据 · 1 处
A.3.4 博伊斯—科德范式
BCNF检查每个非平凡函数依赖的决定项是否足以标识整行。标签决定类型,但标签本身不能决定哪条缺陷使用它,即使有多个候选组合,也可能重复保存标签类型。
BCNF要求决定项能标识整行
BCNF要求每个非平凡函数依赖的决定项是超键。超键能唯一确定整行,不必是最小候选键。例中每缺陷每种标签类型只选一个标签,候选键可为(bug_id, tag)或(bug_id, tag_type),但tag决定tag_type而tag不是超键;把类型放入Tags可减少重复。拆分后跨表的“每缺陷每类型一项”仍需维护。
BCNF要求决定项能标识整行保存独立卡片。
原书依据 · 1 处
A.3.5 第四范式
报告人、负责人、验证人若各有多个且彼此独立,把它们放一张关系表会产生重复组合和空值。各自建关系表,才能直接记录每类关联,而不假造三种角色的配对。
第四范式分开独立多值事实
同一缺陷的多个报告人、负责人和验证人若相互独立,把三组值合到一张表会重复组合,人数不匹配时还需空占位。分别保存三类关联可减少冗余。关键是“独立”:若某个报告人与某个负责人配对本身有业务含义,就不能擅自拆掉该事实;4NF针对非平凡多值依赖而非凡多列即拆。
结构图:
erDiagram
Bugs ||--o{ BugsReported : 报告者集合
Bugs ||--o{ BugsAssigned : 负责人集合
Bugs ||--o{ BugsVerified : 验证者集合
图以三类角色彼此独立为前提;每张关联表另以外键指向Accounts。
第四范式分开独立多值事实保存独立卡片。
原书依据 · 1 处
A.3.6 第五范式
工程师可服务哪些产品,与现在被指派处理哪些缺陷,是不同事实,原例将它们分表有明确建模价值。但第五范式的判断还要证明依赖与无损重构,不能由“主键不是复合”直接推出。
服务资格与当前指派分开
工程师能服务某产品,不等于此刻正在处理该产品的缺陷。把资格与当前指派塞在同一行,会让尚无任务的资格无法表达,既有资格也反复保存。分别记录EngineerProducts与BugsAssigned,让两类事实独立维护;若还要恢复某条缺陷的具体产品含义,须保留相应关系而不能任意联结推断。
服务资格与当前指派分开保存独立卡片。
原书依据 · 1 处
第五范式关注无损连接依赖
5NF要求非平凡连接依赖由候选键所蕴含。直观上,若事实能从更小投影无损重构,应避免额外冗余;拆分不能丢掉配对,也不能连接出原本没有的事实。原书“BCNF且没有复合主键即5NF”不能仅按所选主键判断;本例更直接说明资格与指派的事实分离,不能替代依赖证明。
第五范式关注无损连接依赖保存独立卡片。
原书依据 · 1 处
A.3.7 更多的范式
DKNF希望约束都由域和键推出,而6NF要求没有非平凡连接依赖,常用于把随时间变化的属性分开。两者关注点不同,不应把任何CHECK或历史表都直接等同某个范式。
域键范式让约束来自域与键
DKNF要求关系约束可由域约束与键约束推出。若“状态为NEW则工时为零且未验证”需要独立的跨属性条件,就不能仅凭各列合法域和键保证。判断看约束语义,不是看到CHECK或非键列便直接判违反:CHECK也可能只是表达域限制。
域键范式让约束来自域与键保存独立卡片。
原书依据 · 1 处
第六范式拆到不可再分事实
6NF要求关系不存在非平凡连接依赖,强调不可再无损分解的事实单元;不是禁止任何JOIN或连自明依赖也消除。属性随时间独立变化时,高度分解有助于分别保存历史,但会增加表与重组复杂度。单纯给每列加历史表不自动证明6NF,应核对键、时间与真实依赖。
第六范式拆到不可再分事实保存独立卡片。
原书依据 · 1 处
锚定建模的高度分解
Anchor Modeling是原书给出的6NF应用线索,通过高度分解表达实体及属性演进。其作者资料明确以6NF为基础;这里用于说明高范式有实际用途,不证明任何系统都应采用它。具体历史建模、表消除优化与工程代价须继续专门研究。
锚定建模的高度分解保存独立卡片。
原书依据 · 1 处
附录B 参考书目
本附录是参考书目,为关系模型、模式、性能、浮点和软件工程提供继续查阅的来源。这里已逐项登记实际书目信息,不把标题提名补造成已读原作的知识。后续查阅应核对具体版次和原文,当前笔记不声称完成这些外部作品的阅读。
附录B列出16项书籍和文章,对应反模式、关系理论、浮点、安全、SQL、架构、Rails和设计方法。它提供检索入口而没有展开各作品内容,故只登记文献,不冒充已经阅读这些作品。
跨章综合
许多反模式都把显式结构换成隐式约定。逗号列表、多态ID、EAV和元数据分裂,看起来减少了表或修改次数,却让类型、引用与查询规则散落到应用。附录解释这些结构背后的依赖,帮助判断哪些事实应拆开、哪些应共同约束。拆分不是目标,能够一致表达事实才是。
数据库约束与应用验证相互补充。外键、唯一性和类型覆盖所有经过该引擎的写入,应用则说明更丰富的业务规则和用户反馈。参数绑定保护语句结构,仍不能证明用户有权修改那条记录;密码散列保护泄漏后的凭据,仍不能替代传输与重置流程。每一种保证都应写清作用范围。
表示、查询和性能需要分别验证。浮点误差不能用更漂亮的格式消除,错误统计不能靠加索引变正确,随机查询也不能仅凭速度判断均匀。先固定语义、边界和结果粒度,再观察目标引擎的计划与代价,最后比较替代方案。书中的SQL是讨论载体,不能省去对代码自身的核对。
维护性来自可追踪的责任。显式列清单、稳定身份、可识别错误、版本化迁移和边界清楚的模型,都让改变的影响可见。单条语句、单张表或单个模型类并不天然更简单;应比较修改发生时需要同时理解和保持一致的范围。
发散与实践
以下为读者延伸计划。第一项以SQLite3.46.1固定提交解释外键检查:已实际读检查入口、机制注释和完整fkey1测试文件,形成首轮映射;VM执行与提交细节继续保留待办。第二项将本书已经发现的历史版本差异用于资料库版本核对表。两项保存于ideas目录。
优先进行第一项,因为它把本书“把引用完整性放在共同入口”的论点连接到真实实现。计划不要求重写数据库,也不表示已执行测试、迁移或修改当前数据库配置。