MySQL面试题总结

2022-02-16 约 25665 字预计阅读 52 分钟

基础篇

执行一条SQL语句，期间发生了什么？

MySQL 的架构共分为两层：Server 层和存储引擎层，

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

第一步：连接器

使用MySQL服务之前，需要连接MySQL 服务，连接过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的。如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。

空闲连接会一直占用吗？

MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

MySQL的连接数限制？

MySQL 服务支持的最大连接数由 max_connections 参数控制。超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

怎么解决长连接占用内存的问题？

有如下两种解决方案：

定期断开长连接。

客户端主动重置连接。

第二步：查询缓存

MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。如果命中缓存则直接返回结果；如果没有命中缓存就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

注意：对于更新比较频繁的表，查询缓存的命中率很低，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。

第三步：解析器

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。

解析器会做如下两件事：

词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树。
语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

第四步：执行SQL

接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

prepare 阶段，也就是预处理阶段；
optimize 阶段，也就是优化阶段；
execute 阶段，也就是执行阶段；

预处理器

预处理阶段主要工作为：

检查 SQL 查询语句中的表或者字段是否存在

将 select * 中的 * 符号，扩展为表上的所有列

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

执行器

根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

MySQL一行记录是怎么存储的？

MySQL的存储行为由存储引擎实现，下面介绍InnoDB的情况。

MySQL数据存放在哪个文件？

我们每创建一个 database（数据库）都会在/var/lib/mysql/目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。该目录下主要有三类文件：

db.opt：用来存储当前数据库的默认字符集和字符校验规则。
表.frm：表结构会保存在这个文件。
表.idb：表数据会保存在这个文件。MySQL5.6.6开始，每一张表数据都会有一个单独的.idb文件。称之为独占表空间文件。

表空间文件结构

表空间由段（segment）、区（extent）、页（page）、行（row）组成。

行：数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

页：InnoDB 的数据是按「页」为单位来读写的，默认每个页大小为16kb，页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

区：因为InnoDB通过B+树来管理数据，而B+树的每一层都通过双向链表相连，为了让链表相邻的页物理空间也相邻。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。

段：段一般分为数据段、索引段和回滚段等。数据段存放B + 树的叶子节点的区的集合；索引段存放 B + 树的非叶子节点的区的集合；回滚段存放的是回滚数据的区的集合，用于事务多版本管理。

InnoDB的COMPACT行格式

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。Redundant行格式非紧凑型，已经弃用。Dynamic和Compressed行格式和Compact非常类似，下面讲解Compact行格式。

记录的额外信息

记录的额外信息主要包含三部分：变长字段长度列表、NULL值列表、记录头信息。

变长字段长度列表

对于varchar这样的变长数据类型，存储数据时也需要存储数据占用的长度。变长字段的真实数据占用的字节数会按照列的顺序逆序存放。当数据表中没有变长字段时，变长字段长度列表将会被省略。

为什么会按逆序存放？

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置。而「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

NULL值列表

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。值为1表示该列值为NULL；值为0表示该列值不为NULL。

注意：NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

当所有列都设置为NOT NULL的时候，行格式中就不会有NULL值列表了。

记录头信息

包含的信息较多，常见有：

delete_mask ：标识此条数据是否被删除。

next_record：下一条记录的位置。

record_type：表示当前记录的类型。

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer。

row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。

trx_id：事务id，表示这个数据是由哪个事务生成的。

roll_pointer：这条记录上一个版本的指针。

varchar(n)中n最大取值？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

Varchar(n)中的n是指最多存储的字符数量。对于ascii码字符集，一个字符占一个字节，对于utf-8，一个字符占三个字节。

单字段情况

一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和「NULL 值列表」所占用的字节数的。所以，我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。NULL值列表如果允许为空，那么需要消耗一个字节；变长字段长度列表，遵循如下规格：

条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；

条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；

因此消耗两个字节。

所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

在 UTF-8 字符集下，一个字符串最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

多字段情况

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

行溢出后，MySQL是如何处理的？

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，当一页不能存放记录时，就会发生行溢出，多的数据就会存到另外的「溢出页」中。

针对Compact行格式，会在真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

而对于Compressed 和 Dynamic 这两个行格式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页，而实际的数据都存储在溢出页中。

