Mysql几种存储引擎比较，数据库缓冲，事务语法，约束，视图，分区表，InnoDB关键特性

干干干就完了

已于 2022-02-26 15:32:37 修改

阅读量282

点赞数

分类专栏：数据库文章标签： mysql

于 2021-05-06 11:42:24 首次发布

原文链接：https://mp.csdn.net/console/home?spm=1001.2014.3001.4503

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MySQL存储引擎

存储引擎是基于表的，而不是数据库。

InnoDB存储引擎

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特定是行锁设计，支持外键，并支持类似Oracle的非锁定读，即默认读取操作不会产生锁。

Innodb存储引擎是默认的存储引擎。

InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了4种隔离级别，默认为REPEATABLE READ级别。同时，使用一种被称为next-key lock的策略来避免幻读现象的产生。除此之外，innodb存储引擎还提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能。

MyISAM存储引擎

MyISAM存储引擎不支持事务，支持表锁设计，支持全文索引，主要面向一些OLAP（联机分析）数据库应用。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件，这点和大多数的数据库都非常不同。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件，

NDB存储引擎

NDB存储引擎是一个集群存储引擎。

NDB的特点是数据全部放在内存中，因此主键查找的速度很快，并且通过添加NDB数据存储节点可以线性地提高数据库性能，是高可用，高性能的集群系统。

Memory存储引擎

Memory存储引擎将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的维度表。

Memory存储引擎默认适用哈希索引，而不是我们熟悉的B+树索引。

虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如，只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段varchar时是按照定长字段char的方式进行的，因此会浪费内存。

Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作。

Archive存储引擎非常适合存储归档数据，如日志信息。

Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供告诉的插入和压缩功能。

数据库缓冲

InnoDB存储引擎在处理客户端的请求时，如果需要访问某个页的数据，就会把完整的页中的数据全部加载到内存中。也就是说，即使只需要访问一个页的一条记录，也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，而且在读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省下磁盘I/O的开销了。

缓存池Buffer Pool

Buffer Pool（缓冲池）是为了缓存磁盘中的页，是mysql服务器向操作系统申请的一片连续的内存。

Buffer Pool对应的一片连续的内存被划分为若干个页面，页面大小与InnoDB表空间使用的页面大小一致，默认都是16KB。

每个缓冲页对应的控制信息占用的内存大小是相同的，我们把每个页对应的控制信息占用的一块内存称为一个控制块。控制块和缓冲页是一一对应的，它们都存放在Buffer Pool中。

控制块中主要包含该页所属的表空间编号，页号，缓冲页在Buffer Pool中的地址，链表节点信息等。

free链表和flush链表

当最初启动Mysql服务器的时候，需要完成Buffer Pool的初始化过程，就是先向操作系统申请Buffer Pool的内存空间，然后把它划分成若干对控制块和缓冲页。

我们把所有空闲的缓冲页对应的控制块作为一个节点放到一个链表中，这个链表称为free链表。

拥有了free链表之后，每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取出一个空闲的缓冲页，并把该缓冲页对应的控制块的信息填上，然后把该缓冲页对应的free链表节点从链表中移除，表示该缓冲页已经被使用。

如果我们修改了Buffer Pool中摸一个缓冲页的数据，这样的缓冲页称为脏页。

创建一个链表，凡是被修改过的缓冲页对应的控制块都会作为一个节点加入这个链表中，这个链表称为flush链表。

划分区域的LRU链表

情况1，InnoDB提供了预读的服务。预读就是InnoDB认为执行当前的请求时，可能会在后面读取某些页面，于是就预先把这些页面加载到Buffer pool中。
- 这些预读的页会放到LRU链表的头部，但是，如果此时Buffer Pool的容量不大，而且很多预读的页面都没有用到的话，就会导致处于LRU链表尾部的一些缓冲页很快被淘汰掉，大大降低了Buffer Pool命中率。
情况2，有的程序员可能会写一些需要进行全表扫描的语句

可能降低Buffer Pool命中率的两种情况：

加载到Buffer Pool中的页不一定被用到
如果有非常多的使用频率偏低的页被同时加载到了Buffer Pool中，则可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉

因为这两种情况的存在，InnoDB将LRU链表按照一定比例分成两截。

一部分存储使用频率非常高的缓冲页，这一部分链表也称为热数据，或者称为Young区域
另一部分存储使用频率不是很高的缓冲液；这一部分链表称为冷数据，或者称为old区域

针对前面提到的两种可能降低Buffer Pool命中率的情况进行优化

