HelloCH

主题

MySQL

MySQL 介绍 MySQL ​

  • 安装
  • 配置
  • sql语法

MySQL索引 MySQL Index ​

1. B+树（索引数据结构） ​

什么是索引？ 为什么索引能加快查询？ 索引的数据结构是什么？ B+ 树 和（B 树 和 红黑树）有什么区别？ 为什么选择 B+树 作为索引数据结构？

为什么Mysql InnoDB选择B+ Tree作为索引？ ​

  1. B+ 树 vs B 树 B+ 树只在叶子节点存储数据，B树的非叶子节点也要存储数据，所以B+ 树的单个节点的数据量更小
  2. B+ 树 vs 二叉树 对于有N个叶子节点的B+ 树，搜索复制度为O（logdn）
  3. B+ 树 vs Hash

08 索引:排序的艺术

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

2. 索引组织表（索引存储） ​

堆表和索引组织表有什么区别？

分别应用场景是什么？

Mysql InnoDB存储引擎中数据存储方式：索引组织表

数据存储有堆表和索引组织表两种。

  • 堆表中的数据是无序存放的，数据的排序完全依赖索引

  • 索引组织表，数据根据主键进行排序存放在索引中，主键索引也叫聚集索引（Clustered Index）

在索引组织表中，数据即索引，索引即数据

二级索引 ​

InnoDB存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其它的索引都称为二级索引（Secondeary Index），或者非聚集索引

二级索引也是一颗B+树索引，但是它和主键索引不同的是叶子节点存放的是索引键值、主键值

通过二级索引idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终结果。

这种二级索引通过主键索引进行再一次查询的操作叫做“回表”

这样的二级索引设计的好处：若记录发生了修改，则其它索引无须进行维护，除非记录的主键发生了修改

在索引组织表中，万物皆索引，索引就是数据，数据就是索引。

二级索引的性能评估 ​

  • 要比较顺序，对聚集索引性能友好

  • 尽可能紧凑，对二级索引的性能和存储友好

函数索引（先了解） ​

...

09 索引组织表:万物皆索引

3.组合索引（联合索引） ​

联合索引的结构是什么？

如果利用联合索引提升查询性能

组合索引（Compound Index）是指由多个列所组合而成的B+树索引

组合索引既可以是主键索引，也可以是二级索引，只是排序的键值从1个变成了多个，本质还是一棵B+树索引

索引覆盖 ​

目的是为了避免回表，由于二级组合索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则可以直接返回结果，无需回表。

这种组合索引避免回表的优化手段称为索引覆盖（Covering Index）

10 组合索引:用好，性能提升 10 倍!

MySQL夜市8月25日（联合索引）

4.索引失效 ​

有哪些索引失效的场景？

为什么会失效？

前提：索引可以提高语句查询速度，但是索引并不是万能的，建立了索引，并不意味着任何查询语句都能走索引扫描

索引存储结构长什么样？ ​

MySQL默认的存储引擎是InnoDB，采用的是B+树作为索引的数据结构。在建表的时候，InnoDB存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引

失效情况 ​

A. 对索引使用左或者左右模糊匹配 ​

索引B+树是按照索引值有序存储的，只能根据前缀进行比较

B. 对索引使用函数 ​

索引保存的是索引字段的原始值，而不是经过函数计算后的值

C. 对索引进行表达式计算 ​

原因与索引使用函数差不多，进行了表达式计算后得到的值不是原本的值，无法走索引

D. 对索引隐式类型转换 ​

Mysql的类型转换规则：

  • 字符串 --> 数字，就相当于 数字比较

  • 数字 --> 字符串，就是字符串比较

小总结：在Mysql中，遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较

E. 联合索引非最左匹配 ​

F. Where 子句中的OR ​

在WHERE子句中，如果在OR前的条件列是索引列，而OR后面不是索引列，那么索引会失效

索引失效有哪些？

B+树里面的节点存放的是什么？查询数据的结果又是怎样的？

5.索引选择 ​

Mysql数据库中的优化器是怎么执行的？

根据什么标准选择索引？

MySQL是如何选择所索引的？ ​

在关系型数据库中，B+树索引只是存储的一种数据结构，具体使用还需要依赖数据库的优化器，优化器决定了具体某一索引的选择

而优化器的选择是基于成本（cost），哪个索引的成本越低，优先选择哪个索引

C
Cost = Server Cost + Engine Cost
     = CPU Cost + IO Cost

先看MySQL数据库的结构，MySQL由Server层和Engine层组成：

  • Server层有SQL分析器、SQL优化器、SQL执行器，用于负责SQL语句的具体执行过程

  • Engine层负责存储具体的数据，常使用InnoDB存储引擎，还有用于内存中存储临时结果集的TempTable引擎

MySQL索引出错：

  1. 未使用创建的索引

  2. 索引创建在有限状态上

11 索引出错:请理解 CBO 的工作原理

6.索引应用 ​

建立索引有什么优点和缺点？

如何正确使用索引？

哪些场景下适合建立索引？

哪些场景下不适合建立索引？

总结 ​

  1. B+树索引

    1. 索引的加快查询的一种数据结构，其原理是插入时对数据排序，缺点是会影响插入的性能

    2. Mysql当前支持B+树索引、全文索引、R树索引

    3. B+树索引的高度通常为3~4层，高度为4的B+树可以存放50亿左右的数据

    4. 由于B+树的高度不高，查询效率高，50亿的数据也只需插叙4次I/O

    5. Mysql单表的索引没有个数限制，业务查询需要，创建即可

    6. 可以通过表sys.schema_unused_indexes和索引不可见特性，删除无用的索引

    7. Mysql采用B+树索引？从数据结构、磁盘I/O操作次数出发

  2. 索引组织表

Mysql InnoDB存储引擎是索引组织表，以及索引组织表和堆表之间的区别：

  • 索引组织表主键是聚集索引，索引的叶子节点存放表中一整行完整记录

  • 除主键索引外的索引都是二级索引，索引的叶子节点存放的是（键值，主键值）

  • 由于二级索引不存放完整记录，因此需要通过主键值再进行一次回表才能定位到完整数据

  • 索引组织表对比堆表，在海量并发的OLTP业务中能有更好的性能表现

  • 每种不同数据，对二级索引的性能开销影响不一样

  • 有时通过函数索引可以更快解决线上SQL的性能问题

  • 虚拟列不占用实际存储空间，在虚拟类上创建索引本质就是函数索引

  1. 组合索引

    组合索引也是一颗B+树，只是索引的列由多个组成，组合索引既可以是主键索引，也可以是二级索引

    组合索引的三大优势

    1. 覆盖多个查询条件，如（a，b）索引可以覆盖查询 a = ? 或者 a = ? and b = ?

    2. 避免SQL的额外排序，提高SQL性能，如WHERE a = ? OR ORDER BY b 这样的查询条件

    3. 利用组合索引包含多个列的特性，可以利用索引覆盖技术，提高SQL的查询性能，用好索引覆盖技术，性能提升10倍不是难事

  2. 索引失效

    6种会发生索引失效的情况：

    1. 使用左或者左右模糊匹配的时候，也就是 like %xx 或者like %xx% 这两种方式，都会造成索引失效

    2. 当我们查询条件中对所有列使用函数，会导致索引失效

    3. 在查询条件中对所有列进行表达式运算，会导致索引失效

    4. MySQL遇到字符串和数字比较的时候，会自动把字符串转为数字，再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么所有列会发生隐式类型转换，由于隐式类型转换是通过CAST函数实现的，等于对索引列使用了函数，所以导致索引失效

    5. 联合索引要能正确使用遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则索引会失效

    6. 在WHERE子句中，如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，那么所有会失效

  3. 索引选择

