Memorykk

never too late to learn

MySQL

Mysql高级

索引是帮助MySQL高效获取数据的排好序的数据结构，将无序的数据编程有序的查询。

  • 二叉树：单链
  • 红黑树：二叉平衡树，提高一倍。旋转成本，树的高度可能会很高，效率很差
  • Hash表：
  • B-Tree：改进红黑树，使高度尽可能小，节点横向扩展，一个存储更多的节点 。

索引数据分布在叶子、非叶子中，索引不重复，索引从左至右递增。

image-20210825202125656

  • B+Tree：叶子节点指针连接，非叶子节点不存储数据，叶子节点包含所有索引，非叶子节点包含一部分索引，冗余索引（只需要冗余主键），数据放在叶子节点。

双向链表便于大于、小于的索引查找

image-20210825202004251

树的高度越小越好，一个节点上存储更多索引（16KB），所以从磁盘一次加载的数据多，二分查找速度快

B+树非叶子节点不存储数据，也就能放更多的索引，存储量大，B树节点有数据就不行。所以决定树的高度：非叶子节点中能放的索引数量。

三层即可存储2000w+索引，在mysql启动时将上层索引加载到内存中，索引取数据只需要大约一次IO。

image-20210825203138095

.frm 表结构

.MYD 表数据

.MYI 表索引

myisam查询过程：条件是否是索引，是则遍历myiB+树，根据地址找到myd磁盘地址。

.frm 表结构

.ibd 数据+索引，表数据也是按照B+组织

innodb推荐建立主键：自增主键：

  • 聚集索引：叶节点包含了完整的数据记录，数据和索引聚集在一起，innodb主键索引。

innodb非主键索引（回表、二次）：叶子节点存储主键值：一致性和节省存储空间。

  • 非聚集索引：索引和数据分开存储。（myisam）效率不如聚集，回表：二次索引（拿到索引再去磁盘遍历索引）

image-20210825204526349

必须要建主键？Mysql会使用主键构建B+树，没有主键Mysql会找一个不重复的列来构建，找不到就生成一个隐藏列rowid来构建，所以节省空间。

为什么不能用UUID？UUID非整型非自增，比较大小效率慢，所以推荐整型。B+范围查找只需两次，非自增只向后插，不用分页，分裂次数少，维护B+结构成本小，推荐自增。

索引就是目录，用空间换时间，B+的节点就是16KB的页，查找就是遍历页上的有序数据，一个页上有多个节点，但是页越来越长，形成链表效率会降低，所以构建目录的目录，即树。

mysql运行时间长之后，缓存加载到内存越来越大，

  • Hash索引：

一次计算。不支持范围查询，hash冲突问题

  • 联合索引：

多个字段，按先后顺序，谁先排好序就以谁的排序为准，后面的忽略。

image-20210825211720812

建立联合索引需要重新构建B+树，所以会和主键索引构建的B+树大量冗余数据，资源浪费，所以可以使联合索引树叶子节点不存储数据，只存储主键，然后回表，是个折中的办法。

image-20210826090232766

主键索引：

image-20210826094000399

select * from t where b>1;(bcd联合索引)

选择索引

利用联合索引可以找到数据，但是查找的是*全部数据，叶子节点只有bcd三列数据，所以需要7次回表查出全部数据，全表扫描走主键只需要四次，更快，所以实际执行的是全表扫描，非联合索引。

  • 如果是select b,c,d,a；那么也能走联合索引，因为想要的四个字段都在一棵树上，叶子是主键a

  • select b;联合索引的叶子能够找出满足要求的数据，就不用走全表扫描。

  • 同样地，如果联合索引回表次数<全表扫描加载页次数，也不用走全扫描。

  • select b from t;没有where条件也能走联合索引，主键索引和联合索引都能找到所需数据，并且如果二者的页数相等，但是因为联合索引叶子节点存储主键，主键索引叶子存储数据，所以联合索引的一页比主键索引的一页存储的数据更多，虽然不完整，只有bcd，但是查询我们只要b就够了，所以不走主键。

  • select * from t order by b,c,d;走bcd不用排序但需要回表八次，全表扫描需要四次，需要排序。因为只有8条数据，排序很快，所以走的是全表扫描。如果select b不需要回表，走的是联合索引。

最左前缀法则：查询从最左前列开始并且二不能跳过中间。也就是联合索引触发必须从包含第一个列的条件开始。

原理：比如age=30，如果不考虑name是否有序而使用age索引的前提是前面的name相同，那么就应该理解为同一列的节点的age是有序的，但是不是有序的，需要全表扫描（从左向右逐个检查）。判断索引是否会用到的原则，当前列是否有序。

