MySQL技术内幕InnoDB存储引擎学习笔记

1、MYSQL体系结构:

 

2、INNODB存储引擎:
支持事务，其设计目的主要是面向在线事务处理的应用。

特点：行锁设计，支持外键，并支持类似oracle的非锁定读，同时设计用来最有效的利用使用内存和CPU；5.5.8开始默认使用innodb存储引擎

使用多版本并发控制来获得高并发性，并实现了sql的4种隔离级别

提供了插入缓存，二次写，自适应hash索引，异步IO,刷新邻接页预读等高性能和高可用功能

对于表中存储，innodb存储引擎采用聚集的方式，因此每张表都是按主键顺序进行存放（没显性定义，会自动生成6个字节的rowid,并以此来作为主键）

INNODB存储引擎版本：

版本	功能
老版本InnoDB1.0	支持ACID,行锁设计，MVCC
InnoDB1.0.x	继承了上述所有功能，增加了compress,dynamic页格式
InnoDB1.1.x	继承了上述所有功能，增加了linux AIO,多回滚段
InnoDB1.2.x	继承了上述所有功能，增加了全文索引支持、在线索引添加

 

 

3、INNODB的体系架构

 

 

内存池的负责的工作：

维护所有进程/线程需要访问的多个内存数据结构

缓存磁盘中的数据，方便快速读取

重做日志缓存（redo log）

后台线程的主要作用：主要负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。将已修改的磁盘数据刷新到磁盘文件，同时保证数据库发生异常情况下InnoDB能恢复到正常运行状态。

 

3.1后台线程：

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

 

Master Thread

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

IO Thread

在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大的提高数据库的性能。而IO Thread

的主要工作是负责这些IO的请求回调处理

write ,read insert buffer 和log IO thread

innodb版本查询

show variables like 'innodb_version' \G

show varables like 'innodb_%io_thread' \G

show engine innodb status 来观察InnoDB中的IO Thread:

 

Purge Thread

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

Innodb 1.1版本之前，purge 操作仅在Master Thread 中完成，1.1版本之后，purge操作可以独立到单独的线程进行，减轻

Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。

1.2版本之前innodb_purge_threads设置大于1，启动时还是为1，并在错误文件显示告警：‘innodb-purge-threads’：unsigned value 4 adjusted to 1

1.2版本之后可以设置多个，这样的目的是进一步加快undo页的回收。PurgeThread 需要离散地读取undo页，这样也能进一步的利用磁盘的随机读取性能。

 

Page Cleaner Thread

Page Cleaner Thread 是在InnoDB1.2.x版本中引入的。其作用是将之前版本中的脏页的刷新操作都放入到单独的线程中来

减轻Master Thread的工作，提高Innodb的性能

 

 

3.2 内存

3.2.1缓冲池

InnoDB存储引擎是基于磁盘存储，并将其中的数据按照页的方式进行管理。因此是基于磁盘的数据库系统，由于CPU与磁盘之间的沟壑，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库整体性能。

缓冲池参数：innodb_buffer_pool_size

缓冲池实例参数：innodb_buffer_pool_instances  1.0.x及以上开始支持，原因是：每个页跟进hash平均分配到缓冲池，减少数据库的内部内存的竞争，增加数据库并发处理能力。

 

SHOW ENGINE INNODB STATUS \G 观察

SELECTPOOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGESFROMINNODB_BUFFER_POOL_STATUS \G

LRU list、 Free List和Flush List

数据库中的缓冲池是通过LRU（Latest Recent Used,最近最少使用）算法来进行管理的。使用频繁的在LRU列表的前端

而最少使用的页在LRU的尾端。当缓冲池不能存放新读取数据页时，将首先释放LRU列表的尾端的页。

默认16KB页的大小

LRU算法的优化：LRU列表中加入了midpoint 位置，默认配置在5/8位置

innodb_old_blocks_pct

innodb_old_blocks_time

Buffer pool hit rate (缓冲池命中率)，这个例子中为100%说明运行良好。正常该值不小于95%

 

重做日志缓冲：

InnoDB存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）:

innodb_log_buffer_size 重做日志缓冲大小，默认8MB;刷新的3种情况：

