InnoDB存储引擎笔记

第二章:架构概述

InnoDB体系架构

InnoDB存储引擎中有多个内存块。可以认为这些内存块组成一个大的内存池,负责的工作有:

  1. 维护所有线程和进程需要访问的多个内部数据结构
  2. 缓存数据,快速读取。或者对磁盘文件的数据修改前在这里做缓存
  3. 重做日志(redo log,就是记录哪些操作要重做的)

#后台线程

  1. Master Thread

    主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新等。

  2. IO Thread

    InnoDB存储引擎使用大量的AIO来处理IO请求,主要负责IO请求的回调处理。

  3. Purge Thread

    事务提交后,其所需的undolog可能不再需要,因此要用这个线程来回收已经使用并分配了的undo页

  4. page cleaner thread

    将脏页的刷新操作放入到单独的线程中完成

#内存

  1. 缓冲池

    缓冲池中缓存的数据类型有:索引页,数据页,undo页,插入缓存(insert buffer),锁信息等。

    而在1.0.x版本后,允许有多个缓冲池实例,每个页根据hash值平均分配到不同的实例中。

    image-20200422184908061

    查看命令:

    show ENGINE INNODB STATUS\G
    
  2. 内存管理
    1. LRU:InnoDB对传统的LRU做了一些优化,新读取到的页,并不会直接加入到LRU列表的首部,而是放到一个叫midpoint的位置。默认配置下,该位置位于列表长度的5/8处。
      show variables like 'innodb_old_blocks_pct'\G;
      

      为什么不能放在首部呢?因为这样做会某些页被刷出去,而这些页又是索引或者数据的扫描操作时会用到的,因此为了防止这些看上去不热门的数据过早地被刷出去,我们把位置定在中间。而为了解决这个问题,InnoDB还引入了Innodb_old_blocks_time来表示页在mid位置多久后才能被加入到LRU的前端。

      假如我的热点数据很多很多,那么我们可以首先设置Innodb_old_blocks_time变得长一些,经量保证前面的数据不会被替换,又或者通过通过下面的语句来降低热点页面被刷出去的概率:

      set gobal innodb_old_blocks_pct=20;
      

      而当数据库刚刚启动时,LRU列表是空的,这时候页都存放在Free列表中,当需要从缓存池中分页时,首先从Free列表中查找有没有可用的空闲页面,有则将该页从Free中删除,否则就淘汰LRU列表的末尾。

      有趣的是,Free Buffers + Database pages的数量之和不等于Buffer Pool Size,因为类似自适应hash索引,lock信息和insert buffer等页不需要LRU算法进行维护。

      InnoDB存储引擎现在支持压缩页功能。将16KB压缩成1KB 2KB 4KB和8KB,这些页用unzip_LRU管理,通过伙伴算法进行内存的分配。

      还有一个Flush列表是用于管理将页刷新回磁盘的。脏页即存在于LRU列表中也存在于Flush列表中。前者用于管理缓存池中页的可用性,后者专门用于将页刷回磁盘。

    2. 重做日志缓冲

      InnoDB引擎首先将重做日志信息放到这里,再按照一定频率刷新到磁盘上的重做日志上:

      1. Master Thread每秒刷一次
      2. 每个事务提交回刷一次
      3. 缓冲池小于1/2时刷一次‘

#Checkpoint技术

因为我们使用缓冲池,我们会出现脏页的情况,而每改一次就回写到磁盘上是不行的。因此我们可以采用Write Ahead Log策略,当事务提交时先重做日志,再修改页。

理论上我们可以光做日志不写磁盘,前提是日志可以无限大的同时,缓存池可以缓存所有数据。但是日志越大重做时间越久。而Checkpoint技术就是来解决一下这些问题的:

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

因此当宕机时,数据库不需要重做所有日志,只要做checkpoint之后的就可以了。而LRU算法淘汰页面时,若淘汰出的页是脏页,则会强制执行checkpoint。

checkpoint分为以下两种:

  1. sharp checkpoint:数据库关闭时将所有的脏页刷新回磁盘

  2. fuzzy checkpoint:运行时刷新一部分脏页回磁盘

    1. Master Thread Checkpoint

      每秒或每十秒的速度从缓存池的脏页列表中刷新一定的比例的脏页回磁盘,异步,不会阻塞查询

    2. Flush_LRU_LIST Checkpoint

      InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可以使用,没有则会将LRU列表尾端的页移除。如果这些页中有脏页,则需要进行Checkpoint。(现在默认是1024个页)

    3. Async/Sync Flush Checkpoint

      定义一个检查点变量:checkpoint_age = redo_lsncheckpoint_lsn,代表着目前为止写入到重做日志刷回到磁盘差距是多少,如果差太多则要把脏页刷回磁盘。这个值是为了保证重做日志的循环使用的可用性。(防止日志被塞爆了)

    4. Dirty Page too much

      脏页数量太多(75%),强制进行Checkpoint行为

#Master Thread的工作方式

由多个loop组成:

  1. 主循环

    1s或者10s跑一次,日志刷新到磁盘上,刷新100个脏页到磁盘(可能),如果当前没有用户活动则切换到background loop。注意即使某个事务没有提交,InnoDB存储引擎仍然会每秒将重做日志缓冲中的内容刷新到重做日志文件。然后10s的那次一定会发生合并插入缓冲操作以及删除undo页。

  2. 后台循环

    删除无用的undo页,合并20个插入缓存,跳回主循环

  3. 刷新循环

    刷新100个页面供使用

  4. 暂停循环

    如果flush loop也没事情干了,就会直接挂起,等待活动

