数据表

• 说到数据表时，一般是指关系型数据库的数据表（table）。非关系型数据库一般不使用数据表这种数据结构。
• 一张数据表中，可以记录多行数据。每行数据拥有相同数量、类型的字段。

主键

• 主键（Primary Key）：数据表中每行数据的唯一标识。
  • 通常选取一个在各行数据中取值都不同的字段（比如编号），作为主键，以便区分各行数据。
  • 每行数据都应该拥有主键字段，且取值不同。
  • 主键的值应该固定不变，否则可能导致定位不到数据。
• 复合主键（Composite Key）：在一个数据表中，由多个字段组合成一个主键。

设计范式

• 创建数据表时有一些流行的规范，称为设计范式（Normal Form ，NF）。

  • 例如：1NF、2NF、3NF、BCNF、4NF、5NF、6NF 。

• 一个数据表的示例：

  学号	姓名	学科	分数
  20201201	张三	语文	80
  20201201	张三	数学	90
  20201202	李四	语文	80
  20201202	李四	数学	80
  20201203	张三	语文	70
  20201203	张三	数学	90

• 依赖关系：

  • 假设一个或一组属性 A 的值确定时，属性 B 的值就会确定不变，则称 B 依赖 A 。
  • 例如上表中，"姓名" 依赖 "学号" ，但 "学号" 不依赖 "姓名" ，因为学号不同的两个人可能重名.

• 完全依赖：

  • 假设属性 B 依赖 A ，A 是单个属性，或者 A 是一组属性但 B 并不依赖 A 的真子集，则称 B 完全依赖 A 。

• 部分依赖：

  • 假设属性 B 依赖 A ，A 是一组属性，并且 B 依赖 A 的真子集，则称 B 部分依赖 A 。
  • 部分依赖可以简化成完全依赖的形式。
  • 例如上表中，"姓名" 完全依赖 "学号" ，部分依赖 "学号,学科" 。

• 传递依赖：

  • 假设 C 依赖 B ，而 B 依赖 A ，则称 C 传递依赖 A 。

• 码：

  • 假设一个或一组属性 A 的值确定时，其它属性的值都确定不变，则称 A 构成了候选码（candidate key），简称为码。
  • 码可以用作每行数据的唯一标识。
  • 一个数据表中可能存在零个、一个或多个码。通常只需选用一个码，称为主码（即主键）。
  • 构成码的属性称为主属性，其它属性称为非主属性。

1NF

：第一范式。

• 特点：

  • 数据的每个属性具有原子性。即数据表中最小的管理单位是字段。

• 例：

  学号	学科分数
  20201201	语文 80、数学 90

  应该将 "学科分数" 拆分成更明确的字段：

  学号	学科	分数
  20201201	语文	80
  20201201	数学	90

  或者拆分成多张数据表，通过外键关联。

2NF

：第二范式。

• 特点：

  • 满足第一范式作为基础。
  • 每个非主属性对于码，都是完全依赖，不能部分依赖。即数据表中，主键应该为单个字段，或者最简化的复合主键。

• 例：

  学号	姓名	学科	分数
  20201201	张三	语文	80
  20201201	张三	数学	90

  假设这里用 "学号,学科" 作为主键，则 "分数" 完全依赖它，但 "姓名" 部分依赖它。应该拆分为两张表：

  学号	姓名
  20201201	张三

  学号	学科	分数
  20201201	语文	80
  20201201	数学	90

3NF

：第三范式。

• 特点：

  • 满足第二范式作为基础。
  • 每个非主属性对于码，不能传递依赖。即非主键的其它字段之间，不存在依赖关系。

• 例：

  学号	姓名	班级号	班主任
  20201201	张三	001	刘老师
  20201202	李四	002	李老师

  假设这里用 "学号" 作为主键，则 "班主任" 依赖于 "班级" ，应该拆分为两张表：

  学号	姓名	班级号
  20201201	张三	001
  20201202	李四	002

  班级号	班主任
  001	刘老师
  002	李老师

  这样能减少单张数据表中重复出现的字段。