Master Thread 每秒将重做日志缓冲刷新到重做日志文件

每个事务提交时会将重做日志缓冲刷新到重做日志文件

当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

额外的内存池：

额外的内存池通常被DBA忽略，他们认为该值并不十分重要，事实恰恰相反，该值同样

十分重要。在InnoDB存储引擎中，对内存的管理是通过一种称作内存堆的方式进行。

 

 

Checkpoint技术：

1、缓冲池可以存储数据库的所有数据

2、重做日志可以无限增大

3、如果不刷新，数据库运行几个月，几年恢复时间代价太大

因此Checkpoint技术的目的是解决一下几个问题：

1、缩短数据库的恢复时间

2、缓冲池不够用时，将脏页刷新到磁盘

3、重做日志不可用时，刷新脏页

有2种Checkpoint

Sharp Checkpoint  默认数据库停止时全部脏页刷新到磁盘

Fuzzy Checkpoint  可能发生刷新脏页的情况

Master Thread Checkpoint

FLUSH_LRU_LIST Checkpoint

Async/Sync Flush Checkpoint

Dirty Page too much Checkpoint

 

Master Thread 工作方式

Master Thread 具有最高的线程优先级别。其内部由多个循环（loop）组成：

主循环，后台循环，刷新循环，暂停循环。会根据运行状态在4个循环种进行切换

伪代码如下：

void master_thread(){

loop:

for (int i=0;i<10:i++){

   do thing once per second

   sleep 1 second if necessary

}

do things once per ten seconds

goto loop;

}

在负载很大的情况下可能会有延迟（delay）;源代码通过其他方式来尽量保证这个频率

每秒一次的操作包括：

日志缓冲刷新到磁盘，即使这个事务还未提交（总是）

合并插入缓冲（可能）

至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）

如果当前没有用户活动，则切换到background loop(可能)

接下来是10s的操作：

刷新100个脏页到磁盘（可能的情况）；

合并至多5个插入缓冲（总是）

将日志缓冲刷新到磁盘（总是）

删除无用的Undo页（总是）

刷新100个或10个脏页到磁盘（总是）

innodb_io_capacity 合并插入缓冲为此值的5%

缓冲取刷新脏页时，刷新脏页的数据量为innodb_io_capacity

 

Innodb的关键特性：

插入缓冲

自适应hash

两次写

异步IO

刷新邻接页

插入缓冲是InnoDB存储引擎关键特性中最令人激动和兴奋的一个功能。

插入缓冲使用的满足的条件：索引是辅助索引，索引不是唯一的

比如将IBUF_POOL_SIZE_PER_MAX_SIZE改为3则最大只能使用1/3的缓冲池内存

2、Change Buffer

Insert Buffer、Delete Buffer、Purge Buffer

使用对象依旧时非唯一的辅助索引。

innodb_change_buffer_max_size默认是25，表示最多使用1/4的缓冲池内存空间。

Insert Buffer的内部实现：

B+树

刷新邻接页：工作原理：当刷新一个脏页时，InnoDB存储引擎会检测该页所在的区的所有页，如果是脏页，那么一起进行刷新。

需要考虑2个问题：

是不是可能将不怎么脏的页进行了写入，该页之后又会很快变成脏页？

固态硬盘有着较高的IOPS，是否还需要这个特性？

参数：innodb_flush_neighbors进行控制，传统机械盘启用该特性，固态硬盘可以设置成0，关闭此特性。

启动、关闭、恢复

innodb_fast_shutdown,参数可取0、1、2默认是1

0表示在MySQL数据库关闭时，InnoDB需要完成所有的full purge和merge insert buffer，并且将所有的脏页刷新回磁盘。

升级时必须将此参数设置为0，然后再关机。

1、刷新脏页，不刷新full purge merge insert buffer

2、写入日志保证数据不丢，但是启动时需要时间恢复

innodb_force_recovery有1~6。大的数字表示包含了前面所有小数字的表示的影响。

1（SRV_FORCE_IGNORE_CORRUPT）:忽略检测到的corrupt页

2（SRV_FORCE_NO_BACKGROUND）:阻止Master Thread 线程的运行，如：Master Thread线程需要进行full purge，而这会导致crash.