InnoDB 1.2.x版本后刷新脏页的操作交给了Page Cleaner Thread

InnoDB关键特性

#插入缓存

  1. insert buffer

    我们平时建表的时候都是用自增长的主键,而在聚集索引的情况下,插入新的条目是递增按照顺序的,则不需要随机读取另一个页中的记录。(但如果是UUID啥的还是会随机访问)但是我们的表有时候要对其他字段建索引,这样在进行插入操作时,数据页的存放还是按着主键a进行顺序存放的。但是对于非聚集索引叶子节点的插入不再是顺序的了。这时就要离散地访问非聚集索引页,其实就是主键id是1,2,3,4…这样插,但是非主键会1,2,99,100这样插,会分到不同的页上。因此InnoDB设计了Insert Buffer,对于非聚集索引的插入或者更新操作,不是每次直接插到索引页中,而是先去缓冲池找,有就在缓冲池直接插,不再则先放到Buffer对象中,欺骗这个非聚集的索引已经插入到叶子节点中,最后再以一定的频率和辅助索引页进行合并操作。

    使用这个insert buffer的条件是:

    1. 索引是辅助索引
    2. 索引不是唯一的(唯一就要把所有索引找到以确定唯一性,没意义)
  2. Change Buffer

    前者的升级版,可以对INSERT、DELETE、UPDATE操作都进行缓存。现在对一条记录的update操作可能分为两个过程:将记录标记为已删除,真正将记录删除。前者在Delete Buffer后者在Purge Buffer。

    假如要修改页号为40的索引页,而这个页正好在缓冲池内。

    img

    加入写缓冲优化后,流程优化为:

    (1)在写缓冲中记录这个操作,一次内存操作;

    (2)写入redo log,一次磁盘顺序写操作;

    其性能与这个索引页在缓冲池中相近。可以看到,40这一页,并没有加载到缓冲池中。

    (1)数据库异常奔溃,能够从redo log中恢复数据;

    (2)写缓冲不只是一个内存结构,它也会被定期刷盘到写缓冲系统表空间;

    (3)数据读取时,有另外的流程,将数据合并到缓冲池;

    不妨设稍后的一个时间,有请求查询索引页40的数据。

    img

    此时的流程如序号1-3:

    (1)载入索引页,缓冲池未命中,这次磁盘IO不可避免;

    (2)从写缓冲读取相关信息;

    (3)恢复索引页,放到缓冲池LRU里;

    可以看到40这一页在真正被读取时才会被加载到缓冲池中并进行合并操作。

  3. 内部实现

    image-20200422220929953

    当一个辅助索引要插入到页的时候,如果页不在缓冲池中,那么会构造一个search key,接下来查询 Insert Buffer这棵B+树,将这条记录插入到树的节点上。

    image-20200422221534051

  4. Merge Insert Buffer

    当以下几种情况时,会发生merge操作:

    1. 辅助索引页被读取到缓冲池中
    2. Insert Buffer Bitmap追踪到该辅助索引页已经无可用空间时(一个bitmap追踪16384个辅助索引页)
    3. Master Thread周期性写入

#两次写

带给InnoDB存储引擎的是数据页的可靠性。当发生部分写失效时(无法将脏页完整地回写到磁盘上,此时内存中的脏页丢了,磁盘上的数据也坏了),我们要需要通过一个页的副本来还原该页。(貌似和redo log没什么关系)

总体的思路是:

A. 在覆盖磁盘上的数据前,先将Page的内容写入到磁盘上的其他地方(InnoDB存储引擎中的doublewrite buffer,这里的buffer不是内存空间,是持久存储上的空间).

B. 然后再将Page的内容覆盖到磁盘上原来的数据。

如果在A步骤时系统故障,原来的数据没有被覆盖,还是完整的。
如果在B步骤时系统故障,原来的数据不完整了,但是新数据已经被完整的写入了doublewrite buffer. 因此系统恢复时就可以用doublewrite buffer中的新Page来覆盖这个不完整的page。

image-20200422223302076

脏页先复制到buffer中,然后把内容写到doublewrite中(顺序写),写完再把buffer内的数据同步到磁盘上(离散写)。

img

#自适应hash索引

InnoDB存储引擎会对表上各索引页的查询,若观察到建立hash索引可以提升速度,则建立hash索引。AHI通过缓存池的B+树页构造而来,建立速度很快。

img

img

#异步IO

我们不必发一个IO等一个IO,可以同时发多个,比如请求多个索引页时可以一次性发多个请求,然后还可以合并请求,比如请求(8,6)(8,7)(8,8)时,只会发送一个IO,请求(8,6)

#刷新临近页

刷新一个脏页时还会检测该页所在区的页,如果是脏会一起刷,可以将多个IO变成一个IO,但也会把不怎么脏的页进行了写入,不一会又变脏了

#启动、恢复和关闭

当我们直接kill -9 mysql时,参数innodb_fast_shutdown会影响存储引擎的行为:

image-20200422225210277

第三章:文件

参数文件

#参数类型

  1. 动态参数:可以在MySQL实例运行中更改。而更改可以基于整个实例或者是基于会话(session),比如autocommit就是基于会话的参数。但不管怎么样动态修改后再次重启,动态参数会恢复原样。
  2. 静态参数:整个实例生命周期内都不能更改

日志文件

#错误日志

show variables like 'log_error'\G

#慢查询日志

配置:

log-output=FILE
general-log=1
general_log_file='general_log.log'
slow-query-log=1
slow_query_log_file='slow.log'
long_query_time=10

然后我们可以开启一个参数把没有用到索引的查询也记录到慢查询日志中:

