[大数据]MySQL技术内幕

MySQL技术内幕

MySQL体系结构和存储引擎

定义数据库和实例

实例：MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。数据库实例才是真正用于操作数据库文件。

MySQL被设计为一个单进程多线程架构的数据库。MySQL数据库实例在系统上的表现就是一个进程。

MySQL数据库中，可以没有配置文件，在这种情况下，MySQL会按照编译时的默认参数设置启动实例，MySQL数据库会以读取到的最后一个配置文件中的参数为准。

MySQL体系结构

MySQL由以下几部分组成：

连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件

存储引擎是基于表的，而不是数据库。

MySQL存储引擎

InnoDB引擎支持事务，其设计目标主要面向在线事务处理的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认操作不会产生锁。从MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

InnoDB通过多版本并发控制来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATEABLE级别，同时，使用一种被称为next-key locking的策略来避免幻读现象的产生。

对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键

MylSAM存储引擎

MylSAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP数据库应用，在MySQL5.5.8版本之前MylSAM存储引擎是默认的存储引擎(除Windows版本外)。MylSAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件。

NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC集群。NDB的特点是数据全部放在内存种，因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性地提高数据库性能，是高可用、高性能的集群系统。

Memory存储引擎

Memory存储引擎将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表，Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引。

Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作，从MySQL 5.1开始支持索引

Federated 存储引擎

Federated 存储引擎并不存放数据，它只是指向一台远程MySQL数据库服务器上的表，这非常类似于SQL Server的链接服务器和Oracle的透明网关，不同的是Federated 存储引擎只支持MySQL数据库表，不支持异构数据库表

Maria 存储引擎

Maria 存储引擎是新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而称为MySQL的默认存储引擎。

通过SHOW ENGINES语句可以查看当前使用的MySQL数据库所支持的存储引擎

连接MySQL

本质上是进程通信

TCP/IP

TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式，这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端在一台服务器上，而MySQL实例(server)在另一台服务器上，这两台及其通过一个TCP/IP网络连接。

比如用户可以在window服务器下请求一台远程linux服务器下的MySQL实例

UNIX套接字

在Linux和Unix环境下，还可以使用UNIX域套接字，UNIX域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在一台服务器上的情况下使用。

InnoDB存储引擎

版本

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P5M0dfNZ-1633272866275)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210922212206128.png)]

体系结构

InnoDB存储引擎有多个内存块，可以认为这些内存块组成一个大的内存池，负责如下工作：

• 维护所有进程/线程需要访问的多个内部数据结构
• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存
• 重做日志缓存

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nxnXdMQA-1633272866278)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210922212256403.png)]

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。

后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。

1、Master Thread

Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收等。

2、IO Thread

在InnoDB存储引擎中大量使用了AIO来处理写IO请求，这样可以极大提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调处理。

3、Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。

内存

1、缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，因此可将其视为基于磁盘的数据库系统。

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响，在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程被称为页"fix"在缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中，若在缓冲池中，称该页在缓冲池中被命中，直接读取该页，否则，读取磁盘上的页。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lxazb36M-1633272866280)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210922213700134.png)]

2、LRU

InnoDB存储引擎对传统的LRU(Latest Recent Used 最近最少使用)算法做了一些优化，在InnoDB存储引擎中，LRU列表还加入了midpoint位置，新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入LRU列表的midpoint位置。在默认配置i中，midpoint位置在LRU列表长度的5/8处。

为什么不采用朴素的LRU算法，直接将读取的页放入到LRU列表的首部呢？这是因为若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新处，从而影响缓冲池的效率。

如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。

为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

3、重做日志缓冲

InnoDB存储引擎首先将重做日志先放入到这个缓冲区，然后按一定的频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置很大

4、额外的内存池

在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

Checkpoint技术

checkpoint技术的目的是解决以下几个问题：

• 缩短数据库的恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用时，刷新脏页

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复时间。

Master Thread工作方式

Master Thread 具有最高的线程优先级别，其内部由多个循环组成：主循环、后台循环、刷新循环、暂停循环。

