《MySQL技术内幕InnoDB存储引擎》笔记（在更）

MySQL体系结构和存储引擎

1.数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合。在MySQL数据库中，数据库文件看可以frm、MYD、MYI、ibd结尾的文件。

实例：MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程共享。另外，数据库实例才是真正用于操作数据库文件的。

在MySQL数据中实例与数据库的关系是一一对应的。而在集群情况下可能出现一个数据库被多个数据实例使用的情况。
MySQL被设计为单进程多线程架构的数据库，MySQL数据库实例在系统上表现就是一个进程。

2.体系结构

MySQL体系结构

由以下几部分组成

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

MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持。存储引擎是基于表的，而不是数据库

3.MySQL存储引擎

3.1InnoDB存储引擎

 InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（On-Line Transaction Processing）的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。从MySQL 5.6版本开始，InnoDB开始支持全文索引。从MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认存储引擎。
 InnoDB引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB存储引擎自身进行管理。从MySQL4.1版本开始，它可以将每个InnoDB存储引擎的表单独放到一个独立的ibd文件中。此外，InnoDB存储引擎支持用裸设备（row disk）用来建立其表空间。
 InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为可重复读（REPEATABLE）。同时，使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生。InnoDB存储引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read head）等高性能高可用的功能。
 对表中的数据，InnoDB存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显示地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键

3.2 MyISAM存储引擎

 MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP（On-Line Analytical Processing）数据库应用。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件。
 MyISAM存储引擎表由MYD和MYI组成，MYD（data）用来存放数据文件，MYI（index）用来存放索引文件。可以通过使用myisampack工具来进一步进行压缩数据文件，因为myisampack工具使用Huffman编码静态算法来压缩数据，因此使用myisampack工具压缩后的表是只读的，当然用户也可以通过myisampack来解压数据文件。
 在MySQL5.0版本开始，MyISAM默认支持256TB的单表数据。

Note：对于MyISAM存储引擎表，MySQL数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成，这与其他使用LRU算法缓存数据的大部分数据库大不相同。此外，在MySQL 5.1.23版本之后，64位系统可以支持大于4GB的索引缓冲区。

4.各存储引擎之间的比较

存储引擎时MySQL体系结构的核心。图中展现了各存储引擎之间的不同之处，包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。

5.连接MySQL

5.1 TCP/IP

 TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端（client）在一台服务器上，而MySQL实例（server）在另一台服务器上，两台服务器通过TCP/IP网络连接。例如用户可以在Windows服务器下请求一台远程Linux服务器下的MySQL实例：

C:\>mysql -h192.168.0.101 -u david -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.

 这里客户端时Windows，它向一台Host IP为192.168.0.101的MySQL实例发起了TCP/IP连接请求，并且连接成功。之后就可以对MySQL数据库进行一些数据库操作，如DDL和DML等。
 需要注意的是，再通过TCP/IP连接到MySQL实例时，MySQL数据库会先检查一张权限视图，用来判断发起请求的客户端IP是否允许连接到MySQL实例。

5.2 命名管道和共享内存

 在如Windows XP、Windows 2000同一平台之上，如果两个需要进程通信的进程在同一台服务器上，那么可以使用命名管道。在MySQL数据库中须在配置文件中启用 --enable-named-pipe选项。在MySQL4.1之后的版本中，MySQL还提供了共享内存的连接方式，这是通过在配置文件中添加–shared-memory实现的。如果想使用共享内存方式，在连接时，MySQL客户端还必须使用–protocol=memory选项。

5.3 UNIX域套接字

 在Linux和Unix环境下，还可以使用UNIX域套接字。UNIX域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在一台服务器上的情况下使用。用户可以在配置文件中指定套接字文件的路径，如–socket=/tmp/mysql.sock。当数据库实例启动后，用户可以通过下列命令来进行UNIX域套接字的查找：

知道了UNIX域套接字文件的路径后，就可以使用该方式进行连接了：

InnoDB存储引擎

 InnoDB是事务安全的MySQL存储引擎，通常来说，也是OLTP应用中核心表的首选存储引擎。

1.InnoDB存储引擎概述

 该存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU

2.InnoDB存储引擎的版本

版本	功能
老版本InnoDB	支持ACID、行锁设计、MVCC
InnoDB 1.0.x	继承了上述版本的所有功能，增加了 compress 和 dynamic 页格式
InnoDB 1.1.x	继承了上述版本所有功能，增加了 Linux AIO、多回滚段
InnoDB 1.2.x	继承了上述版本所有功能，增加了全文索引支持、在线索引添加

3.InnoDB体系架构

 InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

• 维护所有进程/线程需要访问的多个内部数据结构。
• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
• 重做日志（redo log）缓冲
  … …

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

3.1 后台线程

 InnoDB存储引擎是多线程的墨西哥，因此其后台有多个不同的后台线程，负责处理不同的任务
1. Master Thread
 Master Thread主要负责将缓冲池的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（insert buffer）、undo页的回收等。
2. IO Thread
 在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的主要工作是负责这些IO请求的回调（call back）处理。InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO thread。在Linux平台下，IO Thred的数量不能进行调整，但是在Windows下可以通过参数innodb_file_io_threads来增大IO Thread。从InnoDB 1.0.x版本开始，read thread和
