MYSQL基础语句本人已在学校的课程中学习过，本章学习MYSQL的进阶学习

MYSQL事务

事务：一组操作的集合，不可分割 事务会把这组集合中的操作当作一个整体一起向系统提交或撤回请求，这些操作要么同成功要么同失败
例如 转账操作 张三 转账给李四 需要两条sql语句 这两条sql语句必须同成功或同失败

事务操作

MYSQL中的事务默认是自动提交的，及每一条sql都会当作一个事务，sql的结束都会自动提交事务

修改MYSQL事务的默认设置

  • SELECT @@autocommit; 查看当前会话事务是否自动提交 1 代表是 0 代表不是
  • SET @@autocommit=0; 设置当前会话事务不自动提交
  • COMMIT ; 提交事务
  • ROLLBACK; 回滚事务
    当我们将事务设置成不自动提交后， 我们需要手动提交事务（执行多条sql语句后手动执行 COMMIT 或者 ROLLBACK）

    命令开启事务

  • START TRANSACTION 或 BEGIN; 开启事务
  • COMMIT ; 提交事务
  • ROLLBACK; 回滚事务
    执行sql语句前需要手动开启事务，需要的sql语句执行结束后同手动执行 COMMIT 或者 ROLLBACK

    事务的四大特性 ACID

  • 原子性(Atomicity):桑作单元，要么全部成功，要么全部失败。事务是不可分割的最小
  • 一致性(Consistency):事务完成时，必须使听有的数据都保持一致状态，
  • 隔离性(lsolation):数据库系统提供的隔离,制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability):事务一旦提交或回滚它对数据库中的数据的改变就是永久的。

    事务并发问题

    这三个问题我学的时候有一点疑惑，事务没有提交的话并不会影响到原来数据表中的数据，但仍是发生了这些问题
    后面学了事务的隔离，

    脏读

    一个事务读到了另一个事务还没有提交的数据
    例如：
    事务A更新了数据，但是并未提交事务，但是事务B此时查询了数据，但是查询的数据是更新过的数据

    但是这个时候查看表的数据仍是更新前的数据，只有当A事务提交了，表中的数据才会更新。
    这种情况在MYSQL的默认隔离中是不会发生的

    不可重复读

    一个事务先后读取同一条记录，但两条读取的数据不同

    不可重复读问题是在解决了脏读的基础上发生的

    例如：
    事务A 查询了两次数据。但是事务B在事务A第一次查询完数据后，对数据进行了更新并提交了事务，事务A第二次查询的数据将会是更新后的数据

    幻读

    一个事务按照条件查询数据，但并没有查询到数据，但是在插入数据时，又发现数据已经存在

    幻读问题是在解决了不可重复读的基础上发生的

    例如：
    事务A 第一步查询数据，发现并没有数据，然后事务B插入数据并提交了事务 此时A第二步插入数据就会报错，但是A第三步查询数据 并没有查询到数据
    (有点像我考试的时候看着题很眼熟 ，明明学过但是大脑有印象但是想不起了 称为幻学)

    事务隔离级别

    MYSQL默认的是Repeatable Read 解决了脏读和不可重复读 有幻读的风险
    从上到下效率依次降低

    MYSQL存储引擎

    存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

    存储引擎更像是规定的规则，存储数据，建立索引，更新/查询数据等都需要遵循指定的存储引擎
    MYSQL在5.5版本后的默认存储引擎为InnoDB
    每张表都可以指定不同的存储引擎，不指定默认为InnoDB。

    show engines 展示数据库支持的存储引擎

    InnoDB

  1. 介绍
    InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MVSOL5.5之后，InnoDB是默认的 MVSOL 存储引擎。
  2. 特点
  • DML操作遵循ACID摸型，支持事务;
  • 行级锁，提高并发访问性能;
  • 支持外键 FOREIGN KEY约束，保证数据的完整性和正确性;
  1. 存储结构
    这里的Row行就是表中每一行的数据 例如

    Page页相当于一张表 里面包含多行数据
    这里的TableSpece表空间指的是MYSQL文件下InnoDB为每张表生成的 xxx.idb文件

    MyISAM

  2. 介绍
    MyISAM是MySOL早期的默认存储引擎。
  3. 特点
  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

    索引

  1. 介绍
    索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

    索引本质是一种数据结构，用来查找数据的

  2. 演示
  • 无索引查找
    当我们查找数据时，会从上到下依次查找所有的数据的age进行对比(全表扫描，效率极低)
  • 有索引查找 (假设索引是二叉树)
    查找数据时会根据索引生成一颗二叉树，如图查询age=45时只需要查询三次即可
  1. 优点
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
  1. 缺点
  • 索引列要占用空间
  • 索引提高查询效率的同时，降低了增删改的效率

    索引结构

  1. 二叉树

    缺点：顺序插入时，会形成链表，查询性能大大降低
  2. 红黑树(变种二叉树)
    大数据量情况下，层级较深，检索速度慢