Mysql中类型不匹配时，字符转数字，数字型字符会自动类型转成数字，非数字型字符会转成0，即：

select ‘a’=0; true

select ‘b12’=0; true

select ‘1’=0; false

隐式的类型转换可能会导致索引失效。

Mysql5.8之前仅支持升序索引，5.7支持语法层面的降序，但是索引组织仍然是升序的。

B树 B+树

B树每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

20170920132504569

B+树只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

20170920132523536

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指
针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

image-20210827092732211

原因有很多，最主要的是这棵树矮胖，呵呵。一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

那为什么是B+树而不是B树呢，因为它内节点不存储data，这样一个节点就可以存储更多的key。

索引类型

image-20210826161730787

普通可重复，唯一不可重复，唯一可以有null，可以有多个，主键不能有null，且只能有一个。全文索引like关键字。

聚簇 非聚簇

image-20210826163536434

innodb仅叶子节点将索引数据放在一起。

表中除了主键索引的其他索引都是辅助索引，其叶子节点存储地十主键，类非聚簇索引，但存储的不是行地址，而是主键，所以非聚簇索引都是辅助索引。

索引数据结构

image-20210826164046114

image-20210826164125546

image-20210826164153227

索引的设计原则

image-20210826164315221

  • 适合索引的是where
  • 使用前缀段短索引
  • 不能在更新频繁、数据区分度小、重复度高、数据量小的列建索引

锁

image-20210826164836207 image-20210826165201233 image-20210826165417928 image-20210826170133849

1 4 5 7，间隙锁锁住234，临建锁锁住1234.

行锁页锁会出现死锁。

临建锁next key = 记录锁record + 间隙锁gap，三者都属于行锁。

意向锁提高了加锁效率。

innodb默认采用行锁，myisam默认采用表锁。

执行计划

  • id：select的序号，顺序增长，越大优先级越高；

  • select_type：

    • simple：简单查询，不包含子查询和union；
    • primary：复杂查询中最外层的select；
    • subquery：select中的子查询；
    • derived：from中的子查询。

  • table：正在访问哪个表

    • from有子查询时，drivenN，N代表id=N的查询
    • 有union时，union1,2，表示参与union的id

  • type：关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
    依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
    一般来说，得保证查询达到range级别，最好达到ref

    • NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
    • const：通过索引一次命中；
    • system：表中只有一行记录；
    • eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。唯一性索引扫描
    explain select * from film_actor left join film on film_actor.film_id = film.id;
    • ref：相比 eq_ref，非唯一性索引扫描，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 和eq_ref都需要回表
    • range：范围扫描，通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
    • index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
    • ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了

  • key：显示mysql实际采用哪个索引来优化对该表的访问。
    如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用
    force index、ignore index ；

  • possible_keys：显示查询可能使用哪些索引来查找。
    explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
    如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果 。

  • key_len：mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列，特别是联合索引。

    索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

  • ref：在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（等值查询），字段名（例：film.id）

  • rows：mysql估计要读取并检测的行数，越少越好，注意这个不是结果集里的行数

  • filtered：读取rows行，返回x行，x/rows 返回百分比

  • Extra：额外信息

    • Using index：使用覆盖索引 ，性能高

    覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

    explain select film_id from film_actor where film_id = 1;
    • Using where：使用 where 语句来处理结果，和是否读取索引无关
    explain select * from actor where name = 'a';
    • Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
    explain select * from film_actor where film_id > 1;
    • Using temporary：排序、分组时等，mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化 
    explain select distinct name from film;

    • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

    • Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是 

    explain select min(id) from film;

事务

原子性、一致性、隔离性、持久性。

image-20210826205502058

一致性：事务之前id唯一，不能操作之后不唯一了。

image-20210826205907242

readview只针对查询操作，如果在此期间其他事务插入了新数据，就会导致之幻读。配合间隙锁解决。

image-20210826210528829

主从同步

image-20210826211212487

image-20210826211235973

image-20210827101814389

Buffer Pool

客户端 - server（连接器 - 查询缓存 - 分析器 - 优化器 - 执行器） - 存储引擎（查询结果，返回结果集）

Buffer Pool 128MB：查找的结果从磁盘复制到BufferPool，先从pool查，修改先改pool，离散的页数组：

  • free list：记录空闲的页，便于插入

  • flush list：记录脏页，便于后台线程寻找

  • lru list：pool占满时，最近最久未使用的页被淘汰，头部是新的，尾部是旧的

对于全表扫描，大量数据会将pool换掉。Innodb将lru分为热点区5/8，冷数据区3/8，优先淘汰冷数据区，两次访问到数据的间隔>1s，表示一个正常的频率，转移冷数据->热数据，全表扫描时间隔小于1s，就不会发生冷替换热的情况。

redo log

