疯狂期末周也要发布的MySQL高阶部分！

MySQL

高阶部分

数据建模(Data Modelling)

数据建模是为存储在数据库中的数据创建模型的过程，分为四个过程。理解和分析业务需求（重要！多与业务参与方、领域专家甚至终端用户交谈）——构建业务的概念模型，识别业务中的实体、事物或概念以及它们之间的联系。概念模型是我们所用概念的一种视觉表示，用于涉众交流，确保进度的一致性——生成数据模型或者数据结构以存储数据，即逻辑模型，逻辑模型是独立于数据技术的抽象数据类型，只显示你需要的表和列——建立实体模型，实体模型是围绕特定数据库技术的逻辑模型的实现。

%% 进而产生了三种数据模型：概念、逻辑、实体，接下来将依次介绍

概念模型(Conceptual Models)

• 本质：识别业务中的实体、事物或概念以及它们之间的联系，可以让我们对业务领域和领域涉及的内容有一个高度的概览，也可以用它和商业参与方交接，便于双方更好地理解。
• 实体(Entity)通常包括人、事件、位置等等。++比如学生注册登录看网课，我们的实体就有学生、课程、注册等。++
• 可视化方式观察实体以及实体之间的关系：
  • 实体关系图(Entity Relationship,ER)：常用于数据建模。
    • 有各种工具创建实体关系图，如Microsoft Visio/在线draw.io/LucidCharts
    • 打开draw.io，不用怎么学，就是个画概念模型的图。
  • 标准建模语言图(Uified Modelling Languages,UML图)：适用范围很广

逻辑模型(Logical Models)

在概念模型地基础上增加更多细节，甚至增添了新的实体以完善结构，通过逻辑模型我们几乎可以了解存储数据的结构或者表，这些实体最终会以表格的形式出现在我们设计的数据库中。

• 指定每个属性的类型
  • 如*字符串(string)、整数、浮点数(float)、日期(date Time)*等等。注意，不能使用varchar，因为varchar是一种实现细节，它是MySQL的一种数据类型，逻辑模型独立于数据技术。如name(string)
  • 尽量细分实体的信息。如名和姓，后续可以根据名字或姓氏进行查询或排序。如地址，分为国家、邮政编码、城市、街道等。
• 指定实体之间的关系类型
  • 三种主要的关系：一对一，一对多、多对多
• 建模是一种递归事件，我们需要不断更新不断尝试，插入新实体很有可能会改变之前的实体关系。一定要多想几对几关系，还挺复杂的。

%% 实体模型是逻辑模型通过特定数据库技术的实现

实体模型(Physical Models)

• MySQL新建实体模型：
  • 打开workbench，左上角File，New Model（新建模型），右键可以编辑重命名等，这里还只是一个模型，并非数据库。
  • 在模型上添加图Add Diagram，在这个表中我们可以添加表和视图。双击修改表名，这里我们遵行老师的习惯，概念、逻辑模型中的实体名为单数，实体模型表名为复数
  • 之后添加列，填入具体的列名、确切的数据类型(如VARCHAR(50)/DATETIME日期和时间都能存储)、以及列的属性（PK/NN等）
• 添加主键(Primary Keys)、定义关系：
  • 主键是唯一标识给定表里每条记录的列，复合主键包含多个列的组合。::注意，我们添加主键，在后续建立实体关系中，表1主键会在表2中重复出现，所以主键要保持简洁，建议新增id列作为主键。::
  • 理想情况下主键不能更改，值应永远不变。主键最好放在表的最上面，windows拖动就行。每一个表都要有一个主键，大多数情况下我们是考虑勾选AI自动生成id值。
• 外键(Foreign Keys):
  • 如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。
  • 在两个表添加关系时，关系的一端称为父表或主键表，另一端称为子表或外键表。::其中的关系是，没有父表就没有子表，没有子表父表依然有意义::
  • 添加关系时，我们先选择关系，然后先选择外键表，再点击选择主键表。添加之后，MySQL会为外键表添加一列，主键表名_主键表主键名，如students_student_id这个id就是外键表的外键，可以重命名变成student_id更简洁。
• 关于复合主键：
  • 优点：复合主键是根据两个字段组成的，主键值不会变化也不能重复，所以复合主键可以防止意外数据变化。
  • 是否选择复合主键没有什么硬性规定，它取决于该表是否还有另一张子表，如果还有子表的话，那么该表的复合主键会在子表中作为子表的外键反复出现，不简洁。如果没有子表并且数据量都比较小的时候是可以用复合主键的。
  • 创建：就是在表中给多个列的PK打勾