write thread分别增大到了4个，并且不再使用innodb_file_io_threads参数，而是分别使用innodb_read_io_threads和innodb_writes_io_threads参数进行设置。可以通过命令SHOW ENGINE INNODB STATUS命令来观察InnoDB中的IO Thread。
3. Purge Thread
 事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，清楚（purge）操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能，不过只支持启用一个Purge Thread。从1.2版本开始，InnoDB支持多个Purge Thread，这样做的目的是为进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。
4. Page Cleaner Thread
 它是在1.2版本中引入的。作用是将之前版本中的脏页的刷新操作都放入到单独的线程中来完成。其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能

3.2 内存

1. 缓冲池
 InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。
 缓冲池简单来说是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，则该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。（类似于操作系统中的TLB）。
 对于数据页中的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。同样是为了提高数据库的整体性能。
 对于InnoDB存储引擎而言，其缓冲池的配置通过参数innodb_buffer_pool_size来设置。缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。

• InnoDB内存数据对象
  
   从1.0版本开始，允许有多个缓冲池实例。可以通过参数innodb_buffer_pool_instances来进行配置，其默认值为1。从MySQL 5.6版本开始，可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态：

SELECT POOL_ID, POOL_SIZE,
	   FREE_BUFFERS, DATABASE_PAGES
	   FROM INNODB_BUFFER_POOL_STATS;

输出：

2. LRU List、Free List和Flush List
 数据库中的缓冲池是通过LRU（Latest Recent Used）算法来进行管理的。最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。InnoDB存储引擎对传统的LRU算法做了一些优化。在InnoDB存储引擎中，LRU列表还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。在默认配置下，该位置在LRU列表长度5/8处。midpoint位置可由参数innodb_old_blocks_pct控制(即设置里末尾百分之多少的位置)。在InnoDB的存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。
 为什么采用改进的LRU算法？因为若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被让入LRU列表的首部，那么可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。**当需要从缓冲池中分页时，首先从Free列中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配配个新的页。当页从LRU的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。**可以通过命令SHOW ENGINE INNODB STATUS;；来观察LRU列表及Free列表的使用情况和运行状态。从1.2版本开始，还可以通过表INNODB_BUFFER_POOL_STATS来观察缓冲池的运行状态，如：

SELECT POOL_ID, HIT_RATE,
		PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNG
		FROM information_schema.INNODB_BUFFER_POOL_STATS;

输出：

 从1.0版本开始，InnoDB支持压缩页功能，即将原本16KB的页压缩为1KB、2KB、4KB和8KB。对于非16KB的页，是通过unzip_LRU列表进行管理的。引出问题，对于压缩页的表，每个表的压缩比率可能各不相同。unzip_LRU是怎么样从缓冲池中分配内存的呢？首先在unzip_LRU列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如需要从缓冲池中申请页为4KB的大小，其过程如下：

1. 检查4KB的unzip_LRU列表，检查是否有可用的空闲页；
2. 若有，则直接使用；
3. 否则，检查8KB的unzip_LRU列表；
4. 若能够得到空闲页，将页分成2个4KB页，存放到4KB的unzip_LRU列表；
5. 若不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页、2个4KB的页，分别存放到对应的unzip_LRU列表中。

 在LRU列表中的页被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过checkpoint机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。同LRU列表一样，Flush列表也可以通过命令SHOW ENGINE INNODB STATUS来查看，其中Modified db pages显示了脏页的数量。

3. redo log缓冲
 由InnoDB内存区可以看到，内存区除了有缓冲池外，还有重做日志缓冲。InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到外部的重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制，默认为8MB。
 通常情况下8MB的重做日志缓冲池足以满足绝大部分的应用，因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

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

4. 额外的内存池
 在InnoDB存存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

4.Checkpoint技术

 因为页的操作首先都是在缓冲池中完成的，如果一条DML改变了页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。
 如果每个页发生变化，就将新页的版本刷新到磁盘，这个开销是非常大的。若热点数据集中在某几个页中，那么数据库的性能将变得非常差。同时，如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先重做日志，再修改页。通过redo log来完成数据恢复。这也是事务ACID中D（Durability持久性）的要求。
 宕机后数据库的恢复时间。当数据库运行了几个月甚至几年时，这时发生宕机，重新应用重做日志的时间会非常久，此时恢复的代价也会非常大。因此延伸出checkpoint技术解决一下几个问题

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

当数据库宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复时间。
 在InnoDB存储引擎内部，有两种Checkpoint，分别为：

• Sharp Checkpoint
• Fuzzy Checkpoint

 Sharp Checkpoint发生在数据库关闭时将所有脏页都刷新回磁盘。InnoDB存储引擎内部使用Fuzzy Checkpoint进行部分脏页的刷新。在InnoDB存储引擎中可能发生的Fuzzy Checkpoint：

• Master Thread Checkpoint
• FLUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpoint
• Dirty Page too much Checkpoint

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

 FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。

 Async/Sync Flush Checkpoint指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的

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

5.Master Thread工作方式

 InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的。

参考文献：[1][MySQL技术内幕InnoDB存储引擎第2版][姜承尧著]