《InnoDB存储引擎》笔记

程序员文章站 2022-05-10 22:41:03

第1章 Mysql体系结构和存储引擎 1.1 定义数据库和实例数据库：database，物理的操作系统文件或其他形式文件类型的集合。当使用NDB存储引擎时，数据库文件可能是存放在内存中而不是磁盘之上。实例：instance，Mysql数据库实例由后台线程和一个共享内存区组成。实例才是真正用于操作 ......

第1章 Mysql体系结构和存储引擎

1.1 定义数据库和实例

数据库：database，物理的操作系统文件或其他形式文件类型的集合。当使用NDB存储引擎时，数据库文件可能是存放在内存中而不是磁盘之上。

实例：instance，Mysql数据库实例由后台线程和一个共享内存区组成。实例才是真正用于操作数据库文件的。

1.2 Mysql体系结构

mysql由以下几部分组成：连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件。

mysql体系结构图：

mysql架构图：

1.3 Mysql存储引擎

OLTP是在线事务处理，读少写多；OLAP是在线分析处理，读多写少。

（1）InnoDB存储引擎

从mysql 5.5.8开始，InnoDB是默认存储引擎，主要面向OLTP。

特点：支持事务、行锁设计、支持外键、支持非锁定读（默认读取不会产生锁）。

InnoDB将数据存放在一个逻辑表空间里，每张表单独存放到一个独立的ibd文件。使用MVCC（多版本并发控制）来获得高并发性，默认隔离级别为可重复读。另外还提供了插入缓冲（Insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。

表中数据存储采用聚集（cluster）方式，每张表的存储都是按主键顺序。当表没有显示指定主键时，会生成一个6字节的ROWID做为主键。

（2）MyISAM存储引擎

mysql 5.5.8之前是默认存储引擎，主要面向OLAP。

特点：不支持事务、表锁设计、支持全文检索、缓冲池只缓冲索引文件不缓冲数据文件。

表由MYD数据文件和MYI索引文件组成。其中数据文件可以压缩。

（3）NDB存储引擎

是一个高可用、高性能的集群存储文件，类似oracle的RAC集群。

特点：数据全部放在内存、主键查找速度极快、通过添加集群节点来线性提高数据库性能、类似Join的复杂操作在Mysql上层完成而开销大速度慢。

（4）Memory存储引擎

数据存放在内存中、适合存储临时数据的临时表，默认采用哈希索引，而不是B+树索引。

特点：数据存放在内存崩溃会丢失、只支持表锁、并发性能差、不支持TEXT和BLOB列类型。

mysql查询中间结果集的临时表会放在Memory存储引擎。

（5）Archive存储引擎

设计目标主要提供高速插入和压缩，适合存储归档数据。

特点：只支持Insert和Select操作、数据行压缩。

（6）Maria存储引擎

新开发的用来取代MyISAM，从而成为默认存储引擎。

特点：支持缓存数据和索引文件、行锁设计、提供MVCC、支持事务和非事务选项、更好的BLOB字符类型处理性能。

第2章 InnoDB存储引擎

2.1 InnoDB存储引擎概述

是Innobase Oy公司开发，其创始人和linus是芬兰赫尔辛基大学校友。

2.2 InnoDB体系结构

（1）后台线程

①Master Thread

负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、undo日志回收等。

②IO Thread

负责大量AIO异步写的回调处理，4种IO线程：write、read、insert buffer、log。

③Purge Thread

当事务提交后，用多线程负责回收不需要的undo日志。减轻Master线程工作。

④Page Cleaner Thread

负责脏页的刷新操作，减轻Master线程工作。

（2）内存

①缓冲池

由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统都使用缓冲池技术来提高DBMS整体性能。缓冲池（buffer pool）是一块很大的内存区域，大小直接影响DBMS整体性能，强烈建议安装在64位操作系统，以突破32位最大3G的内存上限。

A、读记录操作

将从磁盘读到的页FIX到缓冲池，下一次再读相同页时，先去缓冲池查看是否命中，否则读磁盘。

