1.mysql基础概念

一、三大范式

  1. 第一范式：列都是不可再分的
    第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子单元）。

  2. 第二范式：每个表只描述一件事情
    首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。

  3. 第三范式：不存在对非主键列的传递依赖
    满足第二范式，并且表中的列不存在对非主键列的传递依赖。

第三范式的理解需要结合场景才能理解，传递依赖换句话说就是数据表有字段的值和另一个字段有关联，也就是依赖，当然得是非主键字段

id name sex grade grade_score
1 小明 男 优秀 95
2 小红 女 良好 80

这里满足第一和第二范式，但可以发现grade是对grade_score的映射，可以说grade对grade_score存在传递依赖，这个时候应该分成两张表
表一：student_info

id name sex grade_score
11 小明 男 95
22 小红 女 80

表二：student_grade_score

id student_id grade_score grade
1 11 95 优秀
2 22 80 良好

当然分数和等级可以只是一张映射表
表二：grade_score

id student_id_min grade_score_max grade
1 90 100 优秀
2 80 90 良好

需要通过分数获取其所在范围的等级时只需关联映射表查询，相比于大量的学生信息记录，将等级与分数变为映射表可以节省数据存储空间

二、事务

2.1 事务特性

什么是事务：MySQL的事务是指一个或多个SQL语句组成的逻辑单元，它们被当作一个整体来执行。事务将一系列操作封装在一个完整的工作单元中，要么全部执行成功，要么全部回滚（撤销）。

事务必须具备四个特性，通常被称为ACID特性：

  1. 原子性（Atomicity）：事务被视为一个不可分割的最小工作单元，它要么全部执行成功，要么全部失败回滚，不会出现中间状态。

  2. 一致性（Consistency）：事务的执行必须使数据库保持一致状态。事务开始之前和结束之后，数据库的完整性约束不被破坏。

  3. 隔离性（Isolation）：多个事务并发执行时，每个事务的执行都应该相互隔离，互不干扰。每个事务在执行过程中所做的修改对其他事务都是不可见的。

  4. 持久性（Durability）：事务一旦提交成功，其所做的修改将永久地保存在数据库中，即使宕机或系统故障也不会丢失。

上面是关于这些的定义与说明，mysql通过一系列设计来确保能实现这个四个特性：

  • undo log日志记录了需要回滚的日志信息，事务回滚是撤销已经执行的sql，确保原子性
  • 两种隔离级别：可重复读（REPEATABLE READ）和读提交（READ COMMITTED），通过锁机制确保隔离性
  • mysql修改数据同时会在内存和redo log中记录这次操作，宕机时可以从redo log恢复，确保持久性
  • 程序代码要保证业务上的一致性，也通过其他三个特性确保一致性

2.2 事务状态

image

  • 活动的（active）
    事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

  • 部分提交的（partially committed）
    当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。

  • 失败的（failed）
    当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

  • 中止的（aborted）
    如果事务执行了半截而变为失败的状态，撤销失败事务对当前数据库造成的影响，我们把这个撤销的过程称之为回滚。
    当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

  • 提交的（committed）
    当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

对于失败和中止的状态有些说法会将其归类到回滚状态（Rollback）：当事务执行过程中发生错误，或者请求回滚时，事务会进入回滚状态。
在这个状态下，事务所做的操作将被全部回滚，数据恢复到事务开始时的状态。注意，回滚操作并不是将修改操作撤销掉，而是将事务对数据库做的修改全部撤销，并且撤销的过程也需要进行锁定，以保证数据的一致性。

2.3 隔离级别

MySQL数据库支持以下四种隔离级别：

  1. 可串行化（Serializable）：最高隔离级别，保证了事务的完全隔离性，每个事务在提交之前都会获取并持有所有的共享锁，避免了脏读、不可重复读和幻读问题。

  2. 可重复读（Repeatable Read）：事务可以多次读取相同的数据，保证了相同的查询结果，解决了不可重复读问题，但可能会导致幻读问题。

  3. 读已提交（Read Committed）：事务只能读取到其他事务已提交的数据，解决了脏读问题，但可能会导致不可重复读和幻读问题。

  4. 读未提交（Read Uncommitted）：最低隔离级别，一个事务可以读取到其他事务未提交的数据，可能会导致脏读、不可重复读和幻读等问题。

一般来说，可重复读是一个较为常用的隔离级别，可以提供较好的数据一致性，但需要注意处理并发情况下的幻读问题。
如果对数据一致性要求非常高，可以选择可串行化隔离级别，但会对并发性能有一定影响。
注意数据库引擎的支持情况，不同的数据库引擎对隔离级别的实现方式可能有所不同。

以下表student_info说明事务隔离级别中会出现的问题