log_queries_not_using_indexes=1

最后我们通过mysqldumpslow来分析mysql语句

mysqldumpslow -s al -n 10 slow.log

如果我们将slow.log的输出形式改成table的话。我们可以在mysql库中找到slow_log表,这时慢查询会被记录到slow_log表中。

然后还可以通过设置逻辑读取和物理读取来加强捕获:

  1. 逻辑读取:所有的读取,包括磁盘和缓存池
  2. 物理读取:磁盘读取

可以设置slow_query_type来选择是根据运行时间还是逻辑IO的次数为标准来将查询记录进slow log

#二进制日志

用处:

  1. 恢复
  2. 复制(主从复制)
  3. 审计(有没有对数据库进行注入攻击)

开启二进制日志会使性能下降1%,参数max_binlog_size指定单个二进制日志文件的最大值,超过该值会产生新的二进制文件,后缀名+1,默认是1GB。还有一个参数是binlog_cache_size,默认大小是32K,记录所有未提交的二进制日志,提交后会将缓存中的日志写入二进制日志文件中(注意这里的写依旧可能出现缓冲写的情况,未必真的就同步到磁盘上,可能仅仅存在buffer上,我们可以设置sync_binlog=1控制,默认,sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失)。

注意参数是基于会话的,一个线程开启事务会分配一个binlog_cache_size的缓存。

一个事务,在没有提交(uncommitted)的时候,产生的日志,记录到Cache中;等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘。

然后假如我们要搭建master=>slave=>slave的架构的话,要开启log-slave-update,因为slave节点是不会将从master获得的二进制日志写入自己的二进制日志文件中去的。

还有一个参数是binlog_format,有下面几种格式:

  1. Statement:记录逻辑SQL语句
  2. ROW:记录表的行更改情况(占的空间会更大)
  3. MIXED:混着来

#表结构定义文件

MySQL会有一个以frm为后缀名的文件,这个文件记录了该表的表结构定义,当然建立了视图也会产生对应的frm文件

#表空间文件

若设置了 innodb_file_per_table=ON,则每个Innodb表都会单独开一个idb文件存放数据,否则会放到默认的innodb_data_file_path下的ibdata1文件内,共同管理。

#重做日志文件

为了保证可靠性,每个innodb引擎至少有一个重做日志文件组,下面至少有2个重做日志文件,如ib_logfile0/1这样的文件。为啥会有这么多呢,因为当日志1写满时引擎会切换去写文件2,2也满了就切回1。

现在我们来看看redo log的工作原理。说白了,redo log就是存储了数据被修改后的值。当我们提交一个事务时,InnoDB会先去把要修改的数据写入日志,然后再去修改缓冲池里面的真正数据页。

我们着重看看redo log是怎么一步步写入磁盘的。redo log本身也由两部分所构成即重做日志缓冲(redo log buffer)和重做日志文件(redo log file)。这样的设计同样也是为了调和内存与磁盘的速度差异。InnoDB写入磁盘的策略可以通过innodb_flush_log_at_trx_commit这个参数来控制。

img

img

当该值为1时,当然是最安全的,但是数据库性能会受一定影响。

为0时性能较好,但是会丢失掉master thread还没刷新进磁盘部分的数据。

DB宕机后重启,InnoDB会首先去查看数据页中的LSN的数值。这个值代表数据页被刷新回磁盘的LSN的大小。然后再去查看redo log的LSN的大小。如果数据页中的LSN值大说明数据页领先于redo log刷新回磁盘,不需要进行恢复。反之需要从redo log中恢复数据。

那这个redo log和二进制log有什么不一样?
首先二进制日志会记录所有与MySQL相关的日志记录,而redo log只会记录有关InnoDB存储引擎有关的事务日志。其次是二进制日志记录到是一个事务的具体操作,而redo log记录的是关于每个page的更改的物理情况。最后是写入的时间点不同,二进制log仅仅在事务提交前提交,只写磁盘一次(之前提到的binlog_cache_size仅仅起到缓存的作用,并没有每时每刻都写入,commit的时候才写入磁盘),而在事务进行过程中,redo log是不断被写入的(修改数据前先修改redo log)注意redo log的写入是先写入一个日志缓冲中再写入磁盘:

image-20200423170127495

流程大概是:写日志(内存) — 改数据(内存)— commit提交 — 二进制日志(直接刷进磁盘) — redo log(刷入磁盘)— 脏页开始回写 — 两次写进程开始 — (宕机或部分写的情况出现时,用两次写过程出现的备份来复原磁盘上损坏的文件,再根据redo来恢复commit了但是没有刷到磁盘上的操作,然后是根据undo日志来回滚没有commit但是脏页已经被刷进磁盘的操作)— commit完成

第四章:表

InnoDB逻辑存储结构

image-20200423174726530

#表空间

如果启用了innodb_file_per_table的参数,每张表的表空间内存存放的只是数据,索引和插入缓冲Bitmap页。其他的诸如:回滚(undo)信息,插入缓冲索引页,系统事务信息,二次写缓冲(Double write buffer,脏页回写相关)等还是存放在原来的共享表空间(ibdata1)内。

在DB恢复时,对于已经COMMIT但更新仍停留在缓冲区的事务要执行REDO(重做)操作,即根据日志内容(我们先修改日志改磁盘上的信息,然后在修改磁盘时还要再进行二次写操作,保证磁盘写的完整性)把该事务对DB修改重做一遍。而对于还未结束的事务要执行UNDO(撤销)操作,即据日志内容把该事务对DB已作的修改撤销掉