索引篇

什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

索引的分类

我们可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

按物理存储分类

从物理存储的角度来看，索引可以分为聚簇索引(又叫主键索引)和非聚簇索引(又叫二级索引、辅助索引)。

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

# 创建表时，创立主键索引方式
CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

# 建表时
CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

# 建表后
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

# 建表时
CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

# 建表后
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

# 建表时
CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

# 建表后
CREATE INDEX index_name
ON table_name(column_name(length));

B+树索引

InnoDB存储引擎默认使用B+树索引作为索引的数据结构。建表时，InnoDB存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列(UNIQUE)作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

例如创建一个(a, b, c)的联合索引，下列查询条件就可以匹配上联合索引：

where a = 1;

where b = 1 and a = 1;

注意：因为有查询优化器，所以字段在where语句中的顺序不重要。

下列几种查询，因为不符合最左匹配原则，所以无法匹配上联合索引：

where b = 1;

Where b = 1 and c = 2;

b 和 c是全局无序，局部有序的情况。

联合索引范围查询

在使用范围查询的时候，联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的。 于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围（b 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。

所以，这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

索引下推

我们知道，对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引区分度

建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数。

比如性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引进行排序

针对select * from order where status = 1 order by create_time ascSQL语句，怎样通过建立索引提高查询效率？

可以给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。因为status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

什么时候需要/不需要创建索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候需要创建索引？

字段有唯一性限制的，比如商品编码；

经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

表数据太少的时候，不需要创建索引；

经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

有什么索引优化方法？

常见的索引优化方法有：前缀索引优化，覆盖索引优化，主键索引最好是自增，防止索引失效。

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。 在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过前缀索引有一定的局限性：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

# 建表时创建前缀索引
CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

# 建表后创建前缀索引
CREATE INDEX index_name
ON table_name(column_name(length)); 

# 使用ALTER可以不用提供索引名，MYSQL会自动创建名称
ALTER TABLE table_name ADD index(column_name(length));

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作，减少了大量的I/O操作。

主键索引最好是自增的

如果我们使用自增主键，每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，可能会导致页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

自增的主键什么情况下会造成坏结果？

对于高并发工作负载，按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”，因为所有的插入都发生在这里，所以并发插入可能会导致间隙锁的竞争。

索引最好设置为NOT NULL

索引设置为NOT NULL，主要有以下两点原因：

第一个原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，如果允许存在NULL的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表。

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序的方式：或者按索引顺序扫描。

如果explain出来的type列值为index，就说明MySQL使用了索引扫描来做排序。

当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时， MySQL则可以使用索引来对结果进行排序。

ORDER BY子句要使用索引来进行排序，也必须满足最左匹配原则。但如果前导列为常量时，也可以使用索引列来进行排序。

例如：表中有索引(rental_date, inventory_id, customer_id)，下列查询语句可以用到索引进行排序：
SELECT rental_id, staff_id FROM table_1 
WHERE rental_date ='2005-05-05' 
ORDER BY inventory_id, customer_id;

防止索引失效

发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

为什么MySQL采用B+树作为索引？

二叉查找树

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，但是在极端情况下，二叉查找树会退化成链表。而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

自平衡二叉树

自平衡的二叉树在二叉查找树的基础上，增加了每个节点的左右子树的高度差不能超过1的限制。但由于依旧是二叉树，随着元素增加树的高度也会增加，意味着IO次数也会增加。

B树

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。并且如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

B+树

B+树与B树的差异主要有以下几点：

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引；

下面将比较B树与B+树的几个方面的性能：

单点查询

由于B树的非叶子结点也存放了数据，所以B树的单点查询性能波动较大，最快可以达到O(1)。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

插入删除效率

B+树有大量冗余节点，因此插入和删除的效率更高。

范围查询

因为 B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。

而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，会涉及多个节点的磁盘IO。

注意：InnoDB中的B+树，每一层节点都通过双向链表进行连接，这样的好处是既能向右遍历，也能向左遍历。

count(*)的性能以及如何优化？

哪种count()性能最好？

按照性能排序：count(*) = count(1) > count(主键字段) > count(字段)。

count()是一个聚合函数，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count(*)与count(1)的执行过程

