create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成

-- 准备数据

create table stu(
    id int primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄'
) comment '简单学生表';
insert into stu values (1,'tom',1),(3,'cat',3),(8,'rose',8),(11,'jetty',11),(19,'lily',19),(25,'luci',25);

InnoDB引擎 逻辑存储结构

InnoDB引擎的逻辑存储结构(在前面的a_44_0笔记里学过一遍)

1、TableSpece:表空间。存放例如ibd后缀的文件 在一个表空间中会包含若干个Segment段

2、Segment:段。在一个Segment段中包含若干个Extent区

3、Extent:区。一个区包含若干个Page页。一个Extent区的大小是固定的，为1M。一个区中包含64个页

4、Page:页，有数据页、索引页。一个Page页包含若干个Row行。是磁盘操作的最小单。一个Page页的大小是固定的，为16K

5、Row:行。行就是我们存储在表里面具体的一行行的数据。一个Row行包含如下 (1)Trx id:最后一次操作事务的id (2)Roll pointer:指针 (3)col1:字段1 (4)col2:字段2 (5)col3:字段3 (6)......

表空间(ibd文件): 一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。 例如linux的mysql数据文件是在 /var/lib/mysql 目录下，其中的mysql.ibd文件就是表空间文件

段: 分为数据段、索引段、回滚段，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区

区: 表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16k，即一个区中一共有64个连续的页

页: 是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4~5个区

行: InnoDB存储引擎数据是按行进行存放的

Trx_id: 每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。简单理解Trx_id就是最后一次操作事务时的id

Roll_pointer: 每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该 记录修改前的信息。简单理解Roll_pointer就是指针，通过这个指针我们能找到数据在增删改之前的数据

InnoDB引擎 内存架构

架构 MySQL5.5版本之后，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛 架构分为内存结构和磁盘结构，这节课学内存结构

DDL语句的作用: 对数据库对象(数据库、表、列、索引等)进行创建、删除、修改 DML语句的作用: 用于添加、修改、删除和查询数据库记录，并检查数据完整性

内存结构 共有四块区域，分别是缓冲池(buffer pool)、更改缓冲区(change buffer)、日志缓冲区(log buffer)、自适应哈希索引(adaptive hash index)

一、缓冲池: 是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有 数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以page页为单位，底层采用链表数据结构管理page。根据状态，将page分为三种类型: (1)free page: 空闲page，申请了空间但暂时未被使用 (2)clean page: 被使用page，数据没有被修改过，也就是第一次写入数据 (3)dirty page: 脏页，被使用page，数据被修改过，page中的数据与磁盘的数据产生了不一致

二、更改缓冲区: 作用于非唯一的二级索引页。在执行DML语句时，如果这些数据page没有在缓冲池中，不会直接操作操盘，而会将数据变更存 在'更改缓冲区'中，在以后我们需要读取数据时，再将数据合并恢复到缓冲池里面，然后将合并后的数据刷新到磁盘中。

更改缓冲区的意义是什么 与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页， 如果每一次都操作磁盘，会造成大量的磁盘IO。有了'更改缓冲区'之后，我们就可以在缓冲池中进行'合并处理'，减少磁盘IO

三、自适应哈希索引: 用于优化对缓冲池数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则自动建 立hash索引，称之为自适应hash索引

自适应哈希索引，无须人工干预，是系统根据情况自动完成的。hash索引的特点是快，只需要一次匹配(前提是不存在hash冲突)，缺点是不适合做范 围查询，只能做等值匹配 参数: adaptive_hash_index

四、日志缓冲区: 用来保存要写入到磁盘中的log日志数据(redo log、undo log)，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需 要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O 参数: (1) Innodb_log_buffer_size: 缓冲区大小 (2) Innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机。日志在每次事务提交时，写入并刷新到磁盘。0表示每秒将日志写入并刷新到磁盘一次。 1表示日志在每次事务提交时写入并刷新到磁盘。2表示日志在每次事务提交后写入，并每秒刷新到磁盘一次

