老鱼进阶篇-Mysql从入门到精通-上篇

鱼穷千里目
394 阅读17分钟

这是我参与8月更文挑战的第3天，活动详情查看：8月更文挑战

1、Mysql 体系架构图

mysql架构体系

中文名称英文概述功能描述
连接层Connectors客户端连接到mysql，都需要经过连接器连接到默认mysql的3306端口，连接器一直处于监听状态，主要用于接收用户的请求。max_connections 代表设置的最大连接数，生产环境一般设置为1000-2000
服务层Server主要分为Sql接口、解析器Parser、Optimizer优化器、Cache和Buffer查询缓存、以及系统管理和控制工具
存储引擎层Storage Engin主要分为MyISAM、InnoDB等，存储引擎具有可插拔的特点，因为存储引擎不是针对于数据的，而是针对于具体的数据库表的

2、Mysql 查询执行流程

2.1 流程图

image-20210811201602234.png

2.2 连接器建立连接

第一步，先连接到这个数据库上，这时候接待的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

2.3 查询缓存

第二步，如果查询sql和上次完全相同（多一个空格等都会认为不相同），则通缓存进行数据获取数据，但是缓存一般失效非常频繁，表结构变动、数据更新都会导致缓存失效。

因此，mysql不建议使用查询缓存，原因是利大于弊。

2.4 解析器进行sql解析

第三步，解析器进行词法分析，把一个完成的sql语句分割成一个个的字符串；然后进行语法分析，判定语法是否合法有效；然后会根据mysql定义的语法规则，根据sql生成一个解析树，如下所示：

image-20210811202531618

2.5 预处理器进行解析树合法检查

第四步，预处理器则会进一步去检查解析树是否合法，比如表名是否存在，语句中表的列是否存在等，在这一步MySQL会检验用户是否有表的操作权限。预处理之后会得到一个新的解析树。

2.6 优化器执行sql优化处理

第五步，查询优化器根据生成的解析树最终生成不同的执行计划，然后选择最优的执行计划，mysql内部使用的基于成本模型的优化器，执行计划所需成本越少则越优先。优化器所做的处理功能部分如下：多个索引存在时决定使用那个索引；存在多表关联时，决定表的连接顺序，确定基准表。

2.7 根据执行计划执行sql语句

第六步，执行器根据执行计划，进行sql语句的执行，主要是根据存储引擎定义的接口，去获取数据。在获取数据之前会检查用户的权限。

3、Mysql 存储引擎

存储引擎描述
MyISAM5.5版本之前MySQL的默认数据库，高速引擎，拥有较高的插入，查询速度，但不支持事务，行锁，外键
InnoDB5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢
ISAMIndexed Sequential Access Method。MyISAM的前身，MySQL5.0以后不再默认安装
Memory内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
CSVCSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)
Falcon一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive用于数据存档。将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
MRG_MyISAM（MERGE）将多个表联合成一个表使用，在超大规模数据存储时很有用
BLACKHOLE丢弃写操作，读操作会返回空内容
FEDERATED用来访问远程表
NDBMySQL集群专用存储引擎

4、Mysql 索引

4.1 索引分类

4.1.1 按照使用类型划分

  • 主键索引：索引列的值必须是唯一的，不允许为空值，常使用主键字段
  • 普通索引：索引列的值没有任何限制，可以重复也可以为空
  • 唯一索引：索引列的值是唯一的，但是可以允许空值
  • 全文索引：文本类型字段CHAR、VARCHAR、TEXT才可以使用，推荐在数据量少或者并发度低的时候使用，否则可以选型es、lucene等

4.1.2 按照索引列数量划分

  • 单列索引: 建立的索引只有一个列
  • 组合索引: 建立的索引使用2个以上的字段，组合索引的使用时需要遵从最左前缀原则，一般情况下建议使用组合索引代替单列索引（主键索引除外），因为更省空间

4.2 索引存储及特点

表中的数据是存储在磁盘文件上的，MySQL在处理数据时，需要先把数据从磁盘上读取到内存中。一个硬盘一般由多个盘片组成，盘片的数量一般都在5片以内。

