Mysql体系结构和存储引擎

基本概念

  数据库:物理操作系统文件或其他形式文件类型的集合。(是文件的集合,是依照某种数据模型组织起来并存放于二级存储器中的数据集合。)

  数据库实例:由数据库后台进程/线程以及一个共享内存区域组成。(是应用程序,是位于用户与操作系统之间的一层数据管理软件,用户对数据库的任何操作都是在数据库实例下进行的,应用程序只有通过数据库实例才能和数据库打交道。)一个实例对应一个数据库,但在集群环境下,一个数据库可能对应多个实例

数据库是由一个一个文件组成的,如果要对这些文件执行诸如SELECT、INSERT、UPDATE和DELETE之类的操作,不能通过简单的操作文件来更改数据库的内容,需要通过数据库实例来完成对数据库的操作。

Mysql是一个单线程多进程的应用

preview

Mysql体系架构

image.png

组成部分:

  • 连接池组件
  • 管理服务和工具组件
  • SQL接口组件
  • 查询分析器组件
  • 优化器组件
  • cache组件
  • 插件式存储引擎 (最重要的特色)
  • 物理文件

 MySQL区别于其他数据库的最重要的特点是插件式的表存储引擎。(存储引擎是底层物理结构的实现,它是基于表的,而不是基于数据库

常见引擎

InnoDB

  • InnoDB存储引擎支持事务,主要面向在线事务处理(OLTO)方面的应用。
  • 特点:行锁设计,支持外键,并支持非锁定读(即默认情况下读取操作不会产生锁)。
  • MSQL在windoes版本下的InnoDB是默认的存储引擎。
  • InnoDB将数据放在一个逻辑的表空间中,这个表空间就想黑盒一样由InnoDB自身进行管理。从MySQL4.1版本开始,它可以将每个InnoDB存储引擎的表单独放在一个独立的ibd文件中。
  • InnoDB通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准中的4种隔离级别,默认为REPEATABLE级别,同时使用一种被称为next-key locking的策略来避免幻读。 除此之外,InnoDB还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。
  • 对于表中数据的存储,InnoDB存储引擎采用了聚集的方式,这种方式类似于Oracle的索引聚集表。每张表的存储都按主键的顺序存放,如果没有显式地在表定义时指定主键,InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。

MyISAM

  • 特点:不支持事务、表锁和全文索引,对于一些OLAP(在线分析处理)操作速度快。除windows版本外,是所有其他版本默认的存储引擎。
  • MyISAM存储引擎表由MYD和MYI组成,MYD用来存放数据文件,MYI用来存放索引文件。
  • 对于MyISAM存储引擎表,Mysql数据库只缓存其索引文件,数据文件的缓存交由操作系统本身来完成,这与其他使用LRU算法缓存数据的大部分数据库大不相同。

Memory:

InnoDB引擎

体系架构

简略版

image-20220425110154818

详细版

baa2ac28-2778-473d-b2a4-e1a8ede2689b

说白了就是一堆线程操作一堆内存和一堆文件

后台线程

  • InnoDB是多线程模型,后台有多个不同的线程,分别处理不同的任务。
  • Master Thread,在各种loop中跳转,低版本中,负责脏页刷新,合并插入缓存,undo 页的回收
  • IO Thread,负责 IO 请求
  • Purge Thread,1.1 才出现,负责 undo 页的回收
  • Page Cleaner Thread,1.2才出现,负责脏页刷新

缓冲池

  • 它的作用就相当于Redis对于Mysql的作用,不过这里缓冲池就是为了缓解磁盘的速度慢,并且,缓存的最小单位是页(默认16K),因为磁盘的管理是基于页的。
  • 当修改了缓冲池中的数据后,以一定的频率刷回磁盘(基于checkpoint机制)
  • 当然缓冲池里面不仅仅是以页为单位的数据页和索引页,只不过是这两部分占据了缓冲池的大部分。

LRU、Free、Flush List

  • LRU List,当缓冲池不够的时候,使用LRU算法来进行淘汰,但是这里的LRU并不是真正的LRU(新来的放到list表头,然后不够了淘汰表尾的。),这里是根据一个参数innodb_old_blocks_pct,来确定新的缓冲页放在list什么位置。比如这个参数为20(默认为37),那么新的缓冲页会放在链表的4/5处(从左至右)。这样做是为了避免全表扫描的时候,把很多页都加载进来,然后把链表撑爆了,以前缓冲的数据全被冲刷了。

  • Free List,说白了Free List就是一段待用的内存,因为系统刚启动的时候LRU List并没有分配内存,当开始查询,需要进行缓存的时候,LRU的内存从哪儿来,就是从Free List那里分割出来,随着查询越来越多,总有时候Free被分割完毕了,在缓冲新的数据,就会执行LRU淘汰过程了。

  • Flush List,当缓冲池中的数据被修改后,就变成了脏页(与磁盘数据不一致),那么这些脏页会被统一的放到一个list中等待被刷回(实际上是拷贝,LRU和Flush同时存在这两个页),这个list就是Flush List。

关键特性

插入缓冲

Insert buffer(插入缓冲)并不是缓冲池的一个部分,它和数据页一样,是物理页的一个组成部分。我们知道,主键是行唯一的标识符,在应用程序中记录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚集索引一般是顺序的,不需要磁盘的随机读取。因此,这样的情况下,插入操作一般很快就能完成。但是,不可能每张表上只有一个聚集索引,在更多情况下,一张表上有多个非聚集的辅助索引

比如以name作为查找字段,并且name不唯一,这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时,数据页的存放还是按主键id的执行顺序存放,但是对于非聚集索引,叶子结点的插入不再是顺序的了。这时就需要离散的访问非聚集索引页,插入性能在这里变低了。然而这并不是name字段上索引的错误,因为B+树的特性决定了非聚集索引插入的离散型

插入缓冲对于非聚集索引的插入和更新操作,不是每一次直接插入索引页中,而是先判断插入的非聚集索引页是否在缓存池中。如果在,则直接插入;如果不在,则先放入一个插入缓冲区中,好似欺骗数据库这个非聚集的索引已经插入到叶子结点了,然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对非聚集索引执行插入和修改操作的性能。

  插入缓冲的使用要满足两个条件:

  • 索引是辅助索引
  • 索引不是唯一的
  • 辅助索引不能是唯一的,因为在把它插入到插入缓冲时,我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况,插入缓冲就失去了意义。

存在的问题:

在写密集的情况下,插入缓冲会过多的占用缓冲池内存,默认情况下最大可以占用1/2的缓冲池内存。

两次写

插入缓冲带给InnoDB存储引擎性能,两次写带给InnoDB数据的可靠性。

当数据库宕机时,可能发生数据库正在写一个页面,而这个页只写了一部分的情况,我们称之为部分写失效。

可能会想,如果发生写失效,可以通过重做日志进行恢复。但是需要知道,重做日志中记录的是对页的物理操作,如果这个页本身已经损坏,在对其进行重做是没有意义的。这就是说,在应用重做日志前,我们需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再重做日志,这就是两次写

img

doublewrite由两部分组成:一部分是内存中的doublewrite buffer,大小为2MB;另一部分是物理磁盘上共享表空间中连续的128个页,即两个区,大小同样为2MB。

当缓冲区的脏页刷新时,并不直接写磁盘,而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer。之后通过doublewrite buffer再分两次,每次写入1MB到共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘,避免缓冲写带来的问题。在这个过程中,因为doublewrite页是连续的,因此这个过程是顺序写的,开销并不是很大。在完成doublewrite页的写入后,再将doublewrite buffer中的页写入到各个表空间文件中,此时的写入则是离散的。

**如果操作系统在将页写入磁盘的过程中崩溃了,在恢复过程中,InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本,将其拷贝到表空间文件,再应用重做日志。

自适应哈希索引

InnoDB存储引擎会监控对表上索引的查找,如果观察到建立哈希索引可以带来速度的提升,则建立哈希索引,所以称为自适应的自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快,而且不需要将整个表都建哈希索引,InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

需要注意的是,哈希索引只能用来搜索等值的查询,而对于其他类型,如范围查找,是不能使用的。

异步IO

TODO

刷新临近页

TODO

表结构

InnoDB存储引擎表类型

  在InnoDB存储引擎表中,每张表都有个主键,如果在创建表时没有显式地定义主键,则InnoDB存储引擎会按如下方式选择或创建主键:

  • 首先表中是否有非空的唯一索引,如果有,则该列即为主键。
  • 不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。

InnoDB逻辑存储结构

 所有数据都被逻辑地存放在一个空间中,我们称之为表空间。表空间又由段(segment)区(extent)、**页(page)**组成,页有时也称为块。

img

表空间

  • 表空间可看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都是存放在表空间中。默认情况下InnoDB有一个共享表空间ibdata1,即所有数据都放在这个表空间内,如果启用参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。
  • 在启用参数后,需要注意的是:每章表的表空间内存放的只是数据、索引和插入缓冲,其他类的数据,如撤销信息、系统事务信息、二次写缓冲等还是放在原来的共享表空间内。

  

  • 表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等。前面介绍过InnoDB存储引擎表是由索引组织的,因此数据即索引,索引即数据。那么数据段即为B+树的页节点(图4-1中的leaf node segment),索引段即为B+树的非索引节点(none-leaf node segment)。
  • 注意并不是每个对象都有段。因此更准确的说,表空间是由分散的页和段组成。

  

  • 区是由64个连续的页组成的,每个页大小为16KB,即每个区大小为1MB。对于大的数据段,InnoDB存储引擎最多每次可以申请4个区,以此来保证数据的顺序性能。
  • 在每个段开始时,先有32个页大小的碎片页来存放数据,当这些页使用完之后才是64个连续页的申请。

  

  • 页是InnoDB磁盘管理的最小单位
  • 常见页类型有:数据页、Undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

  

  • InnoDB存储引擎是面向行的,也就是说数据的存放按行进行存放每个页最多存放7992行记录

**InnoDB物理存储结构 **

物理意义上,InnoDB表由共享表空间、日志文件组和表结构定义文件组成。

Compact行记录格式:(MySQL5.0开始被引入的)设计目标是能高效存放数据。简单来说,如果一个页中存放的行数据越多,其性能就越高。

      img

表约束

(1)数据完整性

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

  • 实体完整性
    • 保证表中有一个主键。在innodb存储引擎表中,我们可以通过定义Primary key或Unique key约束来保证实体的完整性。或者我们还可以通过编写触发器来保证数据完整性。
  • 域完整性
    • 保证数据的值满足特定的条件。在innodb中,域完整性可通过以下途径来保证:选择合适的数据类型可以确保一个数据值满足特定条件,外键约束,编写触发器。还可以考虑用default约束作为强制域完整性的一个方面。
  • 参照完整性
    • 保证两张表之间的关系。InnoDB支持外键,因此允许用户定义外键以强制参照完整性,也可以编写触发器强制执行。

  对于InnoDB,提供了4种约束

  • Primary Key(主键)
  • Unique key
  • Foreign Key(外键)
  • Default
  • Not null

(2)约束和索引的区别:

  当你创建了一个唯一索引,就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的,约束更是一个逻辑的概念,用来保证数据的完整性,而索引是一个数据结构,有逻辑上的概念,在数据库中更是一个物理存储的方式

(3)触发器与约束:

  触发器的作用是在insert、delete和update命令之前或之后自动调用sql命令或者存储过程。我觉得它就像一个脚本一样,对于单个表有效。 

  创建触发器命令:create trigger

  最多可以为一个表建立6个触发器,分别为insert、update、delete的before和after各定义一个。(before和after代表触发器发生的时间,表示是在每行操作的之前发生还是之后发生)

(4)视图:

  视图(View)是一个命名的虚表,它由一个查询来定义,可以当作表使用。与持久表不同的是,视图中的数据没有物理表现形式

  视图的主要用途之一是被用作一个抽象装置程序本身不用关心基表的结构,只需要按照视图定义来获取数据或更新数据。因此,视图同时在一定程度上起到一个安全层的作用。

 create view v_t as select * from t where id<10;

数据页结构

84f33a0f-109c-4f0a-8844-ef09a570da23

其中比较刺眼,或者让人比较亲切的两个组成部分

  • FIL_PAGE_PREV,当前页的上一个页。
  • FIL_PAGE_NEXT,当前页的下一个页。

从这两个组成部分,直接和B+树的叶子结点对应起来了,我们可以猜测。所有的数据页,组成一个双向链表,共同构成一颗B+树的叶子结点(当然这里的B+树是聚簇索引构成的B+树)。并且B+树的结点实际上是页(而不是前面提到的所谓的段,作者想表达的意思应该是数据段中的数据页)

索引与算法

InnoDB的主要索引

1.简单来说,数据库索引就是数据库的数据结构!进一步说则是该数据结构中存储了一张表中某一列的所有值,也就是说索引是基于数据表中的某一列创建的。总而言之:一个索引是由表中某一列上的数据组成,并且这些数据存储在某个数据结构中。

2.**索引的作用。**举个例子,假设有一张数据表Emplyee,该表有三列:Employee_name,Employee_age,Employee_address

表中有几万条记录。现在要执行下面这条查询语句,查找出所有名字叫“Jesus”的员工的详细信息:Select * from Employee where Employee_name=‘Jesus’

3.如果没有数据库索引功能,数据库系统会逐行的遍历整张表,对于每一行都要检查其Employee_Name字段是否等于“Jesus”。因为我们要查找所有名字为“Jesus”的员工,所以当我们发现了一条名字是“Jesus”的记录后,并不能停止继续查找,因为可能有其他员工也叫“Jesus”。这就意味着,对于表中的几万条记录,数据库每一条都要检查。这就是所谓的“全表扫描”( full table scan)

4.而数据库索引功能索引的最大作用就是加快查询速度,它能从根本上减少需要扫表的记录/行的数量。

5.如何创建数据库索引。可以基于Employee表的两列创建索引即可:CREAT INDEX name_index ON Employee(Employee_name,Employee_age)

InnoDB存储引擎支持三种常见的索引:一种是B+树索引,另一种是hash索引,还有全文索引。

  • InnoDB支持的哈希索引是自适应的,会根据表的使用情况自动为表生成hash索引,不能人为干预是否在一张表中生成hash索引。

  • B+树索引就是传统意义上的索引,这是目前关系型数据库系统中最常用、最有效的索引。B+树索引的构造类似于二叉树,根据键值快速找到数据,需要注意:B+树的B不是代表二叉树,而是代表平衡,B+树从最早的平衡二叉树演化而来,但是B+树不是一个二叉树

  • 全文索引 TODO

B+树索引并不能找到一个给定键值的具体行。B+树能找到的只是被查找数据行所在的页,然后数据库通过把页读入内存,再在内存中进行查找

B+树插入规则

插入规则

img

直接上例子

特别注意,下面的B+树有些叶子结点没有连接成链表,因为书上出于排版考虑,懂意思就行。

a324e5d4-0414-413a-a0d7-a714f7924fb4

插入28,根据索引结点直接找下来,插进去就完事儿,没啥好说的

0c6a857b-aabb-4dd9-9ffc-38cf897aa014

插入70,找下来,找到50那个页,发现满了,分裂,然后选中间的结点向上合并为索引结点,被合并的结点放在分裂的右半部分。

fcfd8bcb-7830-4c4d-95b2-6090bd20b37e

插入95,发现叶子结点满了,那么分裂,发现索引结点也满了,索引结点分裂。

首先分裂叶子结点,85向上合并。

再分裂索引结点,注意索引结点的分裂规则和叶子结点分裂规则不一样,以60分裂开,60向上合并(在这里是成了只有一个元素的独立的结点),这里并没有将60归为分裂的右半部分。这是和叶子结点分裂的不同之处。

6356699d-afa7-4424-9693-5ca43a562e7a

B+树删除规则

TODO

B+树索引

自己之前看了很多这方面的视频,感觉已经差不多了,再看看书就OK了。(鸽)

关于分裂

在说B+树插入的时候,我们说如果结点满了,需要进行分裂,那里我们的规矩是直接以中间结点为基准进行分裂,然而在B+树拿来当索引的时候,实际上我们并不是严格按照那个要求来,我们可以通过一些参数来控制我们的B+树再需要进行分裂的时候以什么地方为基准点进行分裂。

这样做的目的是什么呢?其实就是为了避免 自增主键造成页结点的空间浪费。设想,我们的主键是自增的,那么每次插入,其实都会按顺序插入,这样就会造成前面的结点分裂过后,分裂页的前半部分实际上存在空间永远没法使用

聚集索引

  • 即主键索引
  • 叶子节点存放的是行记录数据所在的页,而页中的每一行都是完整的行(叶子节点也被称为数据页)
  • 针对范围查询也比较快

聚集索引图:

其中,根节点部分的Key:80000001代表主键为1;Pointer:0004代表指向数据页的页号(即第4页);

数据页节点的的PageOffset:0004代表第4页,其中存储的数据是完整的每一行。

img

辅助索引

  • 叶子节点存放的也是行记录数据所在的页,但还是页中存放的不是完整的行,而是仅仅是一对key-value和一个指针,该指针指向相应行数据的聚集索引的主键
  • 假设辅助索引树高3层,聚集索引树为3层,那么根据辅助索引查找数据,需要先经过3次IO找到主键,再经过3次IO找到行做在的数据页
  • 针对辅助索引的插入和更新操作:辅助索引页如果在缓冲池中,则插入;若不在,则点放到InsertBuffer对象中,之后在以一定的平率进行InsertBuffer和辅助索引页子节点的合并

辅助索引图:

其中,idx_c表示对第c列做了索引;idx_c中的Key:7fffffff代表c列的一个值,其实是-1;idx_c中的Pointer:80000001代表该行的主键是80000001,即1;下面的就是聚集索引部分。

img

5、联合索引(多列索引)

  • 左边匹配原则(如果索引为(a,b),则where a=x可以用到索引,但是b=x用不到,如果是覆盖索引有可能会用到)

覆盖索引

  • 从辅助索引中直接获取记录
  • 对于统计操作,例如count(1),有可能联合索引,右边也会匹配(优化器自己会做),因为count(1)操作不需要获取整行的详细数据,所以不需要去聚集索引的叶子节点去获取数据,直接在辅助索引树中就完成了操作
  • select username from xxx where username=‘lisi’,如果username是辅助索引,那么整个查询在辅助索引树上就可以完成,因为辅助索引树上虽然没有保存完整的行,但是保存着<username,lisi>这个key-value对;如果select username, age from xxx where username=‘lisi’,那么就要走聚集索引了

自适应Hash索引

本质上是索引,这个索引由InnoDB创建,

只适用于等值查询

通过缓冲池的B+树页构造,因此构建速度很快,并且不是对整张表都构建hash索引,它根据访问频率对热点页进行构造

全文索引

Innodb 1.2开始支持全文索引,mysql5.7.6开始支持

倒排索引

看起来很复杂,以后有时间再研究。

Cardinality

它就是一个数,

这个数代表什么呢?它代表某一个列里面数据的“多样性程度”,专业名词叫选择性。

什么意思?比如我某一个列里面的数据为 1 2 3 4 5 5 5,那么这个Cardinality的值就是5。

那如果出现null呢,比如 null null 1 2 3 3 4,这该为多少呢?这根据一个参数(innodb_stats_method)来定。参数有三个值,看名字j结合例子就知道什么意思

  • nulls_equal,那么这个Cardinality就是5
  • nulls_unequal,那么这个Cardinality就为6
  • nulls_ignored,那么这个值就为4

那这个Cardinality是怎么计算的呢?采样

这个Cardinality有什么意义呢?它用来衡量某一个列有没有必要建索引,以及就算建立是索引,查询优化器会根据这个值来判断当前列有没有必要走索引。

其实是这样的,系统在执行这条语句的时候,会进行预测:究竟是走 c 索引扫描的行数少,还是直接扫描全表扫描的行数少呢?显然,扫描行数越少当然越好了,因为扫描行数越少,意味着I/O操作的次数越少。

如果是扫描全表的话,那么扫描的次数就是这个表的总行数了,假设为 n;而如果走索引 c 的话,我们通过索引 c 找到主键之后,还得再通过主键索引来找我们整行的数据,也就是说,需要走两次索引。而且,我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行,万一这个表是全部数据都符合呢?这个时候意味着,走 c 索引不仅扫描的行数是 n,同时还得每行数据走两次索引。

Multi Range Read

最终目的是减少IO,

做了以下几方面的努力,

  • 将辅助索引键值进行缓存并排序。
  • 避免缓存被冲刷。
  • 优化范围查询

直接看例子。

select * from table where a > 100 and a < 150 and b = 1000;

# 上面这个例子会被优化成一堆等值查询
select * from table where a =101 and b = 1000;
select * from table where a =102 and b = 1000;
··· 
select * from table where a =149 and b = 1000;

这样做有什么好处,这样可以减少扇出的数据,因为a在100~150之间的数据,并不是所有的b都为1000,如果坚持使用范围查询,那么a在100~150之间的数据全部被扇出进行二次处理。

Index Condition Pushdown

这个例子其实是mysql官方的例子,但是有两个点并没有给出明确的说明

  • 第一个,这个联合索引是聚集索引吗?其实这一点也无关紧要,是聚集索引,就最终查询结果就返回数据;不是就返回主键,然后走聚集索引。
  • 第二个,举得例子都是模糊查询的,难道这是为模糊查询定制的吗?现在没法论断,但是就例子本身而言,可以说明一定的问题,我们就假设他是聚集索引,上面的例子,在innodb层找到了zipcode=95054的数据后,因为模糊查询的原因,是没办法继续过滤lastname的,索引会把匹配的zipcode=95054的所有数据全部返回到mysql的server层,让server层进行数据过滤。那么如果有索引吓退,则会在innodb层直接按lastname的过滤条件进行一次过滤,减少返回的数据。

日志

日志文件

注意这里说的日志文件针对的是Mysql的日志文件,并没有指定某种存储引擎。

主要有以下几类日志

  • 错误日志
  • 二进制日志(binlog)
  • 慢查询日志
  • 查询日志

错误日志

错误日志文件对mysQL的启动、 运行、 关闭过程进行了记录。该文件不仅记录了所有的错误信息, 也记录一些警告信息或正确的信息。 用户可以通过命令show variables like ’log error’ 来定位该文件,

在默认情况下错误文件的文件名为服务器的主机名。

慢查询日志

慢查询日志 (slow log) 可帮助DBA定位可能存在问题的SQL语句: 从而进行SQL语句层面的优化

例如, 可以在MysQL启动时设一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。 DBA每天或每过一段时间对其进行检查, 确认是否有SQL语句需要迸行优化。 该阈值可以通过参数 long_query_time来设置: 默认值为10, 代表 10秒。

在默认情况下, MySQL数据库井不启动慢查询日志, 用户需要手工将这个参数设

查询日志

查询日志记录丁所有对MysQL数据庠请求的信息, 无论这些谙求是否得到了正确 的执行。 默认文件名为: 主机名.log。

二进制日志

二迸制日志 (binary log) 记录了对MySQL数据库执行更改的所有操作(逻辑SQL), 但是不包括SELECT和SHOW这类操作, 因为这类操作对数据本身井没有修改。 然而, 若操作本身井没有导致数据库发生变化, 那么该操作可能也会写入二迸制日志。

# 实际上a = 2并不存在,所以这个语句对mysql并不会产生影响,但是仍然会记录
Update table set a = 1 where a = 2;
  • binlog的三大作用:

    • 恢复,
    • 复制,主从的原理就是基于此
    • 审计,可以用来分析mysql是否受到注入攻击
  • 默认状态下并未开启binlog

  • binlog的三种文件格式

    • STATEMENT,记录的就是逻辑的SQL语句。
    • ROW,不再是简单的SQL语句,而是表的行更改情况。因此这种格式占用空间也会更大。比如说update table set a = 1 where a = 2;在STATEMENT格式下,他就是朴素的一句SQL,但是在row格式下,他记录的是所有a = 2的表记录的变化情况。
    • MIXED,混合模式,默认采用STATEMENT,但是有的情况会使用ROW
  • 当我们提交事务的时候,

    会写二进制日志,在写二进制日志的时候,并不一定是直接写回了磁盘,实际上也是存在缓冲机制的,那么什么时候把缓冲刷回磁盘呢?

    • 通过sync_binlog参数控制
    • 1,表示直接写磁盘
    • 0,表示写到内核缓冲区,让os去写到磁盘
    • n,表示每写n次缓存,就进行一次刷回磁盘。

锁和事务

latch

  • 保证并发线程操作临界资源的正确性,没有死锁检测的机制
  • 自旋锁,自旋指定的次数后,若还没获取到锁,则进入等待状态,等待被唤醒

lock

  • 事务锁,锁定的可能是表、页或行
  • 释放点:事务commit或rollback
  • 两种标准的行级锁
    • 共享锁:S lock,事务T1获取了r行的S锁,事务T2也可以获取r行的S锁
    • 排他锁:X lock,事务T1获取了r行的S锁,事务T2就不能获取r行的X锁;事务T1获取了r行的X锁,事务T2就不能获取r行的X/S锁

锁的类型

①共享锁(S Lock):允许事务读一行数据。

②排他锁(X Lock):允许事务删除或更新一行数据。

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

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

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

①意向共享锁(IS Lock):事务想要获得一张表中某几行的共享锁。

②意向排他锁(IX Lock):事务想要获得一张表中某几行的排他锁。

用一张图表示,锁兼容性如下:

img

一致性非锁定读

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

img

对于快照数据的定义,在不同的事务隔离级别下的定义是不一样的,在读已提交隔离级别下,对快照数据的定义是锁定行的最新快照数据版本,也就是说读取的快照版本不受当前事务的id所影响。

在可重复度的事务隔离级别下,对于快照数据的定义是当前事务id对应的快照数据,

事实上,快照的生成是由undo log实现的,另外关于undo log的保存机制,什么时候删除,请看下面

  • Insert undo log,指的是在insert操作中产生的undo log,因为insert操作的记录,只对本事务可见,所以在当前事务提交后,该log可以直接删除。
  • Update undo log,指的是在delete操作和update操作产生的undo log,因为该undo log可能需要提供mvcc机制,所以不能删除。事务提交的时候会放在undo log链表中,等待purge线程来进行删除

Read View(读视图)

事务进行快照读操作的时候生产的读视图(Read View),在该事务执行的快照读的那一刻,会生成数据库系统当前的一个快照

记录并维护系统当前活跃事务的ID(没有commit,当每个事务开启时,都会被分配一个ID, 这个ID是递增的,所以越新的事务,ID值越大),是系统中当前不应该被本事务看到的其他事务id列表

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候,对该记录创建一个Read View读视图,把它比作条件用来判断当前事务能够看到哪个版本的数据,既可能是当前最新的数据,也有可能是该行记录的undo log里面的某个版本的数据。

一致性锁定读

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

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

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

自增长与锁

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

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

外键和锁

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

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

锁的算法

三种行锁算法

Record Lock,就是普通的锁,锁某一行

Gap Lock,锁定一个范围,但是不包含本身(指的是开区间)

Next-Key Lock,Gap锁和Record锁的结合体,锁定一个范围,并且包含本身(前开后闭,或者后闭合前开)

InnoDB中默认采用的都是Next-Key Lock,但是当使用等值查询且是唯一索引的时候会变成Record Lock

幻读问题(phantom)

在Mysql中,把幻读直接定义成了不可重复读。

关于这个幻读的问题,其实很多地方的定义都是不清不楚,到现在我也没有看到一个确定的定义。

在这本书里面的定义是:同一事务下,连续执行两次同样的sql语句可能导致不同的结果,第二次是sql语句可能返回之前不存在的行。

其实在InnoDB中,通过前面提到的next-key lock,就已经解决了幻读问题。为什么呢?首先分析等值查询并且是唯一索引的情况,这种情况下next-key lock直接退化成了record lock,所以再用相同的等值条件,肯定查询结果一直。如果是非唯一索引的等值查询,仍然是next-key lock,如果是非聚集索引,还会在当前等值条件的下一个区间加一个gap lock。接着我们分析范围查询,因为next-key lock的存在,所以所查询的范围实际上都是上了锁的。插入语句是没法执行成功的。非唯一列的索引一定是非聚集索引吧,所以等值查询一定是锁了前后的,前开后开。队伍非等值查询,那就所条件对应的那一片

锁问题

脏读

定义是:在一个事务中读取到别的事务未提交的数据。

其实MVCC的快照读就已经解决了这个问题。因为读快照,根本读不到别的事务没提交的。

不可重复读

前面说mysql中定义跟幻读一样,

丢失更新

这里要注意是理论中的丢失更新,还是具体场景下所谓的丢失更新

  • 理论中

8ecff1b1-1bd2-418a-8771-5d0ce870bbc8

这种丢失更新其实通过加锁就解决了,在默认的事务隔离级别下,不会存在。

  • 实际业务中

image

这种按定义来看是不存在问题的,因为一直事务都是顺利的执行,但是从业务的角度来看就有问题。

现在在User1的视角,它的更新是丢失了。其实从流程看他是一个很正常的情况,但是某些场景下他是有问题的,主要是读后写的场景。

比如转账,A是支付宝绑定的银行卡,B是微信绑定的银行卡。是同一张银行卡,有100块。

  • A先查看银行卡有多少钱(因为要判断是否够,实际的转账不是这样的),有100
  • B也要转账,也是先查看有多少钱,也发现有100。
  • A转出去50,更新账户余额50。(此时如果B想更新,是会阻塞的,因为更新加了X锁)
  • B拿到锁后,转账10块,更新账户余额90。
  • 这就出现问题了,根源在哪儿,就是读后写的问题。那么怎么解决,从一开始就不让B读。

怎么解决?看下面,读的时候直接加锁。

29942085-124f-4c56-a228-b31ee8a7ffd7

阻塞

我知道,事务拿不到锁,会阻塞,但是阻塞不可能一直阻塞,如果超过一定的时间(默认是50秒,通过innodb_lock_wait_timeout控制),它是会抛出异常的

抛出异常其实很可怕,因为在默认的情况下,innodb对于抛出异常的事务是不会进行回滚的

所以异常事务中的数据还是会更新到数据库中

可以通过innode_rollback_on_timeout,参数来控制,默认OFF,代表不回滚,设置为ON,开启回滚。

死锁

innodb有死锁检测机制

死锁的概率是极小的

发生死锁的时候,innodb会权衡让哪个事务回滚代价最小来解除死锁

事务四大特性

A,原子性:事务里面的sql语句要么全部成功,要么全部失败

C,一致性:从一种状态转变为下一种一致性状态,事务执行前后,两个状态都是正确的

I,隔离性:事务之间互不影响,某个事务提交前对其他事务都不可见。

D,持久性:事务一旦提交,结果是永久的,即时发生宕机,数据也能恢复。

后面再看看学长的博客吧,懒得写了

《Mysql技术内幕-Innodb存储引擎》读书笔记 - 成电飞书云文档 (feishu.cn)