脏页刷新丢失：

1、修改pool产生脏页

2、生成逻辑redo log（mysql内存中脏页持久化到磁盘需要，挂掉之后重新执行redo恢复） -> log buffer

3、redo log 持久化（当事务提交时）

4、bin log 持久化

5、undo log

6、修改成功

redo(log file0,log file1)file满检查点：将log file持久化到磁盘。logfile太小持久化频繁，太大恢复启动慢。

持久化机制：不持久化、立即持久、立即刷新到OS缓存而不立即持久化

image-20210826145638599

执行事务生成bin log

redo bin log
innodb mysql级别
物理的、记录一页的某个位置的数据修改，速度快 逻辑的sql语句，慢。用于主从复制

undo log：反向日志，记录修改之前的数据，回滚使用，实现事物的隔离级别。

double write

（OS中）

innodb数据页16KB，OS页4KB分四次写入。解决问题：如果中途挂了，就不清楚是写入了还是没写入，即没有原子性，所以出现双写缓存。

innodb写入双写缓存即认为成功，并产生redo log，成功之后log失效

刷新一次先写入双写缓存，再写入表空间两部完成。如果第一步挂了 ，这时候可以使用redo log恢复，如果第二部挂了，可以重新从双写缓存拿到完整的数据重新写。

Change Buffer

插入缓冲区：写操作更新数据页、索引页，数据页由日志优化，change优化索引页。

储存在buffer pool（索引页+数据页）中，占25%。存储修改的信息（update语句），修改时数据页被更新，但索引页不更新，暂时存在change buffer中，等到下次查询走索引调用的时候将磁盘中的索引调到pool中，再和change中对应的update整合，拿到正确的索引页，即延迟更新机制，使update效率变高了。

可重复读

开启两个事务，a先读取，b修改此值，a再次读取，结果仍为之前的旧值。

undo实现：每个事务通过链表readview记录和它同时存在的活跃的事务，以及它们的undo log，在第二次查询时，按照记录的undolog，判断生成的事务是否在readview里，若在则执行undolog，这样就可以还原之前的值，然后输出。

image-20210826155926540

可重复读在第一次读时生成readview，后面使用同一个readview；读已提交在别的事务提交之后发生更新，判断undo链发现不在readview里，就直接取最新值。也就是说，在别的事务提交之后，查找的数据还不是最新的，而是事务启动时候的，读取已提交却可以拿到最新数据，所以可重复读相对更严。

隔离级别越来越严，越难读到最新值，效率也就越低。

image-20210826161145756

视图

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

能够对数据进行修改，但是只能修改一张表中的数据。

视图相对于普通的表的优势主要包括以下几项。

  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

存储过程

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

  • 函数 ： 是一个有返回值的过程 ；

  • 过程 ： 是一个没有返回值的函数 ；

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

体系结构

image-20210827093733982

整个MySQL Server由以下组成
Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

连接层、服务层、引擎层、存储层。

MySQL提供了插件式的存储引擎架构，存储引擎是基于表的，而不是基于库的。

MyISAM InnoDB

image-20210826211329868

MYISAM适合查询，InnoDB适合写。

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 有 有 没有 有
事务安全 ==支持==
锁机制 ==行锁(适合高并发)== ==表锁== 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持(5.6版本之后) 支持
集群索引 支持
数据索引 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 高 低 N/A 低 低
内存使用 高 低 中等 低 高
批量插入速度 低 高 高 高 高
支持外键 ==支持==

外键

  • MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 要求父表必须有对应的索引 ， 子表在创建外键的时候， 也会自动的创建对应的索引。

  • MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

存储方式

  • InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

  • 每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

    .frm (存储表定义)；

    .MYD(MYData , 存储数据)；

    .MYI(MYIndex , 存储索引)；

选择

  • InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
  • MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。

改情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

违法最左前缀法则 ， 索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3). 范围查询右边的列，不能使用索引 。

image-20210827095442175

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

4). 不要在索引列上进行运算操作， 索引将失效。

5). 字符串不加单引号，造成索引失效。

image-20210827095508302

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ： 

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

image-20210827095641389

8). 以%开头的Like模糊查询，索引失效。

解决：不使用select *，使用覆盖索引select

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

10). is NULL ， is NOT NULL 有时索引失效。

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

SQL优化

  • 大量插入

    • 按照主键插入
    • 关闭唯一校验

    导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，

    • 手动提交事务

    导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交

  • insert语句

    • 尽量使用一条语句包含多个值插入
    • 开一个事务插入
    • 有序插入

  • 分页

    一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

    • 先查主键，在根据主键查全部记录

      image-20210827100841455

    • 适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

      image-20210827100919667

  • 使用SQL提示

    • USE INDEX：提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
    • IGNORE INDEX：忽略一个或者多个索引
    • FORCE INDEX：强制MySQL使用一个特定的索引

