# 数据表

  • 说到数据表时,一般是指关系型数据库的数据表(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=1where 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 等多种数据库都支持倒排索引,以大幅提高全文搜索的速度,方便在海量文本中搜索一个字符串。