MySQL优化器是CBO，是一种基于成本的优化器。会判断每个索引的执行成本，从中选择出最优的执行计划

  • MySQL优化器是CBO（Cost-based Optimizer）的

  • MySQL会选择成本最低的执行计划，可以通过explain命令查看每个SQL的成本

  • 一般只对高选择度的字段和字段组合起来建立索引，选择度低的字段如性别，不建议建立索引

  • 若数据存在倾斜，可以创建直方图，让优化器知道索引中的数据的分布，进一步校准执行计划

面试题： ​

1.为什么InnoDB选择B+Tree作为索引的数据结构？ ​

  1. B+树 vs B树

    1. 存储相同数据量级下，B+树高比B树低，磁盘I/O次数更少

    2. B+树叶子节点用双向链表串起来，适合范围查询，B树无法做到这点

  2. B+树 vs 二叉树

    1. 随着数据量的增加，二叉树的树高会越来越高，磁盘I/O次数也会更多，B+树在千万级别的数据量下，高度依然维持在3~4层左右

  3. B+树 vs Hash

    1. 虽然Hash的等值查询效率高，但是无法做到范围查询

2.什么时候适用索引？ ​

  • 字段有唯一限制性

  • 经常用于WHERE查询条件

  • 经常用于GROUP BY 和 ORDER BY的字段

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

  • WHERE条件，GROUP BY，ORDER BY里用不到的字段

  • 字段中存在大量重复数据

  • 表数据太少

  • 经常需要更新的字段

4.什么时候索引会失效？ ​

  • 左或左右模糊匹配

  • 在查询条件中对索引列做了计算、函数、类型转换等操作

  • 联合索引要正确遵循最左匹配原则

  • 在WHRER子句中，如果在OR前的条件是索引列而OR后的条件列不是索引列

  • 为了更好使用索引，索引列要设置为NOT NULL

5.有什么优化索引的方法？ ​

回答：

  • 前缀索引优化

  • 覆盖索引优化

  • 主键索引最好是自增的

  • 防止索引失效

  • #MySQL

MySQL事务 MySQL Transaction ​

1.事务有哪些特征 ​

原子性，隔离性，一致性，持久性

  • 原子性：要么全做，要么全不做

  • 隔离性：保证其它的状态转换不会影响到本次状态的转

  • 一致性：数据全部符合现实世界的约束

  • 持久性： 更新后的数据存储到磁盘

InnoDB引擎通过以下技术来保证事务的四个特性

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

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

  3. 隔离性是通过 mvcc（多版本并发控制）或者锁机制来保证

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

2.并发事务会引发的问题 ​

MySQL服务端是允许多个客户端连接，这意味着MySQL会出现同时处理多个事务的情况

在同时处理多个事务的时候，可能会出现脏读、不可重复读、幻读的问题

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

  • 不可重复读：在一个事务中多次读取同一个数据，出现前后两次读到的数据不一样的情况

  • 幻读：在一个事务中多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数据不一样的情况

以上三个现象，问题的严重性是 脏读 > 不可重复读 > 幻读

3.事务的隔离级别 ​

四种隔离级别：

  1. 读未提交：指一个事务还没有提交时，它做的变更就能被其他事务看到

  2. 读提交：指一个事务提交之后，它做的变更才能被其他事务看到

  3. 可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB引擎的默认隔离级别

  4. 串行化：对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生读写冲突的时候，后访问的事务必须等前一个事务执行完成

按隔离水平高低排序如下：

串行化 > 可重复读 > 读已提交 > 读未提交

针对不同的隔离级别：并发事务时可能发生的现象也不同

  • 读未提交：脏读、不可重复读、幻读

  • 读提交：不可重复读、幻读

  • 可重复读：幻读

  • 串行化：

可重复读的隔离级别下，可以很大程度上避免幻读现象的发生，所以MySQL不使用串行化隔离级别来避免幻读现象的发生，因为串行化隔离级别会影响性能

InnoDB在默认隔离级别：可重复读的情况下很大程度上解决幻读现象的解决方案有两种：

  • 针对**快照读（普通 select 语句），**是通过MVCC方式解决幻读

  • 针对**当前读（select ... for update），**通过next-key lock（记录锁+间隙锁）方式解决了幻读

四种隔离事务是怎么实现的

  • 对于读未提交：可以读到未提交事务修改的数据，所以直接读取就行

  • 对于串行化，通过加读写锁的方式来避免并行访问

  • 对于读提交和可重复读这两种隔离级别的事务，是通过Read View来实现的，它们的区别是在于创建Read View时，读提交隔离级别是在每个语句执行之前都会重新生成一个Read View；而可重复读隔离级别是启动事务时生成一个Read View，然后整个事务都在用这个Read View

在执行开启事务命令，并不意味着启动了事务：

在MySQL中，开启事务有两种命令，分别是：

  • begin/start transaction，执行命令后，并不意味着事务启动，只有执行了第一条select语句，才是事务真正启动的时机

  • start transaction with consistent snapshot，马上启动事务

Read View在MVCC中是如何工作的？ ​

Read View的四个字段

Plain
<creator_trx_id> <m_ids> <min_trx_id> <max_trx_id>

  • creator_trx_id:创建该Read View的事务的事务id

  • m_ids:指创建Read View时，当前数据库中活跃事务的事务id列表

  • min_trx_id:生成ReadView时系统中活跃的事务中最小的事务id，即m_ids中的最小的事务id，也是表示活跃的事务最早的那个

  • max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值

这里还需要了解聚簇索引记录中的两个隐藏列，trx_id和roll_pointer

  • trx_id，当一个事务对某条聚簇索引进行改动时，会把该事务的事务id记录在trx_id隐藏列中

  • roll_pointer，这个隐藏列是指针，指向每一个旧版本记录