B-Tree(多路平衡查找树)

在图中的5阶B 树中 分别有5个指针，分别为自己，小于20，20~39，39~62，62~89
根据需要查找的数据选择不同的指针进行索引，大大提高了查询效率
pARMPv6.jpg

B-Tree的构建过程也很有趣，有点像二叉排序树构建过程的升级版，这里我不详细介绍了，因为我不知道怎么描述
要描述清楚需要很多很多照片。可以简单叙述一下，例如5阶的B-Tree 最大KEY为4，当来第5个KEY时，会将中间的KEY值向上移


例如 第一层已经有4个key了，当要插入1200时会有5个key，此时中间的KEY会上移
pARMa2q.jpg
pARM0MV.jpg

B+Tree

B+Tree是B树的一个变种

  1. 特点：

    • 所有数据都会出现在叶子节点
    • 叶子节点形成单向链表

      除了叶子节点，其他节点只起到索引的作用并没有具体数据

      pARU3UP.jpg

  2. 构建过程
    构建过程相比于普通的B-Tree也做出了一些改变
    例如 5阶的B+Tree 最多也只有4个KEY，当我们插入第5个KEY时，中间元素会向上移，但是子节点仍会保留中间KEY
    pARUzIP.jpg
    pARUxat.jpg

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

    MYSQL的B+Tree的叶子节点不在是单向链表，而是双向链表

  4. 为什么InnoDB存储引擎选择使用B+tree索引结构?

    1. 相对于二叉树，层级更少，搜索效率高;
    2. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
    3. 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

普通分类

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT

在InnoDB中根据存储形式又可以将上面索引分为

分类含义特点
聚集索引将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据=必须有，而且只有一个
二级索引将数据存储与索引分开存储，索引结构的叶子节点关联的对应的主键可以有多个
  1. 聚集索引的选定规则
    • 存在主键，主键索引就是聚集索引
    • 不存在主键，第一个唯一索引作为聚集索引
    • 没有主键，也没有合适的唯一索引，则InnoDB自动生成一个rowid作为隐藏的聚集索引

例如图中 在MYSQL中默认的结构为B+Tree， 这里的Id为主键，所以为主键生成了主键索引也是聚集索引，他的叶子节点对应的数据是一整行的数据
而其他字段的索引是二级索引，叶子节点对应的数据是他们的主键

例如我们进行数据查询时，如果不是根据Id来查，会进行回表查询
pARahQg.jpg

索引语法

  1. 创建索引
    1
    create [unique|Fulltext] index 索引名 on 表名(字段名)

字段名可以有多个

  1. 查看索引
    1
    show index from 表名
  2. 删除索引
    1
    drop index 索引名 on 表名

    索引使用

    最左前缀法则

    如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
    如果跳跃某一列，索引将部分失效(后面的字段索引失效)。
    例如：我们先在一个表中创建一个索引TESTINDEX，关联三个属性列
    1
    create  index TESTINDEX on table(A,B,C)
    分别执行三条查询语句
  • select * from table where A=’0’ and B=’1’ and C=’2’;
  • select * from table where A=’0’ and B=’1’;
  • select * from table where A=’0’ ;
    [猜猜结果]

    答案是这三条sql都会走索引，加快查询

    接着看
  • select * from table where B=’1’ and C=’2’;
  • select * from table where A=’0’ and C=’2’;
    [再猜一次]

    答案是第4条sql会完全不走索引，走的是全表查询，第5条sql查询 A=’0’时会走索引，由于中间跳过了一列，所以查询C=2 时索引失效，会走全表查询

    范围查询

    联合索引中，范围查询(>,<)，右边的索引会失效
    例如 select * from table where A=’0’ and B>’1’ and C=’2’;
    出现了范围查询，所以 C=2 会索引失效 走全表查询

    只有 >,< 会失效 >=,<= 并不会失效

    索引失效情况

  1. 不遵循最左前缀法则
  2. 范围查询出现>,<
  3. 对索引列进行了运算
    例如：查询手机号最后两位是81的用户 会对phone进行substring函数运算，所以索引会失效
    1
    2
    3
    SELECT * FROM USER WHERE PHONE = '18639254981';

    SELECT * FROM USER WHERE substring(phone,10,2)='81';

    substring(phone,10,2) 表示从第10 位开始截取两位字符串

  4. 字符串不加引号
  5. 模糊查询

    头部模糊会失效，尾部模糊不会失效

  6. or连接的条件
    • 用or分隔开的条件，如果or前面的条件有索引，后面条件列没有索引，那么涉及的索引都会失效
  7. 数据分布影响
    • 如果MYSQL评估使用索引比全表更慢，则不使用索引
  8. 查询时 条件用and连接，且前后就是单列索引，那么后面条件的索引会失效

    SQL提示

    SQL提示，是优化数据库的一个重要手段，简单来说就是在SQL语句加入一些人为的提示来达到优化的目的。

