数据库系统设计（索引、优化、范式、陷阱等）

一、数据独立性

• 物理数据独立性：物理级别可以更改，而无需更改逻辑级别
• 逻辑数据独立性：逻辑级别可以更改，而不必更改外部级别

---

二、数据库系统设计

1. 数据库规划
2. 系统定义
3. 需求收集和分析
4. 数据库设计
   1. 逻辑数据库设计：识别需要在数据库中表示的重要对象以及这些对象之间的关系
      • 创建并检查ER模型
   2. 物理数据库设计：描述如何在目标DBMS中物理实现逻辑设计（作为表）
      • 将ER模型映射成为物理表
5. DBMS选择
   1. 成本、功能性问题、操作系统问题、系统平台问题…
6. 应用设计
7. 原型设计
8. 数据转换和加载
9. 测试
10. 运行和维护

数据库一些知识：

• 一个数据库是操作系统文件的集合
• 数据库之间不能进行文件共享
• 一个数据库至少包括一个数据文件和一个日志文件
• 数据和日志文件最好不要放在同一驱动器上
• 数据库中每个文件的信息和属性存放在数据库系统视图sys.database_files
  • 主数据文件（.mdf）
  • 辅助数据文件（.ndf）
  • 日志文件（.ldf）
• DBMS通常提供两种类型的数据库安全：
  • 系统安全：系统级访问和使用数据库（如用户名/口令）
  • 数据安全：访问和使用数据库对象（如表和视图）

---

三、数据完整性

• 实体完整性：每个表都必须有一个有效的主键。
• 参照完整性：没有不匹配的外键值。引用完整性要求外键值与它们所引用的表中的现有主键值匹配。（父表的主键在子表作为外键）

---

四、数据类型

• Date，DateTime，Timestamp

  • Date 表示年月日，如YYYY-MM-DD
  • DateTime 表示年月日和时间信息，如YYYY-MM-DD HH:MM:SS
  • Timestamp 和DateTime表示的信息相同，但时间范围不同

• Char，Varchar

  • Char：长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；
  • 有var前缀的，长度可变的，表示实际存储空间是变长的，比如varchar, nvarchar。
  • 注意：Varchar类型的实际长度是它的值的实际长度+1，最后一个字节用于保存实际使用了多大的长度

• nUnicode，non-Unicode

  • 数据库中，英文字符只需要一个字节存储，但汉字和其他众多非英文字符，则需要两个字节存储。如果英文与汉字同时存在，由于占用空间数不同，容易造成混乱，导致读取出来的字符串是乱码。
  • Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。而前缀n就表示Unicode字符。

char，varchar	最多8000个英文，4000个汉字
nchar，nvarchar	可存储4000个字符，无论英文还是汉字

总结：

• CHAR，NCHAR ：定长，速度快，占空间大，读取可能需处理。
• VARCHAR，NVARCHAR，TEXT ：不定长，空间小，速度慢，读取无需处理。
• NCHAR、NVARCHAR、NTEXT ：处理Unicode编码，不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

---

五、数据库范式

数据库规范化是组织关系数据库的列（属性）和表（关系）以最小化数据冗余的过程。

• 1NF：要求数据库中的表都是二维表
• 2NF：消除非主属性对主键的部分函数依赖
• 3NF：消除非主属性对主键的传递函数依赖
• BCNF：消除主属性对主键的部分及传递函数依赖
• 4NF：消除非平凡多值依赖

反规范化：有时规范化的数据库设计不能提供最大的处理效率，可能有必要接受不满足规范化设计却有利于性能的某些好处的损失。

实际上反规范化设计是使用空间（冗余）换取时间（效率）

要考虑读写操作比例，读取操作与写入操作的比例为4:1或者3:1时，读操作的频率要远远高于写操作，写操作的效率降低并不能抵消读操作的效率提高所带来的总体性能提升。这时应该考虑使用反规范化设计。

---

六、分解

• 无损分解：对关系模式分解时，原关系模型下任一合法的关系值在分解之后应能通过自然联接运算恢复起来
• 有损分解：对关系模式分解时，原关系模型下任一合法的关系值在分解之后不能通过自然联接运算恢复起来