有了ReadView，以及undo log，在访问某条记录的时，按照以下步骤进行判断：

  1. trx_id == creator_trx_id ，意味着当前事务在访问自己修改过的记录，可以访问

  2. trx_id < min_trx_id ，表明生成该版本的事务在当前事务生成Read View前已经提交，可以访问

  3. trx_id > max_trx_id ，表明生成该版本的事务在当前事务生成Read View后才开启，不可以访问

  4. min_trx_id < trx_id < max_trx_ix ，还需要再进一步判断

    1. trx_id 存在 m_ids 中，说明创建Read View时生成该版本的事务还是活跃的，不可以访问

    2. trx_id 不在 m_ids 中，说明创建Read View时生成该版本的事务已经提交了，可以访问

Read COMMITTD、REPETABLE READ这两种隔离级别的一个很大不同：生成ReadView的时机不同，REAED COMMITTD在每一次进行普通select操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView，不会再生成一个新的ReadView

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

可重复读是如何工作的？ ​

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

如果某个事物开启后，去读取记录，发现记录的trx_id 比自己事物id小且在活跃的事物id列表里面有该事务id，那么该事务不会读取该版本的记录，而是沿着undo log链条往下找旧版本的记录，直到找到trx_id比事务b小的min_trx_id值的第一条记录

读提交是如何工作的？ ​

读提交隔离事件在每次读取数据的时候，都会生成一个新的Read View

MySQL锁 MySQL Lock ​

锁的类型 ​

Mysql的锁，根据加锁的范围可以分为全局锁、表级锁和行锁三类

全局锁 ​

要使用全局锁，执行下面这条命令：

C
flush tables with read lock

执行之后，整个数据库就处于只读状态，这时其他线程执行以下操作，就会被阻塞

  • 对数据的增删改，比如insert、delete、update等

  • 对表结构的更改操作，比如alter table、drop table等

要释放全局锁，执行下面的命令：

C
unlock tables

全局锁的应用场景：

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

加全局锁带来的缺点：会导致业务停滞，因为加全局锁之后，整个数据库都只是只读状态，不能更新数据

可以通过开启事务，在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的Read View，

备份数据库的工具是mysqldump ，在使用mysqldump时加上-single-transaction 参数的时候，就会在备份数据库之前开启事务

表级锁 ​

MySQL里面表级锁有以下几种：

  • 表锁

  • 元数据锁（MDL）

  • 意向锁

  • AUTO-INC锁

表锁 ​

使用下面的命令对表加锁和释放锁

C
// 加读锁
lock tables <table_name> read;
// 写锁
lock tables <table_name> write;

// 释放锁
unlock tables;

表锁会影响别的线程和本线程的读写操作

元数据锁（MDL） ​

对于MDL，我们不需要显示使用，因为当我们在对数据库进行操作时，会自动给这个表上加MDL：

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

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

MDL是为了保证当前用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更

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

需要注意的是，在事务启用之后，如果事务A没有提交，此时如果有表结构的修改请求发起，就会发生阻塞，这个阻塞也会导致其他CURD的请求被阻塞住

这是因为申请MDL锁的操作会形成一个队列，队列中写锁获取优先级大于读锁，一旦出现MDL写锁等待，会阻塞该表后续的CRUD操作

意向锁 ​

  • 在使用InnoDB引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁

  • 在使用InnoDB引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁

在执行insert、update、delete操作时，需要先对表上加 意向独占锁，然后对该记录加独占锁

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

C
// select也是可以对记录加共享锁和独占锁，

// 先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
// 先表上加上意向锁，然后再读取记录加独占锁
select ... for update

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

AUTO-INC锁 ​

表里面的主键通常设置成自增的，在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过AUTO-INC锁实现的

Auto-Inc锁是特殊的表锁机制，不是在一个事务提交后才释放，而是再执行完插入语句后就会立即释放

行级锁 ​

InnoDB引擎是支持行级锁的，而MyISAM引擎并不支持行级锁

行级锁的类型主要有三类：

  • Record Lock，记录锁，也就是仅仅一条记录锁上

  • Gap Lock，间隙锁，锁定一个范围，但不包含记录本身

  • Next-key Lock，Rocord Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身

Record Lock 记录锁 ​

Record Lock称为记录锁，锁住的是一条记录。而且记录锁也有s锁和x锁之分

Gap Lock 间隙锁 ​

Gap Lock称为i而间隙锁，只存在于可重复隔离级别，目的是为了解决可重复读级别下幻读的现象

间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不会存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出

Next-key ​

Next-key Lock成为临键锁，是Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身

Next-key Lock是包含间隙锁+记录锁，如果一个事务获取了X型的next-key lock，那么另外一个事务在获取相同范围的X型的next-key lock时，是会被阻塞的

插入意向锁 ​

在一个事务插入一条记录的时候，需要判断插入的位置释放已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有，插入操作就会阻塞，直到间隙锁被释放，在此期间会生成一个插入意向锁，表明有事务想在某区域插入新记录，但是处于等待状态

MySQL加锁 ​

InnoDB引擎是支持行级锁，而MyISAM是不支持行级锁，了解Mysql是怎么加行级锁，其实也是说InnoDB引擎是怎么加锁的。

普通select语句是不会对记录加锁（除了串行化隔离级别），因为它属于快照读，是通过MVCC（多版本并发控制）实现的

对查询时对记录加行级锁，可以使用下面两种方式，这两种查询会加锁的语句叫做锁定读

C
// s lock
select ... lock in share mode;
// x lock
select ... for update;

上面两个语句必须在事务中，因为当事务提交了，锁就释放。

除了上面两条锁定读语句会加行级锁之外，update和delete操作都会加行级锁，且锁定类型都是独占锁(X)

C
update table ...
delete from table ...

x型锁和s型锁之间的兼容型未读读共享，读写互斥

XS
X不兼容不兼容
S不兼容兼容

  • 行级锁

    • 读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅一条记录锁上

    • 可重复读隔离级别下，行级锁的种类除了记录锁，还有间隙锁（目前是为了避免幻读

在执行commit后，事务过程中生成的锁都会被释放

MySQL是怎么加行级锁的？ ​

行级锁加锁规则复杂，目前仅保留了解程度

MySQL 是怎么加锁的？

总结1：在能够使用记录锁或者间隙锁就能避免幻读的现象的场景下，next-key lock就会退化成为记录锁或间隙锁

Mysql死锁 ​

使用引擎为InnoDB，隔离级别为可重复读（RR）

死锁的发生 ​

有表如下：

SQL
create table `t_order` (
    `id` int not null auto_increment,
    `order_no` int default null,
    `create_date` datetime default null,
    primary key(`id`),
    key `index_order` (`order_no`) using btree
) engine = InnoDB;

