Mysql

最新推荐文章于 2024-08-12 13:39:03 发布

weixin_47142201

最新推荐文章于 2024-08-12 13:39:03 发布

阅读量265

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_47142201/article/details/130178824

版权

文章详细介绍了MySQL数据库中的索引类型，包括B+树、哈希索引和MVCC在不同隔离级别下的工作原理。同时，讨论了事务的ACID特性，以及如何通过锁和MVCC来解决并发问题，如脏读、不可重复读和幻读。此外，还提到了InnoDB存储引擎的行级锁、MVCC在解决幻读问题的角色，以及redolog和undolog的作用。

摘要由CSDN通过智能技术生成

Mysql前置知识

三大范式

第一范式：字段不能再分，保证列的原子性

第二范式：不存在局部依赖，保证数据库表中的每一列都和主键相关

以上这张表不符合第二范式（2NF），虽然有主键，但是实体的属性不完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式：要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即数据表中的每一列数据都和主键直接相关，而不能间接相关

数据库列表类型

字符和字节的关系

所谓字节(Byte)，是计算机数据存储的一种计量单位。一个二进制位称为比特(bit)，8个比特组成一个字节，也就是说一个字节可以用于区分256个整数(0~255)。由此我们可以知道，字节本是面向计算机数据存储及传输的基本单位，后续的字符也就是以字节为单位存储的，不同编码的字符占用的字节数不同。

那么在Java中，除了存储的意义外，Java还将字节Byte作为一种基本数据类型，该数据类型在内存中占用一个字节，用于(-128~127)范围内的整数

计算机底层存储的是字节，字符的设计则是用于展示符号。屏幕上显示的各种文字，数字，符号等就是解码的字符。所以我们说字符是用来显示的符号，它将存储的字节转换成人们看得懂的符号，因此字符的核心就是定义字节与展示符号之间的关系，这种映射关系通常也叫做编码。

编码的由来：为什么要编码呢？前面我们知道数据都是以字节为单位存储在计算机中，字节可以区分256个整数，最容易想到的就是将这256个整数定义为256种状态并分别对应256个字符。但是人类符号太多了，256种是不够的。所以人们想到将多个字节合并起来表示人类语言符号，编码的问题就转化成了字节的组合问题。

所以字符在不同的编码中对应的字节也不同

UTF-8，一个英文字母为一个字节，一个中国汉字为三个字节

1.整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示主键自增：不使用序列，通过auto_increment，要求是整数类型

2.实数类型

实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确类型存储FLOAT和 DOUBLE类型（浮点类型）。DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，DECIMAL里面存储65个数字，DECIMAL对于列的空间消耗比较大，另外DOUBLE比 FLOAT有更高的精度和更大的范围。

3.字符串类型

char

CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

varchar长度可变

因为varchar类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段需要多少个字符时推荐使用varchar类型，这样可以大大地节约磁盘空间，提高存储效率

在内部实现上，既然是变长，VARCHAR需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

日期和时间类型

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25

查询语法

左外链接

-- 左外连接： left outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果select * from emp eleft outer join dept don e.deptno = d.deptno;

右外连接

-- 右外连接： right outer join -- 右面的那个表的信息，即使不匹配也可以查看出效果select * from emp eright outer join dept don e.deptno = d.deptno;

全外连接

-- 全外连接 full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据 -- scott ，40号部门都可以看到select * from emp efull outer join dept don e.deptno = d.deptno;

-- 解决mysql中不支持全外连接的问题：select * from emp eleft outer join dept don e.deptno = d.deptnounion -- 并集去重效率低select * from emp eright outer join dept don e.deptno = d.deptno;

B+树

B树与B+树的差别

1、B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定。

2、B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。

3、B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

同时要注意，MySQL中实现的B+树，叶子节点之间的链表是双向链表，这是一个细微的差别。