磁盘的工作机制，决定了它读取数据的速度。读写一次磁盘信息所需的时间可分解为：寻道时间、延迟时间、传输时间。磁盘读取数据花费的时间，是这三个操作步骤所需时间之和。

MySQL本质上是一个软件，MySQL需要读取数据时，MySQL会调用操作系统的接口，操作系统会调用磁盘的驱动程序将数据读取到内核空间，然后将数据从内核空间copy到用户空间，随后MySQL就能从用户空间中读取到数据。操作系统读取磁盘时，Linux读取的最小单位一般为4K。最小单位由操作系统决定，不同的操作系统可能会有所不同。

MySQL的InnoDB存储引擎的数据读取以页为单位，也大小由参数innodb_page_size控制，默认值是16k。

4.3 索引的数据结构

4.3.1 Hash表

Hash表，Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。

Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

4.3.2 二叉树

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大，二叉树的检索复杂度和树高相关，而且主键索引会存在单向链表结构的特殊情况。

4.3.3 平衡二叉树

树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。

存在问题：

  • 时间复杂度和树高相关。树有多高就需要检索多少次
  • 平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高

4.3.4 B树

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万）。

B树是一种多叉平衡查找树，主要特点是：

  • B树的节点中存储着多个元素，每个内节点有多个分叉
  • 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
  • 父节点当中的元素不会出现在子节点中
  • 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针

B树的缺点：

  • B树不支持范围查询的快速查找，如果我们想要查找区间的数据，查找到起始值之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
  • 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

4.3.5 B+树

只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层 的叶子节点形成了一个双向有序链表。

B+树可以保证等值和范围查询的快速查找。

4.4 索引条件下推ICP

Index Condition Pushdown, 简称ICP, 主要是mysql一种优化手段，主要用于使用索引从表中检索行的情况。可以通过参数optimizer_switch控制ICP的开始和关闭。

ICP的主要目的是为了减少回表查询的次数，可用于InnoDB和MyISAM引擎，对于前者仅用于辅助索引。

总结：

1、不实用ICP时，满足最左前缀的索引条件是在存储引擎层进行比较的，非索引条件是在Server层进行过滤的。

2、使用ICP时，所有的索引条件的比较都是在存储引擎层比较的，非索引条件的比较是在Server层进行过滤的。

对比使用ICP和不使用ICP，可以看到使用ICP可以有效减少回表查询次数和返回给服务层的记录数，从而减少了磁盘IO次数和服务层与存储引擎的交互次数。

4.5 覆盖索引

主要是通过查询时查询列都在索引列中，保证从索引查询时都是执行的数据获取，不再进行单独的回表查询，减少回表次数和IO次数，加快查询速度。

4.6 索引失效分析

  • 不满足索引的最左前缀原则，或者在索引条件上使用<>, != 等判定，从第一个判定条件开始不走索引
  • 在索引列上使用操作函数
  • 使用is null, is not null查询条件无法走索引
  • 字符串查询时不添加引号
  • like 模糊匹配时使用'%xxxx%'时无法走索引
  • 尽量使用覆盖索引，减少select *
  • 少用or进行条件查询连接，会导致索引失效
  • 多使用全值匹配索引查询

5、explain查询执行计划说明

5.1 select_type

  • simple

    表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。

  • primary

    一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary，且只有一个。

  • union

    union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。

  • dependent union

    与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

  • union result

    包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

  • subquery

    除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

  • dependent subquery

    与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

  • derived

    from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

5.2 type

  • system

    表中只有一行数据或者是空表。

  • const

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

  • eq_ref

    用于多表关联的等值连接情况下，并且等值连接的两个是主键或者唯一索引。此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 = , 查询效率较高。

  • ref

    用于多表关联的等值连接情况下，并且等值连接的是非唯一索引，或者使用的是最左前缀规则的索引查询。

  • fulltext

    全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

  • ref_or_null

    与ref方法类似，只是增加了null值的比较。实际用的不多。

  • unique_subquery

    用于where中的in形式子查询，子查询返回不重复值唯一值

  • index_subquery

    用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

  • range

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

  • index_merge

    表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

  • index

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

  • all

    这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

