《InnoDB存储引擎》读书笔记

第一章

数据库:物理操作系统文件的集合。 实例:后台线程和一个共享内存区。

单进程多线程架构。

配置文件 * /etc/my.cnf * /etc/mysql/my.cnf * /usr/local/mysql/etc/my.cnf * .my.cnf

体系结构

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

InnoDB

面向OLTP。多版本并发控制MVCC。 REPEATABLE READ

聚集clustered方法,表的储存按主键的顺序存放。如果没有显式指定主键,则会为每行生成一个6字节的ROWID作为主键。

MyISAM

不支持事务、表锁设计,支持全文索引。主要面向OLAP。

由MYD和MYI组成。MYD存放数据,MYI存放索引文件。

第二章 InnoDB

后台线程

  • Master Thread。负责缓冲池中的数据异步刷新到磁盘,脏页刷新、合并插入缓冲、UNDO页的回收。
  • IO Thread. 使用AIO,共有4个线程, write, read, insert buffer, log
  • Purge Thread. 事务提交后,undolog不再需要。该线程回收已经使用并分配的undo页。
  • Page Cleaner Thread。脏页刷新。

内存

缓冲池。

储存引擎基于磁盘储存,记录按照页的方式进行管理。缓冲池是一块内存区域,下一次再读取相同的页,先在缓冲池中找。

对于数据库页的修改,首先修改缓冲池的页,再以一定频率刷新到硬盘。

缓冲池中的数据页类型有:索引页、数据页、undo页、insert buffer、adaptive hash index、lock info、 数据字典信息。

LRU List、Free List、Flush List

缓冲池通过LRU(Latest Recent Used)算法管理。最频繁使用页在LRU列表的前端。首先释放LRU列表中的尾端的页。 缓冲池中页的大小默认为16KB。

新读取的页,不直接放到LRU的首部,而是放到LRU列表的midpoint位置,列表长度的5/8。 把midpoint后面的列表称为old列表,之前的列表称为new列表。innodboldblocks_time表示页在midpoint需要多久才加入到LRU列表的热端。

为什么不采用朴素的LRU? 因为某些SQL操作会访问很多页,甚至全部页,但仅仅在该次查询操作,并不是活跃的热点数据。

在LRU类表的页被修改后,称为脏页(Dirty Page),即缓存和硬盘的页数据不一致。数据库会通过CHECKPOINT机制将脏页刷新回磁盘,Flush列表中的页即为脏页列表。

重做日志缓冲 redo log buffer

InnoDB首先将重做日志放到这个缓冲区,然后按一定频率刷新到重做日志文件。一般每秒。

buffer刷新到磁盘的情况 * Master Thread每一秒。 * 每个事务提交时。 * 当buffer剩余空间小于1/2.

2.4 Checkpoint

Write Ahead Log策略,事务提交时,先写重做日志,再修改页。

Checkpoint解决的问题: * 算短数据库的恢复时间。 * 缓冲池不够时,将脏页刷新到磁盘。 * 重做日志不可用时,刷新脏页。

数据库只需对checkpoint后的重做日志进行恢复。

通过Log Sequence Number来标记版本,8字节的数字。

checkpoint类型 * Sharp checkpoint。 在数据库关闭时,将所有的脏页刷新回磁盘。 * Fuzzy checkpoint。 + Master Thread checkpoint。每秒 + Flush LRU list checkpoint。 保证LRU列表需要100个空闲页可供使用。 + Async/Sync Flush checkpoint。重做日志文件不可用时,强制将一些页刷新到磁盘。达到重做日志文件的大小阈值。 + Dirty page too much。90%

2.5 Master Thread

1.0.x

每秒操作: * 总是,日志缓冲刷新到磁盘 * 可能,合并插入缓冲 * 可能,至多刷新100个缓冲池中的脏页到磁盘。当脏页比例超过bufgetmodifiedratiopct。默认90,90%。该值太大。 * 可能,如果没有当前活动,切换到background loop