另外B* 树的话，与B+树的差别就是在非叶子节点之间，也有相互的指针指向。Oracle中使用的是B** *树。

那为什么MySQL不用B树而使用B+树呢？

因为B数据每个节点都存储数据，每次查询的数据大小固定，就会造成每次查询返回的数据的条数变少，相同数据规模的情况下B树会增加io次数，而B+树，则数据量较小，一次可以返回多条记录，io次数较少
范围查询B+树明显优于B树

为什么关系型数据库都选择了B+树，这个和磁盘的特性有着非常大的关系。

为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，这个称之为预读。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，页大小通常为4k。

按照磁盘的这种性质，如果是一个页存放一个B+树的节点，自然是可以存放很多的数据的，比如InnoDB里，默认定义的B+树的节点大小是16KB，这就是说，假如一个Key是8个字节，那么一个节点可以存放大约1000个Key，意味着B+树可以有1000个分叉。同时InnoDB每一次磁盘I/O，读取的都是 16KB的整数倍的数据。也就是说InnoDB在节点的读写上是可以充分利用磁盘顺序IO的高速读写特性。

同时按照B+树逻辑结构来说，在叶子节点一层，所有记录的主键按照从小到大的顺序排列，并且形成了一个双向链表。同一层的非叶子节点也互相串联，形成了一个双向链表。那么在实际读写的时候，很大的概率相邻的节点会放在相邻的页上，又可以充分利用磁盘顺序IO的高速读写特性。

所以我们对MySQL优化的一大方向就是 尽可能的多让数据顺序读写，少让数据随机读写 。

MySQL中的索引

聚集索引

InnoDB中使用了聚集索引（主键索引），就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。

因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数据。当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索引通常可以节省磁盘IO操作。

如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

创建表id为主键，age列为普通索引

主键索引数据结构图：

辅助索引

除了聚簇索引之外的索引都称为辅助索引，InnoDB的辅助索引只会存储主键而非磁盘地址

以age列为例子，age索引的索引结构图如下图

组合索引

1，组合索引的存储结构

我们在使用索引时，组合索引是我们经常用的索引类型。那组合索引时如何构建的，查找的时候又是如何进行查找的呢？

表t_multiple_index，id为主键列，创建了一个联合索引idx_abc(a,b,c)，构建的B+树索引结构如图所示。索引树中节点中的索引项按照（a，b，c）的顺序从大到小排列，先按照a列排序，a列相同时按照b

列排序，b列相同按照c列排序。在最地城的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序。

2，组合索引查找方式

select * from t_multiple_index where a =13 and b=16 and c=4

先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，14>13,走左路。（1

次磁盘IO）

将左子树节点加载到内存中，先比较a列，a=13，比较b列b=14，14<16，走右路，向下检索。（1

次磁盘IO）

达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）第一项（13,14,3,id=4）：先比较a列，a=13，比较b列b=14，b!=16不符合要求，丢弃。第二项（13,14,4,id=1）：一样的比较方式，a=13，b=16，c=4 满足筛选条件。取出索引data值即主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）第三项（13,14,5,id=3）：a=13，b=16，c!=4 不符合要求，丢弃。查询结束。
最后得到1条符合筛选条件，将查询结果集返给客户端。
最后根据主键值等于1，回表查询主键，获取id=1的行记录

回表

根据辅助索引树查找数据时，首先通过辅助索引找到主键值，然后需要再根据主键值到主键索引中找到主键对应的数据。这个过程叫做回表

覆盖索引

使用辅助索引查询比基于主键索引的查询多检索了一棵索引树，那是不是所有使用辅助索引的查询都需要回表查询呢？

如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了我们的查询需求，这时MySQL就不会白费力气的回表查询，这中现象就是覆盖索引。使用explain工具查看执行计划，可以看到extra中“Using index”，代表使用了覆盖索引。

哈希索引

InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3、4层,故需要3、4次的IO查询。

所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。

InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。

索引优化建议

频繁出现在where 条件判断，order排序，group by分组字段
select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
多表join关联查询，on字段两边的字段都要创建索引
表的索引个数不能太多，占用磁盘空间，影响更新和插入的时间（需要更新所有的索引树）
频繁更新的字段，区分度低的字段，数据类型太大的字段都不适合建立索引。
尽量创建组合索引，而不是单列索引。

优点：一个组合索引等同多个索引效果，节省空间

可以使用覆盖索引

索引正确使用

1，全值匹配我最爱

2，最佳左前缀法则（带头索引不能死，中间索引不能断）

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

3，不要在索引上做计算

4，尽量使用覆盖索引

5，索引字段上不要使用不等

索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描

6，索引字段上不要判断null

索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描

7，索引字段使用like不以通配符开头

索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描

总结：

Explain执行计划

列的含义如下🆔 SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

type参数详解

显示的是单位查询的连接类型或者理解为访问类型

结果值从最好到最坏依次是：出现比较多的是system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

system

表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，这个也可以忽略不计

const（重要）

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

eq_ref（重要）

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref（重要）

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range（重要）

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index（重要）

select结果列中使用到了索引，type会显示为index。

全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all

最熟悉的全表扫描，将遍历全表以找到匹配的行

extra

Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。需要优化sql.

Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。需要优化SQL

using index（重要）

查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！如果同时出现Using Where ，说明索引被用来执行查找索引键值如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作。

using where（重要）

表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；

Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

impossible where

where子句的值总是false ，不能用来获取任何元组

mysql事务

事务是数据库管理系统执行过程中的一个逻辑单位（不可再进行分割），由一个有限的数据库操作序列构成，要不全部成功，要不全部不成功。

事务特性

事务具有4个属性：原子性（atomicity），一致性（consistency），隔离性（isolation），持久性(durability)。这四个属性通常称为ACID特性。

事务并发引发的问题

脏读

当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读

1、在事务A执行过程中，事务B对数据资源进行了修改，事务B读取了事务A修改后的数据。2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。这种读取到另以个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读

当事务内相同的记录读取两次，且两次得到的结果不同时，此现象称为不可重复读

幻读

在事务执行的过程中，另外一个事务将记录添加到正在读取的事务中时，会发生幻读

不可重复读和幻读的区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除

解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表

事务的隔离级别

事务的传播行为

事务传播行为类型	说明
PROPAGATION_REQUIRED	如果当前没有事务，就创建一个事务，如果已经存在一个事务，就加入到这个事务中。这是最常见的选择（默认）
PROPAGATION_SUPOORTS	支持当前事务，如果当前没有事务，就以非事务方式执行
PROPAGATION_MANDATORY	使用当前的事务，如果当前没有事务，就抛出异常。
PROPAGATION_REQUIRES_NEW	新建事务，如果当前存在事务，把当前事务挂起
PROPAGATION_NOT_SUPPORTED	以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
PROPAGATION_NEVER	以非事务方式执行，如果当前存在事务，则抛出异常
PROPAGATION_NESTED	如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作

REQUIRED,SUPPORTS,MANDATORY

如果程序正常执行，内层事务不会提交，在外部事务中同一提交。如果内层事务，或者外部事务出现异常情况，那么会在外层事务的处理中统一进行回滚

REQUIRES_NEW

正常执行，则内层事务优先提交，然后外层方法再提交；

如果内层方法异常，内部事务回滚，外部事务也会回滚

如果外层方法异常，内部事务正常提交，外部事务回滚

NOT_SUPPORTED

外层方法有事务，直接挂起，内层方法没有异常情况的话直接顺利执行

如果内层方法有异常的话，那内层方法中已经执行的数据操作不会回滚，而外层方法的事务会回滚

如果外层方法中出现了异常，内部事务不会回滚，只有外部事务回滚

