InnoDB引擎

结构
文件结构
体系架构
- 后台线程
- 内存
Checkpoint机制
- 1. Sharp Checkpoint（关库时的机制）
- 2. Fuzzy Checkpoint（运行时的机制）
Innodb特性
innodb启动、关闭和恢复
日志文件
约束
视图
分区表
- 分区类型
B+ Tree
锁
事务

结构

client层

一般的可访问数据库的程序均为client层

server层

用以解析 SQL 语法、语义、生成查询计划、接管从 MySQL 存储引擎上推的数据进行二次过滤等

engine层

按照 server 层下发的 SQL 请求通过索引、全表扫描等方式访问基表，并将查询到的数据上推给 server 层。
常见的引擎有：Innodb、Myisam等

文件结构

索引组织表

所谓的索引组织表指的是innodb中所有的表都是通过主键顺序组织存放的，每个表都有主键

创建表时如果指定了主键，那么该主键就是就是组织表的键
如果没有显式的定义主键，那么引擎决定主键的顺序为：
- 如果表中有唯一且非空的主键（unique NOT NULL），则该键为主键（当表中有多个符合条件的键时，会使用定义索引时的顺序的第一个）
- 如果没有则自动创建一个6字节大小的指针作主键（即row_id）

表空间

独立表空间

MySQL 默认开启独立表空间

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

当开启后，每个表会独占一个 .ibd 和 .frm 文件，

.frm 文件负责描述表结构，如字段长度等；
.ibd 文件负责存储表数据和索引数据

注：MySQL8.0之后，已经没有了.frm文件。因为原先会把表格结构信息同时存储在data dictonary 和 .frm 文件中，当在写入data dictonary 后宕机，会导致数据不一致。

root@VM-20-9-debian:/var/lib/mysql/test# ls
test_table2.ibd  test_table3.ibd  test_table.ibd

优点：

每个表有自己独立的表空间，可以实现单表在不同数据库中的转移
drop table之后可以对空间进行回收
可以方便的统计一个表的磁盘容量大小
缺点：
可能会出现单表过大的问题，造成读取速度缓慢，因此我们会有分表的优化操作

共享表空间

insert buffer 是一棵B+树，存放在ibdata1共享表空间

需要先关闭独立表空间设置

mysql> set global innodb_file_per_table=OFF;
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

新建表之后，数据表信息会存放在 ibdata(序号) 表中（注意该文件所有数据库共用，位置在mysql目录下，而独立表则是在对应的数据库之下）

查看默认的共享表结构大小：默认大小为12M，并且自动增长

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

可以设置多个文件组成一个共享表空间，同时制定文件的属性，如：

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这样设置将会由/db/ibdata1和/dr2/db/ibdata2 两个文件共同组成，如果两个文件处于不同的磁盘，那么可以提高数据库的整体性能
优点：

可以把表空间分散在多个文件，避免了单表过大而使单个文件过大的问题
缺点：
删除操作会造成空间间隙，无法回收造成磁盘空间浪费
因为无法进行空间回收，因此一些统计表数据大小的工具无法使用（已经无用的表空间可以被重用）

表空间的组成

一个表空间（如.ibd文件），由 segment（段）、extent（区）、page（页） 组成

segment（段）

常见的段有数据段、索引段、回滚段

数据段（Leaf node segment）：即B+树的叶子节点，存放数据
索引段（Non-Leaf node segment）：B+树的非叶子节点，负责存放索引信息
回滚段（rollback segment）：回滚日志存放的地方

extent（区）

由连续页组成，大小固定为1MB，即64个页。每次引擎都会申请4~5个区。
对于每个段，开头时会使用32个页去存储数据，当使用完32个页之后才会创建连续的64个页（即一个区）的磁盘容量，这样做是为了对于小表和Undo类的段，可以申请较小的空间，节约磁盘开销

page（页）

默认大小为16KB

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

常见的页类型：

数据页（B- tree Node）
undo页（undo Log Page）
系统页（System Page）
事务数据页（Transaction system Page）
插入缓冲位图页（Insert Buffer Bitmap）
插入缓冲空闲列表页（Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page 即溢出页）
压缩的二进制大对象页（compressed BLOB Page）

B-tree Node数据页

数据页的组成结构：
Image [2].png

File Header（文件头）

固定大小为38字节，记录页的头信息
Image [3].png

数据页都有指向上下页的指针（实际是页在磁盘上的偏移量），形成双向链表

Page Header（页头）

固定大小为56字节，记录数据页的状态信息
Image [4].png

主要存储指向 User Record 第一个元素的指针和指向第一个被标记为Free的数据页（被删除之后会形成空闲空间链表）

Infimnum 和 Supremum Record（两个虚拟行）

每个数据页中都有两个虚拟的行记录，用来限定记录的边界，Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值，这两个值在页创建时被建立，并且在任何情况下都不会被删除
Image [5].png

User Record 和 Free Space（行记录和空闲空间）

存储的是当前页包含的行记录，这些行记录是以单链表形成组成。同时当插入数据时并不会进行数据的重排，而是根据插入的顺序，只是使用指针进行串联为单链表
Image [6].png

当一条记录被删除后，该空间被加入到空闲空间链表
一开始 User Record 为空，而 Free Space 不为空，每当新插入记录会从 Free Sapce中申请空间，当申请失败后，会新开页

Page Directory（页目录）

存放多个槽位（slots），每个槽位指向一条 User Records中的记录，每隔6条数据会创建一个Slots
Image [7].png

类似于跳表的数据结构，实现二分查找

File Trailer（文件结尾信息）

Image [8].png

用来与 File Header 中的 checksum 和 LSN 比较，从而判断数据页是否完整

row（行）

每个页中可以存放最多7992行记录，每一行最多存储65535个字节（超过了一个页的大小，利用溢出页实现）

Compact格式

Image [9].png

变长字段长度列表：每个变长列的占1字节或者2字节（varchar 最多是65535，因此2字节足够），逆序放置
NULL值列表：有则对应位为1，否则为0（bitmap位图）
记录头信息：固定占用5字节
实际数据：NULL不占该部分任何空间，即NULL除了占用NULL标志位，实际存储不占用任何空间。这点与Redundant格式有重大区别。除了包含实际数据之外，还包含了最近修改此行的事务ID列（6字节）、回滚指针（7字节，指向Undo日志）、以及没有定义主键时自动创建的row_id列（6字节）
示例数据1：