xxxxxxxxxx
show variables like '%hash_index%'; #自适应哈希索引。查看默认的innodb_adaptive_hash_index为开启状态ON
show variables like '%log_buffer_size%'; #日志缓冲区。查看默认的缓冲区大小为16777216
show variables like '%flush_log%'; #日志缓冲区。查看默认的日志刷新到磁盘时机为1

InnoDB引擎 磁盘架构

架构 MySQL5.5版本之后，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛，分为内存结构和 磁盘结构，这节课学磁盘结构

磁盘结构 共有七块区域，分别是系统表空间(system tablespace)、每张表独立的文件表空间(file-per-table tablespace)、通用表空间(general tablespaces)、 撤销表空间(undo tablespaces)、临时表空间(temporary tablespaces)、双写缓冲区(doublewrite buffer files)、重做日志(redo log)

一、系统表空间: 作用是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数 据。MySQL5.x版本中的系统表空间还包含InnoDB数据字典、undolog。MySQL8.0之后的系统表空间主要包含的就是'更改缓冲区'的数据 参数: innodb_data_file_path

二、文件表空间: 每张表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。对于InnoDB存储引擎来说， 默认的每张表的独立表空间文件是开启的，也就意味着我们建立的每一张表都会生成一个对应的表空间文件，因此表数据并不会在'系统表空间'存放 参数: innodb_file_per_table

三、通用表空间: 需要手动通过 create tablespace语法创建通用表空间。在创建表时，指定这个表使用我们创建好的表空间。 创建表空间语法

create tablespace 表空间名 add
datafile '自定义文件名.ibd'
engine = 引擎;

创建表，并让这个表指向我们创建好的表空间

create table 表名 tablespace 表空间名;

在linux查看我们创建的表空间:

xxxxxxxxxx
cd /var/lib/mysql && ll

四、撤销表空间: MySQL实例在初始化时会自动创建两个默认的undo表空间文件(初始大小16M)，用于存储undo log日志。 这两个默认的表空间文件分别是undo_002、undo_004，这两个文件大小都是16M

五、临时表空间: InnoDB引擎使用'会话临时表空间'和'全局临时表空间'。存储用户创建的临时表等数据

六、双写缓冲区: InnoDB引擎会将数据页从缓冲池(buffer pool)刷新到磁盘前，在刷新之前会先将数据页写入'双写缓冲区'文件 中，便于系统异常时恢复数据。双写缓冲区的两个文件为#ib_16384_0.dblwr、#iib_16384_1.dblwr

七、重做日志: 是用来实现事务的持久性。该日志文件由两部分组成，重做日志缓冲(redo log buffer) + 重做日志文件(redo log)， 前者是在内存中。后者是在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错 误时，进行数据恢复使用。重做日志的两个文件为ib_logfile()、ib_logfile1

show variables like '%data_file_path%';#系统表空间。查询到的ibdata1文件就是'系统表空间文件'
#如果是linux系统，那ibdata1文件的路径是 /var/lib/mysql ，在该路径下就有ibdata1文件

show variables like '%file_per_table%';#文件表空间。默认的每张表的独立表空间文件是开启(ON)的
#如果是linux系统，那么执行cd bilibili/ 就会看到很多ibd结尾的文件，这些文件就是每一张表对应的表空间文件

create tablespace ts_bilibili add datafile 'mybilibili.ibd' engine = innodb;# 通用表空间。创建自定义表空间
#在下面那行创建hello表，并指向我们创建好的表空间，以后hello表产生的数据就只会在这个表空间文件
create table hello(id int primary key auto_increment,name varchar(10)) engine=innodb tablespace ts_bilibili;
#如果是linux系统，执行 cd /var/lib/mysql && ll 就可以看到有一个mybilibili.ibd文件

InnoDB引擎 后台线程

后台线程的作用: 将InnoDB引擎的缓冲池里面的数据，在合适的时机刷新到磁盘文件中。对于InnoDB引擎的后台线程共有四类，如下

1、master thread: 核心后台线程，负责调度其他线程。还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性。还包括脏页的 刷新、合并插入缓存、undo页的回收

