SQL必知必会-进阶篇[SQL学习笔记]

本篇博客是对于陈旸老师极客专栏“SQL 必知必会”进阶篇的笔记总结。需要学习资料可私信。

第20课

数据库优化

需要思考的几个问题

• 数据库调优的目标（目的）
• 如何确定调优的目标
• 如何进行数据库调优（三个方面）

第21课

数据库的设计范式都有哪些？

6 种范式，按照范式级别，从低到高分别是：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BCNF（巴斯 -科德范式）、4NF（第四范式）和 5NF（第五范式，又叫做完美范式）。

数据表的键都有哪些？

• 超键：能唯一标识元组的属性集叫做超键。

• 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。

• 主键：用户可以从候选键中选择一个作为主键。

• 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。

• 主属性：包含在任一候选键中的属性称为主属性。

• 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

举个例子

• 球员表（player）：球员编号、姓名、身份证号、年龄和球队编号
• 球队表：球队编号、主教练和球队所在地。
• 超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。
• 候选键就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
• 主键是我们自己选定，也就是从候选键中选择一个，比如（球员编号）
• 外键就是球员表中的球队编号。
• 主属性是（球员编号）（身份证号），其他的属性（姓名）（年龄）（球队编号）都是非主属性。

1NF、2NF 和 3NF 指的是什么？

参考博客！！！！！！！！！！！！

1NF

• 第一范式：符合1NF的关系中的每个属性都不可再分，如下的就不符合

• 第一范式存在的几个问题，如下图，具体描述参考链接
  • 数据冗余过大
  • 插入异常
  • 删除异常
  • 修改异常

2NF

• 第二范式：2NF 指的数据表里的非主属性都要和这个数据表的候选键（也叫做码）有完全依赖关系
• 其实需要理解这个概念只需要知道2个概念 完全函数依赖和部分函数依赖，举个例子如下
  • 完全函数依赖： 学号 F→ 姓名 or （学号，课名） F→ 分数
  • 部分函数依赖：（学号，课名） P→ 姓名
  • 还有一个传递函数依赖也需要知道
• 还有一个需要掌握的就是如何判断是否符合2NF
  • 第一步：找出数据表中所有的码。
  • 第二步：根据第一步所得到的码，找出所有的主属性。
  • 第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。
  • 第四步：查看是否存在非主属性对码的部分函数依赖
• 为了消除这些部分函数依赖，只有一个办法，就是将大数据表拆分成两个或者更多个更小的数据表，但是方法并不唯一
• 第二范式也是存在问题的，具体参考链接

3NF

• 第三范式：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。也就是说， 如果存在非主属性对于码的传递函数依赖，则不符合3NF的要求。
• 举个例子：，主码为学号，主属性为学号，非主属性为姓名、系名和系主任。因为 学号 → 系名，同时 系名 → 系主任，所以存在非主属性系主任对于码学号的传递函数依赖，所以学生表的设计，不符合3NF的要求。

需要注意的事项

范式只是提出了设计的标准，实际上设计数据表时，未必要符合这些原则。一方面是因为这些范式本身存在一些问题，可能会带来插入，更新，删除等的异常情况（这些会在下一讲举例说明），另一方面，它们也可能降低会查询的效率。这是为什么呢？因为范式等级越高，设计出来的数据表就越多，进行数据查询的时候就可能需要关联多张表，从而影响查询效率。

第22课

3NF 有什么不足？除了 3NF，我们为什么还需要 BCNF？

从上述表中我们可以得到以下结论

• 仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个属性。

• 找到数据表的候选键是（管理员，物品名）和（仓库名，物品名）

• 候选键中选择一个作为主键，比如（仓库名，物品名）。

• 主属性是包含在任一候选键中的属性，也就是仓库名，管理员和物品名。非主属性是数量这个属性。

• 符合 1NF 的要求；其次，数据表中非主属性”数量“都与候选键全部依赖，（仓库名，物品名）决定数量，（管理员，物品名）决定数量，因此，数据表符合 2NF 的要求；最后，数据表中的非主属性，不传递依赖于候选键。因此符合 3NF 的要求。

• 但是依然存在问题如下

  • 增加一个仓库，但是还没有存放任何物品。根据数据表实体完 整性的要求，主键不能有空值，因此会出现插入异常；
  • 如果仓库更换了管理员，我们就可能会修改数据表中的多条记录；
  • 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

• 人们在 3NF 的基础上进行了改进，提出了BCNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。通过拆分表能达到要求

有了范式设计，为什么有时候需要进行反范式设计？

• 尽管围绕着数据表的设计有很多范式，但事实上，我们在设计数据表的时候却不一定要参照这些标准。

• 我们在之前已经了解了越高阶的范式得到的数据表越多，数据冗余度越低。但有时候，我们在设计数据表的时候，还需要为了性能和读取效率违反范式化的原则。反范式就是相对范式化而言的，换句话说，就是允许少量的冗余，通过空间来换时间。

• 如果我们想对查询效率进行优化，有时候反范式优化也是一种优化思路。

反范式设计适用的场景是什么？又可能存在哪些问题？

• 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂。比如采用存储过程来支持数据的更新、删除等额外操作，很容易增加系统的维护成本。

• 比如用户每次更改昵称的时候，都需要执行存储过程来更新，如果昵称更改频繁，会非常消耗系统资源。

• 在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。

• 当冗余信息有价值或者能大幅度提高查询效率的时候，我们就可以采取反范式的优化。

数据库和数据仓库的区别

1. 数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据；
2. 数据库对数据的增删改实时性要求强，需要存储在线的用户数据，而数据仓库存储的一般是历史数据；
3. 数据库设计需要尽量避免冗余，但为了提高查询效率也允许一定的冗余度，而数据仓库在设计上更偏向采用反范式设计。

第23课

什么情况下创建索引，什么时候不需要索引？

什么是索引
数据库中的索引，就好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率。
索引就是帮助数据库管理系统高效获取数据的数据结构。

• 必须知道索引不是万能的，在有些情况下使用索引反而会让效率变低。
• 举个例子
  • 在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。
  • 另外，当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引

索引的种类有哪些？

索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。