每10秒: * 可能,刷新100个脏页 * 总是,合并至多5个插入缓冲。(写入密集的情况,忙不过来) * 总是,将日志缓冲刷新到磁盘 * 总是,删除无用的Undo页 * 总是,刷新100个或者10个脏页到磁盘

background loop * 总是,删除无用的undo页 * 总是,合并20个插入缓冲 * 总是,跳回到主循环

1.2.x

innodbiocapacity * 合并插入缓冲时,数量为innodbiocapacity的5% * 刷新缓冲区脏页的数量为innodbiocapacity

bufgetmodifiedratiopct改为75

自适应的刷新innodbadaptiveflushing,判断产生redo log的速度来决定最合适的刷新脏页数量。

2.6 InnoDB关键特性

Insert buffer

插入聚集索引一般时顺序的。但非聚集索引是离散的。

对于非聚集索引的插入和更新,先判断插入的索引页是否在缓冲池,若在,直接插入;若不在,先放入到Insert buffer对象。 将多个插入合并,大大提高了对非聚集索引插入的性能。

需满足的条件 * 辅助索引 * 不是唯一的。

默认最大可以占用1/2的缓冲池内存。

Change Buffer

Insert buffer、delete buffer、purge buffer

适用非唯一的辅助索引。

对一条记录update分两步 * 将记录标记为删除 * 真正地删除

Insert Buffer实现

B+树。

试图通过独立表空间ibd文件恢复表中数据,往往导致check table失败。因为表的辅助索引中的数据可能还在insert buffer。所以repair table后需要重建表的所有辅助索引。

非叶子节点的search key space | marker | offset

  • space表示待插入记录所在表的表空间id,每个表都有唯一的space id。
  • marker
  • offset页所在的偏移量

还有一个特殊的页用来标记每个辅助索引页(space, page_no)的可用空间,这个页的类型为Insert Buffer Bitmap

两次写

保证可靠性。

比如16KB的页,只写了4KB,之后发生服务器挂了。

重做日志中记录的是对页的物理操作,如果这个页本身已经损坏,再对其重做是没有意义的。 在应用重做前,用户需要一个页的副本,当写入失效时,先通过页的副本还原,再进行重做,这就是doublewrite。

doublewrite分两部分 * 内存中的buffer,大小2MB * 物理磁盘上共享表空间中连续的128个页,即2个区(extent),大小同为2MB。

对缓冲区的脏页进行刷新时,通过memcpy先复制到doublewrite buffer,之后buffer再分两次,每次1MB顺序地写入磁盘,马上调用fsync函数。

自适应哈希索引

InnoDB自动为某些热点页建立哈希索引。

异步IO

AIO可以进行IO Merge操作。

如用户访问页的(space, page_no)为(8,6) (8,7) (8,8),每个页的大小为16KB,同步IO需要3次IO操作。可以优化为从(8,6)开始读取48KB。

刷新临接页

当刷新一个脏页时,InnoDB会检查该页所在extent的所有页,如果是脏页,一起刷新。

2.7 启动、关闭、恢复

innodbfastshutdown * 0。关闭时,需完成所有的full purge、merge insert buffer,将所有脏页刷新回硬盘 * 1。默认值。不需要完成full purge、merge insert buffer,刷新部分脏页。 * 2。只写日志文件。下次启动时,回进行恢复操作。

第三章 文件

慢查询日志

设一个阈值,运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。 默认情况,MySQL不启动慢查询。

logqueriesnotusingindexes 记录没有使用索引的语句 logthrottlequeriesnotusing_indexes 阈值

3.2.4 二进制日志

记录了对Mysql数据库执行更改的所有操作。但不包括select和show这类操作。

二进制日志的作用: * 恢复recovery。数据库全备恢复后,通过binlog进行point-in-time的恢复。 * 复制replication。 * 审计audit

