MySQL存储引擎之InnoDB

一.MySQL组成部分

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

1.1mysql的连接方式
在windows的窗口下发送TCP/IP连接请求

mysql -h127.0.0.1 -u root -p

二.InnoDB存储引擎

查看inindb版本

SHOW VARIABLES LIKE 'innodb_version'；

查看innodb信息

SHOW ENGINE INNODB STATUS\G;

2.1InnDB存储引擎特点

1.InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，
即默认读取操作不会产生锁。从 MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

2.InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB存储引擎自身进行管理。从 MySQL 4.1（包括4.1）版本开始，
它可以将每个InnoDB存储引擎的表单独存放到一个独立的 ibd文件中。此外，InnoDB存储引擎支持用裸设备（row disk）用来建立其表空间。

3.InnoDB通过使用多版本并发控制(MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。
同时，使用一种被称为next-keylocking的策略来避免幻读( phantom）现象的产生。
除此之外，InnoDB储存引擎还提供了插人入缓冲( insert buffer)、二次写(double write)、自适应哈希索引(adaptive hashindex)、预读（read ahead）等高性能和高可用的功能。

4.对于表中数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。
如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

2.2组成部分

2.2.1后台线程

①Master Thread

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

②IO Thread

在InnoDB存储引擎中大量使用了AIO (Async IO）来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back>处理。

查看io命令

SHOW VARIABLES LIKE 'innodb_%io_threads';

③Purge Thread

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

查看命令

SHOW VARIABLES LIKE 'innodb_purge_threads';

④Page Cleaner Thread

将之前版本中脏页的刷新操作都放人到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

2.2.2 内存

①缓冲池

缓冲池简单来说就是一块内存区域。InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database)。
在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

查看缓冲池大小命令

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

②FreeList、LRUList和 FlushList
FreeList

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。
当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。
如果FreeList没有空闲页没有，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。
当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

LRUList

数据库中的缓冲池是通过LRU (Latest Recent Used，最近最少使用）算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。InnoDB的存储引擎中，LRU列表中还加人了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint，在3/8位置中,之前列表称为new列表，之后称为old列表

为什么放在3/8位置

若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。

查看新读取的页插入的位置

 SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\G;

FlushList

在LRU列表中的页被修改后，称该页为脏页(dirty page)，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。需要注意的是，脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

③重做日志缓冲

InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。默认为16MB

Master Thread每一秒将重做日志缓冲刷新到重做日志文件;
每个事务提交时会将重做日志缓冲刷新到重做日志文件;
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

查看重做日志命令

SHOW VARIABLES LIKE 'innodb_log_buffer_size'\G;

④额外内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆(heap)的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。
例如，分配了缓冲池（innodb_buffer_pool)，但是每个缓冲池中的帧缓冲(frame buffer）还有对应的缓冲控制对象(buffer control block)，这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。

2.3Checkpoint技术

2.3.1 为什么要有缓冲池和重做日志？

重做日志目的：

1. 如果每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常大的。若热点数据集中在某几个页中，那么数据库的性能将变得非常差。
2. 如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。

缓冲池的目的：

1. 缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。因此页的操作首先都是在缓冲池中完成的。
2. 避免数据丢失问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D (Durability持久性）的要求。

2.3.2 Checkpoint作用

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

作用体现

1. 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。
2. 此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。
3. 重做日志是可以被重用的（通过LSN标记重做位置），当数据库发生宕机时，数据库恢复操作不需要的那部分的重做日志，因此这部分就可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

2.3.3 Checkpoint分类

2.3.3.1.Sharp Checkpoint

在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1。

2.3.3.2.Fuzzy Checkpoint

只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

发生Fuzzy Checkpoint几种情况

• ①Master Thread Checkpoint
  以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。

• ②FLUSH_LRU_LIST Checkpoint
  为了保证LRU列表中有足够的空闲页，需要将列表中尾端的脏页刷新进行Checkpoint

• ③Async/Sync Flush Checkpoint（5.6以后由Page Cleaner Thread完成）
  重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_Isn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn

• ④Dirty Page too much Checkpoint
  脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制:

查看脏页占用比例命令（当脏页达到70%会强制执行Checkpoint）

SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'\G;

2.4 InnoDB关键特性

2.4.1 插入缓冲

①Insert Buffer

对于非聚集索引的插人或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插人﹔若不在，则先放入到一个Insert Buffer对象中，好似欺骗。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer 和辅助索引页子节点的merge(合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中)，这就大大提高了对于非聚集索引插人的性能。

需要满足的条件：

• ①索引是辅助索引
• ②索引不是唯一的

问题：在写密集的情况下，插入缓冲会占用过多的缓冲池内存（ innodb_buffer_pool)，默认最大可以占用到1/2的缓冲池内
解决：修改IBUF_POOL_SIZE_PER_MAX_SIZE，例如修改为3 表示1/3

表结构

Insert Buffer是一棵B+树，因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的search key（键值)，共占用9个字节
①space表示待插入记录所在表的表空间id，在InnoDB存储引擎中，每个表有一个唯一的space id，可以通过space id查询得知是哪张表。space占用4字节。
②marker是用来兼容老版本的Insert Buffer，占用1字节
③offset表示页所在的偏移量，占用4字节。

当一个辅助索引要插入到页(space，offset）时，如果这个页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询Insert Buffer这棵B+树，然后再将这条记录插人到 Insert Buffer B+树的叶子节点中。

metadata结构如下

②Change Buffer

Change Buffer包括Delete Buffer、Purge buffer。
Delete Buffer对应UPDATE操作的第一个过程，即将记录标记为删除。
Purge Buffer对应UPDATE 操作的第二个过程，即将记录真正的删除。
针对对象为：非唯一的辅助索引

通过参数innodb_change_buffer_max_size来控制Change Buffer最大使用内存的数量，该参数的最大有效值为50

命令(表示25%)

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'\G;

2.4.2 两次写（doublewrite）

doublewrite 由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区(extent)，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写人共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的

2.4.3 自适应哈希索引

哈希（hash）是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3～4层，故需要3～4次的查询。

2.4.4异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO (Asynchronous IO,AIO）的方式来处理磁盘操作。

2.4.5 刷新临近页

InnoDB存储引擎还提供了Flush Neighbor Page（剧新邻接页）的特性。其工作原理为:当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（ extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写人操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。
但是需要考虑到下面两个问题:

1. 是不是可能将不怎么脏的页进行了写人，而该页之后又会很快变成脏页?
2. 固态硬盘有着较高的IOPS，是否还需要这个特性?

三.文件

3.1参数文件

MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。

命令

mysql--help l grep my.cnf

3.2 日志文件

3.2.1错误日志

错误日志文件对 MySQL的启动、运行、关闭过程进行了记录

查看错误日志命令

show variables like 'log_error';

3.2.2 慢查询日志

慢查询日志(slow log）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。例如，可以在 MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该阈值可以通过参数 long_query_time来设置，默认值为10，代表10秒。

查看时长命令

show variables like 'long_query_time';

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

查看慢查询日志开启命令

 SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志和更改时长命令

SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0.001;

mysql5.1之后慢查询日志记录在slow_log表中（使用的是CSV引擎 可以改）

3.2.3 查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为:主机名.log。使用方法和慢查询日志一样

查看查询日志命令

show variables like '%general_log%';

3.2.4二进制日志

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

查看二进制文件状态（默认关闭的）

show variables like 'log_bin';

mysql5.7开启二进制文件,修改my.cnf并且将以下参数加入其中,重启mysql实例

server-id=11           #由于bug,所以需要设置该参数.否则无法启动mysql实例
log-bin = mysql-bin    #其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径

二进制文件的作用

1. 恢复（ recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
2. 复制（replication):其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（-一般称为slave或standby）与一台 MySQL数据库（一般称为master 或primary)进行实时同步。
3. 审计（ audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

3.3 InnoDB存储引擎文件

3.3.1表空间文件

查看innodb_data_file_path参数。

SHOW VARIABLES LIKE '%innodb_data_file_path%';

设置共享表空间文件，ibdata1和ibdata2的大小要和data目录下的这个俩文件大小相同，因此查看此时的ibdata1大小，并设置为76M

innodb_data_file_path= ibdata1:76M;ibdata2:76M:autoextend:max:500M

我们可以将 ibdatal和ibdata2两个文件用来组成表空间。若这两个文件位于不同的磁盘上,磁盘的负载可能被平均,因此可以提高数据库的整体性能。注意若要指定不同的磁盘空间，则需要添加空的innodb_data_home_dir参数才行。然后将之前的redo log 和 共享表空间文件删除。

innodb_data_home_dir =innodb_data_file_path = E:\\ibdata1:76M;D:\\ibdata2:76M:autoextend:max:500M

注意：使用frm和idb文件恢复数据。共享表空间文件也要进行备份恢复

3.3.2 重做日志文件

当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性；

1. 每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1；
2. 日志组中每个重做日志的大小一致，并循环使用；
3. InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；
4. 为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面；

查看重做日志的相关参数

show variables like 'innodb%log%';

四、表结构

4.1索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table)，如果没有会按如下方式创建。

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

4.2 InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment)、区( extent)、页(page）组成。页在一些文档中有时也称为块（block)

4.3InnoDB数据页结构

File Header（文件头）
Page Header（页头)
Infimun和 Supremum Records
User Records（用户记录，即行记录)
Free Space（空闲空间)
Page Directory（页目录)
File Trailer（文件结尾信息)

4.3.1 File Header

用来记录页的一些头信息

①组成部分

② 页的类型

4.3.2 Page Header

用来记录数据页的状态信息

组成部分

4.3.3lnfimum和Supremum Record

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值

4.3.4User Record和 Free Space

User Recor即实际存储行记录的内容。
Free Space指的是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

4.3.5Page Directory

Page Directory（页目录）中存放了记录的相对位置（注意，这里存放的是页相对位置，而不是偏移量)，有些时候这些记录指针称为Slots（槽）或目录槽（DirectorySlots)。

4.3.6File Trailer

为了检测页是否已经完整地写人磁盘（如可能发生的写人过程中磁盘损坏、机器关机等)，InnoDB存储引擎的页中设置了File Trailer部分。

Linux

//多个配置文件mysql会以最后一个为准
mysql --help | grep my.cnf

windows

mysql--help