2019年3月

MySQL技术内幕(InnoDB存储引擎概述)学习笔记01-MySQL基本概念

MySQL组成部分

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

MySQL是单进程多线程

插件式存储引擎是MySQL的一大特点

InnoDB体系架构

  • 内存块
  • 后台线程

内存块

  • 维护所有进程/线程需要访问的多个内部数据结果
  • 缓存磁盘上的数据,方便快速的读取,同时对磁盘文件的数据修改之前在这里花村
  • 重做日志(redo log)缓冲

后台线程

作用

  • 刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。
  • 将已修改的数据文件刷新到磁盘文件
  • 保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态

不同的线程

InnoDB是多线程的模型,其后台有多个不同的线程,负责处理不同的任务

  • Master Thread
    是一个核心线程,主要将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收
  • IO Thread
    在InnoDB中大量使用了AIO(Async IO)来处理写请求,这样可以极大的提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。InnoDB 1.0版本之前共有4个IO Thread,分别是write,read,insert buffer和log IO Thread。在Linux平台下,IO Thread的数量不能进行调整,但是在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_write_io_threads参数进行设置。
  • Purge Thread
    事务被提交后,其所使用的undolog可能不再需要,因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前,purge操作仅在InnoDB存储引擎的Master Thread中完成。而从1.1版本开始,purge操作独立到单独的线程中进行,以此来减轻Master Thread的工作,从而提高CPU的使用率以及提升存储引擎的性能
  • Page Cleaner Thread
    在1.2.x版本引入。作用是将之前版本中脏页的刷新操作都放到单独的线程来完成。其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞,进一步提高InnoDB存储引擎的性能。

内存

  • 缓冲池

InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中,由于CPU速度与磁盘速度之间的鸿沟,基于磁盘的数据库系统通常使用缓冲池极速来提高数据库的整体性能。
缓冲池简单说就是一块内存区域,通过内存的速度来弥补磁盘速度相对较慢的速度。在数据库中进行读取页的操作,首先将从磁盘读取到的页放在缓冲池中,这个过程称为将页FIX在缓冲池中。下一次再读相同的页时,首先判断该页是否在缓冲池中。如在缓冲池中,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。
对于数据库中页的修改操作,则首先修改在缓冲池中的页,然后再以移动的频率刷新到磁盘上。这里需要注意的是,页从缓冲池刷新回磁盘的操作并不是在每次更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘。同样,这也是为了提高数据库的整体性能。
综上所述,缓冲池的大小直接影响着数据库的整体性能。由于32位操作系统的限制(最多将该值设置成3G,也可以通过操作系统的PAE选项来获得最大64GB的支持),建议数据库服务器采用64位的系统。

对于InnoDB而言,其缓冲池的配置通过参数innodb_buffer_pool_size来设置。

具体来看,缓冲池重的数据页类型有 : 索引页,数据页,undo页,插入缓冲(insert buffer),自适应哈希索引(adaptive hash index),InnoDB存储的锁信息(lock info),数据字典信息(data dictionary)等。不能简单的认为缓冲池只是缓存索引页和数据页,他们只是占缓冲池的很大一部分而已。

从InnoDB 1.0.x开始,允许有多个缓冲池实例。每个页根据hash值平均分配带不同缓冲池实例中,这样做的好处是减少数据库内存的资源竞争,增加数据库的并发处理能力。可以通过innodb_buffer_pool_instances来进行设置,该值默认为1。

在配置文件中将innodb_buffer_pool_instances来进行配置,该值默认为1。将该值设置为大于1就能得到多个缓冲池实例。再通过SHOW ENGINE INNODB STATUS可以查看

  • LRU List,Free List和Flush List

通常来说,数据库中的缓冲池是通过LRU(Last Recent Used,最近最少使用)算法来进行管理的。即最频繁使用的在LRU列表前面,最少使用的在LRU列表的尾端。当缓冲池不能存放新读取到的页的时候,将首先释放LRU列表尾端的页。
InnoDB中对传统的LRU算法做了优化。在InnoDB中加入了midpoint位置。新读取到的页,虽然是最新访问的页,但是不是直接放到LRU列表的首部,而是加入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下成为midpoint insertion strategy。默认配置下,该位置在列表长度的5/8处。midpoint可由参数innodb_old_blocks_pct控制。

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

为了解决这个问题,InnoDB存储引擎引入了另一个参数来进一步管理LRU列表,这个参数是innodb_old_block_time,用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。因此当需要执行上说所说的SQL操作时,可以通过下面的方法尽可能使LRU列表中的热点数据不被刷出。