B、写记录操作

先修改缓冲池中的页，然后再以一定的频率刷新回磁盘。通过Checkpoint机制触发刷新，而不是每次修改都触发。

允许有多个缓冲池实例，页通过哈希分配。好处：减少资源竞争，增加并发处理能力。

缓冲池通过LRU（Latest Recent Used，最近最少使用）算法进行管理。队头为热端，队尾为冷端，新页插入到midpoint，每页默认16KB。页可以压缩。

②LRU List、Free List 和 Flush List

Free List：数据库刚启动时，页都存放到Free列表中，此时LRU列表为空。

LRU List：管理已经读取的页。当读页时，先查找Free列表，存在则将页移动到LRU列表；不存在则从LRU读取，否则从磁盘取新页并淘汰LRU队尾页。页被修改后，则称为脏页（dirty page）。

Flush List：脏页列表，数据库通过checkpoint机制将脏页刷新回磁盘。

脏页同时存在于LRU和Flush List中。其中LRU列表用来管理缓冲池中页的可用性；Flush列表用来管理将页刷新回磁盘。

③重做日志缓冲

redo log buffer：先将重做日志放入到这个缓冲区，然后默认每1秒将其刷新到重做日志文件。默认8MB。

将重做日志刷新到磁盘有如下三种情况：Master Thread每一秒、事务提交时、重做日志缓冲剩余空间小于1/2时。

④额外的内存池

存储每个缓冲池的控制对象，以及其他如LRU管理、锁、等待等信息。

2.3 Checkpoint技术

Write Ahead Log策略：当脏页刷新回磁盘发生宕机，为避免数据丢失。当事务提交时，先写重做日志，再修改页。

Checkpoint：检查点技术，职责：将缓冲池中的脏页刷新回磁盘。解决三个问题：缩短数据库恢复时间、缓冲池不够用时将脏页刷新到磁盘、重做日志不可用时刷新脏页。

Checkpoint分为如下两类：

①Sharp Checkpoint：数据库关闭时将所有脏页都刷新回磁盘。默认工作方式。

②Fuzzy Checkpoint：只选择一部分脏页刷新回磁盘，而非全部。

Fuzzy的四种场景：Master Thread每1秒和10秒从脏页列表Flush List异步刷新、LRU列表空闲页不足100个强制刷新、循环使用的重做日志文件不可用时强制刷新、脏页总数量太多且占比超过75%时强制刷新。

2.3 插入缓冲

主键聚集索引：数据页记录按主键顺序存放，插入速度极快。

辅助索引：插入需要随机读取，来离散访问非聚集索引页，性能不高。

Insert Buffer：是缓冲池中的一块内存，数据结构为B+树，用途：非唯一辅助索引的插入操作。全局只有一棵Insert Buffer B+树，负责对所有表的非唯一辅助索引进行插入缓冲，存放在共享表空间。

Change Buffer：InnoDB 1.0版本引入，是插入缓冲的升级，可以对INSERT、DELETE、UPDATE等DML操作都进行缓冲，分别是Insert | Delete | Purge Buffer。

Insert Buffer原理：对于非聚集索引的插入和更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中来告诉数据库，非聚集索引页以及插入到叶子节点成功，实际并没有。然后以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的合并操作。

Insert Buffer优点：通过将同一个索引页中的多个插入合并到一个操作中，大大提高了非聚集索引插入性能。

Merge Insert Buffer（合并插入缓冲）发生场景如下：

①辅助索引页被读取到缓冲池时。

②Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时。

③Master Thread。

2.4 两次写

部分写失效：当脏页刷新到磁盘时，如果碰到宕机，只将该页16K的一部分如4K写入磁盘，则出现数据丢失。

重做日志文件：记录的是对页的物理操作，比如：偏移量200写入记录A。恢复时还需有一份该页的副本才行，而两次写通过在共享表空间保存宕机时刻的页副本，来提高可靠性。