有两个事务，一个事务要插入订单1007，另外一个事务也要插入订单1008，因为需要对订单做幂等性校验，所以两个事务先要查询订单是否存在，不存在才插入记录

这里两个事务都陷入阻塞（前提是没有打开死锁检测），也就是发生了死锁，都在互相等待对方释放锁

死锁的产生 ​

可重复隔离级别下，是存在幻读的问题

InnoDB引擎为了解决可重复读隔离级别下的幻读问题，就引出了next-key锁，它是记录锁和间隙锁的组合

  • Record Lock，记录锁，锁的是记录本身

  • Gap Lock，间隙锁，锁的是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读

普通的select是通过mvcc实现的快照读，不会对记录进行加锁，如果要在查询的时候加行锁，可以使用下面的两种方式

Plain
 begin;
 // 对读取的记录加共享锁
 select ... lock in share mode;
 commit;

 begin;
 // 对读取的记录加排他锁
 select ... for update;
 commit;

行锁的释放时机是在事务提交（commit）后，锁才会释放，并不是在一条语句执行完毕之后释放锁

SQL
select * from performance_schema.data_lock\G;

执行以上的语句，可以查看事务执行SQL过程中加了什么锁

LOCK_TYPE 中的RECORD表示行级锁，通过LOCK_MODE 可以确认是next-key锁，间隙锁还是记录锁

  • LOCK_MODE: X ，说明是X型的next-key锁；

  • LOCK_MODE: X, REC_NOT_GAP ，说明是X型的记录锁；

  • LOCK_MODE: X, GAP ，说明是X型的间隙锁；

当事务B往事务A next-key锁的范围插入记录时，就会被锁住

执行插入语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以两个事务中**select ... for update** 语句并不会相互影响。

这样，事务A和事务B在执行完select ... for update 语句之后都持有了间隙锁，而接下来的insert 操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

为什么间隙锁和间隙锁之间是兼容的？

MySQL官网上有描述：

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from Inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

间隙锁的意义只在于阻止区间被插入，因此可以共存。**一个事务获取的间隙锁不会阻止另外一个事务获取同一个间隙范围的间隙锁，**共享和排他的间隙锁是没有区别的，它们相互不冲突，且功能相同，即两个事务可以共同持有包含共同间隙的间隙锁

共同间隙包括两种场景：

  • 两个间隙锁的间隙区间完全一样

  • 一个间隙包含的间隙区间是另外一个间隙锁区间的子集

注意：next-key lock是包含间隙锁+记录锁的，如果一个事务获取了X型的next-key lock，那么另外一个事务在获取相同范围的X型的next-key lock时，是会被阻塞的

再注意：对于右区间为+∞的next-key lock，因为+∞并不是一个真实的记录，所以我不需要考虑X型和S型

插入意向锁是什么？

MySQL的描述：

An Insert intention lock is a type of gap lock set by Insert operations prior to row Insertion. This lock signals the intent to Insert in such a way that multiple transactions Inserting into the same index gap need not wait for each other if they are not Inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to Insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with Insert intention locks prior to obtaining the exclusive lock on the Inserted row, but do not block each other because the rows are nonconflicting.

这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的是一个点，因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁

插入意向锁的生成时机：

  • 每插入一条新纪录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，如何锁的状态设置为等待状态，现象就是Insert语句会被阻塞（_PS：_MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁）

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

Insert语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自待的trx_id 隐藏列来作为隐式锁来保护记录的

什么是隐式锁？

当事务需要加锁时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁

隐式锁是InnoDB实现的一种延迟加锁机制

如何避免死锁？ ​

死锁的四个必要条件：

  • 互斥

  • 占有且等待

  • 不可强占用

  • 循环等待

只要发生死锁，这些条件必然成立，但是只要破环其中一个条件死锁就不会成立

在数据库层面，有两种策略通过打破循环等待条件来解除死锁状态：

  • 设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行混滚，于是锁就释放了，另外一个事务就可以继续执行了。在InnoDB中，参数innodb_lock_wait_timeout 是用来设置超时时间的，默认时间为50s

  • 开启主动死锁检测：主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将innodb_deadlock_detect 设置为on，表示开启这个逻辑，默认就开启。

MySQL内存 MySQL Buffer Pool ​

为什么要有Buffer Pool ​

MySQL的数据存储在磁盘的，如果每次都从磁盘里面读取数据，这样性能是很差的

提高性能，就需要加入缓存。当数据从磁盘中取出来之后，缓存内存中，下次查询同样的数据，直接从内存中读取

为此InnoDB存储引擎设计了一个缓存池（Buffer Pool），来提高数据库的读写性能

有了缓冲池后：

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

Buffer Pool有多大？ ​

Buffer Pool在MySQL启动的时候，向操作系统申请的一片连续的内存空间，默认配置下Buffer Pool只有128MB

可以通过调整innodb_buffer_pool_size 参数来设置Buffer Pool的大小，一般建议设置为可用物理内存的60%~80%

Buffer Pool缓存什么？ ​

InnoDB会把存储的数据分为若干个页，以页作为磁盘和内存交互的基本单位，一个页的默认大小为**16kb，**因此Buffer Pool同样需要按页来划分

在MySQL启动的时候，**InnoDB会为Buffer Pool申请一片连续的内存空间，然后按照默认的16kb的大小划分出一个个的页，Buffer Pool中的页就叫做缓存页。**这些缓存页都是空的，之后随着程序的运行，才会有磁盘上的页被缓存到Buffer Pool中

所以，MySQL刚启动的时候，其使用的虚拟内存空间很大，而使用到的物理内存空间很小，这时因为这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系

Buffer Pool缓存了以下的：

  • 索引页
  • 数据页
  • 插入缓存页
  • Undo页
  • 自适应哈希索引
  • 锁信息

为了更好管理Buffer Pool中的缓存页，InnoDB为每一个缓存页都创建了一个**控制块，**控制块包括缓存页的表空间，页号，缓存页地址，链表节点等，控制块也占据内存空间，它是在Buffer Pool的最前面，接着才是缓存页

暂时无法在飞书文档外展示此内容

上面的控制块和缓存页之间的空白空间称为碎片空间

碎片空间：每一个控制块对应一个缓存页，在分配足够多的控制块和缓存页后，可能剩余的空间不足够一个控制块和缓存页的大小，那么这块空间就不被使用，剩下的这块空间就被称为碎片

当Buffer Pool的大小设置的刚刚好，就不会产生碎片

查询一条记录时，InnoDB会把整个页的数据加载到Buffer Pool中，通过索引只能定位到磁盘中的页，而不能定位到页中一条记录。

mp.weixin.qq.com(从数据页的角度看B+树——InnoDB存储引擎)

记录是按照行来存储的，但是数据库的读取并不是以行为单位，否则一次读取（一次IO操作）只能处理一行数据，效率会非常低，因此，InnoDB的数据是按照数据页为单位来读写的

