MySQL八股学习笔记

一、MySQL结构

1.宏观结构

1.1.Server层

建立连接→解析SQL→执行SQL；

包含连接器、查询缓存、解析器、预处理器、优化器、执行器等。以及内置函数、跨存储引擎的功能。

1.2.存储引擎层

目前最常用的是InnoDB存储引擎。

2.建立链接-连接器

通过TCP进行连接，同时分为短连接（每次执行SQL都需要建立TCP连接）、长连接（减少反复建立和断开TCP连接）。

3.查询缓存

MySQL8.0之后已废弃

查询语句会优先去位于server层的查询缓存（query cache）中查找缓存数据。如果命中，直接返回；如果没有命中，继续往下执行，查询结果会被存入查询缓存。

但，一个表有更新，其对应的缓存就会被清空。对于频繁更新的表，很不适用。

4.解析SQL-解析器

（1）词法分析

识别SQL语句的关键字和非关键字。

（2）语法分析

判断SQL语句是否符合语法（不负责判断字段和表是否存在），构建SQL语法树。

5.执行SQL

5.1.预处理器 prepare

• 检查SQL语句中的字段、表是否存在。
• 将select *中的*扩展为表上的所有列。

5.2.优化器 optimize

确定SQL语句的执行方案，如索引的选择。

5.3.执行器 execute

（1）主键索引查询

• 第一次查询：
  优化器选择索引类型→执行器调用函数指针read_first_record 指向InnoDB 引擎索引查询的接口→存储引擎定位→执行器判断条件符合与否，返回结果

• 非第一次查询：
  优化器选择索引类型→执行器调用函数指针read_record指向-1（访问类型const时）→执行器直接返回结果

（2）全表扫描

• 第一次查询：
  优化器选择索引类型→执行器调用函数指针read_first_record指向InnoDB 引擎全扫描的接口→存储引擎定位→执行器判断条件符合与否，逐条返回结果

• 非第一次查询：
  优化器选择索引类型→执行器调用函数指针read_record指向InnoDB 引擎全扫描的接口→存储引擎读取下一个结果，逐条返回给执行器→执行器返回读取完毕结果

（3）索引下推

在二级索引中，将原本执行器的判断工作，交给存储引擎来做。有利于减少回表操作。

二、MySQL存储

1.表空间结构

• 行 row
  数据库中的记录按行存储，
• 页 page
  数据库按页来读写，默认页大小16KB。
• 区 extent
  表的数据量大时，索引的空间按照区分配（不再是页），每个区大小1MB（64个页）。使得索引的链表中相邻的页，实现物理位置的相邻。可以使用顺序I/O。
• 段 segment
  • 索引段：存放B+树非叶子节点
  • 数据段：存放B+树叶子节点
  • 回滚段：存放回滚数据的区的集合

2.行格式

以下均为Compact行格式。

2.1.记录的额外信息

（1）变长字段长度列表

可选字段。每个1或2字节的位存储（取决于变长字段是否大于255位），存在变长字段时，如varchar，逆序存放字段的长度信息（不含NULL）。

逆序存放有利于提高CPU Cache的命中率。这是因为可以减少CPU缓存行分裂（第一个字段数据和对应变长字段长度列表，更大的可能被放在同一个CPU缓存行）和提升数据局部性（局部性原理：一个数据被访问时，其附近的数据大概率也将要被访问。提高CPU猜中下一步数据的概率）。

（2）NULL值列表

可选字段。共1或2字节的位存储（取决于可NULL的字段数），1表示为NULL，0表示不是NULL，同样位逆序存放。

（3）记录头信息

• delete_mask ：标识此条数据是否被删除。
• next_record：下一条记录的位置。指向的是下一条记录的「记录头信息」和「真实数据」之间的位置。
• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。

2.2.记录的真实数据

（1）row_id

非必需的隐藏字段，占6字节。如果表无主键，也没有唯一约束，才会添加。

（2）trx_id

事务id，必需的，占6字节。表示数据由哪个事务生成。

（3）roll_pointer

指向上一个版本的指针，必需的，占7字节。

2.3.行溢出与行类型

对于Compact行格式：真实数据处占用20个字节指向溢出页的地址。

对于Compressed和Dynamic行格式：真实数据处置存储20个字节的指针指向溢出页的地址。

3.数据页

3.1.数据页组成

3.2.页目录

（1）创建

1. 将所有的记录划分成几个组，包括最小记录和最大记录；
2. 组内最大记录的头信息中，会存储该组记录数量（上图中粉红色字段）
3. 页目录用来存储组内最大记录的地址偏移量（槽）。

（2）规定

