MySQL知识点总结（上）一、基本概念 1、范式第一范式：(确保每列保持原子性) 最基本的范式，数据库中的字段具有原子

一、基本概念

1、范式

第一范式：(确保每列保持原子性)
- 最基本的范式，数据库中的字段具有原子性，字段都是单一属性的，不可再分，并且是单一职责。
第二范式：(确保表中的每列都和主键相关)
- 建立在第一范式的基础上，第二范式要求数据库表中的每个实例或行必须可以被唯一区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主键。
- 2NF是在1NF的基础上消除了非主属性对键的部分函数依赖。
第三范式：(确保每列都和主键列直接相关,而不是间接相关)
- 建立在第二范式的基础上，确保每列都和主键列直接相关，而不是间接相关，不存在其他表的非主键信息。
- 3NF是在2NF的基础上消除了非主属性对键的传递函数依赖。
BC范式
- BCNF是在3NF的基础上消除了主属性对键的部分函数依赖和传递函数依赖。
第四范式
- 4NF是在3NF的基础上消除了属性间的非平凡且非函数依赖的多值依赖。
在日常开发当中，并不是所有的表一定要满足最高范式要求，有时候冗余几个字段可以少关联几张表，有可能对查询效率带来质变的提升。

2、事务

数据库事务是构成单一逻辑工作单元的操作集合。
事务就是一个对数据库操作的序列，是一个不可分割的工作单位，这个序列里面的操作要不全部执行，要不全部不执行。
事务特性（ACID）：
- 原子性（Atomicity）： 事务中的所有操作要么全部执行成功，要么全部不执行。
- 一致性（Consistency）： 事务前后数据的完整性必须保持一致。
- 隔离性（Isolation）： 并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。
- 持久性（Durability）： : 事务一旦提交，其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。
不同事务级别带来的并发问题：
- ①脏读： 脏读发生在一个事务A读取了被另一个事务B正在进行修改，但是还未提交的数据。假如B回退，则事务A读取的是无效的数据。这跟不可重复读类似，但是第二个事务不需要执行提交。
- ②不可重复读： 事务B提交成功，它所做的修改已经可见。然而事务A已经读取了一个其它的值。在序列化和可重复读的隔离级别中，数据库管理系统会返回旧值，即在被事务B修改之前的值。在提交读和未提交读隔离级别下，可能会返回被更新的值。执行select语句时不添加读锁，就会发生不可重复读问题。
- ③幻读： 幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。执行select语句时不添加范围锁，就会发生幻读问题。
隔离级别：
- ①ISOLATION_READ_UNCOMMITTED： 这是事务最低的隔离级别，它允许令外一个事务可以看到这个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻像读。
- ②ISOLATION_READ_COMMITTED： 大多数数据库系统的默认隔离级别。 保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。
- ③ISOLATION_REPEATABLE_READ： 这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读。它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了避免不可重复读的情况产生。是MySQL的默认事务隔离级别。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题。
- ④ISOLATION_SERIALIZABLE： 这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。每个读的数据行上加上共享锁，除了防止脏读，不可重复读外，还避免了幻像读。在这个级别，可能导致大量的超时现象和锁竞争。

3、分布式事务

分布式事务是指事务的参与者、支持事务的服务器、资源服务器以及事务管理器分别位于不同分布式系统的不同节点之上。
一个大的操作由N多的小的操作共同完成。而这些小的操作又分布在不同的服务上。针对于这些操作，需要全部成功执行，要么全部不执行。
分布式CAP定理：
- (1)一致性（C）： 在分布式系统中的所有数据备份，在同一时刻是否拥有同样的值。（等同于所有节点访问同一份最新的数据副本）
- (2)可用性（A）： 在集群中一部分节点故障后，集群整体是否还能响应客户端的读写请求。（对数据更新具备高可用性）
- (3)分区容错性（P）： 即使出现单个组件无法可用，操作依然可以完成。
- 在一个分布式系统的任何数据库设计中，以上三点特性无法同时满足，一个Web应用至多只能同时支持上面的两个属性。显然，任何横向扩展策略都要依赖于数据分区。因此根据实际优先级必须在一致性与可用性之间做出选择。
分布式BASE理论：
- BASE理论是CAP定理的进一步扩充。
- BASE理论指的是：
  - Basically Available（基本可用）
  - Soft state（软状态）
  - Eventually consistent（最终一致性）