InnoDB 关键特性

插入缓冲

对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗数据库这个非聚集的索引已经插入到叶子节点，而实际并没有，只是存放在另一个位置，然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能将多个插入合并到一个操作中。

Change Buffer

对一条记录进行UPDATE操作可能分为两个过程：

• 将记录标记为已删除
• 真正将记录删除

两次写

doublewrite由两部分组成，一部分是内存中的doublewrite buffer，另一部分是物理磁盘上共享表空间的连续的页。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过double buffer再分两次，每次1MB顺序地写入，然后同步磁盘，避免缓冲写带来的问题。

自适应哈希索引

InnoDB存储引擎会监视对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引。

异步IO

用户可以在发出一个IO请求后立即再发出另一个IO请求，异步IO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为一个IO

刷新邻接页

当刷新一个脏页时，InnoDB存储引擎会检测该页所在区的所有页，如果是脏页，那么一起进行刷新

文件

参数文件

参数

可以把数据库参数看成一个键值对

参数类型

MySQL数据库的参数可以分为两类：

动态参数、静态参数

动态参数意味着可以在MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像只读的。可以通过SET命令对动态参数值进行修改。

SET的global和session关键字，它们表明该参数是基于当前会话还是整个实例的生命周期

日志文件

日志文件记录了影响MySQL数据库的各种类型获得，MySQL数据库中常见的日志文件有：

错误日志、二进制日志、慢查询日志、查询日志

错误日志

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

慢查询日志

慢查询日志可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。在默认情况下，MySQL数据库并不启动慢查询日志

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行

二进制日志

二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。

套接字文件

套接字文件可由参数socket控制，一般在/tmp目录下，名为mysql.sock

pid文件

当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件可由参数pid_file控制，默认位于数据库目录下

表结构定义文件

不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义

frm还用来存放视图的定义

InnoDB存储引擎文件

表空间文件

InnoDB采用将存储的数据按表空间进行存放的设计，在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件，用户可以通过参数对其进行设置

重做日志文件

重做日志文件对InnoDB存储引擎至关重要，它们记录了对于InnoDB存储引擎的事务日志。每个InnoDB存储引擎至少有1各重做日志文件组，每个文件组下至少有2个重做日志文件

重做日志文件的大小设置对于InnoDB存储引擎的性能有非常大的影响。

因为重做日志的存在，才使得InnoDB存储引擎可以提高可靠的事务。

表

在InnoDB存储引擎中，表都是根据主键顺序阻止存放的，这种存储方式的表称为索引组织表，在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主题，则InnoDB存储引擎会按如下方式选择或创建主键

首先判断表中是否有非空的唯一索引，如果有，则该列即为主键

如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针

InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称为表空间。表空间又由段、区、页组成。页在一些文档中有时也称为块

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fhTungKi-1633272866282)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210923210018844.png)]

表空间

表空间可以看作是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中

段

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

区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。

页

页是InnoDB磁盘管理的最小单位

行

InnoDB存储引擎是面向列的，也就是说数据是按行进行存放的，每个页的存放行记录也是有硬性定义的

InnoDB行记录格式

InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据，这也是目前使用最多的一种格式。

Compact行记录格式

设计目标是高效地存储数据

行记录格式的首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DKaaLhac-1633272866286)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210923211226878.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WWemgu6b-1633272866288)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210923211236619.png)]

Redundant 行记录格式

Redundant是MySQL5.0版本之前的InnoDB的行记录存储方式，MySQL5.0支持Redundant是为了兼容之前版本的页格式。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4iWrTqe7-1633272866289)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210923211756239.png)]

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，一般认为BLOB、LOB这类的大数据对象列类型的存储会把数据存放在数据页面之外，但是即使是VARCHAR列数据类型，依然有可能被存放为行溢出数据

Compressed和Dynamic行记录格式

两种新的记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式

CHAR的行结构存储

CHAR是存储固定长度的字符类型。而在前面的小节中，用户已经了解行结构的内部的存储，并可以发现每行的变长字段长度的列表都没有存储CHAR类型的长度。