@Transactional 注解的一些参数和参数的含义@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.READ_UNCOMMITTED

MVCC

全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。MVCC最大的好处：读不加锁，读写不冲突(快照读)

MVCC在mysql中的实现依赖的是undo log与read view

InnoDB的MVCC是通过在每行记录后面保存两个隐藏列来实现的。这两个列，一个保存了行的事务ID，一个保存了行的回滚指针。每开始一个新事务，都会自动递增产生一个新的事务id。事务开始时刻会把事务id放入当前事务影响的行事务id中，当查询时需要用当前事务id和每行记录的事务id进行比较

undo log

undo日志：为了实现事务的原子性，InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来。一般每对一条记录做一次改动，就对应着一条undo日志，但在某些更新记录的操作中，也可能会对应着2条undo日志。一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的undo日志，这些undo日志会被从0开始编号，也就是说根据生成的顺序分别被称为第0号undo日志、第1号undo日志、...、第n号undo日志等，这个编号也被称之为undo no。）

undo log又分为insert undo log与 update undo log

为了说明这个问题，我们创建一个演示表

CREATE TABLE teacher (number INT,name VARCHAR(100),domain varchar(100),PRIMARY KEY (number)) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入一条数据：

INSERT INTO teacher VALUES(1, '李瑾', 'JVM系列');

假设插入该记录的事务id为60，那么此刻该条undo log记录的示意图如下所示

假设之后两个事务id分别为80、120的事务对这条记录进行UPDATE操作，操作流程如下：

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。

ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，直接读取记录的最新版本就好了。对于使用

SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录.

对于使用 READ COMMITTED 和REPEATABLE READ 隔离级别的事务来说，就需要用到我们上边所说的版本链了。核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的。

所以设计 InnoDB 的设计者提出了一个ReadView的概念，这个 ReadView 中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids，并确定三个变量的值:

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。creator_trx_id：表示生成该ReadView的事务的事务id。

READ COMMITTED

脏读问题解决

READ COMMITTED 隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

READ COMMITTED —— 每次读取数据前都生成一个ReadView

比方说现在系统里有两个事务id分别为80、120的事务在执行：Transaction 80

UPDATE teacher SET name = '马' WHERE number = 1;UPDATE teacher SET name = '连' WHERE number = 1;...

此刻，表teacher 中number为1的记录得到的版本链表如下所示：

假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

使用READ COMMITTED隔离级别的事务

BEGIN;SELECE1：Transaction 80、120未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

第1次select的时间点如下图：

这个SELECE1的执行过程如下：在执行SELECT语句时会先生成一个ReadView：

ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'连'，该版本的trx_id值为80，在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问），根据roll_pointer跳到下一个版本。下一个版本的列name的内容是'马'，该版本的trx_id值也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。下一个版本的列name的内容是'李瑾'，该版本的trx_id值为60，小于ReadView中的min_trx_id值，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'李瑾'的记录。

所以有了这种机制，就不会发生脏读问题！因为会去判断活跃版本，必须是不在活跃版本的才能用，不可能读到没有 commit的记录。

REPEATABLE READ

REPEATABLE READ 解决不可重复读问题

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了

MVCC下的幻读解决和幻读现象

REPEATABLE READ隔离级别下MVCC可以解决不可重复读问题，那么幻读呢？MVCC是怎么解决的？幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这个记录来自另一个事务添加的新记录。

我们可以想想，在REPEATABLE READ隔离级别下的事务T1先根据某个搜索条件读取到多条记录，然后事务T2插入一条符合相应搜索条件的记录并提交，然后事务T1再根据相同搜索条件执行查询。结果会是什么？按照ReadView中的比较规则(后两条)：3、如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。4、如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id < trx_id < max_trx_id)，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

不管事务T2比事务T1是否先开启，事务T1都是看不到T2的提交的。请自行按照上面介绍的版本链、ReadView以及判断可见性的规则来分析一下。