binlog默认不启动,开启会降低1%性能。

InnoDB,uncommited的binlog会被记录到一个缓冲中,commited时将缓冲中的binlog写到binlog文件。

binglog并不是每次写的时候同步到磁盘。参数sync_binlog=N表示每写缓冲多少次就同步到磁盘。当N为1,即同步。

binlog_format * STATEMENT, 逻辑SQL语句 * ROW,记录表的行更改情况。 * MIXED

binlog是二进制文件,不能直接查看,需要用mysqlbinlog工具 mysqlbinlog --start-position=203 test.0004

3.5表结构定义文件

每个表都有frm为后缀的文件。

还用来存放视图的定义,如创建了va视图,会产生一个va.frm文件

3.6.1 表空间文件

按表空间tablespace进行存放,默认配置下初始化ibdata1文件,大小10MB。

参数innodbfileper_table,每个表产生一个独立的表空间。表名.ibd

3.6.2 重做日志文件 redolog

至关重要,记录了事务日志。

当media failure时,重做日志就能派上用场。

至少有一个重做日志文件组group,每个组下至少有2个重做日志文件iblogfile0, iblogfile1。 先写重做日志文件1,当文件满后,切换到文件2,文件2满后,切换到文件1.

binlog和redolog * binlog记录所有Mysql的日志 * redolog是InnoDB引擎本身的日志 * binlog的格式是STATEMENT,逻辑日志。 * redolog记录的是关于每个页Page的更改物理情况 * binlog仅在事务提交前提交,只写一次磁盘。而事务进行过程中,不断有redolog写入到重做日志文件。

redolog先写到buffer,再写到文件。从buffer向磁盘写入,按512个字节,即一个扇区的大小写入。因为扇区是写入的最小单位,因此可以保证写入必定成功。因此不需要doublewrite。

innodbflushlogattrx_commit * 0,事务提交时,并不将redolog写入到磁盘的日志,而等待主线程刷新 * 1,commit时将日志缓冲同步到磁盘,即伴有fsync * 2,将重做日志写到磁盘,即文件系统的缓存,不一定fsync。

第四章 表

索引组织表

选择或创建主键 * 第一个定义的非空的唯一索引 Unique NOT NULL * 如果没有,创建一个6字节大小的指针

4.2 InnoDB逻辑储存结构

所有数据被逻辑地存放到一个空间,称为表空间tablespace。 表空间由段segment,区extent,页page组成。页在一些文档中也称为块block。

表空间

如果启动了innodbfileper_table,则每张表的表空间内存放的是数据、索引、插入缓冲bitmap页。 其他类的数据,如回滚undo信息、插入缓冲索引页、系统事务信息、double write buffer等还是存放在共享表空间内。

表空间由各个段组成。 数据段、索引段、回滚段

区由连续的页组成,每个区大小都是1MB,默认页的大小为16KB,即一个区有64个连续的页。

InnoDB磁盘管理的最小单位,默认大小16KB。

数据按行存放,每个页最多存放16KB/2-200行的记录。即7992行记录。

4.3 InnoDB行记录格式

Compact行记录

一个页存放的行数据越多,则性能越高。

变长字段长度列表 | NULL标志符 | 记录头信息 | 列1数据 | 列2数据 | …

变长字段长度列表。 按照列的顺序逆序放置。 若列的长度小于255字节,用1个字节表示;否则用2个字节表示。 NULL标志符。如0000110,为1的值代表第2列和第三列的数据为NULL

Redundant行记录格式

兼容之前版本的页格式

字段长度偏移列表 | 记录头信息 | 列1数据 | 列2数据 | …

行溢出数据

65535长度是所有VARCHAR列的长度总和。

InnoDB的页为16KB,放不下65532个字节。一般情况下,InnoDB的数据都存放在页类型为B-tree node中,当发生行溢出时,数据放到页类型为Uncompress BLOB页中。