两次写由两部分组成：一部分是内存中的doublewrite buffer，大小2MB；另一部分是磁盘上共享表空间中连续的2个区，共128页，总大小也是2MB。

两次写过程如下：

①对缓冲池脏页刷新时，不直接写磁盘，而是先将脏页用memcpy函数复制到内存中的doublewrite buffer。

②分成两次，每次将1MB数据写入磁盘共享表空间中的一个区，并立即调用fsycn函数强制同步磁盘，避免磁盘缓冲。因两个区地址是连续的，所以为顺序写入，速度很快。

③最后将内存中的doublewrite buffer写入到磁盘各个表空间文件中，为离散随机写入。

两次写崩溃恢复过程：从共享表空间两个区找到该页的一个副本，然后复制到表空间文件，再应用重做日志。

2.5 自适应哈希索引

Adaptive Hash Index：AHI是数据库内部自动优化，默认开启。查询定位只用一次操作，而B+树高度一般3~4层，需要3~4次操作。AHI通过缓冲池中的B+树页构造而来，建立速度很快，不需要对整张表构建哈希索引。

存储引擎会自动根据访问频率和模式来自动的为某些热点页建立哈希索引。

一般要满足如下三个条件才会建立：

①对该页的访问模式必须是连续且一样的，不能交替访问。

②以该访问模式访问了100次或页中记录*1/16次。

2.6 异步IO

AIO：Async IO，优点：可以通过避免同步等待、合并IO操作来提高IOPS。需要操作系统Native AIO支持。

在InnoDB中，预读、脏页刷新到磁盘等都是通过AIO完成。

2.7 刷新邻接页

当刷新一个脏页时，会检测该页所在区（extent）的所有页，如果是脏页，则一起刷新。好处是通过AIO将多个页IO操作合并为一个IO操作，提升性能。

第3章 文件

mysql和InnoDB文件包括：参数文件、日志文件（错误日志、慢查询日志、查询日志、二进制日志）、套接字文件、pid文件、表结构定义文件、存储引擎文件（表空间文件、重做日志文件）等。

3.1 二进制日志

binary log：记录了对mysql数据库执行更改的所有操作。属于mysql服务层，而不是存储引擎层。默认不启动。

二进制日志的作用：point-in-time恢复、主从复制、安全审计。

当存储引擎支持事务时，所有事务未提交的二进制日志会被记录到一个会话级别的缓存中去，当事务提交时，直接将缓存中的二进制日志写入二进制日志文件中。

文件内容格式有三种：STATEMENT（逻辑SQL语句）、ROW（行记录更改情况）、MIXED（默认采用Statement，有时采用Row）。ROW格式的优点是复制和恢复可靠性高，但缺点是文件较大。

3.2 其他文件

.sock后缀为套接字文件，在Unix系统下本地连接Mysql可以采用Unix域套接字方式。

.pid后缀为pid文件，mysql实例启动时，会将自己的进程ID写入该文件。

.frm后缀为表结构定义文件。还用来存放视图定义。

3.3 表空间文件

.ibd后缀为表空间文件，分为两类：

共享表空间文件：也叫默认表空间文件。

独立表空间文件：当设置innodb_file_per_table参数时，每个表都会有一个独立表空间。否则数据保存到共享表空间。

3.4 重做日志文件

redo log file：InnoDB存储引擎级别的事务日志文件。以循环写入方式运行。InnoDB有51种重做日志类型。

重做日志文件与二进制日志文件都是记录事务日志，但区别如下：

①级别和范围不同。binlog属于Mysql服务层，会记录所有与Mysql数据库有关的日志，包括所有存储引擎；而redolog属于InnoDB存储引擎层，只会记录该引擎的事务日志。

②记录内容不同。binlog记录的是事务逻辑日志；而redolog记录的是关于每个页的更改物理情况。

③写入时间不同。binlog仅在事务提交时写一次磁盘；而redolog却在事务进行中不断写入。

第4章 表

4.1 索引组织表