问题是为啥没有commit也会被修改呢?因为我们在事务的过程中确实修改了内存中的数据页,这些脏页会被回写回磁盘中,因此磁盘中确实会有脏页,而假如没有commit,则说明事务是失败的,我们要通过undo(rollback)来恢复之前的状态。

有了上面的背景知识后,我们可以有这样一个想法:如果我们尝试rollback,是不是可以缩小ibdata1文件的大小呢?

答案是不行:

InnoDB引擎不会在执行rollback时去收缩这个表的空间,但是会自动判断这些undo信息是不是需要的,不需要可以将这些空间标记为可用空间(full purge操作,Purge Thread来回收已经使用并分配的undo页面)。

#段

从图4-1可以看出,表空间是由段组成的,数据段就是B+树的叶子节点,索引段就是B+树的非索引节点,还有回滚段啥的。

#区

区(Extend)的大小总是1MB,一个区可以有64个连续的页,当然页大小的改变会引起区内页的页数。创建表默认大小是96KB,为啥会小于1MB呢,因为每个段开始前,会先用32个页大小的碎片页来存放数据,使用完后才是64个页的连续页的申请。

#页和行

页有数据页,undo页,事务数据页等,而行的话每个页最多允许存放7992行数据。

InnoDB行记录格式

#Compact行记录格式

image-20200423222728063

这里的变长字段长度列表的描写很迷惑,它的意思是第一个字段是一个列表,然后列表中的每个小块最大不能超过2子节,16位。为什么?因为最长的也不能超过65535字节,而 1111 1111 1111 1111的大小正好是65535。说白了第一个字段就是说明每列的数据究竟多大,用bit位的方式来表达这列占多少Byte。

然后假如列中有NULL,则仅仅会占据NULL标记位而不占用任何空间。还用两个隐藏列:事务ID列和回滚指针列,分别占6,7字节。若没有主键,InnoDB还会增加一个6字节的rowid列。

image-20200423223920374

括号加起来的是长度指示列表,然后是null位,蓝色是行指针(地址)而前面的2c代表了下一个记录的偏移量,因此页内部是按照一个链表的结构来串行记录的。而对于固定长度的位,会用0x20来填充。而对于列中存在NULL的,NULL字段会在对应的位设1,其余设为0。

特别注意:deleted_flag放在记录头信息里面,1个bit

#Redundant记录格式

image-20200423225410771

注意:CHAT类型的NULL值会占空间

#行溢出数据

MySQL手册中对varchar定义的65535长度指所有的VARCHAR列的总和,如果加起来超过,会创建失败。而且注意的是这里的长度是字符长度,像UTF-8一个字符占3个字节的就不能开这么大。

这里会有疑惑:一个页最大就16KB,16384字节,为啥能存放65532字节呢?当发生行溢出时,数据会存放到类型为Uncompress BLOB页中。

image-20200423231443499

当然数据放在数据页还是放在BLOB页主要看一个页能不能保证放两条记录(不能则会退化成链表)

#Compressed和Dynamic行记录格式

相比起之前的格式,现在多于溢出的数据数据页仅仅存20个字节的指针,实际的数据放在OFF Page中。而且大数据会被压缩。

#Char的行结构存储

之前CHAR类型的大小是不会被变长字段长度的列表记录的(不够用空补上),但是新版本后CHAR(N)的N变为字符长度而不是字节长度,因此CHAR的长度也会变来变去的。因此现在引擎在内部视其为变长字符类型,在多字符集的情况下,CHAR和VARCHAR的实际行存储是没什么区别的。

InnoDB的数据页结构

image-20200423234223490

image-20200423234338999

image-20200423235409167

找三次:

  1. 叶节点
  2. 通过Page Directory找槽位
  3. 在槽位内通过记录头的next_record来找相关记录

分区表

分区的过程是将一个表或索引分解为多个更小,可管理的部分。MySQL数据库支持的分区类型是水平分区,不支持垂直分区。此外MySQL支持的是局部分区索引,一个分区中既有数据也有索引。

#分区类型

  1. Range分区:根据分区列进行分区,数据根据分区列的值存放在不同的物理文件中。

    image-20200424093610798

    注意若插入一个没有在分区中定义的值时,MySQL会抛出一个异常。

    这样分区的应用有,比如我们要按年份把数据存储起来,可以建立一下规则:

    create table sales(
    money int,
    date DATETIME
    )ENGINE=INNODB
    PARTITION by RANGE(YEAR(date)){
    PARTITION p2008 VALUE LESS THEN (2009),
    PARTITION p2009 VALUE LESS THEN (2010),
    }
    

    这样删除时可以一次性删除一个分区,而查询也不用遍历所有表,但是当出现跨区查询时效率依旧会降下来,跨了一天也是跨。注意优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数优化,因此分月的处理会比较麻烦。要:

    PARTITION by RANGE(TO_DAYS(date)){
    ...
    }
    
  2. List分区:分区列的值是离散的

    image-20200424100433464

  3. hash分区:

    image-20200424101030462

  4. COLUMNS分区:可以直接使用非整型的数据进行分区,不需要转化为整型

    image-20200424101323320

#子分区

image-20200424101548091

#分区对NULL的处理

  1. RANGE类型:NULL会被插到范围最左(小)的那个分区
  2. LIST类型:要对某个列加入NULL,比如PATITION p0 VALUES IN (1,2,3,4,5,NULL)
  3. HASH和KEY类型:HASH(NULL) = 0

#分区和性能

