数据库的范式

第一范式

主要是为了确保原子性的，也就是存储的数据具备不可再分性。

1
2
3
4
5
6
SELECT * FROM `zz_student`;
+----------------------+--------+-------+
| student              | course | score |
+----------------------+--------+-------+
| 小怪兽，男，185cm      | 语文   |    95 |
| 怪兽，男，185cm      | 数学   |   100 |

在上述的学生表中，其中有一个student学生列，这一列存储的数据则明显不符合第一范式：原子性的规定，因为这一列的数据还可以再拆分为姓名、性别、身高三项数据
1
2
3
4
5
6
7
SELECT * FROM `zz_student`;
+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
|小怪兽         | 男          | 185cm          | 语文   |    95 |
| 怪兽         | 男          | 185cm          | 数学   |   100 |

第二范式

第二范式的要求表中的所有列，其数据都必须依赖于主键，也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系，还是上面的那张表数据为例：

1
2
3
4
5
6
7
SELECT * FROM `zz_student`;
+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
|小怪兽         | 男          | 185cm          | 语文   |    95 |
| 怪兽         | 男          | 185cm          | 数学   |   100 |


虽然此时已经满足了数据库的第一范式，但此刻观察course课程、score分数这两列数据，跟前面的几列数据实际上依赖关系并不大，同时也由于这样的结构，导致前面几列的数据出现了大量冗余，所以此时可以再次拆分一下表结构：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT * FROM `zz_student`;
+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 竹子   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 熊猫   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

SELECT * FROM `zz_course`;
+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
|         1 | 语文        |
|         2 | 数学        |
|         3 | 英语        |
+-----------+-------------+

SELECT * FROM `zz_score`;
+----------+------------+-----------+-------+
| score_id | student_id | course_id | score |
+----------+------------+-----------+-------+
|        1 |          1 |         1 |    95 |
|        2 |          1 |         2 |   100 |
|        3 |          1 |         3 |    88 |
|        4 |          2 |         1 |    99 |
|        5 |          2 |         2 |    90 |
|        6 |          2 |         3 |    95 |

经过上述结构优化后，之前的一张表此时被我们拆分成学生表、课程表、成绩表三张，每张表中的id字段作为主键，其他字段都依赖这个主键。无论在那张表中，都可以通过id主键确定其他字段的信息。

第三范式

第三范式要求表中每一列数据不能与主键之外的字段有直接关系。

三范式小结

  • 第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。
  • 第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
  • 第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

巴斯-科德范式

第三范式的要求是：任何非主键字段不能与其他非主键字段间存在依赖关系，也就是要求每个非主键字段之间要具备独立性。而巴斯-科德范式在第三范式的基础上，进一步要求：任何主属性不能对其他主键子集存在依赖。

第三范式只要求非主键字段之间，不能存在依赖关系，但没要求联合主键中的字段不能存在依赖，因此第三范式并未考虑完善，巴斯-科德范式修正的就是这点
例如：一张表中 并没有uuid,和id,而是用联合主键来确保唯一，联合主键是有多列组成。需要确保这多列之间没有以来关系

第四范式

第四范式（4NF） 是在第三范式和 巴斯-科德范式（BCNF） 基础上的进一步规范化，它主要解决 多值依赖（Multivalued Dependency） 带来的数据冗余问题

假设以「用户名、角色、权限」三个字段作为联合主键，先来分析一下这张表是否满足之前的范式：

  • 表中每列数据都不可再分，具备原子性，满足第一范式。
  • 表中数据都仅描述了用户权限这一种业务属性，具备唯一性，满足第二范式。
  • 除主键外，表中其他字段不存在依赖关系，具备独立性，满足第三范式。
  • 联合主键中的用户、角色、权限都为独立字段，不存在依赖性，满足BC范式。

但是这个时候 如果一个用户 可有多个角色，一个角色又有多个权限。这就是多值依赖。也就是一张表中存在一对多的关系 。这时候就需要拆分成三张表，把角色表和权限单独做成一个表

第五范式

第五范式的定义：建立在4NF的基础上，进一步消除表中的连接依赖，直到表中的连接依赖都是主键所蕴含的。 看不懂就先跳过了

总结

第一范式：原子性，每个字段的值不能再分。
第二范式：唯一性，表内每行数据必须描述同一业务属性的数据。
第三范式：独立性，表中每个非主键字段之间不能存在依赖性。
巴斯范式：主键字段独立性，联合主键字段之间不能存在依赖性。
第四范式：表中字段不能存在多值依赖关系。
第五范式：表中字段的数据之间不能存在连接依赖关系。

SQL语句的执行过程

先上图:

  1. SQL接口会接收到 客户端发来的SQL语句
  2. mysql8.0中已经移除了缓存
  3. 解析器 会判断这个SQL语法对不对。不对会抛异常
  4. 优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
  5. 工作线程根据执行计划，调用存储引擎所提供的API获取数据。
  6. 存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据….）。
  7. 发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。
  8. SQL接口会对所有的结果集进行处理（剔除列、合并数据….）并返回。

索引篇

聚簇索引和非聚簇索引

聚簇索引的特点是：索引结构和数据存储在一起，数据按照索引顺序进行存储。

1
2
3
4
5
普通索引
叶子节点 → 存储主键

聚簇索引
叶子节点 → 存储整行数据

聚簇索引是指索引结构和数据存储在一起的一种索引方式，在InnoDB存储引擎中主键索引就是聚簇索引。它的叶子节点存储的是整行数据，因此通过主键查询可以直接获取数据，不需要回表。由于数据按照索引顺序存储，所以一个表只能有一个聚簇索引。