HOT：堆组织表，索引和表数据是分离的，索引记录了数据所在位置的的rowid。数据插入时存储位置是随机的，主要由数据库内部块的空闲情况决定，获取数据是安装命中率计算，全表扫描时不见得先插入的数据先查到。

IOT：索引组织表，索引和数据是在一起的，行数据以索引形式存放。

两者区别：查询速度索引组织表更高，插入速度堆组织表更高。Oracle支持堆表，也支持索引组织表。Innodb只支持索引组织表。

InnoDB存储引擎中，表都是按照主键顺序组织存放的，如果没有显式定义主键，则先判断表是否有非空唯一索引，有则当主键；若没有则自动创建一个6字节大小的指针，即“_rowid”。

4.2 InnoDB逻辑存储结构

所有数据都被逻辑的存放在表空间（tablespace）中，表空间又由段（segment）、区（extent）、页（page）组成。

（1）表空间

共享表空间：存放undo日志、插入缓冲索引页、redo日志、二次写缓冲等。

各表的独立表空间：数据、索引、插入缓冲bitmap页。

（2）段

表空间由各个段组成，常见段有：数据段、索引段、回滚段等。

（3）区

区由连续页组成的空间，任何情况下每个区大小都是1MB。一个区有64个连续的页，每个页大小是16KB。

（4）页

页是InnoDB磁盘管理的最小单位。常见页类型有：数据页、undo页、系统页、事务数据页、插入缓冲位图页等。

4.3 InnoDB行记录格式

行记录格式分为Compact和Redundant两种。Compact是最新版，Redundant是为了兼容老版本。

Compact格式下，NULL值不占用任何存储空间。每行数据有额外的2~3个隐藏列：事务ID列、回滚指针列、可能的_rowid列。

行溢出数据：将一条记录中的某些数据存储在真正的数据页面之外，当发生行溢出时，该行数据可能保存在2~N个页。BLOB、LOB、varchar等大对象有可能在页内或页外。varchar定义时可设置最大65535字节，但实际因别的开销最大只能存储65532字节。

对于多字节编码的Char类型，InnoDB内部将其视为变长字符类型，char（N），N是字符个数。

4.4 约束

InnoDB有5种约束：Primary Key、Unique Key、Foreign Key、Default、Not Null。

约束和索引区别：约束是逻辑概念，用来保证数据完整性。而索引是一个数据结构，既有逻辑概念，又有物理存储方式。

触发器的作用：在执行INSERT、DELETE和UPDATE之前或之后自动调用SQL命令或存储过程。

4.5 视图

视图是基于基表的一个虚拟表，对视图的操作就是基于视图的定义操作基础表。

物化视图：该视图不是基于基表的虚拟表，而是根据基表实际存在的实表。物化视图可用于预先计算并保存多表的链接或聚集等耗时角度的SQL操作结果。

4.5 分区表

分区功能是在mysql服务层完成，而不是在存储引擎层完成，但并不是所有的存储引擎都支持分区。

局部分区索引：mysql数据库的分区是局部分区索引，一个分区中即存放了数据又存放了索引。

全局分区：数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前mysql还不支持全局分区。

mysql数据库支持如下4种分区类型：

①RANGE分区：行数据基于连续区间的列值被放入分区。

②LIST分区：面向离散的值。

③HASH分区：根据用户自定义的表达式的返回值来进行分区，不能为负数。

④KEY分区：根据mysql数据库提供的哈希函数来进行分区。

不论采用何种分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。否则可以指定任何一个列为分区列。允许对NULL值做分区，与排序一样总是视NULL值为最小值。

子分区：也叫复合分区，是在分区的基础上再进行分区。Mysql数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

第5章 索引与算法

5.1 InnoDB索引概述

InnoDB存储引擎支持三种索引：B+树索引、全文索引、哈希索引。其中B代表平衡，哈希索引不能人工干预。

5.2 数据结构与算法

二分查找法：也称折半查找法，用来查找一组有序记录数组中的某一记录。