char类型不能占用的长度会使用0x20填充

示例数据2：
Image [14].png
Image [15].png

NULL标志位是06，即 00000110，代表第2列和第3列数据为NULL。可见在实际的数据存储中并没有实际的存储NULL列

Redundant格式

对于varchar类型的NULL值不占用空间，但是Char类型的NULL值会占用空间

行溢出时的处理

在前面《页》介绍时提到了页有“数据页”（B-Tree Page）和“二进制大对象页”（Uncompressed Blob Page，即溢出页）
当未发生行溢出时，数据都是存储在数据页，当发生行溢出时，数据存放在溢出页
Image [16].png

当发生溢出时，原数据页存放前768个字节，剩余字节存放在溢出页
并不是所有的Blob类型都是存放在溢出页，只有溢出时才会存放在溢出页
如果行是Compressed格式和Dynamic格式（新的格式），则Blob类型数据就是都保存在溢出页，原数据页只保存20个字节的指针

char的行存储结构

char(N)中的N指的是存储的字符的长度（4.0版本之前存放的是字节数），所以在不同的字符集情况下，char占用的字节数是不同的

体系架构

Image [17].png

后台线程

Innodb是多线程模型，不同线程负责处理不同的任务

1. Master Thread

主线程，负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新（每秒定时刷新）、合并插入缓冲（merge insert buffer）、redo页的回收

2. IO Thread

Innodb存储引擎使用了大量的异步IO（Async IO），主要分为四种IO：

insert buffer：1个
log：1个
read：有4个
write：有4个

mysql> show engine innodb status;
...
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
...

3. Purge Thread

负责当事务提交之后，回收其使用的undo页（之前是Master Thread负责）

mysql> show variables like 'innodb_purge_threads';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

4. Page Cleaner Thread

负责脏页的刷新（保持LRU List有1024个左右的空闲页可使用，注意区别于Master Thread的定时脏页刷新，此功能之前是Master Thread负责）

mysql> show variables like 'innodb_page_cleaners';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_page_cleaners | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

内存

内存分布图：

1. 缓冲池

负责缓冲一些必要的数据页、索引页、insert buffer等，对于什么时候将页数据刷回磁盘，则是通过Checkpoint机制，因此不是每次也更新则将数据回刷
默认大小为128MB

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

且支持设置多个缓冲池示例，默认为1

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

缓冲池的淘汰机制 LRU List、Free List 和 Flush List

数据库的缓冲池通过优化的LRU算法进行页管理，对于新读取的页，不是直接放置在LRU List首部，而是放入midpoint位置，由innodb_old_blocks_pct控制，即插入位置为LRU List尾部的37%处。midpoint前的数据成为new，后面的数据成为old

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.00 sec)

当进行全表查询时，会一次性把大量的数据加载到缓存中，此时数据还处于midpoint位置，通过innodb_old_blocks_time = N参数限定了处于midpoint之后的数据在被访问到，要等待N ms之后，才会被移动到new区域。这样如果在移动之前就被替换掉了，就不会造成无用的替换操作。

mysql> show variables like 'innodb_old_blocks_time';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
1 row in set (0.00 sec)

old -> new的操作称为：page made young
old -> new但是还处于innodb_old_blocks_time时间的操作称为：page not made young

当数据库启动时，数据最先是加载在Free List，当缓存命中之后，才把数据页从Free List移动到LRU List

当LRU List 中的数据页被修改后，则为脏页，会同时放置在Flush List 中，依赖于 Checkpoint 机制刷回磁盘

mysql> show engine innodb status;
----------------------
BUFFER POOL AND MEMORY
----------------------
Buffer pool size   8192     // LRU List
Free buffers       7101     // Free List
Database pages     1080
Old database pages 413
Modified db pages  0    // Flush List
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0     // made young and not young
0.00 youngs/s, 0.00 non-youngs/s
Pages read 897, created 183, written 549
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1080, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

2. 重做日志缓冲（redo log_buffer）

用于缓冲执行的事务日志，一般不需要太大，因为默认会每秒刷回到磁盘的redo日志文件中。（单位是Byte，默认是16MB）

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

刷回到磁盘的redo日志文件的时机：

Master Thread 每一秒将redo log_buffer 回刷到磁盘redo日志文件（即使事务还没有提交）
每个事务提交时会将redo log_buffer 回刷到磁盘redo日志文件
当redo log_buffer剩余空间小于1/2时，回刷到磁盘

3. 额外的内存池

当对数据结构本身的内存进行分配时，首先从额外的内存池中申请，不足时才从缓冲池中申请。

Checkpoint机制

负责判断脏页回写磁盘的时机

1. Sharp Checkpoint（关库时的机制）

当数据库关闭时把所有的脏页回刷到磁盘中，默认开启

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

2. Fuzzy Checkpoint（运行时的机制）

Master Thread Checkpoint：
每秒定时刷新脏页到磁盘

FLUSH_LRU_LIST Checkpoint：
保持LRU List有1024个左右的空闲页，由purge Thread负责，参数如下

mysql> show variables like 'innodb_lru_scan_depth';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+
1 row in set (0.00 sec)

Async/Sync Flush Checkpoint
当重做日志不可用时，会将一些页刷回磁盘，保证了重做日志的循环可用性

Dirty Page too much Checkpoint
当脏页过多时，强制进行回收，默认参数为90，即超过90%时立即回收

mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 90.000000 |
+----------------------------+-----------+
1 row in set (0.00 sec)

Innodb特性

插入缓冲（insert buffer，提升性能）

当索引是聚集索引时（主键索引），通常如果我们使用自增值作为主键，在插入时按照主键递增的顺序进行插入，那么是不需要磁盘的随机读取的，效率高。

CREATE TABLE t {
    a INT AUTO_INCREMENT,
    b VARCHAR(30),
    PRIMARY KEY(a),
    KEY(b)
};

因此我们应该优先使用自增值作为主键，当然如果使用非自增值依然会在插入时造成随机读取

对于非聚集索引，大部分索引值有随机性，因此会造成大量的随机读（页分裂和B+树节点自旋等）
因此Innodb设计了insert buffer：