无损分解举例：r = r1 ⋈ r2：（r表分解为r1和r2后，自然连接可恢复）

r	A	B	C
	1	1	1
	1	2	1

r1	A	B
	1	1
	1	2

r2	A	C
	1	1

有损分解举例：r = r1 ⋈ r2：（r表分解为r1和r2后，自然连接不可恢复）

r	A	B	C
	1	1	4
	1	2	3

r1	A	B
	1	1
	1	2

r2	A	C
	1	4
	1	3

---

七、派生属性

• 由单个属性派生而出;
• 由多个属性派生而出；
• 由其他属性的多个值派生而出；
• 由其他表的多个属性的多个值派生而出；

数据库中，一般只存基属性值，而派生属性只存其定义或依赖关系，用时再从基属性中计算出来。

---

八、递归关系

举例一：正在为一个新闻网站开发一个评论功能，读者可以评论原文甚至相互回复。相互回复会导致无限多的分支，无限多的祖先-后代关系。

举例二：正在设计一个描述公司员工之间上下级关系的表，A员工是B员工上级的同时，A员工也会是C员工的下级，这会导致无限多的分支，无限多的祖先-后代关系。

上述情况就是一种典型的递归关系数据。这种情况，需要将该表的主键设计为该表的外键，形成递归关系。

---

九、连接陷阱

• 扇形陷阱：两个实体之间有一个“一对多”关系，该关系从第三个实体向外展开，但这两个实体之间应该有一个直接关系，以提供必要的信息。
  • 举例：一个部门有多个员工（部门一对多员工），一个部门有多辆汽车（部门一对多汽车），此时无法描述员工和汽车的使用关系（扇形陷阱），需要在员工和汽车之间建立关系，来表示员工对汽车的使用关系。
• 深坑陷阱：模型表明实体之间存在关系，但某些实体之间不存在路径
  • 举例：一个部门有多个汽车（部门一对多汽车），一个员工使用一辆汽车（员工一对一汽车），此时无法描述部门和员工的使用关系，中间产生了断层（深坑陷阱），需要在员工和部门之间建立关系，来表示员工和部门之间的关系。

---

十、继承结构

• 局部特殊化：超类的某些实例不属于任何子类类型（ER图中一条横线）
• 全局特殊化：超类的所有实例都必须至少属于一个子类类别（ER图中两条横线）
• 分离规则：超类的一个实例至多属于一个子类类别（ER图中横线上圆圈的字母是d）
• 交叠规则：一个超类实例可能属于超过一个子类类别（ER图中横线上圆圈的字母是o）

继承结构为数据模型添加了更多的信息，但是也使模型更加复杂。

---

十一、逻辑数据库设计

1. 标识实体：查找需求说明书中的名词或名词短语，查找主要的实体对象
2. 标识关系：查找需求说明书中的动词和动词短语，使用ER建模，同时检查扇形和深坑陷阱、建立外键
3. 标识实体或关系实体的相关属性：命名、类型及其长度、是否允许null、是否是派生的…
4. 确定属性域：确定属性的允许值和格式
5. 确定候选键、主键：Unique、Not Null、Minimal、Nonupdateable
6. 特化和泛化实体：如果合适的话，标识超类和子类
7. 检查模型的冗余性：删除冗余
8. 检查模型是否支持用户事务
9. 创建表：将ER模型映射为数据库表
10. 使用规范化方法（范式）检查表结构
11. 检查表是否支持用户所需的事务：检查所建的表是否如用户需求说明中所要求的那样。
12. 检查业务规则

---

十二、物理数据库设计

• 尽量避免使用触发器：可能产生意想不到的数据异常、日后维护可能遇到困难、使业务逻辑变得复杂…
• 在高并发应用中避免使用外键约束：降低数据导入效率、增加维护成本…
• 反规范化设计

---

十三、物理存储

• 易失性存储：关闭电源时丢失内容
  • 主存储器：速度最快但不稳定的介质，如（缓存、主存储器）