在REPEATABLE READ隔离级别下InnoDB中的MVCC 可以很大程度地避免幻读现象，而不是完全禁止幻读。

MVCC总结

从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的

SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成 ReadView 的时机不同， READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView ，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个 ReadView ，之后的查询操作都重复这个 ReadView

就好了。

Mysql中的锁

并发事务问题：一个事务进行读取操作，另一个进行改动操作，这种情况可能会发生脏读，不可重复度，幻读的问题

方案一：读操作MVCC，写操作进行加锁

事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，也称之为快照读，但是往往读取的是历史版本的数据。所有普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下都算是一致性读。

愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些情况下，要求必须采用加锁的方式执行。

方案二：读写操作都采用锁

使用场景：

业务背景下不允许读取记录的历史版本，而是每次都必须去读取记录的最新版本

比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。------读的时候加锁，不允许读的时候再有其他事物操作

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。采用加锁的方式解决幻读问题就有不太容易了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点麻烦—— 因为并不知道给谁加锁。InnoDB中是如何解决的，我们后面会讲到。

锁定读（LockingReads）/LBCC

也称当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

哪些是当前读呢？select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁/独占锁)、delete (排他锁)、串行化事务隔离级别都是当前读。

当前读这种实现方式，也可以称之为LBCC（基于锁的并发控制，Lock-Based Concurrency Control），怎么做到？

共享锁和独占锁

在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，MySQL中的锁有好几类：

共享锁英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。

假如事务E1首先获取了一条记录的S锁之后，事务E2接着也要访问这条记录：

如果事务E2想要再获取一个记录的S锁，那么事务E2也会获得该锁，也就意味着事务E1和E2在该记录上同时持有S锁。

独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

如果事务E2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务E1提交之后将S锁释放掉。

如果事务E1首先获取了一条记录的X锁之后，那么不管事务E2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务E1提交。

所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的，画个表表示一下就是这样：

X 不兼容X 不兼容S

S 不兼容X 兼容S

写操作的锁

平常所用到的写操作无非是DELETE、UPDATE、INSERT这三种：

DELETE：

对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。

INSERT：

一般情况下，新插入一条记录的操作并不加锁，InnoDB通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。当然，在一些特殊情况下INSERT操作也是会获取锁的，具体情况我们后边再说。

UPDATE：**

在对一条记录做UPDATE操作时分为三种情况：

1、如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

2、如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。

3、如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

给表加S锁

如果一个事务给表加了S锁，那么：

别的事务可以继续获得该表的S锁

别的事务可以继续获得该表中的某些记录的S锁

别的事务不可以继续获得该表的X锁

别的事务不可以继续获得该表中的某些记录的X锁

给表加X锁

如果一个事务给表加了X锁（意味着该事务要独占这个表），那么：

别的事务不可以继续获得该表的S锁

别的事务不可以继续获得该表中的某些记录的S锁

别的事务不可以继续获得该表的X锁

别的事务不可以继续获得该表中的某些记录的X锁。

为了更好的理解这个表级别的S锁和X锁和后面的意向锁，我们举一个现实生活中的例子。我们用曾经很火爆的互联网风口项目共享Office来说明加锁：

共享Office有栋大楼，楼自然有很多层。办公室都是共享的，客户可以随便选办公室办公。每层楼可以容纳客户同时办公，每当一个客户进去办公，就相当于在每层的入口处挂了一把S锁，如果很多客户进去办公，相当于每层的入口处挂了很多把S锁（类似行级别的S锁）。

有的时候楼层会进行检修，比方说换地板，换天花板，检查水电啥的，这些维修项目并不能同时开展。如果楼层针对某个项目进行检修，就不允许客户来办公，也不允许其他维修项目进行，此时相当于楼层门口会挂一把X锁（类似行级别的X锁）。

上边提到的这两种锁都是针对楼层而言的，不过有时候我们会有一些特殊的需求：