数据库应用分为:OLTP(在线事务处理)如电子商务,网络游戏和OLAP(在线分析处理)如数据仓库,数据管理等。对于OLAP而言,分区确实可以很好地提升性能,而对于OLTP而言,有可能会对多个分区取几条数据,如果走的是主键查询,性能会提高,因为B+树的高度减少了,少了一次IO,但如果走的是其他KEY,这时磁盘就要IO所有分区,每个分区开销为2次IO,这就慢很多了,如果不分区3次IO就行了。

第五章:索引

什么是Cardinality,怎么计算

并不是所有列都需要添加索引,高重复度的列建索引是没有意义的。而Cardinality则是衡量重复性的一个指标。而统计这个值,是通过采样的方法来完成的。在InnoDB中,更新Cardinality的策略为:

  1. 表中1/16的数据已经发生了变化
  2. stat_modified_counter > 2000000000(当出现对几行数据修改了很多次时,cardinality也能更新)

采样过程:

  1. 获得B+树索引中叶子节点的数量,记为A
  2. 随机拿8个叶子,统计每个页不同的个数,P1…P8
  3. Cardinality = (P1 + … + P8)/ 8 * A

当页足够多的时候,每次采样到的Cardinality值是不同的,而同理,当页比较小时,抽样都抽到一样的,Cardinality的计算结果是一样的。

联合索引

我们建立联合索引(a,b,c)

select * from table where a = xxx order by b    
select * from table where a = xxx and b = xxx order by c
select * from table where a = xxx order by c

第一,第二个走联合索引,数据直接排序好

第三个走完联合索引后,还要再进行一次文件排序

覆盖索引

说白了我们可以直接从辅助索引中获得想要的数据,使用覆盖索引的好处是我们可能只用取一页就能找到我们要的数据,而走主键由于叶节点存着一列的所有数据,因此可能要走很多页很多次IO才行。

不走索引的情况

select * from oreder_details where order_id > 1000 and order_id < 5000;

其中order_id和product_id两个组成联合主键,同时对order_id建立索引。运行后并不会走order_id,而是走主键,因为走了辅助索引虽然可以顺序读出相应的主键,但是在用主键拿真正的数据时是离散读,还不如直接走主键(需求的数据大于一部分),因为走主键一定是顺序读

Multi-Range Read优化

目的是减少磁盘的随机访问,并将随机访问转换为比较顺序的数据访问。适用于range,ref类型的查询/

好处:

  1. 在查询辅助索引时,首先根据得到的查询结果,按照主键进行排序,并按照主键排序的顺序进行查找
  2. 减少缓冲池中页的被替换次数
  3. 批量处理对键值的查询

什么意思呢?比如辅助索引中查到(1,3)(2,1)(3,5)这样我们会拿3,1,5这三个主键ID去找数据,但是这样就变成了离散读,不是顺序读,因为我们可以先对id排序,再读。

除此之外对于联合索引对应的联合条件,MRR会将条件映射成KEY-VALUE对,根据这些拆出来的条件进行查询。

where  (a between 100 and 200) and (b between 1 and 3)
->
(100,1)(100,2)...

Index Condition PushDown(ICP)优化

将where的过滤放到index那里做,设有联合索引(id,name)

select * from people where id = '555' and name like '%lius%'

正常来说会先通过索引找到所有id=555的主键,然后再用这些主键找数据,最后通过这些数据筛选name,ICP优化后就可以直接在联合索引那里做过滤了(过滤主键)。

全文索引

倒排索引

它在辅助表中存储了单词和单词自身在一个或多个文档中所在位置之间的映射。

image-20200424225234074

image-20200424225246342

image-20200424225315510

img

第六章

lock与latch

latch一般称为轻量级锁,latch可分为mutex(互斥锁)和rwlock(读写锁),目的是用来保证并发线程操作临界资源的正确性,没有死锁检测

lock的对象是事务,用于锁定的是数据库的对象(表,页,行),一般lock的对象仅在事务commit或rollback后进行释放。

image-20200425100508392

InnoDB存储引擎中的锁

锁的类型

  1. 在mysql中有表锁:

    LOCK TABLE my_tabl_name READ; 用读锁锁表,会阻塞其他事务修改表数据。

    LOCK TABLE my_table_name WRITe; 用写锁锁表,会阻塞其他事务读和写。

  2. Innodb引擎又支持行锁(注意只有当通过索引检索数据时才采用行锁),行锁分为:

    共享锁,一个事务对一行的共享只读锁。

    排它锁,一个事务对一行的排他读写锁。

又有表锁又有行锁,会出现下面这种情况:

事务A锁住了表中的一行,让这一行只能读,不能写。之后,事务B申请整个表的写锁。如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。

数据库要怎么判断这个冲突呢?

  1. 判断表是否已被其他事务用表锁锁表
  2. 判断表中的每一行是否已被行锁锁住。

注意第二种方法效率实在不高,因为需要遍历整个表。于是就有了意向锁。

在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。在意向锁存在的情况下,上面的判断可以改成如果发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。

一致性非锁定读

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

img

该实现是通过undo段来完成,因此快照数据本身是没有额外开销的。然后因为一个行记录可能不止一个快照数据,因此提出MVCC来控制并发多版本的问题。

而MVCC在不同隔离级别下的表现是不同的,比如已提交读和可重复读,它们生成ReadView的策略不同。

Read View就是事务进行快照读操作的时候生产的读视图(Read View),在该事务执行的快照读的那一刻,会生成数据库系统当前的一个快照,记录并维护系统当前活跃事务的ID(当每个事务开启时,都会被分配一个ID, 这个ID是递增的,所以最新的事务,ID值越大)。ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务,也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。其中最主要的与可见性相关的属性如下:

设计InnoDB的大叔提出了一个ReadView的概念,这个ReadView中主要包含当前系统中还有哪些活跃的读写事务,把它们的事务id放到一个列表中,我们把这个列表命名为为m_ids。这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本是否可见:

  1. 如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id,表明生成该版本的事务在生成ReadView前已经提交,所以该版本可以被当前事务访问。
  2. 如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id,表明生成该版本的事务(更新是在这次选择这个动作之后发生的,自然不可能被访问,至于DATA_TRX_ID大于low_trx_id本身出现也只有当多个SQL并发的时候,在一个SQL构造完ReadView之后,另外一个SQL修改了数据后又进行了提交,对于这种情况,数据其实是不可见的。)在生成ReadView后才生成(select 后才生成ReadView),所以该版本不可以被当前事务访问。
  3. 如果被访问版本(链表中的块)的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间,那就需要判断一下trx_id属性值是不是在m_ids列表中,如果在,说明创建ReadView时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView时生成该版本的事务已经被提交,该版本可以被访问。
  4. 如果某个版本的数据对当前事务不可见的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可见性,依此类推,直到版本链中的最后一个版本,如果最后一个版本也不可见的话,那么就意味着该条记录对该事务不可见,查询结果就不包含该记录。

用一张图更好的理解一下:

up_limit_id:当前已经提交的事务号 + 1,事务号 < up_limit_id ,对于当前Read View都是可见的。理解起来就是创建Read View视图的时候,之前已经提交的事务对于该事务肯定是可见的。

low_limit_id:当前最大的事务号 + 1,事务号 >= low_limit_id,对于当前Read View都是不可见的。理解起来就是在创建Read View视图之后创建的事务对于该事务肯定是不可见的。

trx_ids:为活跃事务id列表,即Read View初始化时当前未提交的事务列表。所以当进行RR读的时候,trx_ids中的事务对于本事务是不可见的(除了自身事务,自身事务对于表的修改对于自己当然是可见的)。理解起来就是创建RV时,将当前活跃事务ID记录下来,后续即使他们提交对于本事务也是不可见的。

img

最后我们来举个例子让我们更好理解上面的内容。

比如我们有如下表:

img

现在有一个事务id是60的执行如下语句并提交:

update user set name = '强哥1' where id = 1;

此时undo log存在版本链如下:

img

提交事务id是60的记录后,接着有一个事务id为100的事务,修改name=强哥2,但是事务还没提交。则此时的版本链是:

img

此时另一个事务发起select语句查询id=1的记录,因为trx_ids当前只有事务id为100的,所以该条记录不可见,继续查询下一条,发现trx_id=60的事务号小于up_limit_id,则可见,直接返回结果强哥1。

那这时候我们把事务id为100的事务提交了,并且新建了一个事务id为110也修改id为1的记录name=强哥3,并且不提交事务。这时候版本链就是:

img

这时候之前那个select事务又执行了一次查询,要查询id为1的记录。

如果你是已提交读隔离级别READ_COMMITED,这时候你会重新一个ReadView,那你的活动事务列表中的值就变了,变成了[110]。按照上的说法,你去版本链通过trx_id对比查找到合适的结果就是强哥2。

如果你是可重复读隔离级别REPEATABLE_READ,这时候你的ReadView还是第一次select时候生成的ReadView,也就是列表的值还是[100]。所以select的结果是强哥1。所以第二次select结果和第一次一样,所以叫可重复读!

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView,之后的读都复用之前的ReadView。

一致性锁定读

select ... for update
select ... in share mode

强制上(行)锁,保证数据逻辑的一致性。必须注意上排他锁后依然可以读,但不能上读锁或写锁(增删改操作)

外键和锁

对于外键值的插入和更新,首先需要查询父表中的记录,但是对于父表的select操作,不是使用一致性非锁定读的方法,因为这样会发生数据的不一致性的问题。因此这里采用的是一致性锁定读:

select ... in share mode

这意味着当在子表插入一个和外键(父表)相关的记录时,假如父表有事务没有提交,那么子表会卡在原地。背后的机制是:父表加了一个X锁,而子表的插入会向父表加一个S锁,两者冲突了。这样做的好处是假如我在插子表的时候父表数据被删了,那子表的外键就不会指向一个不存在的值。

死锁

除了事务A拿着1,2,3行请求事务B的4,5,6行,而B反过来请求1,2,3行这种死锁外,还有一种死锁是两个事务分别持有排他锁和共享锁。当然不是同一行:

image-20200426103343971

第七章

事务的分类

  1. 扁平事务,一回滚全滚
  2. 带保存点的扁平事务,回滚时事务回到保存点,注意rollback后保存点的计数不会跟着回退,而是线性增加
  3. 链事务
  4. 嵌套事务,就是在事务中开事务,只有叶子节点才能访问数据库,高层只负责控制逻辑。(其实可以用保存点来模拟,区别在于使用保存点的时候用户无法选择哪些锁可以被子事务继承)而想要并行地进行事务,必须用嵌套循环。
  5. 分布式事务

事务的实现

redo log:保证事务的原子性和持久性

undo log:保证事务的一致性

#redo log

redo log由两部分组成,一个是易丢失的redo log buffer,另外一个是已经刷进磁盘的redo log file,当事务的commit操作提交时,首先要把 所有重做日志刷进磁盘。而为了确保每次日志都写入重做日志文件,在每次将重做日志缓冲写入重做日志文件后,InnoDB存储引擎都需要调用一次fsync操作,这个可以通过设置innodb_flush_log_at_trx_commit参数去调节具体的调用情况(详情在第三章的重做日志文件)。当每commit一次就要立刻刷磁盘,那么插入大量数据的时间会变长。