• 非易失性存储：即使关闭电源，内容也会保持不变。包括二级和三级存储器，以及电池备份主存储器。
  • 二级存储：层次结构中的下一级，非易失性，访问速度适中，也称为在线存储，如闪存、磁盘
  • 三级存储：层次结构中的最低级别，非易失性，访问时间慢，也称为离线存储，如磁带、光盘存储

内存和外存的一次数据交换称为一次I/O操作，每次交换的数据量是一个Block。

内存中开辟的缓冲区大小至少要等于一个block，Block的大小通常由DBMS厂商决定。

记录在文件中的存储组织：

• 堆–记录可以放在文件中有空间的任何地方
• Sequential—根据每条记录的搜索键值，按顺序存储记录
• 散列–对每条记录的某些属性计算的散列函数；结果指定记录应放在文件的哪个块中
• 每个关系的记录可以存储在单独的文件中。在一个多任务集群文件中，几个不同关系的记录可以存储在同一个文件中
  • 动机：将相关记录存储在同一块上，以最小化I/O

---

十四、索引

索引是用于加快访问所需数据的机制。例如，图书馆图书的标签。

14.1 顺序索引和散列索引：

• 顺序索引：搜索键按有序顺序存储
  • 主索引（Primary index）：在顺序文件中，其搜索键指定文件顺序的索引。也称为聚簇索引
    • 主索引的搜索键通常是主键，但不一定是主键
  • 二级索引（Secondary index）：其搜索键指定的顺序与文件的顺序不同。也称为非聚簇索引
  • 索引顺序文件（Index sequential file）：带有主索引的有序顺序文件
• 散列索引：使用“Hash函数”在“bucket”中均匀分布搜索键

每个表只能有一个主索引或一个聚簇索引。二级索引为可用于更有效地检索数据的基表提供附加键。

14.2 密集索引和稀疏索引：

• 密集索引：文件中的每个搜索关键字值都会出现在索引记录中。
• 稀疏索引：只包含某些搜索关键字值的索引记录，当记录按搜索键顺序排序时适用。
  • 例如，要查找具有搜索关键字值K（比如100）的记录，则需要查找最大搜索关键字值小于K的索引记录（比如98），然后从索引记录指向的记录开始按顺序搜索文件
  • 稀疏索引和密集索引相比：
    • 插入和删除的空间更少，维护开销更少
    • 查找记录的速度通常比密集索引慢

可建立辅助索引，它对每个搜索键值都有一索引记录，索引记录指向包含具有该搜索键值的所有实际记录的指针的桶（建立索引的索引）。

单级索引项删除：

• 密集索引：删除搜索键类似于删除文件记录。
• 稀疏索引：如果索引中存在搜索关键字的条目，则通过将索引中的条目替换为文件中的下一个搜索关键字值（按搜索关键字顺序）来删除该条目。如果下一个搜索关键字值已经有索引项，则该项将被删除而不是替换。

14.3 B+树索引

B+树索引是索引顺序文件的替代方法。B+树利大于弊，被广泛使用。

索引顺序文件的缺点：

• 由于创建了许多溢出块，性能随着文件的增长而降低。
• 需要对整个文件进行定期重组。

B+树索引文件的优点：

• 面对插入和删除，自动用小的、本地的更改重新组织自身。
• 为了保持性能，不需要对整个文件进行重组。

B+树的缺点：

• 额外的插入和删除开销，空间开销。

B+树满足的属性：

• 从根到叶的所有路径长度相同
• 不是根或叶的每个节点都有⌈𝑛/2⌉到n个子节点。
• 叶节点的值介于⌈（𝑛-1）/2⌉和n–1之间
• 特殊情况：
  • 如果根不是一片叶子，它至少有两个孩子。
  • 如果根是叶（即树中没有其他节点），则其值可以介于0和（n–1）之间。