对于满足条件的非聚集索引的插入或者更新，不是每一次都直接插入到索引页中，而是先判断是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个insert buffer对象中，再以一定的频率和情况进行insert buffer 和辅助索引叶子节点的合并。

非聚集索引可使用insert buffer 的条件

索引是辅助索引
索引不是唯一的（即定义该字段时不能是 unique，因为如果是唯一的，那么引擎还需要在插入时进行扫描判断是否与已有索引相同，也就没有insert buffer的意义了）

现在升级为了change buffer，可以对insert、update、delete同时缓存，分为insert buffer、purge buffer、delete buffer

内部实现

内部结构是一棵 B+树，且全局唯一，放在共享表空间，默认是ibdata1中。

非叶子节点结构

Image [2].png

space：对应着表空间id（每个表有唯一的space id），4字节
marker：用以区分新老版本，1字节
offset：表示当前索引页在原有表中的偏移量，4字节

根据 (sapce, offset) 进行判断唯一索引

叶子节点结构

相较于非叶子节点，增加了metadata结构和插入数据

metadata：前两个字节为counter，存储了插入该(space, offset)索引页的顺序

根据 (space, offset, counter) 确定一条记录

因此每个数据都会额外增加 4 + 1 + 4 + 4 = 13字节的额外数据

ibuf bitmap

ibuf bitmap存在于每一个ibd文件中，每隔16384个页（innodb中一个页是16KB，因此1638416/1024= *256MB），有一个ibuf bitmap，且每个page占4bits。

4bits的数据代表的意义如下：
Image [3].png

merge insert buffer

将insert buffer的数据合并到磁盘的时机为：

辅助索引页被读取时
即当执行了select操作时，会根据bitmap判断该页是否在insert buffer有缓存，当有缓存时，会一次性merge回写到原有的索引页中
ibuf bitmap追踪到该辅助索引页已无可用空间时
当检测到剩余的页空间小于1/32页时（由4bits数据中的前两位决定），会强制触发一次select操作，即利用上面的规则引发一次merge
master thread
定时的merge insert buffer

两次写（double write，提升可靠性）

MySQL采用 WAL（Write Ahead Log）机制（先顺序写磁盘中的日志文件，再随机写磁盘中的数据页） 实现了在脏页写入磁盘前如果断电的情况下，能够进行回滚或者重执行（Redo Log 和 Undo Log）。
doublewrite 解决的则是当在写入磁盘的过程中，发生宕机情况依然能够恢复，不能只依赖于WAL的原因是此时的宕机会造成原有的磁盘空间破坏。（Redo log 记录的某页某偏移量的当前状态，因此如果是某页已被修改了部分磁盘结构，此时的 Redo log 并不能做到恢复）

内部实现

Image [4].png
由两部分组成：

内存中的 doublewrite buffer，大小为2MB
共享表空间中连续的128个页，大小同样为2MB

执行过程

当脏页需要写回磁盘时，会先通过 memcpy 函数复制到内存中的 doublewrite buffer，然后通过 doublewrite buffer 分两次，每次1MB顺序的写入共享表空间中的 doublewrite 磁盘空间，这个过程写的是连续的磁盘空间，即顺序写，因此开销不大。
当成功写入到共享表空间的 doublewrite 磁盘空间后，再把 doublewrite buffer 中的数据写入到各个表空间中（.ibd文件）。
如果此时发生宕机，那么会从共享表空间中的 doublewrite 恢复。

自适应哈希索引

通常一个B+树会设计为34层的结构，每一层代表了一次IO，因此至少需要34次IO才能读取到值。
innodb会根据查询频率，自动对热点数据建立hash索引，条件如下：

该模式查询了100次
页通过该模式访问了N次，N = 页中记录 * 1/16

注：以(a, b)联合索引为例:
where a = xxx;
where a = xxx and b = xxx;
属于两种模式，因此如果是交替的使用，不会建立hash索引
另外，hash索引只适用于等值查询，对于范围查询不会建立hash索引

异步IO（Asynchronous IO，提升性能）

AIO的优势：

用户无需等待前一个IO回复之后再请求下一个IO
AIO可以合并多个关联IO为一个IO请求，提升效率

刷新邻接页（Flush Neighot Page，提升性能）

当某个脏页刷回磁盘时，会同时检查该页所在区的所有页，如果亦是脏页，则一同刷回磁盘，这种做法的好处是利用了AIO的合并IO的特性，提升了性能

innodb启动、关闭和恢复

通过 **innodb_fast_shutdown** 参数控制关闭时的行为，默认为1

0：表示在关库时需要完成所有的full puge 和 merge insert buffer，并将所有的脏页回刷到磁盘。可能会耗时很久
1：表示不完成full purge 和 merge insert buffer，但是还是会将所有的脏页回刷到磁盘
2：表示不完成full purge 和 merge insert buffer，同时不回刷脏页，而是存入日志文件，等重启之后再恢复

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

日志文件

错误日志

记录了启动、运行、关闭过程中产生的错误信息，通过查看log_error参数可以得知具体的位置。
也可以在错误日志中查看一些warning信息，进行对数据库进行优化

mysql> show variables like 'log_error';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| log_error     | /var/log/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.00 sec)

慢查询日志

负责记录运行时间超过设定的阈值，且可记录没有使用到索引的SQL语句
默认的时间阈值是10秒

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

默认是不开启慢查询日志的，因此需要手动开启

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

同时默认也是不记录不使用索引的SQL语句，需要手动开启

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

查询日志

记录了所有的请求信息，无论是否成功执行，默认的文件名为：主机名.log
默认是关闭的

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |
| general_log_file | /var/lib/mysql/VM-20-9-debian.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)

二进制文件（binary log）

记录对MySQL数据库执行更改的所有操作（注意所有引擎的修改操作都会被记录），不包括select、show等语句。
主要作用有：

恢复（recovery）：可以依赖于bin-log文件进行数据恢复，一般用在主从库的复制同步
复制（replication）：通过复制和执行bin-log文件，一般用在主从库的复制同步
审计（audit）：可通过bin-log中的信息进行审计，判断是否有数据库注入攻击等
查看默认的文件位置

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Image [5].png

一些常用的配置参数

max_binlog_size

默认的大小为1GB，当超过1GB之后会递增最后的序号，如binlog.000002

mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

binlog_cache_size

