MySQL基础学习-这次彻底搞懂MVCC-一一网

mysql简介：MySQL 是最流行的关系型数据库软件之一，由于其体积小、速度快、开源免费、简单易用、维护成本低等，在集群架构中易于扩展、高可用，因此深受开发者和企业的欢迎。

innodb

从mysql5.5开始就默认使用Innodb作为引擎，主要讲innodb

innodb架构图

如下图是官方的innodb的架构图，主要分为内存结构和磁盘结构两大部分。

innodb内存空间

内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。

Buffer Pool：缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数
据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO作，提升效率。
- Page管理机制 Page根据状态可以分为三种类型：
  - free page ：空闲page，未被使用
  - clean page：被使用page，数据没有被修改过
  - dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致
  针对上述三种page类型，InnoDB通过三种链表结构来维护和管理
  - free list ：表示空闲缓冲区，管理free page
  - flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。
  - lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。
  改进型LRU算法维护
  - 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
  - 改性LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。
  - 每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。
- 脏页的刷新机制
  1. 当LRU淘汰时会将脏页刷新磁盘
  2. 当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm的值时，这个时候innodb就会开始刷新脏页到磁盘。
  3. 当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm的值，而且还超过innodb_max_dirty_pages_pct时innodb就会进入勤快刷新模式(agressively flush）这个模式下innodb会把脏页更快的刷新到磁盘。
  4. 还有一种情况叫做sharp checkpoint ,当innodb要重用它之前的redo文件时，就会把innodb_buffer_pool中所有与这个文件有关的页面都要刷新到磁盘；这样做就有可能引起磁盘的IO风暴了，轻者影响性能，重者影响可用性。
  参数说明
  - innodb_max_dirty_pages_pct默认值为75，也就是说当脏页比例超过75%时才会进入勤快刷新模式。
  - innodb_max_dirty_pages_pct_lwm默认值是0，0对于innodb_max_dirty_pages_pct_lwm来说是一个特殊值，它表示不启用这个功能；由于没有启用这个功能，也就是说innodb_buffer_pool中的脏页比例会操持在75%左右。
  IO风暴解决方案：
  - 对于第三种情况MySQL的解决思路是这样的，IO风暴是由redo 文件重用时要刷新整个redo文件对应的innodb_buffer_pool页面，也就是说风暴发生的时刻是重用redo日志是发生的；针对这一问题Mysql的解决方案是根据redo的生成速率，实时的刷新innodb_buffer_pool中的页面到磁盘，这样在redo日志文件重用时就不要刷新过多的页面，也就没有IO风暴了。由于页面的刷新是根据redo的生成速率自动调整的，所以这个方案的名字叫innodb_adaptive_flushing；
  - 由于innodb_adaptive_flushing 会自动的刷新，它基本上把2.中的情况也给解决了，所以默认情况下mysql会把innodb_max_dirty_pages_pct_lwm=0，0在这里是一个特殊值，在这里表示关闭的意思。
Change Buffer：写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。
- ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size;当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。
- 写缓冲区，仅适用于非唯一普通索引页，为什么？如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。
- 写缓冲区何时会真正更新数据？
  - 主动：innoDB引擎中有线程会主动的定期做merge操作
  - 被动：在下次查询命中时会先和changebuufer合并，然后放入bufferpool
Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。
- InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
Log Buffer：日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率。
- innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1
  - 0 ：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer–>OS cache，刷盘OScache–>磁盘文件），最多丢失1秒数据
  - 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
  - 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

索引

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

唯一索引

与”普通索引”类似，不同的就是：索引字段的值必须唯一，但允许有空值。

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主
键。

复合(组合)索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。

B树与B+树

B树

索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有
命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+树

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能
- 叶子结点存储的行(row)数据，行数据之间用单向链表存储
- 行数据在mysql中存储在页(page)中，页是一个双向链表

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进
行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

Hash

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引、InnoDB 自适应哈希索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。
InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。
自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

四种日志

redo log(引擎层)

redolog来记录事务修改的数据信息，并且会把redolog持久化到磁盘，数据库意外崩溃时，通过读取redolog恢复最新数据。

redo Log 的生成和释放：

生成：随着事务操作的执行，就会生成redo Log，在事务提交时会将产生redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。redolog设置Innodb_flush_log_at_trx_commit值的三种持久化设置：
- 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
- 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
- 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OScache -> flush cache to disk 的操作。
释放：等事务操作的脏页写入到磁盘之后，redo Log 的使命也就完成了，redo Log占用的空间就可以重用（被覆盖写入）。

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

write pos是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到0号文件开头；
checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
write pos和checkpoint之间还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

undo log(引擎层)

undo log日志主要是做回滚的，同时undo日志也分为两种：

insert undo

是在生成时的undo日志，只在事务回滚的时候需要用的，所以当事务提交之后就会被回收。

update undo

在执行update以及delete操作的时候生成，主要是快照读以及会滚会用到，所以在事务提交之后并不会立刻删除，而是在没有回滚以及快照读的需求下由purge thread统一清理

undo日志其实是一种逻辑存储
- 插⼊时，只要把这条记录的主键值记下来，回滚时根据主键删除记录。
- 修改时，可能只需要把修改的字段的旧值记录下来

binlog(数据库层)

binlog是MySQL Server自己的日志，即Binary log（二进制日志），简称binlog。
binlog文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下：

ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
- 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
- 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
- 优点：日志量小，减少磁盘IO，提升存储和恢复速度
- 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

relay log(数据库层)

其实是主从复制的时候用到，有兴趣的小伙伴可以了解一下。

锁

隔离级别

读未提交

Read Uncommitted 读未提交：解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。

已提交读

Read Committed 读已提交：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。

可重复读

Repeatable Read 可重复读：解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。

可串行化

Serializable 串行化：所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

record lock

行锁，只锁索引一行，RC隔离级别，RR隔离级别。解决了update、delete不可重复读问题。

举例

目前有数据 id=1，name=’张三’，当前有T1和T2两个事务，当T1更新数据name=’李四’之后，T2立刻更新数据name=’王五’提交，此时T1进行查询，本应查到name=’李四’，但查到了name=’王五’

gap lock

间隙锁，锁住索引左右的范围，RR隔离级别。

next key

行锁+间隙锁，锁住索引以及左右的间隙，RR隔离级别。解决了insert不可重复读问题。

举例

当前有数据 id=1 name=’语文’ gradeId=1,id=1 name=’语文’ gradeId=2,还是T1和T2两个事务，T1执行update set gradeId=3 where name=’语文’,T2立刻执行insert into name=’语文’ gradeId=4提交，此时T1再执行select就会出现幻读。

MVCC

mvcc的概念：Multi-Version Concurrency Control，多版本并发控制，mvcc解决了select的不可重复读问题，核心思想是cow(copy-on-write)。

mvcc的实现

根据数据版本链与当前事务的ReadView确定数据可⻅性

mvcc的基础

每个事务有⼀个id：trx_id

事务执⾏过程中，只有在第⼀次真正修改记录时，才会被分配⼀个单独的事务id，这个事务id是递增的。

每一行都有隐式字段

DB_TRX_ID:最近修改/插入事务ID：记录创建这条记录/最后一次修改该记录的事务ID

DB_ROLL_PTR:回滚指针，指向这条记录的上一个版本

DB_ROW_ID:隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

实际还有删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

ReadView

Read View是事务进行快照读生成的读视图，在执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID，每当一个事务开启都会新分配一个trx_id这是自增的。

Read View其实由如下部分组成，分别是：

trx_ids:当前活跃的事务id
min_trx_id：当前活跃事务最小id
max_trx_id：生成readview之后，系统分配的下一个trx_id
creator_trx_id:生成readview的事务id

举例：

目前有1、2、3、5、7、9事务，其中1和7已经提交，这时候9生成readview，则如上属性分别是多少？

trx_ids:2,3,5

min_trx_id:2

max_trx_id:10

creator_trx_id:9

MVCC原理

拿上述例子举例如下情况

当前生成read view事务trx_id=9,此时要查询一行记录，接下来看流程：

首先到3.update的记录，获取trx_id=2，对比min_trx_id 以及creator_trx_id ，如果小于min_trx_id或者等于creator_trx_id则代表生成该read view的时候此事务已提交或者就是当前事务的记录，所以记录应该是可见的，但不小于，则继续对比
将trx_id对比max_trx_id，如果大于，则是生成该read view之后的事务的修改记录，也是不可见的，但此处不小于，则继续下一步
对比trx_id是否在trx_ids中，如果不在则生成该read view的时候事务已提交，是可见的。如果在则是不可见的，继续下一步。
通过DB_ROLL_PTR指针获取上一个记录，并从步骤1开始，直到找到可用的记录，或者没有记录。

为什么有时侯无法解决幻读？

举个?

目前有两个数据：
- id=1,name=’A’;
- id=3,name=’C’;
目前有两个事务T1 T2；
1. 首先T1 select * 可以查出两条记录，并生成readview
2. T2进行 insert into id=2 name=‘B’
3. T1再次执行select * 目前由于MVCC机制还是只能读取两条记录
4. 此时T1执行update set name=‘b’ where id=2
5. T1再次执行select * 就会出现id=2的记录产生幻读
主要是因为T1进行更新后生成新的记录并且trx_id=T1的id，这时候通过MVCC判断时会发现id=2的最新记录是当前事务的是可见的，就读取出来了。