除了根节点外，所有树节点都必须保持50%的占用率(即半满）

B+树有n个指针，n-1个搜索码值。

14.4 B树索引

B树允许搜索关键字值只出现一次，消除了搜索关键字的冗余存储：

• 非叶节点中的搜索键不会出现在B-树中的其他位置
• 必须包含非叶节点中每个搜索键的附加指针字段。

B树索引的优点：

• 可能比相应的B+树使用更少的树节点。
• 有时可以在到达叶节点之前找到搜索键值。

B-树索引的缺点：

• 在所有搜索关键字值中，只有一小部分是在早期找到的
• 非叶节点更大，因此扇出减少，通常比相应的B+树具有更大的深度
• 插入和删除比B+树更复杂
• 实现比B+树更困难。

一般来说，B树的优点并不胜过缺点。

14.5 使用方案

是否使用索引和使用什么类型的索引应考虑的因素:

• 定期重组成本
• 插入和删除的相对频率
• 以牺牲最坏情况下的访问时间为代价优化平均访问时间是否可取？

什么时候用什么索引会更好:

• 哈希通常更擅长检索具有指定键值的记录。
• 如果范围查询是常见的，则优先使用顺序索引

哪些DBMS支持哪些类型索引:

• PostgreSQL支持散列索引，但由于性能不佳而不鼓励使用
• Oracle支持静态哈希组织，但不支持哈希索引
• SQLServer只支持B+树
• MySQL支持B树索引

14.6 使用总结

• 对于更新事务，要注意被更新的列，这些列不适合作为索引列。
• 在where子句中的列，可能是索引的候选。
• 联接列，也可能是索引的候选。
• 对常用和关键事务中的查询条件使用的列，需要重点考虑。
• 如果查询条件包含多个条件，并且条件中包括一个or子句，而该条件没有索引，那么为其他列添加索引将不会改善查询速度。
• 建议：
  • 不要索引小表。
  • 如果不是文件组织的键，则为表的索引主键
  • 向任何常用作辅助键的列添加辅助索引。
  • 如果FK经常被访问，则向其添加辅助索引。
  • 在涉及的列上添加辅助索引：选择或联接条件；排序依据；分组依据；以及涉及排序的其他操作（例如UNION或DISTINCT）。

---

十五、查询优化

给定一个关系代数表达式，查询优化器的主要任务是产生一个查询执行计划，该计划能获得与原关系表达式相同的结果，并且得到结果的执行代价最小。

具体步骤：

• 生成逻辑上与给定表达式等价的表达式
  • 利用等价规则将一个表达式转换成另一个等价的表达式
• 注解(Annotate)结果表达式以得到其他查询执行计划
• 基于估算代价选择最廉价的计划

整个过程称为基于代价(cost based)的优化，查询执行计划的开销估算可以基于：

• 关系的统计信息：元组数目、一个属性的非重复值数目
• 中间结果的大小
• 各类算法的开销计算

优化步骤：

1. 分解合取选择成为一个单选择操作序列
2. 将选择操作移到查询树下方以便尽早执行
3. 首先执行能产生最小关系的选择和连接操作
4. 笛卡儿积操作后接选择条件用连接操作替换
5. 将投影属性列表分解并尽可能移到查询树下方，必要时创建新投影
6. 确认其操作可以流水线化的子树，并利用流水线执行之

核心：

• 尽早执行选择，减小要连接的关系的大小
• 尽早执行投影，减小要连接的关系的大小

---

十六、冲突可串行化（优先图）

如果某个调度S的冲突等价于一个串行调度，则说明这个调度S是冲突可序列化的。

优先图(precedence graph)：

一个调度S的优先图是这样构造的：它是一个有向图G =（V，E），V是顶点集，E是边集。顶点集由所有参与调度的事务组成，边集由满足下述条件之一的边Ti→Tj组成：

• 在Tj执行read(Q)之前，Ti执行write(Q)
• 在Tj执行write(Q)之前，Ti执行read(Q)
• 在Tj执行write(Q)之前，Ti执行write(Q)

冲突可串行化判定准则：

• 如果优先图中存在边Ti→Tj ，则在任何等价于S的串行调度S’中，Ti都必须出现在Tj之前
• 如果调度S的优先图中有环，则S是非冲突可串行化的。如果图中无环，则S是冲突可串行化的

---

十七、NoSQL

NoSQL分类：

分类	主流产品	典型应用场景	数据模型	优点	缺点
键值（key-value）	Tokyo Cabinet/Tyrant, Redis, Voldemort, Oracle BDB	内容缓存，主要用于处理大量数据的高访问负载，也用于一些日志系统等等。	Key 指向 Value 的键值对，通常用hash table来实现	查找速度快	数据无结构化，通常只被当作字符串或者二进制数据
列存储数据库	Cassandra, HBase, Riak	分布式的文件系统	以列簇式存储，将同一列数据存在一起	查找速度快，可扩展性强，更容易进行分布式扩展	功能相对局限
文档型数据库	CouchDB, MongoDB	Web应用（与Key-Value类似，Value是结构化的，不同的是数据库能够了解Value的内容）	Key-Value对应的键值对，Value为结构化数据	数据结构要求不严格，表结构可变，不需要像关系型数据库一样需要预先定义表结构	查询性能不高，而且缺乏统一的查询语法。
图形(Graph)数据库	Neo4J, InfoGrid, Infinite Graph	社交网络，推荐系统等。专注于构建关系图谱	图结构	利用图结构相关算法。比如最短路径寻址，N度关系查找等	很多时候需要对整个图做计算才能得出需要的信息，而且这种结构不太好做分布式的集群方案。

NoSQL的重点是non-relational，有如下优势：

• 易扩展
  • NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。也无形之间，在架构的层面上带来了可扩展的能力。甚至有多种NoSQL之间的整合。
• 灵活的数据模型
  • NoSQL无需事先为要存储的数据建立字段，随时可以存储自定义的数据格式。而在关系数据库里，增删字段是一件非常麻烦的事情。如果是非常大数据量的表，增加字段简直就是一个噩梦。
• 高可用
  • NoSQL在不太影响性能的情况，就可以方便的实现高可用的架构。比如Cassandra，HBase模型，通过复制模型也能实现高可用。
• 大数据量，高性能
  • NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。

NoSQL同样有不适用的场景和不足之处：

不适用场景：

• 要求高度事务性的系统
• 传统的商业智能应用
• 复杂的跨文档（表）级联查询

劣势：

• 开发消耗高
• 商业资源模式少
• 功能不够齐全
• 实际上技术还不是完全成熟

17.1 分布式数据库

分布式数据系统的CAP原理的三要素：

• 一致性(Consistency)：在分布式系统中的所有数据备份，在同一时刻具有同样的值。（等同于所有节点访问同一份最新的数据副本）。**NoSQL系统通常注重性能和扩展性，而非事务机制（事务就是强一致性的体现）
• 可用性(Availability)：在集群中一部分节点故障后，集群整体依然可以响应客户端的读写请求。
• 分区容忍性(Partition tolerance)：系统在物理网络分区的情况下依然工作良好。分区：（两个区域网络不连通）

CAP原理：在分布式系统中，这三个要素最多只能同时实现两点，不可能三者兼顾：

对于分布式数据系统，分区容忍性是基本要求。而由于当前的网络硬件肯定会出现延迟丢包等问题，所以分区容忍性是必须需要实现的。所以只能在一致性和可用性之间进行权衡，没有NoSQL系统能同时保证这三点。

对于大多数web应用，牺牲一致性而换取高可用性，是目前多数分布式数据库产品的方向。

17.2 三个“一致性”：

• 强一致性：（即时一致性） 假如A先写入了一个值到存储系统，存储系统保证后续A，B，C的读取操作都将返回最新值。
• 弱一致性：假如A先写入了一个值到存储系统，存储系统不能保证后续A，B，C的读取操作能读取到最新值。此种情况下有一个“不一致性窗口”的概念，它特指从A写入值，到后续操作A，B，C读取到最新值这一段时间。
• 最终一致性：最终一致性是弱一致性的一种特例。假如A首先write了一个值到存储系统，存储系统保证如果在A，B，C后续读取之前没有其它写操作更新同样的值的话，最终所有的读取操作都会读取到A写入的最新值。此种情况下，如果没有失败发生的话，“不一致性窗口”的大小依赖于以下的几个因素：交互延迟，系统的负载，以及复制技术中replica的个数。