BCNF

• 特点：
  • 满足第三范式作为基础。
  • 每个主属性对于码，不能部分依赖、传递依赖。即数据表中只存在一个码。

索引

• 很多种数据库都提供了索引（index）功能，为数据表创建索引表，用于加速查询。

  • 假设一张数据表中有 1000 条数据，每条数据有 10 个字段。则用户查询字段 name=one 的数据时，流程如下：
    1. 读取第一条数据的全部字段，如果其中 name 字段的值匹配查询条件，则返回这条数据给用户。
    2. 重复第一步的操作，逐一读取各条数据并检查，直到完成全表扫描。
  • 如果事先另外建立一张表，称为索引表，专门记录每条数据的存储地址 address 以及 name 字段的值，不记录其它字段。则用户查询字段 name=one 的数据时，流程如下：
    1. 逐一读取索引表中的各条数据，如果某条数据的 name 字段匹配查询条件，则暂记其 address 。
    2. 根据 address 从原表读取完整的那条数据，包含全部字段，返回给用户。该过程称为回表查询。
  • 例如使用 MySQL 数据库时，用户可以对一张数据表的多个字段建立多个索引，然后在 where 子句中，刻意使用已建立索引的字段作为查询条件，MySQL 便会自动使用索引加速查询。
    • 例如 id 字段建立了索引，而 name 字段没有索引，则查询 where id=1 比 where name='one' 快很多。

• 启用索引的效果：

  • 优点：
    • 读取的数据量少。
      • 查询索引表时，每条数据只需读取部分字段，不必读取原表的全部字段，避免了全表扫描，因此大幅减少查询耗时、读取磁盘量、占用内存。
    • 不用遍历全部数据。
      • 在原表查找一条数据时，通常需要逐一读取每条数据，时间复杂度为 O(n) 。而索引表可以采用 B+ tree 等数据结构，通过二分法等方式提高查找效率。
      • 因此一般在索引表中查找一条数据时，不必遍历所有条数据。即使遍历了所有条数据，也不必读取原表的全部字段，依然比查询原表快。
    • 覆盖索引。
      • 如果查询语句只需要访问索引表中已有的字段，则不必从原表读取其它字段。这种情况称为覆盖索引（covering index），即一个能覆盖原表的索引表，避免了回表查询，减少开销。
      • 例如 id 字段建立了索引，则查询 select id from tb1 where id is not null 就会遍历索引表中的所有条数据，返回它们 id 字段的值，不会读取原表。
  • 缺点：
    • 数据表启用索引之后，读操作更快，写操作更慢。
      • 每次在原表新增、删除、修改一条数据，都需要同步修改索引表中的条目。
      • 索引表采用 B+ tree 等存储结构时，新增一条数据，可能需要对相邻的一些数据重新排序，消耗一定时间。
    • 查询结果包含很多条数据时，用索引查询也慢。
      • 如果在索引表的查询结果包含很多条数据，需要回表查询，则会多次随机读取磁盘，耗时较久，甚至可能比查询原表时顺序读取磁盘的耗时更久。
      • 因此应该只通过索引表查找少量数据，或者尽量实现覆盖索引。
    • 原表非常大时，用索引查询也慢。
      • 如果原表非常大，比如有几亿条数据，则索引表的体积也会很大，查询也慢。此时建议拆分成多个数据表。

• 索引按范围分类：

  • 稠密索引
    • ：对全部数据建立索引，每条数据都在索引表中有一个索引项。
    • 例如：给每条数据记录一个唯一编号 id 、该数据的存储地址 address ，想读取某条数据时，可直接根据 id 找到其 address 。
    • 每次在原表新增、删除、修改一条数据，都需要修改索引表中的索引项。
  • 稀疏索引
    • ：只对部分数据建立索引。
    • 例如：将 n 条数据存储到连续的地址空间，每条数据占用的存储空间一样大。然后只记录第 1 条数据的存储地址，因为其后 n-1 条数据的存储地址都可以推算出来。
    • 与稠密索引相比，稀疏索引建立的索引表更小，且不必每次修改数据都更新索引表，但查询耗时更久，因为需要推算。