Compressed和Dynamic行记录格式

存放在BLOB的数据采用了完全的行外溢的方式。在数据页只存放20个字节的指针,实际数据存放在Off Page。而之前的Compact和Redundant格式会存放768字节的前缀。

Compressed行记录格式会以zlib算法进行压缩,有效存储BLOB、TEXT、VARCHAR

CHAR的行结构存储

CHR(N)中的N是指字符的长度,而不是字节的长度。

4.4 InnoDB数据页结构

  • File Header
  • Page Header
  • Infimun+Supremum Records
  • User Records. 实际储存记录的内容。
  • Free Space。链表数据结构,一条记录被删后,该空间会加入到空闲链表。
  • Page Directory。存放了记录的相对位置。B+树索引只能找到记录所在的页,数据库把页加载到内存,然后通过Page Directory进行二分查找。
  • File Trailer。

4.6 约束

创建一个唯一索引就是创建了一个唯一的约束。 约束是一个逻辑概念,而索引是一个数据结构。

4.7 视图

一个命名的虚表,视图中的数据没有实际的物理储存。

4.8 分区表

逻辑上一个表或一个索引,物理上可能由多个文件组成。

类型 * range。 连续区间的值 * list。 离散的值 * hash。均匀地分到各个分区 * key

分区视NULL值小于任一非NULL值。

第五章 索引和算法

聚集索引,clustered index,按照表的主键构建一颗B+树,叶子节点存放行数据。 辅助索引,Secondary Index,非聚集索引。叶子节点的索引行包含索引列的值和主键的值。再通过主键索引才能拿到完整的行数据。

Cardinality

索引中不重复记录数量的估计值

Cardinality/nrowsin_table应尽可能接近1。 如果非常小,可以考虑删除该索引。 如果一个字段是性别,只有M、F,完全没必要添加索引。

生产环境,索引的更新非常频繁,通过采样的方式统计Cardinality。

更新策略 * 表中1/16数据发生了变化 * statmodifiedcounter > 2 000 000 000

采样过程 * 取得B+树索引的叶子节点数量,记为A * 随机取8个节点,统计每个页不同记录的个数,即P1, P2, …, P8 * Cardinality = (P1+P2+…+P8) * A/8

索引提示

INDEX HINT 告诉优化器使用哪个索引 select * from t use index(a) where a=1 and b=2

Multi-Range Read优化

  • 查询辅助索引,根据查询结果,按主键rowid进行排序,按照主键循序进行行数据查找
  • 减少缓冲池中页被替换的次数。
  • 批量处理对键值的查询操作。

第六章 锁

6.3.1 锁的类型

  • 共享锁 S Lock。允许事务读一行数据
  • 排他锁 X Lock。允许事务删除或更行一行数据。

事务T1已经获得行r的S Lock,那么事务T2可以立即获得行r的S Lock。锁兼容。 如果有事务T3项获得r的X Lock,需要等待T1、T2释放行r的S Lock。锁不兼容。

意向锁,将锁定的对象分为多个层次。对最细粒度的对象进行上锁,首先需要对粗粒度的对象上锁。 如对行r进行上X锁,则首先分别需对数据库A、表、页上意向锁IX,最后对行r上X锁。

意向锁即为表级别的锁。 * 意向共享锁。IS Lock。事务想要获取一张表的某几行的共享锁 * 意向排他锁。IX Lock。事务想要获取一张表的某几行的排他锁

意向锁不回阻塞除全表扫描以外的任何请求。

一致性非锁定读

如果读取的行正在update或delete,这时读操作不会等待,而是去读该行的一个快照数据。

快照数据是该行之前的版本,通过undo段完成。undo用在事务中回滚数据,因此快照数据本身没有额外的开销。

  • read committed, 快照是被锁定行的最新一份快照
  • repeatable read,快照是事务开始时的行数据版本。