5.3 extra

  • using index

    查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！

    如果同时出现Using Where ，说明索引被用来执行查找索引键值。如果没有同时出现则代表索引用来读取数据而非执行查找动作。

  • using where

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

  • using index condition

    表明使用了ICP索引条件下推。

  • using filesort

    排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。说明mysql会使用一个外部的索引排序，而不是按照索引顺序进行读取，mysql中无法利用索引完成的排序操作称为“文件排序”。

  • using temporary

    表示mysql在对查询结果order by和group by时表使用了临时表存储中间结果。

  • distinct

    在select部分使用了distinct关键字 （索引字段）

  • no tables used

    不带from字句的查询或者from dual查询。

    使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

6、Mysql 锁简介

6.1 锁的划分

6.1.1 按粒度划分

  • 全局锁：锁的是整个database。由MySQL的SQL layer层实现的

  • 表级锁：锁的是某个table。由MySQL的SQL layer层实现的，主要有表锁和元数据锁。

  • 行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。行锁锁定的是某行数据，也可能锁定行之间的间隙。InnoDB行锁是通过给索引树上的叶子节点中索引项加锁来实现的。

    InnoDB的行级锁，按照锁定范围来说，分为三种：

    记录锁（Record Locks）: 锁定索引中一条记录。

    间隙锁（Gap Locks）: 锁住的是两个索引之间的区间（缝隙），是一个左开右开区间。

    Next-Key Locks: 间隙锁+紧邻间隙锁的下一个记录锁，左开右闭区间。

    而且在无索引的情况下，行锁会升级为表级锁。

6.1.2 按功能划分

  • 共享读锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 排他写锁：允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是读）和排他写锁。

6.1.3 按实现方式

  • 悲观锁
  • 乐观锁

7、Mysql 事物和MVCC底层原理

7.1 事物四大特性（ACID）

  • 原子性

    事务是可以提交或回滚的工作的原子单位。当一个事务对数据库进行多次更改时，要么所有更改 在事务提交时成功，要么所有更改在事务回滚时撤消。它的意思就是在事务中发生的一系列操作是一个不可分割单元，事务里面的一系列更新操作，它们要么在事务提交全部是成功执行，要么在事务回滚时全部撤销。我们在程序中使用事务如果发生了异常的话，一定要进行事务回滚，如果进行了回滚的话，那么我们前面进行的数据库更新操作就像都没有执行过。

  • 一致性

    数据库在任何时候都保持一致的状态——在每次提交或回滚之后，以及在事务进行期间。如果是跨多个表更新相关数据，在事务外查询时将看到所有旧值或所有新值，而不是新旧值的混合。事务开始和结束之间的数据的中间状态不会被其他事务看到，事务的原子性保证了数据的一致性。

  • 隔离性

    事务在进行过程中相互隔离，它们不能相互干扰或查看彼此未提交的数据。事务的隔离性是通过锁定机制实现的，有经验的开发人员可以通过调整隔离级别，提高性能和并发性，这样他们就可以确保事务之间不会相互干扰。

    由锁机制和MVCC机制来实现的；MVCC(多版本并发控制)：优化读写性能（读不加锁、读写不冲突）

  • 持久性

    事务的结果是持久的，事务执行成功后必须全部写入磁盘：一旦提交操作成功，该事务所做的更改就不会受到电源故障、系统崩溃等其他潜在危险的影响。数据库的数据通常是保存在磁盘上的，对数据的修改涉及对磁盘存储的写操作，其中包含一定数量的冗余，以防止在写操作期间出现电源故障或软件崩溃。

7.2 InnoDB内存和磁盘结构

7.2.1 内存结构

Buffer Pool 缓冲池

Buffer Pool是主内存的一块区域，在InnoDB访问表时会将数据页和索引页缓存到缓冲池中。在MySQL专用服务器上，通常将75％以 上的物理内存分配给缓冲池，可以通过参数innodb_buffer_pool_size控制缓冲池的大小。

