MySQL之innodb数据库引擎

简介

innodb事务型数据库的首选引擎，支持ACID事务，支持行级锁定。InnoDB是为处理巨大数据量时的最大性能设计。Innodb存储引擎完全与MySQL服务器整合，InnoDB存储引擎为主存中缓存数据和索引而维持它自己的缓冲池。InooDB存储它的表和索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如MyISAM表被存放在单独的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上（因为它可以包含数个文件）。每个表也可以使用独立的空间，只需要启用选项 innodb_file_per_table。InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

InnoDB给MySQL提供了具有事务、回滚和崩溃修复能力、多版本并发控制的事务安全型表。InnoDB行级锁，提供与Oracle类似的不加锁读取。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

Inoodb的存储结构

　从物理意义上来讲，InnoDB表由共享表空间、日志文件组（redo文件组）、表结构定义文件组成。若将innodb_file_per_table设置为on，则系统将为每一个表单独的生成一个table_name.ibd的文件，在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。表结构文件则以.frm结尾，这与存储引擎无关。

　以下为InnoDB的表空间结构图：

在InnoDB存储引擎中，默认表空间文件是ibdata1，初始化为10M，且可以扩展，如下图所示：

实际上，InnoDB的表空间文件是可以修改的，使用以下语句就可以修改：

Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend

　　使用共享表空间存储方式时，Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

　　而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。

以下即为不同平台下，单独表空间文件最大限度。

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB

※ 以下是MySQL文档中的内容：
Windows用户请注意： FAT和VFAT (FAT32)不适合MySQL的生产使用。应使用NTFS。

共享表空间与独占表空间可以通过参数innodb_file_per_table来转换，若为1，则开启独占表空间，否则，开启共享表存储。

在服务器资源有限,单表数据不是特别多的情况下, 独立表空间明显比共享方式效率更高 . 但是MySQL 默认是共享表空间 。

具体的共享表空间和独立表空间优缺点如下：

共享表空间：
优点：
可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。
缺点：
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：在配置文件（my.cnf）中设置： innodb_file_per_table

优点：
1． 每个表都有自已独立的表空间。
2． 每个表的数据和索引都会存在自已的表空间中。
3． 可以实现单表在不同的数据库中移动。
4． 空间可以回收（除drop table操作处，表空不能自已回收）
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用truncate table也会使空间收缩。
c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。

※ 对于启用了innodb_file_per_table 的参数选项之后，在每个表对应的.idb文件内只是存放了数据、索引和插入缓冲，而撤销（undo）信息，系统事务信息，二次写缓冲等还是存放在了原来的共享表空间内。

※ 数据段即B+树的叶节点，索引段即为B+树的非索引节点。

※ InnoDB存储引擎的管理是由引擎本身完成的，表空间是由分散的页和段组成。

※ 区由64个连续的页组成，每个页大小为16K，即每个区大小为1MB，创建新表时，先有32页大小的碎片页存放数据，使用完后才是区的申请，（InnoDB最多每次申请4个区，保证数据的顺序性能）

※ 页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、以及插入缓冲空闲列表页。

InnoDB存储引擎

innodb引擎架构

   InnoDB的多个内存块组成了内存池，负责如下工作：

1).维护所有进程/线程需要访问的多个内部数据结构。

  2).缓存磁盘上的数据，方便快速的读取，并且在对磁盘文件的数据进行修改之前在这里缓存。

  3).重做日志缓存。

  后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据，此外、将已经修改的数据文件刷新到磁盘文件

后台线程

  innodb存储引擎后台有7个线程，—–4个IO线程(insert buffer thread,log thread,read thread,write thread)，1个master thread，一个lock监控线程，一个错误监控线程。

内存

  innodb存储引擎内存由以下三个部分组成：缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional memory pool)。可以使用 show engine innodb status来查看innodb_buffer_pool的使用情况。

  innodb_buffer_pool_size：具体看，缓冲池中的数据库类型有：索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hashindex)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)。

     InnoDB工作方式：将数据文件按页(每页16K)读入InnoDBbuffer pool，然后按最近最少使用算法(LRU)保留缓存数据，最后通过一定频率将脏页刷新到文件。

master thread

master thread源码分析

master thread的潜在问题

  1、由于硬件的发展，现在的硬件性能已经提高了很多，如果innodb每秒最大刷新100个脏页，那么效率会很低，为了解决这个问题，innodb plugin提供了一个参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值是200，规则如下：在合并插入缓存时，合并插入缓存的数量为innodb_io_capacity的5%；在从缓冲区刷新脏页时，啥新脏页的数量为innodb_io_capacity。

  2、关于innodb_max_dirty_pages_pct值的争议，如果值过大，内存也很大或者服务器压力很大，那么效率很降低，如果设置的值过小，那么硬盘的压力会增加，建议是在75-80.并且innodb plugin引进了innodb_adaptive_flushng(自适应的刷新)，该值影响每秒刷新脏页的数量。

关键特性,为innodb提高性能的技术

插入缓存

  当一个表有非聚集索引时，对于非聚集索引的叶子节点的插入不是顺序的，这时候需要离散的访问非聚集索引页，性能就在这里降低了，这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。

  对于非聚集索引的插入和更新操作，不是每一次都直接插入索引页，而是先判断插入的非聚集索引页是否在缓存中，如果在就直接插入，如果不在就放入到一个插入缓冲区中，好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。

  插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引)：索引是辅助索引；索引不是唯一的。

两次写

  两次写给innodb带来的是可靠性，主要用来解决部分写失败(partial page write)。在应用重做日之前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。

  doublewrite有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另外一部分就是物理磁盘上的共享表空间中联系的128个页，即两个区，大小同样为2M。当缓冲池的张也刷新时，并不直接写硬盘，而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。

自适应哈西索引

  由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。

启动、关闭、恢复

innodb_fast_shutdown影响InnoDB表关闭。该参数有0、1、2三个参数。

  0 MySQL关闭时  完成所有的full purge和merge insertbuffer操作

     1默认值 只将缓冲池内的一些脏页刷新至磁盘

     2将日志都写入日志文件不会有任何事务丢失但下次启动时会进行recovery

  innodb_force_recovery影响整个innodb存储引擎的恢复状况，该值默认为0，表示当需要恢复时，需要执行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，mysql数据库可能宕机，并把错误写入错误日志中。