采用二分法检索。

1. 第一个分组中的记录只能有 1 条记录；
2. 最后一个分组中的记录条数范围只能在 1-8 条之间；
3. 剩下的分组中记录条数范围只能在 4-8 条之间。

三、索引

1.索引分类

1.1.数据结构

（1）B+树索引

M叉树+双向链表的结构。非叶子节点无数据，叶子节点保存数据。本章第5节详细介绍。

（2）Hash索引

一种基于哈希表的索引。

（3）Full-text索引

使用倒排索引实现，允许全文搜索。

1.2.物理存储

InnoDB中的以下二者都使用B+树实现。

（1）聚簇索引（主键索引）

• 生成
  （1）默认使用主键建立索引。
  （2）无主键时，使用不含NULL的唯一列。
  （3）都没有时，自动生成一个隐式自增id。

• 结构
  基于B+树，非叶子结点无数据，叶子节点存数据。

（2）二级索引（辅助索引/非聚簇索引）

• 结构
  基于B+树，非叶子结点无数据，叶子节点存主键。

1.3.字段特性

（1）主键索引

一张表只有一个，不允许空值、重复。

CREATE TABLE table_name  (....PRIMARY KEY (index_column_1) USING BTREE
);

（2）唯一索引

不允许重复，但允许空值的索引。

CREATE TABLE table_name  (....UNIQUE KEY(index_column_1,index_column_2,...) 
);

（3）普通索引

普通字段上创建。

CREATE TABLE table_name  (....INDEX(index_column_1,index_column_2,...) 
);

（4）前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引。可以建立在字段类型为 char、varchar、binary、varbinary 的列上。

CREATE TABLE table_name(column_list,INDEX(column_name(length))
); 

1.4.字段个数

（1）单列索引

（2）联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。

CREATE INDEX index_product_no_name 
ON product(product_no, name);

• 最左匹配原则
  假如有一个索引(a,b,c)，在进行筛选时where时，要保障有a的前提下，再筛选b；有b的前提下，在筛选c（写的顺序无所谓）。否则会联合索引失效（导致了无序）。

• 联合索引范围查询
  范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

比如where a>1 and b=2，b是无法用到联合索引的。

但是where a>=1 and b=2，在a=1时，b可以用到联合索引。

但是where a like 'j%' and b=2，在a='j’时（或其他相等的值时），b可以用到联合索引。其他时，b无法用到联合索引。

但是where a like '%j' and b=2，比较复杂。当联合的是主键和剩下唯一的一列时，是可以用到联合索引的。其他情况，应该是不能的（存疑）。

• 索引区分度
  要把区分大的字段放在前边。如果区分度太小，联合索引会被优化器忽略，进行全表扫描。

• 排序优化

select * from order 
where status = 1 
order by create_time asc

给status和create_time建立联合索引，排序效率会更高。

2.索引创建

2.1.索引缺点

1. 占用物理空间
2. 创建和维护耗费时间
3. 降低表的增删效率（B+树为了维护有序性，需要动态维护）

2.2.适用索引

1. 字段具有唯一性
2. 经常用where查询语句
3. 经常用order by和group by

2.3.不适用索引

1. where，order by和group by用不到的字段
2. 索引区分度低
3. 数据太少时
4. 频繁更新的字段

3.索引优化

3.1.前缀索引优化

使用前缀索引，减少索引字段大小。

但，order by无法使用前缀索引；无法吧前缀索引用作覆盖索引。

3.2.覆盖索引优化

建立联合索引的二级索引，避免/减少回表操作。

3.3.主键索引自增

每插入一条新纪录，只需要追加，不需要重新移动数据。

3.4.索引NOT NULL

NULL会导致优化器所索引选择时更加困难；
NULL占用额外物理空间（参考行格式）。

3.5.防止索引失效

避免索引失效的情况。详情见本章第6节。

4.索引效率

执行计划的参数：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。

type字段，执行效率从低到高：

All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。

extra字段：

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，效率是很低的。
Using temporary：使了用临时表保存中间结果，效率低。
Using index：使用了覆盖索引，避免了回表操作，效率不错。

5.B+树

5.1.特点

1. 组成为M叉树+双向链表
2. 所有节点按照索引键的大小排序
3. 只有叶子节点才存放数据，非叶子节点仅存放目录项作为索引

5.2.查找

1. 从根节点，先用二分法定位包含查询值的页。
2. 非叶子节点，继续使用二分法定位包含查询值的页。
3. 到叶子节点时，使用二分法定位槽，然后遍历槽找到对应的记录。

6.索引失效

索引失效会大幅降低查询性能。

6.1.左/左右模糊匹配