A、有投资人要来考察Office的环境。

投资人和公司并不想影响客户进去办公，但是此时不能有楼层进行检修，所以可以在大楼门口放置一把S锁（类似表级别的S锁）。此时：

来办公的客户们看到大楼门口有S锁，可以继续进入大楼办公。

修理工看到大楼门口有S锁，则先在大楼门口等着，啥时候投资人走了，把大楼的S锁撤掉再进入大楼维修。

B、公司要和房东谈条件。

此时不允许大楼中有正在办公的楼层，也不允许对楼层进行维修。所以可以在大楼门口放置一把X锁（类似表级别的X锁）。此时：

来办公的客户们看到大楼门口有X锁，则需要在大楼门口等着，啥时候条件谈好，把大楼的X锁撤掉再进入大楼办公。

修理工看到大楼门口有X锁，则先在大楼门口等着，啥时候谈判结束，把大楼的X锁撤掉再进入大楼维修。

意向锁

但是在上面的例子这里头有两个问题：

如果我们想对大楼整体上S锁，首先需要确保大楼中的没有正在维修的楼层，如果有正在维修的楼层，需要等到维修结束才可以对大楼整体上S锁。

如果我们想对大楼整体上X锁，首先需要确保大楼中的没有办公的楼层以及正在维修的楼层，如果有办公的楼层或者正在维修的楼层，需要等到全部办公的同学都办公离开，以及维修工维修完楼层离开后才可以对大楼整体上X锁。

我们在对大楼整体上锁（表锁）时，怎么知道大楼中有没有楼层已经被上锁（行锁）了呢？依次检查每一楼层门口有没有上锁？那这效率也太慢了吧！于是InnoDB提出了一种意向锁（英文名：Intention Locks）：

意向共享锁 ，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

意向独占锁 ，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

InnoDB中的行级锁

行锁，也称为记录锁，顾名思义就是在记录上加的锁。但是要注意，这个记录指的是通过给索引上的索引项加锁。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

如果有客户到楼层中办公，那么他先在整栋大楼门口放一把IS锁（表级锁），然后再到楼层门口放一把S锁（行锁）。

如果有维修工到楼层中维修，那么它先在整栋大楼门口放一把IX锁（表级锁），然后再到楼层门口放一把X锁（行锁）。

之后：

如果有投资人要参观大楼，也就是想在大楼门口前放S锁（表锁）时，首先要看一下大楼门口有没有IX锁，如果有，意味着有楼层在维修，需要等到维修结束把IX锁撤掉后才可以在整栋大楼上加S锁。

如果有谈条件要占用大楼，也就是想在大楼门口前放X锁（表锁）时，首先要看一下大楼门口有没有IS锁或IX锁，如果有，意味着有楼层在办公或者维修，需要等到客户们办完公以及维修结束把IS锁和IX锁撤掉后才可以在整栋大楼上加X锁。

注意：客户在大楼门口加IS锁时，是不关心大楼门口是否有IX锁的，维修工在大楼门口加IX锁时，是不关心大楼门口是否有IS锁或者其他IX锁的。IS和IX锁只是为了判断当前时间大楼里有没有被占用的楼层用的，也就是在对大楼加S锁或者X锁时才会用到。

总结一下：IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

InnoDB中的行级锁

不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。

记录锁是有S锁和X锁之分的，当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁；

Gap Locks

我们说MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks的锁，官方的类型名称为：LOCK_GAP，我们也可以简称为gap锁。

间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁。

会话1开启一个事务，执行

begin;
update teacher set domain ='JVM' where number='6';

会对2~6之间和6到10之间进行上锁。

如图中为2~6和 6 ~ 10的记录加了gap锁，意味着不允许别的事务在这条记录前后间隙插入新记录。

begin;
insert into teacher value(7,'晁','docker');