• 关于联合主键：
  • 联合主键是多个字段结合生成的主键

其实可以这么理解：候选键往往可以分割成多个主键，往往是主键的集合，往往是任选一个作为主键。但复合主键、联合主键本质上就是一个主键，一个由多字段组成的主键往往用于好几个属性都不能单独作为主键但是联合起来可以作为主键的情况

• 设置外键约束：
  • 本质上可以保护数据不受损坏
  • 外键约束就是告诉MySQL当主键被更新或删除时应该进行什么操作，一个是更新后，一个是删除后。
  • 操作：打开表，下方第三栏Foreign Keys
    • 左侧为该表的外键。::有时候MySQL会自动在外键名字结尾加1，2防止名字冲突，在已知不会产生冲突的情况下我们可以把这些数字删除::
    • 中间表示所选外键在表中的列
    • 设置外键约束纯看右侧上面两行。第一行On Update是父表主键更新后操作，第二行On Delete是父表主键删除后操作。就是要在主键表主键更新或删除（不常见）之后，确保外键表也随之更新或删除。
      • CASCADE(级联)MySQL自动更新子表中的记录
      • NO ACTION或RESTRICT都是拒绝更新或删除，这里常常用于删除后操作On Delete::因为有时候我们需要外键表存储的某个信息，比如财务信息，后续如果要根据历史财务信息制定方案，那么财务信息就不应该随着主键的删除而删除::
      • SET NULL如果更改、删除，外键值就会被设为NULL，这样该子记录就与父表失去了联系，该记录就被称为孤儿记录。它属于不良数据，我们应尽量避免。
  • 总结：所有的外键都要设置外键约束，一般来说，更新设置为CASCADE,删除还要看情况。一切的根源来自于业务需求。

---

标准化(Normalization)

在继续生成数据库表格之前，我们需要确保设计最优，不允许存在冗余或重复的数据。标准化是审查设计，确保设计遵循一些防止数据重复的预定义规则的这一过程。基本上有7条原则，也被称为七范式，99％只需要应用前三条。

第一范式(First Normal Form,1NF)

• 第一范式要求一行中每个单元格/属性都应该有单一值，且不能出现重复列。
  ::例子：课程的属性tags标签存在多个值。根据1NF，应满足单一值，但我们不确定课程的标签数量，所以不能将tags展开为tag1,tag2,tag3等，并且假使我们知道数量也不能分开列，因为tag1,tag2,tag3是属于重复列的，不符合1NF综上，只能新增标签表，并将其与课程表建立多对多的实体关系::
• RDBMS中没有多对多的关系，只能通过多个“一对多”关系来实现“多对多”关系，此时中间联系的表称为链接表。杨老师上课说的其实就是给我们表示一定的概念，但实际的DBA设计当中我们需要使用链接表，用两种一对多的关系实现多对多
  ++比如我们要实现表1、表3的多对多，则用表2为链接表，表1、表3分别与表2形成“一对多”的关系，链接的时候都是先点击表2作为外键表++
  %% 像这样新增标签表的操作，当我们进行更新或删除标签时只需要更改标签表里的内容，并且标签行是唯一被锁定的行。如果标签还是在课程表里面的话，我们对于标签的更改要伴随着课程表的出现而多次执行，浪费资源，并且还会给课程表这个大表上锁，造成麻烦。::小的东西要独立出来::

第二范式(Second Normal Form,2NF)

• 前提：一组关系必须符合第一范式
• 第二范式要求每张表都要有单一目的，只能代表一种且仅有一种实体类型，表的每一列都应该用来描述该实体，每一列都是该实体的属性。
• 所以，描述其他实体的列我们应该将其提出并单独生成表。如order表中我们的customer_name不是order的性质，我们要在外单独设置customer_name的表，并设置customer_id与之连接。
  ++基本上啊，物体表出现人名都是要把人名提取的，因为人名太长，不仅浪费空间、更新缓慢，而且还不算是物体的性质，即不符合2NF。提取人名基本上都是把人名和id相对应，这样更新方便且大大减小了数据的冗余。++
  %% 数据重复是没有标准化的一大现象，大多数是人名等字符串的重复！