在一个事务中，未提交的指令会缓存于cache中，默认大小为32KB，且是基于会话session的，即每个线程开启一个事务都会分配一个缓存。
因此当binlog_cache_size过大时，会造成内存资源浪费，过小时会使将部分缓冲写入临时日志，影响性能

mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

sync_binlog

是否每次commit之后即将缓存中的二进制数据写入磁盘

0：不写入，等待由系统自行决定
1：commit时，立即写入，会影响性能

当commit之前写入了日志，但是宕机了，会造成实际数据与bin-log内容不一致，此时依赖于XA事务进行回滚处理
N：commit N次之后再写入

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

binlog-do-db、binlog-ignore-db

只写入或者忽略某个库的日志，默认为空

log-slave-update

对于 master => slave => slave 的架构复制，那么需要开启此选项

binlog_format

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

二进制日志的格式，可设置值有：

STATEMENT：记录逻辑SQL语句
ROW：记录的是表的行更改情况（默认格式），在此模式下，将隔离级别设置为 READ_COMMITTED 可以获得更好的并发性
MIXED：混合模式

重做日志文件（Redo Log File）：保证了持久性

InnoDB是事务的存储引擎，在事务提交时，必须先把该事务的所有日志写入到重做日志（Redo log 和 Undo log）进行持久化。
Redo log 基本上都是顺序写的，在数据库正常运行时不需要对Redo log的文件进行读取操作。
默认情况下在MySQL目录下会有两个重做日志文件：ib_logfile0 和 ib_logfile1，组成一个重做日志文件组，以循环写入的方式运行，即会先写入 ib_logfile0，当文件满了之后，切换写入 ib_logfile1，当 ib_logfile1 也满了之后再切换回 ib_logfile0
Image [6].png

重做日志格式

Image [7].png

redo_log_type：表示重做日志的类型，1字节
space：表空间ID，采用压缩方式，可能小于4字节
page_no：页的偏移量，采用压缩方式
redo_log_body：重做日志的数据部分，恢复时调用相应的函数进行解析

写入方式

写入日志文件的操作会先写入一个重做日志缓冲（redo log buffer，在《内存分布》一节中有提到），然后按照一定的条件顺序地写入内核态的 os buffer，再使用 fsync() 写入磁盘的日志文件。写入的大小是按照一个扇区的大小写入（512个字节），一个扇区的写入是原子的，因此不需要doublewrite
Image [8].png
“一定的条件”的时机是：

Master Thread会每秒将 redo log buffer 的数据回刷到磁盘（不管事务是否已经提交）
由 **innodb_flush_log_at_trx_commit** 参数控制，可选项有：
- 0：表示在提交事务之后不将事务的重做日志回刷到磁盘，只依赖 Master Thread 线程的每秒定时回刷
- 1：表示提交事务之后立即同步回刷，即有fsync的同步操作（会使用 fsync() 把内核态的 os buffer 的缓冲数据刷回到磁盘，并阻塞等待写入结果）（如果是保证ACID中的持久性，必须要选择此项）
- 2：表示异步回刷

Image [9].png

group commit

如果事务是非只读事务，则每次事务提交时都需要进行一次 fsync 操作，以保证日志都成功写入磁盘，这样在宕机后可以使用 redo log 进行恢复。为了提升 fsync 的效率，数据库提供了 group commit 功能，即 一次 fsync 同时将多个事务的 redo log 写入磁盘

和 binlog 的区别

binlog 是 server 层的日志，会记录所有MySQL数据库的相关操作，不管是哪个引擎触发的，而 redo log file 只记录innodb相关的操作，且 binlog日志的记录时机先于 redo log file 的记录时机
binlog记录的是关于每个事务的具体操作内容，基于行格式的记录，即该日志是逻辑日志，如某行记录的每列值是多少。而 redo log file 记录的是每个页（page）更改的物理情况
binlog只在事务提交时回写磁盘，即记录的是成功执行后的事务或语句。而 redo 日志在数据准备修改前和事务提交前都会优先写入（同理的还有undo log），且在整个事务期间会一直有写入操作

LSN序号（日志的逻辑序列号）

该序列号随着日志的写入和而增大，提供以下信息：

数据页的版本信息。
写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。
可知道检查点的位置。

LSN不仅存在于redo log中，还存在于数据页中，在每个数据页的头部，有一个fil_page_lsn记录了当前页最终的LSN值是多少。通过数据页中的LSN值和redo log中的LSN值比较，如果页中的LSN值小于redo log中LSN值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到redo log中记录的LSN值时的状态。

mysql> show engine innodb status;
Log sequence number          18295750  // 当前log buffer中的LSN
Log flushed up to            18295750 // 当前刷新到磁盘中的log buffer的LSN
Pages flushed up to          18295750 // 已经刷新到磁盘数据页的LSN
Last checkpoint at           18295750 // 上一次检查点位置的LSN，每次到达检查点时会把脏页刷回磁盘，此LSN之前的数据都是确定已经写盘成功

数据恢复机制

每次重启innodb时都会进行数据恢复，依赖于 checkpoint 的LSN值，如果该 LSN值小于日志的 LSN，且事务已经提交，则从checkpoint的LSN开始恢复。（redo 日志具有幂等性）

双写机制（2PC，两阶段提交，属于内部XA事务）

redo log日志和 binlog日志属于两次不同的写磁盘时机，因此在生产环境中会出现一方写入成功而另一方写入失败的情况（redo log的写入时机先于 binlog日志），因此使用 2PC 机制来保证二者的同步。
2PC 是一种保证分布式事务数据一致性的协议，它中文名叫两阶段提交，它将分布式事务的提交拆分成了2个阶段，分别是Prepare和Commit/Rollback。

更新流程

Prepare阶段，将Redo Log写入文件，并刷入磁盘，记录上内部XA事务的ID，同时将Redo Log状态设置为Prepare。Redo Log写入成功后，再将Binlog同样刷入磁盘，记录XA事务ID。
Commit阶段，向磁盘中的Redo Log写入Commit标识，表示事务提交。然后执行器调用存储引擎的接口提交事务。这就是整个过程。

可行性验证

