Mysql存储InnoDB关键特性

2023年 7月 24日 72.1k 0

Mysql存储-InnoDB关键特性

一、插入缓冲(Insert Buffer)

1.1 Insert Buffer

Insert Buffer 和数据页一样,是物理页的一个组成部分。

通常在使用InnoDB的过程中,主键是表中一行数据的唯一标识,通常是按照主键递增的顺序插入的。所以,插入主键或者说聚集索引是顺序的,不需要磁盘的随机读取。页中的记录会随着主键的顺序逐个记录,这时候的速度还是很快的。

注意:如果插入的是随机数据,比如UUID,那么无论主键是否自增,都将导致数据的写入是随机的,并非连续的。

通常情况每张表会有除聚集索引以外的其他辅助索引,这时聚集索引的存放仍然是顺序的,但是在辅助索引的叶子节点存储的数据则是离散的,由于随机读取的操作而导致了写入性能的下降。

在某些特定情况下,非聚集索引的插入仍然可以保证是顺序的,例如订单表的下单时间字段。

为了提高非聚集索引的写入性能,InnoDB开创性的设计了Insert Buffer:对于非聚集索引的插入和更新操作,会先去查询缓冲池,看非聚集索引的页是否在缓冲池中,若存在,则直接写入;若不存在,则先存放到一个Insert Buffer对象中,然后以一定的频率使Inser Buffer和辅助索引叶子结点merge(合并)。这是通常是多个写入操作合并到一个操作中(在一个索引页中),这样就大大提高了非聚集索引插入的性能。