- BASE理论是对CAP中的一致性和可用性进行一个权衡的结果，理论的核心思想就是：我们无法做到强一致，但每个应用都可以根据自身的业务特点，采用适当的方式来使系统达到最终一致性（Eventual consistency）。
分布式事务解决方案
- (1)本地消息表：
  - 利用了各系统本地的事务来实现分布式事务，会有一张存放本地消息的表，一般放在数据库中，在执行业务时将业务的执行和将消息放入消息表中的操作放在同一个事务中，保证消息放入本地表中业务肯定是执行成功的。之后再去调用下一个操作，如果下一个操作调用成功，消息表的消息状态可以直接改成已成功。如果调用失败，会有后台任务定时去读取本地消息表，筛选出还未成功的消息再调用对应的服务，服务更新成功后再变更消息的状态。这时候有可能消息对应的操作不成功，因此也需要重试，重试需要保证对应服务的方法是幂等的，而且一般重试会有最大次数，超过最大次数可以记录报警让人工处理。
  - 本地消息表实现的是最终一致性，容忍了数据暂时不一致的情况。
- (2)消息事务：
  - 消息事务的原理是将两个事务通过消息中间件进行异步解耦，和上述的本地消息表有点类似，但是是通过消息中间件的机制去做的，其本质就是将本地消息表封装到了消息中间件中。
  - 执行流程：
    - 发送prepare消息到消息中间件
    - 发送成功后，执行本地事务
    - 如果事务执行成功，则commit，消息中间件将消息下发至消费端
    - 如果事务执行失败，则回滚，消息中间件将这条prepare消息删除
    - 消费端接收到消息进行消费，如果消费失败，则不断重试
  - 这种方案也是实现了最终一致性，对比本地消息表实现方案，不需要再建消息表，不再依赖本地数据库事务，所以这种方案更适用于高并发的场景。目前市面上实现该方案的有阿里的RocketMQ。
- (3)二阶段提交（2PC）：
  - mysql的事务就是通过日志系统来完成两阶段提交的。
  - 两阶段协议可以用于单机集中式系统，由事务管理器协调多个资源管理器；也可以用于分布式系统，由一个全局的事务管理器协调各个子系统的局部事务管理器完成两阶段提交。
  - 第一阶段投票阶段：
    - 协调者首先将命令写入日志
    - 发一个prepare命令给B和C节点这两个参与者
    - B和C收到消息后，根据自己的实际情况，判断自己的实际情况是否可以提交
    - 将处理结果记录到日志系统
    - 将结果返回给协调者
  - 第二阶段决定阶段：
    - 当A节点收到B和C参与者所有的确认消息后，判断所有协调者是否都可以提交，如果可以则写入日志并且发起commit命令；有一个不可以则写入日志并且发起abort命令
    - 参与者收到协调者发起的命令，执行命令
    - 将执行命令及结果写入日志
    - 返回结果给协调者
  - 可能存在的问题：
    - 单点故障： 一旦事务管理器出现故障，整个系统不可用。
    - 数据不一致： 在阶段二，如果事务管理器只发送了部分 commit 消息，此时网络发生异常，那么只有部分参与者接收到 commit 消息，也就是说只有部分参与者提交了事务，使得系统数据不一致。
    - 响应时间较长： 整个消息链路是串行的，要等待响应结果，不适合高并发的场景。
    - 不确定性： 当事务管理器发送 commit 之后，并且此时只有一个参与者收到了 commit，那么当该参与者与事务管理器同时宕机之后，重新选举的事务管理器无法确定该条消息是否提交成功。
- (4)三阶段提交（3PC）：
  - 3PC相对于2PC来说增加了CanCommit阶段和超时机制。如果段时间内没有收到协调者的commit请求，那么就会自动进行commit，解决了2PC单点故障的问题。
  - 但是性能问题和不一致问题仍然没有根本解决。
  - 第一阶段CanCommit阶段：
    - 协调者询问事务参与者是否有能力完成此次事务
    - 如果都返回yes，则进入第二阶段
    - 如果有一个返回no或等待响应超时，则中断事务，并向所有参与者发送abort请求
  - 第二阶段PreCommit阶段：
    - 此时协调者会向所有的参与者发送PreCommit请求，参与者收到后开始执行事务操作，并将Undo和Redo信息记录到事务日志中
    - 参与者执行完事务操作后（此时属于未提交事务的状态），就会向协调者反馈“ack”表示我已经准备好提交了，并等待协调者的下一步指令
  - 第三阶段DoCommit阶段：
    - 在阶段二中如果所有的参与者节点都可以进行PreCommit提交，那么协调者就会从“预提交状态”转变为“提交状态”
    - 然后向所有的参与者节点发送"doCommit"请求，参与者节点在收到提交请求后就会各自执行事务提交操作，并向协调者节点反馈“ack”消息，协调者收到所有参与者的Ack消息后完成事务
    - 相反，如果有一个参与者节点未完成PreCommit的反馈或者反馈超时，那么协调者都会向所有的参与者节点发送abort请求，从而中断事务。