使用SQL提示的场景是：一个属性列拥有多个索引的情况

  1. use index (建议使用) //MYSQL会判断是否会用
  2. ignore index (忽略某索引)
  3. force index(强制使用某索引)
    以上关键字均用在SQL语句中表名的后面

    覆盖索引

    尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中也经全部能够找到)，减少select*
    如下示例中，第二条select语句就是覆盖索引，查询的列是 id和name，这两个字段都有索引，所以只需要查询一次即可
    但是第三个select语句需要多查询一个字段gender,需要先根据name的索引查找到Arm,然后根据Id进行回表查询
    pAW8AiD.jpg

    前缀索引

  4. 应用场景
    • 字段类型为 varchar text等时，创建普通索引可能字符串很长，这会让索引变得很大，查询时，浪费磁盘IO，影响效率。此时可以将字符传的一部分前缀作为索引
  5. 语法
    相比于正常创建索引多了一个(n),表示截取字段的前几个字符
    1
    create [unique|Fulltext] index 索引名 on 表名(字段名(n))
  6. 前缀长度
  • 可以根据索引的选择性来决定，索引选择性越高则查询效率越高唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    选择性是指 一个字段属性中所不重复数据的数量 和 全部数据的数量 的比值
    例如 select count( distinct substring(A,1,5))/ count(*) from user

    • 该sql语句表示 查询 A属性中前五个字符不相同的数据的数量和总数量的比值
    • distinct 表示去重

    索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。4.
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个7索引最有效地用于查询。

    SQL优化

    插入数据 insert

  8. 批量插入 Insert into tb test values(1,’Tom’),(2,’Cat’),(3, jerry’);
  9. 手动提交事务
    减少了MYSQL开启提交事务的时间
    1
    2
    3
    4
    5
    start transaction;
    insert into tb test values(1,'Tom'),(2,'Cat'),(3, jerry')
    insert into tb test values(4,'Tom'),(5,'Cat'),(6, jerry');
    insert into tb test values(7,'Tom'),(8,'cat'),(9, jerry');
    commit;

  10. 主键顺序插入

  11. 插入数据量极大

    • 使用 load命令将本地文件插入数据库

      注意：本地文件数据需要有一定顺序

      1
      2
      3
      4
      #客户端连接服务端时，加上参数--local-infile
      mysql--local-infile -u root -p
      #设置全局参数local infie为1，开启从本地加载文件导入数据的开关
      set global local infile=1:
    • 使用load将下图数据插入数据库
      pAWfC34.jpg
      1
      2
      #执行load指令将准备好的数据，用逗号隔开，换行代表一行数据的结束 加载到表结构中
      load data local infile '/root/sql1.log' into table `tb user’ fields terminated by ',’ lines terminated by '\n';

      排序优化 order by

      order by 在MySQL底层实现中分为两种
  • Using filesor:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort bufer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  • Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

    注意： order by的优化策略其实就是建立合适的索引，通过索引直接返回，不需要重新排序

  1. 优化
  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。减少 select *。
    • 因为 如果有些字段没有设置索引会回表查询，然后Using filesor排序
  • 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort buffer size(默认256K)。

    分组优化 group by

    group by 默认在MYSQL查询中，会创建一个临时表来存储分组后的数据，利用索引后就可以无需临时表
    所以优化group by 也是创建合适的索引即可
    create index TESTINDEX on table(A,B,C)

    注意： group by使用联合索引时页遵守最左前缀法则，以下sql也遵循最左前缀法则
    create index TESTINDEX on table(A,B,C)
    select A，B count(*) from table where A=’1’ group by B;

    分页查询优化 limit

    我们先来看一条普通的分页查询,查询9000000到9000010的数据 最后耗时19s
    1
    select * from item limit 9000000,10 ;
    优化分页查询，我们可以子查询 最后耗时11s
    1
    select * from item where id in (select id from item order by id limit 9000000,10)

    更新语句 update优化

    学习了这么多优化，针对更新的优化当然也是索引
    值得一提的是

    值得一提的是 在InnoDB存储引擎中 默认是添加行级锁，但是如果更新的where所跟条件字段没有索引，会升级为表锁
    InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁

    锁

    锁分类 按照锁的粒度
分类含义
全局锁锁定数据库中所有的表
表级锁每次操作锁住整张表
行级锁每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
备份当然要加全局锁啦，总不能刚备份完一张表，结果立马来了条新数据，那不就乱套了吗

1
2
3
4
5
6
# 添加全局锁
flush tables with read lock ;
# 数据备份
mysqldump -uroot-p1234 itcast>itcast.sql
# 解锁
unlock tables ;

pAf9Uvd.jpg

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁 主要分为三类

  • 表锁
  • 元数据锁
  • 意向锁

    表锁

    表锁分为
  1. 表共享写锁(read lock)
    • 对一张表加 read lock后所有写操作都会堵塞，只能进行读操作
  2. 表独占写锁(write lock)
    • 加写锁后只有自己可以进行读写操作，其他人的所有操作都会堵塞
      语法：
      1
      2
      3
      4
      # 加锁
      lock tables 表名... read/write;
      # 解锁
      unlock tables

      元数据锁

      MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性

      简单来说 当有未提交的事务时，会对表结构加锁，此时不能修改表结构

      意向锁

      为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
分类特点
意向共享锁(IS)与表锁共享锁(read)兼容，与表锁排它锁(write)互斥
意向排他锁(IX)与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥

可以通过这条sql查看该表的意向锁

1
select object_schema,object_ name, index_name,lock type,lock_ mode,lock_data from performance_schema.data_ locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。
对于行级锁，主要分为以下三类

分类含义
行锁(Record Lock)锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC，RR隔离级别下都支持
间隙锁(GapLock)锁定索引记录间隙(不含该记录)，确保索引记录间除不变，防止其他事务在这个间隙进行inser，产生幻读。在RR隔离级别下都支持
临键锁(Next-Key Lock)每次操作锁住对应的行数据行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

行锁

InnoDB中有两种类型的行锁

  1. 共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  2. 排他锁(X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的排他锁和共享锁
    如图是行锁的加锁方式，说明了在InnoDB中行锁是针对索引，而不是数据记录
    pAfWqy9.jpg

    注意：如果不通过索引条件检索数据，那么行锁会升级为表锁，例如 where所跟的条件字段没有建立索引，那么会升级为表锁

分类行锁类型说明
insert排他锁自动加锁
update排他锁自动加锁
delete排他锁自动加锁
select不加锁
select … lock in share mode共享锁需要手动在select之后加 lock in share mode
select.. for update排他锁需要手动在select后加for update

间隙锁&临键锁

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。
  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
  3. 索引上的范围查询(唯一索引)—会访问到不满足条件的第一个值为止。
    间隙锁：锁的是两个索引之间的间隙 例如 6-12之间的间隙 但是不包括6和12
    pAfhB8S.jpg

    注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

    临键锁：行锁和间隙锁的结合
    pAfh0C8.jpg

事务原理

介绍原理之前我们需要先回顾一下事务的四大特性ACID

ACID特性实现
原子性(Atomicity)操作作单元，要么全部成功，要么全部失败。事务是不可分割的最小redo.log undo.log
一致性(Consistency):事务完成时，必须使听有的数据都保持一致状态redo.log undo.log
隔离性(lsolation)数据库系统提供的隔离,制，保证事务在不受外部并发操作影响的独立环境下运行锁，MVCC
持久性(Durability)事务一旦提交或回滚它对数据库中的数据的改变就是永久的redo.log undo.log

redo log

  • 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
    该日志文件由两部分组成:重做日志缓冲(redolog buffer)以及重做日志文件(redolog file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
  • redo log 的工作流程
    当执行增删改sql时，会先访问内存中的Buffer Pool(缓存池)，如果缓存池中没有要操作的数据，缓存池会访问磁盘中的数据，并磁盘中的数据存入缓存池中，然后在缓存池中进行增删改的操作。增删改的操作结束后，会得到一些修改之后的数据(图中深橙色)，此时缓存池和磁盘数据不一致，我们称为脏页
    ，接着应该有缓存池将脏页数据刷新存储到磁盘中
    pAf4VG8.jpg
    为了防止脏页刷新存储失败我们引入了 redo.log, redolog buffer(日志缓冲) 会记录脏页的数据 并存入磁盘的 redolog file(日志文件)中，当发生脏页刷新失败时，通过redolog file 进行数据恢复
    pAf4RLd.md.jpg

undo log

回滚日志，用于记录数据被修改前的信息
作用包含两个:

  1. 提供回滚
    • undolog和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当 update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

      注意:undo log在事务执行时产生，事务提交时，并不会立即删除undolog，因为这些日志可能还用于MVCC
      Undolog存储:undolog采用段的方式进行管理和记录，存放在前面介绍的 rollbacksegment 回滚段中，内部包含1024个undolog

  2. MVCC(多版本并发控制)。

MVCC 多版本并发控制

基本概念

  1. 快照读：
    • 简单的select(不加锁)就是快照读，读取的是记录数据的可见版本，有可能是历史数据。
  2. 当前读：
    • 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
      [快照读&当前读小例子]

      当我们执行一条 select from user 的sql语句后，另一个客服端执行了 update set name= “小怪兽” where id =1 的sql语句并提交了事务
      此时我们再次执行 select       from user 的结果 id为1 的name 并不等于 “小怪兽”,而是和第一次查询的结果相同。这是因为MYSQL的默认事务隔离级别解决了不可重复读的问题所以读到的数据是一样的，本质上是第一次的查询语句会进行一个快照，后面的查询都将是快照读。想要查到最新数据也很简单，第二次大的sql更改为 select * from user lock in share mode 就可以查询到最新数据了也就是当前读

  3. MVCC：
    • 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。

      隐藏字段

      其实我们创建的每张表中，MYSQL都会为我们自动生成两个或者三个隐藏字段 用于MVCC管理
隐藏字段含义
DB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

undo log版本链

不同事物或者相同事务对同一记录进行修改，会导致该记录的undolog生成一条记录版本链，链表的头部是最新的旧纪录，尾部是最早的旧纪录
例如：
多个事务一次对一条记录进行修改
pAfI13T.jpg
在事务2修改时会现在undolog备份原始记录，然后进行修改，隐式字段DB_TRX_ID 也会记录事务的Id,DB_ROLL_PTR会保存备份的地址
pAfI3gU.jpg
以此类推最终形成一个链表
pAfI8vF.jpg

readView

  1. ReadView是快照读SQL执行时MVCC提供的依据，记录并维护系统当前活跃的事务ID(未提交的ID)

    我们在介绍快照读的时候介绍了快照读，读的可能是历史版本，那么读的是哪一个历史版本，这个问题就是由ReadView提供规则，MVCC根据规则判断读哪一个历史版本

  2. 首先介绍readView的四个字段
字段含义
m_ids当前活跃事务Id集合
min_trx_id最小活跃事务的ID
max_trx_id预分配事务ID，最大事务ID+1
creator_trx_idReadView创造者的事务ID
  1. 版本链访问规则(trx_id指当前事务ID也就是隐藏字段DB_TRX_ID)
规则是否成立结果
trx_id == creator_trx_id?成立，说明数据是当前这个事务更改的。可以访问该版本
trx_id<min_trx_id?成立，说明数据已经提交了。可以访问该版本
min_trx_id <= trx_id<= max trx id?成立，说明数据已经提交。如果trx id不在m ids中是可以访问该版本的
trx_id>max_trx_id?成立，说明该事务是在ReadView生成后才开启。不可以访问该版本
  1. RC(读已提交)隔离级别下 MVCC实现
    RC隔离级别下 在事务中每一次执行快照读时生成ReadView。
    我们仍用undo log 版本的例子来解释
    当事务5执行第一次查询时，事务2已经提交事务，所以m_ids中只有3，4，5(未提交的事务ID)，此时readView的四个字段都已经明确了，
    然后需要将trx_id代入规则判断是否成立，当前记录的trx_id为4代入四条规则均不成立，则根据undo log链进行下一个比对，即trx_id=3，进行对比；
    直到trx_id=2时 第二条规则成立，所以访问的是DB_TRX_ID=2 的历史版本
    pAhSic9.jpg