再来回顾一下redo log和bin log的区别,两种日志写入磁盘的时间点是不同的,二进制只在事务提交的时候一口气写入,而redo log在事务进行的过程中不断写入,不随着事务提交的顺序进行写入。

在InnoDB存储引擎中,重做日志都是以512字节进行存储的,这意味着重做日志缓存,文件都是以块的方式进行保存的。每个块都是512字节大小。因此重做日志的写入可以保证原子性,不会说一个日志记录块只能刷一半进磁盘的情况。

image-20200427103712700

HDR_NO:标记这个块在buffer中的位置

DATA_LEN:log block的大小,写满时是0x200,512字节

FIRST_REC_GROUP:块中第一个日志的偏移量,如果该值与上面的LEN相同,则表示该块只记录了一个日志。

image-20200427104922177

前面的章节提到过,redo log file(磁盘固化的redo log)是一个组,新的块写入文件时未必是顺序的,还要去第一个redo log file的头部更新信息,比如检查点什么的。

这里再引入一个概念叫LSN,表示事务写入重做日志的字节的总量,例如当前重做日志的LSN为1000,有一个事务T1写入了100字节的重做日志,那么LSN就变成了1100,每个页的头部都有一个值FIL_PAGE_LSN,记录了该页的LSN。在页中,LSN表示该页最后刷新时LSN的大小。那么LSN的作用是什么呢?其实就是判断某个页到底要不要进行恢复操作。如果一个页的LSN小于重做日志中的LSN,那么该事务就已经提交(重做日志已经记录在磁盘上了),且数据库要对它进行恢复操作。

这个恢复的过程是这样的:

首先checkpoint(就是redo log file group中第一个文件的头部中的checkpoint)表示已经刷新到磁盘页上的LSN(就脏页上的一个标识符,比如脏页1的LSN是100,那么脏页2的LSN是200,那么脏页2刷进磁盘后checkpoint是200,此时还有脏页3,4等等脏页没刷进磁盘,但是相关的日志已经刷了),因此在恢复过程中仅需恢复checkpoint开始的日志部分。比如假如数据的checkpoint是1000且在这时发生宕机,那么恢复时会去恢复1000以后的日志。

redo日志记录的是物理操作而不像二进制日志是逻辑操作,物理操作就是直接对页进行操作:

page(2,3),offset 32,value 1,2

因此它是幂等的。而二进制日志就不是幂等的。

#undo log

存储方式是:rollback segement — undo log segement — undo页

不能简单地理解为undo log只是将数据库逻辑恢复成原来的样子,因为假如有很多个事务操作同一个页的不同行,一个事务回滚不能直接将整个页都回滚了,这样会影响其他事务。

而MVCC就是通过undo来实现的,假如用户读取的一行记录被其他事务占领了,那么当前的事务可以通过undo读取之前的行版本信息,以此实现非锁定读。

当事务提交后理论上undo log和undo log所在的页就没有用了,但是还不能马上删除,因为还有可能有其他事务需要通过undo log来得到行记录之前的版本,故提交后将undo log放入一个链表中,是否可以真正删除undo log由purge线程来判断。

然后为了保证undo页可以被复用(一个事务给一个undo log 页开销太大),一个undo页的使用空间若小于3/4,则可以被重用,即之后的新的undo log可以被记录在当前undo log的后面。

undo log有两种格式:

  1. insert undo log:insert操作中产生的undo log,因为insert操作的记录,只对事务本身可见,对其他事务不可见,因此事务提交后可以直接删除,因为这个操作只有自己可见。
  2. update undo log:由于在原有的基础上对记录进行修改,因此MVCC的机制要求undo log不能立刻被删除。提交时放到undo log链表,等待purge线程清除。

不管是哪种undo log,事务未提交时undo log页都会放入一个叫xxx_undp_list的地方,而commit后最终到达一个叫xxx_undo_cache的地方,供下次重用。

值得一提的是,update操作的undo log对于主键更新和非主键更新表现的是很不同的,update的主键更新分两步完成,首先将原来的主键标记为已删除(行记录的信息头的delete_flag置1,最终的删除操作在purge中进行),产生一条del的undo log,然后插入一条新的记录,产生一个insert log。

这里就扯到了purge线程,无论是update还是delete,在事务没提交前都是将主键列等于xx的记录的delete_flag置为1,记录并没有被删除,数据依旧在B+树中。而辅助索引更是没有任何的变动,要删的那条信息的主键依旧在辅助索引树的叶子节点上。真正的删除放在purge线程上,若该行记录不被任何其他事务引用,那么就可以进行delete操作。

一个很自然的想法是:要清理undo页,先从最早提交好的事务去清理,因此我们提出了一个叫history list的概念,所有提交的事务按照顺序在list上排列,然后purge线程首先在list上的第一个事务找它对应的undo log(在undo log page中),找到后清理,然后清理该页上其他没有被其他事务引用且事务已经提交了的undo log日志记录,找完了再去list找第二个没有被清理undo log的事务。这样做的好处是避免了大量随机读取的操作,提升purge的效率。

#redo log和binlog一致性的保持

MySQL中Redo与Binlog顺序一致性问题?

Mysql为了保证master和slave的数据一致性,必须保证binlog和redo日志的一致性,因为备库通过二进制日志重放主库提交的事务,而主库binlog写入在commit之前,如果写完binlog主库crash,再次启动时会回滚事务。但此时从库已经执行,则会造成主备数据不一致,而通过两阶段提交可以做到这个保证,如果数据库系统发生崩溃,当数据库系统重新启动时会进行崩溃恢复操作,存储引擎中处于prepare状态的事务会去查询该事务是否也同时存在于binlog中,如果存在就在存储引擎内部提交该事务因为此时从库可能已经获取了对应的binlog内容),如果binlog中没有该事务,就回滚该事务。