InnoDB数据页结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Owltufq9-1633272866291)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210923212447925.png)]

约束

数据完整性

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。

一般来说，数据完整性有以下三种形式：

1. 实体完整性：

   保证表中有一个主键

2. 域完整性

   保证数据每列的值满足特定的条件

3. 参照完整性

   保证两张表之间的关系

约束的创建和查找

约束的创建可以采用以下两种方式：

表建立时就进行约束定义、利用ALTER TABLE命令来创建约束

约束和索引的区别

索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

约束是一个逻辑的概念，用来保证数据的完整性

对错误数据的约束

在某些默认设置下，MySQL数据库允许非法的或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值。

ENUM和SET约束

MySQL数据库不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决一部分这样的约束需求

触发器与约束

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

创建触发器的命令是CREATE TRIGGER，只有具备super权限的MySQL数据库用户才可以执行这条命令

外键约束

外键用来保证参照完整性

视图

视图的作用

视图的主要用途之一就是被用做一个抽象装置

物化视图

Oracle数据库支持物化视图——该视图不是基于基表的虚表，而是根据基表实际存在的实表

分区表

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区，此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

当前MySQL数据库支持以下几种类型的分区：

• RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区
• LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值
• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
• KEY分区：根据MySQL数据库提供的哈希函数来进行分区

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

这四种分区的条件是数据必须是整型，COLUMN分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转换为整型。

子分区

在分区的基础上再进行分区，有时也称这种分区为复合分区。

分区中的NULL值

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。MySQL数据库的分区总是视NULL值小于任何的一个非NULL值

分区和性能

数据库的应用分为两类：一类是OLTP（在线事务处理）如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。

对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。

然而对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能获取一张大表中10%的数据，大部分是通过索引返回几条记录即可。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

在表和分区间交换数据

MySQL5.6开始支持ALTER TABLE ... EXCHANGE PARTITION语法。该语句允许分区或子分区的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中，若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。

索引与算法

InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

B+树

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。

二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出。

平衡二叉树首先符合二叉查找树的定义，其次必须满足任何节点的两个子树高度最大差为1。

B+树由B树和索引顺序访问法演化而来

聚集索引

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

辅助索引

对于辅助索引（也称非聚集索引），叶子节点并不包含行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点中的索引行还包含了一个书签。

Cardinality值

Cardinality值表示索引中不重复记录数量的预估值。在实际应用中Cardinality/n_rows_in_table应该尽可能的接近1，如果非常小，那么用户需要考虑是否还有必要创建这个索引。

B+树索引的使用

对于OLTP应用，B+树建立索引后，对于该索引的使用应该只是通过该索引取得表中少部分数据，这时建立B+树索引才有意义，否则即使建立了，优化器也可能选择不使用索引

对于OLAP应用，都需要访问表中大量的数据，目的是为决策者提高支持，因此OLAP中索引的添加根据应该是宏观的信息，而不是微观。

联合索引

联合索引是指对表上的多个列进行索引。

覆盖索引

即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

优化器选择不使用索引的情况

在某些某些情况下，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，这种情况多发生于范围查找、JOIN链接操作等情况下。

全文检索

MySQL支持全文检索的查询，其语法为MATCH...AGAINST()，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去查询

锁

什么是锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的开发访问。InnoDB存储引擎会在行级别上对表数据上锁，不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。

lock与latch

latch一般称为闩锁(轻量级的锁)，在InnoDB存储引擎中，latch又可以分为mutex(互斥量)和rwlock（读写锁），其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制

lock的对象是事务，用来锁定的是数据库中的对象

InnoDB存储引擎中的锁

类型

InnoDB存储引擎实现了如下两种标准的行级锁：

• 共享锁，允许事务读一行数据
• 排他锁，允许事务删除或更新一行数据

如果一个事务T1已经获得了行r的共享锁，那么另外的书屋T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。

但若其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁。意向锁将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

一致性非锁定锁

是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这时读取数据不会因此去等待行上锁的释放，相反地，InnoDB存储引擎会去读取行的一个快照数据