假设Redo Log就写入失败，根据之前的介绍可知Redo Log的写入是在事务commit之前，因此事务失败，此时进行回滚。
假设Redo Log刷入成功了，但是还没来得及刷入Binlog MySQL就挂了。此时重启之后会发现Redo Log并没有Commit标识，此时根据记录的XA事务找到这个事务，进行回滚。
如果Redo Log刷入成功，而且Binlog也刷入成功了，但是还没有来得及将Redo Log从Prepare改成Commit MySQL就挂了，此时重启会发现虽然Redo Log没有Commit标识，但是通过XID查询到的Binlog却已经成功刷入磁盘了。此时，虽然Redo Log没有Commit标识，MySQL也要提交这个事务。因为Binlog一旦写入，就可能会被从库或者任何消费Binlog的消费者给消费。如果此时MySQL不提交事务，则可能造成数据不一致。而且目前Redo Log和Binlog从数据层面上，其实已经Ready了，只是差个标志位。

回滚段（rollback Segment）：保证了原子性

与 Redo Log File 存于日志文件不同，Undo存放在数据库内部的一个特殊段（segment）中，称为Undo段（默认共128个），位于共享表空间（亦可配置）。
Undo记录的是逻辑日志，主要作用是实现对事务的回滚和实现MVCC中的非锁定读取（即通过undo读取之前版本的数据）

Undo 存储管理

每个 rollback segment 记录有 1024 个 undo log segment 段，而在每个 undo log segment 段中会进行 undo 页的申请。
特别注意的是，事务在 undo log segment 分配页并写入 undo log 的过程中同样需要写入重做日志，当事务提交时，InnoDB存储引擎会做以下两件事：

将 undo log 放入列表中，以供之后的 purge 线程操作

事务提交之后并不能马上删除 undo log 及 undo log页，因为还有可能其他事务需要通过 undo log 来得到行记录之前的版本（MVCC），故放入一个链表中，是否最终删除 undo log 及 undo log页由 purge thread 判断
判断 undo log 所在的页是否可以重用，若可以则分配给下个事务使用

此外，InnoDB 会对 undo 页进行重用，以防止高并发事务时，每个事务分配一个 undo 页，造成大量的存储空间浪费。当事务提交之后，首先将 undo log 放入链表中，然后判断 undo 页的使用空间是否小于 3/4，若是则表示该 undo 页可以重用，之后新的 undo log 继续记录在当前 undo 页上，由于存放 undo log 的列表是以记录的形式组织（逻辑日志），而 undo 页存放了不同事务的 undo log，因此 purge 操作时需要涉及到离散的磁盘IO，比较缓慢。

undo log 格式

在 InnoDB 存储引擎中，undo log 分为：

insert undo log
update unod log

insert undo log

指的是 insert 操作中产生的 undo log，因为 insert 操作的记录，只对事务本身可见，对其他事务不可见（事务隔离性的要求），故该 undo log 可以在事务提交后直接删除，不会进行 purge 操作。

update undo log

记录的是对 delete 和 update 操作产生的 undo log，可能会被 MVCC 机制利用，因此提交时会放入 undo log 链表，等待 purge 线程最后再进行删除。
Delete 操作在事务中并不是真正的删除掉数据行，而是一种 Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种”假删除”，之后通过purge线程删除。（原因是为了支持MVCC）

update分为两种情况：update的列是否是主键索引。

如果不是主键索引，在undo log中直接反向记录是如何update的。即update是直接进行的。

如果是主键索引，update分两部执行：先删除该行，再插入一行目标行。

与insert undo log不同的，update undo log日志，当事务提交的时候，innodb不会立即删除undo log，会将该事务对应的undo log放入到删除列表中，之后通过purge线程删除。

因为后续还可能会用到undo log，例如隔离级别为repeatable read时的MVCC，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除（即undo log不能删除）,且undo log分配的页可重用减少存储空间和提升性能。

purge 线程的两个主要作用是：清理 undo 页和清除 page 里面的带有 Delete_Bit 标识的数据行

约束

关系型数据库通过约束机制保证了数据的完整性
一般有：

Primary Key
Unique Key
Foreign Key
Default
NOT NULL

视图

一个命名的虚表，由一个SQL查询来定义，可以当做表来使用，使得程序本身不需要关心基表，也起到了一定的安全性作用

分区表

分区是MySQL Server层支持的（8.0改成了由Innodb引擎提供），过程是把一个表或索引分解为多个更小、更可管理的部分，对于访问的用户而言，从逻辑上讲，只有一个表或者一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。
只支持水平分区（即根据行分区），且是局部分区索引，一个分区即存放了数据又存放索引。（全局分区则是数据存放在各个分区，所有数据的索引放在一个对象中）

分区类型

不管何种分区类型，只要表中存在主键或者唯一键，那么分区列必须是唯一索引的一个组成部分
Image [11].png

Range分区

示例：

mysql> create table test_partition(
    -> id INT
    -> )ENGINE=INNODB
    -> PARTITION BY RANGE (id)(
    -> PARTITION p0 VALUES LESS THAN (10),
    -> PARTITION p1 VALUES LESS THAN (20));

注意当插入的值超过分区规则时，会报错，因此要设置最大/最小值

List分区

示例：

mysql> CREATE TABLE test_partition2(
    -> a INT,
    -> b INT)ENGINE=INNODB
    -> PARTITION BY LIST(b)(
    -> PARTITION p0 VALUES IN (1, 3, 5, 7, 9),
    -> PARTITION p1 VALUES IN (0, 2, 4, 6, 8)
    -> );

与Range分区差不多，只是分区的值是离散的，插入不存在的分区值依然会报错

Hash分区

mysql> CREATE TABLE test_partition3(
    -> a INT,
    -> b DATETIME
    -> )ENGINE=INNODB
    -> PARTITION BY HASH(YEAR(b))
    -> PARTITIONS 4;  // 指定分区数

如果未指定PARTITIONS值，则默认是1

Key分区

mysql> CREATE TABLE test_partition4(
    -> a INT,
    -> b DATETIME
    -> )ENGINE=INNODB
    -> PARTITION BY KEY(YEAR(b)) // YEAR函数负责转换为整数
    -> PARTITIONS 4;  // 指定分区数

与hash分区类似，区别在于hash分区可以指定分区函数，而key分区只能使用mysql 提供的分区函数

RANGE COLUMNS 和 LIST COLUMNS分区

与前面的分区类型区别在于，前面的分区类型都必须要是整数，而COLUMNS可以使用非整数类型进行分区
RANGE COLUMNS还支持同时根据多个值进行分区
示例：