set global innodb_old_blocks_time = 1000;
# data or index scan operation
set global innodb_old_blocks_time = 0;

如果预估到活跃的热点数据不止63%,那么在执行SQL语句之前,还可以通过下面的语句来减少热点页可能被刷出的概率。

set global innodb_old_blocks_pct = 20;

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列表的使用情况及运行状态。注意这个命令显示的不是当前的状态,而是过去的某个时间范围内的InnoDB存储引擎的状态,具体的时间范围可以在命令执行后的结果中看到。

InnoDB从1.0版本开始支持压缩叶的功能,即将原本16KB的页压缩为1KB,2KB,4KB和8KB。而由于页的大小发生了变化,LRU列表也有了些许改变。对于非16KB的页,是通过unzip_LRU列表进行管理的。通过命令SHOW ENGINE INNODB STATUS可以观察到如下内容

...
LRU len: 266, unzip_LRU len: 0
...

可以看到LRU列表一共有266个页,而unzip_LRU列表中有0个页。这里需要注意的是,LRU中包含了unzip_LRU列表中的页。

对于压缩页的表,每个表的压缩比率可能各有不同。可能存在有的表页大小为8KB,有表页大小为2KB的情况。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列表中

同样可以使用infomation_schema架构下的表 INNODB_BUFFER_PAGE_LRU 来观察unzip_LRU列表中的页

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE FROM INNODB_BUFFER_PAGE_LRU WHERE COMPRESSED_SIZE <> 0;

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

同LRU列表一样,Flush列表也可以通过命令SHOW ENGINE INNODB STATUS来查看,Modify db pages显示的就是脏页的数量。information_schema中可以用下面的语句查看

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION > 0;
  • 重做日志缓冲

InnoDB存储引擎会首先将重做日志信息放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件。重做日志缓冲一版不需要设置的很大,因为一般情况下每一秒钟会将重做日志刷新到日志文件,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制,默认为8MB

在通常情况下,8MB的重做日志缓冲池足以满足绝大部分的应用,因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

  1. Master Thread每一秒将重做日志刷新到重做日志文件
  2. 每个事务提交时会将重做日志刷新到重做日志文件
  3. 当重做日志缓冲池空间小于1/2时,重做日志缓冲刷新到重做日志文件
  • 额外的内存池

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

Checkpoint

Checkpoint(检查点)技术的目的主要是解决以下几个问题

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

在InnoDB存储引擎中,Checkpoint发生的时间,条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷新到磁盘。不同之处在于每次刷新多少页到磁盘,每次从哪里取脏页,以及什么时间出发Checkpoint。在InnoDB存储引擎内部,有两种Checkpoint,分别为

  • Sharp Checkpoint
  • Fuzzy Checkpoint

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

但是数据库如果再运行时也使用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个空闲页可供使用。在InnoDB 1.0.x版本之前,需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中,显然这会阻塞用户的查询操作。倘若没有100个可用空闲页,那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页,那么需要进行Checkpoint,而这些页是来自LRU列表的,因此成为FLUSH_LRU_LIST Checkpoint。

而从MySQL 5.6版本,也就是InnoDB1.2.x版本开始,这个检查被放在了一个单独的Page Cleaner线程中进行,并且用户可以通过参数innodb_lru_scan_depth控制LRU列中可用页的数量,该值默认为1024。

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

Dirty page too much即脏页数量太多。导致InnoDB存储引擎强制进行Checkpoint。其目的总得来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制。
innodb_max_dirty_pages_pct的值为百分比,如果是90则代表当缓冲池脏页数量超过90%时,强制进行Checkpoint,刷新一部分脏页到磁盘。

InnoDB关键特性

  • 插入缓冲(Insert Buffer)
  • 两次写(Double Write)
  • 自适应哈希索引(Adaptive Hash Index)
  • 异步IO(Async IO)
  • 刷新邻接页(Flush Neighbor Page)

插入缓冲

Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池的一个组成部分。其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分。

在InnoDB存储引擎中,主键是行的唯一标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚集索引(Primary Key)一般是顺序的,不需要磁盘的随机读取。比如按照下列SQL定义表:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a)
)

其中a列是自增长的,若对a列插入NULL值,则由于其具有AUTO_INCREMENT属性,其值会自动增长。同时页中的行记录按照a的值进行顺序存放。在一般情况下,不需要随机读取另一个页中的记录。因此对于这类插入操作,速度是非常快的。

