设置存储引擎SQL语句

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

show engines

在创建表时指定存储引擎

create table 表名 engine = innodb;

修改已有表的存储引擎

alter table 表名 engine = innodb;

innodb存储引擎

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

存储格式

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

例如：创建一个test数据，新建一张student表，选择存储引擎为innodb, 然后打开mysql的data下的test目录，发现有以下3个文件。

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

其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm是表结构文件，仅存储了表的结构、元数据(meta)，包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件，包括了单独一个表的数据及索引内容。

如果往表里插入了新的数据，则在mysql的data目录下会生成ibdata1文件，这个文件是存储了所有innodb表的数据。

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

了解表空间相关内容可以访问：https://chonglian.blog.csdn.net/article/details/122044721

体系架构

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

一.后台线程

Master Thread：主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSERT BUFFER)、UNDO页的回收等。
IO THread：在InnoDB存储引擎中大量使用了AIO (Async IO)来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。
Purge Thread：事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。（在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread 的工作，从而提高CPU的使用率以及提升存储引擎的性能。）
Page Cleaner Thread：Page Cleaner Thread是在InnoDB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread 的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

二.内存

缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database)。在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

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

具体来看，缓冲池中缓存的数据页类型有索引页，数据页，undo页，插入缓冲( insert buffer),自适应哈希索引(adaptive hash index)，InnoDB存储的锁信息（lockinfo)，数据字典信息(data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。从InnoDB 1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。

重做日志缓冲

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

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

Master Thread每一秒将重做日志缓冲刷新到重做日志文件;
每个事务提交时会将重做日志缓冲刷新到重做日志文件;
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

额外内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

例如，分配了缓冲池（ innodb_buffer_pool)，但是每个缓冲池中的帧缓冲（frame buffer）还有对应的缓冲控制对象(buffer control block)，这些对象记录了一些诸如LRU，锁，等待等信息，而这个对象的内存需要从额外内存池中申请。因此，在申请了很大的InnoDB缓冲池时，也应考虑相应地增加这个值。

关键特性

一.插入缓冲

插入聚集索引一般是顺序的，不需要磁盘的随机读取但插入非聚集索引叶子节点不是顺序的，需要离散访问非聚集索引页，速度较慢对于非聚集索引的插入或更新，先判断插入的非聚集索引页是否在缓存池中，若在，直接插入，或不在，先放到一个Inser Buffer对象中，然后根据一些算法将Insert Buffer缓存的记录通过后台线程慢慢合并刷新回辅助索引。插入缓冲将多次插入合并为一次操作，减少磁盘的离散操作。

使用Insert Buffer需满足两个条件：

索引是辅助索引
索引不是唯一的（不需要查找索引页判断唯一性）

InnoDB从1.0.x引入Change Buffer，对INSERT，DELETE，UPDATE都进行缓冲。

二.两次写

当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写失效（partial page write)。

因此在使用重做日志恢复数据库，需要有一个页的副本，当发生部分写失效时，先通过页的副本还原该页，再进行重做。于是InnoDB实现了doublewrite技术。

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

doublewrite要求刷新缓冲池的脏页时执行以下步骤：

通过memcpy函数将脏页复制到内存的doublewrite buffer
doublewrite buffer分两次，每次1MB顺序写入共享表空间
调用fsync函数同步磁盘，避免缓冲写带来问题，确保数据刷新到共享表空间（顺序写，开销小）
将上述的脏页数据写入各个表空间文件（离散写）

三.自适应哈希索引

哈希(hash）是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为o(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3～4层，故需要3～4次的查询。

InnoDB会监控对表上各索引页的查询执行情况，如发现建立哈希索引可以提升速度，则建立哈希索引，这是过程不需要用户干预。

四.异步 IO

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

五.刷新邻接页

当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，一起刷新，这是可以通过AIO将多个IO写入操作合并为一个IO操作。

考虑到下面两个问题：

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

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

更多精彩文章还请访问：https://blog.csdn.net/weixin_45692705?spm=1011.2124.3001.5343

详解MySQL存储引擎Innodb