一致性锁定读

InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读操作：

• SELECT..FOR UPDATE
• SELECT..LOCK IN SHARE MODE

第一个是对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁，第二个是对读取的行基记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

自增长与锁

在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器，当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，插入操作会依据这个自增长的计数器值加1赋予自增长列，这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不上在一个事务完成后才释放，而是在完成对自增长插入的SQL语句后立即释放。

外键和锁

对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样避免表锁

锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁
• Osp Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

解决 Phantom Problem

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

将会话事务的隔离级别设置为READ COMMITTED

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jgfRarvi-1633272866292)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210924204027496.png)]

锁问题

脏读

脏读指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的。

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到是已经提交的数据，但是其违反了数据库事务一致性的要求。

丢失更新

丢失更新是另一个锁导致的问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞，阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务将无法推进下去。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

当前数据库还都普遍采用wait-for-graph的方式来进行死锁检测。

锁升级

锁升级是指将当前锁的粒度降低。如果在数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级的现象。

事务

InnoDB存储引擎中的事务完全符合ACID的特性

概述

A：原子性，要么都做，要么都不做

C：一致性，一致性指事务将数据库从一种状态转变为下一种一致的状态。

I：隔离性，要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务都不可见，通常这使用锁来实现

D：持久性，事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

分类

从事务理论的角度来说，可以把事务分为以下几类：

• 扁平事务
• 带有保存点的扁平事务
• 链事务
• 嵌套事务
• 分布式事务

扁平事务是事务类型中最简单的一种，但在实际生产环境中，这可能是使用最为频繁的事务，在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚，因此扁平事务是应用程序成为原子操作的基本组成模块。

带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。

链事务：可视为保存点模式的一种变种。链事务中的回滚仅限于当前事务，即只能恢复到最近一个的保存点。对于锁的处理，两者也不相同。链事务在执行COMMIT后即释放了当前事务所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。

事务的实现

事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log 称为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。

redo通过是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

redo

重做日志用来实现事务的持久性，由两部分组成：一部分是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sAAI03d6-1633272866293)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211003213427555.png)]

通过的头部格式由以下3部分组成：

• redo_log_type：重做日志的类型
• space：表空间的ID
• page_no：页的偏移量

之后的redo log body部分，根据重做日志类型的不同，会有不同的存储内容

undo

与redo不同，undo存放在数据库内部的一个特殊段中，这个段称为undo段。undo段位于共享表空间内。

undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子，所有的修改都被逻辑的取消了，但是数据库和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。因此不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

事务控制语句

在MySQL命令行的默认设置下，事务都是自动提交的，因此要显式地开启一个事务需使用命令BEGIN、START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，禁用当前会话的自动提交

START TRANSACTION | BEGIN：显式地开启一个事务

COMMIT：要使用这个语句的最简形式，只需发出COMMIT。COMMIT会提交事务，并使得已对数据库做的所有修改称为永久性的。

ROLLBACK回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

SAVEPOINT identifierSAVEPOINT 允许在事务中创建一个保存点，一个事务中可以由多个SAVEPONIT

RELEASE SAVEPOINT identifier删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常

ROLLBACK TO[SAVEPOINT] identifier这个语句与SAVEPOINT命令一起使用，可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作

SET TRANSACTION这个语句用来设置事务的隔离级别

对于事务操作的统计

由于InnoDB存储引擎是支持事务的，因此InnoDB存储引擎的应用需要在考虑每秒请求时的同时，应该关注每秒事务处理TPS的能力

计算TPS的方法是(com_commit+com_rollback) / time。但是利用这种方法进行计算的前提是：所有的事务必须都是显式提交的，如果存在隐式地提交和回滚，不会计算到com_commit + com_rollback变量中。

事务的隔离级别

READ UNCOMMITTED称为浏览访问，仅仅针对事务而言的。

READ COMMITED称为游标稳定

SERIALIZABLE称为隔离

InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因为避免幻读的产生

分布式事务