为什么不能插入？因为记录（7,'晁','docker'）要插入的话，在索引idx_number上，刚好落在6 ~ 10之间，是有锁的，当然不允许插入。

但是当SQL语句变为：insertinto teacher value(70,'晁','docker');能插入吗？

当然能，因为70这条记录不在被锁的区间内。

Mysql的体系架构

可以看出MySQL是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成

连接层

当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器进程会创建一个线程专门处理与这个客户端的交互。当客户端与该服务器断开之后，不会立即撤销线程，只会把他缓存起来等待下一个客户端请求连接的时候，将其分配给该客户端。每个线程独立，拥有各自的内存处理空间

以下命令可以查看最大的连接数：

show VARIABLES like '%max_connections%' ，默认值151

Server层(SQL处理层)

这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。

当然作为一个SQL的执行流程如下：

缓存（了解即可）

存储引擎层

MyISAM和InnoDB比较

mysql的目录和文件

日志文件

在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志、Undo日志等等，日志文件记录了影响MySQL数据库的各种类型活动。

常见的日志文件有：错误日志（error log）、慢查询日志（slow query log）、查询日志（query log）、二进制文件（bin log）。

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息

用户可以通过下面命令来查看错误日志文件的位置：

show variables like 'log_error'\G;

当MySQL不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了错误信息。

慢查询日志

慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。

我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，需要我们手动开启。

show VARIABLES like 'slow_query_log';

二进制日志（binlog）

二进制日志记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的

二进制日志的几种作用：**

恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步

审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

redo log

在事务的实现机制上，Mysql采用的是预写式机制来实现的。就是所有修改都先被写入到日志中，然后再被应用到系统中。通常包括redo log和undo log

Mysql中使用redo log来在系统Crash重启之类的情况时修复数据（事务的持久性），而undo log来保证事务的原子性

其他的数据文件

除了我们上边说的这些用户自己存储的数据以外，数据文件下还包括为了更好运行程序的一些额外文件，当然这些文件不一定会放在数据目录下，而且可以在配置文件或者启动时另外指定存放目录。

崩溃后的恢复为什么不用binglog

binlog为记录表所有的修改，包括更新删除数据，更改表结构等等，主要用于人工恢复的，而redo log对于我们是不可见的，它是InnoDB用于保证crash-safe能力的，也就是在事务提交后MySQL崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。

一句话概括：binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。

redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。

redo log是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”；

redo log是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。

最重要的是，当数据库crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

InnoDB

三大特性：双写机制，Buffer pool，自适应哈希索引

自适应哈希索引：自适应Hash索引，InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据。

InnoDB的内存结构和磁盘存储结构图总结如下：

InnoDB的表空间

表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。

再回忆一次，InnoDB是以页为单位管理存储空间的，我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以B+树的形式保存到表空间的，而B+树的节点就是数据页。

独立表空间结构

系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面，相当于是表空间之首，所以它的表空间 ID（Space ID）是0。

系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页面被称为Double write buffer，也就是双写缓冲区。

双写缓冲区/双写机制

它是一种特殊文件flush技术，带给InnoDB存储引擎的是数据页的可靠性。它的作用是，在把页写到数据文件之前，InnoDB先把它们写到一个叫doublewrite buffer（双写缓冲区）的连续区域内，在写doublewrite buffer完成后，InnoDB才会把页写到数据文件的适当的位置。如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。

doublewrite buffer是InnoDB在系统表空间上的128个页（2个区，extend1和extend2），大小是2MB

所以在正常的情况下, MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个页数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。

所以，虽然叫双写缓冲区，但是这个缓冲区不仅在内存中有，更多的是属于MySQL的系统表空间，属于磁盘文件的一部分。那为什么要引入一个双写机制呢？

InnoDB的页大小一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以页为单位进行操作的。而操作系统写文件是以4KB作为单位的，那么每写一个InnoDB的页到磁盘上，操作系统需要写4个块。