注意并不是所有的主键插入都是顺序的。若主键是类似UUID这样的,那么插入和辅助索引一样,同样是随机的。即使主键是自增类型,但是插入的是指定的值,而不是NULL值,那么同样可能导致插入并非连续的情况。

但是不可能每张表上只有一个聚集索引,更多的情况下,一张表上有多个非聚集的辅助索引(secondary index)。比如,用户需要按照b这个字段进行查找,并且b这个字段不是唯一的,即表是按如下的SQL语句定义的:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a),
KEY(b)
)

在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时,数据页的存放还是按主键a进行顺序存放的,但是对于非聚集索引叶子节点的插入不再是顺序的了,这时就需要离散的访问非聚集索引页,由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个b字段上索引的错误,而是因为B+树的特性决定了非聚集索引插入的离散型。

需要注意的是,在某些情况下,辅助索引的插入依然是顺序的,或者说是比较顺序的,比如用户购买表中的时间字段。在通常情况下,用户购买时间是一个辅助索引,用来根据时间进行查询。但是在插入时却是根据时间的递增而插入的,因此插入也是较为顺序的。

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

然后Insert Buffer的使用需要同时满足两个条件

  • 索引是辅助索引(secondary index)
  • 索引不是唯一的(unique)

当满足以上两个条件时,InnoDB会使用Insert Buffer,这样就能提高插入操作的性能了,不过考虑到这样一种情况:应用程序进行大量的插入操作,这些都涉及了不唯一的非聚集索引,也就是使用了Insert Buffer。若此时MySQL数据库发生了宕机,这时势必有大量的Insert Buffer并没有合并到实际的非聚集索引中去。因此这时恢复可能需要花很长的时间,在极端条件下甚至需要几个小时。

辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生,从而导致Insert Buffer失去了意义。

目前Insert Buffer存在一个问题 :在写密集的情况下,插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool),默认最大可以占用到1/2的缓冲池内存。

这对于其他的操作可能会带来一定的影响。

Change Buffer

InnoDB从1.0.x版本开始引入了Change Buffer,可将其视为Insert Buffer的升级。从这个版本开始,InnoDB存储引擎可以对DML操作--INSERT,DELETE,UPDATE都进行缓冲,他们分别是:Insert Buffer,Delete Buffer,Purge Buffer。

当然和之前Insert Buffer一样,Change Buffer使用的对象依然是非唯一的辅助索引。
对一条记录进行UPDATE操作可能分为两个过程

  • 将记录标记为已删除。
  • 真正将记录删除。

因此Delete Buffer对应UPDATE操作的第一个过程,即将记录标记为已删除。Purge Buffer对应UPDATE操作的第二个过程,即将记录真正的删除。同时,InnoDB存储引擎提供了参数innodb_change_buffering,用来开启各种Buffer的选项。该参数可选的值为 :inserts,deletes,purges,changes,all,none。inserts,deletes,purges就是前面讨论过的三种情况。changes表示启用inserts和deletes,all表示启用所有,none表示都不启用。该参数默认值为all。

从InnoDB 1.2.x版本开始,可以通过参数innodb_change_buffer_max_size来控制Change Buffer最大内存使用量

mysql> show variables like "%innodb_change_buffer_max_size%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

该值默认为25,表示最多使用1/4的缓冲池内存空间。需要注意的是改值的最大有效值为50.

...
Ibuf: size 1, free list len 0, seg size 2, 470 merges
merged operations:
 insert 585, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
...

seg size :显示了当前Change Buffer的大小为1 * 16KB = 16KB
free list len :代表了空闲页的数量
size : 代表已经合并记录页的数量。seg size - (1 + free list len)。原文如下 :The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
merges :代表合并的次数,也就是实际读取页的次数
merged operations - insert: 表示操作Insert Buffer的次数(插入的次数)
merged operations - delete mark: 表示操作Delete Buffer的次数
merged operations - delete: 表示操作Purge Buffer的次数;
discarded operations :表示当Change Buffer发生merge时,表已经被删除,此时就无需再将记录合并到辅助索引中了。

merged operations - insert/merges = 585/470 与等于 1.3 / 1
从这个值就可以看出性能的提升

Insert Buffer的内部实现http://jinblog.com/admin/write-post.php?cid=905#wmd-preview

Insert Buffer的使用场景,即非唯一辅助索引的插入操作。但是对于Insert Buffer具体是什么,以及内部是怎么实现可能依然模糊。

