Mysql

P2

Threads

Master Thread

包含多个循环（主循环，background，flush，suspend）

日志写入磁盘
合并 Insert Buffer 到磁盘
刷新脏页
删除无用 Undo 页
1.2.x 版本前，刷新脏页等 IO 速度硬编码，版本后添加 innodb_io_capacity 代表磁盘速度，此变量影响刷新页的速度

IO Thread

InnoDB 使用 Async IO ，IO Thread 负责这些异步 IO 请求的回调

Purge Thread

1.1 版本后，回收 Undo 页由主线程转为此线程负责

Page Cleaner Thread

1.2.x 版本后，刷新脏页由主线程转为此线程负责

缓冲池

页类型

索引页、数据页、undo 页、插入缓冲 (insert buffer)、自适应哈希索引(adaptive hash index>、InnoDB 存储的锁信息（lockinfo)、数据字典信息(data dictionary) 等

innodb_buffer_pool_instances

设定缓冲池数量

池内存管理

改动过的 LRU 算法

LRU 列表中加入了 midpoint 位置：最新访问的页并不是直接放入到 LRU 列表的首部，而是放入到 LRU 列表的 midpoint 位置（默认配置下，该位置在 LRU 列表长度的 5/8 处）

若直接将读取到的页放入到 LRU 的首部，那么某些 SQL 操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入 LRU 列表的首部，那么非常可能将所需要的热点数据页从 LRU 列表中移除，而在下一次需要读取该页时，InnoDB 存储引擎需要再次访问磁盘。

innodb_old_blocks_time 控制的 Old 链表头部页面的转移策略。该 Page 需要在 Old 链表停留超过该时间，之后再次被访问，才会移动到 Young 链表。这么操作是避免 Young 链表被那些只在 innodb_old_blocks_time 时间间隔内频繁访问，之后就不被访问的页面塞满，从而有效的保护 Young 链表。

缓冲命中率

Buffer pool hit rate，表示缓冲池的命中率，这个例子中为 100%，说明缓冲池运行状态非常良好。通常该值不应该小于 95%。若发生 Buffer pool hit rate 的值小于 95% 这种情况，用户需要观察是否是由于全表扫描引起的 LRU 列表被污染的问题。

Checkpoint

将缓冲池中的脏页刷回到磁盘

缩短数据库的恢复时间
缓冲池不够用时，将脏页刷新到磁盘
重做日志不可用时，刷新脏页。

Sharp Checkpoint

一次性将 buffer pool 当中的所有脏页刷新到磁盘数据文件

Fuzzy checkpointin

一个后台进程，定期刷新 buffer pool 当中的脏页

Master Thread Checkpoint：主线程定期异步地刷新一部分脏页
FLUSH_LRU_LIST Checkpoint：LRU 中需保证的可用页不足时移除部分尾端页，其中的脏页被刷新
Async/Sync Flush Checkpoint：redo log 不可用时
Dirty Page too much Checkpoint：脏页过多，由参数 innodb_max_dirty_pages_pct 控制

插入缓冲 Insert Buffer

聚集索引基本为递增，而非聚集索引大多为非顺序，插入操作需要随机写入会影响性能，因此插入缓冲在插入或更新操作，先判断插入的非聚集索引页在缓冲池中，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中。再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，大大的提升了非聚集索引的插入性能。

实现

B+ Tree

使用条件

索引是辅助索引：聚集索引本来就是顺序
索引不是唯一：如果是唯一索引，在插入时，需要查找辅助索引树，判断该记录是否存在以确保唯一性，查找过程就是随机读的过程，因此插入缓冲就没意义了

Merge 场合

辅助索引页被读取到缓冲池时，也就是常规 SELECT 使用了辅助索引时
Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时
Master Thread 定期合并

change buffer

InnoDB 从1.0.x版本开始引入了 Change Buffer，可将其视为Insert Buffer 的升级。从这个版本开始，InnoDB存储引擎可以对 DML操作——INSERT、DELETE、UPDATE都进行缓冲，他们分别是: Insert Buffer、Delete Buffer、Purge buffer