InnoDB表中数据不管是主键索引还是辅助索引都是以页为单位存储在磁盘空间中的。当InnoDB访问某个页中的数据时，会先把这个 页整体加载到缓冲池中然后在进行读写操作。在完成操作后，InnoDB并不会立即将这个页从缓冲池中删除，而是将它缓存起来，这 样当下次再有操作需要访问这个页时，会直接从缓冲池中获取，这样就省去了磁盘IO开销，加快了数据访问速度。

Buffer Pool中页的大小和数据文件上页大小是一样的，都是16K。当Buffer Pool空间不够用时，Buffer Pool会使用LRU算法淘汰最近最少使用的页。

在MySQL初始化时会将Buffer Pool划分为若干个缓存页：

  • 空闲页链表：未使用的缓存页
  • 脏页链表：发生修改的缓存页，InnoDB存储引擎对数据做修改的时候，会先把数据页从磁盘中读到内存中(buffer pool)中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称这个页为dirty page 脏页
  • LRU链表：LRU链表中保存着所有加载到Buffer Pool的数据页和索引页。按照最近最少使用的原则，最近使用的排在链表的头部，最近最少使用排在链表尾部。

当Buffer Pool空间没有空闲页可用时，就从LRU链表尾部淘汰一些缓存页，经过淘汰之后，LRU头部就保存的是热点数据。LRU链表不是传统的LRU链表，InnoDB对LRU做了优化，将LRU链表分成了yong区（5/8）和old区（3/8）。这个比例可以通过参数innodb_old_blocks_pct调节，默认值37，代表old区占比37%。

young区：存储使用频率非常高的缓存页，这一部分链表也叫做热数据。

old区：存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据。

当InnoDB将页面读入缓冲池时，它首先将其插入old列表的头部，当下次访问这个页面满足一定条件时在将这个页面插入到yong区的头部。满足一定条件是指：最近一次访问页面时间减去首次访问的时间<某个时间间隔。这个时间间隔可以通过innodb_old_blocks_time 参数控制，默认值1s。即第一次和最后一次访问该页面的时间间隔小于1s时，才会将这个页面插入到yong区的头部。

对于缓冲池中的一些热点索引页，InnoDB为了提高这些页的访问速度，会自动在缓冲池中建立一个自适应Hash索引。自适应哈希索引功能可以使用参数innodb_adaptive_hash_index是否开启，默认开启。

Change Buffer

Change Buffer是一种特殊的数据结构，是针对二级索引（辅助索引）页的更新优化措施。当二级索引页不在Buffer Pool中，InnoDB会将对二级索引的数据更改操作先暂时缓存在Change Buffer中，稍后当索引页面因为其他读取操作加载到Buffer Pool的时候，会将这些更改操作合并更新到索引页中。Change Buffer缓存的更改可能由 Insert 、Delete 和 Update操作导致，这样通过合并操作可以减少二级索引的随机IO。Change Buffer的使用可以有效的提升insert，updte，delete的执行速度。

步骤： 
1.二级索引页的DML操作，并且这个索引页页没有在Buffer Pool内，那么把这个操作存入Change Buffer。 
2.那么下一次需要加载这个页面的时候，索引页被加载到Buffer Pool中。Change Buffer内的更改 合并到Buffer Pool。 
3.随后当服务器在空闲的时候，这个更改会刷到磁盘上。

什么时候将change buffer更新到buffer pool的索引页？

答：索引页加载到缓存池中时在内存中合并更新。

什么时候将buffer pool的脏页更新到磁盘文件中？

答：redo log写满时；数据库空闲时，由后台线程；数据库正常关闭时。同样change buffer也会在这三种情况同时被更新到磁盘中。

Log Buffer（Redo Log）

MySQL会将更新先缓存在内存中，当服务器空闲时才会选择将脏页刷新到磁盘中。但是这就会有个问题，如果在脏页落盘之前服务器异常关机或者MySQL崩溃宕机，就会造成脏页这些数据的丢失。为了避免这个问题，InnoDB把对页面的修改操作会同时写入一个日志文件持久化到磁盘上，这样当MySQL崩溃重启后，MySQL就会使用这个日志文件执行恢复操作，将更改重新应用到数据文件，实现了更新操作的持久化。这个日志文件就是Redo Log。