分步式事务是允许多个独立的事务资源参与到一个全局的事务中，事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交要么都回滚，另外，在使用分布式事务时，InnoDB存储引擎的事务隔离等级必须设置为SERIALIZABLE

备份与恢复

概述

Hot Backup（热备）直接在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响

Cold Backup（冷备）备份操作是在数据库停止的情况下，这种一般只需要复制相关的数据库物理文件即可

Warm Backup（温备）同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响

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

• 逻辑备份 备份出的文件内容是可读的，一般是文本文件
• 裸文件备份 复制数据库的物理文件，既可以是在数据库运行中的复制，也可以是在数据库停止运行时直接的数据文件复制，这类备份的恢复时间往往较逻辑备份短很多

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

• 完全备份 是指对数据库备份进行一个完整的备份
• 增量备份 在上次完全备份的基础上，对于更改的数据进行备份
• 日志备份 主要是指对MySQL数据库二进制日志的备份

MySQL数据库复制的原理就是异步实时地将二进制日志重做传送并应用到从数据库

快照备份

MySQL数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区中，然后对该分区进行快照操作。

复制

复制是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用，总体来说，replication的工作原理分为以下3个步骤：

1、主服务器把数据更改记录到二进制日志中

2、从服务器把主服务器的二进制日志复制到自己的中继日志中

3、从服务器重做中继日志中的日志，把更改应用到自己的数据库中，以达到数据的最终一致性

性能调优

选择合适的CPU

用户首先需要清楚当前数据库的应用类型，一般而言，可分为两大类：OLTP和OLAP

InnoDB存储引擎一般都应用于OLTP的数据库应用，这种应用的特点如下：

• 用户操作的并发量大
• 事务处理的时间一般比较短
• 查询的语句较为简单，一般都走索引
• 复杂的查询较少

可以看出OLTP的数据库应用本身对CPU的要求不是很高因为复杂的查询可能需要执行比较、排序、连接等非常耗CPU的操作，这些操作在OLTP的数据库应用中较少发生。

内存的重要性

在开发应用前预估"活跃"数据库的大小是多少，并以此确定数据库服务器内存的大小

硬盘对数据库性能的影响

机械硬盘和固态硬盘

合理地设置RAID

RAID的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区，RAID看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。

RAID的作用是：

• 增强数据集成度
• 增强容错功能
• 增加处理量或容量

操作系统的选择

Linux是MySQL数据库服务器中最常使用的操作系统，与其他操作系统不同的是Linux有着众多的发行版本，每个用户的偏好可能不尽相同。然而将Linux操作系统作为数据库服务器时需要考虑更多的是操作系统的稳定性，而不是新特性。

制到自己的中继日志中

3、从服务器重做中继日志中的日志，把更改应用到自己的数据库中，以达到数据的最终一致性

性能调优

选择合适的CPU

用户首先需要清楚当前数据库的应用类型，一般而言，可分为两大类：OLTP和OLAP

InnoDB存储引擎一般都应用于OLTP的数据库应用，这种应用的特点如下：

• 用户操作的并发量大
• 事务处理的时间一般比较短
• 查询的语句较为简单，一般都走索引
• 复杂的查询较少

可以看出OLTP的数据库应用本身对CPU的要求不是很高因为复杂的查询可能需要执行比较、排序、连接等非常耗CPU的操作，这些操作在OLTP的数据库应用中较少发生。

内存的重要性

在开发应用前预估"活跃"数据库的大小是多少，并以此确定数据库服务器内存的大小

硬盘对数据库性能的影响

机械硬盘和固态硬盘

合理地设置RAID

RAID的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区，RAID看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。

RAID的作用是：

• 增强数据集成度
• 增强容错功能
• 增加处理量或容量

操作系统的选择

Linux是MySQL数据库服务器中最常使用的操作系统，与其他操作系统不同的是Linux有着众多的发行版本，每个用户的偏好可能不尽相同。然而将Linux操作系统作为数据库服务器时需要考虑更多的是操作系统的稳定性，而不是新特性。

另外FreeBSD也是另一个常见优秀的操作系统。