like %xx 或者 like %xx% 这两种方式都会造成索引失效。变成全表扫描。

6.2.函数

对索引使用函数，会造成索引失效。索引保存的是索引字段的原始值，而不是经过函数计算后的值。

6.3.表达式计算

对索引使用表达式计算（如where id +1 = 10），会造成索引失效。索引保存的是索引字段的原始值，而不是经过计算后的值。

6.4.隐式类型转换

MySQL在遇到字符串和数字比较时，会把字符串转为数字。相当于对索引使用了函数。

6.5.联合索引非最左匹配

参考本章1.4节。

6.6.WHERE中的OR

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

四、事务

1.事务特性

ACID

1.1.原子性

undo log
一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。中间发现错误就回滚到事务开始前的状态。

1.2.一致性

原子性+隔离性+持久性
是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

1.3.隔离性

MVCC（多版本并发控制）和锁机制
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

1.4.持久性

redo log
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2.并发事务的问题

严重程度：

2.1.脏读

读到其他事务未提交的数据；
（未提交事务可能回滚）

2.2.不可重复读

前后读取的数据不一致；
（另一个事务在两次其读之间修改了数据）

2.3.幻读

前后读取的记录数量不一致。
（另一个事务在两次其读之间新增/删除了数据）

3.事务隔离级别

隔离水平：

可能发生的问题：

3.1. 读未提交

指一个事务还没提交时，它做的变更就能被其他事务看到；

3.2.读提交

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

• 采用MVCC（Read View）方式实现。

3.3.可重复读

指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的（默认）。

实际上可以很大程度上避免幻读。

• 快照读（普通 select 语句，查询）
  采用MVCC（Read View）方式解决。

• 当前读（select … for update 等语句，查询并更新）
  采用next-key lock（记录锁+间隙锁）。

3.4.串行化

会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

• 采用读写锁方式解决。

4.Read View

4.1.Read View基础

（1）字段组成

• m_ids ：创建时，当前数据库中「活跃事务」的事务 id 列表。
• min_trx_id ：创建时，「活跃事务」中事务 id 最小的事务。
• max_trx_id ：创建时当前数据库中应该给下一个事务的 id 值，全局事务中最大的事务 id 值 + 1；
• creator_trx_id ：指的是创建该 Read View 的事务 id。

（2）可见性划分

与行格式的trx_id的关系：

1. trx_id<min_trx_id，创建前已有，可见。
2. trx_id>max_trx_id，创建后才有，不可见。
3. min_trx_id<trx_id<max_trx_id时，若trx_id在m_ids中，不可见；若trx_id不在m_ids中，可见。

4.2.Read View与读提交

当事务A提交后，事务B的Read View会重新创建。事务B会找历史中，trx_id小于其自身min_trx_id的记录。

4.3.Read View与可重复读

不论事务A是否提交，事务B的Read View都不会重新创建。事务B会找历史中，trx_id小于其自身min_trx_id的记录。

五、锁

1.锁分类

1.1.全局锁

主要用于全库逻辑备份，整个数据库都会变成只读。但是效率较低，备份期间，无法更新数据。

可以通过可重复读的隔离级别避免全局锁。备份前创建Read View，加上MCVV的支持，使得备份期间也可以进行更新操作。

1.2.表级锁

（1）表锁

颗粒度越大，会影响并发性能。

//表级别的共享锁，也就是读锁；允许自己/其他人读
lock tables t_student read;
//表级别的独占锁，也就是写锁；允许自己写
lock tables t_student write;
//解锁
unlock tables

（2）元数据锁（MDL）

• 对一张表进行创建、读取、更改、删除操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

无需显式调用，常规的读共享，写独占。但是已有读锁是，申请写锁会被阻塞，之后有新的读锁也会被阻塞（写锁优先级高）。

（3）意向锁

• 作用：
  一个表级别的标识作用，当想给表里的部分记录加共享锁/独占锁之前，需要先在表级别上加上一个意向共享锁/意向独占锁。用来告诉后续的，想加表级别锁的事务，这里以及有锁了。

• 原因：
  由于表级别的共享锁/独占锁，与行级别的共享锁/独占锁存在读读共享、读写互斥、写写互斥。如果没有意向锁，想加表级别锁时，需要遍历查看表里是否有独占锁，效率会很低。

意向锁之间、意向锁与表级锁/行级锁不会发生冲突。

（4）AUTO-INC锁

对于自增表：AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

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

• innodb_autoinc_lock_mode
  • 0 采用AUTO-INC锁，语句执行结束后才释放锁
  • 2 采用轻量级锁，申请自增主键后就释放
  • 1
    • 普通insert：2；
    • 类似insert…select这样批量的：0；

