Mysql面试题

本文引用：【精选】MySQL数据库面试题总结（2022最新版）_mysql面试题_程序猿周周的博客-CSDN博客 

1 简介

数据库：存储数据的物理操作系统文件或其他形式文件的集合
数据库实例：MySql数据库实例由后台线程以及一个共享内存区组成，负责操作数据库文件。
MySql是一个单进程多线程架构的数据库，MySql数据库实例在系统上表现就是一个进程。

1.1体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

1.1.1 网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务器编程技术，例如常见的Java、C、Python、.NET等，它们通过各自的API技术与MySQL建立连接

1.1.2 服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分

1.1.3 存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

1.1.4 系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

1.2 MySQL运行机制

前台操作触发Mysql服务器执行请求
前台⽤户各种操作触发后台sql执⾏，通过web项⽬中⾃带的数据库连接池：如dbcp、c3p0、druid等，与数据库服务器的数据库连接池建⽴⽹络连接；数据库连接池中的线程监听到请求后，将接收到的sql语句通过SQL接⼝响应给查询解析器，查询解析器将sql按照sql的语法解析出查询哪个表的哪些字段，查询条件是啥；再通过查询优化器处理，选择该sql最优的⼀套执⾏计划，然后执⾏器负责调⽤存储引擎的⼀系列接⼝，执⾏该计划⽽完成整个sql语句的执⾏。

连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接

SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果

解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法

查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互

执行器：根据执行计划调用存储引擎的接口。执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL 语句的执行计划。

ref Mysql和Innodb存储引擎_mysql引擎innodb_小刘想学架构的博客-CSDN博客

2 日志

MySQL 中有哪些常见日志？
重做日志（redo log）：物理日志
作用是确保事务的持久性。 redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已提交事务数据。

回滚日志（undo log）：逻辑日志
作用是保证数据的原子性。 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

二进制日志（binlog）：逻辑日志
常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。

错误日志（errorlog）
记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

普通查询日志（general query log）
记录了服务器接收到的每一个命令，无论命令语句是否正确，因此会带来不小开销，所以也是默认关闭的。

慢查询日志（slow query log）
记录执行时间过长和没有使用索引的查询语句（默认 10s），同时只会记录执行成功的语句。

中继日志（relay log）
在从节点中存储接收到的 binlog 日志内容，用于主从同步。

两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

ref MySQL中的几种日志了解

3 事务

3.1 数据库事务

数据库事务具有ACID四大特性：原子性、一致性、隔离性和持久性

原子性(Atomicity)：事务是一个原子性质的操作单元，一个事务所包含的所有操作，要么全部执行成功，要么一个都不执行，如果中途操作失败，整个事务都会失败回滚。

实现原理：通过undo_log来实现，记录当前操作的反操作，失败回滚时便执行undo_log，成功提交时不做处理。

一致性(Consistency)：在事务开始之前和完成之后，数据都必须保持一致状态，必须保证数据库的完整性。也就是说，数据必须符合数据库的规则。

实现原理：通过原子性、隔离性、持久性的实现保证一致性。

