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

1、MYSQL体系结构:

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

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

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

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

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

INNODB存储引擎版本：

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记录格式：

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

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

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

Redundant

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

索引与算法：

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

B+树索引

全文索引

hash索引

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

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

性能优化：

选择合适的CPU

内存的重要性

硬盘对数据库性能的影响

合理地设置RAID

操作系统的选择也很重要

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

选择合适的基准测试工具

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

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

本栏目推荐文章
• 2024/1/13 算法笔记
• Python学习笔记
• Jmeter 如何连接mysql数据库？
• HDP 笔记
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 学习进度笔记2
• 微软企业库Unity学习笔记(一)
• 微软企业库Unity学习笔记(二)
• kepware关于U-CON的使用笔记 (串口_非主动问答式)
• Anaconda使用笔记

内幕 引擎 笔记 InnoDB MySQL内幕 引擎 笔记innodb 引擎innodb mysql 文件 引擎innodb mysql 引擎innodb myisam mysql 架构 引擎innodb mysql 特点 引擎innodb mysql 引擎myisam innodb mysql 引擎 简介innodb mysql 事务 引擎innodb mysql 引擎innodb