采用2，性能高，但是主从复制经过binlog可能存在不一致的情况下，可以用过binlog_format=row解决。

1.3.行级锁

（1）Record Lock

Record Lock 称为记录锁，锁住的是一条记录。读共享（S锁），锁独占（X锁）。

（2）Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别。间隙锁之间是兼容的。

（3）Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。读共享，写独占。

（4）插入意向锁

不是意向锁，而是一种特殊的间隙锁，值锁住一个点。与间隙锁互斥。

2.行级锁实现

如果 LOCK_MODE 为 X，说明是 next-key 锁；
如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

加锁的对象是索引

2.1.唯一索引

（1）等值查询

查询记录存在：Next-Key Lock会退化成记录锁。
查询记录不存在：Next-Key Lock会退化成间隙锁。

（2）范围查询

要考虑Next-Key Lock的左开右闭特性！再看各个分段是哪个锁！

大于
select * from user where id > 15 for update;

大于等于
select * from user where id >= 15 for update;

小于
select * from user where id < 6 for update;

小于等于
select * from user where id <= 5 for update;

2.2.非唯一索引

会影响到主键，同时记录锁也会变成next-key锁。

（1）等值查询

记录不存在情况
select * from user where age = 25 for update;

锁住的不仅是age的(22,39)，还包括id的(10,20)。

记录存在情况
select * from user where age = 22 for update;

锁住的不仅是age的(21,22],(22,39)，还包括id的(5,20)。

（2）范围查询

不会退化
select * from user where age >= 22 for update;

锁住的不仅是age的(21,22],(22,39),(39,+∞]，还包括id的(5,+∞)。

2.3.无索引

每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了。

3.死锁问题

3.1.死锁产生

事务A和事务B都给表加入了间隙锁（不冲突），之后想插入时，再想加入插入意向锁，便会产生冲突。需要等对方释放间隙锁，此时便产生死锁。

3.2.死锁避免

死锁的必要条件：互斥、占有且等待、不可强占用、循环等待。

• 设置事务等待锁的超时时间：一个事务的等待时间超过该值后，进行回滚。
• 开启主动死锁检测：检测到死锁后，主动回滚某一事务。

3.3.Insert加行级锁

Insert语句用的是隐式锁，也就是说不发生冲突时，不会提前加锁。

（1）间隙锁情况

当其他事务在本事务想要插入数据的地方，提前加了间隙锁。那么本事务会生成一个插入意向锁，然后阻塞住。

（2）唯一键冲突

当插入的记录，与已有记录的主键/唯一键二级索引列相同时，插入会失败，然后会为这条记录加上S型锁。分别加S型记录锁/S型next-key锁。

六、日志

1.undo log 回滚日志

1.1.特点

保证了事务的原子性；发生崩溃时用来回滚，作原先操作的相反操作（如原本是新增，那么就删除）。

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

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；
  

1.2.特点

1. 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
2. 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

2.buffer pool 缓存池

基于内存。

2.1.作用

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

2.2.缓存内容

• undo log 会写入到undo页。
• 没查询一条记录，会将整个页加载到buffer pool中。

3.redo log 重做日志

基于磁盘。

3.1.作用

（1）WAL技术

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

（2）redo log特性

redo log是物理日志，持久化于磁盘；内存修改undo log后，需要记录对应的redo log。

（3）与undo log对比

undo log：记录事务开始前；
redo log：记录事务完成后。

（4）特点

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

3.2.刷盘

基于redo log buffer。

（1）刷盘时机

1. MySQL正常关闭
2. redo log buffer满了一半
3. InnoDB后台线程每间隔一秒
4. 主动刷盘

（2）主动刷盘

innodb_flush_log_at_trx_commit参数：

• 0：不触发主动刷盘（安全性依赖数据库）
• 1：每次事务提交都主动刷盘
• 2：每次事务提交都写入到page cache（安全性依赖系统）

3.3.文件写满

有两个redo log文件组成“重做日志文件组”，两个文件循环写。

若redo log满了，MySQL会阻塞，停下来将buffer pool中的脏页刷新到磁盘，标记对应redo log可以擦除的部分。

4.binlog 归档日志

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

4.1.与redo log对比

（1）适用对象

• binlog ：MySQL 的 Server 层实现的日志，所有存储引擎都可以使用

• redo log ：Innodb 存储引擎实现的日志；

（2）文件格式

• binlog ：STATEMENT（默认，记录SQL语句）、ROW（记录行数据变化）、MIXED。

• redo log ：物理日志；

（3）写入方式