mysql> CREATE TABLE test_partition5(
    -> a INT,
    -> b INT,
    -> c CHAR(3),
    -> d INT
    -> )ENGINE=INNODB
    -> PARTITION BY RANGE COLUMNS(a, d, c)(
    -> PARTITION p0 VALUES LESS THAN (5, 10, 'ggg'),
    -> PARTITION p1 VALUES LESS THAN (10, 20, 'mmm'),
    -> PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));

子分区

示例：

mysql> CREATE TABLE test_partition6(
    -> a INT,
    -> b DATE)ENGINE=INNODB
    -> PARTITION BY RANGE(YEAR(b))
    -> SUBPARTITION BY HASH(TO_DAYS(b))
    -> SUBPARTITIONS 2 (
    -> PARTITION p0 VALUES LESS THAN (1990),
    -> PARTITION p1 VALUES LESS THAN (2000),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE);

示例中，先是根据 Range 分区了3个，后又根据 Hash 分区了2个，因此总分区为3*2 = 6 个。

NULL值的处理

RANGE分区：插入NULL值默认是小于任何值，即会插入《RANGE分区》示例中的p0分区
LIST分区：插入时要在分区时显示的指定，否则会插入失败，即《LIST分区》示例中的分区将会插入失败。
HASH分区：将NULL值转换为0值计算
KEY分区：将NULL值转换为0值计算

分区的性能

对于游戏业务来说，一般都是都是针对某个玩家key进行查询，且该key一般是主键，依赖于B+ Tree的特性，本身就可以通过2~3次IO定位到对应的数据页，因此分表对性能的影响不大。（当然如果分区之后从三层B+ Tree 降为了两层B+ Tree，避免了一次IO，还是能够提高性能）。
如果对该主键进行分区，那么对于其他索引的查询，需要查询所有的分区表会大大的降低性能。（如nickname是索引，查询时需要遍历所有的分区表。当然如果本身查询值是非索引，那么分区不分区都需要扫描整个表，性能差别不大）。

B+ Tree

B+ Tree是多路搜索树，根据预设的路数，分为索引页和叶子节点页

树的插入删除流程

插入流程

B+ Tree的插入必须保证插入后叶子结点（数据页）的记录依然有序，因此有三种可能的情况
Image [12].png

为了维护有序性会有拆分页的操作，因此自增有序的主键能够带来更高的性能

删除流程

使用填充因子来控制树的删除变化，50%是填充因子可设的最小值，依然需要考虑三种删除情况：
Image [13].png

聚集索引

对于那些经常访问，但是只需要访问一小部分时，建立B+树索引才有意义。
数据库中的B+ Tree是聚集索引类型，即叶子节点存放的是数据（即数据页）。
树的高度一般是23层，即只需要23次IO即可读取到目标数据所在的数据页（还需要进行类似二分查找的方式才能真正定位到目标数据）。
聚集索引的存储不是物理上的连续，而是逻辑上的连续。数据页之间通过双向指针维护顺序，数据页内的记录也是通过链表的形式组织。
一个表只能有一个聚集索引，因为叶子节点就是数据页，而不能同时有多种数据页的组织方式。

辅助索引

对于辅助索引，叶子节点不包括行记录的全部数据，叶子节点除了包括键值以外，每个叶子节点的索引行中还包含了一个书签（bookmark），该书签用来告诉InnoDB存储引擎哪里可以找到索引相对应的行数据，即对应的是聚集索引键。
辅助索引是非聚集索引，一个表可以有多个辅助索引。

cardinality值

表示索引中唯一值的数目的估计值，优化器会根据该值判断是否使用该索引。当该值远小于总数据行数时，表明有很多重复数据，此时建立索引的意义不大，还空占了磁盘空间。
最佳状态：cardinality / n_row_in_table = 1

mysql> show index from test_table;
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_table |          0 | PRIMARY      |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | name_age_sex |            1 | name        | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | name_age_sex |            2 | age         | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | name_age_sex |            3 | sex         | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

联合索引

联合索引是指对表上的多个列进行索引。
具有最左前缀的原则。
当需要多个值的排序搜索时，联合索引因为本身就有一定的顺序性，因此可以提升查询速度。
假设有a，b两个值，建立两个索引a和（a,b），那么搜索where a = xxx时，优化器选择使用的索引是a，因为一个页可以加载更多的索引，性能更好。
如果搜索的条件是：where a = xxx order by b，那么会优化器使用的就是联合索引（如果不存在联合索引，通过explain可以看到mysql会对查询到的结果进行using filesort排序）

覆盖索引

当从辅助索引中就可以查询到的记录，则不会查询聚集索引，从而减少了大量的IO操作
如 id 为 primary key，name 为辅助索引，那么 select id from xx where name = yy 此语句将会使用覆盖索引
又如 **select count() from xx where name = yy 也不会访问聚集索引

如何实现

对于经常搜索的数据组成联合索引，这样就可以利用覆盖索引的机制，加速查询

索引下推（ICP）

在最左匹配原则中，当遇到范围查询（>、<、between、like）就会停止匹配，这就造成一些查询没能利用联合索引，从而影响查询性能。

// id(name, age)
select * from t_user where name like 'L%' and age = 17;

上面语句在没有索引下推时，只能利用到name，age只能依赖于回表之后再过滤。

当使用 ICP 时，即可把原先位于Server层的where条件过滤下推到引擎层实现，这样减少了引擎层访问基表的次数和Server层访问引擎层的次数
ICP默认是开启的，当关闭之后，其过程如下：

当开启之后，过程如下：

优化器不使用索引的情况

当使用范围查找、join等情况时，尽管查询的值有辅助索引，且该索引是有顺序的，但是因为需要每次读取到值之后回表到聚集索引查询具体的记录，因此效率不高。
当查询大量的数据时（比如20%）将会直接使用聚集索引查询，意味着全表查询。

索引提示

使用 USE INDEX(索引名)：可以强制使优化器使用该索引

SELECT * FROM t USE INDEX(a) WHERE a = 1 AND b = 2;

多范围读（MRR)

具体的实现逻辑是在查询辅助索引时，将查询得到的书签对应的主键索引进行排序，即由有序的辅助索引（对应的主键索引不一定是有序的）-> 有序的主键索引。因此可以把随机的访问转换为顺序访问，提升性能。

全文索引