- (5)补偿事务（TCC）：
  - 核心思想是：针对每个操作，都要注册一个与其对应的确认和补偿（撤销）操作。
  - 分为Try,Confirm,Cancel三个阶段：
    - Try阶段主要是对业务系统做检测及资源预留。
    - Confirm阶段主要是对业务系统做确认提交，Try阶段执行成功并开始执行 Confirm阶段时，默认 Confirm阶段是不会出错的。即：只要Try成功，Confirm一定成功。
    - Cancel阶段主要是在业务执行错误，需要回滚的状态下执行的业务取消，预留资源释放。
  - TCC 事务机制相比于2PC，解决了其几个缺点：
    - 解决了协调者单点，由主业务方发起并完成这个业务活动。业务活动管理器也变成多点，引入集群。
    - 同步阻塞，引入超时，超时后进行补偿，并且不会锁定整个资源，将资源转换为业务逻辑形式，粒度变小。
    - 数据一致性，有了补偿机制之后，由业务活动管理器控制一致性
  - TCC就是通过代码人为实现了两阶段提交，不同的业务场景所写的代码都不一样，并且很大程度的增加了业务代码的复杂度，因此，这种模式并不能很好地被复用。
- (6)最大努力通知：
  - 最大努力通知的方案实现比较简单，适用于一些最终一致性要求较低的业务。
  - 执行流程：
    - 系统 A 本地事务执行完之后，发送个消息到 MQ；
    - 这里会有个专门消费 MQ 的服务，这个服务会消费 MQ 并调用系统 B 的接口；
    - 要是系统 B 执行成功就 ok 了；要是系统 B 执行失败了，那么最大努力通知服务就定时尝试重新调用系统 B, 反复 N 次，最后还是不行就放弃。
- (7)Seata框架：
  - Saga事务模型又叫做长时间运行的事务。
  - 核心思想是将长事务拆分为多个本地短事务，由Saga事务协调器协调，如果正常结束那就正常完成，如果某个步骤失败，则根据相反顺序一次调用补偿操作。
  - Seata框架中一个分布式事务包含3种角色：
    - Transaction Coordinator (TC)：事务协调器，维护全局事务的运行状态，负责协调并驱动全局事务的提交或回滚。
    - Transaction Manager (TM)：控制全局事务的边界，负责开启一个全局事务，并最终发起全局提交或全局回滚的决议。
    - Resource Manager (RM)：控制分支事务，负责分支注册、状态汇报，并接收事务协调器的指令，驱动分支（本地）事务的提交和回滚。
  - seata框架为每一个RM维护了一张UNDO_LOG表，其中保存了每一次本地事务的回滚数据。
  - 具体流程：
    - 首先TM向TC申请开启一个全局事务，全局事务创建成功并生成一个全局唯一的 XID。
    - XID在微服务调用链路的上下文中传播。
    - RM开始执行这个分支事务，RM首先解析这条SQL语句，生成对应的UNDO_LOG记录。下面是一条UNDO_LOG中的记录，UNDO_LOG表中记录了分支ID，全局事务ID，以及事务执行的redo和undo数据以供二阶段恢复。
    - RM在同一个本地事务中执行业务SQL和UNDO_LOG数据的插入。在提交这个本地事务前，RM会向TC申请关于这条记录的全局锁。如果申请不到，则说明有其他事务也在对这条记录进行操作，因此它会在一段时间内重试，重试失败则回滚本地事务，并向TC汇报本地事务执行失败。
    - RM在事务提交前，申请到了相关记录的全局锁，然后直接提交本地事务，并向TC汇报本地事务执行成功。此时全局锁并没有释放，全局锁的释放取决于二阶段是提交命令还是回滚命令。
    - TC根据所有的分支事务执行结果，向RM下发提交或回滚命令。
    - RM如果收到TC的提交命令，首先立即释放相关记录的全局锁，然后把提交请求放入一个异步任务的队列中，马上返回提交成功的结果给TC。异步队列中的提交请求真正执行时，只是删除相应UNDO LOG记录而已。
    - RM如果收到TC的回滚命令，则会开启一个本地事务，通过XID和Branch ID查找到相应的UNDO LOG记录。将UNDO LOG中的后镜与当前数据进行比较。如果不同，说明数据被当前全局事务之外的动作做了修改。这种情况，需要根据配置策略来做处理。如果相同，根据UNDO LOG中的前镜像和业务SQL的相关信息生成并执行回滚的语句并执行，然后提交本地事务达到回滚的目的，最后释放相关记录的全局锁。

4、长事务弊端