由于InnoDB会以相同的处理方式处理count(*)和count(1)，所以两者性能上没有差别，当表有二级索引时，优化器会使用key_len 最小的二级索引进行扫描。当只有主键索引时，优化器会扫描主键索引。并且不会读取记录中的任何字段值。 因此效率最高。

count(主键字段)的执行过程

count(主键字段)执行时，如果有有二级索引，则会扫描二级索引，如果只有主键索引，会扫描主键索引，但是会读取主键字段的值用于判断是否为NULL，因此效率相对上面的情况要低一些。

count(字段)

count(字段)的执行效率最差，因为会执行全表扫描。

为什么要通过遍历的方式来统计？

在MyISAM存储引擎中执行count(*)(注意；如果加了where则一样要进行遍历)，执行的效率是O(1)，因为该存储引擎中维护了一个row_count的元信息。

在InnoDB中，由于存储引擎支持事务，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

如何优化count(*)?

近似值

如果业务对于统计个数不需要很精确，可以使用 show table status 或者 explain 命令来表进行估算。

额外的表保存计数值

如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的一张计数表中。在新增和删除操作时，我们需要额外维护这个计数表。

索引合并优化

当单表查询中，使用到了多个索引，优化器则用到了索引合并优化(Index merge)。 即一次查询语句中，MySQL用到了多个索引并将其求交集或者并集。在使用EXPLAIN时，在type那一列会显示index_merge。Extra字段会显示：Using intersect (...) Using union(...) Using sort_union(...)。

索引合并优化算法有以下的限制：

对于复杂的where子语句，包含了很深的and/or 嵌套，那么不会使用到索引合并技术。

不适用于全文索引

具体包含如下算法：

Index Merge Intersection access (索引合并交集访问算法)
Index Merge Union access（索引合并并集访问算法）
Index Merge Sort-Union access (索引合并排序并集访问算法)

索引合并交集访问算法

它的工作流程是：对于每一个使用到的索引进行查询，查询主键值集合，然后进行合并，求交集，也就是and运算。

会用到该算法必须满足以下两个条件：

二级索引是等值查询；如果是组合索引，组合索引的每一位都必须覆盖到，不能只是部分

InnoBD表上的主键范围查询

# 主键可以使用范围查询，二级索引只能使用等值查询
SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

# 没有主键的情况
SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

为什么二级索引必须要求等值查询？

原因在于后续的求交集算法过程：

针对两个升序排序的数组，进行归并：逐个取出两个数组中的最小的值，如果相等，就放入结果集，否则将较小的数指针向后移动。时间复杂度O(N)。

因此如果二级索引是范围查询，那么查询出来的主键是无序的。

索引合并并集访问算法

执行流程和求交集算法类似，使用OR连接，求并集。

用到该算法必须满足如下三个条件：

二级索引是等值查询；如果是组合索引，组合索引的每一位都必须覆盖到，不能只是部分

InnoBD表上的主键范围查询

符合 index merge intersect 的条件

# 无主键，OR连接
SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

# 既有AND,也有OR
SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

求并集的算法流程：

两个有序的升序数组，从两个数组中取出最小的数，然后比较，如果相等并且等于目前最小值，就放入结果集中，向后移动两个指针；否则，将小的数添加到结果集中。

索引合并排序并集访问算法

交集、并集访问算法对二级索引的要求比较严格，更多的时候二级索引也采用了范围查询，因此有了该算法。该算法的流程是：根据索引查询得到主键集合，对于每个主键集合进行排序，然后求并集。 这样的好处是扩展了使用条件，增加了使用的范围；缺点就是消耗更大了。

必要条件:

二级索引不必等值查询，联合索引也不必匹配所有的索引项。

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

事务篇

事务的概念

事务是由 MySQL 的引擎来实现的，我们常见的 InnoDB 引擎它是支持事务的。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性

事务必须遵循如下四个特性：

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB引擎通过什么技术来保证事务的四个特性呢？

持久性是通过 redo log （重做日志）来保证的；

原子性是通过 undo log（回滚日志）来保证的；

隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；

一致性则是通过持久性+原子性+隔离性来保证；

事务的隔离级别是如何实现的？

并发事务会出现什么问题？

MySQL在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