数据页的结构分为7个部分

File Header(38)文件头，表示页的信息
Page Header(56)页头，表示页的状态信息
infimum+supermun(26)两个虚拟伪记录，分别表示页中最小记录和最大记录
User Records(unclear)存储行记录内容
Free Space(unclear)页中还没被使用的
Page Directory(unclear)页目录，存储用户记录的相对位置，对记录起索引作用
File Tailer(8)校验页是否完整

其中，行记录由infimum+supremum 和 User Records构成

在File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向链表

采用链表结构是让数据页之间不需要物理上的连续，而是逻辑上的连续

数据页中User Records是怎么组织数据的？ ​

**数据页中的记录按照主键顺序组成单向链表，**单向链表的特点是插入、删除非常方便，但是检索效率不高

因此，在数据页中有一个页目录（Page Directory），起记录的索引作用，可以快速找到记录

页目录创建过程如下：

  1. 将所有记录划分为几个组，这些记录包括最小记录和最大记录，但不包括标记已删除的记录

  2. 每个记录组的最后一条记录是组内最大的那条记录，并且最后一条记录的头信息都会存储该组一共多少条记录，作为n_owned字段

  3. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称为槽（slot），每个槽相当于指针指向了不同组的最后一个记录

页目录就是由多个槽组成，槽相当于分组记录的索引。因为记录是按照主键值大小从小到大排序，所以通过槽查找记录时，可以使用二分查找法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，在遍历槽内的所有记录，找到对应的记录

InnoDB里的B+树中的每个节点都是一个数据页

InnoDB对每个分组中的记录条数是有规定的，槽内的记录就有几条：

  • 第一个分组中的记录只能由1条

  • 最后一个分组的记录条数范围只能在1-8条之间

  • 剩下的分组中记录条数范围只能在4-8条之间

如何管理Buffer Pool？ ​

  1. 空闲页的管理 ​

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

Free链表上除了控制块，还有一个头结点，该头结点包含该链表的头结点地址，尾节点地址，以及当前链表中节点的数量等信息

Free链表节点是一个个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于Free链表节点都对应一个空闲缓存页

有了Free链表后，每当需要从磁盘中加载一个页到Buffer Pool中，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从Free链表中移除

  1. 脏页的管理 ​

Buffer Pool除了提高读性能，还能提高写性能，就是更新数据的时候，不需要每次都写入磁盘，而将Buffer Pool对应的缓存页标记为脏页，然后由后台线程将脏页写入到磁盘

innodb设计出了Flush链表，跟Free链表类似，链表的节点是控制块，区别是Flush链表的元素是脏页

有了Flush链表，后台线程可以遍历Flush链表，将脏页写入磁盘

如何提高缓存命中率 ​

Buffer Pool的大小是有限的，所以需要使用一些策略，保证常用数据留在Buffer Pool，少用的数据在某个时机可以淘汰掉

最常见的是LRU算法（Least recently used）

这个算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没有使用的，那么当空间不够时，就淘汰最久没有使用的节点

简短的LRU算法实现思路如下：

  • 当访问的页在Buffer Pool中，就直接将该页对于的LRU链表节点移动到链表的头部

  • 当访问的页不在Buffer Pool中，除了把页放入到LRU链表的头部，还要淘汰LRU链表末尾的节点

至此，Buffer Pool里有三种页和链表来管理数据：

  • Free Page（空闲页）：表示此页未被使用，位于Free链表

  • Clean Page（干净页）：表示此页已经被使用，但是页面未发生修改，位于LRU链表

  • Dirty Page（脏页）：表示此页已经被修改，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成干净页。脏页同时存在于LRU链表和FLUSH链表

简短LRU算法没有被MySQL使用，因为简短LRU算法无法避免一些两个问题：

  • 预读失效

  • Buffer Pool污染

怎么解决预读失效而导致缓存命中率减低的问题？ ​

预读失效：

MySQL的预读机制。程序有空间局部性，靠近当前被访问数据的数据，在未来大概率被访问

MySQL在加载数据页的时候，会提前把相邻的数据页一并加载，减少磁盘IO

但是这些被提前加载进来的数据页，并没有被访问，相当于预读是白做，这个就是预读失效

如果使用简单的LRU算法，就会把预读页放到LRU链表头部，而当Buffer Pool空间不够，还需要淘汰末尾的页

这里会出现一个奇怪的问题，预读页可能一直不会被访问到，却会占用LRU链表前排的位置，而末尾淘汰的页可能是频繁访问的页，这样就大大降低了缓存命中率

避免预读失效带来的影响，最好就是让预读的页停留在Buffer Pool里时间尽可能的短，让真正被访问的页才移动到LRU链表的头部，从而保证真正被读取的热数据留在Buffer Pool里的时间尽可能长

MySQL做了以下修改：将LRU划分了2个区域：old区域和young区域

young区域在LRU链表的前半部分，old区域则是在后半部分，old区域占整个LRU链表长度比例可以通过innodb_old_blocks_pct 参数来设置，默认是37，代表整个LRU链表中young区域和old区域比例是63:37

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

MySQL改进后的LRU算法，通过划分young区域和old区域避免了预读失效带来的影响，但是没有解决Buffer Pool污染的问题

怎么解决出现Buffer Pool污染而导致缓存命中率减低的问题？ ​

Buffer Pool污染：

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

像全表扫描的查询，很多缓存页其实只会被访问一次，但是它却因为被访问一次而进入到young区域，从而导致热点数据被替换

为了解决这个问题，MySQL提高了进入young区域的门槛，这样就能有效保障young区域里的热点数据不会被替换掉

想要进去young区域条件增加了一个停留在old区域的时间判断

具体过程如下，在对某个处在old区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

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

这个间隔时间是由innodb_old_blocks_time 控制的，默认是1000ms

也就是说，只有同时满足被访问与old区域停留时间超过1s两个条件，才会被插入到young区域的头部，这样就解决了Buffer Pool污染问题

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

脏页什么时候会被刷入磁盘？ ​

引入Buffer Pool后，当修改数据时，首先修改Buffer Pool中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据

因此，脏页需要刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会变差，因此一般都会在一定时机进行批量刷盘

但是如果脏页在还没来得急刷入磁盘时，MySQL宕机了，数据会丢失吗？

不会，InnoDB的更新操作采用的是Write Ahead Log策略，即先写日志，在写入磁盘，通过redo log日志让MySQL拥有崩溃恢复能力

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

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

MySQL日志 MySQL Log ​

先理解执行一条sql语句，在mysql内部会发生什么？