2、io thread: 在InnoDB存储引擎中大量使用了aio(aio也就是异步非阻塞io，nio是同步非阻塞，bio同步阻塞)来处理io请求，这样可以 极大地提高数据库的性能，而io thread主要负责这些io请求的回调。不同的线程类型(共10个线程)作用如下

3、purge thread 主要用于回收事务已经提交了的undo log。由于在事务提交之后，undo log可能不用了，所以就用purge thread来回收undo log 简单理解作用就是撤销日志

4、page cleaner thread 协助master thread来进行脏页刷新，page cleaner thread可以减轻master thread的工作压力，减少堵塞

查看InnoDB引擎的状态信息，其中有事务(后面有学)和io(共10行io，也就是10个线程)的情况

show engine innodb status;

InnoDB引擎 事务原理 概述

事务 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求， 即这些操作要么同时成功，要么同时失败

事务四大特性

1、原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败

2、一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态

3、隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

4、持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务原理

1、redo log和undo log这两个日志文件: 作用是保障事务的原子性、一致性、持久性

2、锁机制和MVCC多版本并发控制: 作用是保障事务的持久性

InnoDB引擎 事务原理 redo log

redo log重做日志 记录的是事务提交时数据页的物理修改，是用来实现事务的持久性 该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file)，前者是在内存中，后者在磁盘中。 当事务提交之后会把所有修改信息都存到redo log文件中，redo log用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用

InnoDB引擎 事务原理 undo log

undo log回滚日志 记录数据被修改前的信息，作用是提供回滚和MVCC(多版本并发控制)，是用来实现【事务的原子性】。

undo log和上节课的redo log记录物理日志不一样，undo log是逻辑日志。可以认为当delete删除一条记录时，undo log日志里面 会记录一条对应的insert记录，当update更新一条记录时，undo log日志里面会记录一条相反的update记录。当执行rollback回滚时， 就可以从undo log日志文件中的逻辑记录读取到相应的内容并进行回滚

undo log销毁: undo log日志文件在事务执行时产生，事务提交时，并不会立即删除undo log日志文件，因为这些日志可能还用于MVCC

undo log存储: undo log文件采用'段的方式'进行管理和记录，undo log存放在'rollback segment回滚段'中，内部包含1024个undo log segment

InnoDB引擎 MVCC 概念

当前读 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作， 如:select...lock in share mode(共享锁)，select...for update、insert、delete(排他锁)都是一种当前读

快照读 简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读 对于不同的三个隔离级别，快照读的定义不同，如下

1、Read Committed: 每次执行select语句，都生成一个快照读

2、Repeatable Read:开启事务后第一个select语句才是快照读的地方(MySQL的默认隔离级别是repeatable read)

3、Serializable: 快照读会退化为当前读

MVCC 全称 Multi-Version Concurrency Control ，多版本并发控制。 指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读的功能。MVCC的具体实现，还 需要依赖于3个部分，分别为'数据库记录中的三个隐式字段'、'undo log日志'、'readView'。后面几节课会讲这3个部分

#这节课需要使用终端或命令行。开两个会话 #为了下面描述方便，两个命令行窗口分别叫server端、client端

xxxxxxxxxx
mysql -h192.168.127.138 -uroot -p
use bilibili;

注意不同的隔离级别的'快照读'定义不一样，'当前读'定义是一样的。下面介绍的是MySQL的默认隔离级别repeatable read的快照读和当前读

在server端和client端都开启事务

xxxxxxxxxx
begin;

在server端查看stu表的数据。对于RR隔离级别，第一条正常的查询就是快照读(产生一个快照)。后面如果还有跟这行完全一样的语句，那么后面的就

不算快照读。后面如果还有跟这行完全一样的语句的话，查询的就是快照(第一次查询生成的)里面的数据

xxxxxxxxxx
select * from stu;

在client执行更新语句，并提交事务

xxxxxxxxxx
update stu set name = 'Jsp' where id = 1;
commit;

在server端查看stu表的数据，发现查询不到上面那条更新之后的记录

xxxxxxxxxx
select * from stu;