针对预读的页面可能不进行后续访问的优化
- 当磁盘上的某个页面在初次加载到Buffer Pool中的某个存储页时，该缓冲页对应的控制块会放到old区域的头部，这样一来，预读到Buffer Pool却不进行后续访问的页面就会被逐渐地从old区域逐出，不会影响young区域中使用比较频繁的缓冲页。
针对全表扫描，短时间内访问大量使用频率非常低的的页面的优化
- 在对某个处于old区域的缓冲页进行第一次访问时，就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会从old区域移动到young区域的头部，否则将它移动到young区域的头部。

Mysql中事务的语法

可以使用下面两种语句来开启一个事务

BEGIN[WORK];

BEGIN语句代表开启一个事务，后面的WORK可有可无。开启事务后，就可以继续写若干条语句，这些语句都属于刚刚开启的这个事务。

BEGIN;
加入事务的语句...

START TRANSACTION;

START TRANSACTION语句与BEGIN语句有相同的功效，都标志着开启一个事务。

STRAT TRANSACTION;
加入事务的语句...

相比于BEGIN语句，START TRANSACTION语句后面可以跟随几个修饰符。

READ ONLY：标识当前事务是一个只读事务，也就是说属于该事务的数据库操作只能读取数据，而不能修改数据。
READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
WITH CONSISTENT SNAPSHOT：启动一致性读

如果不显式指定事务的访问模式，那么该事务的访问模式就是读写模式。

提交事务

开启事务之后就可以继续编写需要放到该事务中的语句了。当最后一条语句写完之后就可以提交该事务了。

COMMIT[WORK];

COMMIT语句就代表提交一个事务，后面的WROK可有可无。

手动中止事务

如果写了几条语句之后发现前面某条语句写错了，可以手动使用ROLLBACK语句将数据库恢复到事务执行之前的状态。

ROLLBACK [WORK];

ROLLBACK语句代表回滚一个事务，后面的WORK可有可无。

ROLLBACK语句是程序员在手动回滚事务时使用的。如果事务在执行过程中遇到了某些错误而无法继续执行的话，大部分情况下会回滚失败的语句，在某些情况下会回滚整个事务，比方说发生了死锁的情况下会回滚整个事务。

自动提交

MySQL有一个系统变量autocommit，用来自动提交事务。

autocommit默认值为ON，就是在默认情况下，如果不显式地使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这叫做事务的自动提交。

如果想关闭这种自动提交的功能，有两种方法

显式地使用START TRANSACTION或者BEGIN语句开启一个事务
把系统变量autocommit的值设置为OFF，SET autocommit = OFF;

隐式提交

如果输入了某些语句，且这些语句会导致之前的事务悄悄地提交掉（就像输入COMMIT语句一样），因为某些特殊语句而导致事务提交的情况称为隐式提交。

定义或修改数据库对象的数据定义语言（DDL）

所谓的数据库对象，指的是数据库，表，视图，存储过程等这些东西。当使用CREATE，ALTER，DROP等语句修改这些数据库对象时，就会隐式地提交前面语句所属的事务。

隐式使用或修改Mysql数据库中的表

在使用ALTER USER,CREATE USER,DROP USER,GRANT,RENAME USER,REVOKE,SET PASSWORD等语句时，也会隐式提交前面语句所属的事务。

事务控制或关于锁定的语句

当我们在一个事务还没有提交或者还没有回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务，此时会隐式提交上一个事务

加载数据的语句
关于Mysql复制的语句

保存点

定义保存点的语法如下

SAVEPOINT 保存点名称;

当想回到某个保存点时，可以使用以下语句

ROLLBACK TO 保存点名称;

如果想删除某个保存点，可以使用以下语句

RELEASE SAVEPOINT 保存点名称;

约束

数据完整性

数据完整性有以下三种形式：

实体完整性保证表中有一个主键。在InnoDB存储引擎表中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。用户可以定义一个触发器来保证数据完整性。

域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证

选择合适的数据类型确保一个数据值满足特定条件
外键（Foreign Key）约束
编写触发器
还可以考虑用DEFAULT约束作为强制域完整性的一个方面

参照完整性保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性。也可以通过编写触发器以强制执行。

对于InnoDB存储引擎本身而言，提供了以下几种约束

Primary Key
Unique Key
Foreign Key
Default
NOT NULL

约束的创建和查找

约束的创建可以采用以下两种方式：

表建立时就进行约束定义
利用ALTER TABLE命令来进行创建约束

对Unique Key（唯一索引）的约束，用户还可以通过命令CREATE UNIQUE INDEX来建立。对于主键约束而言，其默认约束名为PRIMARY。而对于Unique Key约束而言，默认约束名和列名一样，当然也可以人为指定Unique Key。

ENUM和SET约束

Mysql数据库不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个型别类型，规定域的范围只能是male和female，在这种情况下，用户可以通过ENUM类型来进行约束。