例如:当崩溃发生在第一步和第二步之间时,明显处于prepare状态的事务还没来得及写入到binlog中,所以该事务会在存储引擎内部进行回滚,这样该事务在存储引擎(redo log)和binlog(包括从库)中都不会存在;当崩溃发生在第二步和第三步之间时,处于prepare状态的事务存在于binlog中,那么该事务会在存储引擎内部进行提交,这样该事务就同时存在于存储引擎(redo log)和binlog(包括从库)中。

为了保证数据的安全性,上面的3个步骤都需要调用fsync将数据持久化到磁盘。由于在引擎内部prepare好的事务可以通过binlog恢复,所以通常情况下第三个fsync是可以省略的。

另外,MySQL内部两阶段提交需要开启innodb_support_xa=true,默认开启。这个参数就是支持分布式事务两段式事务提交redo和binlog数据一致性就是靠这个两段式提交来完成的,如果关闭会造成事务数据的丢失

但是上面这个方案只能够保证redo log和binary log一致,但是在并发的情况下还要保证事务日志和Mysql binlog的提交顺序一致。

不但因为提交事务的顺序会影响结果,而且在使用热备工具(利用redo log恢复数据)时会丢失事务数据。(提一句这个热备工具就是你想把主库的内容复制到从库时用到的工具)

image-20200427154924691

在A机子上同时提交三个事务,然后他们都搞定了prepare阶段和write binary log阶段,然后刷完磁盘后三个事务开始commit确认,然而假如这时T2,T3commit了,然后备份工具介入,读取redo log,这时工具会认为T1是没有commit成功的,因此会在slave机器上回滚(把工具收集到的redo log放上去slave恢复后),这样T1事务中的操作会回滚。但注意到在从机上它自己认为的master的binlog的位置是在T3这个位置的,意味着开启同步后T1事务commit这件事永远无法被slave得知,而T1操作确实写在了A机子上的提交了,这样两者的信息就会出现偏差。

然而假如commit的顺序和binlog顺序一致的话:

image-20200427162324159

此时只要我在A机子上认可的行为(成功写入binlog日志,且被从机获取到),在备份时一定会被记录到新的机子上。这时备份工具介入后虽然会认为T3是失败并回滚,但是因为binlog的位置依旧在T1那里,因此开启同步(start slave)后成功提交的T2,T3都会被陆续复制到新的机子上。

事务的隔离级别

这里只提一点:在read commited级别的隔离下,binlog如果是statement格式的话会出问题,statemnet格式记录的是master上产生的sql语句(而row则是行的变化)。带来的影响是:

在事务A中,我们先删id小于5但不提交

在事务B中,我们插入id=3的记录,提交

然后A再提交

此时在master上id=3的记录是能够被插入的,但是在slave上是啥也没有的。因为:

  1. 事务没有使用gap lock锁定,因此会话B能够插入id=3这条记录
  2. statement记录的是sql语句,在master上执行的是先删后增,在statement上执行的是先增(因为插入语句先提交)后删除

分布式事务

分布式事务指允许多个独立的事务资源参与到一个全局的事务中。XA事务由一个或多个资源管理器,一个事务管理器(需要和参与全局事务的所有资源管理器通信)以及一个应用程序组成。

这里还是用到一个刚刚提到的思想:两段式提交

  1. PREPARE:全局的节点告诉事务管理器他们准备好提交了
  2. COMMIT/ROLLBACK:事务管理器告诉资源管理器执行rollback或者commit,如果任意一个节点表示无法提交,那么所有节点回滚。

上面的被称为外部XA,而内部XA就是存储引擎与插件之间的事务处理,比如binlog和InnoDB引擎之间。

事务上的优化

  1. 循环提交:当我们打开默认事务时,循环插入1k条就会创建1000次redo log,并且中间出错了情况也很尴尬,因此我们可以把这几千次的插入写到一个事务中:
    CREATE PROCEDURE store1(count INT)
    BEGIN
    DECLARE s INT DEFAULT 1;
    DECLARE c CHAR(80) DEFAULT('a',80)
    START TRANSACTION;
    WHILE s <= count DO
    INSERT INTO t1 VALUES(...)
    SET s=s+1;
    END WHILE;
    COMMIT;
    END;
    
  2. 关掉自动提交,事务的开启由应用程序控制。

第八章:备份

按方法分:冷备份,热备份

按备份后的文件内容分:逻辑备份(sql语句或数据),裸文件备份

按备份数据库的内容分:完全备份,增量备份(LSN比大小),日志备份

逻辑备份

  1. mysqldump
    mysqldump --databases db1 ... >dump.sql
    

    出来的文件就是表结构和数据,所有这些都是用SQL语句方式表示。

    重要的参数:

    1. –single-transaction:开启参数进行备份时,确保没有其他DDL语句执行
    2. –lock-tables:如果数据库中又有innodb又有myisam,则用这个上锁。但是只是对每个架构下的表
    3. –lock-all-tables:把所有结构的所有表上锁
    4. –add-drop-database:在create database前运行drop database
  2. select [column1]… into outfile ‘name’

    导出的文件是数据并且列与列之间默认用TAB分割

逻辑备份的恢复

mysql -uroot -proot < backup.sql

二进制日志的备份与恢复

开启:

[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

恢复:

shell> mysqlbinlog binlog.00001 | mysql -uroot -proot