• 索引按方向分类：

  • 正排索引
    • 原理：
      1. 当用户搜索一个字符串 keyword 时，检查所有数据，找出包含该字符串的所有条数据的 id 。
      2. 然后建立索引：某个字符串，存在于 id 为 xxx 的数据中。
  • 倒排索引
    • 原理：
      1. 事先建立索引：某个单词，存在于 id 为 xxx 的数据中。
      2. 当用户搜索一个字符串时，先将它拆分成多个单词，然后根据索引找到包含每个单词的数据。
    • 优点：搜索字符串时，速度比正排索引快很多。
    • 缺点：需要事先建立倒排索引。
    • ElasticSearch 等多种数据库都支持倒排索引，以大幅提高全文搜索的速度，方便在海量文本中搜索一个字符串。

列式存储

以该表为例，主键为 id ，比较数据表的两种存储架构：

id	student_id	student_name	subject	score
1	20201201	张三	语文	80
2	20201201	张三	数学	90
3	20201202	李四	语文	80
4	20201202	李四	数学	80

• 行式存储（Row-oriented Storage）

  • ：存储时以 row 为单位。
  • 比如先存储 id=1 的那行数据：

    1:20201201,张三,语文,80
    

    再存储 id=2 的那行数据：

    2:20201201,张三,数学,90
    

    每行数据的全部字段存储在连续的地址空间，不同行数据的地址空间则不一定相邻。
  • 优点：
    • 写入、读取一整行数据的速度较快，因为是顺序访问磁盘。
  • 缺点：
    • 读取时，以 row 为单位，不能以 filed 为单位。比如想读取 id=1 的那行数据的 student_name 字段，实际却要读取整行数据（即全部字段），然后取出其中的 student_name 字段，这样增加了额外的开销。
  • 传统的数据库大多采用行式存储。但用户读取数据表时，大多场景下不想读取一整行数据，只想读取一个或多个字段，而行式存储会导致读取速度慢。
    • 比如执行 SELECT student_name FTOM tb WHERE id=1 ，只想读取一个字段，实际却要读取这行数据的全部字段。
    • 比如执行 SELECT id FTOM tb WHERE student_name=张三 ，只想读取所有行数据的 student_name 字段，实际却要读取每行数据的全部字段，即全表扫描。
    • 为了加快读取某个字段的速度，用户通常会对该字段建立索引。比如对 student_name 字段建立索引。

• 列式存储（Column-oriented Storage）

  • ：存储时以 filed 为单位，不同字段的数据分别存储。
  • 比如将所有行数据的 student_id 字段存储在连续的地址空间：

    1:20201201
    2:20201201
    3:20201202
    4:20201202
    

    将所有行数据的 student_name 字段存储在连续的地址空间：

    1:张三
    2:张三
    3:李四
    4:李四
    

  • 优点：
    • 读取时，以 filed 为单位。比如用户可以只读取 id=1 的那行数据的 student_name 字段，不必读取整行数据（即全部字段）。
    • 同一字段的多项数据存储在连续的地址空间，批量读取时属于顺序访问磁盘，能更快地统计分析。比如用户可读取所有行数据的 score 字段，计算平均值。
    • 同一字段的多项数据存储在连续的地址空间，通常内容相似，存储时的压缩率更高。
  • 缺点：
    • 写入、读取一整行数据的速度较慢，因为是随机访问磁盘。
      • 写入一整行数据时，需要先拆分各个字段，然后分别存储。
      • 读取一整行数据时，需要先读取该 id 的各个字段取值，然后拼接成一行数据。
  • 与行式存储相比，列式存储相当于对每个字段都建立了单列索引，因此大多场景下查询速度更快，更适合 OLAP 。