借助于倒排索引实现，表现形式为：{单词 : (单词所在文档的ID, 在文档中的具体位置)}

锁

根据锁的类型主要可以分为：

共享锁（S Lock）：允许不同事务或进程读取同一个记录，对于select操作会自动添加共享锁，也可以使用 select ... for share（MySQL8.0之后）
排他锁（X Lock）：只允许当前事务或进程独占当前记录，对于 UDI（更新、删除、插入）操作会自动添加排他锁，也可以使用 select ... for update

二者之间的关联性如下：
Image [14].png
在Innodb中还有 意向共享锁（IS Lock） 和 意向排它锁（IX Lock），这两个锁由数据库自己维护的。
一般来说，当我们给一行数据加上共享锁之前，数据库会自动在这张表上加上意向共享锁(IS锁)；当我们给一行数据加上排它锁之前，数据库会自动在这张表上加上意向排它锁(IX锁)。
意向锁可以认为是S锁和X锁在数据表上的标志，通过意向锁可以快速判断表中是否有记录被上锁，从而避免通过遍历的方式来查看表中是否有记录被上锁，提升加锁效率。例如，我们要加表级别的X锁，这时候数据表里如果存在行级别的X锁或者S锁，加锁就会失败，此时直接根据意向锁就能知道这张表是否有行级别的锁。

表锁

MyISAM引擎

MyISAM引擎支持的锁级别，粒度最大，会对SQL语句所要涉及的所有表都加锁，因此 不会出现死锁，但是锁冲突的概率大（毕竟整张表都加锁，会阻塞大量的关于表的操作），开销小，加锁快，并发低，不支持事务

使用MyISAM引擎的表结构

