MySQL之InnoDB引擎（一）

1.InnoDB介绍

InnoDB是一个通用的存储引擎，同时具备高可靠性与高性能的特性，除非用户指定存储引擎的类型，否则其作为MySQL Server的默认存储引擎。

使用InnoDB存储引擎的优势包括如下几点：

• DML操作符合ACID模型，使用事务提交、事务回滚以及故障恢复的措施保护用户数据的安全
• 支持行级锁以及一致性读，能提升多用户使用场景下的并发性以及性能
• 数据表使用主键优化查询来自磁盘中的数据记录，每个数据表都有一个被称之为聚簇索引的主键索引，使用该索引查询数据的时候可以最小化I/O次数
• 使用外键约束措施保证数据的完整性，当执行增加、更新、删除操作时会检查相关联的数据表从而保证相关联数据记录的一致性

下表列出了InnoDB存储引擎的特性：

特性	支持
B-tree索引	是
备份与恢复	是
集群数据库	否
聚簇索引	是
数据缓存	是
数据加密	是
外键约束	是
全文检索	是
地理空间数据类型	是
地理空间索引	是
哈希索引	否
索引缓存	是
锁粒度	行级
MVCC	是
主从复制	是
存储容量	最大64TB
T-tree索引	否
事务	是
数据字典的统计	是

表1 InnoDB存储引擎的特性

2.InnoDB架构

下图是InnoDB的总体架构图，包括两个部分：内存存储架构与磁盘存储架构

​​​​​​​​​​​​​​

图1 InnoDB的总体架构

2.1内存存储架构

内存存储架构主要包括数据缓存与数据缓冲的管理，包括如下几个部分：缓冲池、缓冲变化、适应性哈希索引、缓冲日志。

2.1.1缓冲池（Buffer Pool）

缓冲池是一块来自主内存的区域，InnoDB使用该区域缓存数据表以及索引需要访问的数据。缓冲池缓存了频繁被使用的数据，这种直接访问内存数据的方式可以大幅提升数据处理的性能。在数据库专用服务器中，缓存池占用物理内存的比例可以达到80%。

为了提升数据按列读的访问效率，InnoDB对缓冲池进行分页式管理，其中每页包含多行记录，所有页是以链表的数据结构实现存储管理。缓存的淘汰策略使用变异的LRU算法，该策略能淘汰最近最少被使用的数据。

因此，使用缓冲池去管理热点访问的数据是MySQL调优中一个非常重要的手段。

2.1.1.1缓冲池LRU算法

InnoDB使用变异的LRU算法以链表的数据结构管理缓冲池，也就是，需要增加一个新页到缓冲池的时候，最近最少被使用的页将被淘汰出缓存池，新页将被增加到页链表的中间位置，中间位置插入策略把链表分成两个子链表：

• 头部子链表，保存最近被访问的新页列表
• 尾部子链表，保存最近很少被访问的旧页列表

如下图所示：

图2 缓冲池链表数据结构图

从上图可知，LRU算法在新子链表（New Sublist）中保存频繁被使用的页，在旧子链表（Old Sublist）中保存非频繁被使用的页，这些很少被使用页将会逐渐被LRU算法淘汰出缓冲池。

默认地，LRU算法以下列方式运作：

• 3/8的缓冲池空间被分配给旧子链表，5/8的缓冲池空间被分配给新子链表
• 缓冲池链表的中间位置（Midpoint）是新子链表的尾部与旧子链表的头部相连接的地方
• 当InnoDB从磁盘重读取一页数据到缓冲池时，一开始时插入到旧子链表的头部位置（Midpoint）。用户进行SQL查询时，InnoDB从缓存中读取并返回页数据，有些数据页是预读取到缓冲池的，其目的是提高SQL查询性能。
• 如果在旧子链表的数据页被用户SQL查询读取，则该数据页会立刻被插入到新子链表的头部，如果是预读取的数据页，则不会被立刻更新到新子链表中。但是，如果预读取数据页自始至终都没有被读取，则最终都会被淘汰出缓冲池。如上图所示，右边的垂直向上的直线（Accessed pages），其表示的意义是，被读取访问的数据会被插入到新子链表的头部，是更新趋势。
• 随着数据库持续地被SQL查询操作，旧子链表与新子链表中的被读取的数据页都会被增加到新子链表的头部，旧子链表的被读取的数据页也会被增加到中间位置（Midpoint）。如上图所示，右边的垂直向下的直线（Unused pages），其表示的意义是，最终未被读取的数据页都会被挤到旧子链表尾部，并最终被淘汰出缓冲池，是淘汰趋势。

默认地，查询读取的数据页会立刻被移动到新子链表中，也就是这些数据页保存在缓冲池中的时间更长些。例如，mysqldump操作、无where查询条件的查询操作会从磁盘中读取大量的数据保存到缓冲池中，与此同时，同等空间大小的数据页会被淘汰出缓冲池。同理，当预读取的数据页被读取，则会立刻被移动到新子链表的头部，在这种情况下也引起大量的数据页被淘汰出缓冲池。

