数据库八股文

Mysql

Mysql select 语句执行流程图

mysql 有几种存储引擎？

常见的存储引擎好像有：InnoBD，MyISAM，Memory等。

innoDB 是怎么存储一个行数据的？

在讲这个问题之前，我们先来了解下MySQL的数据是保存在哪个文件的。当我们创建一个database后，会在var/lib/mysql目录下创建一个以数据库为名的目录。保存表结构和表数据的文件都会存放在这个目录里。该目录下有三种类型的文件：

• db.opt: 存储当前数据库的默认字符集和字符校验的规则；
• t_order.frm: 存储表结构的文件（元数据，表结构定义）
• t_order.ibd: 存储表数据的文件

存储数据的文件，你知道这个文件的结构是吗？表空间由段（segment）、区（extent）、页（page）、行（row）组成。

• 行：数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。 后面我们详细介绍 InnoDB 存储引擎的行格式，也是本文重点介绍的内容。
• 页：InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB。页是innodb的最小磁盘资源管理单元，也是磁盘和内存之间传输的最小单位。
• 区：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
• 段：表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。
  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合， MVCC 利用了回滚段实现了多版本查询数据。

InnoDB 的行格式

我们知道行是存储记录的，行格式就是存储记录的格式。InnoDB提供了四种行格式：Redundant、Compact、Dynamic和Compressed。

• 重点介绍Compact行格式，这是InnoDB的默认行格式

记录 = 记录的额外信息 + 记录的真实信息

记录的额外信息： 变长字段长度列表、NULL 值列表、记录头信息

• 变长字段长度：像varchar这种字段的长度不是固定的，而是由用户定义的可变长的，需要记录对应的真实数据的长度。

变长字段的真实数据占用的字节数会按照列的顺序逆序存放，为什么呢？，记录头信息中指向下一个记录的指针，指向的是系啊一条记录的记录头信息和真实数据之间的位置。变长字段长度列表和NULL值列表逆序排放可以提高访问效率，可以使得位置靠前的记录的真实数据和数据对应的 字段长度信息可以同时存在在一个CPU Cache Line，提高CPU cache的命中率。

• NULL值列表：如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。 二进制位的值为1时，代表该列的值为NULL。 二进制位的值为0时，代表该列的值不为NULL。 另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

注意：值为NULL的列是不会储存在真实数据中的，只会在NULL值列表中的相应位置置为1。

• 记录头信息：
• delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
• next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

就是记录各列的值，重点是有三个字段：row_id、trx_id、roll_pointer

• row_id(6个字节)：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
• trx_id(6个字节): 事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
• roll_pointer(7个字节)：回滚指针，表示这个数据的回滚指针，即记录上个版本的指针。roll_pointer是必需的，占用 7 个字节。

如何计算varchar（n）的n最大值？

单字段

n的最大值不能超过65535，使用65535 - NULL值列表占用字节数 - 变长字段长度列表占用字节数(变长字段占有字节数小于255占用1个字节，大于255占用2个字节) = 可以使用的最大n

注意这是在ascill的情况下，一个字符占有一个字节；在utf8的情况下，一个字符占有3个字节。所以在utf8的情况下，n的最大值就是可以使用最大n/3

多字段

如果有多个字段，要保证所有字段的长度之和小于等于可以使用的最大字节数。

行溢出如何处理

当某列值类型为text长文本类型时，大概率会发生行溢出，即一行里面没办法存储所有的数据。前面我们知道有页这个结构，页有个类型叫做溢出页，当一行的数据无法存储在一个页里面时，就会存储在溢出页里面。然后在真实数据内使用20个字节存贮溢出页的地址。

MVCC 是什么？&& innoDB 事务的隔离级别

MVCC叫多版本并发控制，用来实现可恢复读的隔离级别的。

首先来深挖这句话。

什么是隔离？隔离是事务的四大特性之一，其他的特性分别是ACID，原子性，一致性，隔离性，持久性。

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成。如果事务执行过程中发生错误，就要触发回滚到事务开始前的状态。通过undo日志实现
• 一致性：事务操作前和操作后数据满足完成性约束。换句话来说，就是数据要保持完整。这个特性的实现是依靠其他三个特性的。
• 隔离性：数据库允许并发事务，同时多个并发事务的交叉执行不能导致数据的不一致。隔离性的实现是比较困难的，下文会介绍。通过MVCC实现或者锁机制
• 持久性：事务一旦提交，对数据的改变是永久性的。通过redo日志。