CREATE TABLE a(
	id INT,
    sex ENUM('male', 'female')
);

如果想要实现CHECK约束，还需要配合设置参数sql_mode。

SET sql_mode = 'STRICT_TRANS_TABLES';

这样的约束只限于对离散数值的约束，对于传统的CHECK约束支持的连续纸的范围约束或更复杂的约束，ENUM和SET类型还是无能为力。这时用户需要通过触发器来实现对于值域的约束。

约束和索引的区别

当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

对错误数据的约束

在某些默认设置下，MySQL数据库允许非法的或者不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向NOT NULL的字段插入一个NULL值，MySQL数据库会将其更改为0在进行插入。因此数据库本身没有对数据的正确性进行约束。

触发器与约束

完整性约束通常可以使用触发器来实现。

触发器的作用是在执行INSERT,DELETE,UPDATE命令之前或之后自动调用SQL命令或存储过程。

创建触发器的命令是CREATE TRIGGER，只有具备super权限的Mysql数据库用户才可以执行这条命令。

CREATE [DEFINER = {user | CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tbl_name FOR EACH ROW trigger_stmt

最多可以为一个表建立6个触发器，即分别为INSERT,UPDATE,DELETE的BEFORE,AFTER各定义一个。BEFORE,AFTER代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。

通过触发器，用户可以实现Mysql数据库本身并不支持的一些特性，如对于传统CHECK约束的支持，物化视图，高级复制，审计等特性。

外键约束

外键用来保证参照完整性。Mysql数据的Myisam存储引擎本身不支持外键。对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。

用户可以在执行CREATE TABLE就添加外键，也可以在表创建后通过ALTER TABLE命令来添加。

CREATE TABLE parent(
	id INT NOT NULL,
    PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE child(
	id INT,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
)ENGINE=INNODB;

一般来说，称被引用的表为父表，引用的表称为子表。

外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE操作时，对子表所做的操作。

可定义的子表操作有：

CASCADE
SET NULL
NO ACTION
RESTRICT

CASCADE表示当父表发生DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE或UPDATE操作。SET NULL表示当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值，但是子表中相对应的列必须允许为NULL值。NO ACTION表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。RESTRICT表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。

如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

视图

在Mysql数据库中，视图（view）是一个命名的虚表，它由一个SQL查询来定义，可以当作表使用。与持久表不同的是，视图中的数据没有实际的物理存储。

视图的主要用途之一就是被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来取数据或者更新数据。

视图同时在一定程度上起到一个安全层的作用。

虽然视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基本表。一般称可以进行更新操作的视图为可更新视图。

视图定义中的WITH CHECK OPTION是针对于可更新视图的，即更新的值是否需要检查。

对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

分区表

分区功能并不是在存储引擎层完成的，因此不是只有innodb存储引擎支持分区，常见的存储引擎myisam，NDB等都支持。但也并不是所有的存储引擎都支持，如CSV，FEDORATED，MERRGE等就不支持。

分区的过程是将一个表或索引分解为多个更小，更可管理的部分。从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，mysql数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

大多数人会有这样的误区：只有启动了分区，数据库就会运行得更快。分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理。

当前Mysql数据库支持以下几种类型的分区

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。
LIST分区：类似RANGE分区类型，只是LIST分区面向的是离散的值
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
KEY分区：根据Mysql数据库提供的哈希函数进行分区

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一部分。

如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

RANGE分区

下面的CREATE TABLE语句创建了一个id列的区间分区表，当id小于10时，数据插入p0分区，当id大于10小于20时，数据插入p1分区。

CREATE TABLE t(
	id INT
)ENGINE=INNODB
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20)
);

对于表t，由于定义了分区，因此对于插入的值因该严格遵守分区的定义，当插入一个不在分区中定义的值时，Mysql数据库会抛出一个异常。

mysql> INSERT INTO t SELECT 30;
ERROR 1526 (HY000): Table has no partition for value 30

对于上述问题，可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值放入p2分区。

mysql> ALTER TABLE t
	-> ADD PARTITION(
    -> partition p2 values less than maxvalue);

mysql> INSERT INTO t SELECT 30;
Query OK, 1 row affected

RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年份来分区存放销售记录。

LIST分区

LIST分区和RANGE分区非常相似。只是分区的值是离散的，而非连续的。

CREATE TABLE t(
	a INT,
    b INT
)ENGINE=INNODB
PARTITION BY LIST(b)(
PARTITION p0 VALUES IN (1,3,5,7,9),
PARTITION P1 VALUES IN (0,2,4,6,8)
);

不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN。因为每个分区的值都是离散的，因此只能定义值。

如果插入的值不在分区的定义中，Mysql数据库同样会抛出异常。

在用INSERT插入多个行数据的过程中遇到分区未定义的值时，myisam和innodb存储引擎的处理完全不同。myisam引擎会将之前的行数据都插入，但是之后的数据不会被插入，而Innodb存储引擎将其视为一个事务，因此没有任何数据插入。

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，Mysql自动完成这些工作，用户需要做的只是将要进行哈希分区的列值指定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。

使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句，其中"expr"是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的列名。此外，用户很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

下面的例子创建了一个HASH分区的表t，分区按日期列b进行

CREATE TABLE t t_hash(
	a INT,
    b DATETIME
)ENGINE=InnoDB
PARTITION BY HASH(YEAR(b))
PARTITION 4;

如果插入一个列b为2010-04-01的记录到表t_hash中，那么保存该条记录的分区如下：

MOD(YEAR('2010-04-01'), 4)
=MOD(2010,4)
=2

因此记录会放入分区p2中。

mysql数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。

CREATE TABLE t_linear_hash(
	a INT,
    b DATETIME
)ENGINE=InnoDB
PARTITION BY LINEAR HASH (YEAR(b))
PARTITIONS 4;

LINEAR HASH分区的优点在于，增加，删除，合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用Mysql数据库提供的函数进行分区。

对于NDB Cluster引擎，Mysql数据库使用MD5函数来分区；对于其他存储引擎，Mysql数据库使用其内部的哈希函数。

COLUMNS分区

前面介绍的RANGE，LIST，HASH和KEY这四种分区中，分区的条件是：数据必须是整型，如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()，TO_DAYS()，MONTH()等函数。

COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。

RANGE COLUMNS分区可以对多个列的值进行分区。

COLUMNS分区支持以下的数据类型

所有的整型类型，如INT,SMALLINT,TINYINT,BIGINT。FLOAT和DECIMAL则不予支持
日期类型，如DATE和DATETIME。其余日期类型不予支持
字符串类型，如CHAR,VARCHAR,BINARY和VARBINARY。BLOB和TEXT类型不予支持。

子分区

子分区是在分区的基础上再进行分区，这种分区也称为复合分区。

Mysql数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

CREATE TABLE ts(a INT, b DATE)engine=innodb
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))
SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