Insert Buffer的数据结构是一颗B+树。在MySQL 4.1之前的版本中每张表都有一颗Insert Buffer B+树。而在现今的版本中,全局只有一颗Insert Buffer B+树,负责对所有表的辅助索引进行Insert Buffer。而这棵B+树存放在共享表空间中,默认也就是ibdata1中。因此,试图通过独立表空间ibd文件恢复表中数据时,往往会导致CHECK TABLE失败。这是因为ibd文件进行恢复后,还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。

Insert Buffer是一颗B+树,因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的search key(键值)

当一个辅助索引要插入到页时,如果这个页不在缓冲池中,那么InnoDB引起会构造一个search key,接下来查询Insert Buffer这棵B+树,然后将这条记录插入到Insert Buffer B+树的页子节点中。

Merge Insert Buffer

Merge Insert Buffer的操作可能发生在以下几种情况

  • 辅助索引页被读取到缓冲池时
  • Insert Buffer Bitmap页追踪到改辅助索引已无可用空间时
  • Master Thread

两次写

如果写失效,可以通过重做日志进行恢复。但是需要认识到,重做日志中记录的是对页的物理操作,如偏移量800,写“aaa”记录。如果这个页本身就已经损坏了,再对其进行重做是没有意义的。这就是说,在应用重做日志前,需要一个页的副本,放写入失效时,先通过页的副本还原该页,再进行重做,这就是doublewrite。

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

可以用show global status like "innodb_dblwr%";观察到doublewrite的运行情况,如果Innodb_dblwr_pages_written(doublewrite一共写的次数):Innodb_dblwr_writes(实际写入的次数)远小于64:1,那么可以说明系统写入压力并不是很高。

如果操作系统在将页写入到磁盘的过程中发生了崩溃,在恢复过程中,InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本,将其复制到表空间文件,再应用重做日志。

有些文件系统本身就提供了部分写失效的防范机制,在这种情况下,可以使用skip_innodb_doublewrite禁用doublewrite功能以提高性能。

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来,因此构建速度很快,而且不需要对整张表建立索引。

AHI有一个要求,即对这个页的连续访问模式必须是一样的。例如对于(a,b)这样的联合索引页,其访问模式可以是以下情况

  • where a=xxx
  • where a=xxx and b=xxx

访问模式一样是指的查询的条件是一样的,若交替进行上述两种查询,那么InnoDB存储引擎不会对该页构造AHI,此外还有如下要求

  • 以该模式访问了100次
  • 页通过该模式访问了N次,其中N=页中记录*1/16

根据InnoDB存储引擎官方的文档显示,启用AHI后,读取和写入速度可以提高2倍,辅助索引的链接操作性能可以提升5倍。毫无疑问,AHI是非常好的优化模式,其设计思想是数据库自优化(self-runing),则无需DBA对数据库进行人为调整。

可以使用show engine innodb status;查看哈希索引的使用情况

show engine innodb status;

......

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 125156, seg size 125158, 122194 merges
merged operations:
 insert 367572, delete mark 25630980, delete 1001186
discarded operations:
 insert 9209, delete mark 0, delete 0
0.00 hash searches/s, 0.56 non-hash searches/s
---
LOG
---
...

可以使用innodb_adaptive_hash_index来禁用或启用此特性,默认为开启

异步IO

异步IO的优势

  1. 提高数据库的性能
  2. 可以进行IO Merge操作

异步IO需要系统支持才能开启,可以用innodb_use_native_aio开控制。

官方的测试,开启Native IO,恢复速度可以提升75%

在InnoDB存储引擎中,read ahead方式的读取都是通过AIO完成,脏页的刷新,即磁盘的写入操作则全部由AIO完成。

刷新邻接页

InnoDB提供了Flush Neigbor Page(刷新邻接页特性)。其工作的原理为:在刷新一个脏页时,InnoDB存储引擎会检测该页所在区(entent)的所有页,如果是脏页,那么一起刷新。这样做可以通过AIO将多个IO写入操作合并为一个IO操作。改工作机制在传统的机械磁盘下有显著优势。但是需要考虑两个问题

  • 是不是可能将不怎么脏的页进行了写入,则该页之后又会很快变成脏页?
  • 固态硬盘有较高的IOPS,是否还需要这个特性?

为此。InnoDB在1.2.x版本之后提供了innodb_flush_neighbors,用来控制是否启用该特性,对于固态硬盘有着超高IOPS的磁盘,可以将该参数设置为0,即关闭此特性。

启动,关闭与恢复

可以使用innodb_fast_shutdown控制MySQL数据库关闭时的行为(刷新脏页和merge数据到磁盘),innodb_force_recovery控制启动时的行为(启动时有数据需要恢复的情况下,恢复的行为)