下面来详细讲解隔离性的实现。

首先实现隔离性会遇到即可困难，这个困难是伴随并发事务的出现必然会出现的。

• 脏读：一个事务读到另一个事务的未提交的修改的数据。

• 不可重复读：一个事务两次读取同一个数据的内容不一样。

• 幻读：一个事务内两次查询到的符合查询条件的记录数量不一致。

通过解决上面的问题中的几个，划分出多个隔离级别，

• 什么也没解决：读未提交级别；
• 解决脏读：读提交级别；
• 解决脏读和不可重复读：可重复读级别；
• 解决脏读、不可重复读和幻读：串行化级别。

等于于读提交级别和可重复读级别的实现就是Read View来实现的，它们的区别就是创建读快照的时机不同而已。读提交级别是在每个语句执行前都会重新生成一个读快照。可重复读级别是在事务开始的时候生成一个读快照。

Read View在MVCC中式如何工作的

Read View到底是什么？

Read View的组成中有四个重要的字段：creator_trx_id, m_ids, min_trx_id, max_trx_id

• creator_trx_id：创建者事务ID，即创建这个Read View的事务ID；
• m_ids：创建Read View时，当前数据库中活跃但未提交的事务id列表；
• min_trx_id：创建Read View时，最小的活跃事务ID；
• max_trx_id：创建Read View时，应当给下一个事务的id值。

还记得我们在之前的行结构的讲解中讲到记录真实数据中的隐藏字段吗？就是trx_id 和 rolled_pointer。这里可以讲讲这两个字段的作用了。

• trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务id记录在trx_id字段中。
• roll_pointer：每次对某条聚簇记录进行改动时，都会把旧版本的记录写入到undo日志中。然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

非常重要的地方来叻！

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

注意：不可见只是在查询的时候会取查找旧版本的数据，但是你修改就直接修改了。

可重复读是如何工作的？ 可重复读隔离级别是在启动事务时生成一个Read View，然后整个事务期间都在用这个Read View。

举个例子：

• 事务 B 读取小林的账户余额记录，读到余额是 100 万；
• 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
• 事务 B 读取小林的账户余额记录，读到余额还是 100 万；
• 事务 A 提交事务；
• 事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；

接下来，跟大家具体分析下。

事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小，这意味着修改这条记录的事务早就在事务 B 启动前提交过了，所以该版本的记录对事务 B 可见的，也就是事务 B 可以获取到这条记录。

接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链，如下图：

你可以在上图的「记录的字段」看到，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了，于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的 trx_id 是事务 A 的事务 id（trx_id = 51）。

然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。

而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。

最后，当事物 A 提交事务后，由于隔离级别时「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务， 事务 B 第三次读取记录时，读到的记录都是小林余额是 100 万的这条记录。

就是通过这样的方式实现了，「可重复读」隔离级别下在事务期间读到的记录都是事务启动前的记录。

读提交是如何工作的？

工作流程和可重复读基本一致，区别的地方在于都快照创建的时机不同，读提交创建的时机是每次读取数据时，都会生成一个读快照。

以上便是MVCC的工作流程了。

那么幻读呢？

对于快照读(即普通的select语句)，通过MVCC机制避免；对于当前读语句，是通过next-key lock(记录锁+间隙锁)的方式解决幻读的对于锁机制的讲述，在下文进行。

Mysql有哪些锁？

全局锁

表级锁

• 表锁
• 元数据锁(MDL)
• 意向锁
• AUTO-INC锁

  行级锁

• Record 锁
• Gap 锁
• Next-key 锁
• 插入意向锁（防止被饿死）

innodb索引的类型

inoodb的索引的类型可以从四个角度取分类：数据结构、物理储存、字段特性、字段个数

• 数据结构
  • B+树索引
  • Full-text索引
• 物理存储（Innodb的索引类别）
  • 聚簇索引（主键索引）
  • 二级索引（辅助索引）
• 字段特性
  • 主键索引
  • 唯一索引
  • 普通索引
  • 前缀索引 / 对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的
• 按字段个数分类
  • 单列索引
  • 联合索引 / 建立在多列的索引上，通过将多个字段组成的一个索引，该索引被称为联合索引。

注意：联合索引的key值是多段的，按照最左匹配的原则。所以查询需要遵循最左匹配原则。

索引优化方法

• 前缀索引优化
• 覆盖索引优化
• 主键索引最好是自增的
• 防止索引失效
• 索引最好设置为NOT NULL