默认情况下，redo log对应的物理文件位于数据库的数据目录下的ib_logfile1和ib_logfile2。可以通过参数控制日志文件的存储文职，数量和大小，设置如下所示：

#指定日志文件所在的路径，默认./，表示在数据库的数据目录下。 
innodb_log_group_home_dir=./ 
#指定重做日志文件组中文件的数量，默认2，表示有两个重做日志文件。 
#两个文件循环写入，一个写满之后才能开始使用另外一个，一般保持2就可以。 
innodb_log_files_in_group=2 
#每个重做日志文件的大小，默认48M。 
innodb_log_file_size=16777216

这种日志和磁盘配合的过程 ， 其实就是MySQL里经常说到的WAL 技术（Write-Ahead Logging），它的关键点就是在写磁盘前，先写日志。MySQL在内存中专门开辟了一块区域Log Buffer专门保存将要写入redo log的数据，它的大小可以通过数innodb_log_buffer_size控制，默认16M。

落盘时机：Log Buffer写入磁盘的时机，由参数 innodb_flush_log_at_trx_commit 控制，默认是1，表示事务提交后立即落盘。

落盘规则：

  • 0: MySQL每秒一次将数据从log buffer写入日志文件并同时fsync刷新到磁盘中。每次事务提交时，不会立即把 log buffer 里的数据写入到redo log日志文件的。如果MySQL崩溃或者服务器宕机，此时内存里的数据会全部丢失，最多会丢失1秒的事务。
  • 1：每次事务提交时，MySQL将数据从log buffer写入日志文件并同时fsync刷新到磁盘中。该模式为系统默认，MySQL崩溃已经提交的事务不会丢失，要完全符合ACID，必须使用默认设置1。
  • 2：每次事务提交时，MySQL将数据从log buffer写入日志文件，MySQL每秒执行一次fsync操作将数据同步到磁盘中。每次事务提交时，都会将数据刷新到操作系统缓冲区，可以认为已经持久化磁盘，如果MySQL崩溃已经提交的事务不会丢失。但是如果服务器宕机或者意外断电，操作系统缓存内的数据会丢失，所以最多丢失1秒的事务。

总结：

综合安全性和性能的考虑，在业务中经常使用2这种模式，在MySQL异常重启时不会丢失数据，只有在服务器意外宕机时才会丢失1秒的数据，这种情况几率是很低的，相对于性能来说，这时可以容忍的。

日志文件也是磁盘文件，为什么不直接更新到数据文件中，而是要先更新到redo log中呢？

答：如果事务提交时，我们需要更新的数据是分散在不同页不同扇区中的，更新数据时需要根据磁盘地址找到对应的磁道，然后再找到对应的扇区，才能写入数据，这个时间一般需要10ms。一次事务提交的数据需要多次的磁盘IO交互才能完成，这个是随机IO，读取和写入速度比较慢。

而redo log文件是在磁盘中一块连续的区域，事务提交时，写入redo log时，我们只要找到找到第一块扇区，只需要依次向后写入就行，也就是说只需要执行一次磁盘IO操作，这就是顺序IO。脏页落盘是随机IO，记录日志是顺序IO，通过使用WAL技术，先将更改操作记录在日志文件中，延迟落盘，可以提高系统性能。需要注意的是，redo log主要用于崩溃恢复。磁盘中数据文件的数据的更新，仍旧来自于 buffer pool中的脏页落盘。

redo log的特点：

  1. redo log 是InnoDB存储引擎层产生的，其他存储引擎没有。

  2. redo log是物理日志，记录的是“某个数据页上的数据做了什么修改”。

  3. redo log的文件数和大小是固定的，redo一个文件写满后会切换到下一个文件。从第一个文件开始写，写到最后一个文件就又会回到第一个文件开始循环写。当系统空闲时或者redolog写满时，MySQL会将redo log前面的数据擦除，对应的数据修改会被同步到数据文件中。

bin log