二叉查找树：根节点键值总是大于左子树的键值，且小于右子树的键值。中序遍历。极端情况退化为顺序查找。

平衡二叉树：AVL树，定义是：首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。通过左旋和右旋来平衡。多用于内存结构对象。

B+树：由平衡二叉树和B树演化而来，是为磁盘或其他存储设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。B+树插入和删除节点时，会通过旋转或拆分来重新平衡。

5.3 B+树索引

B+树索引分为聚集索引和辅助索引，两者内部都是2~4层高度平衡的B+树，区别是叶子节点存放的是否是一整行的信息。

（1）聚集索引

clustered index：也叫主键索引，按照每张表的主键构造一棵B+树，同时叶子节点存放的即为整张表的行记录数据，叶子节点也叫数据页，中间节点叫索引页。每个数据页都通过一个双向链表连接。因为InnoDB是索引组织表，表中数据按照主键顺序存放。所以每张表只能有一个聚集索引。数据逻辑上连续，但物理上不连续。主键排序速度极快。

（2）辅助索引

secondary index：也叫二级索引、非聚集索引。叶子节点存放的是主键，而不是整行记录。每张表可以有多个辅助索引。

当通过辅助索引找数据时，分成两步：先通过辅助索引找叶子节点的主键，如果索引不能覆盖，再通过主键索引找到行记录。一般通过预读来避免辅助索引的多次离散读。

（3）B+树索引的分裂

B+树索引的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。

（4）B+树索引的管理

索引的创建和删除有两种方法：一种是alter table；另一种是create/drop index。

聚集索引的创建和删除过程：（缺点：对大表耗时较长，导致不可用）

①首先创建一张新的临时表，表结构为通过alter table命令新定义的表结构。

②然后把原表中数据导入到临时表。

③接着删除原表。

④最后把临时表重命名为新表。

辅助索引的创建和删除采用FIC（Fast Index Creation），过程如下：（缺点：写操作仍然不可用）

创建时：InnoDB对创建索引的表加一个S锁。不需要重建表，速度较快。

删除时：只需删除内部视图上对该表的索引定义即可。

FIC只能用于辅助索引，聚集索引仍然要重建表。

5.4 Cardinality值

低选择性列：如性别、地区、类型等字段，可取值范围很小，不需要建B+树索引。

高选择性：某个字段可取值范围很广，几乎没有重复，则很适合B+树索引。

Cardinality值：表示索引中不重复记录数量的预估值。通过抽样获取，不是准确值。Cardinality与表中行记录总数的比值应尽可能接近1。

5.5 联合索引

联合索引是指对表上的多个列进行索引。也是一棵B+树，并且已经对第二个键值进行了排序。

5.6 覆盖索引

covering index：也称索引覆盖，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

覆盖索引的好处：辅助索引不包含整行记录的所有信息，故其大小要远远小于聚集索引，因此可以减少大量的IO操作。

当辅助索引不能覆盖时，且查询返回大量行，就算查询条件匹配了辅助索引，但这时优化器却会选择走聚集索引，用顺序读来替换随机读的查找。

5.7 MRR优化

MMR：Multi-Range Read优化，即多范围读优化。查询性能提高10倍。目的：减少磁盘的随机访问，并转化为较为顺序的访问。

MRR工作方式如下：

①将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引顺序排序的。

②将缓存中的键值根据RowID进行排序。

③根据RowID的排序顺序来访问实际的数据文件。

5.7 ICP优化

ICP：Index Condition Pushdown优化，即索引条件下沉。通过将where的部分过滤操作放在了存储引擎层，即在取出索引的同时，判断是否可以进行where条件的过滤。目的：减少上层SQL层对记录的索取。

5.8 全文检索

Full-Text Search：从InnoDB 1.2版本开始支持全文检索。全文检索使用倒排索引来实现。

Inverted index：倒排索引，也是一种索引结构，通过在辅助表中存储了单词与单词自身在一个或多个文档中所在位置的映射。利用关联数组实现，拥有两种表现形式：