1
2
select .. for update
select .. lock in share mode

行锁的3种算法

  • Record Lock。单个行的锁
  • Gap Lock,间隙锁,锁定一个范围,但不包含记录本身
  • Next-Key Lock。默认。锁定一个范围,并锁定记录本身。

如果一个索引有10,11,13,20四个值,则区间为 * (-00, 10] * (10, 11] * (11, 13] * (13, 20] * (20, +00)

当查询的索引含有唯一值,Next-key lock会优化成Recrod Lock,仅锁定该行,而不是范围。

1
2
3
4
5
6
create table z (a int, b int, primary key(a), key(b))
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

select * from z where b=3 for update 因为有两个索引,需分别进行锁定。对聚集索引,仅对a=5的索引加上Record Lock。对于辅助索引,加上next-key lock,锁定(1,3)。同时还会对辅助索引的下一个范围加锁,即(3,6)

6.7 死锁

因争夺资源而造成的互相等待现象。

最简单解决方法:超时。回滚undo量最小的事务。

6.8 锁升级

InnoDB不存在锁升级的问题。不是根据每个记录来产生行锁,而是根据每个事务访问的每个页page对锁进行管理,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常一样。

第七章 事务

7.2 事务的实现

redolog保障事务的原子性和持久性。 undolog保证事务的一致性。

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

redo

  • 内存中的redo log buffer
  • redo log file

Fore log at commit。事务提交时,先将事务日志写入到redo log file。

重做日志缓冲先写入文件系统缓冲,再fsync到磁盘

innodbflushlogattrx_commit * 0。不写入重做日志 * 1。默认。事务提交必须调用一次fsync * 2。仅写入文件系统的缓存中,不fsync

性能优化。将50万行数据插入后一次性commit,而不是每插入一条commit一次。

重做日志以512字节进行储存,以块block的方式保存,称为重做日志块。 磁盘扇区大小也是512字节,日志的写入可以保证原子性,所以不需要doublewrtie。

undo

回滚操作需要undo。undo存放在数据库内部的一个特殊段segment中,称为undo segment。位于共享表空间内。

undo是逻辑日志,只是将数据库逻辑地恢复到原来的样子。数据结构和页本身在回滚后可能打不相同,因为可能会有数个并发事务。

回滚时 * 对于insert, 完成一个delete * 对于delete,执行一个insert * 对于update,一个相反的update

事务提交后,不能马上删除undolog,可能还有其他事务通过undolog得到行记录之前的版本。

若为每一个事务单独分配一个undo页会浪费空间。 设计上可以重用undo页。undo页可能存放不同事务的undolog。事务提交时,将undolog放入链表,判断undo页的空间是否小于3/4,若是则重用。 因此purge操作需要对磁盘进行离散读,会比较缓慢。

undolog格式 * insert undo log。insert操作只对事务本身可见,在事务提交后可以直接删除,不需进行purge操作。 * update undo log。提供MVCC机制,不能在事务提交时删除。提交时放入undolog链表,purge线程删除。

purge

delete和update可能不直接删除数据。

delete from t where a=1 仅将主键为1的记录delete flag设为1。对辅助索引,甚至没有产生undolog。

group commit

一次fsync刷新多个事务日志写入文件。

保证事务和二进制日志的一致性,二者之间使用了两阶段事务 * 事务提交,InnoDB进行prepare * mysql写入二进制日志。 * InnoDB将日志写入到重做日志文件 + 修改内存中事务的信息,将日志写入重做日志缓冲 + 调用fsync确保重做日志写入磁盘。

7.3 事务控制语句

默认情况,事务自动提交,在sql语句后马上执行commit

  • BEGIN,显式地开启一个事务
  • commit
  • rollback
  • savepoint identifier
  • release savepoint identifier
  • rollback to [savepoint] identifier,并不能结束一个事务,仍需要commit或rollback
  • start transaction 设置隔离级别