• binlog ：追加写，不会覆盖。

• redo log ：追加写，循环写，会覆盖（redo 因此做不到大范围恢复）；

（4）用途

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

4.2.主从复制

（1）过程

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

（2）从库数量

并非越多越好，过多消耗主库资源、受限于主库网络带宽。

（3）主从复制模型

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。性能很差；可用性很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行。

4.3.刷盘

基于binlog cache。
binlog cache→（write）→page cache→（fsync）→磁盘

sync_binlog参数：

• 0 每次提交只write（默认）
• 1 每次提交都write和fsync
• N>1 没词提交都write，但积累N个事务后才fsync

5.两阶段提交

5.1.作用

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。既在宕机前，其中一个刷入磁盘，另一个没有来得及写。

5.2.过程

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

• commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（状态无需持久化磁盘）。

5.3.异常重启

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

• 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
• 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

两阶段提交是以 binlog 写成功为事务提交成功的标识

5.4.存在问题

• 磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
• 锁竞争激烈：「多事务」的情况下，还需要加一个锁来保证提交的原子性。

5.5.组提交

binlog组提交：当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数。
redo组提交：将prepare阶段的刷盘，延迟到flush阶段。

每个队列的第一个事务会成为leader。

（1）flush阶段

多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；

flsuh队列用于支持redo log的组提交，如果这一步崩溃，会回滚该组事务。

（2）sync阶段

对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
等待时长受到参数binlog_group_commit_sync_no_delay_count和binlog_group_commit_sync_delay影响。时间到了将binlog刷盘。

如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务的提交。

（3）commit阶段

调用引擎的提交事务接口，各个事务按顺序做 InnoDB commit 操作；

七、buffer pool的管理

有关管理空闲页、脏页、提高缓存命中率、脏页刷盘等。详情见小林。

八、其他

1.1.vanchar(n)中n的最大取值

• 所有字段长度
• 每个变长字段字节数列表所占用字节数（每个1或2字节）
• NULL标识

1.2.长连接占用内存问题

• 定期断开长连接
• 客户端主动重置连接

1.3.回表和覆盖索引

（1）回表

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇 索引中获得数据行，这个过程就叫作「回表」。

（2）覆盖索引

当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」。

2.1.MySQL单表最大值

略，自行看小林。
$$\text{Total}=x^{z-1}\times y$$
其中 $x$ 为非叶子节点，指向其他页的数量；
$y$ 为叶子节点所能容纳的数据行数；
$z$ B+树的层数。

2.2.MySQL使用"%x"一定会索引失效吗

不一定，如果只有主键+二级索引，会走全扫描二级索引树。其他情况下会失效。

2.3.count(*)和count(1)对比

（1）对比

对于InnoDB，按照性能排序:
count(*)= count(1)>count(主键字段)>count(字段)

（2）count(*)/count(1)过程

• 优先选二级索引，但只有聚簇索引时：循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值。不区分NULL与否。

（3）count(主键字段)过程

• 优先选二级索引，但只有聚簇索引时：循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。

（4）count(字段)过程

可能使用全表查询，效率很低。

2.4.为什么采用B+树

（1）相比B树

B树在非叶子节点也存储数据。

1. I/O访问：因此，B+树的单个节点数据量更小，相同I/O下可以查询更多结点。
2. 内存：查询B树时，中途用不到的数据也会被反复放入内存，浪费资源。
3. 范围查询：B+树叶子节点采用双向链表连接，适合范围的顺序查找。B树无法做到。
4. 单点查询：B树虽然单点查询略快，但速度不稳定。
5. 插入删除：B+树可以直接从叶子节点删除，无需动非叶子结点，效率高很多，树结构变化小。插入同理。

（2）相比二叉树

当数据量比较大时，二叉树的高度会很高${\log }_{2}N$，而B树每层几百个，高度很浅${\log }_{M}N$。有利于减少磁盘I/O操作。

（3）相比Hash

Hash很适合等值查询，但不适合范围查询。

4.1.update全局锁事故

在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了。

5.1.MySQL磁盘I/O优化

1. 设置binlog_group_commit_sync_delay（延迟时间微秒提交）和binlog_group_commit_sync_no_delay_count（延迟事务个数提交）。
2. 设置sync_binlog为大于1的值。
3. 设置innodb_flush_log_at_trx_commit设置为2.

5.2.update全过程

UPDATE t_user 
SET name = 'xiaolin' 
WHERE id = 1;

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：

   • 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
   • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

   • 如果一样的话就不进行后续更新流程；
   • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

3. 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。

4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术。

5. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。

6. 事务提交，剩下的就是「两阶段提交」的事情了。