①inverted file index，表现形式为 { 单词，单词所在文档ID } 。

②full inverted index，表现形式为 { 单词，（单词所在文档ID，在具体文档中的位置）} 。InnoDB采用此种方式。

Auxiliary Table：辅助表，是用来保存单词并持久化到磁盘的表。为提高并行性能，总共有6张表，每张表根据单词的Latin编码分区。

FTS Index Cache：全文检索索引缓存，是一个红黑树结构。

全文检索使用限制：

①每张表只能有一个全文检索的索引。

②由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

③不支持没有单词界定符的语言，如中文，日文，韩语。

第6章 锁

6.1 什么是锁

锁机制用于管理对共享资源的并发访问，来提供数据的完整性和一致性。latch和lock都可以称为锁。

latch：闩锁，轻量级锁，要求锁定时间非常短。InnoDB中，latch有mutex（互斥量）和rwlock（读写锁）。目的是用来保证并发线程操作临界资源的正确性。没有死锁检测机制。

lock：锁的对象是事务，用来锁定数据库中的对象，如：表、页、行。锁住的对象仅在事务提交或回滚时释放。有死锁检测机制。

6.2 InnoDB存储引擎中的锁

（1）锁的类型

InnoDB实现了两种标准的行级锁：

①共享锁（S Lock）：允许事务读一行数据。

②排他锁（X Lock）：允许事务删除或更新一行数据。

锁兼容：共享锁和排他锁都是行锁，兼容是对同一行记录锁的兼容性情况。

意向锁：Intention Lock，将锁定的对象分为多个层次，InnoDB支持的多粒度锁定允许事务在行级上的锁和表级上的锁同时存在。若将上锁的对象看成一棵树，如果对细粒度的对象上锁，那么首先需要对其上层粗粒度的对象上意向锁。如对页中row上X或S行锁，那么需要对表、页等对象上意向锁。

InnoDB只有表级别意向锁，设计目的是为了在一个事务中揭示下一行将被请求的锁类型。支持两种意向锁：

①意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁。

②意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁。

锁兼容性如下：

（2）一致性非锁定读

一致性非锁定读：指InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行上有X锁，则不需要等待X锁释放，会读取行的一个快照数据。快照数据是该行之前版本的数据，通过事务undo段实现，没有额外开销，且读取快照不需要加锁。

非锁定读是默认的读取方式，极大提高数据库的并发性。

MVCC：多版本并发控制。一个行记录可能有不止一个快照数据（事务undo段），一般称这种技术为行多版本技术，由此带来的并发控制，称为MVCC。

快照数据的定义在事务不同隔离级别下不一样：

①读已提交隔离级别下，非锁定读总是读取被锁定行的最新一份快照数据。

②可重复读隔离级别下，非锁定读总是读取事务开始时的行数据版本。

（3）一致性锁定读

InnoDB对select语句支持两种一致性锁定读，都必须在一个事务里：

①select .... for update，对读取的行记录加一个X锁。

②select .... lock in share mode，对读取的行记录加一个S锁。

（4）自增长与锁

InnoDB存储引擎提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的性能。

InnoDB中，自增长的列必须时是索引，且为索引的第一个列。

（5）外键和锁

外键主要用于引用完整性的约束检查，在InnoDB中，对于一个外键列，如果没有显式对这列加索引，InnoDB会自动对其加一个索引，来避免表锁。

对于外键值的插入或更新，首先需要查询父表中的记录，但对父表的查询操作，不能使用一致性非锁定读，因为会发生数据不一致。因此使用select .... lock in share mode方式，对父表加S锁读取。

6.3 行锁的三种算法

InnoDB存储引擎有三种行锁算法：

①Record Lock：单个行记录上的锁。

②Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

③Next-Key Lock：记录锁+间隙锁，锁定一个范围，并且锁定记录本身。

Next-Key Locking技术的锁定区间为从负无穷到正无穷的多个开闭区间范围。设计目的是为了解决幻读。当查询的索引含有唯一属性时，Next-Key Lock会降级为Record Lock，即仅锁住索引本身，而不是范围。