可以通过使用SUBPARTITION语法来显式地指出各个子分区的名字。例如对上述ts表同时可以这样写

CREATE TABLE ts(a INT, b DATE)
PARTITION BY RANGE( YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
PARTITION p0 VALUES LESS THAN (1990)(
SUBPARTITION s0,
SUBPARTITION s1,
),
PARTITION p1 VALUES LESS THAN (2000)(
SUBPARTITION s2,
SUBPARTITION s3,
),
PARTITION p2 VALUES LESS THAN MAXVALUE(
SUBPARTITION s4,
SUBPARTITION s5,
),
);

子分区的建立需要注意以下几个问题：

每个子分区的数量必须相同
要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区
每个SUBPARTITION子句必须包括子分区的一个名字
子分区的名字必须是唯一的。

InnoDB关键特性

InnoDB存储引擎的关键特性包括

插入缓冲
两次写
自适应哈希索引
异步IO
刷新邻接页

插入缓冲

insert buffer

在Innodb存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。

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

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

对于辅助索引。在进行插入操作时，数据页的存放还是按主键a进行顺序存放，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。

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

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

Insert Buffer的使用需要同时满足以下两个条件

索引是辅助索引
索引不是唯一的

当满足以上两个条件时，Innodb存储引擎会使用insert buffer，这样就能提高插入操作的性能了。

change buffer

innodb存储引擎可以对DML——INSERT,DELETE,UPDATE都进行缓冲，它们分别是insert buffer,delete buffer,purge buffer。

change buffer适用的对象依然是非唯一的辅助索引。

对一条记录进行UPDATE操作可能分为两个过程

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

DELETE Buffer对应UPDATE操作的第一个过程，即将记录标记为删除。Purge Buffer对应UPDATE操作的第二个过程，即将记录真正的删除。

merge insert buffer

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

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

第一种情况为当辅助索引页被读取到缓冲池中时，例如正在执行正常的SELECT查询操作，这时需要检查Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会大幅度提高。

Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并至少有 $1 / 32$ 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 $1 / 32$ 页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是第二种情况。

还有一种情况，在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

两次写

两次写带给Innodb存储引擎的是数据页的可靠性。

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

在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。

自适应哈希索引

Innodb存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。Innodb存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

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

WHERE a=xxx
WHERE a=xxx and b=xxx

访问模式一样指的是查询的条件一样，若交替进行上述两种查询，那么innodb存储引擎不会对该页构造AHI。

此外AHI还有如下的要求

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

异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。

与异步IO方式对应的Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。

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