而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K时，发生了系统断电或系统崩溃，只有一部分写是成功的，这种情况下会产生partial page write（部分页写入）问题。这时页数据出现不一样的情形，从而形成一个"断裂"的页，使数据产生混乱。在InnoDB存储引擎未使用doublewrite技术前，曾经出现过因为部分写失效而导致数据丢失的情况。

doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能影响不大，相比不双写，降低了大概5-10%左右。

InnoDB的Buffer Pool

缓存的重要性

我们知道，对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。

但是磁盘的速度慢，所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了

Buffer Pool

InnoDB为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool（中文名是缓冲池）。那它有多大呢？这个其实看我们机器的配置，默认情况下Buffer Pool只有8M大小，这个值其实是偏小的。

show variables like 'innodb_buffer_pool_size';

多个Buffer Pool实例

我们上边说过，Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的各种链表都需要加锁处理，在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

InnoDB的内存结构总结

其中的Insert/Change Buffer主要是用于对二级索引的写入优化，Undo空间则是undo日志一般放在系统表空间，但是通过参数配置后，也可以用独立表空间存放，所以用虚线表示。

Mysql事物底层和实现原理

锁+MVCC来实现的。

事务的一致性通过原子性、隔离性、持久性来保证。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。

redo日志

redo log称为重做日志，每当有操作时，在数据变更之前将操作写入redo log，这样当发生掉电之类的情况时系统可以在重启后继续操作

redo通常是物理日志，记录的是页的物理修改操作，用来恢复提交事务修改的页操作。而undo是逻辑日志，根据每行记录进行记录，用来回滚记录到某个特定的版本。

当事务提交之后会把所有修改信息都会存到redo日志中。redo日志由两部分组成，一个是在内存里的redo log buffer，另一个是在磁盘里的redo log文件。

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

系统重启后读取redo log恢复最新数据。虽然redolog会在事务提交前做一次磁盘写入，但是这种IO操作相比于buffer pool这种以页（16kb）为管理单位的随机写入，它做的是几个字节的顺序写入，效率要高得多。

redo log buffer中的数据，会在一个合适的时间点刷入到磁盘中。

这个合适的时间点包括：

1、MySQL 正常关闭的时候；

2、MySQL 的后台线程每隔一段时间定时的讲 redo log buffer 刷入到磁盘，默认是每隔 1s 刷一次；

3、当 redo logbuffer 中的日志写入量超过 redo log buffer 内存的一半时，即超过 8MB 时，会触发 redo log buffer 的刷盘；

4、当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整，不会进行数据重做。

undo日志

undo log称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态。

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

而事务的隔离性，也可以通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，帮助用户实现一致性非锁定读取，这也是MVCC的实现机制的组成部分

Mysql集群

主从复制原理

1，master服务器将数据改变的记录二进制bing.log日志

2，slave服务器会定时对master节点二进制日志进行探测是否发生改变，如果发生改变，则开始一个I/O线程请求master二进制事件

3，同时master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使其数据与主节点保持一致

也就是说：

从库会生成两个线程,一个I/O线程,一个SQL线程;
I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
主库会生成一个log dump线程,用来给从库I/O线程传binlog;
SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

binglog的三种模式：

1，statement level模式

每一条会修改数据的sql都会记录在master的bin.log中。slave复制的时候sql进程会解析成和原来master端执行过的相同的sql来执行。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。

优点：不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。

缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。

2，row level模式

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改

优点：bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。

缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id='d' where owner_member_id='a',执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大

3.mixed模式

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

基于bing.log主从复制

1）关闭主从机器的防火墙

2）开启bin.log

第二步：重启mysql服务

第三步：主机给从机授备份权限

Mysql分库分表

为什么要分库分表

关系型数据库以MySQL为例，单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。为了减少数据库的负担，提升数据库响应速度，缩短查询时间，这时候就需要进行分库分表。