MYSQL的重要知识概述（高级篇）

整理仓促，文章中有任何问题，敬请提出，感谢支持，让我们共同进步吧！

MYSQL的相关知识概述，共分基础篇、进阶篇和高级篇！

1、事务

事务（Transaction）是由一系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执行逻辑单元。

(1) 事务的语法

(2) 事务的特性

(3) 事务并发问题

(4) 事务隔离级别

(5) 不同隔离级别的锁的情况

(6) 隐式提交

（1）事务的语法

1. start transaction; begin;

2. commit; 使得当前的修改确认

3. rollback; 使得当前的修改被放弃

（2）*事务的ACID*特性 **

1. 原⼦性（Atomicity）

事务的原⼦性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只允许出现两种状态之一。

（1）全部执行成功

（2）全部执行失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣一样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

1. ⼀致性（Consistency） 事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执行之后，数据库都必须处以⼀致性状态。
2. 隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。 ⼀个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。

1. 持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服 务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态

（3）事务的并发问题

脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的 数据是脏数据。 不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的 过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。 幻读：重复查询的过程中，数据 就发⽣了量的变化（insert， delete）。

（4）**事务隔离级别 **

读未提交（READ_UNCOMMITTED）

读已提交（READ_COMMITTED）

可重复读（REPEATABLE_READ）

顺序读（SERIALIZABLE）

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。 ⼀般数据默认级别是 读以提交或可重复读

（5）不同的隔离级别的锁的情况

1. 读未提交（RU）: 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。2. 读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。

2. 可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，并且没有幻读的情况。

3. 序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

（6）隐式提交

DQL:查询语句句

DML:写操作(添加,删除,修改)

DDL:定义语句句(建库,建表,修改表,索引操作,存储过程,视图)

DCL:控制语⾔言(给⽤用户授权,或删除授权)

DDL（Data Defifine Language）：都是隐式提交。

隐式提交：执⾏行这种语句句相当于执⾏行行commit;

2、**存储引擎 **

存储引擎以前叫做 表处理器 ，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操

作。

为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为

MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQL

server 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、"读取

索引下一条内容"、"插入记录"等等。

所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返

回给客户端就好了。

MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录

组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存

储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引

擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

4、*MyISAM和InnoDB***表引擎的区别 **

**1) ****事务支持 **

MyISAM不支持事务，而InnoDB支持。

**2) ****存储结构 **

MyISAM：每个MyISAM在磁盘上存储成三个文件。

frm文件存储表结构。

MYD文件存储数据。

.MYI文件存储索引。

InnoDB：主要分为两种文件进行存储

frm 存储表结构

ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）

**3) ****表锁差异 **

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。 InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

**4) ****表主键 **

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 InnoDB的主键范围更大，最大是MyISAM的2倍。

**5) ****表的具体行数 **

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后， myisam和innodb处理的方式都一样。

*6) CURD***操作 **

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用 truncate table这个命令。

**7) ****外键 **

MyISAM：不支持 InnoDB：支持

**8) ****查询效率 **

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视 图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。 另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优 势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

*9）MyISAM和InnoDB***两者的应用场景： **

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用InnoDB。

5、MySQL中的utf8和utf8mb4

有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使 用 utf8mb4 。

6、三大范式

第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：属性完全依赖于主键** [ 消除部分子函数依赖 ]**。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是 在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）‘

’*第 二范式（2NF*）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上 一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第三范式：**属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足 第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF****）要求一个数据库表中不包含已在其 **

**它表中已包含的非主关键字信息。 **

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。