原因:当前事务隔离级别是repeatable read(RR '可重复读')，所以即使client端提交了事务，server端也是查不到该数据的

解决:在server端，把当前事务级别改为'当前读'，只需要加上锁就会变成当前读，如下

select * from stu lock in share mode;#此时就能查看到client提交事务的数据。加锁的查询就是当前读

InnoDB引擎 MVCC 隐藏字段

数据库记录中的三个隐式字段

记录中的隐藏字段 当我们创建表时，除了我们指定的字段，MySQL会额外在这个表创建三个字段，为DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

如何查看ibd文件，以linux为例，我们需要去磁盘文件查看表结构，执行如下

cd /var/lib/mysql/bilibili && ll

会发现全部是ibd文件，这些ibd文件就是前面学的独立表空间文件

查看ibd文件的数据字典信息，例如查看stu表空间文件，目的就是查看一下stu表里面是否有我们上面讲的隐藏字段

cd /var/lib/mysql/bilibili && ibd2sdi stu.ibd

当结果展示出来的时候，我们滑到顶部，找到"columns":[{},{},...]，表示这张表里面有哪些字段

InnoDB引擎 MVCC undolog版本链

undo log (前面在事务原理那里学过): 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志

1、当执行insert语句的时候，产生的undo log日志只在回滚时需要，在事务提交之后，undo log日志可被立即删除

2、当执行update、delete语句的时候，产生的undo log日志只在回滚时需要，在快照读时也需要，所以undo log日志不会被立即删除

上面的undo log日志是前面学的，这节课学的是下面的undo log版本链

undo log版本链 不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log日志文件有多次迭代，这些undo log日志文件就是该记录生成的 一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。这个undo log多个版本+相连的链路=undo log版本链

当我们去查询某条记录的所有历史版本，这个版本链会给我们返回哪个版本呢，涉及到MVCC实现原理的readview，下节课学

InnoDB引擎 MVCC readview

快照读在执行的时候，到底读取undo log版本链的哪个历史记录，就是由readview来决定的

readview(读视图)是快照读SQL执行时MVCC提取数据的依据(就是依据于下面四个字段)，记录并维护系统当前活跃的事务(未提交的)id。 readview包含了四个核心字段，如下:

工作流程如下。先假设有一个db_trx_id，代表的是当前事务的id，然后进行下面的比对(有4个比对)及比对通过后对应的结果(我用中括号围起来的)， 比对的过程就是版本链数据访问规则:

1、db_trx_id == creator_trx_id ? 【可以访问该版本。原因: 可以说明数据是当前这个事务更改的】

2、db_trx_id < min_trx_id ? 【可以访问该版本。原因: 可以说明数据已经提交】

3、db_trx_id > max_trx_id ? 【不可以访问该版本。原因:可以说明该事务是在readview生成后才开启的】

4、db_min_trx_id <= trx_id <= max_trx_id ? 【如果trx_id不在m_ids中是可以访问该版本的。原因: 可以说明数据已经提交】

不同的隔离级别，生成readview的时机也就不同:

1、read committed隔离级别(InnoDB引擎默认的隔离级别): 在事务中每一次执行快照读时都会生成readview 即每执行一次select语句就是一次快照读。把当前表的隐藏字段的db_trx_id值(由于有多个undo log版本链有多个版本，所以每个版本的db_trx_id值 不一样，db_trx_id值是递增的，先把最大db_trx_id值拿出来，跟上面4行进行比对，如果符合上面将的4个比对其中一种，那么当前select语句查到的 就是最大db_trx_id值对应的版本数据，如果不符合，那么就拿第二大db_trx_id值来跟上面4行进行比对，还不符合就拿第三大db_trx_id值来跟上面4 行进行比对，总会有db_trx_id值是符合的，如果符合，则当前db_trx_id值对应的版本就是select语句查到的数据

2、repeatable read隔离级别: 仅在事务中第一次执行快照读时生成readview，后续复用该readview 跟上面的RC隔离级别的过程一样。唯一的区别是这里不同版本的readview是相同的