MySQL面试笔记

目录

脏读、不可重复读与幻读

间隙锁

MySQL数据隔离级别

SQL语言定义了三种操作数据库的能力

事务的四个基本特征

---

MySQL数据类型

MySQL数据库的数据类型主要是分为数值型，日期时间型以及字符串型这三个大类，具体如下图所示：

MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数，MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

字符串类型：

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。主要是定长与变长的区别，CHAR类型占用空间比较大，但是处理速度比VARCHAR快，如果长度变化不大，如身份证号码那种，最好选择CHAR类型。而对于评论字符串，最好选择VARCHAR；

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。

ENUM类型和SET类型：长度不同，ENUM类型最多可以由65535个成员，而SET类型最多只能包含64个成员。且ENUM只能单选，而SET类型可以多选；

脏读、不可重复读与幻读

脏读：是指一个事务中访问到了另外一个事务未提交的数据，如下图，即使事务2还未提交，事务1读取的结果夜市修改后的age=10。

不可重复读：指一个事务查询同一条记录2次，得到的结果不一致

如下图，当事务1两次读取之间，事务2对读取行进行修改提交，那么两次读取结果将不一致。

幻读：一个事务查询2次，得到的记录条数不一致。

如下图，在事务1两次范围查找中间，事务2对表进行了插入操作，导致获取到的记录数量不同（即多了一行）。

注：不可重复读和幻读产生的原因都是读的过程中数据前后不一致，只是前者侧重于修改，后者侧重于增删。严格来讲“幻读”可以被称为“不可重复读”的一种特殊情况，没错的。但是从数据库管理的角度来看二者是有区别的。解决“不可重复读”只要加行级锁就可以了。而解决“幻读”则需要加表级锁（在InnoDB中采用MVCC+索引上的next key lock（间隙锁）策略避免幻读），代价要大许多。

幻读 侧重【行数量】发生了变化；不可重复读 侧重 【某一行数据】发生了变化。幻影行（phantom row），重点是 row，同一个事务中，【某一行】之前没有，后来读到了，那我认为读到幻影行了（比如其他事务插入了新行）。

间隙锁

记录锁也叫行锁，只会锁住某一行数据，以阻止其他事务插入，更新，删除这一行。

间隙锁是封锁索引记录中的间隔，是Innodb在可重复度提交下为了解决幻读问题时引入的锁机制。

间隙锁的枷锁规则如下：

• 1.加锁的基本单位是（next-key lock）,他是前开后闭原则
• 2.插叙过程中访问的对象会增加锁
• 3.索引上的等值查询--给唯一索引加锁的时候，next-key lock升级为行锁
• 4.索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
• 5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

对于唯一索引，以ID为例：

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE `id` = 5 FOR UPDATE;
2. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;

举例：对于表中存在数据2、5、9的情况，则间隙为 ( -INF，2]，(2，5]，(5，9]，(9，INF) 四个区间。

当查询id = 5时，会产生记录锁锁住该行，不会产生间隙锁。

当查找[2，5]这个范围时，会给[2，5]和(5，9]加锁,，(5，9]加锁是因为原则5。