binlog记录了数据库执行更改的操作（所有的ddl语句和dml语句），但不包括select和show这类操作。事务未提交前，所有未提交的二进制日志会被记录到一个缓存中去，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，主要用于实现mysql主从复制、数据恢复。

binlog默认是关闭的，建议开启，需要通过以下配置进行开启。

#默认关闭 
log-bin=OFF 	
#开启，mysql-bin是binlog日志文件的文件名前缀，binlog日志文件的完整名称：mysql-bin-000001.log 
log-bin=mysql-bin

max_binlog_size指定了单个二进制日志文件的最大值。如果超过该值，则产生新的二进制日志文件，后缀名+1，如mysql-bin-000002.log。

二进制缓冲区是基于会话（session）的，其大小由binlog_cache_size 决定，默认大小为32K。当一个线程开始一个事务时，MySQL 自动分配一个大小为binlog cache_ size 的缓存。

落盘时机：

写入磁盘的时机，由参数sync_binlog控制，默认是 1，在事务提交（Commit）前将二进制日志刷新到磁盘。

  • 0：禁用MySQL服务器将二进制日志同步到磁盘的功能。依赖于操作系统不时地将二进制日志刷新到磁盘上，就像处理其他任何文件一样。此设置性能最佳，但是因为二进文件缓存在binlog_cache中，所以在电源故障或者操作系统崩溃时，会存在已提交的事务未同步到磁盘的可能性（在binlog_cache中的所有binlog信息都会被丢失）。

  • 1：在事务提交前将二进制日志同步到磁盘。这是最安全的设置，但是由于磁盘写入次数增加，会对性能产生影响。在电源故障或者操作系统崩溃时，可以保证所有已经提交的事务肯定已经刷新到磁盘中，未刷新到磁盘的事务仅仅是处于准备阶段（未提交），不会丢失事务。

    sync_binlog=1，一个事务发出COMMIT动作之前，会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，如果此时发生了宕机，那么在MySQL数据库下次启动时，由于COMMIT操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，如果在主从复制环境下（或者使用这个binlog来来进行恢复）就会造成主从数据不一致。这个问题可以通过将参数innodb_support_xa=1来解决，它同时确保了二进制日志和InnoDB存储引擎数据文件的同步。

  • N（N>1）：每N次事务，Mysql会同步二进制日志同步到磁盘。在电源故障或者操作系统崩溃时，会存在已提交的事务未同步到磁盘的可能性。

redo log和bin log区别

  1. redo log 是InnoDB存储引擎层产生的，而bin log是数据服务层产生的。

  2. redo log 空间固定，用完后循环写；binlog 采用“追加写”的方式，一个文件达到一定大小后会切换到下一个。

  3. redo log主要用于崩溃恢复；binlog主要用于主从复制和数据恢复。

Double Write双写缓冲区

为什么需要双写缓冲区？

答：数据库数据页大小是16K，操作系统IO的最小单位一般是4K，也就是说Buffer Pool中一个脏页写入数据文件时需要分4次写入。如果数据页在写入的过程中，服务器断电或者宕机，数据页就有可能只写入了一部分（比如只写入了8k），还有一半的数据没有写入，这种现象称为“部分写失效”（partial page write）。就会导致数据文件中的数据页被损坏，在MySQL重启后，就无法使用redo log恢复这个损坏的数据页，所以这个页的数据就丢失了，可能会造成数据不一致。InnoDB为了提高的可靠性，引入了Double Write机制，用来解决部分写失效。

双写缓冲是InnoDB的一个关键特性，可以使用参数innodb_doublewrite控制是否启用双写缓冲区，默认开启，建议开启。如果开启了双写缓冲，在 InnoDB将脏页写入数据文件之前，会先从缓冲池中刷新页面到双写缓冲区 ，然后再将脏页从双写缓冲刷新到数据文件中。如果脏页在写入数据文件过程中MySQL服务崩溃，MySQL重启后InnoDB就可以从系统表空间的Double Write中找到该页最近的一个副本，将其复制到表空间文件，然后再应用redo log，就可以完成了恢复。

undo log