事务的隔离级别

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；在该级别下，可能发生脏读、不可重复读和幻读。
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；在该级别下，不可能发生脏读现象，但可能会发生不可重复读和幻读。
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；在该隔离级别下，不可能发生不可重复读和脏读，但是可能发生幻读。
串行化（serializable ）：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；在该隔离级别下，脏读、不可重复读和幻读都不可能会发生。

注意：MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了)，解决的方案有如下两种:

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

四种隔离级别是如何实现的？

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；

对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

Read view 在MVCC中如何工作的？

Read view有如下四个重要的字段：

m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

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（多版本并发控制）。

MySQL可重复读隔离级别下，完全解决了幻读现象吗？

快照读是如何避免幻读的？

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

当前读是如何避免幻读的？

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。

事务 A 执行了上面这条当前读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。

幻读被完全解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

场景一：

场景二：

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。

T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；

T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

锁篇

MySQL有哪些锁

MySQL中，根据加锁的范围，可以分为全局锁、表级锁和行锁。

全局锁

使用全局锁后，整个数据库就处于只读状态了，其他线程对数据的增删改操作和对表结构的更改操作都会被阻塞。

# 使用全局锁
flush tables with read lock
# 释放全局锁
unlock tables

全局锁的缺点

加上全局锁，意味着整个数据库都是只读状态。会造成业务停滞。

全局锁的应用场景

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

备份数据库时，使用全局锁会影响业务，有什么方式可以避免？

如果数据库的引擎支持的事务支持可重复读的隔离级别，例如：InnoDB。那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表级锁

MySQL中的表级锁有：表锁、元数据锁(MDL)、意向锁和AUTO-INC锁。

表锁

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。意味着如果本线程对表加了「共享表锁」，接下来的读操作也会被阻塞。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

# 表级别的共享锁，也就是读锁；
lock tables t_student read;
# 表级别的独占锁，也就是写锁；
lock tables t_student write;
# 释放锁
unlock tables;

元数据锁(MDL)

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；

对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

意向锁

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」。意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

意向锁的目的是为了快速判断表里是否有记录被加锁。

因为表锁和行锁是满足读读共享、读写互斥、写写互斥的。如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

注意：普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

不过select 也是可以对记录加共享锁和独占锁的，具体方式如下：
# 先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
# 先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

AUTO-INC锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，在插入数据时，会加一个表级别的AUTO-INC锁，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

MySQL5.1.22开始，InnoDB提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB提供了innodb_autoinc_lock_mode系统变量，用于控制是选择AUTO-INC锁还是轻量级锁。

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；

当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

注意：当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。因此此时binlog的日志格式必须使用row。

当 innodb_autoinc_lock_mode = 1：

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。行级锁主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock 临键锁，Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

注意：普通的select语句是快照读，不会加锁，如果要在查询时对记录加上锁，可以通过以下两种方式：
# 对读取的记录加共享锁
select ... lock in share mode;

/# 对读取的记录加独占锁
select ... for update;

MySQL是怎么加锁的？

因为InnoDB存储引擎只是行级锁，所以如下讨论都是针对InnoDB存储引擎的。

哪些语句会加行级锁？

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

下面的语句则会加行级锁：

# 对读取的记录加共享锁(S型锁)
select ... lock in share mode;

# 对读取的记录加独占锁(X型锁)
select ... for update;

# 对操作的记录加独占锁(X型锁)
update table .... where id = 1;

# 对操作的记录加独占锁(X型锁)
delete from table where id = 1;

MySQL是怎么加行级锁的？

注意：对于update、delete、select ...for update等具有加锁性质的语句，如果没有索引列作为查询条件或者查询语句没有走索引，将会导致全表扫描，会对每一个记录加上next-key锁，相当于锁住了全表。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是在某些场景下next-key lock会退化成记录锁或间隙锁。总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成退化成记录锁或间隙锁。

唯一索引等值查询：

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。例如：查询id=2，如果不存在，会锁住区间(1,5)，id=1和id=5是前后两条记录。

非唯一索引等值查询：

当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。

非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

update没加索引会锁全表

为什么会出现这种情况？