id name sex grade_score
1 小明 男 95
2 小红 女 85
3 张三 男 50

  • 幻读：
    同一事务内，不同的查询语句返回了不同数量的行。幻读主要由于其他事务插入或更新了数据导致。
    例如一个事务需要读取所有性别为男的学生记录，但是在这个事务的查询操作执行完之前，另外一个事务插入了一行性别为男的数据，然后第一个事务再次查询时，就会发现有一行多了出来。
    幻读问题关注重点在于insert，需要锁住表，某些数据库引擎也提供了非阻塞读取、MVCC 隔离级别等策略来缓解幻读的问题。

  • 不可重复读:
    不可重复读通常发生在并发环境下，由于其他事务对数据进行了修改或删除，导致在同一个事务内多次读取同一数据时，读取结果不一致的情况。
    当一个事务A读取id=1的记录，这时另一个事务B对id=1的记录进行了修改或删除的操作，那么当事务A再次读取id=1的记录时，发现数据的值已经发生了变化，从而产生了不可重复读的问题。
    重点在于update和delete，需要锁住行

  • 脏读：
    指在多个事务并发执行的情况下，一个事务A读取到了另一个事务B尚未提交的数据，当事务B回滚时，事务A读取的数据将变成无效数据，类似于读取了脏数据。
    假设有两个并发的事务：事务A和事务B，它们现在对对id=2的记录的grade_score进行读取和修改操作

  1. 事务A读取数据：事务A开始，读取到grade_score为85
  2. 事务B修改数据：在事务A读取数据的过程中，事务B修改了数据的字段值，将其改为150，但还未提交
  3. 事务A读取脏数据：事务A继续读取数据，但此时读取到的字段值变成了150，即读取到了事务B尚未提交的数据。
    如果此时事务B回滚了针对数据的修改，那么事务A读取到的数据就是无效的。
  • 不可重复读与幻读的区别
    这俩看着很像，但其实情况是不同的
    不可重复读与幻读不同之处在于，不可重复读是由于其他事务对数据的修改或删除导致的，而幻读是由于其他事务插入（新增）了满足查询条件的数据导致的。
    不可重复读是由于其他线程修改了已经存在的数据导致的，而幻读是由于事务内的查询操作无法锁定所有满足查询条件的行，导致其他事务的更新操作在当前事务中造成了影响。
    不可重复读重点在于update和delete，需要锁住行，幻读重点在于insert，需要锁住表

三、存储引擎

3.1 存储引擎类型

常用的关系型数据库数据存储引擎有：

  • InnoDB：是MySQL的默认存储引擎，支持ACID事务，提供行级锁和外键约束，适合于高并发读写、强一致性的应用场景。

  • MyISAM：是MySQL的一种非事务性的存储引擎，适合于以读操作为主的应用场景，性能较好，但不支持事务、外键约束和行级锁。

  • Memory（或Heap）：是MySQL的一种基于内存的存储引擎，数据存储在内存中，不支持持久化存储，适合于缓存和临时数据存储。

  • SQLite：是一种轻量级的关系型数据库，使用简单且跨平台，适合于移动设备和嵌入式应用，但不适合高并发和大数据量的应用。

它们适用于不同的场景，最主流的当然是InnoDB，其他存储引擎使用场景特殊，多数时候我们接触的就是InnoDB

3.2 InnoDB

当前主流同时也是绝大多数MySQL中都是默认的存储引擎

  1. ACID事务支持：InnoDB支持事务的四个关键特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），保证数据的可靠性和完整性。

  2. 行级锁定：InnoDB提供了行级别的锁定机制，可以避免并发读写操作之间的冲突，提高并发性能。

  3. 外键约束：InnoDB支持外键约束，可以在数据库层面实现数据的参照完整性，保证相关数据的一致性。

  4. MVCC（多版本并发控制）：InnoDB使用MVCC来处理并发访问，允许读取事务的一致性快照而不会受到写入操作的影响，提高了并发性和并发控制的能力。

  5. 自动崩溃恢复：InnoDB具备崩溃恢复能力，可以在数据库异常崩溃后自动进行恢复操作，保证数据的一致性和完整性。

  6. 热备份：InnoDB支持在线热备份，并提供了各种备份和恢复工具，可以保证数据库的高可用性和容灾能力。

  7. 支持大容量数据表：InnoDB支持处理大容量数据表，可以存储和处理数百万到数十亿行的数据。
    与MyISAM的不同在于InnoDB不支持全文索引，当然这也是MyISAM为数不多的优势，适合高并发读，特别适合于以读操作为主的应用场景，如数据仓库、报表查询、日志分析等。
    当然MyISAM仅仅在读上的些许优势相比于InnoDB的事务、锁等机制自然差了不止一筹

posted @ 2023-07-06 23:15  staticFinal  阅读(22)  评论(0)    收藏  举报