由以上的分析可知，InnoDB使用LRU算法将不常用的数据页淘汰出缓冲池。

2.1.1.2缓冲池配置

MySQL提供可配置参数，用户能根据实际环境的情况修改配置参数，从而获得更好的性能，从以下几个方面描述可配置参数给用户带来的优势：

• 应用实际上需要多大内存空间，则配置多大缓冲池，理论上，缓冲池分配的内存空间越大，InnoDB越接近一个内存数据库。也就是，只要内存空间足够大，则加载到缓冲池的数据页永远不会被淘汰。
• 在64位操作系统中，用户能对缓冲池分区，该性质可以减少在并发环境中资源竞争带来的性能损耗。
• 用户能指定频繁被访问的数据不被淘汰出缓冲池，即使大量的数据被加载到缓冲池中。
• 用户能指定什么时候以什么方式加载预读取的数据到缓冲池。
• 用户能控制什么时候刷数据（已经被更新的脏数据），以及被刷数据量的比率。
• 用户能指定故障恢复重新加载数据页到缓冲池的策略。

2.1.1.3监控缓冲池

MySQL提供标准化的监控措施对缓冲池的运行数据实行监控与分析，用户可以使用命令SHOW ENGINE INNODB STATUS实时查看其统计分析数据，执行该命令输出如下所示：

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 2198863872

Dictionary memory allocated 776332

Buffer pool size   131072

Free buffers       124908

Database pages     5720

Old database pages 2071

Modified db pages  910

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 4, not young 0

0.10 youngs/s, 0.00 non-youngs/s

Pages read 197, created 5523, written 5060

0.00 reads/s, 190.89 creates/s, 244.94 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not

0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read

ahead 0.00/s

LRU len: 5720, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

以下表格对统计分析的各项输出属性实行分析与说明：

项目名称	描述说明
Total memory allocated	缓冲池已申请的总内存空间，单位：字节
Dictionary memory allocated	InnoDB数据字典已申请的总内存空间，单位：字节
Buffer pool size	缓冲池总页数
Free buffers	缓冲池空闲链表的总页数
Database pages	缓冲池LRU链表的总页数
Old database pages	缓冲池LRU旧子链表的总页数
Modified db pages	缓冲池当前已被更新的总页数
Pending reads	等待被读入缓冲池的总页数
Pending writes LRU	等待从缓冲池链表尾部写的总脏页数
Pending writes flush list	执行检查点时等待刷新写的总页数
Pending writes single page	单页等待写的次数
Pages made young	旧子链表中已被移动到新子链表头部的总页数
Pages made not young	旧子链表中未被移动到新子链表的总页数
youngs/s	平均每秒被移动到新子链表头部的总页数
non-youngs/s	平均每秒未被移动到新子链表的总页数
Pages read	从缓冲池中读取的总页数
Pages created	已在缓冲池中创建的总页数
Pages written	从缓冲池中写入的总页数
reads/s	平均每秒从缓冲池中读取的总页数
creates/s	平均每秒在缓冲池中创建的总页数
writes/s	平均每秒从缓冲池中写入的总页数
Buffer pool hit rate	从缓冲池中读取页数与从磁盘中读取页数的命中率
young-making rate	页读取时需要立刻移动到新子链表头部的平均命中率
not (young-making rate)	页读取时不需要立刻移动到新子链表头部的平均命中率
Pages read ahead	平均每秒预读取总页数
Pages evicted without access	平均每次从缓冲池淘汰的总页数
Random read ahead	平均每秒随机预读取总页数
LRU len	LRU链表的总长度
unzip_LRU len	未解压缩的LRU链表的总长度
I/O sum	LRU链表中页数据被访问的总次数
I/O cur	在当前的时间间隔内LRU链表中页数据被访问的总次数
I/O unzip sum	未解压缩LRU链表中页数据被访问的总次数
I/O unzip cur	在当前的时间间隔内未解压缩LRU链表中页数据被访问的总次数

表2 InnoDB缓冲池统计项目

对以上表格的补充说明：

• youngs/s项目的统计信息是对旧子链表的，是基于数据页被访问的次数，包括一个数据页被多次访问的次数。如果youngs/s项目的统计数非常小，则说明没有发生大范围的表扫描。
• non-youngs/s项目的统计信息是对旧子链表的，是基于数据页被访问的次数，包括一个数据页被多次访问的次数。如果non-youngs/s项目的统计数非常小，则说明有发生大范围的表扫描，同时，youngs/s项目的统计数变得非常大。
• young-making项目的统计信息是对LRU链表的，是基于新子链表与旧子链表数据页被访问的次数的总和。但是在新子链表中，只移动离链表头部较远距离的数据页。
• not (young-making rate)项目的统计信息是对LRU链表的，基于不需要移动到新子链表头部的数据页的访问次数。

(未完待续)