(1)并发情况下，数据库连接池容易被撑爆；
(2)容易造成大量的阻塞和锁超时，长事务还占用锁资源，也可能拖垮整个数据库；
(3)执行时间长，容易造成主从延迟；
(4)回滚所需要的时间比较长，事务越长整个时间段内的事务也就越多；
(5)undolog日志越来越大，长事务意味着系统里面会存在很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

5、buffer pool

buffer pool是一块内存区域，为了提高数据库的性能，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据，数据库的增删改查都是在buffer pool上进行。
buffer pool 里面缓存的数据内容也是一个个数据页，其中有三大双向链表:
- free 链表： 用于帮助我们找到空闲的缓存页；
- flush 链表： 用于找到脏缓存页，也就是需要刷盘的缓存页；
- lru 链表： 用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据。
预读机制:
- buffer Pool有一项特技叫预读，存储引擎的接口在被Server层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到buffer Pool。

6、varchar 和 char 区别

varchar：可变长度，varchar(50)表示最多存放50个字符
char：定长

7、VarChar 建议不要超过255

当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
当大于255时，长度标识位需要两个字节，并且建立的索引也会失效

8、Mysql 中有哪些锁

基于锁的属性分类：共享锁、排他锁
基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁
基于锁的状态分类：意向共享锁、意向排它锁、死锁

二、日志类型

1、binlog

binlog是归档日志，属于Server层的日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息。
主要作用：
- 主从复制
- 数据恢复

2、undolog

undolog是InnoDB存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）。
主要作用：
- 事务回滚
- 实现多版本控制(MVCC)

3、MVCC作用

MVCC指多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。
在MVCC协议下，每个读操作会看到一个一致性的快照，这个快照是基于整个数据库的，并且可以实现非阻塞的读，用于支持读提交和可重复读隔离级别的实现。
MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是记录在undolog中的。

4、relaylog

relaylog是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。
master主节点的binlog传到slave从节点后，被写入relaylog里，从节点的slave sql线程从 relaylog里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

5、redolog

redolog是 InnoDB存储引擎所特有的一种日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
可以做数据恢复并且提供crash-safe能力。
当有增删改相关的操作时，会先记录到Innodb中，并修改缓存页中的数据，等到mysql 闲下来的时候才会真正的将redolog中的数据写入到磁盘当中。
日志记录过程：
- InnoDB 的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
- 如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生内存抖动现象，从肉眼的角度来观察会发现mysql会宕机一会儿，此时就是正在刷盘了。

6、redolog 与 binlog 的区别

(1)redolog 是 Innodb 独有的日志，而binlog是server层的，所有的存储引擎都有使用到；
(2)redolog记录了具体的数值，对某个页做了什么修改，binlog记录的是操作内容；
(3)binlog大小达到上限或者flush log会生成一个新的文件，而redolog有固定大小只能循环利用；
(4)binlog日志没有crash-safe的能力，只能用于归档。而redolog有crash-safe能力。

7、WAL

WAL就是 Write-Ahead Logging，将所有的修改都先被写入到日志中，然后再写磁盘，用于保证数据操作的原子性和持久性。
好处:
- (1) 读和写可以完全地并发执行，不会互相阻塞；
- (2) 先写入log中，磁盘写入从随机写变为顺序写，降低了client端的延迟，并且由于顺序写入大概率是在一个磁盘块内，这样产生的io次数也大大降低；
- (3) 写入日志当数据库崩溃的时候可以使用日志来恢复磁盘数据。

三、存储引擎

1、MyISAM 与 InnoDB 的区别

InnoDB支持事务，MyISAM不支持事务。
InnoDB支持行级锁和表级锁，MyISAM仅支持表级锁。
InnoDB支持外键，MyISAM不支持外键。
InnoDB支持MVCC（多版本并发控制），MyISAM不支持。
InnoDB不支持全文索引，MyISAM支持。
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须要有主键；MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
Innodb有redolog日志文件，MyISAM没有。
Innodb存储文件有frm、ibd，其中frm是表定义文件，ibd是数据文件；Myisam存储文件有frm、MYD、MYI，其中frm是表定义文件，myd是数据文件，myi是索引文件。
InnoDB必须有唯一索引(主键)，如果没有指定的话InnoDB会自己生成一个隐藏列Row_id来充当默认主键，MyISAM可以没有唯一索引。

2、Innodb 事务提交

Innodb的事务提交是两阶段提交
先写redolog，后写binlog。假设redolog写完，binlog还没有写完的时候，MySQL进程异常重启，这时候 binlog里面就没有记录这个语句。然后会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写binlog后写redolog。如果在binlog写完之后 crash，由于redolog还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
所以，「如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致」。

3、InnoDB 删除表数据后表的大小却没有变动

在使用delete删除数据时，其实对应的数据行并不是真正的删除，是逻辑删除，InnoDB仅仅是将其标记成可复用的状态，所以表空间不会变小。