使用条件:

  • 索引是辅助索引
  • 索引不是唯一的,插入时,数据库不判断数据的唯一性。
  • 1.2 Change Buffer

    Insert Buffer的升级,对于Insert,Update,Delete都进行缓冲,他们分别是:Insert Buffer、Delete Buffer、Purge Buffer。

    仍然作用于非唯一的辅助索引。

    对数据Update分为两个步骤:

    1)将数据标为已删除。对应Delete Buffer。

    2)执行删除。对应Purge Buffer。

    通过以下命令控制:

    sql
    复制代码
    mysql> show variables like 'innodb_change_buffering';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_change_buffering | all   |
    +-------------------------+-------+
    1 row in set (0.00 sec)
    

    默认是all,表示启动所有。此外还有inserts,deletes,purges,changes,none;changes表示启用inserts和deletes;none表示都不启用。

    通过以下参数控制最大使用内存量:

    sql
    复制代码
    mysql> show variables like 'innodb_change_buffer_max_size';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | innodb_change_buffer_max_size | 25    |
    +-------------------------------+-------+
    1 row in set (0.00 sec)
    

    表示最多使用1/4的缓冲池空间,最大为50.

    使用以下命令可以查看change buffer的状态:

    sql
    复制代码
    mysql> show engine innodb statusG;
    ​
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    

    seg size:表示当前Inser Buffer的大小2*16k

    free list len:表示空闲列表的长度。

    size:表示已合并页数

    merged operations:表示每个change Buffer操作的次数

    insert:表示Insert Buffer

    delete mark:表示Delete Buffer

    delete:表示Purge Buffer

    discarded operations:表示当Change Buffer进行merge时,表进行了删除。此时无需将记录合并到辅助索引中了。

    1.3 Insert Buffer 的实现

    Insert Buffer的数据结构是一棵B+树。

    在mysql4.1版本之前,每张表都有一棵B+树,在其之后,全局只有一个用于Insert Buffer的B+树,负责对所有表的辅助索引进行insert buffer。

    这颗B+树存放在共享表空间当中,默认是ibdata1。因此,如果想通过独立表空间恢复数据,往往会导致check table失败。因为辅助索引的数据可能还在Inser Buffer中,即共享表空间中,在通过ibd文件恢复后,还需要进行repair table来重建表上的所有辅助索引。

    Insert Buffer的非叶子节点存储的是查询的key(search key),其构造如下:

    image.png

    searck key一共9个字节,space表示要待插入记录所在表的表空间id,每个表都有一个唯一的space id。marker一个字节,用来兼容老版本的Insert buffer。offset表示页所在偏移量,4个字节。

    当一个辅助索引要插入到页时,首先查询缓冲池,如果不在,那么存储引擎会构造一个search key,查询Inser buffer 这棵B+树,再将记录插入到这个树的叶子节点当中。

    1.4 Merge Insert Buffer

    哪些情况可能发生合并?如下所示:

  • 辅助索引页被读取到缓冲池时; 当辅助索引页被读取到缓冲池时,检查Insert Buffer Bitmap页,确认该辅助索引页是否有数据存放于Insert Buffer 的B+树中,有则将该数据插入到辅助索引页中。使原本多次操作通过一次合并插入到了辅助索引页中,提高了性能。
  • Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时; Insert Buffer Bitmap用来追踪辅助索引页在缓冲区中可用的空间。并且至少有1/32的空间。如果小于1/32,则进行合并。
  • Master Thread。 每一秒或10秒都进行合并。
  • 二、两次写(Double Write)

    Insert Buffer带给InnoDB的是性能上的提升,而Double Write带给InnoDB的则是数据页的可靠性。

    2.1 存在的原因

    当数据发生宕机的同时,数据正在写入,16k的页数据,可能只写入了4k数据,剩下的12k数据就丢失了。

    当然可以通过重做日志进行恢复,但是如果被写入的页已经被损坏的话,那么这部分数据就无法写入了。

    解决问题的方式是在重做日志进行重做之前,需要这个页的一个副本,当发生写入失效时,先通过这个副本恢复该页,之后在进行重做。这就是Double Write。

    2.2 Double Write体系架构

    image.png

    如上图所示:doublewrite分为两个部分,一部分是内存中的doublewrite buffer ,大小为2M,另一部分是磁盘上共享表空间中的连续128个页,分为两个区,大小也是2M。

    当缓冲池对脏页刷新时,先将脏页通过memcpy(内存拷贝)函数赋值到doublewrite buffer中,然后doublewrite buffer分两次,每次1M将数据写入磁盘共享表空间的doublewrite中,完成后迅速调用fsync函数同步磁盘。doublewrite页是连续的,效率快,开销小。

    通过下面的命令查看mysql的双写信息:

    sql
    复制代码
    mysql> show status like "%InnoDB_dblwr%" ;
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | Innodb_dblwr_pages_written | 22    |
    | Innodb_dblwr_writes        | 6     |
    +----------------------------+-------+
    2 rows in set (0.00 sec)
    ​
    

    Innodb_dblwr_pages_written:一共写入页数。 Innodb_dblwr_writes:实际写入页数。

    通过如下参数控制开启和关闭:

    sql
    复制代码
    mysql> show variables like 'innodb_doublewrite';
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | innodb_doublewrite | ON    |
    +--------------------+-------+
    1 row in set (0.01 sec)
    

    三、自适应哈希索引(Adaptive Hash Index)

    哈希是一种非常快的查找方法,在一般情况下,这种查找方式的时间复杂度为O(1),即一次就能定位到数据。而B+树的查找次数根据其高度而定。通常是3或4层,也就是需要3到4次的查询。

    InnoDB会监控表上的个索引页的查询。如果观察到建立哈希索引可以带来效率的提升,则会自动建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index,AHI)。AHI是通过缓冲池的B+树页建立,速度很快,不需要整张表建立哈希索引。InnoDB会根据访问的频率和模式来自动为某些热点页建立哈希索引。

    建立AHI的要求: 对这个也的连续访问必须是一样的。例如(a,b)这样的联合索引,其访问模式如下: where a = 1 where a = 1 and b = 1 如果以上两种查询交替进行,则不会对该页构造AHI。

    另外两种建立AHI的条件:

    1)同一访问模式访问100次
    2)访问次数 = 数据记录数 * 1/16

    AHI是数据库自优化的,不需要人为进行干预。 使用下面的方式可以查看AHI的数据:

    sql
    复制代码
    show engine innodb statusG;
    ​
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 276671, node heap has 27 buffer(s)
    41.07 hash searches/s, 13.31 non-hash searches/s
    

    每秒哈希查询41.07次,非哈希查询13.31次。

    AHI只能用于指定条件的查询,如等于,对于范围查找不能自动建立。non-hash就显示了不是哈希查询的次数。两个参数对比可以查看哈希查询的效率。

    使用如下参数可以控制AHI的状态,默认是开启的:

    sql
    复制代码
    mysql> show variables like 'innodb_adaptive_hash_index';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | innodb_adaptive_hash_index | ON    |
    +----------------------------+-------+
    1 row in set (0.00 sec)
    ​
    

    四、异步IO(Async IO)

    为了提高磁盘操作的性能问题,当前数据库都是通过异步IO(Async IO)的方式来操作磁盘,InnoDB也是如此。

    与AIO对应的是Sync IO,每进行一次IO,就要等待此次完成后才能进行下一次。当用户发起一条查询请求,这个请求可能需要查询多个索引页,则需要进行多次的IO,使用Sync IO的话,需要等待每一次IO执行后在执行下一次。当时用AIO时,用户在发起一次IO请求后即可以发出下一次的IO请求,待所有请求完成后,会对所有的IO进行合并。

    AIO的优势除了异步速度快,还有另外的优势就是其IO Merge(IO 合并),将多个IO合并为一个IO,可以提高性能。例如:如用户连续访问页的(space,pageNo)是(6,6),(6,7),(6,8),每个页的大小是16k,如果是Sync IO的话,需要串行发送三次IO请求。而AIO则会判断这三次的IO请求是否是连续的,如上面举例得知page_no是连续的,则AIO会发送一个从(6,6)开始的48k的页读取请求。

    使用下面的参数控制AIO的开启,默认是ON:

    sql
    复制代码
    mysql> show variables like 'innodb_use_native_aio';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_use_native_aio | ON    |
    +-----------------------+-------+
    1 row in set (0.01 sec)
    

    目前InnoDB使用的AIO是基于操作系统内核级别支持的,使用的话需要libaio的支持,当前windows和linux都是支持的,而mac则暂时不支持。

    五、刷新邻接页(Flush Neighbor Page)

    当刷新一个脏页时,InnoDB会检测该页所在区(extent,关于extent的内容看这篇文章blog.csdn.net/fu_zhongyua…)的所有页,如果是脏页,那么一起刷新到磁盘。这样做通过AIO将多个IO操作合并为1个IO。在传统的机械磁盘下有显著的效果。

    通过以下参数控制:

    sql
    复制代码
    mysql> show variables like 'innodb_flush_neighbors';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_flush_neighbors | 0     |
    +------------------------+-------+
    1 row in set (0.00 sec)
    

    传统机械硬盘建议开启,固态硬盘建议关闭,即值设为0;

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论