Doublewrite

partial write

InnoDB 的 page size 默认是 16KB，而操作系统与物理磁盘的一个 block size 更小，这会导致在极端情况下 (断电 / os crash) 对磁盘的页写入只有部分成功，即无法确保页写入操作的原子性

实现

doublewrite 是 Innodb 磁盘表空间内部分配的一片缓冲区，一般 double write 包含 128 个页，对于 pagesize 为 16k 的页，总共 2MB。Innodb 在磁盘写入时先将页写入 doublewrite 缓冲区，然后再将其从缓冲区写入真正的表内存储位置

写 double write buffer 本身失败，那么这些数据不会被写到磁盘，InnoDB 此时会从磁盘载入原始的数据，然后通过 InnoDB 的事务日志来计算出正确的数据，重新写入到 double write buffer
写 double write buffer 本身成功，写数据到磁盘失败，Innodb 存储引擎可以从共享表空间的 double write 中找到该页的副本，将其复制到表空间文件，再应用 redo log，就完成了恢复过程

redo log 能否恢复

纯物理日志实现 redo log 完全不依赖物理页的状态，并且是幂等性的，因此可以恢复 partial write

而 innoDB 的 redo 采用的是物理逻辑结合型的日志。物理到具体的哪个 page，页内操作是逻辑的。这种方式既实现了物理日志带来的幂等性（以物理页为整体），又拥有逻辑日志带来的轻量性（物理页内修改是逻辑日志）。所以物理页内修改不是幂等的，需要使用 doublewrite 技术来保证写入的成功

2021-10-24 更新：再次总结一下 double write 必要性的问题（感谢@E99p1ant的指正）

redo log 不具有幂等性，因此恢复需要物理页的上一个完整状态

假如没有 double write ，直接向目标页写入，若发生 partial write ，则该物理页的上一个完整状态将永久失去，因此 redo log 无法恢复损坏页面

启用 double write 后，第一步写入目标变为了磁盘上的 double write buffer ，无论这一步成功与否，都不影响目标页的上一个完整状态。而从 double write buffer 复制写入到目标页的这第二步是具有幂等性的，就算失败也只需要再写一次

Tips：以上内容具有一点猜想成分，由于没去查看源码，也还没有发现此处非常深入的分析资料，可能与事实有出入。例如，启用 double write 后的第一步写，不清楚需不需要上一个状态物理页，如果需要的话还需要从目标页复制一份过来？

redo log 是否需要保护

redo 日志的页大小一般设计为 512 Byte，不会出现 partial write

others

向磁盘上的 doublewrite 缓冲区写入时是顺序写入，因此性能影响不大。缓冲区再向各表同步时还是随机写入的
若文件系统本身支持原子性写入，确保不会发生 partial write 就可以关闭 double write 来避免无意义的性能损失

自适应哈希索引

InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引，来提高查询效率。可以关闭该功能但不能强制启用哈希索引

自动启用条件

连续查询的条件一样：例如不能交替查询 WHERE a=xxx 和 WHERE a=xxx and b=xxx
连续用同样的模式访问100次
页通过该模式访问了N次，其中N=页中记录*1/16 （没看懂）

异步IO / 刷新邻接页

常规特色也就是异步能避免 IO 阻塞线程；另一个优势是可以进行IO Merge操作，也就是将多个IO合并为1个IO，这样可以提高IOPS的性能

当刷新一个脏页时，InnoDB存储引擎会检测该页所在区的所有页，如果是脏页，那么一起进行刷新。该工作机制在传统机械磁盘下有着显著的优势，但是

可能将不怎么脏的页进行了写人，而该页之后又会很快变成脏页
固态硬盘有着较高的IOPS

因此对于固态硬盘建议不启用此特性

启动关闭与恢复

innodb_fast shutdown