MVCC

https://www.cnblogs.com/xuwc/p/13873611.html

什么是MVCC

MVCC
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。
MVCC是一种多版本并发控制机制。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

当前读 快照读

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

  • 当前读
    像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
  • 快照读
    像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

当前读 快照读 MVCC的关系

  • 准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
  • 而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现
  • 要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的，具体可以看下面的MVCC实现原理

MVCC能解决什么问题

数据库并发场景有三种，分别为：

  • 读-读：不存在任何问题，也不需要并发控制
  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
  • 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

备注：第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了；第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失

MVCC带来的好处是？
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
  • 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

小结一下咯
总之，MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合：

  • MVCC + 悲观锁
    MVCC解决读写冲突，悲观锁解决写写冲突
  • MVCC + 乐观锁
    MVCC解决读写冲突，乐观锁解决写写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

MVCC的实现原理

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。所以我们先来看看这个三个point的概念

隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

  • DB_TRX_ID
    6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  • DB_ROLL_PTR
    7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  • DB_ROW_ID
    6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

在这里插入图片描述
如上图，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

undo log

undo log主要分为两种：

  • insert undo log
    代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
  • update undo log
    事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge

  • 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
  • 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：

一、 比如一个有个事务插入persion表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL

img

二、 现在来了一个事务1对该记录的name做出了修改，改为Tom

  • 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁
  • 然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本
  • 拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，既表示我的上一个版本就是它
  • 事务提交后，释放锁

img

三、 又来了个事务2修改person表的同一个记录，将age修改为30岁

  • 在事务2修改该行数据时，数据库也先为该行加锁
  • 然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
  • 修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录
  • 事务提交，释放锁

img

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

Read View

什么是Read View?

什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)

所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View`遵循一个可见性算法，主要是将`要被修改的数据`的最新记录中的`DB_TRX_ID`（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果`DB_TRX_ID`跟Read View的属性做了某些比较，不符合可见性，那就通过`DB_ROLL_PTR`回滚指针去取出`Undo Log`中的`DB_TRX_ID`再比较，即遍历链表的`DB_TRX_ID`（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的`DB_TRX_ID`, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新`老版本

那么这个判断条件是什么呢？即changes_visible方法（不完全哈，但能看出大致逻辑），该方法展示了我们拿DB_TRX_ID去跟Read View某些属性进行怎么样的比较

在展示之前，我先简化一下Read View，我们可以把Read View简单的理解成有三个全局属性

  • trx_list（名字我随便取的）
    一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
  • up_limit_id
    记录trx_list列表中事务ID最小的ID
  • low_limit_id
    ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1
  • 首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
  • 接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
  • 判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的

整体流程

我们在了解了隐式字段，undo log， 以及Read View的概念之后，就可以来看看MVCC实现的整体流程是怎么样了

整体的流程是怎么样的呢？我们可以模拟一下

  • 当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以Read View记录了系统当前活跃事务1，3的ID，维护在一个列表上，假设我们称为trx_list
事务1 事务2 事务3 事务4
事务开始 事务开始 事务开始 事务开始
… … … 修改且已提交
进行中 快照读 进行中
… … …

Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID，还会有两个属性up_limit_id（记录trx_list列表中事务ID最小的ID），low_limit_id(记录trx_list列表中事务ID最大的ID，也有人说快照读那刻系统尚未分配的下一个事务ID也就是目前已出现过的事务ID的最大值+1，我更倾向于后者 >>>资料传送门 | 呵呵一笑百媚生的回答) ；所以在这里例子中up_limit_id就是1，low_limit_id就是4 + 1 = 5，trx_list集合的值是1,3，Read View如下图

img

我们的例子中，只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务，所以当前该行当前数据的undo log如下图所示；我们的事务2在快照读该行记录的时候，就会拿该行记录的DB_TRX_ID去跟up_limit_id,low_limit_id和活跃事务ID列表(trx_list)进行比较，判断当前事务2能看到该记录的版本是哪个。

img

所以先拿该记录DB_TRX_ID字段记录的事务ID 4去跟Read View的的up_limit_id比较，看4是否小于up_limit_id(1)，所以不符合条件，继续判断 4 是否大于等于 low_limit_id(5)，也不符合条件，最后判断4是否处于trx_list中的活跃事务, 最后发现事务ID为4的事务不在当前活跃事务列表中, 符合可见性条件，所以事务4修改后提交的最新结果对事务2快照读时是可见的，所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本

# 此处有图片 4

在这里插入图片描述

也正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

RC RR

正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

  • 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；
  • 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
  • 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

Study MySQL