当查找[5，6）时，会增加(2，5]，根据原则5，又增加(5，9]，由于在5处是等值查询，根据原则3，间隙锁降为行锁，故剩余[5，9]。

MySQL数据隔离级别

MySQL 里有四个隔离级别：

1. Read uncommttied（可以读取未提交数据）
2. Read committed（可以读取已提交数据）
3. Repeatable read（可重复读）
4. Serializable（可串行化）。

不同事务隔离级别有不同的效果(InnoDB默认是Repeatable )，隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，通过访问MVCC数据库引擎返回的快照避免幻读。

SQL语言定义了三种操作数据库的能力

• DDL: Data Definition Language
  DDL允许用户定义数据，即创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行
• DML: Data Manipulation Language
  DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
• DQL: Data Query Language
  DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点：SQL语言关键字不区分大小写，但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

事务的四个基本特征

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。

1）原子性（Atomicity）：

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，[删删删]因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2）一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3）隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

4）持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

ACID靠什么保证？

A 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C 一致性一般由代码层面来保证

I  隔离性由MVCC来保证（MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本）

D 持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

redo log 、 undo log 和 checkpoint

数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer；

存储引擎也会为redo undo日志开辟内存缓存空间，log buffer。磁盘上的日志文件称为log file，是顺序追加的，性能非常高

redo log 和undo log

• 名词：两种流程，redo重做流程，undo撤销还原流程；或者是redo日志与undo段的简称。
• 动词：redo即重做，undo即撤销还原。

redo是物理逻辑日志。undo是逻辑日志

redo 即redo日志，记录数据库变化的日志，只要你修改了数据块那么就会记录redo信息，当然nologging除外了。修改的数据块包括：表所在数据块（表数据块），索引所在数据块（索引数据块），以及undo段所在数据块（undo数据块）！

undo 即undo段，是指数据库为了保持读一致性，存储历史数据在一个位置。

前滚和后滚

• 前滚，是指从“以前正常点”往前，一直到崩溃点。
• 回滚，是指从“崩溃点”往后，一直到数据一致性。

前滚：当实例崩溃时，可以使用redo从以前正常的点前滚到崩溃点。（前滚从一致性检查点，“即当时检查过所有的SCN是全部一致的时间点”，即chenkpoint，一直往前滚到崩溃的时间点）。当数据库回到一致性检查点时，相当于之后什么都没有发生过，数据全被清空了。数据库只好根据redo模拟人的操作，使用redo里的信息重做（use redo log to redo)，构造undo块，表块，索引块等。

回滚：构造的表数据块中，有已修改的脏数据但未提交，就需要利用前滚中构造的undo数据块里的信息来undo撤销还原，覆盖回滚rollback罗（保持一致性啊，每种块里的scn号都一样，那么数据库就可以打开了）。

undo与redo（流程）的联系

• 因为，数据在没有commit前，是随时从内存中写入到表数据块的，属于脏数据。 数据库崩溃后即使使用redo流程进行redo操作，但是脏数据还在，脏数据怎么处理，就只能靠undo流程，使用undo数据块的旧数据覆盖了。
• 但是不管是脏的还是旧的，都在redo日志中复制了一份。

undo与redo作用

undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。

事务执行的各个阶段：

（1）写undo日志到log buffer；

（2）执行事务，并写redo日志到log buffer；

（3）如果innodb_flush_log_at_trx_commit=1，则将redo日志写到log file，并刷新落盘。

（4）提交事务。

checkpoint

是为了定期将db buffer的内容刷新到data file。当遇到内存不足、db buffer已满等情况时，需要将db buffer中的内容/部分内容（特别是脏数据）转储到data file中。在转储时，会记录checkpoint发生的”时刻“。在故障回复时候，只需要redo/undo最近的一次checkpoint之后的操作。

检查点技术可分为简单检查点与更优化的非静止检查点。在一个简单检查点中有如下过程：
（1）停止接受新的事务
（2）等待当前所有活跃事务完成或中止，并在日志中写入commit或abort记录。
（3）将当前位于内存的日志，将缓冲块刷新到磁盘
（4）写入日志记录<CKPT>，并再次刷新到磁盘
（5）重新开始接受事务
系统恢复时，可以从日志尾端反向搜索，直到找到第一个<CKPT>标志，而没有必要处理<CKPT>之前的记录。

非静止检查点

简单检查点期间需要停止响应，如果当前活跃事务需要很长时间来处理，那系统看起来似乎卡住了。非静止检查点允许进行检查点时接受新事务进入，步骤如下：
（1）写入日志记录<START CKPT(t1,…tn)>，其中t1,…tn是当前活跃的事务
（2）等待t1,…tn所有事务提交或中止，但仍接受新事务的进入
（3）当t1,…tn所有事务都已完成，写入日志记录<END CKPT>

当使用非静止检查点技术，恢复时的也是从日志尾向前扫描，可能先遇到<START CKPT>标志，也可能先遇到<END CKPT>标志：
1.先遇到<START CKPT(t1,…tn)>时，说明系统在检查点过程中崩溃，未完成事务包括2部分：(t1,…tn)记录的部分及<START>标志后新进入部分。这部分事务中最早那个事务的开始点就是扫描的截止点，再往前可以不必扫描。

2.先遇到<END CKPT>，说明系统完成了上一个周期的检查点，新的检查点还没开始。需要处理2部分事务：<END CKPT>标志之后到系统崩溃时这段时间内的事务及上一个<START>，<END>区间内新接受的事务。为此扫描到上一个检查点<START CKPT()>就可以截止。