幻读：指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

6.4 死锁

死锁：两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

解决死锁最简单的方式是超时，即当两个事务互相等待时，当一个等待时间超过阈值时，其中一个事务回滚，另一个事务就能继续进行。缺点：当超时回滚事务较大，占用较多undo日志，则回滚代价较高。

等待图：wait-for graph，一种主动的死锁检测方式，判断图中是否存在回路来发现死锁，并选择回滚undo量最小的事务。InnoDB采用等待图来死锁检测。等待图要求数据库保存两种信息：锁的信息链表和事务等待链表。

6.5 锁升级

锁升级是指将当前锁的粒度降低。好处是锁是一种稀有资源，通过锁升级降低锁的数量，减少系统内存等锁开销，在一定程度上提高了效率。缺点是导致并发性能的降低。

InnoDB存储引擎不需要锁升级，因为其不是根据每个记录来产生行锁的，相反，它根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一条记录还是多条记录，其开销通常都是一样的。

第7章 事务

7.1 认识事务

事务分为5种类型：（InnoDB不支持嵌套事务，但支持其他4种事务）

①扁平事务：所有操作处于同一层次，不能提交或回滚事务的某一部分。

②带有保存点的扁平事务：可以回滚到同一事务中较早的一个状态。但崩溃时保存点都会消失。

③链事务：提交事务时释放不需要对象，将必要的处理上下文隐式传递给下一个要开始的事务。

④嵌套事务：顶层事务控制各个层次的子事务，子事务控制每一个局部变换，子事务可以提交或回滚，但不会马上失效，任何子事务都在顶层事务提交后才真正提交。树状结构的任何节点回滚，都会引起其全部子节点事务的回滚。

⑤分布式事务：通常是一个分布式环境下的扁平事务。

7.2 事务的实现

redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。

两者不是彼此的逆过程，都可以视为一种恢复操作。redo log恢复提交事务所修改的页操作。undo log回滚行记录到某个特定版本。两者记录内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

redo log用来保证事务的持久性，undo log用来保证事务回滚和MVCC的功能。

redo log基本都是顺序写，数据库运行时不需要读取操作；而undo log需要进行随机读写。

7.3 redo Log

重做日志由两部分组成：内存中的redo log buffer和磁盘中的redo log file。InnoDB通过Force Log at Commit机制实现持久化。即当事务提交时，必须先将事务的所有redo日志写入到redo log file进行持久化，待事务提交操作完成后才算完成。为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，都需要调用一次fsync操作，因此磁盘性能决定了事务提交性能。

Mysql二进制日志只在事务提交完成后进行一次写入。而InnoDB重做日志在事务进行中不断的被写入。

redo log block：重做日志块，每块大小512字节，重做日志buffer和file都是以block的方式存储。因为块大小和磁盘扇区大小一样，所以重做日志文件写入可以保证原子性，不需要doublewrite技术。

LSN：Log Sequence Number，日志序列号。8字节，单调递增。LSN代表三个含义：重做日志写入的总量、checkpoint的位置、页的版本。

重做日志的恢复：由于checkpoint表示刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分。存储引擎每次启动都会尝试恢复操作。因为redo是物理日志，恢复速度比binlog等逻辑日志快。

Insert操作在二进制日志中不是幂等的，而在重做日志中是幂等的。因为redo记录的是页的物理修改操作。

7.4 undo Log

undo segment：undo存放在数据库共享表空间的undo段中，undo是逻辑日志，将数据库逻辑的恢复到原来的样子。

undo的两个作用：回滚操作和MVCC。

MVCC：当用户读取一行记录时，若该事务已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读。

只读事务：在整个事务中的数据在事务开始时就决定，即使有其他会话在事务周期内修改并提交数据，也不会影响事务。 数据库只读事务也是利用undo段快照来实现的。

事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。事务提交后，并不能马上删除undo log 及undo log所在的页，这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交后，将undo放入一个链表中，由purge线程来判断和最终删除。