以执行一条update 语句为例：

  • 客户端会先通过连接器建立连接，连接器会判断用户身份

  • 这里是一条update语句，所以不需要经过查询缓存（注意，当表上有更新语句，会把整个查询缓存清空，所以在Mysql8.0这个功能就被移除了）

  • 解析器会通过词法分析识别出关键字，构建出语法树，接着做语法分析，判断输入的语句是否符合MySQL语法

  • 预处理器会判断表和字段是否存在

  • 优化器确定执行计划（使用索引或者全表查询）

  • 执行器负责具体执行，找到这一行然后更新

不过，更新语句的流程会涉及到undo log**，redo log，binlog**三种日志：

  • undo log（回滚日志）：是InnoDB存储引擎生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC

  • redo log（重做日志）：是InnoDB存储引擎生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复

  • bing log（归档日志）：是Server层生成的日志，主要用于数据备份和主从复制

1.为什么需要undo log？ ​

在执行一条“增删改”语句的时候，MySQL会隐式开启事务，执行完后自动提交事务

MySQL中执行一条语句后是否自动提交事务，是由autocommit 参数来决定的，默认是开启的

当事务执行过程中，都记录下回滚时需要的信息到一个日志中，那么在事务执行过程中发生MySQL崩溃后，可以通过这个日志回滚到事务之前的数据

实现这一机制就是 undo log**（回滚日志），它保证了事务的ACID特性中的原子性**

每当InnoDB引擎对每种操作进行回滚时，进行相反操作就行：

  • 插入 - 删除

  • 删除 - 插入

  • 更新 - 更新为旧值

一条记录每次进行操作产生的undo log格式都有一个roll_pointer和一个trx_id事务id：

  • trx_id：记录该记录是被哪些事务修改的

  • roll_pointer：指针可以将这些undo log串成一个链表，这个链表被称为版本链

另外，undo log可以跟Read View一起实现MVCC（多版本并发控制）：

对于 读提交 和 可重复读 隔离级别的事务来说，它们的快照读（普通select语句）是通过Read View + undo log来实现的，区别在于创建Read View的时机不同

  • 读提交：是在每一个select都会生成一个新的Read View，也意味着事务期间的多次读取同一数据，前后两次读的数据可能会出现不一致（不可重复读）

  • 可重复读：是在启动事务时生成一个Read View，然后整个事务期间都在用这个Read View，这样保证了事务期间读到的数据都是事务启动时的记录

这两个隔离级别实现是通过事务的Read View里的字段和记录两个隐藏列trx_id和roll_pointer的对比

事务隔离级别是怎么实现的？

因此，undo log两大作用：

  • 实现事务回滚，保障事务的原子性

  • 实现MVCC（多版本并发控制）关键因素之一

Undo log是如何刷盘？

Undo log和数据页的刷盘策略是一样的，都需要通过redo log保证持久化

Buffer pool中有undo 页，对undo页的修改都会被记录到redo log。redo log每秒刷盘，提交事务时也会刷盘，数据页和undo 页都是靠这个机制保证持久化

2.为什么需要Buffer Pool？ ​

MySQL的数据都是存储在磁盘中的，那么我们更新一条记录，得先从磁盘读取该记录，然后在内存中修改记录，修改完之后并不会直接写回磁盘，而是缓存起来，这样下次查询语句命中这条记录，就不需要从磁盘读取数据

为此，InnoDB存储引擎设计了一个**缓冲池****Buffer Pool，**来提高数据库的读写性能

暂时无法在飞书文档外展示此内容

有了Buffer Pool后：

  • 当读取数据时，如果数据存在于Buffer Pool中，客户端会直接读取Buffer Pool中的数据，否则再去磁盘中读取

  • 当修改数据时，如果数据存在Buffer Pool中，那么直接修改Buffer Pool中数据所在的页，然后将页设置为脏页****（该页上的数据和磁盘上的不一样），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择以一个合适的时机将脏页写入到磁盘

Buffer Pool缓冲了什么？ ​

InnoDB会把存储的数据划分为若干个页，以页为磁盘和内存交互的基本单位，一个页的默认大小为16K

Buffer Pool同样按 页 来划分

在MySQL启动的时候，InnoDB会为Buffer Pool申请一片连续的内存空间，然后按照默认的**16k** 的大小划分一个个的页，Buffer Pool中的页叫做缓冲页。

Buffer Pool除了缓存索引页和数据页，还包括Undo页，插入缓存，自适应哈希索引，锁信息等

Undo 页是记录什么？

开启事务后，InnoDB层更新记录前，首先要记录相应的undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是生成一个undo log，undo log会写入到undo页面

查询一条记录，就只需要缓存一条记录吗？

不是，当查询一条记录的时候，InnoDB会将整个页的数据加载到Buffer Pool中，将页加载到Buffer Pool后，再通过页里的页目录去定位到某条具体的记录 换一个角度看B+树

3.为什么需要 redo log？ ​

Buffer Pool是提高了读写效率，但是Buffer Pool是基于内存的，而内存总是不可靠，出现断电重启时内存里没来得及落盘的脏页数据就会丢失

为了防止断电导致数据丢失，当一条记录需要更新，InnoDB就会先更新内存（同时标记为脏读页），然后将本次对这个页的修改以redo log的形式记录下来，这时候才算更新完成

后续，InnoDB引擎会在适合的适合，由后台线程将Buffer Pool的脏页刷新到磁盘里，这个就是WAL****（Write-Ahead-Logging）技术

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

什么是redo log？ ​

redo log是物理日志，记录了某个数据页做了什么修改，每当执行一个事务就会产生这样的一条或者多条物理日志

在事务提交的时候，只要先将redo log持久化到磁盘即可，可以不需要等待到将缓存在Buffer Pool里的脏页数据持久化到磁盘

当系统崩溃时，虽然脏页数据没有持久化，但是redo log语句持久化了，接着Mysql重启后，可以根据redo log的内容，将所有数据恢复到最新的状态

被修改undo页面，需要记录对应redo log吗？ ​

需要的。

开启事务后，InnoDB层要更新记录前，首先要记录相应的undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是生成一条undo log，undo log会写入Buffer Pool中的Undo页面

不过，在内存修改该undo页面后，需要记录对应的redo log

redo log和undo log区别在哪里？ ​

这两种日志都是InnoDB引擎下的日志，它们的区别在于：

  • redo log记录了事务完成后的数据状态，记录的是更新之后的值；

  • undo log记录了事务开始前的数据状态，记录的是更新之前的值；

当事务提交之前发生崩溃，重启后会通过undo log回滚事务，事务提交之后发生崩溃，重启后会通过redo log恢复事务

暂时无法在飞书文档外展示此内容

有了redo log，再通过WAL技术，InnoDB可以保证即使数据库发生异常重启后，之前已提交的记录都不会丢失，这个能力就是 crash-safe（崩溃恢复）

redo log****保证了事务四大特性中的持久性

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

