mysql 各种存储引擎介绍

一、什么是mysql引擎，有啥用？

mysql存储数据时，有不同的存储方式，这些方式都使用了不同的层次实现（如：存储机制，索引技巧，锁定水平），底层实现的差异带来的功能也就不同。在不同的场景下使用合适的存储方式就能让你的数据读写速度更快或者获得额外的功能。

二、查询Mysql支持存储引擎

show engines; //查看所有引擎

show variables like '%storage_engine%';//查看默认引擎

三、MySQL 各大存储引擎

InnoDB

InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并且实现了SQL标志的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目的是处理大容量数据库系统。

适用场景：

经常更新的表，适合处理多重并发更新亲求。

支持事务

可以从灾难中恢复（通过bin-log日志等）

外键约束。只有他支持外键。

支持自动增加列熟悉auto_increment。

.frm 文件 存储表定义；

.ibd 文件 独立表空间数据存储和索引

ibdata1 共享表空间，存储系统信息

InnoDB包含后台线程、缓冲池

后台线程

多线程模型，用来处理不同的问题

(1) Master Thread

把缓冲池数据异步刷新到磁盘，包括脏页、合并插入缓存、undo页回收。

(2)IO Thread

innodb使用大量AIO处理写IO请求。IO Thread的作用就是负责这些IO请求的回调。

(3)Purge Thread

事务提交后，使用undolog可能不再需要，需要purge回收已经分配的undo页。

(4)Page cleaner Thread

脏页的刷盘都放到这个线程了

内存

(1)缓存池

Innodb存储是基于磁盘，存储安装页进行管理，所以需要缓冲池协调一下CPU和磁盘速度，在数据库读页的时候，先从磁盘读到缓冲池，下一次读的时候先看缓冲池有没有。修改时也是先修改缓冲池里的数据，再以一定的频率写入磁盘。

缓存池有索引页，数据页，undo页，插入缓存，自适应哈希，锁信息，数据字典等。

缓冲池里还有redo log缓冲，和额外的缓冲空间。

1.Master Thread每1s把重做日志缓冲刷到重做日志文件

2.当有新事务提交的时候刷。

3.当重做缓冲池剩余空间小于1/2时刷。

(2)checkPoint

InnoDB有两种checkPoint：

sharp checkPoint（默认的工作方式）

fuzzy checkPoint

sharp checkPoint是在数据库关闭时把所有脏页刷到磁盘，但是数据库在运行的时候肯定不能这样。

Fuzzy是只刷新一部分，InnoDB引擎使用这种刷新方式，下面是发生fuzzy checkPoint的几种方式：

(3)mysql日志

日志redolog 和 binlog ，redolog 是独属于 innodb 的日志，binlog 则是属于 server 层的日志。

redolog 是物理日志，记录的是某个表的数据做了哪些修改，redolog 是固定大小的，也就是说后面的日志会覆盖前面的日志。

binlog 又称作归档日志，它记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作。binlog 是逻辑日志，记录的是某个表执行了哪些操作。binlog 是追加形式的写入日志，后面的日志不会被前面的覆盖。

//创建一个InnoDB表

create table testInnoDB(

id int unsigned primary key auto_increment,

name varchar(20) not null

)engine=innodb;

Mylsam

MyIsam 是mysql5.0之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。存储引擎独立于操作系统，简单说就是可用在windows上使用，也可用将数据转移到Lunex操作系统上。系统兼容性很好，插入和更新需要进行全表锁，效率会低一些。适合查询量大的数据库。表损坏后不能主动恢复。

tb_demo.frm，存储表定义；

tb_demo.MYD，存储数据；

tb_demo.MYI，存储索引。

//创建一个myisam表

create table test(

id int unsigned primary key auto_increment,

name varchar(20) not null

)engine=myisam;

innodb和Myiasm这两种引擎的数据结构都是B+树

存储的不同：

mysisam树节点存储得是数据的地址，指向实际的数据；

innodb树节点存储得是实际的数据；这种索引也被称为聚集索引

BDB

源自Berkeley DB,事务型数据库的另一种选择，支持Commit和Rollback等其他事务特效

Memory

创建表的数据都存储在内存中，并且使用HASH索引，服务一旦关闭，表中的数据就会丢失，表的访问速度非常快。

MRG_MYISAM存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

Blackhole

任何写入到此引擎的数据均会被丢失；Sekect语句内容永远是空。

Archive

Archive是归档的意思，在归档之后很多的高级功能就不在支持了，仅仅支持最基本的插入和查询两种。Archive拥有很好的压缩机制，它使用Zlib压缩库。

archive是归档的意思，仅仅支持插入和查询两种功能，在MySQL5.5以后支持索引功能，他拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用。适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差

CSV

数据存储为CSV文件格式，不用进行转换，可以对数据文件直接编辑

文件结构：

.frm表结构描述

.csv数据

.csm表状态、当前记录数量等

特点：

1、没有索引、不能为NULL、不能自增

2、更新和删除时先写入到临时文件，然后在rnd_end()函数中重新生成数据文件

3、可以对数据文件直接编辑（保存文本文件内容）

4、以CSV格式进行数据存储（逗号隔开，引号）

5、数据以文本方式存储在文件中

6、CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。

Performace_Schema

MySql 5.5新出的一个存储引擎，主要是用来收集数据库服务器的性能参数。但是我们无法创建该类型的表，它都是有MySql系统自己创建的。

主要提供如下功能：

1、提供进程的详细信息包含了 锁、互斥变量、文件信息

2、保持历史的事件汇总信息，为MySql服务器的性能做出详细的判断。

3、对于新增和删除监控时间点都非常容易，并可以随意的改变Mysql服务器的监控周期

这个需要在配置文件my.cnf中进行配置才能生效。

Federated

特点：

1、专门针对远程数据库的实现一般情况下在本地数据库中建表会在数据库目录中生成相应的表定义文件，并同时生成相应的数据文件。

但通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中通过这个引擎可以实现类似Oracle 下DBLINK的远程数据访问功能

2、本地的表结构必须与远程的完全一样。

3、远程数据库目前仅限MySQL

4、不支持事务

5、不支持表结构修改

Cluster/NDB

高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用

EXAMPLE

存储引擎是一个不做任何事情的存根引擎。它的目的是作为 MySQL 源代码中的一个例子，用来演示如何开始编写一个新存储引擎。同样，它的主要兴趣是对开发者。EXAMPLE： 存储引擎不支持编索引。