mysql insert buffer_详解MySQL存储引擎Innodb

设置存储引擎SQL语句

查看MySQL目前支持的存储引擎列表

show engines

创建表时指定存储引擎。

create table 表名 engine = innodb;

修改现有表的存储引擎

alter table 表名 engine = innodb;

目录

存储引擎

MySQL 5.5以上版本默认存储引擎，MySQL推荐使用的存储引擎。支持事务、行级锁定、外键约束。事务安全的存储引擎。更加注重数据的完整性和安全性。

存储格式

数据和索引存储在同一个表空间文件中。

create table student(
id int(10),
name varchar(10)
)
engine=INNODB

db.opt存储了数据库配置信息，例如数据库字符集，编码格式等。 .frm是表结构文件，只存储了表结构和元数据（meta），包括表结构定义信息等，无论使用哪种表引擎，都会有一个frm文件。 .ibd是表索引文件，包含单个表的数据和索引内容。

如果在表中插入新的数据，那么在mysql的数据目录下就会生成一个文件，这个文件里面存储了所有表的数据。

insert into student(id,name)VALUES('1','张三')

要了解表空间，请访问：

系统架构

存储引擎有多个内存块，组成一个大的内存池，后台线程主要负责刷新内存池里的数据，将修改后的数据刷新到磁盘等等。

1. 后台线程

：主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括刷新脏页，合并 ()，回收UNDO页等。

IO：存储引擎中广泛使用AIO（Async IO）来处理写IO请求，可以大大提高数据库的性能，IO主要的工作就是处理这些IO请求的回调。

Purge：事务提交后，其所使用的数据可能不再需要，因此需要回收已经使用和分配的undo页。（在1.1版本之前，purge操作仅在存储引擎中完成，从1.1版本开始，purge操作可以在单独的线程中独立执行，以减少工作量，从而提高CPU利用率，提高存储引擎性能。）

Page：Page是在1.2.x版本引入的，它的作用是将以前版本中的脏页刷新操作放到一个单独的线程中，其目的是为了减少用户查询线程的原有工作和堵塞，进一步提高存储引擎的性能。

2.记忆

缓冲池

存储引擎基于磁盘存储，以页的形式管理其中的记录。因此，可以看作是基于磁盘的数据库系统。在数据库系统中，由于CPU速度与磁盘速度的差距，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

简单来说，缓冲池就是内存中的一块区域，当从数据库读取页面时，先把从磁盘读出的页面存放到缓冲池中，这个过程叫做将页面固定在缓冲池中。下次再读取同一个页面时，先判断该页面是否在缓冲池中，如果在缓冲池中，则称该页面在缓冲池中命中，直接读取该页面，否则就读取磁盘上的页面。对于修改操作，先修改缓冲池中的页面，然后以一定的频率刷新到磁盘。这里要注意的是，将页面从缓冲池刷新回磁盘的操作并不是每次更新页面时都触发的，而是通过一种叫做的机制刷新回磁盘。同样，这也是为了提高数据库整体的性能。

具体来说，缓冲池中缓存的数据页类型包括索引页、数据页、undo 页、插入缓冲区（）、自适应哈希索引（hash index）、存储的锁信息（）、数据字典信息（data）等。不能简单认为缓冲池只缓存了索引页和数据页，这两部分只占据了缓冲池很大一部分空间。从1.0.x版本开始，允许存在多个缓冲池实例，每个页面根据hash值均匀分布到不同的缓冲池实例上，这样做的好处是减少数据库内部的资源竞争，提高数据库的并发处理能力。

重做日志缓冲区

存储引擎首先把重做日志信息放入这个缓冲区，然后按照一定的频率刷新到重做日志文件中。重做日志缓冲区不需要设置的很大，因为重做日志缓冲区一般每秒刷新一次到日志文件中，所以用户只要保证每秒产生的事务量在这个缓冲区大小之内就可以了。默认是8MB。

正常情况下，8MB 的重做日志缓冲池对于大多数应用程序来说已经足够了，因为重做日志会在以下三种情况下将重做日志缓冲区的内容刷新到外部磁盘上的重做日志文件中。

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

附加内存池

在存储引擎中，内存是通过一种叫做内存堆的方式来进行管理的，在为某些数据结构分配内存的时候，需要从额外的内存池中申请，当这个区域的内存不足的时候，就从缓冲池中申请。

比如分配了一个缓冲池（ ），但是缓冲池中的每一帧缓冲区（frame ）也都有一个对应的缓冲区控制对象（block），里面记录了一些LRU、lock、wait等信息，而这个对象的内存需要从额外的内存池中申请。因此在申请较大的缓冲池时，也应该考虑相应增大这个值。

主要特点 1. 插入缓冲区

插入聚集索引通常是顺序的，不需要随机读磁盘。但是插入非聚集索引叶子节点不是顺序的，需要离散的访问非聚集索引页，速度比较慢。对于插入或者更新非聚集索引，首先判断插入的非聚集索引页是否在缓存池中，如果在，则直接插入，如果不在，则先放到一个对象中，然后通过后台线程按照某种算法慢慢将缓存的记录合并刷新回辅助索引。插入缓冲将多次插入合并为一次操作，减少了离散的磁盘操作。

使用需满足两个条件：

从 1.0.x 开始引入，全部都是缓冲的。

2. 写两次

当数据库崩溃时，存储引擎可能正在将页面写入表，但只有部分页面被写入。例如，对于 16KB 的页面，只有前 4KB 被写入，然后发生崩溃。这种情况称为部分写入失败（页面写入）。

因此在使用重做日志恢复数据库的时候，需要有该页面的副本，当发生部分写失败时，先通过该页面的副本恢复该页面，然后再进行重做，于是这项技术就被实现了。

分为两部分，一部分在内存中，大小为2MB，另一部分是磁盘共享表空间的128个连续页，也是2MB。

当请求刷新缓冲池中的脏页时，将执行以下步骤：

通过函数两次将脏页复制到内存中，每次顺序写入1MB到共享表空间，调用fsync函数同步磁盘，避免缓冲写入带来的问题，保证数据刷新到共享表空间（顺序写入，开销低），以上脏页数据分别写入各个表空间文件（离散写入） 3.自适应哈希索引

Hash 是一种非常快速的查找方法，一般来说这种查找的时间复杂度是 o(1)，也就是说一次查找就可以找到数据。B+ 树的查找次数取决于 B+ 树的高度，在生产环境中，B+ 树的高度一般为 3 到 4 层，因此需要 3 到 4 次查询。

会监控表上每个索引页的查询执行情况，如果发现创建哈希索引可以提高速度，就会创建哈希索引，这个过程不需要用户干预。

4.异步IO

使用异步IO操作磁盘，避免同步IO造成的阻塞。也可以进行IO Merge操作，将多个IO操作合并为一个IO操作。

5. 刷新相邻页面

当刷新脏页时，会检查该页面所在区域的所有页面，如果是脏页，则一并刷新，这样就让 AIO 可以将多次 IO 写操作合并为一次 IO 操作。

请考虑以下两个问题：

是否可以写入不太脏的页面，但该页面很快就会变脏？SSD 具有高 IOPS，它们还需要此功能吗？

为此，存储引擎从1.2.x版本开始提供了参数rs来控制是否开启该功能，对于传统机械硬盘建议开启该功能，对于超高IOPS性能的固态硬盘建议将该参数设置为0，即关闭该功能。