写入redo log的方式是由了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写

磁盘的顺序写比随机写要高效得多，因此redo log写入磁盘的开销更小

可以说，这是WAL技术的另外一个优点：MySQL****的写操作从磁盘的随机写变成了顺序写

至此，针对为什么需要redo log这个问题我们有两个答案：

  • 实现事务的持久性，让MySQL有crash-safe能力

  • 将写操作从随机写到顺序写，提高MySQL写入磁盘的性能

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

不是，redo log也有自己的缓存——redo log buffer，每当产生redo log时，会先写入到redo log buffer，后续再持久化到磁盘：

Redo log buffer默认的大小是16MB，可以通过innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让MySQL处理大事务时不必写入磁盘，进而提高IO性能

redo log什么时候刷盘？ ​

缓存在redo log buffer里的redo log还是在内存中，它会在以下的时机刷新到磁盘

  1. MySQL正常关闭时

  2. 当redo log buffer中记录的写入量大于redo log buffer内存空间一半时，就会触发落盘

  3. InnoDB的后台线程每隔1s，将redo log buffer持久到磁盘

  4. 每次事务提交时都将缓存在redo log buffer里的redo log直接持久化到磁盘（这个策略可以通过innodb_flush_log_at_trx_commit 参数控制）

innodb_flush_log_at_trx_commit 参数控制的时候什么？ ​

  1. 设置参数为0时，表示每次事务提交时，还是将redo log留在redo log buffer，该模式下事务提交时不主动触发写入到磁盘的操作

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

  3. 设置参数为2时，表示每次事务提交时，都只是缓存在redo log buffer里的redo log写到redo log文件，注意写入到 redo log文件并不意味着写入到了磁盘，而是写入到了Page Cache，就行写入到了操作系统的文件缓存

innodb_flush_log_at_trx_commit 为0和2时，什么时候才将redo log写入磁盘？ ​

InnoDB的后台线程每隔1s：

  • 针对参数0：会把缓存在redo log buffer中的redo log，通过write()写到操作系统的Page cache，然后调用fsync() 持久化到磁盘。所以参数为0的策略，MySQL进程崩溃会导致上一秒所有事务数据的丢失

  • 正对参数2：调用fsync() ，将缓存在操作系统中Page Cache里的redo log持久化到磁盘，所以参数为2的策略，较取值为0情况下更安全，因为MySQL进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒所有的事务数据才可能丢失

innodb_flush_log_at_trx_commit 三个参数的应用场景是什么？ ​

redo log 文件写满了怎么办？ ​

默认情况下，innoDB存储引擎有一个重做日志文件组（redo log Group），重做日志文件组由2个redo log文件组成，这两个redo 日志的文件名叫：ib_logfile0 和ib_logfile1

在重做日志组中，每个redo log file的大小都是固定且一致的

重做日志组是以循环写的方式工作，从头开始写，写到末尾就回到开头，相当于一个环形

InnoDB存储引擎会先写ib_logfile0文件，当ib_logfile0文件被写满的时候，会切换至ib_logfile1文件，1写满时会被切换到0文件

redo log是为了防止Buffer Pool中的脏页丢失而设计的，那么随着系统运行，Buffer Pool的脏页刷新到了磁盘，那么redo log对应的记录也就没有了

redo log是循环写的方式，相当于一个环形，InnoDB用Write pos表示redo log当前记录写到的位置，用checkpoint表示当前要擦除的位置

  • write pos和check point的移动都是顺时针方向

  • write pos ~ check point之间的部分（红色），用来记录新的更新记录

  • check point ~ write pos 之间的部分（蓝色），待落盘的脏数据页记录

如果write pos追上checkpoint，就意味着redo log文件满了，这时MySQL不能再执行新的更新操作，也就是MySQL会发生阻塞（因此针对并发量大的系统，适当增大redo log文件的大小非常重要），此时会停下来将Buffer Pool中的脏页刷新到磁盘中，然后标记redo log哪些记录可以被擦除，接着对旧的redo log记录进行擦除，等擦除完旧记录腾出空间，checkpoint就会往后移动，MySQL恢复正常运行，继续执行新的更新操作

4.为什么需要binlog？ ​

前面的undo log和redo log都是InnoDB存储引擎生成的日志

MySQL在完成一条更新操作后，Server层还会生成一条binlog，等之后事务提交的时候，会将事务执行过程中产生的所有binlog统一写入binlog文件

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

为什么有了binlog，还要redo log？

因为最开始MySQL只有MyISAM引擎，没有InnoDB引擎，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档

而InnoDB是另外一个公司以插件的形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用redo log来实现crash-safe能力

redolog 和 binlog有什么区别？ ​

Redolog 和 binlog有四个区别：

  1. 适用对象不同：

    1. binlog 是MySQL的Server层实现的日志，所有的引擎都可以使用

    2. redolog 是InnoDB存储引擎实现的日志

  2. 文件格式不同：

    1. binlog有3种格式类型，分别是STATEMENT、ROW、MIXED区别如下：

      1. STATEMENT：每一条修改数据的SQL都会记录到binlog中（相当于记录了逻辑操作，所以针对这种格式，binlog可以称为逻辑日志）

      2. ROW：

      3. MIXED：

    2. redolog是物理日志，记录的是在某个数据页做了什么修改。

  3. 写入方式不同：

    1. binlog是追加写，写满一个文件，就创建一个新文件继续写，不会覆盖以前的日志，保存的是全量的日志

    2. redolog是循环写，日志空间大小是固定的，全部写满从头开始，保存未被刷入磁盘的脏页日志

  4. 用途不同：

    1. binlog用于备份恢复，主从复制

    2. redolog用于掉电等故障恢复

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

不可以使用redo log文件恢复，只能使用binlog文件恢复

因为redo log文件是循环写，是会边写边擦日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会被从redolog文件里擦除

binlog文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在binlog上的数据，都可以恢复

主从复制是怎么实现的？ ​

MySQL的主从复制依赖于binlog，记录MySQL上的所有变化以二进制形式保存在磁盘上，复制的过程就是将binlog中的数据从主库传输到从库上

这个过程一般是异步的

MySQL集群的主从复制过程大致三阶段：

  • 写入binlog：主库写binlog日志，提交事务，并更新本地存储数据

  • 同步binlog：把binlog复制到所有从库上，每个从库把binlog写到暂存日志中

  • 回放binlog：回放binlog，并更新存储引擎中的数据

具体详细过程如下：

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

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

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

从库是不是越多越好

不是，从库的数据增加，从库连接上来的I/O线程就越多，主库建立同样多的log dump线程来处理复制的请求，对主库资源消耗比较高，同时还限制于主库的网络带宽

在实际使用中，一般一主跟2~3从库