InnoDB 存储引擎的默认事务隔离级别是「可重复读」，但是在这个隔离级别下，在多个事务并发的时候，会出现幻读的问题，为了避免幻读问题，InnoDB使用了next-key lock(临键锁)，当我们使用update、delete、select ... for update等具有加锁性质的语句时，如果where条件没有使用到索引，则**会进行全表扫描，导致整个表都被锁住。**而这期间除了 select ... from语句，其他语句都会被锁住不能执行。

如何避免这种事故?

可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。在这种情况下：

update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；

使用 limit；

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

MySQL死锁情况

Insert语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。这是一种乐观锁机制，目的是减少加锁数量，提高系统性能。只有在特殊情况下，才会将隐式锁转换为显示锁。如下两种场景：

场景一：记录之间加有间隙锁

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态，现象就是 Insert 语句会被阻塞。

场景二：遇到唯一键冲突

如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录，此时插入就会失败，然后对于这条记录加上了 S 型的锁。

注意：此处加上S型的锁是指两个事务中，插入成功的事务加上S型的锁，目的是防止另外一个事务也能插入相同的记录。

死锁发生场景分析

一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引，有如下记录：

有如下两个事务，执行过程如下：

死锁产生情况分析：

由于两个事务在插入数据之前都做了幂等性校验，即先查询记录是否存在。这个过程都会给锁住(1006,+∞]范围。而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

注意：插入意向锁与间隙锁是冲突的，而间隙锁与间隙锁之间可以相互兼容，这就是产生死锁的条件。

间隙锁与间隙锁之间可以相互兼容，但是临键锁和临键锁之间、记录锁和记录锁之间不相互兼容。上述场景中，对于范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

日志篇

MySQL有三种重要的日志，在对数据库进行增删改的过程中，会涉及这三种日志：

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

为什么需要undo log?

我们执行一条“增删改”语句时，MySQL 会隐式开启事务，如果在事务提交之前，MySQL发生了崩溃，我们要怎么回滚到事务执行之前呢？

实现这一机制就是 undo log（回滚日志），它保证了事务的「ACID 特性」中的原子性（Atomicity）。对记录的每一次修改都会通过roll_pointer 指针串成一个链表，这个链表就被称为版本链。

因此undo log有两大作用：

实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。

实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

为什么需要redo log?

MySQL通过Buffer Pool提高了读写效率，但是Buffer Pool是内存，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失，因此使用了redo log。redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

有了redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。

产生的 redo log 是直接写入磁盘的吗？

redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘。

因此redo log有两大作用：

实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

redo log刷盘时机

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 参数控制）。

由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。InnoDB后台线程每隔1秒，会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;

当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。

当设置该参数为 2 时，表示每次事务提交时，都只是将缓存在 redo log buffer 里的 redo log 写到操作系统的文件系统的Page Cache中。InnoDB 的后台线程每隔 1 秒调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

Redo log文件写满了怎么办

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫：ib_logfile0 和 ib_logfile1 。重做日志文件组是以循环写的方式工作的，所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置。 一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

为什么需要 bin log?

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志。

bin log与redo log的区别

适用对象不同

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

文件格式不同

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
- ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
- MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

写入方式不同

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

由于 redo log 文件是循环写，因此不可以用于恢复数据，而binlog 文件保存的是全量的日志，可以用于恢复整个数据库删除的情况。

用途不同

binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。

主从复制如何实现的？

MySQL 的主从复制依赖于 binlog ，这个过程一般是异步的，详细过程如下：

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。

从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。

从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

MySQL主从复制的模型有哪些？

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。性能差，可用性也很差，基本没有使用过。

异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。

半同步复制：事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

两阶段提交

为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

例如，一条更新语句UPDATE t_user SET name = 'xiaolin' WHERE id = 1;，如果出现半成功状态，就会有如下情况：

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。就可能造成主从不一致；同理也会出现主从不一致情况。

MySQL为了避免两个日志之间的逻辑不一致情况，使用「两阶段提交」来解决。

两阶段提交过程

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」。然后将redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；

commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

两阶段提交过程异常状况分析

发生崩溃主要在两个时刻：一是redo log写入磁盘，binlog还没写入；一是两个都写入，还没有写入commit标志。这两个时刻发生崩溃redo log都是prepare阶段。

MySQL重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就会将redo log 中的 XID(事务ID) 去 binlog 查看是否存在此 XID：

如果 binlog 中没有当前事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。

如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。

两阶段提交有什么问题？

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：

磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

组提交

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数。

MySQL磁盘I/O很高，有什么优化方法？

事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过控制参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率。

内存篇

为什么要有Buffer pool?

MySQL的数据是存储在磁盘里的，如果每次都从磁盘里读取数据，这样性能将会很差。因此，InnoDB存储引擎设计了缓存池(Buffer Pool)，来提高数据库的读写性能。

有了Buffer Pool之后，

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

Buffer Pool 缓存什么？

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

如何管理Buffer Pool?

管理空闲页

为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。

管理脏页

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

提供缓存命中率

MySQL使用改进的LRU算法来提高Buffer Pool中缓存页的缓存命中率。

传统的LRU算法无法避免如下两个问题

预读失效

Buffer Pool 污染

什么是预读失效？

MySQL存在预读机制，即在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。

但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。如果使用简单的LRU算法，这些预读的页会放到LRU链表的头部，但是可能一直不被访问到，会大大降低缓存命中率。

解决方案

将 LRU 划分了 2 个区域：old 区域和 young 区域。预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。

什么是Buffer Pool污染？

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

解决方案

提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

MySQL将进入到 young 区域条件增加了一个停留在 old 区域的时间判断。在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；

如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。

脏页刷盘时机

下面几种情况会触发脏页的刷新：

当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

高可用

怎么解决读写分离的主备延迟问题？

读写分离的主要目标是分担主库的压力。

上述结构是客户端(client)主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说，由客户端来选择后端数据库进行查询。

还有一种架构是，在 MySQL 和客户端之间有一个中间代理层 proxy，客户端只连接 proxy，由 proxy 根据请求类型和上下文决定请求的分发路由。

无论哪种架构，都有可能出现主从延迟的问题，常见的解决方案有如下几种：

强制走主库方案;
sleep 方案;
判断主备无延迟方案;
配合 semi-sync 方案;
等主库位点方案;
等 GTID 方案。

强走主库方案

强制走主库方案其实就是，将查询请求做分类。大致可以将请求分为两类：

对于必须要拿到最新结果的请求，强制将其发到主库上。

对于可以读到旧数据的请求，才将其发到从库上。

该方案的缺陷就是：对于碰到“所有查询都不能是过期读”的需求，比如一些金融类的业务。这样的话，你就要放弃读写分离。

Sleep方案

这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。

例如卖家发布商品，商品发布后，用 Ajax直接把客户端输入的内容作为“新的商品”显示在页面上，而不是真正地去数据库做查询。

等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了 sleep 的目的。

该方案的缺陷就是Sleep多长时间不精确，并且用户体验不好。

判断主备无延迟方案

在执行从库查询前，先判断主备是否有延迟，没有延迟再进行查询。

判断主备延迟的方法：

通过show slave status命令结果中的seconds_behind_master参数的值判断。但是该参数单位是秒；

对比位点确保主备无延迟，通过对比主库最新的位点和从库执行的位点是否相同判断是否有延迟。

对比GTID集合确保主备无延迟。

GTID全称是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。由两部分构成：GTID = server_uuid:gno，

server_uuid是一个实例第一次启动时自动生成的，是一个全局唯一的值

gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。注意：这个事务ID和回滚事务的ID不同，这里的事务ID只有事务提交后才会自动分配。

开启GTID模式下，每一个事务都会和一个GTID一一对应，每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

该方案仍可能出现问题，在主库执行完事务客户端收到确认后，而备库还没有收到binlog日志的时候，也会有不一致问题。

配合semi-sync

Semi-sync就是半同步复制，在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。

但是判断主备无延迟配合半同步方案仍然有如下缺陷：

在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。
在持续延迟的情况下，可能出现过度等待的问题。

等主库位点方案

执行流程如下：

trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position;
选定一个从库执行查询语句;
在从库上执行 select master_pos_wait(File, Position, 1);
如果返回值是 >=0 的正整数，则在这个从库执行查询语句;
否则，到主库执行查询语句。

GTID方案

事务执行完成后，会把这个GTID返回给客户端，因此相比等主库位点方案少了一次查询操作，流程如下：

trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1;
选定一个从库执行查询语句;
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
如果返回值是 0，则在这个从库执行查询语句;
否则，到主库执行查询语句。