一个undo页存放不同事务的undo log。

7.5 group commit

InnoDB在事务提交时会进行两个阶段操作：

①修改内存中事务对应的信息，并且将日志写入重做日志缓冲。

②调用fsync将确保日志都从重做日志缓冲写入磁盘。对于该步骤，可以将多个事务的重做日志通过一次fsync刷新到磁盘，以提高性能，这就是group commit。

当开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，采用二阶段事务，其步骤如下：

一旦Server通过fsync完成二进制日志写入，就表示事务成功提交完成，即使在执行后面的步骤时发生宕机。

BLGC：Binary Log Group Commit，是一种实现机制，支持mysql数据库上层的二进制日志写入是group commit的，而且存储引擎层的redo log也是group commit的。

在Mysql数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为Leader，其他事务称为Follower，Leader控制着Follower的行为。BLGC步骤分为三个阶段：

①Flush阶段，将每个事务的二进制日志写入内存中。

②Sync阶段，将内存中多个事务的二进制日志用一次fsync操作刷新到磁盘，这就是Group Commit。

③Commit阶段，Leader根据顺序调用存储引擎层事务的提交。利用InnoDB本身的Group Commit。

7.6 分布式事务

InnoDB支持XA事务，当使用分布式事务时，InnoDB事务隔离级别必须设置为Serializable。

XA事务由一个应用程序，一个事务管理器，多个资源管理器组成。Mysql有外部XA事务和内部XA事务，分别如下：

①外部XA事务，资源管理器是Mysql数据库本身。

②内部XA事务，指存储引擎与存储引擎之间。

最常见的内部XA事务是binlog和innodb存储引擎之间的。当事务提交时，InnoDB存储引擎会先收到服务层发来的Prepare请求，此时会将事务的xid写入；接着服务层进行二进制日志写入，如果在InnoDB执行最后一步Commit操作时，数据库宕机了，那么在Mysql数据库重启后会先检查Prepare的事务xid是否已经提交，若没有，则存储引擎再进行一次提交操作。

7.7 不好的事务习惯

不好的事务习惯有：长事务、在循环中提交、使用自动提交、使用自动回滚等。

第8章 备份与恢复

8.1 备份

按备份方法不同，将备份分为：

①热备：数据库运行中直接备份。

②冷备：数据库停止后再备份。

③温备：数据库运行中进行，会对数据库操作有一定影响，如加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容，备份又分为：

①逻辑备份：一条条SQL语句的可读文本文件，恢复慢。

②裸文件备份：复制数据库的物理文件。运行中复制工具有ibbackup和xtrabackup。恢复快。

按照备份数据库的内容，备份又可分为：

①完全备份：对数据库进行一个完整的备份。

②增量备份：在上次完整备份的基础上，对更改数据进行备份。

③日志备份：对二进制日志进行备份。通过binlog重放来完成point-in-time的恢复。

8.2 快照备份

打赏

微信扫一扫

MySQL之CONCAT()的用法

转载于https://www.cnblogs.com/aiyr/p/6830593.html mysql CONCAT（）函数用于将多个字... [阅读全文]
应用openvpn

本文目录：1.vpn概述　1.1 vpn应用场景　1.2 隧道协议　1.3 SSL VPN和IPSEC VPN2.openvpn搭建client-t... [阅读全文]
DrawerLayout侧滑

DrawerLayout是Support Library包中实现了侧滑菜单效果的控件，可以说DrawerLayout是因为第三方控件如Sliding... [阅读全文]
sql server 2012 链接服务器不能链接sql server 2000的解决方案

把原来的sql server 2005直接装成了2012，然后在建立链接服务器链接一台sql server 2000的服务器时... 14-05-09 [阅读全文]
前端js日期控件传时间类型到java后台转Date类型

把以下两个工具类放到项目里，然后那个接收参数的bean，参数变量前加上2个注解。 @JsonSerialize(using=DateJso... [阅读全文]