事务的原子性和隔离性都是由undo log来实现的。实现了原子性，其实也就是实现了一致性。事务的持久性是由redo log来实现的。

undolog（撤销日志或回滚日志）记录了事务中更改操作之前的数据状态，如果用户执行了回滚操作，数据库就可以利用undo log 将数据恢复至事务之前的状态。redo Log 和 undo Log ，统称为事务日志。undo Log 默认存储在系统表空间中，也可以将undoLog存储在独立表空间，可以通过以下参数设置。

show global variables like '%undo%';
#undo存储目录，默认在数据目录下 
innodb_undo_directory=./ 
#默认0，表示关闭。undo文件的数量，格式为undo001，undo002等 
innodb_undo_tablespaces=0

除了用于回滚操作，undo log的另一个作用是MVCC，在InnoDB存储引擎中MVCC的实现是通过undolog来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取。

7.2.2 InnoDB事物分析

原子性、持久性和一致性

原子性，持久性和一致性主要是通过redo log、undo log、Force Log at Commit和Double Write机制来完成的。redo log用于在崩溃时恢复数据，undo log用于对事务回滚时进行撤销，也会用于隔离性的多版本控制。Force Log at Commit机制保证事务提交后redo log日志都已经持久化。Double Write机制用来提高数据库的可靠性，用来解决脏页落盘时部分写失效问题。

原子性：redo log、undo log、Force Log at Commit和Double Write机制。

持久性：redo log，Double Write。

redo log主要用于崩溃恢复。数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。

崩溃恢复时除了需要使用redo log对已经提交的事务。在崩溃恢复中还需要回滚没有提交的事务。回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。

事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

隔离性

  • Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

  • Read committed (RC，读已提交)：可避免脏读的发生。

  • Repeatable read (RR，可重复读)：可避免脏读、不可重复读的发生。

    （注意事项：InnoDB的RR还可以解决幻读，主要原因是Next-Key（Gap）锁，只有RR才能使用 Next-Key锁）

  • Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

    （由MVCC降级为Locking-Base CC）

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

  • 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。(select)
  • 当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

**一致性非锁定读(consistent nonlocking read)**是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。

如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个最新可见快照。

事务链表

MySQL中的事务在开始到提交这段过程中，都会被保存到一个叫trx_sys的事务链表中，这是一个基本的链表结构：

ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;

事务链表中保存的都是还未提交的事务，事务一旦被提交，则会被从事务链表中摘除。

RR隔离级别下，在每个事务开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(readview)

RC隔离级别下，在每个语句开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(readview)

show engine innodb status ,就能够看到事务列表。

ReadView

Read View是事务开启时当前所有事务的一个集合，这个类中存储了当前Read View中最大事务ID及最小事务ID。

#假设这就是当前活跃的事务列表。如下所示：
ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;
#ct-trx 表示当前事务的id，对应上面的read_view数据结构如下
read_view->creator_trx_id = ct-trx; 
read_view->up_limit_id = trx3; 低水位 
read_view->low_limit_id = trx11; 高水位 
read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];

**low_limit水位”，即当时活跃事务的最大id，如果读到row的db_trx_id>=low_limit_id，说明这些id在此之前的数据都没有提交，如注释中的描述，这些数据都不可见。

up_limit_id是“低水位”，即当时活跃事务列表的最小事务id，如果row的db_trx_id<up_limit_id,说明这些数据在事务创建的id时都已经提交，如注释中的描述，这些数据均可见。

row的db_trx_id在low_limit_id和up_limit_id之间，则查找该记录的db_trx_id是否在自己事务的read_view->trx_ids列表中，如果在则该记录的当前版本不可见，否则该记录的当前版本可见。

在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global readview。使用trx_assign_read_view函数创建，一直维持到事务结束。在事务结束这段时间内 每一次查询都不会重新重建Read View ， 从而实现了可重复读。

在 read-commited的隔离级别下，在每次语句执行的过程中，都关闭read_view, 重新在row_search_for_mysql函数中创建当前的一份read_view。这样就会产生不可重复读现象发生。

avatar
文章
阅读
粉丝