3 (SRV_FORCE_NO_TRX_UNDO):不进行事务的回滚

4、（SRV_FORCE_NO_IBUF_MERGE）:不进行插入缓冲的合并操作

5、（SRV_FORCE_NO_UNDO_LOG_SCAN）:不查看撤销日志（undo Log）,InnoDB存储引擎会将未提交的事务视为已提交

6、（SRV_FORCE_NO_LOG_REDO）:不进行前滚的操作

innodb_force_recovery设置大于0后，用户可以对表进行select\create\drop，但无法进行insert\update\delete这类操作

重做日志的条目由4个部分组成：

redo_log_type  \space\page_no\redo_log_body

 

4、表

4.1 索引组织表

在InnoDB存储引擎中、表都是根据主键顺序组织存放的，这种存储方式叫索引组织表

CREATE TABLE z(

a INT NOT NULL,

b INT NULL,

c  INT NOT NULL,

d INT NOT NULL,

UNIQUE KEY (b)

UNIQUE KEY(d),UNIQUE KEY(c)

);

insert into z select 1,2,3,4;

insert into z select 5,6,7,8;

insert into z select 9,10,11,12;

select a,b,c,d,_rowid from z;

InnoDB的逻辑存储结构：

表空间，表空间又分为段，区，页组成

 

表空间参数：innodb_file_per_table

表空间存放的数据:数据、索引、插入缓冲bitmap页，

页:整体页结构

 

 

File Header组成部分如下：

InnoDB引擎中页的类型：

 

Page Header由14部分组成

 

 

InnoDB存储引擎中，常见的页类型有：

数据页（B-tree Node）

undo页（undo log Page）

系统页（System Page）

事务数据页（Transaction system Page）

插入缓冲位图页（Insert Buffer Bitmap）

插入缓冲空闲列表页（Insert Buffer Free List）

未压缩的二进制大对象页（Uncompressed Blob page）

压缩的二进制大对象页（compressed Blob Page）

 

行:有2种文件格式：

InnnoDB存储引擎有两种文件格式：一种叫Antelope（羚羊），另一种叫Barracuda（鱼予）

Antelope文件格式下有compact(紧凑的)和redundant（冗余的）两种行记录格式

Barracuda文件格式下有compressed(压缩的)和dynamic（动态的）两种记录格式

Show table status like “表名”；

Compact记录格式：

变长字段长度列表

NULL标志位

记录头信息

1列数据

2列数据

........

变长字段1个字节或者2个字节表示

NULL标示位使用1个字节表示

记录头固定5个字节表示（40位）

名称	大小（bit）	描述
（）	1	未知
（）	1	未知
Delete_flag	1	该行是否已被删除
Min_red_flag	1	为1，如果该记录预先被定义为最小记录
n_owned	4	记录拥有的记录数
heap_no	13	索引堆中，该条记录的排序记录
record_type	3	记录类型，000表示普通，001表示B+树节点指针，010表示infinum,011表示suprenum,1xx表示保留
next_record	16	页中下一条记录的相对位置
Total	40

 

Redundant

字段长度偏移量列表

记录头信息

1列数据

2列数据

........

记录固定6个字节表示（48）

名称	大小（bit）	描述
（）	1	未知
（）	1	未知
Delete_flag	1	该行是否已被删除
Min_red_flag	1	为1，如果该记录预先被定义为最小记录
n_owned	4	记录拥有的记录数
heap_no	13	索引堆中，该条记录的排序记录
n_fields	10	记录中列的数量
1byte_offs_flag	1	偏移列表为1字节还是2字节
next_record	16	页中下一条记录的相对位置
Total	40

索引与算法：

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

B+树索引

全文索引

hash索引

B+树中的B不是代表二叉（binary）,而是代表平衡（balance）,因为B+树是从最早的平衡

二叉树演化而来，但是B+树部署一个二叉树。

 

性能优化：

选择合适的CPU

内存的重要性

硬盘对数据库性能的影响

合理地设置RAID

操作系统的选择也很重要

不同文件系统对数据库的影响

选择合适的基准测试工具

 

OLAP是CPU密集型的操作，OLTP是IO密集型的操作

SHOW GLOBAL STAUTS LIKE 'innodb%read%' \G