为什么要使用B+树作为索引数据结构？

• 查询效率高
• IO次数少
• 插入删除对于B+树的结构变化小
• B+树的叶子节点是有序的，可以做范围查询

Mysql中的B+tree

MySQL日志：undo log、redo log、binlog

undolog

回滚流程：

• 支持事务回滚，实现事务的原子特性
• 是MVCC实现的关键因素

Undo页面：储存事务回滚日志undolog的页面

redolog

WAL技术 Write-Ahead Logging

MySQL的写操作并不是立即马上写到磁盘中，而是先写日志，然后在合适的时间再写到磁盘上。

relog：记录此次事务修改后的数据状态，记录的是更新之后的值

reedo流程：

• 使用redolog实现事务的持久性特性
• 将写操作从随机写变成了顺序写

relog什么时候刷盘

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

• MySQL 正常关闭时； 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下图说明了参数不同时的各自刷盘策略）。

binlog

前两个日志是InnoDB引擎特有的，而binlog是MySQL的Server层实现的。

binlog文件记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如select、show等。

读到这里可能会好奇，既然有了 redo log，为什么还要有 binlog 呢？redolog和binlog的区别是什么？

这个问题和Mysql的时间线有关。最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。 而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

区别主要有四个：

Binlog还有个非常重要的作用，就是实现MYSQL的主从复制

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

同样有何时刷盘问题？

当事务开始时，将上一个事务的binlog提交到binlog cache中，当事务完成时在将binlog cache刷盘。

频繁的刷盘会导致io过快，myssql提供一个参数来控制binlog的刷盘频率：sync_binlog / 这不是跟redis的appendfsync参数一毛一样吗？其实binlog的设计和redis的aof日志的设计上有异曲同工之妙。

Redis

Redis中的key值非常大会造成什么影响？

1. 对持久化的影响（AOF、RDB）： 当 AOF 写回策略配置了 Always 策略，如果写入是一个大 Key，主线程在执行 fsync() 函数的时候，阻塞的时间会比较久，因为当写入的数据量很大的时候，数据同步到硬盘这个过程是很耗时的。

AOF 重写机制和 RDB 快照（bgsave 命令）的过程，都会分别通过 fork() 函数创建一个子进程来处理任务。会有两个阶段会导致阻塞父进程（主线程）：

• 创建子进程的途中，由于要复制父进程的页表等数据结构，阻塞的时间跟页表的大小有关，页表越大，阻塞的时间也越长；
• 创建完子进程后，如果父进程修改了共享数据中的大 Key，就会发生写时复制，这期间会拷贝物理内存，由于大 Key 占用的物理内存会很大，那么在复制物理内存这一过程，就会比较耗时，所以有可能会阻塞父进程。

1. 其他影响：

• 客户端超时阻塞。由于 Redis 执行命令是单线程处理，然后在操作大 key 时会比较耗时，那么就会阻塞 Redis，从客户端这一视角看，就是很久很久都没有响应。
• 引发网络阻塞。每次获取大 key 产生的网络流量较大，如果一个 key 的大小是 1 MB，每秒访问量为 1000，那么每秒会产生 1000MB 的流量，这对于普通千兆网卡的服务器来说是灾难性的。
• 阻塞工作线程。如果使用 del 删除大 key 时，会阻塞工作线程，这样就没办法处理后续的命令。
• 内存分布不均。集群模型在 slot 分片均匀情况下，会出现数据和查询倾斜情况，部分有大 key 的 Redis 节点占用内存多。

如何解决Redis中key值非常大的问题？

• 在设计阶段把key值设计成小key值
• 定时检查 Redis 是否存在大 key ，如果该大 key 是可以删除的，不要使用 DEL 命令删除，因为该命令删除过程会阻塞主线程，而是用 unlink 命令（Redis 4.0+）删除大 key，因为该命令的删除过程是异步的，不会阻塞主线程。

Redis高可用策略

主从复制

哨兵模式

切片集群

Redis脑裂

由于网络问题，集群节点之间失去联系。主从数据不同步；重新平衡选举，产生两个主服务。等网络恢复，旧主节点会降级为从节点，再与新主节点进行同步复制的时候，由于会从节点会清空自己的缓冲区，所以导致之前客户端写入的数据丢失了。

Redis 过期删除

Redis内存满了会怎么处理？

Redis 缓存

缓存雪崩&&缓存击穿

缓存穿透

常见的缓存更新策略

cache aside

read/write through && write back