0表示在MySQL数据库关闭时，InnoDB需要完成所有的full purge和 mergeinsert buffer，并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几个小时来完成。如果在进行InnoDB升级时，必须将这个参数调为0，然后再关闭数据库。
1是参数innodb_fast_shutdown 的默认值，表示不需要完成上述的full purge和merge insert buffer操作，但是在缓冲池中的一些数据脏页还是会刷新回磁盘。2表示不完成full purge和 merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次MySQL数据库启动时，会进行恢复操作（recovery)。
2表示不完成full purge和 merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次MySQL数据库启动时，会进行恢复操作（recovery)。

innodb_force_recovery

默认为0，可设置为1~6

代表当发生需要恢复时，进行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，MySQL数据库可能发生宕机（crash)，并把错误写入错误日志中去

P3

mysql参数

static 静态变量：无法在实例生命周期内修改
dynamic 动态参数：分为session级与global级

日志文件

error log

错误日志文件对MySQL的启动、运行、关闭过程进行了记录，也包含一些警告与正确的信息
文件路径定位：SHOW VARIABLES LIKE’log_error'

slow log

可以在 MySQL启动时设一个阈值，将运行时间大于（不包含等于）该值的所有SQL语句都记录到慢查询日志文件中

参数：long_query_time。默认值为10，代表10秒。
参数：log_slow_queries，是否启用 slow log 。默认不开启，需要手动设为 ON
参数：log_queries_not_using_indexes，是否启用检测未使用索引的语句
参数：log_throttle_queries_not_using_indexes，表示每分钟允许记录到 slow log 的且未使用索引的SQL语句次数。默认为0，表示无限制。此参数作用是防止过于频繁触发未使用索引检测，导致 slow log 过快无意义增长
mysqldumpslow 命令辅助分析慢查询日志

InnoDB 额外统计了 SQL 执行中逻辑读取（包含物理读取与缓冲池读取）与物理读取的次数，观察它们的比例可以辅助判断是否需要优化

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为:主机名.log

bin log

二进制日志记录了对 MySQL 数据厍执行更改的所有操作以及时间等信息，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改

若有 UPDATE 操作未对数据库产生变化，也会被记录在 bin log 中

主要有以下作用：

恢复（recovery）：某些数据的恢复需要二进制日志，例如在一个数据库全备文件恢复后，可以通过二进制日志进行 poing-in-time 的恢复
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志，使两台数据库实时同步
审计（audit）：判断有无 sql 注入攻击

缓存

使用事务的引擎中，未提交的二进制日志会先写入缓存中（基于session）

binlog_cache_size：决定该缓存的大小
缓存超出限制大小时会创建临时文件
binlog_cache_use、binlog_cache_disk_use分别代表使用缓存与使用临时文件缓存二进制日志的次数，观察这俩变量可以得知缓存限制是否过小

同步

sync_binlog

占坑

格式

Statement-based：记录 SQL 语句。容量小，恢复数据快，具有可读性；但存在一些无法确保恢复数据一致的情景，不能使用该模式
Row-based：记录每一行变化的二进制原始数据。安全性高，需要更少的锁因此性能更好；容量更大
MIXED：以上两种混合

详细：MySQL binlog 日志的格式

redo log