mysql> show create table test_engine;
+-------------+----------------------------------------------------------------------------+
| Table       | Create Table                                                               |
+-------------+----------------------------------------------------------------------------+
| test_engine | CREATE TABLE `test_engine` (
  `id` int NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

MyISAM会对select自动添加共享锁，而对UDI（更新、删除、插入）添加排他锁
同时开启两个进程：
进程1：不具有事务性，因此执行完毕之后会立即释放排他锁

mysql> set autocommit=0;
mysql> update test_engine set age = 1 where id = 1;

进程2：因为是表级锁，因此尽管进程2修改的是id=2的记录，依然会阻塞到进程1执行完毕，因为不具有事务性，因此不会出现因为进程1未commit而导致进程2的操作超时

mysql> set autocommit=0;
mysql> update test_engine set age = 1 where id = 2;

当查询的记录 没有索引 时，添加的锁会是表级锁，使用的是IX、IS锁实现的，因此还是有死锁的问题
表定义：使用InnoDB引擎且没有索引的表结构，并插入数据

mysql> show create table test_no_index;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_no_index | CREATE TABLE `test_no_index` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

InnoDB支持事务，分别用两个事务进行测试
事务1：update操作会自动使用排他锁

mysql> set autocommit=0;
mysql> update test_no_index set name = "6" where id = 2;

事务2：update与事务1不同的行，最终结果显示会等待锁超时，原因是事务1加了一个表级的排他锁而未commit释放

mysql> set autocommit=0;
mysql> update test_no_index set name = "7" where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果某个表中有索引，但是本次查询没有用到索引进行搜索的话，依然会使用表级锁

行锁

只有InnoDB支持行级锁，粒度最小，只针对当前操作行进行加锁，开销大，会有死锁问题，并发度高，支持事务
表定义：使用InnoDB引擎且有索引的表

mysql> show create table test_one_index;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                              |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_one_index | CREATE TABLE `test_one_index` (
  `id` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

使用两个事务进行测试：
事务1：对索引（id）执行update操作，会自动添加行级的排他锁

mysql> set autocommit=0;
mysql> update test_one_index set name = "11" where id = 1;

事务2：当更新的记录的索引值与事务1锁住的行不一样时，不会有阻塞的情况

mysql> set autocommit=0;
mysql> update test_engine set age = 10 where id = 2;

行锁的分类

Record Lock：只锁住当前行
GAP Lock：不锁住当前行，而是锁住前后一定范围的行
Next-Key Lock：锁住当前行和前后一定范围的行

主要依赖于索引B+树，因此可以方便的控制某个范围的行记录

在InnoDB事务下，根据不同的隔离级别会加不同类型的行锁，特别的是在 RR
隔离级别下，通过 Next-Key Lock 解决了幻读的问题

页锁

总体性能介于行锁和表锁之间

锁升级

数据库会把某些粒度低的锁提升为高粒度的锁，从而降低加锁解锁的消耗，如对同一个表的1000个行锁升级为1个表锁

事务

ACID

A（Atomicity）：原子性，通过 undo log 日志实现
C（Consistency）：一致性，是实现 AID 之后所要达成的目的
I（Isolation）：隔离性，通过 MVCC 实现
D（Durability）：持久性，通过 Redo log 日志实现

隐式提交和 DDL

默认情况下 MySQL 是开启自动提交功能的，因此如果没有显式的使用事务开启语句如：begin | start transaction，则每个语句都是一个事务，即 自动提交。
当关闭自动提交功能时（set autocommit = OFF），执行的语句需要显式的调用 commit 才能提交。
但是对于 DDL 语句，会有 隐式提交 的操作，常见的 DDL 语句有： ALTER TABLE…，CREATE TABLE…，DROP TABLE…，TRUNCATE TABLE… 等，而其他的DML语句则需要显式提交。

读取问题和隔离级别

并发读取问题

脏读

指的是在A事务中，A事务中的 select 可以读取到B事务的未提交的数据，此为脏读，因为如果B事务发生回滚，A事务读取到的数据就是脏数据。

不可重复读

指的是在A事务中，A事务两次的 select 读取数据不一致，即数据在事务过程中被其他事务修改了。针对的是其他事务对数据的 update 操作，行级锁解决。

幻读

指的是在A事务中，A事务两次使用 count 统计的数据数量不一致，即数据在事务饿过程中被其他事务进行了删除或者增加。针对的是其他事务的 insert 和 delete 操作，表级锁解决。

隔离级别

Read Uncommitted

读未提交，在该隔离级别下可以读取其他事务中被修改的且还未提交的数据

Read Committed

读已提交，在该隔离级别下可以读取其他事务中执行完成后提交的数据

Repeatable Read

可重复读，在该隔离级别下无法读取其他事务对数据的更新修改

Serializable

序列化，一次性只能有一个事务在执行

在不同的隔离级别下，InnoDB事务可容忍的并发读取问题为：

隔离级别	脏读	不可重复读	幻读	实现方式
Read Uncommitted	+	+	+	读操作不加锁（当前读），写操作添加排他锁，直到事务结束后释放
Read Committed	-	+	+	读操作不加锁（快照读，应用MVCC），写操作添加排他锁，直到事务结束后释放
Repeatable Read	-	-	-	读操作不加锁（快照读，应用MVCC），写操作添加排他锁，直到事务结束后释放。快照读下使用 MVCC 解决幻读，当前读下使用 Next-Key锁解决幻读
Serializable	-	-	-	读操作添加表级的共享锁（当前读），写操作添加表级排他锁，两个锁都是在事务结束后才释放

标准SQL事务下，通过加 行级共享锁 和 行级排他锁 ，实现上述隔离级别下的并发问题，性能较低

悲观锁和乐观锁

悲观锁

所谓的悲观锁就是采取保守策略：“先取锁，成功了才访问数据”，这保证了数据获取和修改都是有序进行的。共享锁和排他锁都是一种悲观锁。

实现流程

数据库悲观锁的加锁流程大致如下：

开始事务后，按照操作类型给需要加锁的数据申请加某一类锁：例如共享行锁等
加锁成功则继续后面的操作，如果数据已经被加了其他的锁，而且和现在要加的锁冲突，则会加锁失败（例如已经加了排他锁），此时需等待其他的锁释放（可能出现死锁）
完成事务后释放所加的锁

优缺点

优点：

保守的策略使获取和修改都是有序进行的，因此适合在 写多读少 的环境中使用。当然使用悲观锁无法维持非常高的性能，但是在乐观锁也无法提供更好的性能前提下，悲观锁却可以做到保证数据的安全性。

缺点：

由于需要加锁，而且可能面临锁冲突甚至死锁的问题
悲观并发控制增加了系统的额外开销，降低了系统的效率，同时也会降低了系统的并行性。

乐观锁

本质上乐观锁不是一种锁，而是CAS（Compare And Swap）无锁算法

实现流程

通过保存数值的历史版本，在对数据进行修改完成重写回时，进行数据版本的比较。当发现在本次修改中间已有其他线程对数据进行修改，则本次修改失败，否则修改成功。

优缺点

优点：

因为乐观锁没有实际加锁，因此不会有死锁问题，没有加锁解锁的额外开销，适用于 读多写少 的环境

缺点：

当处于 写多读少 的环境下，会出现很多写冲突，进而使开销大大增加

MVCC

上面已经提到了标准 SQL 事务是通过加锁的方式实现的不同隔离级别，是悲观锁的应用，具有一定的性能瓶颈。不管是悲观锁还是乐观锁只适合于读多写少或读少写多的其中之一的场景，而MVCC 则是悲观锁和乐观锁的折中方案，具有二者的优点，能够大大的提升数据库的并发性能。

实现方式

1. Undo Log

负责记录数据修改前的状态，是逻辑日志，进而 MVCC 可以实现快照读

2. 行记录的三个隐藏段

db_trx_id：6字节，最近修改该行记录的事务ID（每个事务都会有一个唯一且递增的序列号）
db_roll_pointer：7字节，指向本记录的上一个版本，即指向 Undo Log
db_row_id：隐藏主键，当没有设置主键时，由数据库自动设置

Image [15].png

3. Read View

当事务进行快照读（RC 和 RR级别下）操作时产生的读视图，主要记录三个参数：

trx_ids：当前系统活跃（未提交）事务版本号集合
low_limit_id：创建当前 read view时，系统的最大事务版本号+1
up_limit_id：创建当前 read view 时，系统正处于活跃事务的最小版本号
creator_trx_id：创建当前 read view 的事务版本号

判断流程：

获取当前所要查询的数据行记录的事务ID
行记录的三个隐藏段中的 db_trx_id
数据事务ID < up_limit_id 则显示
证明事务最后修改的事务ID在 read view 生成之前，因此可显示
数据事务ID >= low_limit_id 则不显示
事务最后的修改值是在 read view 生成之后，不可显示
up_limit_id <= 数据事务ID < low_limit_id 则与活跃事务集合 trx_ids 进行匹配
情况1：如果事务ID 不存在于 trx_ids 集合（说明 read view 产生时事务已经commit），可以显示。
情况2：如果事务ID 存在 trx_ids 则说明 read view 产生的时候数据还没有提交，但是如果数据的事务ID等于 creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。
情况3：如果事务ID 既存在 trx_ids 而且又不等于 creator_trx_id 那就说明 read view 产生的时候数据还没有提交，又不是自己生成的，所以这种情况下此数据不能显示。
不满足上述条件，从 undo log 里面取数据历史版本，再重新执行数据匹配

MVCC 在 RC 和 RR 隔离级别下的区别

RC 隔离级别下，每次快照读都会生成新的 Read view；
RR 隔离级别下，整个事务期间只是用第一次快照读产生的 Read View

MVCC是通过 Undo Log 保存数据在某个时间点的快照实现的，每个事务读到的数据项都是一个历史快照，使得整个事务看到的数据是事务启动时的数据状态。

当前读（锁定读）

select … lock in share mode;（读共享锁） select … for update;（读排他锁） update, insert, delete（排他锁） 都是当前读，因为有加锁的缘故，保证了数据记录只有当前事务正在读取。是一种悲观锁的应用。

快照读（一致性非锁定读）

select 就是快照读，前提是当前的事务隔离级别不是 Read Uncommitted（读到的总是最新数据）和 Serializable（会加锁）

优点

性能高，但是需要在每条行记录保存指向 Undo log 的指针，这使得需要额外的存储空间。

分布式事务

XA事务

XA事务允许不同的主机间的不同数据库（如MySQL数据库和Oracle数据库）进行分布式事务。

实现方式

主要分为两部分结构：

事务管理器
本地资源管理器

事务提交流程是两阶段提交：

阶段1：由事务管理器通知所有参与事务的节点的本地资源管理器进入Prepare状态
阶段2：事务管理器通知所有节点 commit/rollback 最终数据

Image [16].png

XA这种方案性能不高