MySQL主从复制还有哪些模型

  • 同步复制

  • 异步复制

  • 半同步复制

binlog是什么时候刷盘？ ​

5.为什么需要两阶段提交？ ​

两阶段提交的过程是怎样的？ ​

重启异常会出现什么现象？ ​

MySQL架构 MySQL Architecture ​

6.1 SQL执行过程 ​

https://xiaolincoding.com/mysql/base/how_select.html#mysql-执行流程是怎样的

MySQL架构分为两层：server层和存储引擎层

  • Server层负责建立连接、分析和执行SQL
    • MySQL大多数核心功能模块都在这里：连接器、查询缓存、解析器、预处理器、优化器、执行器等
    • 还有所有的内置函数
    • 所有跨存储引擎的功能
  • 存储引擎层负责数据的存储和提取
    • 支持InnoDB、MyISAM、Memory等多个存储引擎

6.1.1 连接器 ​

MySQL是基于TCP协议进行传输的，所以在连接MySQL的时候需要先进行TCP三次握手，在命令行使用命令进行连接

Bash
mysql -h $ip -u$user -p

用户通过用户密码成功连接后，连接器会获取用户的权限，然后保存起来，在后续的此连接的任何操作，都会基于连接开始的时候读取到的权限逻辑进行判断

建立连接后，即使修改了该用户的权限，也不影响已连接的权限。只有新建的连接才会有新的权限设置

6.1.1.1 查看MySQL服务的客户端连接 ​

可以执行show processlist 命令进行查看

6.1.1.2 空闲连接会一直占着 ​

不会，MySQL定义了空闲连接的最大空闲时长，由wait_timeout 参数控制，默认值是8小时，超过这个时间，连接器就会把这个连接断开

使用命令可以查看该值

Bash
show variables like 'wait_timeout';

可以手动断开空闲的连接，使用的是

SQL
kill connection + id

当空闲的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求时，才会收到报错

“ERROR 2013 (HY000): Lost connection to MySQL server during query”

6.1.1.3 MySQL的连接限制 ​

MySQL服务支持的最大连接数由max_connections 参数控制

Bash
show variables like 'max_connections';

MySQL的连接跟HTTP一样，有短连接和长连接的概念

SQL
// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

一般推荐长连接，但是使用长连接可能会占用内存增多，因为_MySQL在执行查询过程中临时使用内存管理连接对象__，_只有在连接断开的时候才会释放

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

两个解决方案：

  1. 定期断开长连接

  2. 客户端主动重置连接

    MySQL在5.7版本实现了mysql_reset_connection()函数的接口，可以使得客户端执行一个很大的操作后，在代码里调用该函数，来进行重置连接，达到释放内存的效果

  • 与客户端进行 TCP 三次握手建立连接；

  • 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；

  • 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

6.1.2 查询缓存 ​

连接器完成连接后，服务端收到SQL语句，就会解析出SQL语句是什么类型的语句

如果是SELECT语句，MYSQL会先去查询缓存（Query Cache）查找缓存数据，这个查询缓存是以key-value 形式保存在内存中，key为SQL查询语句，value为SQL语句查询的结果

如果缓存命中，就会直接发送value给客户端，否则就继续往下执行

在MySQL8.0版本，这个查询缓存被删除了，因为这个查询缓存的命中率很低，因为只要有一个表有更新操作，那么这个表的查询缓存就会被清空，如果刚缓存了一个查询结果很大的数据，还没有使用，刚好这个表有更新操作，查询缓存就被清空了，相当于缓存浪费了

这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool

6.1.3 解析SQL ​

解析器会做下面两件事

  1. 词法解析：识别关键字

  2. 语法解析：根据词法解析的结果，根据语法规则，构建出SQL语法树

如果我们输入的SQL语句语法不对，就会在解析器这个阶段报错

注意：表不存在或者字段不存在，并不在解析器里识别。解析器只负责检查语法和构建语法树，但不会去查表或者字段存不存在

6.1.4 执行SQL ​

经过解析器后，进入执行SQL查询语法的流程，主要可分为下面三个阶段

  • prepare阶段，预处理阶段

  • optimize阶段，优化阶段

  • execute阶段，执行阶段

6.1.4.1 预处理器 ​

预处理阶段做以下的事：

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

  • 将*扩展为表上所有列

6.1.4.2 优化器 ​

预处理阶段后，需要为SQL语句制定一个执行计划，就交由优化器完成

优化器主要负责将SQL查询语句的执行方案确定下来，决定使用哪个索引

在查询语句前加个explain命令，就会输出这条SQL语句的执行计划

6.1.4.3 执行器 ​

执行器：开始真正执行语句。在执行过程中，执行器就会和存储引擎交互，过程如下

  • 主键索引查询

  • 全表扫描

  • 索引下推

6.1.4.3.1 主键索引查询 ​

在SQL语句中查询条件使用主键索引，访问类型为const，那么执行器与存储引擎执行流程大致如下

  • 执行器第一次查询，调用read_first_record函数指针指向函数，访问类型为const，指向InnoDB引擎索引查询的接口，让存储引擎定位符合条件的记录

  • 存储引擎通过主键索引的B+树结构定位到符合条件的记录，如果记录不存在，就会向执行器上报记录找不到的错误，查询结束；如果记录存在，则返回记录给执行器

  • 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，不符合则跳过该记录

  • 执行器查询的过程是一个while循环，所以会在查询一次，此时调用read_record函数指针指向的函数，因为优化器选择的访问类型是const，这个函数指针指向一个永远返回-1的函数，所以当调用函数的时候，执行器退出循环，查询结束

6.1.4.3.2 全表扫描 ​

全表查询是没有用到索引，所以优化器决定选用访问类型为ALL

  • 执行器第一次查询，调用read_first_record函数指针指向函数，访问类型为const，指向InnoDB引擎全扫描的接口，让存储引擎定位符合条件的记录

  • 执行器会判断读到的记录是不是符合条件，不是则跳过；是则将记录发送给客户（Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）

  • 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；

  • 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；

  • 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

6.1.4.3.3 索引下推 ​

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率（它是将server层部分负责的事，交由存储引擎层去处理）

总结 ​

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

  • 连接器：建立连接，管理连接、校验用户身份；

  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；

  • 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

  • 执行 SQL：执行 SQL 共有三个阶段：

    • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

    • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；

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

6.2 MySQL存储一行记录 ​

总结 ​

  • MySQL的NULL值是怎么存放的？

MySQL的Compact行格式中会使用NULL值列表来标记NULL的列，NULL值并不会存储在行格式中的真实数据

NULL值列表会占用1字节空间，当表中所有字段都定义成NOT NULL，行格式就不会有NULL值列表，可以节省1字节空间

6.? 引擎分类 ​

https://juejin.cn/post/7160557698642083847