隔离性(Isolation）:不同事务之间是互相隔离的，不能互相干扰，一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

实现原理：通过innodb的锁机制，包括共享锁、排它锁，以及MVCC实现的。

持久性(Durability）:持久性也成为永久性，指一个事务一旦提交，那么对应的数据修改是永久写在磁盘上的，它对数据库中数据的改变就应该是永久的。

实现原理：通过redo_log实现，对于每一次操作会形成redo_log，并在事务提交操作前持久化到磁盘，保证发生故障时可以进行数据恢复，保障持久性。

3.2 脏读、幻读和不可重复度

事务的并发问题：脏读、不可重复读、幻读
脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

3.3 隔离级别

MySQL 支持四种隔离级别，默认为 Repeatable read (可重复读)；而 Oracle 只支Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。

第一种隔离级别：Read uncommitted(读未提交)
如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

解决了更新丢失，但还是可能会出现脏读

第二种隔离级别：Read committed(读提交)
如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

解决了更新丢失和脏读问题

第三种隔离级别：Repeatable read(可重复读取)
可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

解决了更新丢失、脏读、不可重复读、但是还会出现幻读

第四种隔离级别：Serializable(可序化)
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

解决了更新丢失、脏读、不可重复读、幻读(虚读)。

4 索引

4.1 定义

索引是对数据库表中一列或多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

4.2 分类

1、从物理结构上可以分为聚集索引和非聚集索引两类：

聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；

非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

「聚簇索引」是 物理有序 的；「非聚簇索引」是 逻辑有序，物理无序。

在 mysql 中 数据存储顺序就是聚簇索引的顺序，所以一个表只有一个聚簇索引，其他索引都是非聚簇的。

2、从应用上可以划分为一下几类：

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；

唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；

主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

4.3 优缺点

1、优点：

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2、缺点

既然增加索引有如此多的优点，为什么不对表中的每一个列都创建一个索引呢？这是因为索引也是有缺点的：

创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

4.4 索引设计原则

• 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

• 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

• 为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

• 限制索引的数目

每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

• 小表不建议索引（如数量级在百万以内）

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

• 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。此时尽量使用前缀索引。

• 删除不再使用或者很少使用的索引

4.5 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.

B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询。

4.6 Hash VS B+ 树索引

Hash
1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。

3）Hash 任何时候都避免不了回表查询数据.

4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+ Tree

1）B+ 树本质是一棵查找树，自然支持范围查询和排序。

2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

4.7 为何使用 B+ 树而非二叉查找树做索引

我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。

文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

4.8 为何使用 B+ 树而非 B 树做索引

在此之前，先来了解一下 B+ 树和 B 树的区别：

B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

• B+ 树减少了 IO 次数。

由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

• B+ 树查询效率更稳定

由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。

• B+ 树更加适合范围查找

B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

ref 为什么 B+ 树比 B 树更适合应用于数据库索引？

4.9 最左匹配原则

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。

再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。

最左匹配的原理

上图可以看出 a 是有顺序的（1、1、2、2、3、3），而 b 的值是没有顺序的（1、2、1、4、1、2）。所以 b = 2 这种查询条件无法利用索引。

同时我们还可以发现在 a 值相等的情况下（a = 1），b 又是顺序排列的，所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

ref 最左匹配原则

4.10 覆盖索引

在 B+ 树的索引中，叶子节点可能存储了当前的键值，也可能存储了当前的键值以及整行的数据，这就是聚簇索引和非聚簇索引。 在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

5 存储

5.1存储引擎

5.1.1 哪些存储引擎

InnoDB存储引擎

MyISAM存储引擎

MEMORY存储引擎

ref 几种MySQL数据库引擎优缺点对比

5.1.2 InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。

2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

4、InnoDB支持外键完整性约束

5.1.3 MyISAM VS InnoDB

1）InnoDB 支持事务，而 MyISAM 不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。

2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。

3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。

那为什么 InnoDB 没有使用这样的变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。

5）InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。

InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。

6）InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，而 Myisam 可以没有主键。

ref MyISAM与InnoDB 的区别（9个不同点）

5.1.4 InnoDB 的四大特性

插入缓冲insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

5.1.5 InnoDB 为何推荐使用自增主键

自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

5.1.6 如何选择存储引擎

默认使用 InnoDB，MyISAM 适用以插入为主的程序，比如博客系统、新闻门户。

1. 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
5. 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
6. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

ref 几种MySQL数据库引擎优缺点对比

5.2 存储结构

5.2.1 页、区、段

• 页（Page）

首先，InnoDB 将物理磁盘划分为页（page），每页的大小默认为 16 KB，页是最小的存储单位。页根据上层应用的需要，如索引、日志等，分为很多的格式。我们主要说数据页，也就是存储实际数据的页。

• 区（Extent）

如果只有页这一个层次的话，页的个数是非常多的，存储空间的分配和回收都会很麻烦，因为要维护这么多的页的状态是非常麻烦的。

所以，InnoDB 又引入了区（Extent) 的概念。一个区默认是 64 个连续的页组成的，也就是 1MB。通过 Extent 对存储空间的分配和回收就比较容易了。

• 段（Segment）

为什么要引入段呢，这要从索引说起。我们都知道索引的目的是为了加快查找速度，是一种典型的用空间换时间的方法。

B+ 树的叶子节点存放的是我们的具体数据，非叶子结点是索引页。所以 B+ 树将数据分为了两部分，叶子节点部分和非叶子节点部分，也就我们要介绍的段 Segment，也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。

Segment 是一种逻辑上的组织，其层次结构从上到下一次为 Segment、Extent、Page。

5.2.2 什么是 Buffer Pool

Buffer Pool 是 InnoDB 存储引擎层的缓冲池，不属于 MySQL 的 Server 层，注意跟 8.0 删掉的“查询缓存”功能区分。

内存中以页（page）为单位缓存磁盘数据，减少磁盘IO，提升访问速度。缓冲池大小默认 128M，独立的 MySQL 服务器推荐设置缓冲池大小为总内存的 80%。主要存储数据页、索引页更新缓冲（change buffer）等。

预读机制
Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到 Buffer Pool。

预读策略有两种，为线性预读（linear read-ahead）和随机预读（random read-ahead），其中 InnoDB 默认使用线性预读，随机预读已经基本废弃。

线性预读认为如果前面的请求顺序访问当前区（extent）的页，那么接下来的若干请求也会顺序访问下一个区的页，并将下一个区加载到 Buffer Pool。在 5.4 版本以后默认开启，默认值为 56，最大不能超过 64，表示顺序访问 N 个页后触发预读（一个页16K，一个区1M，一个区最多64个页，所以最大值64）。

换页算法
与传统的 LRU 算法不同，因为面临两个问题：

1）预读失效：由于提前把页放入了缓冲池，但最终 MySQL 并没有从页中读取数据。

要优化预读失效，则让预读失败的页停留在缓冲池里的时间尽可能短，预读成功的页停留时间尽可能长。具体将 LRU 链分代实现，即新生代和老年代（old subList），预读的页加入缓冲池时只加入到老年代头部，只有真正被预读成功，则再加入新生代。

2）缓冲池污染：当批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL 性能急剧下降。

InnoDB 缓冲池加入了一个老生代停留时间窗口的机制，只有满足预读成功并且在老生代停留时间大于该窗口才会被放入新生代头部。

https://blog.csdn.net/wuhenyouyuyouyu/article/details/93377605

5.2.3 什么是 Change Buffer
​

如果每次写操作，数据库都直接更新磁盘中的数据，会很占磁盘IO。为了减少磁盘IO，InnoDB在Buffer Pool中开辟了一块内存，用来存储变更记录，为了防止异常宕机丢失缓存，当事务提交时会将变更记录持久化到磁盘（redo log），等待时机更新磁盘的数据文件（刷脏），用来缓存写操作的内存，就是Change Buffer

Change Buffer默认占Buffer Pool的25%，最大设置占用50%。

5.3 InnoDB

5.3.1 InnoDB 架构设计

以下主要从内存和线程的角度分析 InnoDB 的架构。

5.3.3.1内存池

主要工作：

• 维护所有进程/线程需要使用的多个内部数据结构
• 缓存磁盘上的数据，方便快速地读取，同时对磁盘文件数据修改之前在这里缓存
• 重做日志缓存

InnoDB内存池主要有以下部分

缓冲池
InnoDB是基于磁盘存储的，并将其中的记录按照页的方式进行管理。
而缓冲池就是一块内存区域，主要缓冲数据页和索引页。
InnoDB中对页的读取操作，首先判断该页是否在缓冲池中，若在，直接读取该页，若不在则从磁盘读取页数据，并存放在缓冲池中。
对页的修改操作，首先修改在缓冲池中的页，再以一定的频率（Checkpoint机制）刷新到磁盘。
参数：innodb_buffer_pool_size设置缓冲池大小

缓冲池通过LRU（Latest Recent Used，最近最少使用）算法进行管理。最频繁使用的页在LRU列表前端，最少使用的页在尾端，当缓冲池不能存放新读取的页时，首先释放LRU列表尾端的页（页数据刷新到磁盘，并从缓冲次中删除）。
InnoDB对于新读取的页，不是放到LRU列表最前端，而是放到midpoint位置（默认为5/8处）。
这是因为一些SQL操作会访问大量的页（如全表扫描），读取大量非热点数据，如果直接放到首部，可能导致真正的热点数据被移除。

关于页的概念会在存储篇解释，这里就理解为InnoDB将表数据拆分为若干固定大小的页，每页保存若干表记录。

重做日志缓存
重做日志先放到这个缓冲区，然后按一定频率刷新到重做日志文件。
参数：innodb_log_buffer_size

刷新规则：

1. Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件
2. 每一事务提交时会将重做日志刷新到重做日志文件（如果配置了）
3. 重做日志缓冲区使用空间大于1/2

额外的内存池
内存堆，对InnoDB内部使用的数据结构对象进行管理

Checkpoint机制

InnoDB对于对于DML语句操作（如Update或Delete），事务提交时只需在缓冲池中中完成操作，然后再通过Checkpoint将修改后的脏页数据刷新到磁盘。

InnoDB有两种Checkpoint
Sharp Checkpoint：数据库关闭是将所有脏页刷新会磁盘
Fuzzy Checkpoint：

• Master Thread Checkpoint
  Master Thread每个1秒或10秒按一定比例将缓存池的脏页列表刷新会磁盘
• FLUSH LRU LIST Checkpoint
  Page Cleaner线程发现LRU列表中可用页数量少于innodb_lru_scan_depth(1024)，就将LRU列表尾端移除，如果这些页中有脏页，就需要Checkpoint
• Async/Sync Flush Checkpoint
  重做日志文件空间不可以用时，将一部分脏页刷新到磁盘。
• Dirty Page too much Checkpoint:
  脏页数量太多（超过比例innodb_max_dirty_pages_pct,默认75），执行Checkpoint。

重做日志

重做日志是为了保证事务的原子性，持久性。InnoDB采用Write Ahread Log策略，事务提交时，先写重做日志，再修改页。
数据库宕机重启时通过执行重做日志恢复数据。
但由于Checkpoint机制，数据库宕机重启并不需要重做所有的日志，因为Checkpoint之前的页都刷新到磁盘了，只需执行最新一次Checkpoint后的重做日志进行恢复，这样可以缩短数据库的恢复时间。

InnoDB中重做日志文件是循环使用的。当页被Checkpoint刷新到磁盘后，对应的重做日志就不需要使用 ，其空间可以被覆盖重用。
如果待写入的重做日志文件空间不可用（脏页还没有刷新到磁盘），就需要强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

InnoDB 1.2.x(MySql 5.6)后，FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint操作放到Page Cleaner线程，以免阻塞用户线程。

5.3.3.2 线程

线程的作用：

1）负责刷新内存池中的数据，保证缓冲池的内存缓冲的是最近的数据

2）已修改的数据文件刷新到磁盘文件

3）保证数据库发生异常的情况下InnoDB能恢复到正常状态。

线程分类：

1）Master Thread

负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲（INSERT BUFFER），UNDO页的回收等。

2）IO Thread

负责 AIO 请求的回调处理。

3）Purge Thread

事务提交后，undo log 可能不再需要，由 Purge Thread 负责回收并重新分配的这些已经使用的 undo 页。

4）Page Cleaner Thread

将Master Threader中刷新脏页的工作移至该线程，如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。

ref深入理解InnoDB – 架构篇

5.3.2 InnoDB关键特性

5.3.2.1插入缓冲

插入聚集索引一般是顺序的，不需要磁盘的随机读取
但插入非聚集索引叶子节点不是顺序的，需要离散访问非聚集索引页，速度较慢。
对于非聚集索引的插入或更新，先判断插入的非聚集索引页是否在缓存池中，若在，直接插入，或不在，先放到一个Inser Buffer对象中，
然后根据一些算法将Insert Buffer缓存的记录通过后台线程慢慢合并刷新回辅助索引。
插入缓冲将多次插入合并为一次操作，减少磁盘的离散操作。

使用Insert Buffer需满足两个条件：
索引是辅助索引
索引不是唯一的（不需要查找索引页判断唯一性）

InnoDB从1.0.x引入Change Buffer，对INSERT，DELETE，UPDATE都进行缓冲。
参数：innodb_change_buffer_max_size，Change Buffer最多使用缓冲池内存空间。

5.2.2.2两次写 doublewrite

部分写失效：页数据写入到磁盘时只写了一部分（如16K数据只写了2K），数据库就宕机了，导致页数据损坏，这时无法使用重做日志恢复。（执行重做日志时需要利用页的一些变量，如checksum）

因此在使用重做日志恢复数据库，需要有一个页的副本，当发生写失效时，先通过页的副本还原该页，再进行重做。于是InnoDB实现了doublewrite技术。

doublewrite有两部分，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是磁盘共享表空间连续的128个页，也是2MB。
doublewrite要求刷新缓冲池的脏页时执行以下步骤

1. 通过memcpy函数将脏页复制到内存的doublewrite buffer
2. doublewrite buffer分两次，每次1MB顺序写入共享表空间
3. 调用fsync函数同步磁盘，避免缓冲写带来问题，确保数据刷新到共享表空间（顺序写，开销小）
4. 将上述的脏页数据写入各个表空间文件（离散写）

5.3.2.3什么是自适应哈希

InnoDB会监控对表上各索引页的查询执行情况，如发现建立哈希索引可以提升速度，则建立哈希索引，这是过程不需要用户干预。
参数：innodb_adaptive_hash_index，默认AHI为开启状态

5.3.2.4异步IO

InnoDB使用异步IO操作磁盘，避免同步IO导致阻塞，也可以进行IO Merge操作，将多个IO操作合并为一个IO操作。

5.3.2.5刷新邻接页

当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，一起刷新，这是可以通过AIO将多个IO写入操作合并为一个IO操作。
参数：innodb_flush_neighbors，控制开关

6 锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。即锁的作用是解决并发问题。

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

数据库锁出现的目的：处理并发问题

并发控制的主要采用的技术手段：乐观锁、悲观锁和时间戳。

从锁的粒度划分，可以将锁分为表锁、行锁以及页锁。

从使用性质划分，可以分为共享锁、排它锁以及更新锁。

从主观上划分，又可以分为乐观锁和悲观锁。

6.1 悲观锁（Pessimistic Lock）

传统的关系数据库里用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。

6.1.1 按使用性质划分

6.1.1.1共享锁（Share Lock）---读锁

S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。
性质
1. 多个事务可封锁同一个共享页；
2. 任何事务都不能修改该页；
3. 通常是该页被读取完毕，S锁立即被释放。

6.1.1.2排他锁（Exclusive Lock）---写锁

X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开了。）
性质
1. 仅允许一个事务封锁此页；
2. 其他任何事务必须等到X锁被释放才能对该页进行访问；
3. X锁一直到事务结束才能被释放。

6.1.1.3 更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

性质
1. 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；
2. 当被读取的页要被更新时，则升级为X锁；
3. U锁一直到事务结束时才能被释放。

因为当使用共享锁时，修改数据的操作分为两步：
1. 首先获得一个共享锁，读取数据，
2. 然后将共享锁升级为排他锁，再执行修改操作。
这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。
如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

6.1.2 按照作用范围划分

6.1.2.1行锁

锁的作用范围是行级别。

6.1.2.2表锁

锁的作用范围是整张表。

数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

举个例子，一个用户表user，有主键id和用户生日birthday当你使用update ... where id=?这样的语句数据库明确知道会影响哪一行，它就会使用行锁，当你使用update ... where birthday=?这样的的语句的时候因为事先不知道会影响哪些行就可能会使用表锁。

6.2 乐观锁（Optimistic Lock）

每次自己操作数据的时候认为没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现

对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，我们只要想办法解决极少量的更新操作的同步问题。换句话说，如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了额外的风险。

实现方式：

6.2.1 版本号

给数据增加一个版本标识，在数据库上的表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update ... where ... and version="old version"这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。

6.2.2 时间戳（timestamp）

时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

6.2.3 待更新字段

和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。

6.2.4 所有字段

和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

6.2.5 区别

新系统设计可以使用version方式和timestamp方式，需要增加字段，应用范围是整条数据，不论那个字段修改都会更新version,也就是说两个事务更新同一条记录的两个不相关字段也是互斥的，不能同步进行。旧系统不能修改数据库表结构的时候使用数据字段作为版本控制信息，不需要新增字段，待更新字段方式只要其他事务修改的字段和当前事务修改的字段没有重叠就可以同步进行，并发性更高。

ref  数据库事务与锁详解 - 简书

6.3 锁对比

6.3.1MyISAM和InnoDB存储引擎使用的锁

• MyISAM 采用表级锁(table-level locking)。
• InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁

6.3.2 表级锁和行级锁对比：

• 表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。

6.4 InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

ref https://www.cnblogs.com/shiwei1930/articles/11717070.html

6.5 并发控制会造成两种锁

并发控制会造成活锁和死锁，就像操作系统那样，会因为互相等待而导致。

6.5.1 活锁

定义：指的是T1封锁了数据R，T2同时也请求封锁数据R，T3也请求封锁数据R，当T1释放了锁之后，T3会锁住R，T4也请求封锁R，则T2就会一直等待下去。
解决方法：采用“先来先服务”策略可以避免。

6.5.2死锁

定义：就是我等你，你又等我，双方就会一直等待下去。比如：T1封锁了数据R1，正请求对R2封锁，而T2封住了R2,正请求封锁R1，这样就会导致死锁，死锁这种没有完全解决的方法，只能尽量预防。
预防方法：
1. 一次封锁法，指的是一次性把所需要的数据全部封锁住，但是这样会扩大了封锁的范围，降低系统的并发度；
2. 顺序封锁法，指的是事先对数据对象指定一个封锁顺序，要对数据进行封锁，只能按照规定的顺序来封锁，但是这个一般不大可能的。

6.5.3系统判定死锁的方法

超时法：如果某个事物的等待时间超过指定时限，则判定为出现死锁；
等待图法：如果事务等待图中出现了回路，则判断出现了死锁。
对于解决死锁的方法，只能是撤销一个处理死锁代价最小的事务，释放此事务持有的所有锁，同时对撤销的事务所执行的数据修改操作必须加以恢复。

ref https://www.cnblogs.com/ismallboy/p/5574006.html

6.6 MVCC

6.6.1 定义

MVCC 的英文全称是 Multiversion Concurrency Control ，中文意思是多版本并发控制技术。原理是，通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

6.6.2 解决什么问题

数据库并发场景有三种，分别为：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题：

• 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，读不相互阻塞，写不阻塞读，这样可以提升数据并发读写处理能力。
• 降低了死锁的概率，这个是因为 MVCC 采用了乐观锁的方式，读取数据时，不需要加锁，写操作，只需要锁定必要的行。
• 解决了一致性读的问题，当我们朝向某个数据库在时间点的快照是，只能看到这个时间点之前事务提交更新的结果，不能看到时间点之后事务提交的更新结果。
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

6.6.3 快照读和当前读

快照读，读取的是快照数据，不加锁的简单 Select 都属于快照读。

SELECT * FROM table WHERE ...

当前读，就是读的是最新数据,而不是历史的数据，加锁的 SELECT，或者对数据进行增删改都会进行当前读。

SELECT * FROM table_name LOCK IN SHARE MODE;--共享锁
SELECT FROM table_name FOR UPDATE;
INSERT INTO table_name values ...
DELETE FROM table_name WHERE ...
UPDATE table_name SET ...

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

6.6.4 实现原理

InnoDB 是如何存储记录多个版本的？这些数据是 事务版本号，行记录中的隐藏列和Undo Log。

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，InnoDB 是如何存储记录多个版本的？它的实现原理主要是依赖记录中的 3个隐式字段、undo日志、Read View 来实现的

ref MVCC 原理
ref MVCC详解

7 常见问题

7.1 超大分页或深度分页如何处理