# 数据表
- 说到数据表时,一般是指关系型数据库的数据表(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 的数据时,流程如下:
- 读取第一条数据的全部字段,如果其中 name 字段的值匹配查询条件,则返回这条数据给用户。
- 重复第一步的操作,逐一读取各条数据并检查,直到完成全表扫描。
- 如果事先另外建立一张表,称为索引表,专门记录每条数据的存储地址 address 以及 name 字段的值,不记录其它字段。则用户查询字段 name=one 的数据时,流程如下:
- 逐一读取索引表中的各条数据,如果某条数据的 name 字段匹配查询条件,则暂记其 address 。
- 根据 address 从原表读取完整的那条数据,包含全部字段,返回给用户。该过程称为回表查询。
- 例如使用 MySQL 数据库时,用户可以对一张数据表的多个字段建立多个索引,然后在 where 子句中,刻意使用已建立索引的字段作为查询条件,MySQL 便会自动使用索引加速查询。
- 例如 id 字段建立了索引,而 name 字段没有索引,则查询
where id=1
比where name='one'
快很多。
- 例如 id 字段建立了索引,而 name 字段没有索引,则查询
- 假设一张数据表中有 1000 条数据,每条数据有 10 个字段。则用户查询字段 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 条数据的存储地址都可以推算出来。
- 与稠密索引相比,稀疏索引建立的索引表更小,且不必每次修改数据都更新索引表,但查询耗时更久,因为需要推算。
- 稠密索引
索引按方向分类:
- 正排索引
- 原理:
- 当用户搜索一个字符串 keyword 时,检查所有数据,找出包含该字符串的所有条数据的 id 。
- 然后建立索引:某个字符串,存在于 id 为 xxx 的数据中。
- 原理:
- 倒排索引
- 原理:
- 事先建立索引:某个单词,存在于 id 为 xxx 的数据中。
- 当用户搜索一个字符串时,先将它拆分成多个单词,然后根据索引找到包含每个单词的数据。
- 优点:搜索字符串时,速度比正排索引快很多。
- 缺点:需要事先建立倒排索引。
- ElasticSearch 等多种数据库都支持倒排索引,以大幅提高全文搜索的速度,方便在海量文本中搜索一个字符串。
- 原理:
- 正排索引