二进制日志会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MylSAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志
其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页(Page）的更改的物理情况。
此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目（redocntry）被写入到重做日志文件中。

上面提过，redo log写入单位为512字节，能保证写入的原子性因此不需要 double write 机制保护

具体还是等事务部分细说，占坑

P4 表

外键约束

外键 (FOREIGN KEY) 用于建立两个表数据中列的链接，从而维护数据一致性。被引用的表称为为父表，引用的表为子表，通常子表引用父表的主键建立约束：子表的外键指向父表主键

默认引擎不支持外键
外键与其指向的主键类型必须相同
子表插入一行时，若父表的主键中不存在与该行外键一致的值则不能插入

外键约束有四种子表操作来实现数据一致性：

CASCADE (级联)：父表更新或删除一行时，子表中外键与其主键一致的所有行也更新或删除
RESTRICT (约束)：父表更新或删除一行时，如果子表中存在外键与其主键一致的行，则不允许并报错
SET NULL：父表更新或删除一行时，子表中外键与其主键一致的所有行将外键设为 NULL（该列需要允许为 NULL）
NO ACTION：与 RESTRICT 一致

外键约束对于确保数据一致性有着很好的作用，但是对性能有显著影响。

不使用外键牺牲了数据库中数据的一致性，但是却能够减少数据库的负载；

模拟外键将一部分工作移到了数据库之外，我们可能需要放弃一部分一致性以获得更高的可用性，但是为了这部分可用性，我们会付出更多的研发与维护成本，也增加了与数据库之间的网络通信次数；

使用外键保证了数据库中数据的一致性，也将全部的计算任务全部交给了数据库

转自：为什么数据库不应该使用外键

P5 索引与算法

B+ tree

InnoDB中一般为2-4层，以适应磁盘随机读取开销较大的特性，在查询时降低随机 IO 次数

插入

以此图为例，插入70

拆分：
旋转：当左右兄弟节点未满时，优先使用旋转提高效率

索引

采用B+ 树结构，叶子节点由双向链表连接

创建索引

ALTER TABLE tbl_name

CREATE [UNIQUE] INDEX index_name [index_type] ON tbl_name

聚焦索引

每张表仅有一个，以主键为索引，叶子节点（数据页）存放的是完整行数据

主键

不可修改（？）：因为主键的第二个作用是让其他表的外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束（仅仅引用主键但并没有设置外键约束），修改主键会导致数据完整性直接被破坏
业务无关：为满足不可修改原则，主键不能与业务有关联
不宜过大：辅助索引的值为主键，因为主键的大小会影响辅助索引大小
推荐使用自增：因为innodb是B+ 树结构，自增主键导致新增行时是高性能的顺序写入，同时避免了B+ 树的拆分。也因此对范围查询拥有高性能
自增主键也会遇到自增锁的性能问题

辅助索引/非聚焦索引

叶子节点只存放主键，因此需要根据主键在聚焦索引里二次查询才能得到完整行内容，这个过程称为回表

联合索引

对多个列组合起来的索引，如 (a,b) 索引将先按 a 再按 b 排序。因此针对 a 或者 a&&b 的条件查询都可以使用该索引，但是 b 条件不行，因为此联合索引没有按 b 的顺序

也因为多列索引都以排序这个特性，可以免去部分范围查询的二次排序

覆盖索引

当只在辅助索引（一般即为联合索引）中就能得到查询的记录时，即可省去回表这步

使用统计（COUNT）时也会优先使用可行的联合索引，因为单节点小，同页节点多查询更快

Fast Index Creation

在5.5版本前，索引的添加或删除需要创建并导入原表数据到一张新表，因此需要宕机很长的时间

5.5版本后有了 FIC 对创建索引的表加上 S 锁，不再需要创建新表因此速度大大提高，此技术仅适用于辅助索引。但这个过程中对表只能读不能写

Cardinality

代表索引中不重复记录的比例，即索引的『选择性』。如性别列中只会有F,M两个选项，就只有50%的选择性

当 Cardinality 值过低代表该索引低选择性，为其建立索引的意义较低

Cardinality 的统计是由引擎层实现的。InnoDB 中 Cardinality 值是随机对8个叶子节点采样统计，因此表未改动的情况下该值也可能不同。统计 Cardinality 可能会产生较大的性能损耗，因此不应随意进行

MRR / Multi-Range Read

在进行如范围查询这种需要批量回表操作的查询时，若不做任何优化则回表查询会是随机读取，而 MRR 的作用就是在回表前，对这一批查询根据主键进行排序再回表，这样回表就变成了较为顺序的读取，从而提高性能

ICP / 索引条件下推优化

开启 ICP 时，使用辅助索引进行条件查询，会在查询辅助索引的同时就判断是否可以进行 WHERE 条件的过滤，过滤掉已经不符合要求的行后再回表。未开启 ICP 则不会有这步过滤，而只会回表取出所有完整行数据再进行过滤。ICP 相当于辅助索引查询时，利用联合索引已经包含多个列信息的特点，提前进行可能的 WHERE 条件筛选，从而减少不必要的回表来提高性能

适用于 InnoDB 和 MyISAM
显然，只有联合索引才可能在查询时提前过滤

启用 ICP 的情况下利用二级索引查找数据的过程为：

用二级索引查找数据的主键；

如果二级索引记录的元组里的列出现在 where 条件里，那么对元组进行过滤；

对索引元组的主键回表读取完整的行记录；

利用 where 语句的剩余条件对行记录进行过滤；

全文索引

InnoDB 使用 full inverted index 形式的倒排索引，记录了每个单词出现在哪个文档以及在其中的位置

用法…

P6 锁

lock / latch

基本锁

共享锁 (S Lock)：允许事务读
排他锁 (X Lock)：允许事务删除或更新
这里的锁粒度在行级
在申请行锁时，需要先申请表锁（意向锁）

查询操作使用了索引时申请的是行锁，而查询没有索引的列只能全表扫描，因此只能升级为表锁

意向锁

分为意向共享锁 (IS Lock) 和意向排他锁 (IX Lock)
意向锁的粒度是表级，其他与基本锁一致

作用在于提高锁的效率：

当向一个表添加表级 X 锁的时候

如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突

如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果

转自：知乎：InnoDB 的意向锁有什么作用？

一致性锁定 / 非锁定读

一致性非锁定读：

通过 MVCC （多版本并发控制）为数据建立多版本快照，当读取的行有 X 锁时，会读取快照中的历史数据
基于 undo log 实现，因此本身不会有额外的开销
因为快照中的都是历史数据不会被修改，因此不需要上锁，从而大大提高并发性能
是否启用要看事务隔离级别

一致性锁定读：

某些情况需要显式地对读取操作加锁来保证数据一致性
SELECT…FOR UPDATE：对读取的行加上 X 锁
SELECT…LOCK IN SHARE MODE：对读取行加 S 锁
必须在事务中才生效

具体转到Innodb 是如何解决脏读 / 幻读 / 不可重复读的

自增锁

对含有自增属性的表进行插入操作时，为了解决并发问题而存在的特殊表锁

根据参数 innodb_autoinc_lock_mode 共有三种运作模式

传统模式：向包含了自增属性列的表中插入数据时，获得 AUTO-INC 自增锁，在语句执行结束后释放，而不需要等到事务结束。确保自增列是顺序的，但同时只能执行一条插入语句
连续模式：如果插入语句能够提前确定插入的数据量，就不使用自增锁（也有说是立刻获取再释放），而是使用更轻量的互斥锁完成自增操作，不必等待 sql 语句执行完才能进行下一次插入
交叉模式：所有插入都不使用自增锁，而是轻量互斥锁，从而可以近似并发地执行。但因此相同自增值并不连续，因为 AUTO_INCREMENT 的值分配会在多个 INSERT 语句中来回交叉的执行，在并发的情况下无法保证数据一致性

因为交叉模式的特性，当 bin log 格式为 statement 时，由于其记录的是 sql 语句，在主从同步时无法确保数据一致。所以当使用交叉模式时，要么设置为 row 格式，要么确保自己永远不会用到 bin log

外键的锁

Innodb 会自动给外键列建立索引，从而避免外键约束检查时用到表锁
外键值的插入或更新需要查询父表，不使用一致性非锁定读，而是申请 S 锁来避免数据不一致

行锁：记录锁、间隙锁、临键锁

记录锁 Record Lock

单行记录的锁

间隙锁 Gap Lock

对索引的一个范围内锁定
范围两边都是开区间，如 between 10 and 20 锁定范围是(10，20)
只在 RR 可重复读隔离级别有效
间隙锁之间不存在冲突关系，只用于阻止在锁定的范围内插入

丁奇老师的总结

原则 1：加锁的基本单位是 next-key lock

原则 2：查找过程中访问到的对象才会加锁

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止（目前已经在 Mysql 8.0.18 中修复）

临键锁 Next-Key Lock

结合了记录锁与间隙锁而成，锁定范围为左开右闭区间内的索引

临键锁如何解决幻读

Innodb 是如何解决脏读 / 幻读 / 不可重复读的