第三范式(Third Normal Form,3NF)

• 前提：符合第二范式
• 内容：表中的所有属性只能由那组关系的候选键决定，不能由非主属性决定，表中的非主属性列不应该派生列。
  %% 大多数简单的表其中的候选键都是id，剩下的列与id没多大关系。基本上都是非主属性，所以，在非主属性列中不能出现某个列是由其余列推导的情况。比如利润=销售额-成本，在有销售额、成本列的前提下，我们就不能设置利润列了，因为利润可以由二者计算推导出来，也就是取决于这两列，还有姓+名=姓名等情况。
• 第三范式和其余范式一样，都能帮助我们减小重复，并提高数据的完整性。

Mosh的实用建议

• 更加注重消除冗余而不是第几第几范式，注重实际，不需要对每张表每列逐个应用标准化规则。
• 不要直接开始建立表，要进行数据建模，否则会很混乱且业余。
• 不要刻意完全应用范式，永远把需求放在第一位，注意，未来的需求经常变化。为我们的问题域建立模型即可，我们的目标是复杂问题简单化，而不是对现实世界的一切事物都要考虑都要建模。

一个模型的正向工程(Forward Engineering a Model)

%% 看了一看，感觉就是在有实体模型的基础之上直接建立数据库的过程，代码什么的由workbench来写。

• 打开实体模型——在顶端找到Database菜单——选择Forward Engineering... 正向工程——其余的基本就保持默认。

数据库同步模型(Synchronizing a Model)

• 在没有数据库的时候使用模型的正向工程建立数据库
• 更改数据库时，在个人电脑登录时，我们可以直接打开表的设计模式直接添加即可。但在大中型组织架构中，通常有好几个服务器，模拟了实际生产环境::生产环境是我们的用户访问我们的应用程序或数据库的地方。除了生产环境，我们还有阶段环境、测试环境、开发环境，每一个环境都有一个或多个服务器。::
• 所以如果要修改数据库的话，在实体模型中更改，顶端Database——Synchronize Model选择连接环境时如果我们在组织中，就可以选择连接到一个测试环境而不是本地连接
• 在获得更改脚本之后我们可以立即执行，但是更好的方法是将脚本保存在我们的文件系统之中，然后录入Git(小组开发工具)，使源代码得以保存可以后续直接复制粘贴。

一个模型的反向工程(Reverse Engineering a Database)

• 本质：某数据库没有模型，如果我们想更改该数据库，就要为数据库进行反向工程处理来创建一个模型。
• 注意：如果我们在建立反向工程的过程中已经打开了某个模型，那么我们建立的模型会被添加到打开的那个模型里这样会出现一个模型对应多个数据库的情况，虽然没什么大碍，但是最好还是为每个数据库配备单独的模型，建议关掉所有打开的模型。步骤和上面差不多。

创建数据库和表(Creating Databases and Tables)

创建和删除数据库

• 创建新数据库：CREATE DATABASE IF NOT EXISTS 库名;
• 删除数据库：DROP DATABASE IF EXISTS 名

深入理解mysql索引及其物理存储

数据库结构

• 最上层用于连接、线程处理；第二层中包含了大多数的核心服务，包括了对 SQL 的解析、分析、优化和缓存等功能，存储过程、触发器和视图都是在这里实现的；而第三层就是真正负责数据的存储和提取的存储引擎，例如：InnoDB、MyISAM等。
  • ++其实也就是外模式、模式、内模式，内模式和存储引擎密切相关++

数据存储结构

%% 之前我以为数据是以什么B+/B存储的，现在才发现其本质的存储

• 在整个数据库体系结构中，我们可以使用不同的存储引擎来存储数据，而绝大多数存储引擎都以二进制的形式存储数据
• 在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间tablespace是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段segment、区extent、页page
• 同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变innodb_page_size选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同：
  选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同：
  ++一页包含很多行，一个区包含很多页，一个段包含很多区，一个表空间包含很多段++
  ++在 InnoDB 存储引擎中，一个区的大小最小为 1MB，页的数量最少为 64 个++
• 在进行数据插入的时候，数据库会分配新的数据页用于存放数据。MySQL一般都是一个extent为单位进行申请。我们在本地建个MySQL，然后慢慢往里面写数据，你会发现这个表的数据文件大小，是一段段增加的

具体mysql引擎内容请见下篇文章！