索引下推

把原本在 Server 层判断的条件，下推到存储引擎层，在扫描索引时就提前过滤数据。

  • 假设有表
    1
    user(id, name, age)
  • 联合索引
    1
    index(name, age)
  • 查询sql
    因为是> 会导致索引失效
    1
    2
    SELECT * FROM user
    WHERE name = 'Tom' AND age > 20;
  • 没有索引下推的流程
    1
    2
    3
    4
    1 通过 name = 'Tom' 查索引
    2 得到很多主键 id
    3 回表查完整数据
    4 再判断 age > 20
  • 有索引下推的流程
    1
    2
    3
    1 通过 name = 'Tom' 查索引
    2 在索引里直接判断 age > 20
    3 只把符合条件的记录回表

MYSQL日志篇

Undo-log撤销日志

Undo即撤销的意思，但咱们通常也习惯称它为回滚日志，在日常开发过程中，如果代码敲错了，一般会习惯性的按下Ctrl+Z撤销，而Undo-log的作用也是如此，但它是用来给MySQL撤销SQL操作的。

undo-log 并不会记录相反的sql。undo-log会生成多版本控制链。当事务回滚的时候，多版本控制链中的指针会移动。

Redo-log重做日志

Undo-log主要用于实现事务回滚和MVCC机制，而Redo-log则用来实现数据的恢复。
Redo-log是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化。

  1. 先刷写一次Redo-log日志到磁盘，后台线程再根据Redo-log日志把数据落盘的原因:

    • 日志比数据先落入磁盘，因此就算MySQL崩溃也可以通过日志恢复数据。
    • 写日志时是以追加形式写到末尾，而写数据时则是计算数据位置，随机插入

      因为写日志会比写数据落盘快，因此日志落盘后返回，比数据落盘后返回要快，对于客户端而言，响应时间会更短

Bin-log变更日志

Bin-log变更日志 和 redo-log日志功能大致相同 。主要是记录所有对数据库表结构变更和表数据修改的操作，对于select、show这类读操作并不会记录。bin-log是MySQL-Server级别的日志，也就是所有引擎都能用的日志，而redo-log、undo-log都是InnoDB引擎专享的，无法跨引擎生效。

  • bin-log 会记录所有表更新的sql(update ,crate inster,drop)。
  • bin-log 是通过二进制文件存储的，先将sql存到内存中，最后刷到磁盘上
  • bin-log 常用于主从同步使用

bin-log ,redo-log的区别

对于Redo-log、Bin-log两者的区别，主要可以从四个维度上来说：

  1. 生效范围不同，Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。
  2. 写入方式不同，Redo-log是用两个文件循环写，而Bin-log是不断创建新文件追加写。

    当redo-log上的数据被刷到磁盘上，则redo-log就不在需要这些数据了。可以被覆盖
    bin-log 的数据满了，会在新创建一个文件 继续追加数据，需要手动清除

  3. 文件格式不同，Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。
  4. 使用场景不同，Redo-log主要实现故障情况下的数据恢复，Bin-log则用于数据灾备、同步。

redo-log 的两阶段提交

  1. 如果只写一次的话，那到底先写bin-log还是redo-log呢？
    • 先写bin-log，再写redo-log：当事务提交后，先写bin-log成功，结果在写redo-log时断电宕机了，再重启后由于redo-log中没有该事务的日志记录，因此不会恢复该事务提交的数据。但要注意，主从架构中同步数据是使用bin-log来实现的，而宕机前bin-log写入成功了，就代表这个事务提交的数据会被同步到从机，也就意味着从机会比主机多出一条数据。
    • 先写redo-log，再写bin-log：当事务提交后，先写redo-log成功，但在写bin-log时宕机了，主节点重启后，会根据redo-log恢复数据，但从机依旧是依赖bin-log来同步数据的，因此从机无法将这个事务提交的数据同步过去，毕竟bin-log中没有撒，最终从机会比主机少一条数据。

  2. redo-log就被设计成了两阶段提交模式，设置成两阶段提交后，整个执行过程有三处崩溃点：

    • redo-log(prepare)：在写入准备状态的redo记录时宕机，事务还未提交，不会影响一致性。
    • bin-log：在写bin记录时崩溃，重启后会根据redo记录中的事务ID，回滚前面已写入的数据。
    • redo-log(commit)：在bin-log写入成功后，写redo(commit)记录时崩溃，因为bin-log中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条redo(commit)记录即可。

通过这种两阶段提交的方案，就能够确保redo-log、bin-log两者的日志数据是相同的，bin-log中有的主机再恢复，如果bin-log没有则直接回滚主机上写入的数据，确保整个数据库系统的数据一致性。

辅助性的日志

  1. error-log：MySQL线上MySQL由于非外在因素（断电、硬件损坏…）导致崩溃时，辅助线上排错的日志。
  2. slow-log：系统响应缓慢时，用于定位问题SQL的日志，其中记录了查询时间较长的SQL。
  3. relay-log：搭建MySQL高可用热备架构时，用于同步数据的辅助日志。

SQL优化篇

  • 尽量不要用select *
    1. 会增加 网络传输数据量
    2. select * 如果不是条件不是主键，那么必然会回表查询。如果查询字段 全部在索引中，就可以走 覆盖索引。
  • 连表查询时尽量不要关联太多表
    1. 数据量会随表数量呈直线性增长，数据量越大检索效率越低
    2. 当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大

  • 小表驱动大表

    1. 可以减少循环查询的次数

  • 不要使用like左模糊和全模糊查询

    1. 会导致索引失效